Java MySQL数据库学习笔记

 

SQL语句

SQL语言的分类

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL

DQL语句

数据查询语言DQL(Data QueryLanguage)

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

 

 

 

 

 

 单行函数

 substr(ename,2,3) 第二个字母开始，共三个字母

补充字符串函数： lpad rpad(表名,表格内容数量，'补充的字符')

 去空格函数：ltrim rtrim("含有空格的内容")

 数值函数

伪表：dual 无表可操作数据库 

abs(-10)        绝对值函数  10

floor(3.2)        向下取整  3

ceil(3.2)        向上取整  4        ceil(3.0)        3

power(2,3)        幂次方        2^3

log(2,4)        取对数       2

ln(e)        自然数的对数        1

round(3.2)        四舍五入        3

now()        当前系统时间

sysdate()        当前系统日期

add_month()        返回指定月数后的日期

last_day()        返回本月最后一天的日期

month_between()        返回两个日期间隔的月数

 

concat("123",456)      字符串拼接  123456

to_char()        转换字符

to_number()        转换数字

 

 

 

 

 组合

 

 分组函数必须在分组后用

 

两写法相同，能用where，就不用having

 

 

 

  

 九二与九九语法

 左右外连接

 ​​​​

 

 SELECT * FROM emp WHERE sal < ANY(SELECT sal FROM emp WHERE deptno = 30);
-- any和some相同

 注意：子查询另外一个结果必须为一个列的

 

 结果作为伪表

