当前位置： 首页 > news >正文

【MySQL】第五弹——表的CRUD进阶(三)聚合查询(上)

news 2025/5/18 6:27:42

文章目录

  • 🌅聚合函数
    • 🌊1.COUNT();统计所有行
    • 🌊2. SUM(列名); 求和
    • 🌊3. AVG() 求平均
    • 🌊4. MAX(),MIIN()
  • 🌅分组查询
    • 🌊GROUP BY 子句
    • 🌊HAVING
  • 🌅联合查询
    • 🌊联合多个表进行查询
      • 🏄‍♂️联合查询是MYSQL内部是如何执行的?
        • 💦1. 取多张表的笛卡尔积
        • 💦2. 通过连接条件过滤无效数据
        • 💦3.通过指定列查询,来精简结果集
    • 🌊内连接
    • 🌊示例练习
      • 🏄‍♂️内连接练习
      • 🏄‍♂️联合查询练习

🌅聚合函数

聚合查询本质上是针对数据表中的行与行进行运算

之前我么你介绍的表达式查询,是 对一行记录中的列和列之间进行运算

比如:语文成绩+数学成绩+英语成绩

  • 常见的联合查询函数

都是一些在MYSQL里内置的一些函数

在这里插入图片描述

注意:这些操作都是针对一行记录中的某一列进行运算的

🌊1.COUNT();统计所有行

在这里插入图片描述
在这里插入图片描述

但是在工作中还是推荐大家使用count(*),因为这种写法是SQL标准中规定的

  • 如果表中某一列数据出现NULL会怎样?

在这里插入图片描述
在这里插入图片描述

count(列名),如果列中有NULL值,则不会被统计在内

🌊2. SUM(列名); 求和

把查询结果中所有行中的指定列进行相加

注意:列中的数据类型必须是数值型,不能是字符或日期…

  • 计算所有学生语文成绩总分

在这里插入图片描述

  • 如果表中某一列数据出现NULL会怎样?–NULL进行运算结果会是NULL吗

之前我们演示过NULL与任何类型的值进行运算结果都是NULL

在这里插入图片描述

在SUM()求和时,NULL值不参与运算

虽然我们介绍过NULL 与任何类型得到值进行运算结果都是NULL,但是MYSQL的创始人写这个内置函数的目的是为了check有没有NULL值吗–并不是,所以我们以后再进行一些设计的时候也要考虑到用户的具体需求,我们只是为了得到总分,不是要检查是否存在NULL值,注意用户需要这个功能的主要目的,再进行设计

如果以后我们做一些公共接口开发时,一定要考虑到用户的真实意图是什么,把特殊值做特殊处理

  • 如果对非数字类型的列进行求和运算,会得到一些警告信息

在这里插入图片描述

🌊3. AVG() 求平均

对所有行的指定列进行求平均值运算

  1. 对所有同学的语文成绩求平均值

在这里插入图片描述
2. 求语文,数学,英语三门课的总分的平均值

在这里插入图片描述

🌊4. MAX(),MIIN()

求所有行中指定列的最大值,最小值

  1. 找出语文成绩的最高分和英语成绩的最低分

在这里插入图片描述

  1. 查找语文成绩的最高分和最低分

在这里插入图片描述

在使用关于运算的聚合函数时,不要使用在非数值的列上,比如sum(),avg(),max(),min()

🌅分组查询

🌊GROUP BY 子句

select 中使用 group by 子句可以对指定列进行分组查询

使用group by 进行分组查询时,select指定的字段必须是’分组依据字段’,其他字段若想出现在select 中则必须包含在聚合函数中

在这里插入图片描述

语法:

在这里插入图片描述

  • 示例:使用emp表进行group by子句演示

在这里插入图片描述

在这里插入图片描述

  1. 计算不同角色工资平均值

要分组的是 role 列,MYSQL 内部先分组再计算
在这里插入图片描述

这样小数点太多了,观感不好,我们使用ROUND(数值,小数点位数) 来规定一下工资的格式

