MySQL--聚合查询、联合查询、子查询、合并查询（上万字超详解！！！）

于 2024-10-05 15:25:17 发布
阅读量3.3k 收藏 13
点赞数 22
分类专栏： MySQL 文章标签： mysql 数据库 大数据 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2302_81090306/article/details/142704684
版权

目录

一、前言

                              欢迎大家来到权权的博客~
              欢迎大家对我的博客进行指导，有什么不对的地方，我会及时改进哦~

博客主页链接点这里–>：权权的博客主页链接

二、聚合查询

如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？这个方法当然可以，但是比较不好。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

2.1 聚合函数

在这里插入图片描述

2.1.1 COUNT():统计所有行

语法：

select count() from...table_name

在这里插入图片描述

2.1.2 SUM(列名) 求和

语法：

select sum() from table_name

把查询结果中所有的行中指定列相加，注意：这里相加的数据类型必须是数值型不能是字符或者日期…

示例：
在这里插入图片描述

2.1.3 AVG()

语法：

select AVG() FROM TABLE_NAM...

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

示例：
在这里插入图片描述

2.1.4 MAX()、MIN()

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

语法：

select max() from...

示例：
在这里插入图片描述

2.2 GROUP BY子句（分组查询）

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

select 中使用group by子句可以对指定列进行分组查询。
需要满足：使用group by 进行分组查询时，select指定的字段必须是“分组依据字段”，其他字段若想出现在select中必须包含在聚合函数之中。

语法:

select column1,sum(column2)...from table group by column1,column2;

例如：计算不同职位的工资平均值：

在这里插入图片描述

2.3 HAVING

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

问题：找出分组后的结果进行过滤，比如说，找出平均工资大于150万的小于200万的角色。
在这里插入图片描述
注意：

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

三、联合查询

设计数据时把表进行拆分，为了消除表中的字段的依赖关系，比如部分函数依赖、传递依赖，这时会导致一条SQL语句查出来的数据，对于业务是不完整的，我们就可以使用联合查询把关系中的数据全部查出来，在一个数据行中显示详细信息。

3.1表的笛卡儿积

问题：联合查询时MySQL是如何执行的？

在这里插入图片描述
例如：
在这里插入图片描述
语法：

select * from 表名,表名;

前提：创建一个school表和class表，然后插入一些数据，然后使用上面这个命令进行观察：
在这里插入图片描述
通过观察一些数据取笛卡儿积之后，一些是无效数据
那么我们如何过滤掉这些无效数据？

通过连接条件过滤掉无效数据

在有外键关系存在的情况下：
在这里插入图片描述

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

查询列表中通过表名.列名的方式指定要查询字段。
在这里插入图片描述

注意：

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

3.2内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;-- 标准的连接写法
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;--where 连接方法

在这里插入图片描述

3.2.1 例题一

先创建class、course、score、student 表：

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

REATE 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.确定结果集的过滤条件：
在where条件中，增加 stu.name="许仙"的过滤条件:
在这里插入图片描述
4.精简查询列表中的字段在这里插入图片描述

3.2.2 例题二

要求：

查询所有学生的总成绩、以及同学的个人信息。
在这里插入图片描述

1.确定要参与查询的表是学生表和成绩表,
在这里插入图片描述
以及查询总成绩要用分组查询。
2.取两张笛卡儿积表：
在这里插入图片描述
3.确定连接条件：

student.student_id=score.student_id

在这里插入图片描述
4.确定所有同学成绩的结果集的过滤条件
按学生的id进行分组，并且在查询列表中，使用聚合函数 sum（分数）计算总分
在这里插入图片描述
5.精简查询字段（并且对总分升序）
在这里插入图片描述

3.3外连接

外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示我们说就是左外连接；右侧的表完全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

在这里插入图片描述
使用内连接：（可以看到没有把猪显示出来）
在这里插入图片描述
那我们要怎么办才能把猪显示出来呢？

3.3.1 右外连接

是以join右边的表为基准，这个表中的数据会全部显示出来，左边的表没有与之匹配的记录全部用null填充。
在这里插入图片描述

3.3.2 左外连接

是以join左边的表为基准，这个表中的数据会全部显示出来，右边的表没有与之匹配的记录全部用null填充。

在这里插入图片描述

