Mysql day03_聚合函数

聚合函数（分组函数）

函数：
函数名部分（5个）
有参数部分
返回值
求最大值：maxz(字段名字)
求最小值：min(字段名)
求平均值：avg(字段名)
求和：sum(字段名)
求总人数：count(*)显示所有条数
count(1)根据第一个字段进行计算，因为第一个字段经常被设计成主键（不允许重复非空）
count(字段名) 忽略空值 count(comm)
练习：求10号部门工资最高和最低的员工的工资

SELECT max(sal) 最高工资,min(sal) 最低工资
FROM emp
WHERE deptno=10;

练习：求10号部门每个月的总工资

SELECT sum(sal)
FROM emp
WHERE deptno=10;

练习：求10号部门的总人数

SELECT count(*)
FROM emp
WHERE deptno=10;

SELECT count(1)
FROM emp
WHERE deptno=10;

可以用来计算带空值的总人数

SELECT count(comm)
FROM emp
WHERE deptno=10;

练习：求10号部门的平均工资

SELECT avg(sal)
FROM emp
WHERE deptno=10;

练习：求20号部门最高工资，最低工资，平均工资，总工资，总人数

SELECT max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,sum(sal) 总工资,count(*) 总人数
FROM emp
WHERE deptno=20;

练习：查询员工最早的入职时间和最晚入职时间

SELECT min(hiredate) 最早的入职时间,max(hiredate) 最晚入职时间
FROM emp;

练习：查询有奖金的总人数

SELECT count(comm) 有奖金的总人数
FROM emp;

练习：查询没有奖金的总人数

SELECT count(*) 没有奖金的总人数
FROM emp
WHERE comm IS NULL OR comm=0;

练习：查询员工表中一共有几种岗位类型。count(distinct job)

SELECT count(DISTINCT job)
FROM emp;

分组group by

练习：求各部门平均工资，显示部门编号，显示部门名称，该部门平均工资

SELECT d.deptno,d.dname,avg(e.sal)
FROM emp e,dept d
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname;

GROUP BY 类别里的有的， SELECT 里可以没有
SELECT 类别里的普通字段， GROUP BY 列表中都要写

练习：查询每个部门每个岗位工资总和

SELECT deptno,job,sum(sal)
FROM emp
GROUP BY deptno,job;

练习：查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT d.deptno,d.dname,count(e.empno),max(e.sal),min(e.sal),sum(e.sal),avg(e.sal)
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname;

练习：查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT d.deptno,d.dname,e.job,count(e.empno),max(e.sal),min(e.sal),sum(e.sal),avg(e.sal)
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname,e.job;

练习：查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT m.empno,m.ename,count(e.empno)
-- 员工表是主表，经理表是副表，以e为准，用左连接实现
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
GROUP BY m.empno;

HAVING 子句

练习：查询部门名称，部门编号，各部门平均工资，按照部门进行分组并且平均工资大于2000

SELECT d.deptno,d.dname,avg(e.sal)
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname
HAVING avg(e.sal)>2000;

练习：查询部门人数大于2的部门编号，部门名称，部门人数。(having count )

SELECT e.deptno,d.dname,count(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY deptno
HAVING count(e.empno)>2;

练习：查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。(having count ,avg order by,asc)

SELECT e.deptno,d.dname,count(e.empno),avg(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY deptno
HAVING count(e.empno)>2 AND avg(e.sal)>2000
ORDER BY count(e.empno) ASC;

查询部门平均工资在2500元以上的部门名称及平均工资。

SELECT d.dname,avg(e.sal)>2500
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno
HAVING avg(e.sal)>2500;

练习：查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

SELECT e.job,d.dname,avg(e.sal)
FROM dept d,emp e
WHERE d.deptno=e.deptno AND e.job NOT LIKE 'SA%'
GROUP BY e.job,d.dname
HAVING avg(e.sal)>2500
ORDER BY avg(e.sal) DESC;

练习：查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

SELECT d.deptno,d.dname,round(min(e.sal),0) 最低工资,round(max(e.sal),0) 最高工资
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY d.deptno,d.dname
HAVING count(e.empno)>2;

练习：查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

SELECT job,sum(sal)
FROM emp
WHERE job!='SALESMAN'
GROUP BY job
HAVING sum(sal)>2500;

练习：显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

SELECT  m.empno,m.ename,min(e.sal)
FROM emp e LEFT JOIN emp m
ON e.mgr=m.empno
GROUP BY m.empno,m.ename
HAVING min(e.sal)>=3000
ORDER BY min(e.sal) DESC;