8.0 聚合函数

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

8.1 常用聚合函数

COUNT: 常数 * 字段

如果要用COUNT(字段)，该选择什么方式

问：在MysQL 中统计数据表的行数，可以使用三种方式： SELECT COUNT（)、SELECT COUNT(1) 和 SELECT COUNT(具体宇段），使用这三者之间的查询效率是怎样的？
答：
在 MysQL InnoDB 存储号1擎中，COUNT(） 和COUNT(1)都是对所有结果进行 COUNT。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计；如果没有 WHERE 子句，则是对数据表的数据行数进行统计。
因此 COUNT（*）和 COUNT(1） 本质上并没有区别，执行的复杂度都是。(N)，也就是采用全表扫描，进行循环＋计数的方式进行统计。
如果是 MysQL MyISAM 存储引擎，统计数据表的行数只需要 o(1）的复杂度，这是因为每张 MyISAM 的数据表都有一个meta 信息存储了row-count 值，而一致性则由表级锁来保证。因为 InnoDB 支持事务，采用行级锁和 MVCC机制，所以无法像 MyISAM 一样，只维护一个row_count 变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计。
需要注意的是，在实际执行中，COUNT(*）和 COUNT(1） 的执行时间可能略有差别，不过你还是可以把它俩的执
行效率看成是相等的。
另外在 InnoDB 引擎中，如果采用 COUNT(*） 和COUNT(1） 来统计数据行数，要尽量采用二级索引。因为主键采的索引是聚族索引，聚筷索引1包含的信息多，明显会大于二级索引（非聚族索引)。对于 COUNT（*）和
COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引1来进行统计。

8.2 GROUP BY 的使用

SELECT中出现的内容一定要出现在GROUP BY中

声明在 FROM、 WHERE后面，ORDER BY 与 LIMIT前面

## 查询各个部门的平均工资
SELECT AVG(salary)
FROM dep
GROUP BY depid;

## 查询
SELECT depid,jobid,AVG(salary)
FROM departments
GROUP BY depid,jobid;
1
2
3
4
5
6
7
8
9

8.3 HAVING 的使用

HAVING必须声明在GROUP BY后面

WHERE 与 HAVING 的对比

• HAVING的范围更大，可以用WHERE的地方一定可以用HAVING
• 有 GROUP BY或聚合函数的地方一定要用HAVING
  • WHERE的执行效率要高于HAVING

### 错误的写法
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary>10000)
GROUP BY department_id;

## 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE
SELECT department-id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000；

# 查询部门id为10，20，30，40 这四个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary)
FROM employees
WHERE depattment_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SQL 92 语法

SELECT ... , ... , ...
FROM ... , ... , ...
WHERE 夺标的链接条件 AND 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... , ...(ASC / DESC)
LIMIT ... , ...
1
2
3
4
5
6
7

SQL 99 语法

SELECT ... , ... , ...(存在聚合函数)
FROM ... (LEFT / RIGHT) JOIN ... ON 多表的链接条件
(LEFT / RIGHT) JOIN ... ON 
WHERE 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
GROUP BY ... , ...(ASC / DESC)
LIMIT ... , ... 
1
2
3
4
5
6
7
8