在这里插入图片描述

group by 子句之后可以跟 order by 子句

在这里插入图片描述

🌊HAVING

GROUP BY 子句进行分组后,需要对分组结果再进行条件过滤,不能使用 WHERE 语句,而需要使用 HAVING

where 是对表中每一行的真实数据进行过滤

having 是对group by 之后,计算出来的结果进行过滤

在这里插入图片描述
在这里插入图片描述

where 用在 from 表名之后么也就是分组之前
having 跟在 order by 子句之后
如果需要对真实数据进行过滤,也需要对分组的结果进行过滤
那只需要在合适的位置写入 where 和 having 即可

  • 查询每个角色的最高工资、最低工资和平均工资

步骤:
①.按角色进行分组
②.使用相应的聚合函数

  • 显示平均工资低于1500的角色和他的平均工资

步骤:
1.按角色分组
2.使用相应的聚合函数
3.使用having子句对分组的结果进行过滤

在这里插入图片描述

注意: having要跟在 group by 列名之后

🌅联合查询

工作中用的最多的查询,而且面试中也经常考察

🌊联合多个表进行查询

设计表时把表进行拆分,为了消除表中的字段间的依赖关系,比如部分函数依赖和传递依赖(第二、第三范式)

这是会导致一条SQL语句查出来的数据,对于业务来说是不完整的,我们就可以使用联合查询把关系表中的数据全部查出来,在一个数据行中显示详细的信息

在这里插入图片描述

两张表产生了主外键关系,但这并不是我们想要的结果集

在这里插入图片描述

这个结果集才是我们想要的

🏄‍♂️联合查询是MYSQL内部是如何执行的?

💦1. 取多张表的笛卡尔积

在这里插入图片描述

在这里插入图片描述
对多张表进行笛卡尔积的过程
1.先从第一张表中取一条记录,然后再与第二张表中的第一条记录进行组合,生成一条新的记录
2.先从第一张表中取一条记录,然后再与第二张表中的第二条记录进行组合,生成一条新的记录
…

最后得到的结果就是一个全排列的结果集

语法:select * from 表名,表名

在这里插入图片描述

两张表取笛卡尔积之后,有些数据是无效数据,如何过滤掉无效数据?

💦2. 通过连接条件过滤无效数据

两个表之间是有主外键关系的,只需要判断两个表主外键字段是否相等即可

因为class_id 在两张表中都存在,MYSQL 分不清楚当前语句的class_id 应该取自哪个表

所以通过表名.列名的方式来解决这个问题

在这里插入图片描述

💦3.通过指定列查询,来精简结果集

查询时通过表名.列名指定要查询出的字段

在这里插入图片描述
这样查询要写的子句太长了,我们通常使用别名来简化SQL语句

student–>s
class–>c

在这里插入图片描述

联合查询也叫做表连接查询
1.首先确定哪几张表要参与查询
2.根据表与表之间的主外键关系,确定过滤条件
2.精简查询字段,得到想要的结果

🌊内连接

语法:

在这里插入图片描述

三种写法
1.使用select from … where and
2.使用select from … inner join on …
3.使用select from … join on … --内连接标准写法省略inner

以上三种写法,工作学习中自己选一个合适的即可

🌊示例练习


