首页 > 基础资料博客日记

MySQL-08.聚合函数和SQL(DQL)的执行流程

2023-12-18 23:33:05基础资料围观227次

文章MySQL-08.聚合函数和SQL(DQL)的执行流程分享给大家，欢迎收藏Java资料网，专注分享技术知识

C-08 聚合(分组)函数和SQL(DQL)执行流程

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

1.聚合函数的介绍

什么是聚合函数
- 聚合函数作用于一组数据，并对一组数据返回一个值。
常用的聚合函数
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
聚合函数语法

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
GROUP BY column,
[ORDER BY column];
*/

聚合函数不能嵌套调用。比如不能出现类似AVG(SUM(列名))形式的调用。

1.1 AVG和SUM函数

只能对数值型的数据，使用AVG和SUM函数

mysql> SELECT AVG(salary),SUM(salary),MAX(salary),MIN(salary)
    -> FROM employees;
+-------------+-------------+-------------+-------------+
| AVG(salary) | SUM(salary) | MAX(salary) | MIN(salary) |
+-------------+-------------+-------------+-------------+
| 6461.682243 |   691400.00 |    24000.00 |     2100.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.00 sec)

1.2 MIN和MAX函数

可以对任意类型的数据使用MIN和MAX函数，但是常用于数值型和字符类型，以及日期时间类型。

mysql> SELECT MIN(hire_date),MAX(hire_date)
    -> FROM employees;
+----------------+----------------+
| MIN(hire_date) | MAX(hire_date) |
+----------------+----------------+
| 1987-06-17     | 2000-04-21     |
+----------------+----------------+
1 row in set (0.00 sec)

1.3 COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。

mysql> SELECT COUNT(*) FROM employees;# COUNT(1)的效果类似于COUNT(*)，相当于把一行数据看作1去计数
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec

COUNT(expr)返回expr不为空的记录总数

mysql> SELECT COUNT(commission_pct) FROM employees;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
|                    35 |
+-----------------------+
1 row in set (0.00 sec)

COUNT(*)，COUNT(1)，COUNT(列名)那个好呢？
其实，对于MyISAM存储引擎的表是没有区别，这种引擎内部有一个计数器在维护者表的行数。
Innodb引擎的表用COUNT(*)，COUNT(1)直接读行数，复杂度是O(n)，因为Innodb真的要去读一遍，但好于具体的COUNT(列名)。
能不能使用COUNT(列名)替换COUNT(*)
不要使用COUNT(列名)来替代COUNT(*)，COUNT(*)是SQL92定义的标准统计行数的语法，和数据库无关，和NULL和非NULL无关。COUNT(*)会统计值为NULL的行，而COUNT(列名)只会统计列名不为NULL值的行。

2.GROUP BY

2.1 基本使用

可以使用GROUP BY子句将表中的数据分成若干组。

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression,]
[ORDER BY column];
*/

注意：

GROUP BY子句一定要在WHERE后面。
在SELECT列表中所有未包含在组函数中的列，都应该包含在GROUP BY子句中。而包含在GROUP BY子句中的列不必包含在SELECT列表中。

举例：

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

SELECT AVG(salary)
FROM employees
GROUP BY department_id;

2.2 使用多列分组

SELECT department_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id;
-- GROUP BY job_id,department_id;结果一样
-- 和排序子句不同，顺序不一样也不会对结果有影响

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出所有记录的总和，即统计记录数量。
但是这个总和，和使用的分组函数有关。

mysql> SELECT department_id,AVG(salary)
    -> FROM employees
    -> WHERE department_id > 80
    -> GROUP BY department_id WITH ROLLUP;
+---------------+--------------+
| department_id | AVG(salary)  |
+---------------+--------------+
|            90 | 19333.333333 |
|           100 |  8600.000000 |
|           110 | 10150.000000 |
|          NULL | 11809.090909 |
+---------------+--------------+
4 rows in set (0.00 sec)

3.HAVING

3.1 基本使用

过滤分组，HAVING子句

1.行已经被分组
2.使用了聚合函数
3.满足了HAVING子句中条件的分组将被显示
4.HAVING不能单独使用，必须要跟GROUP BY一起使用

/*
SELECT [column,] group function(column),...
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression,]
[HAVING group_condition,]
[ORDER BY column];
*/

mysql> SELECT department_id,MAX(salary)
    -> FROM employees
    -> GROUP BY department_id
    -> HAVING MAX(salary) > 10000;
+---------------+-------------+
| department_id | MAX(salary) |
+---------------+-------------+
|            20 |    13000.00 |
|            30 |    11000.00 |
|            80 |    14000.00 |
|            90 |    24000.00 |
|           100 |    12000.00 |
|           110 |    12000.00 |
+---------------+-------------+
6 rows in set (0.00 sec)

