聚合函数
聚合 / 聚集 / 分组函数
1、对一组数据进行汇总的函数
2、输入的是一组数据的集合,输出的是单个值
3、类型
(1)AVG():返回列的平均值
(2)SUM():返回指定列的数值总和
(3)MAX():返回列的最大值
(4)MIN():返回列的最小值
(5)COUNT() :返回指定列的行数(不含 null),COUNT(*) 返回总行数(含 null)
4、类型要求
(1)可以对数值型数据使用 AVG 和 SUM 函数
(2)可以对任意数据类型的数据使用 MIN 和 MAX 函数
5、不能在 WHERE 子句中使用聚合函数
6、聚合函数不能直接嵌套使用
COUNT
1、COUNT(*):返回表中记录总数,适用于任意数据类型,不排除 NULL
2、COUNT(expr):返回 expr 不为空的记录总数,排除 NULL
3、COUNT(*),COUNT(1),COUNT(列名) 对比
(1)对于 MyISAM 引擎的表,没有区别,该引擎内部有一计数器在维护行数
(2)Innodb 引擎的表用 COUNT(*) <=> COUNT(1),直接读行数,不排除 NULL,复杂度为 O(n),但好于具体的 COUNT(列名)
4、不使用 COUNT(列名) 替代 COUNT(*)
(1)COUNT(*) 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关
(2)COUNT(*) 会统计值为 NULL 的行,而 COUNT(列名) 不会统计此列为 NULL 值的行
GROUP BY
1、将表中的数据分成若干组,可以对多个列进行分组
2、在 SELECT 列表中,所有未包含在组函数中的列,都应该包含在 GROUP BY 子句中
3、包含在 GROUP BY 子句中的列,不必包含在 SELECT 列表中
4、GROUP BY ... WITH ROLLUP
(1)使用 WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量
(2)使用ROLLUP时,不能同时使用 ORDER BY 子句进行结果排序,即 ROLLUP 和 ORDER BY 是互相排斥的
5、一般与其他聚合函数搭配使用,MySQL 5.7 单独使用 GROUP BY 不报错,但只返回每个分组的第一行
HAVING
1、过滤分组
(1)行已经被分组
(2)使用了聚合函数
(3)满足 HAVING 子句中条件的分组将被显示
(4)HAVING 不能单独使用,必须与 GROUP BY 一起使用
2、WHERE、HAVING 对比
优点 | 缺点 | |
WHERE | 先筛选数据再关联,执行效率高 | 不能使用分组中的计算函数进行筛选 |
HAVING | 可以使用分组中的计算函数 | 在最后的结果集中进行筛选,执行效率较低 |
(1)WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件
(2)HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件
(3)在需要对数据进行分组统计的时,HAVING 可以完成 WHERE 不能完成的任务,因为在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选,HAVING 在 GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选
(4)WHERE 排除的记录不再包括在分组中
(5)在关联查询中,WHERE 比 HAVING 更高效,WHERE 是先筛选后连接,用一个筛选后的较小数据集,和关联表进行连接,占用资源较少,执行效率较高;而 HAVING 是先连接后筛选,需要先把结果集准备好,即用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,占用资源较多,执行效率较低
3、SELECT、GROUP BY、HAVING 关系
(1)SELECT 所出现的列名(除聚合函数外),必须包含在 GROUP BY 中;另一种说法,SELECT 的字段要么出现在 GROUP BY 中,作为分组的依据,要么包含在聚合函数中
(2)HAVING 的字段、聚合函数,必须包含在 SELECT 的字段、聚合函数
SELECT 执行过程
1、书写顺序:SELECT -> FROM -> JOIN -> ON -> WHERE -> GROUP BY -> HAVING -> UNION -> ORDER BY -> LIMIT
SELECT <字段名>
FROM <表名>
JOIN <表名>
ON <连接条件>
WHERE <筛选条件>
GROUP BY <字段名>
HAVING <筛选条件>
UNION [ALL]
ORDER BY <字段名>
LIMIT <限制行数>;
2、执行顺序
(1)FROM -> ON -> JOIN -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> UNION -> DISTINCT -> ORDER BY -> LIMIT
(2)在 MySQL 和 Oracle 中,SELECT 执行顺序基本相同
(3)在 SELECT 语句执行这些步骤时,每个步骤都会产生一个虚拟表,然后将这个虚拟表传入下一个步骤中作为输入,这些步骤隐含在 SQL 的执行过程中,对于使用者不可见
3、步骤
(1)FORM:选择 FROM 后面的表,产生虚拟表 1
(2)ON:ON 是 JOIN 的连接条件,符合连接条件的行会被记录在虚拟表 2 中
(3)JOIN:如果指定 LEFT JOIN,那么保留表中未匹配的行就会作为外部行添加到虚拟表 2 中,产生虚拟表 3;如果有多个 JOIN 链接,会重复执行步骤 1 ~ 3,直到处理完所有表
(4)WHERE:对虚拟表 3 进行 WHERE 条件过滤,符合条件的记录会被插入到虚拟表 4 中
(5)GROUP BY:根据 GROUP BY 子句中的列,对虚拟表 4 中的记录进行分组操作,产生虚拟表 5
(6)HAVING:对虚拟表 5 进行 HAVING 过滤,符合条件的记录会被插入到虚拟表 6 中。
(7)SELECT:选择指定的列,插入到虚拟表 7 中
(9)UNION:UNION 连接的两个 SELECT 查询语句,重复执行步骤 1 ~ 7,产生两个虚拟表 7,UNION会将这些记录合并到虚拟表 8 中
(10)ORDER BY:将虚拟表 8 中的记录进行排序到虚拟表 9
(11)LIMIT:取出指定行的记录,返回结果集
SQL 执行原理
1、FROM 阶段,如果是多张表联查,还会经历下面的几个步骤
(1)首先先通过 CROSS JOIN 求笛卡尔积,相当于得到虚拟表(virtual table)vt1-1
(2)通过 ON 进行筛选,在虚拟表 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2
(3)添加外部行,如果使用的是左连接、右链接或者全连接,就会涉及到外部行,也就是在虚拟表 vt1-2 的基础上增加外部行,得到虚拟表 vt1-3
(4)若操作的是两张以上的表,还会重复以上步骤,直到所有表都被处理完为止,这个过程得到原始数据
2、WHERE 阶段
(1)拿到查询数据表的原始数据,即最终的虚拟表 vt1
(2)根据 vt1 表的结果进行筛选过滤,得到虚拟表 vt2
3、GROUP 和 HAVING 阶段
(1)在虚拟表 vt2 的基础上进行分组和分组过滤,得到中间的虚拟表 vt3 和 vt4
(2)GROUP BY -> 聚合函数 -> HAVING
4、SELECT 和 DISTINCT 阶段
(1)首先在 SELECT 阶段会提取想要的字段
(2)然后在 DISTINCT 阶段过滤掉重复的行,分别得到中间的虚拟表 vt5-1 和 vt5-2
5、ORDER BY 阶段
(1)按照指定的字段进行排序,得到虚拟表 vt6
6、LIMIT 阶段
(1)在 vt6 的基础上,取出指定行的记录
(2)得到最终的结果,对应的是虚拟表 vt7
7、执行 SELECT 语句时,不一定存在所有的关键字,相应的阶段就会省略
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战