mysql中不支持全外连接。

3.4 自连接

自己与自己进行表连接

在这里插入图片描述

3.4.1例题：

要求：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息。
1.确定涉及的表：
成绩表 课程表
在这里插入图片描述

2.取笛卡儿积
在这里插入图片描述
3.确定连接条件
在这里插入图片描述
4.确定对整个结果集的过滤条件

在这里插入图片描述
要么是s1表中的course_id=1并且s2表中的course_id=3,或者s1表中的course_id=3并且s2表中的course_id=1（本例子是这个）。
5.精简查询字段

在这里插入图片描述
加入最后条件查出计算机原理成绩大于Java成绩：
在这里插入图片描述

四、子查询(嵌套查询)

子查询是把一条SQL的查询结果，当做另一条SQL的查询条件，也可以嵌套很多层。
在这里插入图片描述

4.1 单行子查询

例题
要求：

查询与不想毕业同学的同班同学。

1.确定查询中涉及哪些表
学生表
在这里插入图片描述

2.先查出不想毕业同学的信息。
在这里插入图片描述
3.在学生表中查出与“不想毕业”的同学的同班同学，条件是与“不想毕业”同学班级编号相同的所有学生。
在这里插入图片描述

把上面第3点这个1用子查询的方式替换。
在这里插入图片描述

4.2 多行子查询

返回多行记录的子查询
在这里插入图片描述

案例：查询“语文”或者“英文”课程的成绩信息。

1.确定涉及那些表：
课程表、成绩表
在这里插入图片描述

2.在课程表中获得“语文”和“英文”课程的编号
在这里插入图片描述
3.根据获取到的课程id，在成绩表中查询相应的课程分数
在这里插入图片描述
4.把上面步骤查询的SQL拼装起来，变成子查询
在这里插入图片描述

4.3 多行包含

在这里插入图片描述

查询重复的分数：
可以用分组查询的方式：

在这里插入图片描述
1.同一个学生，同一门课程，同一样的成绩，按这3个列同时去分组。
2.分组之后在having语句中，用count（*）语句判断分组中的记录数
在这里插入图片描述
3.加入外层查询
在这里插入图片描述

4.4 [not] exists 关键字

语法：

select * from 表名 where exists(select * from 表名1)

相当于if语句的判断条件，如果符合条件则执行外层查询。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

4.5 例题

要求：查询所有比"计算机系2019级1班”平均分高的成绩信息。

1.确定涉及的表：
成绩表、班级表、学生表
在这里插入图片描述

2.先算出“计算机系2019级一班”的平均分：
（1）先从班级表中的班级名找到班级编号
（2）根据班级编号在学术表中找到班例的学生以及学生编号
（3）根据学生的编号在成绩表中计算平均分
在这里插入图片描述
3.再用表中学生的真实成绩和平均分做比较：

在这里插入图片描述

五、合并查询

作用：合并多个查询到一个结果集当中
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和UNION ALL时，前后查询的结果集中，字段需要一致。

5.1 union

在这里插入图片描述

根据一张表的结构创建一张新表

+

通过union把两张表的数据显示在一个结果集当中。

union用于取得两个结果集的并集。当使用该操作符时，会去掉结果集中的重复行。
在这里插入图片描述
在这里插入图片描述

5.2 union all

操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
欧耶！！！我学会啦！！！

