5、数据的分组

一、分组函数（多行处理函数）

 1、输入多行，最终输出一行

​ 2、自动忽略null,故不需要提前对NULL进行处理

​ 3、不能直接跟在where子句后面

• 主要的多行处理函数

  • count 计数

    • 取的所有的员工数

      select count(*) from emp;

    • 取得津贴不为null员工数

       select count(comm) from emp;

    • 取得工作岗位的个数

      select count(distinct job ) from emp;

  • sum 求和

    • Sum 可以取得某一个列的和，null 会被忽略
    • 取得津贴的合计

      select sum(comm) from emp;

    • 取得薪水的合计

      select sum(sal+IFNULL(comm, 0)) from emp;

  • avg 平均值
    • 取得平均薪水

       select avg(sal) from emp;

  • max 最大值
    • 取得最高薪水

      select max(sal) from emp;

    • 取得最晚入职得员工

      select max(str_to_date (hiredate, '%Y-%m-%d')) from emp;

  • min 最小值
    • 取得最低薪水

      select min(sal) from emp;

    • 取得最早入职得员工（可以不使用 str_to_date 转换）

      select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;

  • 组合聚合函数
    • 可以将这些聚合函数都放到 select 中一起使用

      select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

二、分组查询

• 2.1、group by

在 SQL 语句中若有group by 语句，那在 select 语句后面只能跟 分组函数+ 参与分组的字段

• 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

  select job, sum(sal) from emp group by job;

• 找出“每个部门，不同工作岗位”的最高薪资
  技巧：两个字段联合成1个字段看。（两个字段联合分组）

  selectd eptno, job, max(sal) from emp group by deptno, job;

• 2.2、having

1、如果想对分组数据再进行过滤需要使用 having 子句

2、原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。

3、having 的过滤是专门对分组之后的数据进行过滤的。

• 取得每个岗位的平均工资大于2000

  select job, avg(sal) from emp group by job having avg(sal) >2000;

• 找出每个部门平均薪资，要求显示平均薪资高于2500的

  select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;

  分组函数的执行顺序：

  • 1、根据条件查询数据
  • 2、分组
  • 3、采用 having 过滤，取得正确的数据

• 2.3、select语句总结

  • 完整的select语句格式如下：

    • select 字段
    • from 表名
    • where …….
    • group by ……..
    • having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
    • order by ……..
    • limit.......

  • 上述语句执行顺序如下

    • 1.from 数据从而而来
    • 2.where 筛选部分数据
    • 3.group by 将数据分组
    • 4.having 再次筛选部分数据
    • 5.select 选定显示的字段
    • 6.order by 使字段按升序或降序排列
    • 7.limit 限制显示的结果数

三、连接查询

连接查询时若不限定条件，则会导致笛卡尔积现象，也即计算机处理数据效率降低，因此应加上条件

• 3.1 、数据去重【distinct】

// distinct只能出现在所有字段的最前方

// distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重

​ // distinct函数可以嵌套

1| select distinct job,deptno from emp;
2| select distinct e.* from emp e;

对整张emp表联合字段进行去重！

• 3.2、内连接（A和B连接，AB两张表没有主次关系，平等的）

//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接inner）

• 3.2.1、等值连接
  字段值匹配/精准匹配
  • 查询每个员工所在部门名称，显示员工名和部门名

    select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

• 3.2.2、非等值连接
  范围匹配/模糊匹配
  • 找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

    select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

• 3.2.3、自连接

自连接就是和自己连接，即一表两用或多用

技巧：一张表看做两张表,分别用a,b表示

• 查询员工的上级领导，要求显示员工名和对应的领导名

  select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;

• 3.3、外连接（在外连接当中，两张表连接，产生了主次关系）

  • 3.3.1、左外连接
    /左表当成主表，右表是次表，主表信息全部遍历匹配，次表则不一定

    select e.ename,d.dname from dept d left join emp e on e.deptno = d.deptno;

  • 3.3.2、右外连接
    //右表当成主表，左表是次表，主表信息全部遍历匹配，次表则不一定

    select e.ename,d.dname from dept d right join emp e on e.deptno = d.deptno;

• 3.4、综合案例

  • 查询每个员工的上级领导，要求显示所有员工的名字和领导名

    select a.ename as '员工名', b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;

  • 三张以上的表怎么连接？
    一条SQL中内连接和外连接可以混合。都可以出现！

    • select ... from a
    • join b on
    • a和b的连接条件
    • join c on
    • a和c的连接条件
    • right join on
    • a和d的连接条件

  • 找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

    select e.ename,e.sal,d.dname,s.grade,l.ename from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp l on e.mgr = l.empno;

四、子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。（简称俄罗斯套娃doge）

• 4.1、在where语句中使用
  // 将子查询的结果作为新条件

  • 找出比最低工资高的员工姓名和工资

    select ename,sal from emp where sal > (select min(sal) from emp);
    //(select min(sal) from emp)等价于800

• 4.2、在from语句中使用
  from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

  • 找出每个岗位的平均工资的薪资等级

    select t.avgsal,s.grade from 
    (select job,avg(sal) as avgsal from emp group by job) t 
    join salgrade s 
    on t.avgsal between s.losal and s.hisal;

• 4.3、在select语句中使用

这个内容不需要掌握，了解即可！！！
注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多于1条，就报错了！太鸡肋了！！

• 找出每个员工的部门名称，要求显示员工名，部门名

  1| select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno)
   as dname from emp e;//返回1条结果则成功运行
  2| selecte .ename,e.deptno,(select dname from dept) as dname from emp e;
  ERROR 1242 (21000): Subquery returns more than 1 row //返回多条结果则报错

五、union

无论表的内外连接，使用union的效率都要高一些。
对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，进行的是积运算！！
但是union可以减少匹配的次数。union进行的是和运算！！
在减少匹配次数的情况下，还可以完成两个结果集的拼接。
union把乘法变成了加法运算！！！

• 查询工作岗位是MANAGER和SALESMAN的员工

  select ename,job from emp where job = 'MANAGER'
  union
  select ename,job from emp where job = 'SALESMAN';

• 使用union的注意事项

  • union在进行结果集合并的时候，要求两个结果集的列数相同

六、Iimit

注意：mysql当中limit在order by之后执行！！！

• 6.1、作用

将查询结果集的一部分取出来。通常使用在分页查询当中；

如百度默认：一页显示10条记录；

分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差；

所以使用分页的功能，使其可以一页一页翻页看。

• 6.2、用法

  • 完整用法：limit <startIndex, length>
    //startIndex是起始下标，length是长度，起始下标从0开始
  • 缺省用法：limit 5; 这是取前5.

• 6.3、分页

  • 每页显示3条记录
    • 第1页：limit 0,3 [0 1 2]
    • 第2页：limit 3,3 [3 4 5]
    • 第3页：limit 6,3 [6 7 8]
    • 第4页：limit 9,3 [9 10 11]
  • 记公式
    • limit (pageNo-1)*pageSize , pageSize

---