5.6总结：排序、分组

关键词相关

关键词一共6个，一条查询语句最少要有2个关键词，最多6个关键词，每个关键词只能出现一次

• select 选列
• from 选表
• where 选行
• group by 分组
• having 选分组后的行
• order by 排序

书写顺序：select — from — where — group by — having — order by
执行顺序：from — where — group by — having — select — order by

排序 order by

asc 升序（从小到大），默认，可写可不写；
desc 降序（从大到小），需在列名后标注

select * from emp order by sal --升序
select * from emp order by sal desc --降序

order by 是唯一一个可以使用别名的关键词

select sal 工资, deptno 部门 from emp order by 工资 desc

排序后空值最大（升序排序时空值在最下面）

select * from emp order by comm desc

desc不共用（每列默认升序，需逐个设置）
多个列进行排序时，先全按照左边的列进行排序，在左边列排完的基础上，再排右边列

--先按部门升序排序，再按工资降序排序
select * from emp order by deptno, sal desc
--两者都需降序排序时需要都写出desc
select * from emp order by deptno desc, sal desc

数字指代能用但不常用

select deptno,ename,sal from emp order by 1,3 desc

分组 group by

单独使用时效果相当于去重，一般只搭配聚合函数一起使用

--两者结果一样
select distinct deptno from emp --去重
select deptno from emp group by deptno  --分组

分组后select后面只能跟分了组的列和聚合函数

聚合函数

多行聚合成一行的函数，如：
最大值max() 最小值min() 求和sum() 平均值avg() 计数count()

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

count求的就是结果集里面有多少条有效数据（空值不计），可使用count(1)或者count(*)来求所有数据的计数

--求每个部门的最高工资
select deptno,max(sal) from emp group by deptno
--每个部门有几个工作为MANAGER的员工
select  deptno,count(1) from emp where job='MANAGER' group by deptno
--可进行多项分组
select deptno,job,avg(sal),count(1),sum(sal) from emp group by deptno,job

有分组的查询语句，那么聚合函数则在分组后产生

having

功能等同where：专门用于筛选聚合函数
与where的区别：

• where是用来过滤行的，having是用来过滤分组的
• having一般跟在group by子句后
• where是在分组前进行过滤，而having是在分组后进行过滤
• where后面不可以加聚合函数过滤条件，having可以
• where效率比having高，因此只有出现聚合函数作为过滤条件时用having，其余所有情况都用where

where条件里为什么不能有聚合函数：聚合函数是对行组进行计算，而执行顺序上是先where对单个行进行过滤，才分组，两者有个先后关系。

--求人数大于4的部门的名称和人数
select  deptno,count(1) from emp  group by deptno having count(1)>4 

having 只能筛选聚合函数和分组后的列

--求各部门工作是销售的员工人数
--错误写法：没有对job进行分组，having无法对job进行筛选
select deptno,count(1) from emp group by deptno having job='SALESMAN'
--正确写法：使用where进行筛选
select deptno,count(1) from emp where job='SALESMAN' group by deptno

练习

--1.查询员工姓名、工资，按照工资的正序排列
select enmae,sal from emp order by sal

--2.查询员工信息，先按照部门编号降序排列，再按照工资的正序排列
select * from emp order by deptno desc, sal

--3.查询员工信息，先按照工资降序排列，再按照部门降序排列
select * from emp order by sal desc, deptno desc

--4.求每个部门有多少员工
select deptno, count(1)  from emp group by deptno

--5.求每个部门里的最高工资
select deptno, max(sal) from emp group by deptno

--6.参加每种工作的人数
select job, count(1) from emp group by job

--7.求员工里面的最高工资
select max(sal) from emp

--8.求公司有多少个部门
--使用dept表
select count(1) from dept
--使用emp表进行子查询，缺点：若emp表中没有某部门数据，则会少算这一部门，可能计数不全
select count(1) from (select distinct deptno from emp)

--9.求部门平均工资大于1600的部门编号
select deptno from emp group by deptno having avg(sal)>1600

--10.求部门平均工资大于1600,并且不是20号部门的部门编号
select deptno from emp where deptno!=20 group by deptno having avg(sal)>1600

--11.列出最低薪金大于1500的各种工作
select job from emp group by job having min(sal)>1500

--12.列出在每个部门工作的员工数量、平均工资
select deptno,count(1), avg(sal) from emp group by deptno

--13.列出各个部门的MANAGER（经理）的最低薪金
select deptno,min(sal) from emp where job='MANAGER' group by deptno

--14.查询每个部门每个岗位的工资总和。
select deptno, job, sum(sal) from emp group by deptno, job order by deptno

--15.查询部门人数大于2的部门编号，最低工资、最高工资,部门人数。
select deptno,min(sal),max(sal),count(1) from emp group by deptno having count(1)>2

--16.查询部门平均工资大于2000，且人数大于2的 部门编号、部门人数、部门平均工资 ，并按照部门人数升序排序。
select deptno,count(1),avg(sal) from emp group by deptno having avg(sal)>2000 and count(1)>2 order by count(1)

--17.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序
select job,avg(sal) from emp where job not like 'SA%' group by job having avg(sal)>2500 order by avg(sal) desc

--18.查询岗位不为SALESMAN，工资和大于等于2500的岗位及 每种岗位的工资和
select job,sum(sal) from emp where job!='SALESMAN' group by job having sum(sal)>=2500

--19.写一个查询，显示每个部门最高工资和最低工资的差额
select deptno,max(sal)-min(sal) from emp group by deptno