第08章_MySQL聚合函数

已于 2023-02-20 12:19:31 修改
阅读量346 收藏
点赞数
分类专栏： mysql 文章标签： sql mysql 数据库
于 2023-02-20 11:28:29 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46655235/article/details/129120956
版权

第08章_聚合函数

讲师：尚硅谷-宋红康（江湖人称：康师傅）

官网：http://www.atguigu.com

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

1. 聚合函数介绍

  • 什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

  • 聚合函数类型

    • AVG()
    • SUM()
    • MAX()
    • MIN()
    • COUNT()

  • 聚合函数语法

  • 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1.1 AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), SUM(salary)
FROM employees;


#如下操作没有意义
SELECT AVG(last_name), SUM(last_name), AVG(hire_date), SUM(hire_date)
FROM employees;

1.2 MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(salary), MAX(salary), MIN(last_name), MAX(last_name), MIN(hire_date), MAX(hire_date) 
FROM employees;

1.3 COUNT函数

  • count(expr)：返回expr在查询结构中出现的个数（不包含NULL值），expr可以是任意数据类型
SELECT COUNT(salary), COUNT(last_name), COUNT(hire_date), COUNT(commission_pct), COUNT(1), COUNT(*)
FROM employees;

SUM函数也是不计算NULL值的，AVG=SUM/COUNT

SELECT AVG(salary), SUM(salary)/COUNT(salary), AVG(commission_pct), SUM(commission_pct)/COUNT(commission_pct), SUM(commission_pct)/COUNT(*)
FROM employees;

image-20230217183427809

#查询公司中平均奖金率
SELECT SUM(commission_pct)/COUNT(*), AVG(IFNULL(commission_pct,0))
FROM employees;

image-20230217183715092

  • 问题1：如何计算表中有多少条记录？

    • count(*)：count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，

    • count(1)

    • count(字段) : 不一定对！

  • 问题2：count(1)，用count(*)，count(字段)哪个效率高?

    • 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)

    • 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(字段)

2. GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组，格式：

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

2.1 基本使用

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

2.2 使用多个列分组

SELECT中出现的非聚合函数的字段必须声明在GROUP BY 中；

反之，GROUP BY中声明的字段可以不出现在SELECT中

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

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算未分组前的所有记录的整体平均值

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

3. HAVING

3.1 基本使用

过滤分组：HAVING子句的使用前提

  1. 行已经被分组，HAVING必须与GROUP BY配合使用
  2. 过滤条件中使用了聚合函数，此时必须用HAVING而不能用WHERE，否则，报错

image-20230217194432790

满足HAVING 子句中条件的分组将被显示

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;

image-20230217194619125

3.2 WHERE和HAVING的对比

区别1：

WHERE 可以直接使用表中的字段作为筛选条件，但不能使用聚合函数作为筛选条件；

HAVING 必须要与 GROUP BY 配合使用，可以把使用表中的字段和聚合函数作为筛选条件。

这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法使用聚合函数对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用聚合函数对分组结果进行筛选。

区别2：WHERE 是先筛选后分组，而 HAVING 是先分组后筛选。

这一点，就决定了WHERE 比 HAVING 更高效

开发中的选择：不包含聚合函数的过滤条件用 WHERE，包含聚合函数的过滤条件用 HAVING

#查询部门id为10,20,30,40的4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)>10000;
#方式2：
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 AND department_id IN (10,20,30,40);

image-20230217215733008

4. SELECT的执行过程

4.1 查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ... 
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON 多表的连接条件
WHERE 不包含聚合函数的过滤条件 
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ... 
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）select：查询
#（7）order by：排序
#（8）limit：分页

4.2 SELECT语句的执行顺序

你需要记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> ON -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

4.3 SQL 的执行原理

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

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

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到的是我们的原始数据。

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

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

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

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

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

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

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

