MySQL学习（十一）：聚合函数与分组

1、聚合函数

• 什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

• 聚合函数类型

函数	用法
AVG()	以对数值型数据求平均
SUM()	以对数值型数据求总和
MAX()	以对任意数据类型求最大值
MIN()	以对任意数据类型求最小值
COUNT()	返回表中记录总数，适用于任意数据类型

• 示例

#求平均工资、工资总和、最高工资和最低工资
SELECT AVG(salary), SUM(salary), MAX(salary),MIN(salary)
FROM employees;

• COUNT函数

计算表中有多少条记录

SELECT COUNT(*)
FROM employees

计算指定字段在查询结构中出现的个数。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

计算指定字段出现的个数时，是不计算NULL值的
AVG函数也不计算NULL值

2、GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组

#按照部门ID分组，计算各个部门平均工资
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中,但包含在SELECT 列表中的列必须包含在GROUP BY 子句中！

• 使用多个列分组

#计算相同department_id和job_id的员工的工资总和
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

• GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

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

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足 HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

#查询最高工资大于10000的部门
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

• WHERE和HAVING的对比

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4、SELECT执行顺序

(7) SELECT 
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。