Mysql数据库学习Day02

DBMS:mysql-installer-community-8.0.30.0

DB可视化工具:SQLyog-12.1.4-0.x64

1.单行函数

        单行函数有很多，用的时候再去查

1.1 数学函数

        abs()绝对值
        ceil()向上取整
        floor()向下取整
        round(4.1234,2)四舍五入,第二个参数是保留小数位数,不写就是不保留小数
        truncate(4.5678,2)不四舍五入,截断小数,必须是2个参数
        rand()产生随机的小数[0,1)
        sqrt()开平方
        pow(2,3)求2的3次方

1.2 字符串函数

        concat("a","b","c");

        concat_ws("-","a","b"); // a-b  指定以什么拼接符进行拼接     
        length("abc")   //3
        length("李白")  //6   求字节数,用的不多

        char_length("李白") //2  求字符数,用的多
        upper  变大写
        lower  变小写
        left 返回字符串从左开始的几个字符
        right 返回字符串从右开始的几个字符    

        position("bc" in "abcdef");  //2   位置从1开始

        lpad("abc",5,"啊") //从左边以“啊”去填充abc，到5位
        rpad("abc",5,"啊") //从右边以“啊”去填充abc，到5位

        trim(str) 去除字符串两边的空白
        ltrim(str) 去除左边的空白
        rtrim(str) 去除右边的空白
        通过trim也可以去除特定的字符，需要去带参数
        insert(str,2,4,"aaa") 从字符串的第二个位置开始，4个字符整体使用aaa替换

        reverse(str)  翻转字符串

        substring()  截取字符串
            SELECT SUBSTRING("abcdefg",2);  //从第2位开始截取到最后
            SELECT SUBSTRING("abcdefg",2,4);//从第2位开始截取4位

        substring_index("a.b.c.d.e.f",".","3")  //a.b.c 

字符串函数练习

            查询名字和邮箱@之前的内容（多种写法）

SELECT ename,email,TRIM(TRAILING "@qq.com" FROM email) FROM t_employee;
SELECT ename,email,LEFT(email,POSITION("@" IN email)-1) FROM t_employee;
SELECT ename,email,SUBSTRING_INDEX(email,"@",1) FROM t_employee;
SELECT ename,email,SUBSTRING(email,1,POSITION("@" IN email)-1) FROM t_employee;

1.3 日期函数

        curdate()  当前日期
        curtime()  当前时间
        now()      获取年月日时分秒
        sysdate()  获取年月日时分秒
        sleep(5)   睡眠5秒,now和sysdate的区别

SELECT NOW(),SLEEP(5),SYSDATE();

        

        year(now())
        month(now())

        day(now())  

        hour(now())

        minute(now())

        second(now())
        datediff(now(),"2023-3-1"); 求当前时间和指定日期的差值

        date_add("2020-1-1",interval 2 year);在当前日期上加2年

        timestampdiff(HOUR,"2020-3-1","2021-3-1"); 求后面的时间和前面的时间的小时差

日期时间函数的练习

            求入职10年以上的员工

SELECT ename,hiredate FROM t_employee WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW();

1.4 条件判断函数

        if(value,t,f)  true返回t，false返回f
             大于15000显示高工资否则显示普通工资

SELECT *,IF(salary > 15000,"high salary","normal salary") FROM t_employee;

        ifnull(value1,value2) 如果value1不为null返回value1,如果为null返回value2
             统计年薪

SELECT ename,salary,TRUNCATE(salary *12 *(1+IFNULL(commission_pct,0)),2) FROM t_employee;

         

        case when 条件1 then result1
              when 条件2 then result2
              ...
              else resultn
              end;

             等价于 if elseif elseif else

             练习1：根据薪资分为 A B C ...

SELECT ename,salary,CASE WHEN salary > 20000 THEN "A"
			WHEN salary >15000 THEN "B"
			WHEN salary >10000 THEN "C"
			WHEN salary >5000 THEN "D"
			ELSE "E" 
			END 薪资类别
			FROM t_employee;

             练习2：
                 eid > 25   AAA
                 eid > 20   BBB
                 eid > 15   CCC
                 eid > 10   DDD
                 else       EEE