假装不痛
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
2021-08-09sql习题
qq_45106034的博客
08-09 2455
1 找出佣金高于薪金60%的雇员。 2 找出部门10中所有经理和部门20中所有办事员的详细资料。 3 找出部门10中所有经理，部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等2000的所有雇员的详细资料。 4 找出收取佣金的雇员的不同工作。 5 找出不收取佣金或收取的佣金低于300的雇员。 6 找出各月最后一天受雇的所有雇员。 7 找出晚于26年之前受雇的雇员。 8 显示只有首字母大写的的所有雇员的姓名。 9 显示正好为5个字符的雇员的姓名。 10显示不带有“R”的雇员姓名。 11显示所有雇员
MySQL第8章聚合函数
weixin_55705029的博客
03-27 214
MySQL聚合函数
数据库查询方式
m0_51041464的博客
11-08 1264
t_stu_subject_score表，该表有两个字段stu_id，subject_id分别记录了student表的id和subject表的id。学生表中的学生为了与班级表的记录产生对应关系，因此在学生表中以class_id字段记录了其所在的班级的主键字段的值。该表字段:max_sal,min_sal,avg_sal,sum_sal,subject_id。缺少连接条件时会产生笛卡尔积，会将A表每条记录与B表每条记录都建立一次连接，产生巨量数据，这通常是一个无意义的结果集，要尽量避免。
mysql学习笔记
weixin_42229749的博客
07-30 171
 Sql分类：  DQL语句 数据查询语言 select DML语句 数据操作语言 insert delete update DDL语句  数据库定义语言  create drop alter   TCL语句   事物控制语言   commit  rollback Show databases;//显示所有数据库 Create  database db; Use db;//切换数据库...