-- 01列出至少有四个员工的所有部门
Select d.dname  from emp e  JOIN dept d on(e.DEPTNO = d.DEPTNO) GROUP BY d.dname having COUNT(e.ename )>=4;
-- 02列出薪金比“adams”多的所有员工。（大于smith员工的最大薪水）
Select ENAME from emp e where sal>(select sal from emp where ename = 'adams');
-- 03列出所有员工的姓名及其直接上级的姓名
select e.ename,m.ename boss from emp e left join emp m on(e.mgr=m.empno);
-- 04 列出受雇日期早于其直接上级的所有员工
select e.ename ,m.ename boss,e.HIREDATE,m.HIREDATE bosshiredate  from emp e  join emp m on(e.mgr=m.empno) where e.hiredate<m.HIREDATE;
select e.ename ,m.ename boss,e.HIREDATE,m.HIREDATE bosshiredate  from emp e ，emp m where e.mgr=m.empno and e.hiredate<m.HIREDATE;
-- 05 列出部门名称和这些部门的员工信息，包括那些没有员工的部门
select d.DNAME,e.ENAME from emp e right JOIN dept d on(e.DEPTNO = d.DEPTNO);
-- 06 列出所有job为“clerk”(办事员)的姓名及其部门名称
select e.ENAME,d.Dname from emp e right JOIN dept d using(DEPTNO) where job='clerk';
-- 07 列出最低薪资金大于1500的各种工作 
select job,min(sal) from emp  GROUP BY job having min(sal)>1500 ;
-- 08列出在部门"sales"(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select e.ENAME ,d.dname from emp e  JOIN dept d using(DEPTNO) where d.DNAME='sales';
-- 09 列出薪金高于平均薪金的所有员工。
select e.ename, e.sal  from emp e  where sal>(select avg(sal) from emp);
-- 10 列出与“scott”从事相同工作的所有员工。
select e.ename,e.job from emp e where job = (select job from emp where ename='scott');
-- 11 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select * from emp where sal in(select sal from emp where DEPTNO = 30) and deptno<>30;
-- 12 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
select * from emp where sal>(select max(sal) from emp where deptno = 30) and deptno<>30;

-- 13 列出在每一个部门工作的员工数量，平均工资和平均服务期限。
select  COUNT(ename),avg(sal),avg(TO_DAYS(now()-HIREDATE)),deptno from  emp group by DEPTNO ORDER BY deptno;
-- 14 列出所有员工的姓名，部门名称和工资。
select e.ename, d.dname,e.sal from emp e join dept d using(deptno);
-- 15 列出从事同一种工作但属于不同部门的员工的一种组合
select e.ename,m.ename ,e.job,e.deptno,m.deptno from emp e,emp m  where e.job=m.job	and e.DEPTNO<>m.deptno and e.empno>m.empno ORDER BY m.deptno;
-- 16 列出所有部门的详细信息和部门人数
select * from emp e right join dept d using(deptno) right join(select e.DEPTNO,COUNT(ename) from emp e  group by e.DEPTNO ORDER BY e.deptno) m using(deptno) ;
-- 17 列出各种工作的最低工资
select min(sal),job from emp GROUP BY job;
-- 18 列出各个部门经理的最低薪金(job为MANAGER)
select MIN(SAL),DEPTNO from emp where job = 'manager' GROUP BY DEPTNO;
-- 19 列出所有员工的年工资，按年薪从低到高排序。
SELECT ENAME, (sal+IFNULL(comm,0))*12 from emp order BY 2; -- 2代表前面第二列
-- 20列出所有job="clerk"的员工平均薪资
select ename,avg(sal) from emp  where job = 'clerk' group by ename;
-- 21列出job = ’clerk‘ 员工的平均薪资 按照部门分组
select ename,avg(sal),deptno from emp  where job = 'clerk' group by deptno,ename ORDER BY DEPTNO;
-- 22 列出job = ’clerk‘ 员工的平均薪资 按照部门分组 并且部门编号 in（10,30） 按照平均薪资 降序排序
select ename,avg(sal),deptno from emp  where job = 'clerk' and deptno in(10,30) group by deptno,ename ORDER BY avg(sal);
-- 23 列出job = ’clerk‘ 员工的平均薪资 按照部门分组 并且部门编号 in（20,30） 并且部门人数>=2人，按照平均薪资 降序排序
select avg(sal),deptno from emp  where job = 'clerk' and deptno in(20,30)  group by deptno  having count(ename)>=2 ORDER BY avg(sal) desc;

DML

2 .数据操纵语言DML(Data Manipulation Language)
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

 

 drop 删表         delete 删数据

 事务的隔离级别

 DDL

3. 数据定义语言DDL(Data Definition Language)
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 视图 索引 同义词 簇

DDL操作是隐性提交的！不能rollback

 数据类型

 ​​​​

 

 

 

 

 回顾 

 

 索引

 

视图 

 

 

一、三大范式
1.第一范式（1NF）
原子性：强调的是列的原子性，即数据库中每一列的字段都是单一属性，不可再分的。并且这个单一属性必须是由基本的数据类型所构成的，如整数、字符串等。下面给大家举个例子：

这是一张员工表：

员工ID    姓名    性别    部门    联系电话
101    周星星    女    销售部    15015246623
现在我们来分析上表，这张员工表是不符合第一范式标准的，每个员工只有一个员工ID，也确实只有一个姓名，一个性别，只属于一个部门，但是在我们的实际生活中，每个人真的只有一个联系电话吗？这里我们就假设最少的情况，每个人都有个人电话和家庭电话，那么联系电话这一字段就是可再分的。这张表的结构设计就没有达到第一范式。
解决方案：我们只需要把联系电话这一字段分为个人电话字段和家庭电话字段，就完全符合了第一范式（如下表）。

员工ID    姓名    性别    部门    个人电话    家庭电话
101    周星星    女    销售部    15015246623    663323
2.第二范式（2NF）
依赖性：在满足1NF的基础上再满足依赖性的两个约束：一张表必须有一个主键；非主键类必须完全依赖于主键，而不能只依赖主键的一部分。
这是一张商品供销信息表：

商品    供销商    价格    重量    分类    供销商电话
啤酒    饮品1厂    3    300ml    液体    18016253155
啤酒    饮品2厂    5    300ml    液体    18055231233
可乐    饮品2厂    5    250ml    液体    18055231233
需要清楚几点：
1.商品与供销商是多对多的关系
2.该表中关键字是一组组合关键字<商品，供销商>，只有商品和供销商两个字段结合才可标识出一件商品
3.存在以下部分依赖的关系
商品---->价格，重量，分类
供销商---->供销商电话

由以上存在的部分依赖关系就可知道该商品表不符合第二范式了，价格，重量，分类只依赖于商品这个主键，而供销商电话也只依赖于供销商这个主键，已经完全违背了第二范式非主键列必须完全依赖于主键而不能只依赖于主键的一部分这一原则了。
解决方案：把上表商品表拆分为商品表和供销商表两张表，既满足了非主键字段必须完全依赖主键这一条件又减少了供销商电话重复这一冗余。

商品    供销商    价格    重量    分类
啤酒    饮品1厂    3    300ml    液体
啤酒    饮品2厂    5    300ml    液体
可乐    饮品2厂    3    250ml    液体
供销商    供销商电话
饮品1厂    18016253155
饮品2厂    18055231233
3.第三范式（3NF）
在满足2NF的基础上，另外再满足一个条件：非主键列必须直接依赖于主键，不能存在传递依赖。
这是一张学生课表：

课程编号    课程名字    上课时间    任课老师    老师电话    老师职位
101    马克思理论基础    8:00    Lily    18016253155    讲师
102    经济学    14:00    Lucy    18055231233    教授
主键：课程编号
大致一看，上表中的非主键列确实完全是依赖于主键（课程编号）的，符合第二范式2NF。但是问题是：老师电话，老师职位直接依赖的是任课老师（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。
解决方案：依然是通过拆分，把上述学生课表拆分为课程表和教师表。

课程表：

课程编号    课程名字    上课时间    任课老师
101    马克思理论基础    8:00    Lily
102    经济学    14:00    Lucy
教师表：

任课老师    老师电话    老师职位
Lily    18016253155    讲师
Lucy    18055231233    教授
二、五大约束
经常有听到三大范式五大约束这种说法，上面我们已经详细说明了三大范式，这里我们就来简单说明一下大家常说的五大约束，到底是哪五个约束呢？
1.PRIMARY KEY(primary key):设置主键约束；
2.UNIQUE(unique)：设置唯一性约束，不能有重复值；
3.DEFAULT(default)：默认值约束，height DOUBLE(3,2) height不输入是默认为(1,2)。
4. NOT NULL(not null)：设置非空约束，该字段不能为空；
5. FOREIGN KEY (foreign key):设置外键约束。

 

 

 

 

 

 

 declare 定义        cursor 针对于

 

 

 ​​​​​​

 

 

 事件

 

 

 中括号内容可不写

 

 

 

 

 

 

JDBC

JDBC处理DML和DQL请求

public class control {
    //Mysql连接的的配置信息
    private static String driver ="com.mysql.cj.jdbc.Driver";
    private static String url ="jdbc:mysql://localhost:3306/crm?useSSL=false&serverTimezone=UTC";
    private static final String user = "root";
    private static final String password = "123";
    private static Scanner scanner = new Scanner(System.in);
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        String id = UUID.randomUUID().toString().substring(0,9);//随机生成不重复的码
//        System.out.println(id);
        //Q01validateUsernameOnly();
        //Q02AddUser();
   //     Q03DeleteUser();
//        Q04UpdatePassword();
   //     Q05LoginUser();
//        Q06RandomInfo();
//        Q07UpdateUserRole();
     //   Q08RolePowerInfo();
     //   Q09SelectUserByPower();

        Q10RolePower();

    }

    private static void Q10RolePower() throws ClassNotFoundException, SQLException {
        //        10.可以对权限和角色关系进行删改查 例如删除角色的权限

        //显示出所有的映射关系
        String sql = "select * from t_role r " +
                "join t_rolepower rp using(rid) join t_power p using(pid) order by rolename";
        System.out.println(sql);
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("角色【"+resultSet.getString("rolename")+resultSet.getString("rid")+"】对应的权限为【"+resultSet.getString("powername")+resultSet.getString("pid")+"】");
        }
        System.out.println("请选择您要操作的角色id");
        String rid = scanner.next();
        System.out.println("请输入您的操作方式：增加/删除");
        String powername = scanner.next();
        System.out.println("请选择您要操作的角色权限id");
        String pid = scanner.next();
        String sqlc = "";
        if ("增加".equals(powername)){
            String rpid = UUID.randomUUID().toString().substring(0,9);
                sqlc="insert into t_rolepower values('"+rpid+"','"+rid+"','"+pid+"')";
            System.out.println(sqlc);
        } else if("删除".equals(powername)){
                sqlc = "delete from t_rolepower where rid = '"+rid+"' and pid = '"+pid+"'";
        }
        //jdbc
        int rows= statement.executeUpdate(sqlc);
        System.out.println(rows==1?"操作成功":"操作失败");
    }

    private static void Q09SelectUserByPower() throws ClassNotFoundException, SQLException {
        //        9.根据权限名查询出该权限被那个用户所持有
        System.out.println("请输入需查询的权限名");
        String powerName = scanner.next();
        String sql ="select * from t_user u join t_role r using(rid) " +
        "join t_rolepower rp using(rid) join t_power p using(pid) " +
                "where powername= '"+powerName+"'";
        System.out.println(sql);
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println("权限【"+powerName+"】被用户"+resultSet.getString("username")+"所拥有");
        }
    }

    private static void Q08RolePowerInfo() throws ClassNotFoundException, SQLException {
        //        8.当用户登录成功后，打印出用户的角色和所有权限
        boolean flag = false;
        System.out.println("请输入您的用户名");
        String username = scanner.next();
        System.out.println("请输入您的密码");
        String password = scanner.next();
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql ="select * from t_user u join t_role r using(rid) " +
                "join t_rolepower rp using(rid) join t_power p using(pid) " +
                "where username ='"+username+"' and password ='"+password+"'";
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            flag=true;
            System.out.println("用户【"+username+"】角色为"+resultSet.getString("rolename")+
                    " 权限为"+resultSet.getString("powername"));

        }
        if(!flag) {
            System.out.println("用户【"+username+"】登录失败");
        }
    }

    private static void Q07UpdateUserRole() throws ClassNotFoundException, SQLException {
    /* 7.修改用户角色
     Scanner提示让用户输入  用户名
     首先显示出 当前用户的角色，并显示出所有的角色
     然后Scanner提示让用户输入新角色名称，根据角色名称修改用户的角色RID*/

        System.out.println("请输入您的用户名");
        String username = scanner.next();
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql ="select u.username ,r.rolename from t_user u join t_role r using(rid) where username = '"+username+"'";

        ResultSet resultSet = statement.executeQuery(sql);
        if(resultSet.next()){
            System.out.println("当前用户【"+username+"】的角色为【"+resultSet.getString("r.rolename")+"】");

            //显示所有角色信息
            String sql1 ="select * from t_role";
            ResultSet resultSet1 = statement.executeQuery(sql1);
            System.out.println("当前所有的角色为:");
            while (resultSet1.next()){
                System.out.print(resultSet1.getString("rolename")+"\t");
            }
            System.out.println();
            //收集信息
            System.out.println("请输入新的角色名");
            String roleName = scanner.next();
            //sql
            String sql2 ="update t_user set rid =(select rid from t_role where rolename = '"+roleName+"') where username = '"+username+"'";
            //jdbc  dml
            int rows = statement.executeUpdate(sql2);
            System.out.println(rows==1?"修改用户【"+username+"】的角色为【"+roleName+"】成功":"修改用户【"+username+"】的角色为【"+roleName+"】失败");

        }else {
            System.out.println("用户名【"+username+"】不存在");
        }
    }

    private static void Q06RandomInfo() throws ClassNotFoundException, SQLException {
    /* 6.以用户为例：批量生成记录
             所有的数据项全部随机生成
     ID UUID
     用户名 长度为8位的数字
     密码 长度为6de数字
     电话号码 长度为8的数字
     创建时间 当前系统时间
     角色ID   默认普通会员*/
        for (int i = 0; i < 20; i++) {
            String uid = UUID.randomUUID().toString().substring(0,9);//随机生成不重复的码
            String username = getRandomChars(8);
            String password = (int)(Math.random()*900000+100000)+"";
            String phoneNumber =(int)(Math.random()*90000000+10000000)+"";
            String rid ="111";
            //使用JDBC将数据更新到数据库
            Statement statement = extracted();
            String sql ="INSERT INTO t_user VALUES('"+uid+"','"+rid+"','"+username+"','"+password+"',"+phoneNumber+",NOW());";
            System.out.println(sql);
            int rows = statement.executeUpdate(sql);
            System.out.println(rows==1?"生成成功":"生成失败");

        }
    }

    private static String getRandomChars(int length){
        if(length<=0){
            length = 10;
        }else if(length >50){
            length = 50;
        }

        StringBuilder sb = new StringBuilder();
        Random rd =new Random();
        for (int i = 0; i < length; i++) {
            int choice = rd.nextInt(2) % 2 == 0 ? 65 : 97; // 取得大写还是小写
            sb.append((char)(choice + rd.nextInt(26)));
        }
        return sb.toString();
    }

    private static void Q05LoginUser() throws ClassNotFoundException, SQLException {
    /*5，以用户为例：登录
    Scanner提示让用户输入 用户名 密码，判断是否匹配*/
        System.out.println("请输入您的用户名");
        String username = scanner.next();
        System.out.println("请输入您的密码");
        String password = scanner.next();
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql ="select username,password from t_user where username ='"+username+"' and password = '"+password+"'";
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println(resultSet.next()?"登录成功":"登录失败");
    }

    private static void Q04UpdatePassword() throws ClassNotFoundException, SQLException {
    /*4，以用户为例：修改密码
    Scanner提示让用户输入 用户名 旧密码 新密码
    如果用户名和旧密码匹配就修改为新密码*/
        System.out.println("请输入您的用户名");
        String username = scanner.next();
        System.out.println("请输入您的旧密码");
        String password = scanner.next();
        System.out.println("请输入您的新密码");
        String newPassword = scanner.next();
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql = "update t_user set password = '"+newPassword+"' where password = '"+password+"'";
        System.out.println(sql);
        int result = statement.executeUpdate(sql);
        System.out.println(result==1?"修改【"+username+"】成功":"修改【"+username+"】失败");


    }

    private static void Q03DeleteUser() throws ClassNotFoundException, SQLException {
    /*3.以用户为例：删除
    Scanner提示让用户输入 用户名，去数据库删除对应的用户*/
        System.out.println("请输入您要删除的用户名");
        String username = scanner.next();
        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql ="DELETE from t_user where username='"+username+"';";
        System.out.println(sql);
        int result = statement.executeUpdate(sql);
        System.out.println(result==1?"删除【"+username+"】成功":"删除【"+username+"】失败");
    }

    private static void Q02AddUser() throws ClassNotFoundException, SQLException {
    /*2.以用户为例:增加
    Scanner提示让用户输入 用户名 密码 电话号码
    ID 自动生成
    创建时间 当前时间
    角色ID 默认普通会员
    然后将收集到的数据插入到数据库*/
        System.out.println("请创建您的用户名");
        String username = scanner.next();
        System.out.println("请设置您的密码");
        String password = scanner.next();
        System.out.println("请输入您的电话号码");
        String phoneNumber = scanner.next();
        String uid = UUID.randomUUID().toString();
        String rid ="111";

        //使用JDBC将数据更新到数据库
        Statement statement = extracted();
        String sql ="INSERT INTO t_user VALUES('"+uid+"','"+rid+"','"+username+"','"+password+"',"+phoneNumber+",NOW());";
        System.out.println(sql);
        int result = statement.executeUpdate(sql);
        if (result==1){
            System.out.println("注册成功");
        }else {
            System.out.println("注册失败");
        }
    }


    private static void Q01validateUsernameOnly() throws ClassNotFoundException, SQLException {
    /*1.以用户为例：唯一性校验
    Scanner 提示用户输入用户名，然后去数据库判断用户名是否存在
    例如：
    输入 admin 提示：用户名已被占用
    输入zhangsan 返回用户名可用*/
        System.out.println("请输入您的用户名");

        String username = scanner.next();
        Statement statement = extracted();
        String sql ="select * from t_user where username='"+username+"'";
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()){
            System.out.println("您的用户名【"+username+"】已经被占用");
        }else {
            System.out.println("您的用户名【"+username+"】可以使用");
        }
    }

    //加载驱动
    private static Statement extracted() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName(driver);
        //创建连接
        Connection connection = DriverManager.getConnection(url, user, password);
        //创建对象可以发送SQL语句
        Statement statement = connection.createStatement();
        return statement;
    }