mysql 第四章 高级查询

分组函数

–分组函数
MAX:最大值
MIN；最小值
SUM：求和
AVG：平均值
count:统计

---

为什么使用分组函数？
查询所有员工的每个月工资总和，平均工资？
查询工资最高和最低的工资是多少？
查询公司的总人数？
查询有奖金的总人数？

– MAX:最大值
– MIN；最小值
– MIN和MAX函数主要是返回每组的最小值和最大值。
查询工资最高和最低的工资是多少？
select MAX(sal),MIN(sal) from emp; – 数值类型
查询最早和最晚的入职日期是多少？
select MIN(hiredate),MAX(hiredate) from emp;-- 日期类型
查询名字最大和最小的员工姓名
select MAX(ename),MIN(ename) from emp; – 字符类型
– SUM：求和
– AVG：平均值
– SUM函数和AVG函数
– 会忽略null
– SUM和AVG函数都是只能够对数值类型的列或表达式操作。
查询所有员工的每个月工资总和，平均工资？
select avg(sal) from emp;-- 2535
select sum(sal) from emp;-- 38025
select sum(sal)/15 from emp;-- 2535
查询所有员工的奖金总和，平均奖金？
select sum(comm) from emp;-- 3200
select avg(comm) from emp;-- 640 not ok 忽略了null
select sum(comm)/15 from emp;-- 213.333333
– IFNULL 函数可以使分组函数强制包含含有空值的记录
select avg(ifnull(comm,0)) from emp;-- 213.333333 ok
– count:统计
– 先查 再统计出现的非空行数
– 除了COUNT（*）,count(1)之外，其它所有分组函数都会忽略列中的空值，然后再进行计算。

select * from emp;
查询公司的总人数？
select ename from emp;
select count(ename) from emp;-- 15 效率较高
select * from emp;
select count(*) from emp; – 15 效率较低
select comm from emp;
select count(comm) from emp;-- 5
– 1只是一个占位符，该表中有多少行就会有多少个1
select 1 from emp;
select count(1) from emp;-- 15 效率最高

查询部门30有多少个员工
select * from emp where deptno=30;
select count() from emp where deptno=30;-- 6
查询部门30有多少个员工领取奖金
select count() from emp where deptno=30 and comm is not null;-- 4

– 组函数中DISTINCT
– DISTINCT会消除重复记录后再使用组函数
查询有员工的部门数量。
select DISTINCT deptno from emp;
select count(DISTINCT deptno) from emp where deptno is not null;-- 3

– group by 子句 分组
– 通过 GROUP BY 子句可将表中满足WHERE条件的记录按照指定的列划分成若干个小组
– 其中GROUP BY子句指定要分组的列
– from–where–group by – select–order by
求每个部门平均工资，按照部门进行分组
select avg(ifnull(sal,0)),deptno from emp where deptno is not null group by deptno
select * from emp;
– GROUP BY 子句的使用规则
– 在SELECT列表中除了分组函数那些项，所有列都必须包含在GROUP BY 子句中。
– 在group by 子句中出行的列，不一定在select 列表中出现
查询每个岗位的平均工资和工资总和,和岗位
select avg(ifnull(sal,0)),sum(sal),job from emp group by job
select avg(ifnull(sal,0)),sum(sal) from emp group by job
– 按照多列进行分组
查询每个部门每个岗位的平均工资和工资总和
select avg(ifnull(sal,0)),sum(sal),deptno,job from emp group by deptno,job;

查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select d.deptno,dname,count(1),max(sal),min(sal),sum(sal),avg(ifnull(sal,0))
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno ,dname

查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。
select e.deptno,dname,job,count(1),max(sal),min(sal),sum(sal),avg(ifnull(sal,0))
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,dname,job
查询每个经理所管理的人数，经理编号，经理姓名，要求包括不是经理的人员信息
select w.mgr,m.ename,count(w.ename)
from emp w right join emp m on(w.mgr=m.empno)
group by w.mgr,m.ename

– having 子句：对分组之后的结果进行再一次的判断筛选
查询每个部门最高工资大于2900的部门编号，最高工资
select max(sal),deptno from emp where deptno is not null group by deptno having 2900<max(sal)
查询每种工作的平均工资大于500的工作和平均工资
select avg(ifnull(sal,0)),job from emp group by job having 500<avg(ifnull(sal,0))

– sql的运行顺序
– from:先确定数据的来源于哪些表
– where：筛选满足条件的数据行
– group by：对where子句筛选之后的结果进行分组
– having：对分了组之后的数据进行再一次筛选
– select:获取查询结果
– order by：对得到的结果集数据进行排序

查询每个部门的平均工资大于2900，部门名称，部门编号，平均工资 结果按照平均工资降序排序
select dname,e.deptno,avg(ifnull(sal,0)) avgsal
from emp e,dept d
where e.deptno=d.deptno
group by dname,e.deptno
having 1000<avg(ifnull(sal,0))
order by avgsal desc