一、前言

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

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

二、聚合查询

如果我们要统计一张表的数据量，例如，想查询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

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

欧耶！！！我学会啦！！！