文章目录
- 1. 聚合函数介绍
- 1.1 什么是聚合函数
- 1.2 常用的聚合函数
- 2. 常用的聚合函数
- 2.1 AVG()
- 2.2 SUM()
- 2.3 MAX()
- 2.4 MIN()
- 2.5 COUNT()
- 2.6 补充
- 3. GROUP BY
- 3.1 分组的基本使用
- 3.2 使用多个列分组
- 3.3 结论
- 3.4 WITH ROLLUP
- 4. HAVING
- 4.1 HAVING 的使用
- 4.2 HAVING 与 WHERE 的对比
- 5. SELECT 的执行过程
- 5.1 SELECT 语句的完整结构
- 5.2 SELECT 的执行顺序
- 6. 聚合函数练习
- 6.1 题目
- 6.2 解答
1. 聚合函数介绍
1.1 什么是聚合函数
聚合(或聚集、分组)函数,是对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值。
聚合函数作用于一组数据,并对一组数据返回一个值。
1.2 常用的聚合函数
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
2. 常用的聚合函数
2.1 AVG()
只适用于数值类型的字段(或变量)
2.2 SUM()
只适用于数值类型的字段(或变量)
2.3 MAX()
MAX()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。
2.4 MIN()
MIN()适用于数值类型、字符串类型、日期时间类型等可以进行大小比较的字段(变量)。
2.5 COUNT()
计算指定字段在查询结果中出现的个数。
COUNT()统计个数,不关注字段的具体取值为多少,只关注某行数据是否具有该字段,该字段是否为空。不为空就加入计数。
COUNT()统计常量
计算表中有多少条记录:
不建议通过统计某个字段的取值的个数来统计表格的行数,因为有些字段的某些行可能取值为空,而COUNT()不会统计空值。
2.6 补充
AVG()、SUM()和COUNT()一样,会过滤空值。
如何需要统计表中的记录数,使用COUNT(
*
)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢?
如果使用的是MyISAM 存储引擎,则三者效率相同,都是O(1)
如果使用的是InnoDB 存储引擎,则三者效率:COUNT(*
) = COUNT(1)> COUNT(字段)
3. GROUP BY
GROUP BY子句可以将表中的数据分成若干组
3.1 分组的基本使用
3.2 使用多个列分组
分组的字段可以调换顺序,结果一致。
最后结果都为(department_id, job_id)一样的为同一组,只是分组的顺序不一样,一个先根据部门分组再根据工作分组,一个根据工作分组再根据部门分组,最终显示的结果一样。
3.3 结论
- 结论1:
SELECT中出现的非组函数的字段必须声明在GROUP BY 中。反之,GROUP BY中声明的字段可以不出现在SELECT中。 - 结论2:
GROUP BY 声明在FROM后面、WHERE后面,ORDER BY 前面、LIMIT前面
3.4 WITH ROLLUP
MySQL中GROUP BY中使用WITH ROLLUP,在所有查询出的分组记录之后增加一条记录,该记录是对所有组在进行一次统计,比如计算每组的平均工资,WITH ROLLUP新加的记录会计算每组合起来总的平均工资,即公司的平均工资。
注意:
当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序,即ROLLUP和ORDER BY是互相排斥的。
MySQL8.0,不报错,WITH ROLLUP新加记录也会参与排序
4. HAVING
4.1 HAVING 的使用
HAVING子句用于过滤分组之后的数据。
1、如果过滤条件中使用了聚合函数,或行已经被分组,则必须使用HAVING来替换WHERE。否则,报错。
2、HAVING 必须声明在 GROUP BY 的后面。
开发中,我们使用HAVING的前提是SQL中使用了GROUP BY。
如果没有使用GROUP BY,使用HAVING,则整个表中的所有记录会被当成一组,然后对这一组记录根据HAVING中的条件进行筛选。
HAVING 不能单独使用,必须要跟 GROUP BY 一起使用。
1、当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING中。
2、当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以。但是,建议大家声明在WHERE中。
上述两种方式,方式一的执行效率高于方式二,即WHERE的效率会高于HAVING。
4.2 HAVING 与 WHERE 的对比
- 从适用范围来讲,HAVING 的使用范围更广。
- 如果过滤条件中没有聚合函数:这种情况下,WHERE的执行效率要高于HAVING。
- WHERE会先过滤掉无用的数据,然后对过滤后的数据进行分组筛选操作,分组筛选操作的数据数小;而HAVING是先对所有的数据进行分组筛选,操作的数据数大,所以WHERE的执行效率要高于HAVING。
5. SELECT 的执行过程
5.1 SELECT 语句的完整结构
5.2 SELECT 的执行顺序
由于GROUP BY分组操作的执行在WHERE之后,所以不能使用WHERE过滤分组后的数据;
由于SELECT的执行时间在WHERE之后,所以在WHERE中不能使用SELECT中字段的别名。
6. 聚合函数练习
6.1 题目
6.2 解答
1.where子句可否使用组函数进行过滤?
2.查询公司员工工资的最大值,最小值,平均值,总和
3.查询各job_id的员工工资的最大值,最小值,平均值,总和
4.查询各个job_id的员工人数
5.查询员工最高工资和最低工资的差距(DIFFERENCE)
6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
8.查询每个工种、每个部门的部门名、工种名和最低工资