Oracle针对EMP表的练习题(很有用，值得收藏)
热门推荐
null_underfined的博客
10-22 1万+
Oracle针对EMP表的练习题(很有用，值得收藏)
SQL练习40题（oracle）
headfirsthtml5的博客
05-05 7309
使用ORACLE中scott自带的emp表做练习 1.查询所有的部门编号： select distinct(deptno) from emp; 2.查询所有有人员的部门编号： select ename,deptno from emp; 3.查询所有岗位名称： select job from emp group by job; 4.查询所有薪水超过两千的员工信息 select * from emp ...
MySQL8_基础_聚合函数
gingerMr的博客
01-01 1600
文章目录聚合函数1. 聚合函数介绍1. AVG和SUM函数2. MIN和MAX函数3. COUNT函数2. GROUP BY2.1 基本使用2.2 使用多个列分组2.3 GROUP BY中使用WITH ROLLUP3. HAVING3.1 基本使用3.2 WHERE和HAVING的对比4. SELECT的执行过程4.1 查询的结构4.2 SELECT执行顺序4.3 SQL 的执行原理5. 练习 聚合函数 1. 聚合函数介绍 什么是聚合函数 聚合函数作用于一组数据，并对一组数据返回一个值。 聚合
第08章_聚合函数.docx
01-17
聚合函数 聚合函数是 SQL 中的一种函数，用于对一组数据进行汇总，输出单个值。聚合函数可以分为五种：AVG、SUM、MAX、MIN、COUNT。 1. AVG 函数 AVG 函数用于计算一组数据的平均值。例如，SELECT AVG(salary) ...
MySQL.rar_MYSQL_Mysql入门
09-22
7. 数据查询：深入理解JOIN操作，子查询，聚合函数（COUNT、SUM、AVG、MAX、MIN），排序（ORDER BY）、分组（GROUP BY）和筛选（HAVING）。 四、数据库设计 8. 第三方范式：了解第一范式（1NF）、第二范式（2NF）、...
MySQL函数讲解(MySQL函数大全)
09-10
这些函数可以分为多类，包括字符串函数、数字函数、日期和时间函数、聚合函数等。 本文将对MySQL函数大全中的字符串函数进行详细讲解，涵盖了ASCII、ORD、CONV、BIN、OCT、HEX、CHAR、CONCAT、LENGTH、LOCATE等函数...
MySQL常用函数汇总
12-14
**二、聚合函数** 1. `AVG(col)`：计算col列的平均值。 2. `COUNT(col)`：返回col列中非NULL值的数量。 3. `MIN(col)`：返回col列中的最小值。 4. `MAX(col)`：返回col列中的最大值。 5. `SUM(col)`：返回col列所有...
MySQL数据库应用从入门到精通_第2版
10-03
还会涉及复杂的查询技巧，如联接（JOIN）、子查询、集合操作以及聚合函数（如COUNT、SUM、AVG等）的使用。 三、数据库设计与建模 在理解了SQL基础后，将进入数据库设计阶段。这部分将涵盖概念数据模型、逻辑数据...
PostgreSQL的Json数据类型如何使用
招风的黑耳CSDN
07-18 428
PostgreSQL中的JSON数据类型提供了一种灵活的方式来存储JSON（JavaScript Object Notation）数据。JSON是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。在PostgreSQL中，你可以使用JSON和JSONB（JSON的二进制格式，支持索引和更快的查询）数据类型来存储JSON数据。
MySQL面试篇章——MySQL核心SQL（基本语法）
itzzan的博客
07-17 949
扩展：group by 查询慢，explain 分析发现是 Using temporary，即产生了一个临时表，因此最好给分组列添加索引。数据控制语句，用于控制不同的许可和访问级别的语句。每次的 insert 操作都会有一个数据库连接操作，是占用资源的，一次连接完成的事情肯定比多次连接完成的事情所花费的时间短。数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括。低，pageNum的偏移所花费的性能，要先偏移到对应的位置，在进行分页拿去后面的数据记录。
PostgreSQL 中如何解决因大量并发删除和插入操作导致的索引抖动？
技术笔记
07-17 935
索引抖动是 PostgreSQL 中一个常见的问题，它会对数据库的性能产生严重的影响。通过采用批量操作、分区表、索引优化和调整数据库参数等方法，我们可以有效地解决索引抖动问题，提高数据库的性能和稳定性。在实际应用中，我们应该根据具体情况选择合适的解决方案，并不断地进行优化和调整，以满足业务的需求。解决索引抖动问题就像是一场战斗，我们需要根据敌人（问题）的特点和弱点，选择合适的武器（解决方案），并灵活运用战术（优化方法），才能取得最终的胜利。
PostgreSQL异常：An I/O error occurred while sending to the backend
最新发布
IT后浪的博客
07-19 359
在使用PostgreSQL数据库批量写入数据的时候，遇到了一个问题，异常内容如下：Cause: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
接口开发：Orcal数据库的批量新增sql
qq_65032048的博客
07-17 807
场景：在日常的CURD中一定会用到批量新增。在我们的项目中，使用的数据库是Orcal，由于之前基本都是使用Mysql的，使用的sql语句也基本都是用mysql的。但是在这次的接口编写时用mysql的批量新增出了问题，刚开始我还以为是写的动态sql有问题，在后续的问题修改也主要是对动态sql修改，因此也浪费了大量的时间，在后续把sql搬到数据库运行一下后才发现是sql的问题，同时也了解到了mysql和orcal 也是有很多地方是不同的。
SQL每日一题：查找重复的电子邮箱
xiongxiaoxuan的博客
07-17 402
编写解决方案来报告所有重复的电子邮件。请注意，可以保证电子邮件字段不为 NULL。以 任意顺序 返回结果表。按照email分组计数，筛选出数量>1的email。(需注意分组计数时一定要记得用group by)此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。id 是该表的主键（具有唯一值的列）。
SQL Server详细使用教程(包含启动SQL server服务、建立数据库、建表的详细操作) 非常适合初学者
hackeroink的博客
07-15 1256
本文主要详细介绍SQL server2019的简单使用，以《数据库系统概论（第5版）》的第79页—第80页为例，详细介绍如何使用SQL server2019这款数据库软件，包括启动SQL server服务，建立数据库（学生—课程模式S-T）,建立课程表等，内容比较简单，容易理解，适合广大初学者了解SQL server的简单使用。不会涉及到复杂的语法知识，如果有也会详细解释的！下文标红的字请重点关注一下！本文的需要建表的数据如下：2.Course课程号Cno课程名Cname先行课Cpno学分。
mysql聚合函数concat,mysql的group_concat聚合函数的spark sql替代
06-28
MySQL聚合函数concat用于将多个字符串连接成一个字符串。例如，concat('hello', 'world')将返回'helloworld'。 MySQL的group_concat聚合函数用于将多个字符串连接成一个字符串，并将结果按照指定的分隔符分隔。例如...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值