MySQL基础篇——第08章 聚合(分组)函数
1. 常用聚合(分组)函数——Group Function
- 聚合(分组)函数,是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值
- 分组函数在
SELECT
或HAVING
子句中使用,表示对每一组数据的操作。若仅在SELECT
中使用,且未使用GROUP BY
分组,则表示对表中所有数据的操作 - 常用的聚合函数:
AVG()
/SUM()
/MIN()
/MAX()
/COUNT()
- 聚合函数不能嵌套调用。比如不能出现类似
AVG(SUM(字段名称))
形式的调用
1.1 AVG()
和 SUM()
AVG()
和SUM()
对当前分组中所有记录的某个字段求平均(或求和)AVG()
和SUM()
函数只适用于数值类型的字段(或变量),不适用于字符串类型或日期时间类型的字段(或变量)AVG()
和SUM()
在计算过程中,自动把当前字段中的NULL值过滤掉了
SELECT AVG(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
1.2 MIN()
和 MAX()
MIN()
和MAX()
返回当前分组中,所有记录某个字段的最小值(或最大值)MIN()
和MAX()
函数适用于任意数据类型的字段(或变量)MIN()
和MAX()
在计算过程中,自动把当前字段中的NULL值过滤掉了
SELECT MIN(salary), MAX(salary), MIN(last_name), MAX(last_name), MIN(hire_date), MAX(hire_date)
FROM employees;
1.3 COUNT()
-
COUNT(*)
/COUNT(常数)
返回当前分组的记录(行)总数,适用于任意数据类型**
SELECT COUNT(*), COUNT(1)
FROM employees
WHERE department_id = 50;
COUNT(字段)
返回当前分组中,该字段(列)不为NULL的记录(行)总数
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;
- 由于
AVG()
和SUM()
在计算过程中,自动过滤NULL值,故满足AVG(字段) = SUM(字段) / COUNT(字段)
# 二者是等价的
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct)
FROM employees;
- 举例:查询公司所有员工平均奖金率
# 错误写法:
SELECT AVG(commission_pct)
FROM employees;
# 正确写法:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct, 0))
FROM employees;
# 或
SELECT AVG(IFNULL(commission_pct, 0))
FROM employees;
- 统计当前分组的记录数,使用
COUNT(*)
/COUNT(常数)
/COUNT(字段)
哪个效率更高?
- 如果使用的是MyISAM存储引擎,则三者效率相同,都是
o(1)
- 如果使用的是Innodb存储引擎,则三者效率:
COUNT(*) = COUNT(常数) > COUNT(字段)
2. GROUP BY
分组操作
2.1 基本使用
可以使用 GROUP BY
子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
- 使用多个字段(列)进行分组时,
GROUP BY
子句中字段声明的先后顺序对查询的结果没有影响SELECT
子句中声明的所有未包含在聚合(分组)函数中的字段,都必须声明在GROUP BY
子句中
- 在查询的结果集中,每组的某个字段理论上只能取一个值,代表该组这一字段的值。如果某个字段只在
SELECT
子句中声明,而未在GROUP BY
中声明,则无法确定该字段的取值
- 反之,
GROUP BY
子句中声明的字段可以不出现在SELECT
子句中,即可以不在结果集中显示- 声明顺序:
SELECT
—>FROM
—> (JOIN …… ON
) —>WHERE
—>GROUP BY
—>HAVING
—>ORDER BY
—>LIMIT
- 举例:查询
employees
表中各部门的平均工资
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-luhF8eYR-1652660358875)(MySQL基础篇——第08章 聚合函数.assets/image-20220422145840330.png)]
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id; # 按照`department_id`字段对表中所有记录分组
2.2 使用多个列分组
- 举例:查询
employees
表中各部门各工种的平均工资(按照department_id
和job_id
进行分组)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7CSdbyTi-1652660358876)(MySQL基础篇——第08章 聚合函数.assets/image-20220422151458007.png)]
# 方式一:
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;
# 方式二:
SELECT job_id, department_id, SUM(salary)
FROM employees
GROUP BY job_id, department_id;
- 先按照
department_id
分组或先按照job_id
分组,二者从本质上是一样的。即使用多个字段分组,字段的先后顺序没有影响
2.3 GROUP BY
中使用 WITH ROLLUP
使用 WITH ROLLUP
关键字,在所有查询出的分组记录之后增加一条记录,该记录在分组统计数据基础上再进行相同的统计(SUM
, AVG
, COUNT
…)
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
注意:当使用
ROLLUP
时,不能同时使用ORDER BY
子句进行结果排序,即ROLLUP
和ORDER BY
是互相排斥的
3. HAVING
对分组进行过滤
3.1 基本使用
可以使用 HAVING
子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
- 如果过滤条件中使用了聚合(分组)函数,则必须使用
HAVING
来替换WHERE
- 非法使用聚合函数:不能在
WHERE
子句中使用聚合函数
HAVING
不能单独使用,必须要跟GROUP BY
一起使用,且必须紧跟GROUP BY
子句之后HAVING
的使用场景是表中的记录(行)已经使用GROUP BY
进行了分组操作- 满足
HAVING
子句中条件的分组将显示在结果集中
- 举例1:查询部门最高工资比1000高的部门
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XfThlft1-1652660358877)(MySQL基础篇——第08章 聚合函数.assets/image-20220422210804356.png)]
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
- 举例2:查询部分id为10,20,30,40这四个部门中最高工资比10000高的部门
# 方式一:将普通条件写在WHERE子句中,将包含分组函数的条件写在HAVING子句中(推荐,执行效率高)
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
# 方式二:将普通条件和包含分组函数的条件都写在HAVING子句中
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20, 30, 40)
AND MAX(salary) > 10000;
3.2 WHERE
和 HAVING
的对比
- 区别1:
WHERE
可以直接使用表中的字段作为筛选条件,但不能使用分组函数作为筛选条件;HAVING
必须要与GROUP BY
配合使用,可以把分组函数和分组字段作为筛选条件
- 在查询语法结构中,
WHERE
在GROUP BY
之前执行,即在分组之前就执行了,所以无法对分组结果进行筛选。HAVING
在GROUP BY
之后执行,故可以使用分组函数和分组字段,对分组的结果集进行筛选 -
WHERE
排除的记录不再包括在分组中
- 区别2:如果需要通过连接从关联表中获取数据,
WHERE
是先筛选后连接,而HAVING
是先连接后筛选
- 在关联查询中,
WHERE
比HAVING
更高效。因为WHERE
可以先筛选,用一个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较高 -
HAVING
则使用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低
优点 | 缺点 | |
| 先筛选数据再关联,执行效率高 | 不能使用分组函数进行筛选 |
| 可以使用分组函数,对分组的结果集进行筛选 | 在最后的结果集中进行筛选,执行效率较低 |
- 开发中的选择:
-
WHERE
和HAVING
也不是互相排斥的,我们可以在一个查询里面同时使用WHERE
和HAVING
。包含分组统计函数的条件用HAVING
,普通条件用WHERE
。这样,我们就既利用了WHERE
条件的高效快速,又发挥了HAVING
可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大的差别
4. 总结:SELECT
查询语句
4.1 SELECT
语句的完整结构
# 方式一:SQL92语法
SELECT [DISTINCT] ..., ...., ...
FROM ..., ..., ....
WHERE 多表的连接条件 AND/OR 不包含分组函数的过滤条件
GROUP BY ..., ...
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ..., ...
# 方式二:SQL99语法
SELECT [DISTINCT] ..., ...., ...
FROM ... (LEFT/RIGHT)JOIN ... ON 多表的连接条件
(LEFT/RIGHT)JOIN ... ON ...
WHERE 不包含分组函数的过滤条件 AND/OR 不包含分组函数的过滤条件
GROUP BY ..., ...
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ..., ...
# 其中:
#(1)FROM:从哪些表中筛选
#(2)ON:关联多表查询时,去除笛卡尔积
#(3)WHERE:从表中筛选的条件
#(4)GROUP BY:分组依据
#(5)HAVING:在分组统计结果中再次筛选
#(6)ORDER BY:排序
#(7)LIMIT:分页
4.2 SELECT
语句底层的执行顺序
FROM
->ON
->(LEFT/RIGHT) JOIN
->WHERE
->GROUP BY
->HAVING
->SELECT
->DISTINCT
->ORDER BY
->LIMIT
- 首先执行
FROM
子句。如果是多表联查,还会经历以下步骤:
- 通过
JOIN
求笛卡尔积,得到虚拟表 vt1-1
- 通过
ON
的连接条件对虚拟表 vt1-1
进行筛选,得到虚拟表 vt1-2
- 如果使用左外连接或右外连接,则在
虚拟表 vt1-2
的基础上添加外部行,得到虚拟表 vt1-3
- 如果操作的是两张以上的表,即有多个
JOIN ... ON
子句,则重复上述步骤。最终得到虚拟表 vt1
- 执行
WHERE
子句。根据不包含分组函数的条件过滤虚拟表 vt1
的记录,得到虚拟表 vt2
。此时的表包含所有字段(列) - 执行
GROUP BY
子句。根据分组字段对虚拟表 vt2
进行分组,得到虚拟表 vt3
- 执行
HAVING
子句。根据包含分组函数的条件对虚拟表 vt3
中的分组进行过滤,保留满足条件的组,得到虚拟表 vt4
- 执行
SELECT
子句。选择指定的字段(列),并给字段取别名;然后执行DISTINCT
,过滤重复记录(行),得到虚拟表 vt5
- 执行
ORDER BY
子句。按照指定字段对虚拟表 vt5
进行排序,得到虚拟表 vt6
- 执行
LIMIT
子句。根据指定的位置偏移量和条目数,取出虚拟表 vt6
中的指定记录(行),得到最终的结果虚拟表 vt7
5. 课后练习
# 1.where子句可否使用组函数进行过滤?
# 不可以!`WHERE`在`GROUP BY`之前执行,即在分组之前就执行了,所以无法使用组函数对分组结果进行过滤
# 2.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary) "max_sal", MIN(salary) "min_sal", AVG(salary) "avg_sal", SUM(salary) "sum_sal"
FROM employees;
# 3.查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT job_id, MAX(salary) "max_sal", MIN(salary) "min_sal", AVG(salary) "avg_sal", SUM(salary) "sum_sal"
FROM employees
GROUP BY job_id;
# 4.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
# 5.查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;
# 6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT manager_id, MIN(salary) "min_sal"
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
# 7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
SELECT d.`department_name`, d.`location_id`, COUNT(e.`employee_id`), AVG(e.`salary`) "avg_sal"
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_name`, d.`location_id` # SELECT子句中声明的所有未包含在分组函数中的字段,必须声明在GROUP BY中
ORDER BY avg_sal DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.`department_name`, e.`job_id`, MIN(e.`salary`) "min_sal"
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY d.`department_name`, e.`job_id`;
name, d.
location_id` # SELECT子句中声明的所有未包含在分组函数中的字段,必须声明在GROUP BY中
ORDER BY avg_sal DESC;
8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name
, e.job_id
, MIN(e.salary
) “min_sal”
FROM departments d LEFT JOIN employees e
ON d.department_id
= e.department_id
GROUP BY d.department_name
, e.job_id
;