DROP TABLE IF EXISTS `score`;
DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `course`;-- ----------------------------
-- Table structure for class
-- ----------------------------CREATE TABLE `class`  (`class_id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');-- ----------------------------
-- Table structure for course
-- ----------------------------CREATE TABLE `course`  (`course_id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');-- ----------------------------
-- Table structure for student
-- ----------------------------CREATE TABLE `student`  (`student_id` bigint NOT NULL AUTO_INCREMENT,`sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`class_id` bigint NULL DEFAULT NULL,PRIMARY KEY (`student_id`) USING BTREE,UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,INDEX `class_id`(`class_id` ASC) USING BTREE,CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);-- ----------------------------
-- Table structure for score
-- ----------------------------CREATE TABLE `score`  (`score_id` bigint NOT NULL AUTO_INCREMENT,`student_id` bigint NULL DEFAULT NULL,`course_id` bigint NULL DEFAULT NULL,`score` decimal(5, 2) NULL DEFAULT NULL,PRIMARY KEY (`score_id`) USING BTREE,INDEX `student_id`(`student_id` ASC) USING BTREE,INDEX `course_id`(`course_id` ASC) USING BTREE,CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);

在之前创建好的表结构中,导入初始化数据

🏄‍♂️内连接练习

1.首先确定哪几张表要参与查询
2.根据表与表之间的主外键关系,确定过滤条件
3.精简查询字段,得到想要的结果

  • 查询许仙同学的成绩

三个步骤一个一个来

1.确定哪几张表参与查询(取笛卡尔积) – 成绩表 学生表

取两张表的笛卡尔
在这里插入图片描述

2.根据表与表之间的主外键关系,确定过滤条件

两张表中通过student_id作为主外键关联字段

在这里插入图片描述

根据具体需求确定结果集的过滤条件

在where语句中,添加 student.name = ‘许仙’ 的过滤条件

在这里插入图片描述
3.精简查询列表中的字段

学生名 分数

在这里插入图片描述

🏄‍♂️联合查询练习

联合查询步骤细化之后:
1.确定查询中涉及哪些表
2.对目标表取笛卡尔积
3.确定连接条件
4.确定对整个结果集的过滤条件
5.精简查询字段

  • 查询所有同学的总成绩及同学的个人信息

总成绩–>需要用到聚合函数SUM 分组查询group by

1.确定哪几张表参与查询 – 成绩表 学生表

分组查询时用学生编号好,还是用学生姓名好?–编号的目的就是为了保证记录的唯一性

2.目标表取笛卡尔积

在这里插入图片描述

3.确定连接关系(过滤条件)

在这里插入图片描述

4.按学生id进行分组,并在查询列表中,使用聚合函数就散总成绩

在这里插入图片描述

5.在查询列表中精简确定要查询的字段

在这里插入图片描述

http://www.xdnf.cn/news/499267.html

相关文章：

  • Docker数据卷
  • 深入解析Spring Boot与JUnit 5的集成测试实践
  • FTP服务搭建实战：安全文件共享解决方案
  • 使用Docker部署Nacos
  • 机器学习-人与机器生数据的区分模型测试 -数据筛选
  • 【AI论文】EnerVerse-AC：用行动条件来构想具身环境
  • stm32 DMA
  • 【八股战神篇】Java集合高频面试题
  • Redis Sentinel如何实现高可用？
  • 类加载 与 Spring容器加载
  • STM32 | 软件定时器
  • 【发票提取表格】批量PDF电子发票提取明细保存到Excel表格，批量提取ODF电子发票明细，行程单明细，单据明细保存到表格，使用步骤、详细操作方法和注意事项
  • Java—异常体系
  • 【Linux笔记】——Linux线程封装
  • Ulyssess Ring Attention
  • Python文件与JSON操作全解：从基础到企业级实践
  • A级、B级弱电机房数据中心建设运营汇报方案
  • Ankr：Web3基础设施的革新者
  • Zephyr OS 中的 FIFO 接口应用介绍
  • SECERN AI提出3D生成方法SVAD！单张图像合成超逼真3D Avatar！
  • Windows系统部署MongoDB数据库图文教程
  • 机器学习-人与机器生数据的区分模型测试-数据处理 - 续
  • 【漫话机器学习系列】263.线性插值（Interpolation）
  • img.dims() ＜= 2 in function ‘cv::matchTemplate报错
  • Mysql 刷题 day05
  • 数学复习笔记 19
  • 1.2.1电商平台
  • 探索C++对象模型：（拷贝构造、运算符重载）成员函数的深度解读（中篇）
  • 力扣HOT100之二叉树：98. 验证二叉搜索树
  • 【网络入侵检测】基于Suricata源码分析运行模式（Runmode）