MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询
MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询
目录
- MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询
- 一、聚合函数
- 1、计数函数（COUNT）
- 2、求和函数（SUM）
- 3、求平均值函数（AVG）
- 4、求最大值函数（MAX）
- 5、求最小值函数（MIN）
- 6、连接数据值函数（GROUP_CONCAT）
- 二、分组查询
- 1、GROUP BY 的语法
- 2、使用列名分组
- 3、使用表达式分组
- 4、分组的同时使用 WHERE 子句
- 5、使用 HAVING 对分组进行选择
- 6、WITH ROLLUP 参数
聚合函数又称为统计函数，可以对查询结果进行统计和汇总，对表中某一列的数据值进行计算并返回一个单一值。
使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组，分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。
一、聚合函数
常用的聚合函数包括 SUM、COUNT、AVG、MAX 和 MIN，实现对表中数据的统计，GROUP_CONCAT 函数的功能与聚合函数类似，可以对某一列中的数据值进行连接操作。聚合函数的语法格式如下：
-- 1、计数函数（count）：使用 count(*) 时不忽略 NULL 值，使用 count(列名|表达式) 时忽略 NULL 值
-- 使用 distinct 可以去除重复数据
COUNT(distinct *|列名|表达式)-- 2、求和函数（sum）：列的类型或表达式返回值的类型必须是数值类型
SUM(列名|表达式)-- 3、求平均值函数：列的类型或表达式返回值的类型必须是数值类型
AVG(列名|表达式)-- 4、求最大值函数：列的类型或表达式返回值的类型可以是任意类型
MAX(列名|表达式)-- 5、求最小值函数：列的类型或表达式返回值的类型可以是任意类型
MIN(列名|表达式)-- 6、连接数据值函数：列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号
-- 使用 distinct 可以去除重复数据
GROUP_CONCAT([distinct] 列名 separator '分隔符')
说明：
（1）如果查询中使用了聚合函数，在没有分组的情况下，查询结果只有一行（只有一个统计结果）。如果使用 GROUP BY 分组，则每一个分组有一个统计结果。
（2）如果查询中使用了聚合函数，在没有分组的情况下，SELECT 后面除了聚合函数之外，一般不能有列名。
1、计数函数（COUNT）
使用 COUNT( ) 函数用于统计记录数量，通常与 GROUP BY 子句合用。
语法格式如下：
COUNT(distinct *|列名|表达式)-- 说明：使用 count(*) 时不忽略 NULL 值，使用 count(字段|表达式) 时忽略 NULL 值
例如：
（1）统计学生人数
mysql> select count(*) stu_cnt from stu;
+---------+
| stu_cnt |
+---------+
| 14 |
+---------+
1 row in set (0.00 sec)
（2）统计学生所在院系的数量
mysql> select count(distinct dept_id) as dept_cnt from stu;
+----------+
| dept_cnt |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
（3）统计女生人数
mysql> select count(1) stu_cnt from stu where gender='女';
+---------+
| stu_cnt |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
2、求和函数（SUM）
使用 SUM( ) 函数可以对表中某一列的数据求和，统计时忽略 NULL 值。如果没有匹配行，则返回 NULL 值。常与 GROUP BY 子句合用。语法格式如下：
SUM(字段|表达式)-- 说明：列的类型或表达式返回值的类型必须是数值类型
例如：
（1）统计学号为【20220124002】的同学选修的所有课程的总分
mysql> select sum(score) total_score from xk where s_id='20220124002';
+-------------+
| total_score |
+-------------+
| 226.00 |
+-------------+
1 row in set (0.00 sec)
（2）统计女生人数
mysql> select sum(1) stu_cnt from stu where gender='女';
+---------+
| stu_cnt |
+---------+
| 6 |
+---------+
1 row in set (0.00 sec)
（3）统计男生人数
mysql> select sum(if(gender='男',1,0)) stu_cnt from stu;
+---------+
| stu_cnt |
+---------+
| 8 |
+---------+
1 row in set (0.00 sec)
3、求平均值函数（AVG）
使用 AVG( ) 函数可以计算表中某一列数据的平均值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：
AVG(字段|表达式)-- 说明：列的类型或表达式返回值的类型必须是数值类型
例如：
（1）统计学号为【20220124002】的同学选修的所有课程的平均分
mysql> select avg(score) avg_score from xk where s_id='20220124002';
+-------------+
| avg_score |
+-------------+
| 75.333333 |
+-------------+
1 row in set (0.00 sec)
（2）统计学生的平均年龄
mysql> select avg(year(now())-year(birth)) avg_age from stu;
+---------+
| avg_age |
+---------+
| 22.0714 |
+---------+
1 row in set (0.00 sec)
4、求最大值函数（MAX）
使用 MAX( ) 函数统计某一列数据的最大值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：
MAX(字段|表达式)-- 说明：列的类型或表达式返回值的类型可以是任意类型
例如：
（1）查询所有学生的最大年龄
mysql> select max(year(now())-year(birth)) max_age from stu;
+---------+
| max_age |
+---------+
| 24 |
+---------+
1 row in set (0.00 sec)
（2）查询编号为【C01002】的课程的最高分
mysql> select max(score) max_score from xk where c_id='C01002';
+-----------+
| max_score |
+-----------+
| 69.00 |
+-----------+
1 row in set (0.00 sec)
5、求最小值函数（MIN）
使用 MIN( ) 函数统计某一列数据的最小值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下：
MIN(字段|表达式)-- 说明：列的类型或表达式返回值的类型可以是任意类型
例如：
（1）查询所有学生的最小年龄
mysql> select min(year(now())-year(birth)) min_age from stu;
+---------+
| min_age |
+---------+
| 21 |
+---------+
1 row in set (0.00 sec)
（2）查询编号为【C01002】的课程的最低分
mysql> select min(score) min_score from xk where c_id='C01002';
+-----------+
| min_score |
+-----------+
| 68.00 |
+-----------+
1 row in set (0.00 sec)
6、连接数据值函数（GROUP_CONCAT）
使用 GROUP_CONCAT( ) 函数可以把某一列的数据值连接成一个字符串，数据值之间使用指定的分隔符分隔（默认为逗号）。语法格式如下：
GROUP_CONCAT([distinct] 列名 separator '分隔符')-- 说明：列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符，如果省略 separator，则默认的分隔符为逗号
例如：
（1）查询编号为【D01】的学院的学生名单
mysql> select group_concat(s_name) from stu where dept_id='D01';
+-----------------------------+
| group_concat(s_name) |
+-----------------------------+
| 薛智玲,杨铭华,张从超,孙金航 |
+-----------------------------+
1 row in set (0.00 sec)
（2）查询编号为【D02】的学院的学生名单，学生之间用空格分隔
mysql> select group_concat(s_name separator ' ') from stu where dept_id='D01';
+-------------------------------------+
| group_concat(s_name separator ' ') |
+-------------------------------------+
| 薛智玲 杨铭华 张从超 孙金航 |
+-------------------------------------+
1 row in set (0.00 sec)
二、分组查询
使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组，分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。
1、GROUP BY 的语法
GROUP BY 子句的语法格式如下：
GROUP BY <列名|表达式>[,...] [HAVING 条件表达式] [WITH ROLLUP]--说明：
（1）使用分组查询时，select 后面的字段列表只能包含 GROUP BY 后面的列名或表达式以及聚合函数，不能包含其他的列或表达式，否则会报错。
（2）列名|表达式：分组依据，按列名或表达式进行分组。
（3）HAVING 条件表达式：对分组进行选择，符合条件表达式的结果才会显示。
（4）WITH ROLLUP：在所有记录的最后加上一条记录，该记录为对所有行的统计结果（求和）。
2、使用列名分组
例如：按照 s_id 分组，统计每个学生所学课程的平均分
mysql> select s_id, avg(score) avg_score from xk group by s_id;
+-------------+-----------+
| s_id | avg_score |
+-------------+-----------+
| 20220124001 | 73.666667 |
| 20220124002 | 75.333333 |
| 20220124003 | 94.500000 |
| 20220124004 | 88.500000 |
| 20220214001 | 85.666667 |
| 20220214002 | 68.000000 |
| 20220214003 | 87.333333 |
| 20220325101 | 80.000000 |
| 20220325102 | 80.333333 |
| 20220325103 | 69.333333 |
| 20220410101 | 82.666667 |
| 20220410102 | 70.666667 |
| 20220410103 | 69.333333 |
| 20220410104 | 69.500000 |
+-------------+-----------+
14 rows in set (0.00 sec)
3、使用表达式分组
例如：按照学生的姓氏分组，查询每组对应的学生人数
mysql> select left(s_name, 2) surname, count(*) stu_cnt from stu group by surname;
+---------+---------+
| surname | stu_cnt |
+---------+---------+
| 孙 | 1 |
| 夏 | 1 |
| 谭 | 1 |
| 杨 | 1 |
| 薛 | 1 |
| 赵 | 2 |
| 张 | 2 |
| 裴 | 1 |
| 聂 | 1 |
| 周 | 1 |
| 董 | 1 |
| 江 | 1 |
+---------+---------+
12 rows in set (0.00 sec)
4、分组的同时使用 WHERE 子句
分组时如果使用了 WHERE 子句，则先使用 WHERE 对表中的数据进行筛选，然后进行分组和统计。
例如：利用 xk 表统计每门课程考试分数大于 80 分的学生人数
mysql> select c_id, count(*) stu_cnt from xk where score>80 group by c_id;
+--------+---------+
| c_id | stu_cnt |
+--------+---------+
| C01001 | 3 |
| C01003 | 3 |
| C02102 | 1 |
| C02103 | 2 |
| C03201 | 2 |
| C03202 | 2 |
| C04112 | 2 |
| C04113 | 1 |
+--------+---------+
8 rows in set (0.00 sec)
5、使用 HAVING 对分组进行选择
使用 HAVING 子句可以对分组进行选择。当 HAVING 子句与 WHER 子句同时使用时，查询执行的顺序为：先使用 WHERE 对表中的记录进行筛选，然后对满足条件的记录分组与统计，再使用 HAVING 子句对分组进行选择。
例如：
（1）利用 xk 表查询每门课的选修人数，并且只显示选修人数大于 3 门的课程信息
mysql> select c_id, count(*) stu_cnt from xk group by c_id having stu_cnt>3;
+--------+---------+
| c_id | stu_cnt |
+--------+---------+
| C01001 | 4 |
| C01003 | 4 |
| C04111 | 4 |
| C04112 | 4 |
+--------+---------+
4 rows in set (0.00 sec)
（2）利用 stu 表查询每个学院的男生人数，并且只显示男生人数大于 3 人的学院信息
mysql> select dept_id, count(*) stu_cnt from stu where gender='男' group by dept_id having stu_cnt>2;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D03 | 3 |
+---------+---------+
1 row in set (0.00 sec)
6、WITH ROLLUP 参数
在所有记录的最后加上一条记录，该记录为对所有行的统计结果（求和）。
例如：
（1）利用 stu 表查询每个学院的学生人数以及总人数
-- 把 dept_id 为空的学生的 dept_id 修改为 D04
mysql> update stu set dept_id='D04' where dept_id is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0-- 分组查询
mysql> select dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D01 | 4 |
| D02 | 3 |
| D03 | 3 |
| D04 | 4 |
| NULL | 14 |
+---------+---------+
5 rows in set (0.00 sec)
（2）利用 stu 表查询每个学院的学生人数以及总人数，并把最后一行的 NULL 修改为 stu_total
mysql> select ifnull(dept_id, 'total') dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
+---------+---------+
| dept_id | stu_cnt |
+---------+---------+
| D01 | 4 |
| D02 | 3 |
| D03 | 3 |
| D04 | 4 |
| total | 14 |
+---------+---------+
5 rows in set, 1 warning (0.00 sec)
相关文章：
MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询
MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询 目录 MySQL 实验 10：数据查询（3）—— 聚合函数与分组查询一、聚合函数1、计数函数（COUNT）2、求和函数（SUM࿰…...
感知机学习算法
感知机 一、感知机简介二、感知机模型2.1 感知机的基本组成2.2 求和函数2.2.1 时间总合2.2.2 空间总合 2.3 激活函数2.4 学习算法2.4.1 赫布学习规则2.4.2 Delta学习规则 三、 结论参考文献 一、感知机简介 M-P神经元模型因其对生物神经元激发过程的极大简化而成为神经网络研究…...
2024年双十一有什么好物推荐？双十一必买清单大汇总
随着科技的飞速发展，数码产品已成为我们生活中不可或缺的伙伴。2024年双十一购物狂欢节即将来临，众多消费者早已摩拳擦掌，准备在这个年度盛事中淘到心仪的数码好物。在这个信息爆炸的时代，如何从琳琅满目的商品中挑选出性价比高、…...
C语言贪吃蛇
#只讲逻辑不讲一些基础，基础大概过一遍就行# project-one: 无 (gitee.com)仓库里面有原代码 一、基础工作 1、先将你的编译器换成32位环境，也就是x86， 如果是控制台主机窗口则管，若不是需要改为控制台主机窗口 打开运行窗口后点…...
SpringBoot宠物咖啡馆平台：创新设计与高效实现
1系统概述 1.1 研究背景 随着计算机技术的发展以及计算机网络的逐渐普及，互联网成为人们查找信息的重要场所，二十一世纪是信息的时代，所以信息的管理显得特别重要。因此，使用计算机来管理基于Spring Boot的宠物咖啡馆平台的设计与…...
李宏毅深度学习-梯度下降和Batch Normalization批量归一化
Gradient Descent梯度下降 ▽ -> 梯度gradient -> vector向量 -> 下图中的红色箭头（loss等高线的法线方向） Tip1: Tuning your learning rates Adaptive Learning Rates自适应lr 通常lr会越来越小 Adaptive Learning Rates中每个参数都给它不…...
java集合框架都有哪些
Java集合框架（Java Collections Framework）是Java提供的一套设计良好的支持对一组对象进行操作的接口和类。这些接口和类定义了如何添加、删除、遍历和搜索集合中的元素。Java集合框架主要包括以下几个部分： 接口： Collection&…...
笔记整理—linux进程部分（8）线程与进程
前面用了高级IO去实现鼠标和键盘的读取，也说过要用多进程方式进行该操作： int mian(void) {int ret-1;int fd-1;char bug[100]{0};retfork();if(0ret){//子进程，读鼠标}if(0<ret){//父进程，读键盘}else{perror("fork&quo…...
使用 Python 实现遗传算法进行无人机路径规划
目录 使用 Python 实现遗传算法进行无人机路径规划引言1. 遗传算法概述1.1 定义1.2 基本步骤1.3 遗传算法的特点 2. 使用 Python 实现遗传算法2.1 安装必要的库2.2 定义类2.2.1 无人机模型类2.2.2 遗传算法类 2.3 示例程序 3. 遗传算法的优缺点3.1 优点3.2 缺点 4. 改进方向5. …...
JAVA基础： synchronized 和 lock的区别、synchronized锁机制与升级
1 synchronized 和 lock的区别 synchronized是一个关键字， lock是一个接口，实际使用的是实现类 synchronized通过触发的是系统级别的锁机制， lock是API级别的锁机制 synchronized自动获得锁，自动释放锁。 lock需要通过方法获得锁…...
自动驾驶 车道检测实用算法
自动驾驶 | 车道检测实用算法 车道识别是自动驾驶领域的一个重要问题，今天介绍一个利用摄像头图像进行车道识别的实用算法。该算法利用了OpenCV库和Udacity自动驾驶汽车数据库的相关内容。 该算法包含以下步骤： 摄像头校准，以移除镜头畸变&…...
22.第二阶段x86游戏实战2-背包遍历REP指令详解
免责声明：内容仅供学习参考，请合法利用知识，禁止进行违法犯罪活动！ 本次游戏没法给 内容参考于：微尘网络安全 本人写的内容纯属胡编乱造，全都是合成造假，仅仅只是为了娱乐，请不要…...
java 的三种IO模型（BIO、NIO、AIO）
java 的三种IO模型（BIO、NIO、AIO） 一、BIO 阻塞式 IO（Blocking IO）1.1、BIO 工作机制1.2、BIO 实现单发单收1.3、BIO 实现多发多收1.4、BIO 实现客户端服务端多对一1.5、BIO 模式下的端口转发思想 二、NIO 同步非阻塞式 IO&#…...
低级语言和高级语言、大小写敏感、静态语言和动态语言、链接
低级语言和高级语言 一般而言，更接近硬件的语言被称为低级语言，反之，更远离硬件被称为高级语言。C语言既有低级语言的特点，又有高级语言的特点，又被称为系统语言。Java/Python一般被称为高级语言。 大小写敏感 DOS/Win…...
P3197 [HNOI2008] 越狱
题目传送门 题面 [HNOI2008] 越狱 题目描述 监狱有 n n n 个房间，每个房间关押一个犯人，有 m m m 种宗教，每个犯人会信仰其中一种。如果相邻房间的犯人的宗教相同，就可能发生越狱，求有多少种状态可能发生越狱。 …...
会声会影导出视频mp4格式哪个最高清，会声会影输出格式哪个清晰
调高分辨率后，mp4视频还是不清晰。哪怕全部使用4K级素材，仍然剪不出理想中的高画质作品。不是你的操作有问题，而是剪辑软件没选对。Corel公司拥有全球顶尖的图像处理技术，该公司研发的会声会影视频剪辑软件，在过去的20…...
Linux：进程调度算法和进程地址空间
✨✨✨学习的道路很枯燥，希望我们能并肩走下来! 文章目录 目录 文章目录 前言 一 进程调度算法 1.1 进程队列数据结构 1.2 优先级 编辑 1.3 活动队列 编辑 1.4 过期队列 1.5 active指针和expired指针 1.6 进程连接 二 进程地址空间 2.1 …...
TCP ---滑动窗口以及拥塞窗口
序言 在上一篇文章中我们介绍了 TCP 中的协议段格式，以及保证其可靠传输的重传机制，着重介绍了三次握手建立连接，四次挥手断开连接的过程(👉点击查看)。 这只是 TCP 保证通信可信策略的一部分，现在让我们继续深入吧&…...
第十二章--- fixed 和 setprecision 函数、round 函数、进制转换及底层逻辑
1. 保留几位小数 在C中，如果你想要控制输出的小数点后的位数，可以使用<iomanip>头文件提供的fixed和setprecision函数。这里的fixed用于设置浮点数的输出格式为定点表示法，而setprecision(n)则用来指定小数点后保留的位数。具体用法如…...
ASP.NetCore---I18n（internationalization）多语言版本的应用
文章目录 0.实现的效果如下1.创建新项目I18nBaseDemo2.添加页面中的下拉框3.在HomeController中添加ChangeLanguage方法4.在Progress.cs 文件中添加如下代码：5. 在progress.cs中添加code6.添加Resource资源文件7.在页面中引用i18n的变量8. 重启项目，应该…...
Python爬虫实战：研究MechanicalSoup库相关技术
一、MechanicalSoup 库概述 1.1 库简介 MechanicalSoup 是一个 Python 库，专为自动化交互网站而设计。它结合了 requests 的 HTTP 请求能力和 BeautifulSoup 的 HTML 解析能力，提供了直观的 API，让我们可以像人类用户一样浏览网页、填写表单和提交请求。 1.2 主要功能特点…...
Cursor实现用excel数据填充word模版的方法
cursor主页：https://www.cursor.com/ 任务目标：把excel格式的数据里的单元格，按照某一个固定模版填充到word中 文章目录 注意事项逐步生成程序1. 确定格式2. 调试程序 注意事项 直接给一个excel文件和最终呈现的word文件的示例，…...
从零实现富文本编辑器#5-编辑器选区模型的状态结构表达
先前我们总结了浏览器选区模型的交互策略，并且实现了基本的选区操作，还调研了自绘选区的实现。那么相对的，我们还需要设计编辑器的选区表达，也可以称为模型选区。编辑器中应用变更时的操作范围，就是以模型选区为基准来…...
Leetcode 3577. Count the Number of Computer Unlocking Permutations
Leetcode 3577. Count the Number of Computer Unlocking Permutations 1. 解题思路2. 代码实现 题目链接：3577. Count the Number of Computer Unlocking Permutations 1. 解题思路 这一题其实就是一个脑筋急转弯，要想要能够将所有的电脑解锁&#x…...
spring：实例工厂方法获取bean
spring处理使用静态工厂方法获取bean实例，也可以通过实例工厂方法获取bean实例。 实例工厂方法步骤如下： 定义实例工厂类（Java代码），定义实例工厂（xml），定义调用实例工厂ÿ…...
10-Oracle 23 ai Vector Search 概述和参数
一、Oracle AI Vector Search 概述 企业和个人都在尝试各种AI，使用客户端或是内部自己搭建集成大模型的终端，加速与大型语言模型（LLM）的结合，同时使用检索增强生成（Retrieval Augmented Generation &#…...
计算机基础知识解析：从应用到架构的全面拆解
目录 前言 1、 计算机的应用领域：无处不在的数字助手 2、 计算机的进化史：从算盘到量子计算 3、计算机的分类：不止 “台式机和笔记本” 4、计算机的组件：硬件与软件的协同 4.1 硬件：五大核心部件 4.2 软件&#…...
嵌入式学习之系统编程(九)OSI模型、TCP/IP模型、UDP协议网络相关编程（6.3）
目录 一、网络编程--OSI模型 二、网络编程--TCP/IP模型 三、网络接口 四、UDP网络相关编程及主要函数 编辑编辑 UDP的特征 socke函数 bind函数 recvfrom函数（接收函数） sendto函数（发送函数） 五、网络编程之 UDP 用…...
解析“道作为序位生成器”的核心原理
解析“道作为序位生成器”的核心原理 以下完整展开道函数的零点调控机制，重点解析"道作为序位生成器"的核心原理与实现框架： 一、道函数的零点调控机制 1. 道作为序位生成器 道在认知坐标系$(x_{\text{物}}, y_{\text{意}}, z_{\text{文}}…...
Java后端检查空条件查询
通过抛出运行异常：throw new RuntimeException("请输入查询条件！");BranchWarehouseServiceImpl.java // 查询试剂交易（入库/出库）记录Overridepublic List<BranchWarehouseTransactions> queryForReagent(Branch…...