SELECT ename,salary,CASE WHEN eid >25 THEN "AAA"
			WHEN eid >20 THEN "BBB"
			WHEN eid >15 THEN "CCC"
			WHEN eid >10 THEN "DDD"
			ELSE "EEE"
			END category FROM t_employee;

         case 字段  when 常量值 then result
                    when 常量值 then result
                    ...
                    else resultn
                    end;

             等价于switch....case

             练习1：根据did分为各种部门...

 SELECT ename, CASE did WHEN 1 THEN "总裁办"
			WHEN 2 THEN "技术部"
			WHEN 3 THEN "市场部"
			WHEN 4 THEN "人事部"
			WHEN 5 THEN "财务部"
			ELSE "其他部门"
			END 部门
 FROM t_employee;

             练习2：
                 job_id  1  总裁
                         2  经理
                         3  程序员
                         4  主管
                         5  前台
                      else  其他人员

 SELECT ename,CASE job_id WHEN 1 THEN "总裁"
			WHEN 2 THEN "经理"
			WHEN 3 THEN "程序员"
			WHEN 4 THEN "主管"
			WHEN 5 THEN "前台"
			ELSE "其他人员"
			END jobCase FROM t_employee;

1.5 其它函数

        系统信息函数

                select user(),database(),version();
        加密函数
                select password("abc"); mysql8不支持
                select MD5("abc");
                select sha("abc");  使用sha1算法加密出来是20字节
                select sha2("abc",0)  后面的数字决定加密的算法 sha224  sha256 sha384 sha512,长度0代表就是256
            

1.6 窗口函数

（联机分析函数）（低于8版本的没有）
        序号函数
        row_number()   顺序排序 每行按照不同的分组顺序排序  1,2,3,4
        rank()         并列排序 每行按照不同的分组顺序排序  1,1,1,4   跳过重复的序号
        dense_rank()   并列排序 每行按照不同的分组顺序排序  1,1,1,2   不跳过重复的序号

        先把要查询的数据查询完成，再去处理窗口函数的语句

        用法:窗口函数（） over（）

        over可以书写子句:
                partition by 字段   以什么字段去分组（篱笆）
                order by 字段     以什么字段在组内进行排序   asc升序  desc降序

2.关联查询

2.1 什么是关联查询

        一张表拿不到想要的所有信息，信息是需要通过多张表联合才能拿到
        这些表之间一定会有关联字段，这些关联字段可以有外键也可以没有

2.2 关联查询的情况说明

        7种情况

2.3 内连接

        关联查询的连接方式
            内连接           inner join   对应的就是A交B
            外连接
                左外连接     left join  显示左表中所有的数据
                右外连接     right join 显示右表中所有的数据
                全外连接     full join（mysql不支持）  显示左表和右表中所有的数据  mysql要使用union去实现

            自连接
                把一张表当做两张表对待，物理上是一张表，逻辑上是两张表

        查询的方式
            1、确定数据源  from   A join B
            2、确定关联条件  on
            3、确定是筛选条件
            4、确定数据

        注意：关联查询时候如果有相同的字段，需要使用表名.字段去区分，可以给表设置别名去简化
             不设置关联条件出现笛卡尔积

        查询员工姓名 员工薪水 部门编号 部门名称

SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`
FROM t_employee e JOIN t_department d
ON e.`did` = d.`did`;

        查询员工姓名 员工薪水 工作编号 工作描述

SELECT e.`ename`,e.`salary`,j.`jid`,j.`jname`
FROM t_employee e JOIN t_job j
ON e.`job_id` = j.`jid`;

        查询性别为男 薪水大于15000的员工姓名 薪水 部门编号和部门名称

SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`
FROM t_employee e JOIN t_department d
ON e.`did` = d.`did`
WHERE gender = "男" AND salary > 15000;

2.4 外连接

            左外： 查看所有的学生选择教室的信息  包含没有教室的学生
            右外： 查看所有学生选择教室的信息  包含没有被学生选择的教室
            全外： 查看所有的学生选择教室的信息，包含没有选择教室的学生也包含没有被学生选择的教室
                    mysql不支持全外连接，如果要实现相同的效果需要使用联合 union 把上面两种结果联合在一起即可

2.5 联合查询练习

        1、显示员工的名字 薪水 工作编号 工作名称 工作的描述信息及没有工作的员工

SELECT e.`ename`,e.`salary`,j.`jid`,j.`jname`,j.`description`
FROM t_employee e LEFT JOIN t_job j
ON e.`job_id` = j.`jid`;

        2、显示员工的名字 薪水 部门编号 部门名称 部门的描述信息及没有部门的员工

SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`,d.`description`
FROM t_employee e LEFT JOIN t_department d
ON e.`did` = d.`did`;

        3、显示员工的名字 薪水 部门编号 部门名称 部门的描述信息及包含没有员工的部门

SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`,d.`description`
FROM t_employee e RIGHT JOIN t_department d
ON e.`did` = d.`did`;

        4、将 23的结果联合在一起

SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`,d.`description`
FROM t_employee e LEFT JOIN t_department d
ON e.`did` = d.`did`
UNION
SELECT e.`ename`,e.`salary`,d.`did`,d.`dname`,d.`description`
FROM t_employee e RIGHT JOIN t_department d
ON e.`did` = d.`did`;

2.6 自连接

        就是把一张表看成是两张表来对待，物理上是一张表，逻辑上是两张表
        获取普通员工的编号、名字、薪水 及上级领导的员工编号 名字和薪水

SELECT e.`eid`,e.`ename`,e.`salary`,u.`eid`,u.`ename`,u.`salary`
FROM t_employee e JOIN t_employee u
ON e.`mid` = u.`eid`;

2.7 关联查询的注意事项

        关联查询如果没有写关联条件会产生笛卡尔积
        sql92和sql99写法都可以去写关联查询，sql92是通过where去关联，后期推荐sql99

3.select的7大子句

    7大子句的顺序是固定的，中间没有谁，弟弟可以往前顶替
    1、from 2、join on 3、where 4、group by 5、having 6、order by 7、limit

3.1 from, join on, where

3.2 group by

        统计每个部门的平均薪资和最高薪资

SELECT AVG(salary),MAX(salary)
FROM t_employee
GROUP BY did;

        统计每个工作的平均薪资 最高薪资 最低薪资  人数

SELECT AVG(salary),MAX(salary),MIN(salary),COUNT(*)
FROM t_employee
GROUP BY job_id;

        统计男女员工人数 平均薪资 最高薪资 总薪资

SELECT COUNT(*),AVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROM t_employee
GROUP BY gender;

        求相同部门不同工种的员工平均薪资 最高薪资和人数（多字段分组）

SELECT did,job_id,AVG(salary),MAX(salary),COUNT(*)
FROM t_employee
GROUP BY did,job_id;

        统计每个部门的最高薪资 最低薪资 人数 部门名称 部门描述信息

SELECT MAX(e.`salary`),MIN(e.`salary`),COUNT(*),d.`dname`,d.`description`
FROM t_employee e JOIN t_department d
ON e.`did` = d.`did`
GROUP BY e.`did`;

        统计每个部门的女员工的平均薪资和人数

SELECT did,AVG(salary),COUNT(*)
FROM t_employee
WHERE gender = "女"
GROUP BY did;

        注意：
            只进行分组，select后面只能放分组字段，否则有结果无意义
            进行分组时可以放多个字段，多字段分组
            关联查询时候，select后面可以放分组字段之外的字段
            with rollup的使用，对分组后的结果进行汇总

3.3 having

        对分组后的数据进行筛选
        统计每个部门的女员工的平均薪资 人数 显示人数>=3的组合

SELECT did,AVG(salary),COUNT(*)
FROM t_employee
WHERE gender = "女" -- and count(*)>=3;
GROUP BY did
HAVING COUNT(*)>=3;

        统计每个部门的 平均薪资 人数  最高薪资 最低薪资 显示人数 >=3 的组合并进行统计

SELECT AVG(salary),COUNT(*),MAX(salary),MIN(salary)
FROM t_employee
GROUP BY did
WITH ROLLUP
HAVING COUNT(*)>=3;

            注意 WITH ROLLUP必须放在group by后面 having前面

3.4 order by

        asc   升序
        desc  降序
        order by 可以根据多个字段排序
            先根据1排序，如果1的顺序相同，再通过2进行排序

3.5 limit m, n

        n代表每页显示几条数据
        m代表从第几条开始的索引下标  从0开始
        limit (page - 1)*pageSize, pageSize

3.6 七大子句练习

        查询每个编号为偶数的部门，显示部门编号 名称 员工数量
        只显示员工>=2的结果，按照员工数量升序排序
        每页显示两条显示第一页

SELECT d.`did`,d.`dname`,COUNT(*)
FROM t_employee e JOIN t_department d
ON e.`did` = d.`did`
WHERE e.`did` % 2 = 0
GROUP BY e.`did`
HAVING COUNT(*) >= 2
ORDER BY COUNT(*) ASC
LIMIT 0,2;