权^
关注
专栏目录
[MySQL]聚合查询
Emperor10的博客
09-20 2829
文章目录1 聚合函数1.1 count1.2 sum1.3 avg1.4 max1.5 min2 分组查询2.1 group by2.2 having 聚合查询是更复杂的查询语句，将若干行给合并起来， 1 聚合函数 count函数可以查询任何类型的数据，但是其他函数只能查询数字，因为它要做基本的数学计算，不是数字就没有意义。 聚合函数 说明 count 返回查询到的数据的数量 sum 返回查询到的数据的总和，不是数字没有意义 avg 返回查询到的数据的 平均值，不是数字没有意
mysql连接查询、联合查询、子查询原理与用法实例详解
09-08
MySQL中的查询操作是数据库操作的核心，它包括连接查询、联合查询和子查询，这些方法都是在处理多表数据时非常重要的技术。 连接查询是将两个或更多表的数据结合在一起进行查询，主要有四种类型：内连接（INNER ...
mysql（二）：使用聚合函数查询
逗号的博客
10-19 1337
count()函数 1.count()函数用来统计记录的条数 2.与group by关键字一起使用 例：select count(*) from t_grade;        select count(*) as total from t_grade;        select stuName,count(*)  from t_grade group by stuNa
【MySQL】聚合查询
程序猿教你打篮球的博客
03-17 3897
本期主要讲解： 1、前言 2、插入查询结果 3、聚合查询 3.1 聚合函数 3.1.1 count 3.1.2 sum 3.1.3 avg 3.1.4 max 和 min 4、GROUP BY 子句 5、HAVING 关键字
MYSQL——聚合查询
最新发布
2301_80251684的博客
08-27 1400
聚合查询就是一些MYSQL中的内置函数聚合查询本质上是针对数据表中的行和行进行计算以上操作都是针对某一列进行计算的。
mysql的聚合查询_MySql聚合查询
weixin_36249942的博客
01-28 1381
SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT。注意，参加集合操作的各查询结果的列数必须相同；对应的数据类型也必须相同。本示例中的数据表有student，sc，course三个，数据表的具体内容请看：Mysql数据库中的EXISTS和NOT EXISTSUNION示例：例子1.1...
MySQL子查询操作实例详解
09-09
MySQL子查询是数据库操作中一个重要的概念，它允许在一个查询语句中嵌套另一个查询语句，用于获取或比较数据。子查询可以用来解决复杂的数据筛选问题，尤其是在处理关联和聚合操作时。本篇将通过实例详细解析MySQL中...
详解MySql基本查询、连接查询、子查询、正则表达查询
09-10
MySQL是世界上最流行的关系型数据库管理系统之一，其查询能力强大，涵盖了基本查询、连接查询、子查询和正则表达式查询等多种方式。以下是对这些查询方式的详细解释。 1. **基本查询**：基本查询是最简单的SQL查询...
MySQL多表查询详解
03-08
### MySQL多表查询详解 #### 一、多表查询的基本概念与操作 多表查询是在实际业务场景中非常常见的需求之一。它允许我们从多个表中检索数据，并将这些数据组合在一起以形成有意义的信息。在MySQL中，多表查询通常...
MySQL查询条件常见用法详解
09-09
MySQL查询条件是数据库操作中非常基础且重要的部分，它允许我们...你可以进一步探索MySQL的其他功能，如聚合函数、联接查询、子查询等，以增强你的数据库管理能力。通过不断地学习和实践，你将成为MySQL查询的专家。
MySQL之聚合查询、分组和排序
10-24
附件包含的主要内容为演示MySQL之聚合查询、分组和排序的相关命令和效果。
MySQL数据库，聚合查询
我的主页
04-21 2355
本期讲解：聚合函数count，sum，max，min，avg函数以及group by子句、having条件语句的讲解。
【MySql】数据库的聚合查询
m0_62729383的博客
09-09 940
哈喽，宝子们，今天给大家带来的是MySql数据库的聚合查询。在前面CRUD章节我们学习了表达式查询，表达式查询是针对列和列之间进行运算的，那么如果想在行和行之间进行运算，那么就需要用到聚合查询。聚合查询除了包含聚合函数外（count，sum，avg，max，min），还包含group by 和 having 语句。喜欢的话可以三连哦~~~
mysql聚合查询_MySQL使用聚合函数查询
weixin_39966602的博客
01-20 458
create table `t_grade` (`id` int ,`stuName` varchar (60),`course` varchar (60),`score` int);insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');insert into `t_grade` ...
MySQL表中的聚合查询
小小码农
02-02 1279
MySQL表中的聚合查询
MySQL之聚合查询和联合查询
crazy_xieyi的博客
12-16 1462
聚合查询（行与行之间的计算）;联合查询（多表查询）
MySQL的聚合查询，分组查询，连接查询（内，外连接），子查询，联合查询
weixin_56738054的博客
11-13 2921
MySQL的聚合查询，分组查询，连接查询（内，外连接），子查询，联合查询
Mysql的聚合查询
小宝儿的学习之路
05-21 272
聚合查询: 如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？ 这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。 仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询： SELECT COUNT(*) FROM students; 使用聚合查询并设置结果
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值