第八章：聚合函数

我们上一章讲了SQL单行单数。实际上SQL函数还有一类，叫做聚合(或聚集、分组)函数，它是对一组数据进行汇总好函数，输入的是一组数据的集合，输出的是单个值。

8.1：聚合函数

AVG和SUM函数
- AVG：求平均数
- SUM：求和
- 可以对数值型数据使用AVG和SUM函数。
```
SELECT AVG(salary), SUM(salary), AVG(salary) * 107
FROM employees;
```

MIN和MAX函数

MIN：求最小值
MAX：求最大值

可以对任意数据类型的数据使用MIN和MAX函数

SELECT MAX(salary), MIN(salary)
FROM employees;SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date)
FROM employees;

COUNT函数
- COUNT：返回个数
- COUNT(*)：返回表中记录总数，适用于任意数据类型
- COUNT(expr)：返回**expr不为空**的记录总数
```
SELECT COUNT(employee_id), COUNT(salary), COUNT(2 * salary), COUNT(1), COUNT(2), COUNT(*)
FROM employees;
```
- 如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段)哪个效率更高呢？
  1. 如果使用的是MyISAM存储引擎，则三者效率相同，都是o(1)。
  2. 如果使用的是InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(字段)

8.2：GROUP BY

基本使用
- 使用GROUP BY子句将表中的数据分成若干组
```
#需求：查询各个部门的平均工资，最高工资
SELECT department_id, AVG(salary), MAX(salary)
FROM employees
GROUP BY department_id;
```
- 说明
  1. SELECT中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
  2. GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面。

使用多个列分组

SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
```
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
```
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

8.3：HAVING

基本使用
```
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
```
说明：
- 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
- HAVING必须声明在GROUP BY后面。

WHERE和HAVING的对比

区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件，HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。

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

开发中的选择

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

8.4：SELECT的执行过程

查询的结构

#方式1：sql92
SELECT ...., ...., ...
FROM ...., ...., ...
WHERE 多表的连接条件 AND 不包含组函数的过滤条件
GROUP BY ...., ....
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...., ...#方式2：sql99
SELECT ...., ...., ...
FROM .... JOIN .... ON 多表的连接条件 JOIN .... ON ...
WHERE 不包含组函数的过滤条件 
GROUP BY ...., ...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...., ...

SELECT执行顺序

关键字的顺序是不能颠倒的

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

SELECT语句的执行顺序(在MySQL和Oracle中，SELECT执行顺序基本相同)

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SQL的执行原理

SELECT是先执行FROM这一步。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
1. 首先先通过CROSS JOIN求笛卡尔积，相当于得到虚拟表vt(virtual table) 1-1。
2. 通过ON进行筛选，在虚拟表vt1-1的基础上进行筛选，得到虚拟表vt1-2。
3. 添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表vt1-2的基础上增加外部行，得到虚拟表vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，知道所有表都被处理完为止。这个过程得到是我们的原始数据。
1. 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段。在这个阶段中，会根据vt1表的结果进行筛选过滤，得到虚拟表vt2。
2. 然后进行第三步和第四步，也就是GROUP和HAVING阶段。在这个阶段中，实际上是在虚拟表vt2的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4。
3. 当完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段。
4. 首先在SELECT阶段会提取想要的字段，然后在DISTINCT阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。
5. 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段，得到虚拟表vt6。
6. 最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7。
当然我们在写SELECT语句的时候，不一定存在所有关键字，相应的阶段就会省略。