• Mysql知识复习二


    多行函数

    1. 不管函数处理多少条,只返回一条记录

    2. 如果你的数据可以分为多个组,那么返回的数据条数和组数相同

      • 每个部门的平均薪资
      • 10 20 30 --》3
    3. 常用的多行函数有5个

      • max最大值

        如果处理的值是字符串,将会把值按照字典序升序

      • min最小值

        如果处理的值是字符串,将会把值按照字典序排序

      • avg平均值‘

        只能用于数值型数据,求平均值

      • sum求和

        如果求和的过程中有null,那么不会计算在内

      • count求总数

        如果统计的数据中有null,不会吧null统计在内

    4. 经典的错误

      • --查询公司最低薪资的员工是谁?

      • select min(sal) , ename from emp;

        MySQL语法不可行

        Oracle语法不可行

      • 将来工作的时候不能把普通列和组函数写在一起

        虽然MySQL语法不会报错,但是给的结果是错误的

    数据分组

    1. 按照某一个条件进行分组,每一组返回对应的结果
    2. group by 可以对指定的类进行分组,列尽量有相同的
    3. having 可以对分组之后的数据进行过滤,所以能出现在having中的比较项一定是被分组的列或者是组函数
    4. 底层
      • where称之为行级过滤,处理的是表中每一行数据的过滤
      • having称之为组级过滤,处理的是分组之后的每一组数据
      • 能使用where的,尽量不要使用having
    1. -- 查询每种工作的平均薪资
    2. select job,avg(sal) from emp group by job;
    3. -- 查询每个部门的最高薪资和最低薪资
    4. select max(sal),min(sal) from emp;
    5. select deptno,max(sal),min(sal) from emp group by deptno;
    6. -- 查询每个部门的人数和每月工资总数
    7. select deptno,count(empno),sum(sal) from emp group by deptno;
    8. -- 查询每个部门,每种工作的平均薪资
    9. select deptno,job , avg(sal) from emp group by depyno,job;
    10. select deptno,job , avg(sal) from emp group by deptno,job order by dedptno,job;
    11. -- 查询个人姓名的平均薪资(尽量对多数据进行分组)
    12. select ename,max(sal),min(sal) from emp group by ename;
    1. -- 查询10,20部门中,并且在二月份入职员工中,每个部门中平均薪资高于1500的工作是什么,并按照部门,工作平均薪资进行排序
    2. select * from emp where deptno in (10,20);
    3. select deptno,job,avg(sal) from emp where deptno in (10,20) group by deptno,job having avg(sal)>1500;
    4. select deptno,job,avg(sal) from emp where deptno in (10,20) group by deptno,job having avg(sal)>1500 order by deptno,avg(sal);
    5. -- 美观写法
    6. SELECT
    7. deptno,
    8. job,
    9. avg(sal) '平均薪资'
    10. FROM
    11. emp
    12. WHERE
    13. deptno IN ( 10, 20 )
    14. GROUP BY
    15. deptno,
    16. job
    17. HAVING
    18. avg(sal) > 1500
    19. ORDER BY
    20. deptno,
    21. avg(sal);

    DQL单表关键字执行顺序

    • select

      我们要显示哪些列的数据

    • from

      从哪张表中获取数据

    • where

      从表中获取数据的时候进行行级的数据过滤

    • group by

      对数据进行分组处理,一组获取对应的结果

    • having

      组级过滤,组级过滤的数据必须是分组条件或者是组函数

    • order by

      排序

      asc desc

    • 执行的顺序

      from —>where —>group by—>having—>select—>order by

    多表查询

    1. 查询的两张表如果出现同名的列,我们需要将表名标注到列明前面

    2. 如果是非同名的列,表名可加可不加,推荐加上

      • 为了书写方便,可以给表添加别名
      • 一般情况下取首字母,特殊情况下取它所代表的含义
      • 表的别名旨在本次查询中生效
    3. 表与表进行关联查询的时候,如果不添加关联条件,查询的总记录数就是a * b = 笛卡尔积

      a 15 b 10 c 10 —> 1500条

    4. 多表查询的时候必须要加条件

      • 等值
      • 非等值
    1. -- 查询每个员工所在的部门的名称
    2. select ename,deptno from emp;
    3. select deptno,dname from dept;
    4. select emp.ename,emp.deptno,dept.deptno,dept.dname from emp,dept;
    5. -- 等值关联查询
    6. select emp.eanme,emp.deptno,dept.deptno,depy.dname from emp,dept where emp.deptno = dept.deptno;
    7. selept emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;
    8. -- 添加别名
    9. select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

    表与表关联的方式

    1. 因为表的关联条件和业务查询条件放在了一起,为了防止混淆于是提供了下面三种方式

    • 自然连接

      会自动选择列名相同并且类型相同的列

    1. -- 查询薪资大于2000的员工姓名和部门名称
    2. select e.enamem,d.dname from emp e,dept d where e.deptno = d.deptno and e.sal>2000;
    3. -- 自然连接
    4. select e.ename,d.dname from emp e natural join dept d;
    5. select e.ename,d.dname from emp e natural join dept d where e.sal > 2000;
    • using

    不需要MySQL帮我们选择等值连接的列,现在我们指定等值连接的列

    1. -- 查询薪资大于2000的员工姓名和部门名称 using
    2. select e.ename,d.dname from emp e join dept d using(deptno);
    3. select e.ename,d.dname from emp e join dept d using(deptno) where e.sal > 2000;
    • on

    我们可以指定两张表关联的条件,可以非等值的操作

    1. -- 查询薪资大于2000的员工姓名和部门名称 using
    2. select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno)
    3. select e.ename,d.dname from emo e join depy d on(e.deptno = d.deptno) where e.sal>2000;
    4. -- 查询每个员工所对应的薪资等级
    5. select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
    6. select e.ename,s.grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
    • 查询名字中带有A字母的员工姓名,部门名称和薪资等级
    1. SELECT
    2. e.ename,
    3. d.dname,
    4. s.grade
    5. FROM
    6. emp e,
    7. depy d,
    8. salgrade s
    9. WHERE
    10. e.deptno = d.deptno
    11. AND e.sal BETWEEN s.losal AND s.hisal
    12. AND e.ename LIKE '%A%';
    13. ------------------------------------------------------------------------------------
    14. SELECT
    15. e.ename,
    16. d.dname,
    17. s.grade
    18. FROM
    19. emp e
    20. JOIN dept d USING ( deptno )
    21. JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal)
    22. WHERE
    23. e.ename LIKE '%A%';

    表与表的外连接

    1. 当我们对两张表进行关联查询的时候,基于数据的基因导致其中一张表中的数据没办法被完全查询出来

    2. 外连接可以让没查询出来的数据也被显示出来

    3. 因为我们写SQL的时候表总有左右之分,外连接也分

      左外连接:显示左面表所有的数据

      右外连接:显示右面表所有的数据

      1. -- 查询每个部门的人数
      2. select deptno,count(empno) from emp group by deptno;
      3. select * from emp e join dept d using(deptno);
      4. select * from emp e left join dept d using(deptno);
      5. select * from emo e right join deot d using(deptno);
      6. select deptno,count(e.empno) from emp e right join dept d using(deptno) group by deptno;
      7. ----------------------全外连接
      8. SELECT
      9. deptno,
      10. e.ename,
      11. d.dname
      12. FROM
      13. emp e LEFT JOIN dept d USINg( deptno );
      14. ---------------------Oracle的全外连接使用Full Join

      表与表的自连接

    • 我们要查询的两个字段同时处于一张表上,我们只能将一张表当作含有不同意义的两张表去处理

    • 给相同的表取不同的简称(按照所代表的含义去取)

    1. -- 查询每个员工与其直属领导的名字
    2. select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
    3. select e.ename,m.ename from emp e join emp m on(e.mgr = m.ename);

    表与表的子连接

    • 把一个SQL语句的查询结果当成另外一个SQL语句的查询结果

    1. -- 查询公司中薪资最低的员工姓名
    2. select ename,sal from emp where sal = (select min(sal) from emp);
    3. -- 查询公司中谁的薪资高于平均薪资
    4. select ename,sal from emp where sal > (select avg(sal) from emp);
    5. -- 谁的薪资高于20部门员工的薪资
    6. select ename,sal from emp where sal > all(select sal from emp where deptno = 20);
    7. select ename,sal from emp where sal > some(select sal from emp where deptno = 20);
    8. select ename,sal from emp where sal > some(select sal from emp where deptno = 20);

    表与表的伪表查询

    • 如果我们所需要的查询条件需要别的SQL语句来提供

    • 如果只需要一个条件那么可以使用子查询来完成

    • 如果需要多个查询条件,这时就要将所有的查询结果当做伪表进行管理

    • 我们需要把一些含有特殊符号的列名设置别名,然后给伪表设置一个别名(见名知意)

    1. -- 查询高于自己部门平均薪资的员工信息
    2. select deptno,avg(sal) avgsal from emp group by deptno;
    3. SELECT
    4. e.ename,
    5. e.sal,
    6. e.deptno
    7. FROM
    8. emp e,
    9. ( SELECT deptno, avg(sal) avgsal FROM emp GROUP BY deptno ) d
    10. SELECT
    11. e.deptno = d.deptno
    12. AND e.sal > d.avgsal;

  • 相关阅读:
    Jenkins服务器IP更换,Jenkins URL地址更换
    一体化运维:挖矿病毒可能正在蚕食你的IT资源
    STM32使用PB3, PB4引脚的注意事项
    Mqtt入门:在线调试连接阿里云
    微服务如何改变软件开发:实战经验与最佳实践分享
    C++设计模式——Proxy代理模式
    kafka群集部署
    xavier china server
    信也科技发布2023年Q3财报:数字金融服务业务增长稳健,持续拉动实体消费
    性能测试方法全解
  • 原文地址:https://blog.csdn.net/qq_57747969/article/details/133579493