MySQL学习（三）—— 数据库的高级查询（聚合函数、分组查询、HAVING子句）

数据操作语言：聚合函数

什么是聚合函数？

• 聚合函数可以对数据求和、求最大值和最小值、求平均值等等

例：求平均月收入

SELECT AVG(sal + IFNULL(comm,0)) FROM t_emp;

####### SUM函数
SUM函数用于求和，只能用于数字类型，字符类型的统计结果为0，日期类型统计结果是毫秒数相加

SELECT SUM(ename) FROM t_emp;

SELECT SUM(sal) FROM t_emp
WHERE deptno IN (10,20);

MAX函数

MAX函数用于获得非空值得最大值

SELECT MAX(comm) FROM t_emp;

// 查询10和20部门中，月收入最高的员工
SELECT MAX(sal+IFNULL(comm,0)) FROM t_emp
WHERE deptno IN(10,20)

// 查询员工名字最长的是几个字符
SELECT MAX(LENGTH(ename)) FROM t_emp;

MIN函数

MIN函数用于获得非空值的最小值

AVG函数

AVG函数用于获得非空值的平均值，非数字数据统计结果为0

SELECT AVG(sal + IFNULL(comm,0)) FROM t_emp;

COUNT函数

COUNT(*)函数用于获得包含空值的记录数，COUNT(列名)用于获得包含非空值的记录数

SELECT COUNT(*) FROM t_emp;
SELECT COUNT(comm) FROM t_emp;

// 查询10和20部门中，底薪超过2000并且工龄超过15年的员工人数
SELECT COUNT(*) FROM t_emp
WHERE deptno IN(10,20) AND sal>=2000
AND DATEDIFF(NOW(),hiredate)/365>=15

WHERE子句不能出现聚合函数

数据操作语言：分组查询 GROUP BY

• 默认情况下，汇总函数是对全表范围内的数据做统计
• GROUP BY子句的作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对每个小区域分别进行数据汇总处理

// ROUND 四舍五入取整
SELECT deptno,ROUND(AVG(sal)) FROM t_emp
GROUP BY deptno

逐级分组

• 数据库支持多列分组条件，执行的时候逐级分组
• 查询每个部门里，每种职位的人员数量和平均底薪

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp
GROUP BY deptno,job
ORDER BY deptno;

对SELECT子句的要求

查询语句中如果含有GROUP BY子句，那么SELECT子句中的内容就必须要遵守规定：SELECT子句中可以包括聚合函数，或者GROUP BY 子句的分组列，其余内容均不可以出现在SELECT子句中

SELECT deptno,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno;

// sal错误
SELECT deptno,COUNT(*),AVG(sal),sal
FROM t_emp GROUP BY deptno;

对分组结果集再次做汇总计算 WITH ROLLUP

SELECT deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP;

GROUP_CONCAT 函数

GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串

SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

各种子句的执行顺序

FROM => WHERE => GROUP BY => SELECT => ORDER BY => LIMIT

数据操作语言： HAVING子句

分组查询遇到的困难
查询部门平均底薪超过2000的部门编号？

SELECT deptno
FROM t_emp 
WHERE AVG(sal)>=2000  // 这里错误
GROUP BY deptno;

SELECT deptno
FROM t_emp 
GROUP BY deptno HAVING AVG(sal)>=2000;

// 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno FROM t_emp 
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2
ORDER BY deptno ASC;

// 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno FROM t_emp 
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2 AND sal>=AVG(sal) // 错误
ORDER BY deptno ASC;

// 查询每个部门中，1982年以后入职的员工超过2个人的部门编号
SELECT deptno FROM t_emp 
WHERE hiredate>="1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2 AND AVG(sal)>=2000
ORDER BY deptno ASC;

HAVING 子句的特殊用法

按照数字1分组，MySQL会依据SELECT子句中的列进行分组，HAVING子句也可以正常使用

SELECT deptno,COUNT(*) FROM t_emp
GROUP BY 1;

SELECT deptno,COUNT(*) FROM t_emp
GROUP BY 1 HAVING deptno IN (10，20);