8.0 聚合函数
我们上一章讲到了 SQL 单行函数。实际上 SQL 函数还有一类,叫做聚合(或緊集、分组) 函数,它是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值。
8.1 常用聚合函数
函数 | 作用 | 备注 |
---|---|---|
AVG( ) | 求平均值 | AVG = SUM / COUNT |
SUM( ) | 求和 | |
MAX( ) | 求最大值 | 数字、字符串、日期时间类型 |
MIN( ) | 求最小值 | 数字、字符串、日期时间类型 |
COUNT( ) | 统计个数 | 不统计NULL |
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;
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;
SQL 92 语法
SELECT ... , ... , ... FROM ... , ... , ... WHERE 夺标的链接条件 AND 不包含聚合函数的过滤条件 GROUP BY ... , ... HAVING 包含聚合函数的过滤条件 ORDER BY ... , ...(ASC / DESC) LIMIT ... , ...
SQL 99 语法
SELECT ... , ... , ...(存在聚合函数) FROM ... (LEFT / RIGHT) JOIN ... ON 多表的链接条件 (LEFT / RIGHT) JOIN ... ON WHERE 不包含聚合函数的过滤条件 GROUP BY ... , ... HAVING 包含聚合函数的过滤条件 GROUP BY ... , ...(ASC / DESC) LIMIT ... , ...
![](https://files.catbox.moe/7htxk7.png)
🎨 执行顺序
![](https://files.catbox.moe/2eu5d4.png)
🎨 执行顺序