不能再WHERE子句中使用聚合函数

mysql> SELECT department_id,MAX(salary)
    -> FROM employees
    -> WHERE MAX(salary) > 10000
    -> GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

3.2 WHERE和HAVING的对比

区别1：

WHER可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件，HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。

区别2：

如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。原因，再SQL的执行过程中会解释。

开发中的选择

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

4.SELECT的执行过程

4.1 查询的结构

/*
方式1
SELECT ...,...,...
FROM ...,...,...
WHERE 表的连接条件 AND 不包含分组函数的过滤条件
GROUP BY 分组列名
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...;

方式2
SELECT ...,...,...
FROM ... (LEFT / RIGHT OUTER) | (INNER)JOIN ... ON 连接条件
JOIN ... ON 连接条件
WHERE　不包含分组函数的过滤条件
GROUP BY 分组列名
HAVING 包含分组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...;
*/

4.2 SELECT 执行顺序

/* 
执行顺序，FROM 子句，从哪些表里查询 -> WHERE / ON 使用表的连接条件进行过滤数据 -> 使用WHERE中的非连接条件过滤数据 ->
GROUP BY 对数据进行分组 -> HAVING 对分组后的数据进行过滤 -> SELECT 行过滤出显示的列 -> DISTINCT 有的话去重过滤 ->
ORDER BY 对数据进行排序 -> LIMIT进行分页
*/
-- 这里解释，为什么将不包含分组函数的条件写在WHERE会比HAVING中好，是因为，WHERE先执行，先过滤不满足的数据，可能会让数据量变小
-- 后续的执行就处理的数据量小，效率自然就高了
/*
关键字顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;
*/

4.3 执行原理

SELECT 是先执行FROM这一子句，在这个阶段，如果是多张表联合查询，还会经历下面的步骤：

1.首先先通过CROSS JOIN 求笛卡尔积，相当于得到虚拟表vt(virtual table) 1-1;
2.通过ON进行筛选，在虚拟表上vt1-1的基础上进行筛选，得到虚拟表vt1-2;
3.添加外部行。如果使用的是左连接，右连接，或者全连接就会涉及到外部行，也就是在虚拟vt1-3的基础上，增加外部行，得到虚拟表vt1-3。

当然如果我们操作的是两张以上的表，就会重复上面1-3步骤，知道表多处理完毕，得到原始数据。

当拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上进行WHERE阶段，对vt1表的结果进行筛选过滤，得到虚拟表vt2。

然后进行第三步和第四步，也就是GROUP BY和HAVING阶段。在这个阶段中，实际上是在虚拟表vt2的基础上进行分组和过滤，得到中甲你的虚拟表vt3和vt4。

当完成了条件筛选部分后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段。

首先在SELECT阶段会提取到想要的字段，然后在DISTINCT阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。

当提取到想要展示的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段，得到虚拟表vt6。

最后在vt6的基础上，取出指定行的记录，也就是LIMIT阶段，得到最终的结果，对应的是虚拟表vt7。

当然SELECT语句，并不一定会写所有的关键字，未写的相应的字段就会省略。

同时因为SQL是一门结构化语言，所以在写SELECT语句时，要注意相应的关键字顺序，执行原理，就是执行的顺序。

只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

文章来源:https://www.cnblogs.com/changming06/p/17912654.html
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！

标签：

上一篇：SpringBoot 异步编程浅谈
下一篇：【流式传输】使用Spring Boot实现ChatGpt流式传输

首页 > 基础资料博客日记

MySQL-08.聚合函数和SQL(DQL)的执行流程

C-08 聚合(分组)函数和SQL(DQL)执行流程

1.聚合函数的介绍

1.1 AVG和SUM函数

1.2 MIN和MAX函数

1.3 COUNT函数

2.GROUP BY

2.1 基本使用

注意：

举例：

2.2 使用多列分组

2.3 GROUP BY中使用WITH ROLLUP

3.HAVING

3.1 基本使用

3.2 WHERE和HAVING的对比

区别1：

区别2：

开发中的选择

4.SELECT的执行过程

4.1 查询的结构

4.2 SELECT 执行顺序

4.3 执行原理

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料 博客日记

MySQL-08.聚合函数和SQL(DQL)的执行流程

C-08 聚合(分组)函数和SQL(DQL)执行流程

1.聚合函数的介绍

1.1 AVG和SUM函数

1.2 MIN和MAX函数

1.3 COUNT函数

2.GROUP BY

2.1 基本使用

注意：

举例：

2.2 使用多列分组

2.3 GROUP BY中使用WITH ROLLUP

3.HAVING

3.1 基本使用

3.2 WHERE和HAVING的对比

区别1：

区别2：

开发中的选择

4.SELECT的执行过程

4.1 查询的结构

4.2 SELECT 执行顺序

4.3 执行原理

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料博客日记