目录
一. 子查询
单行子查询
多行子查询
多列子查询
在from子句中使用子查询
二. 合并查询
union all
union
三.插入查询结果
上期我们讲了内连接、外连接、自连接查询，今天我们继续讲其他联合查询，没看过的之前的可以先去看看上期博客：
https://blog.csdn.net/2402_86304740/article/details/147640075
https://blog.csdn.net/2402_86304740/article/details/147640075
一. 子查询
子查询就是将一个select语句查询出来的结果当成另一个select语句的查询条件，子查询也叫做嵌套查询。
子查询语法：
select *from 表名 where col_name1 {= | IN} (select col_name1 from table2 where col_name2 {= | IN} [(select ...)] ...
)单行子查询
示例演示：查询与“不想毕业”同学的同班同学
-- 想要查出“不想毕业”同学的同班同学，那么就要先查出“不想毕业”同学的班级
mysql> select *from student where name='不想毕业';
+----+--------------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+--------------+--------+------+--------+----------+
| 8 | 不想毕业 | 200004 | 18 | 女 | 2 |
+----+--------------+--------+------+--------+----------+
1 row in set (0.03 sec)
（那么现在我们只需要不想毕业同学的class_id）mysql> select class_id from student where name='不想毕业';
+----------+
| class_id |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
（此时就知道不想毕业同学的班级了）-- 查询班级id为2的同学
mysql> select *from student where class_id=2;
+----+--------------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+--------------+--------+------+--------+----------+
| 5 | 宋江 | 200001 | 18 | 女 | 2 |
| 6 | 武松 | 200002 | 18 | 男 | 2 |
| 7 | 李逹 | 200003 | 18 | 男 | 2 |
| 8 | 不想毕业 | 200004 | 18 | 女 | 2 |
+----+--------------+--------+------+--------+----------+
4 rows in set (0.01 sec)
（此时就有这么多的学生信息）-- 将两个查询语句组合成一个语句也就是子查询语句
mysql> select *from student where class_id=(select class_id from student where name='不想毕业');
+----+--------------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+--------------+--------+------+--------+----------+
| 5 | 宋江 | 200001 | 18 | 女 | 2 |
| 6 | 武松 | 200002 | 18 | 男 | 2 |
| 7 | 李逹 | 200003 | 18 | 男 | 2 |
| 8 | 不想毕业 | 200004 | 18 | 女 | 2 |
+----+--------------+--------+------+--------+----------+
4 rows in set (0.00 sec)
(此时还需要将不想毕业同学的信息给过滤掉)mysql> select *from student where class_id=(select class_id from student where name='不想毕业') and name!='不想毕业';
+----+--------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+--------+--------+------+--------+----------+
| 5 | 宋江 | 200001 | 18 | 女 | 2 |
| 6 | 武松 | 200002 | 18 | 男 | 2 |
| 7 | 李逹 | 200003 | 18 | 男 | 2 |
+----+--------+--------+------+--------+----------+
3 rows in set (0.00 sec)
代码解析：
多行子查询
示例演示：查询MySQL或者Java的成绩信息
-- 要查询这两科的成绩，就要知道这两个课程的课程id
mysql> select *from course where name in('MySQL','Java');
+----+-------+
| id | name |
+----+-------+
| 1 | Java |
| 3 | MySQL |
+----+-------+
2 rows in set (0.01 sec)
mysql> select id from course where name in('MySQL','Java');
+----+
| id |
+----+
| 1 |
| 3 |
+----+
2 rows in set (0.00 sec)-- 再通过课程id查找这两个课程的成绩
mysql> select *from score where course_id in(1,3);
+------+------------+-----------+
| sco | student_id | course_id |
+------+------------+-----------+
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 60 | 2 | 1 |
| 33 | 3 | 1 |
| 68 | 3 | 3 |
| 67 | 4 | 1 |
| 23 | 4 | 3 |
| 81 | 5 | 1 |
+------+------------+-----------+
8 rows in set (0.01 sec)-- 最后将两个查询语句进行合并成一个子查询语句
mysql> select *from score where course_id in (select id from course where name in('MySQL','Java'));
+------+------------+-----------+
| sco | student_id | course_id |
+------+------------+-----------+
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 60 | 2 | 1 |
| 33 | 3 | 1 |
| 68 | 3 | 3 |
| 67 | 4 | 1 |
| 23 | 4 | 3 |
| 81 | 5 | 1 |
+------+------------+-----------+
8 rows in set (0.01 sec)代码分析：
多列子查询
示例演示：查询重复录⼊的分数
-- 插入重复的分数
mysql> insert into score (sco,student_id,course_id)values(70.5,1,1),(98.5, 1, 3),(60, 2, 1);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0-- 查看一下重复数据
mysql> select *from score order by sco desc;
+------+------------+-----------+
| sco | student_id | course_id |
+------+------------+-----------+
| 99 | 3 | 5 |
| 98.5 | 1 | 3 |
| 98.5 | 1 | 3 |
| 98 | 1 | 6 |
| 92 | 7 | 6 |
| 81 | 5 | 1 |
| 80 | 7 | 2 |
| 79 | 6 | 6 |
| 72 | 4 | 6 |
| 70.5 | 1 | 1 |
| 70.5 | 1 | 1 |
| 68 | 3 | 3 |
| 67 | 4 | 1 |
| 60 | 2 | 1 |
| 60 | 2 | 1 |
| 59.5 | 2 | 5 |
| 56 | 4 | 5 |
| 56 | 6 | 2 |
| 43 | 6 | 4 |
| 37 | 5 | 5 |
| 33 | 1 | 5 |
| 33 | 3 | 1 |
| 23 | 4 | 3 |
+------+------------+-----------+
23 rows in set (0.01 sec)
(此时就有3条重复数据了)-- 开始查找出这三条重复数据，那么重复数据的数量肯定大于1，我们可以对成绩和学生id和课程id进行分组，然后统计每个组中的数据数量，如果大于1就是重复数据了
mysql> select sco,count(*)from score group by sco,student_id,course_id having count(*)>1;
+------+----------+
| sco | count(*) |
+------+----------+
| 70.5 | 2 |
| 98.5 | 2 |
| 60 | 2 |
+------+----------+
3 rows in set (0.01 sec)
(此时找到重复的成绩了，但是我们需要的是当前重复成绩的信息)mysql> select *from score group by sco,student_id,course_id having count(*)>1;
+------+------------+-----------+
| sco | student_id | course_id |
+------+------------+-----------+
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 60 | 2 | 1 |
+------+------------+-----------+
3 rows in set (0.00 sec)
（此时得到重复数据的信息了，那么通过这几个成绩信息，就可以查询成绩表中的重新数据）mysql> select *from score where (sco,student_id,course_id) in(select *from score group by sco,student_id,course_id having count(*)>1);
+------+------------+-----------+
| sco | student_id | course_id |
+------+------------+-----------+
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 60 | 2 | 1 |
| 70.5 | 1 | 1 |
| 98.5 | 1 | 3 |
| 60 | 2 | 1 |
+------+------------+-----------+
6 rows in set (0.01 sec)在from子句中使用子查询
示例演示：查询软件班中成绩超过平均分的成绩信息
-- 首先需要知道软件班的平均分
mysql> select avg(sc.sco) from class c,score sc where c.id=sc.course_id;
+-------------------+
| avg(sc.sco) |
+-------------------+
| 66.61538461538461 |
+-------------------+
1 row in set (0.01 sec)-- 然后将软件班学生的成绩与平均分进行比较
mysql> select *from score sc,(select avg(sc.sco) as score from class c,score sc where c.id=sc.course_id) as tmp where sc.sco>tmp.score;
+------+------------+-----------+-------------------+
| sco | student_id | course_id | score |
+------+------------+-----------+-------------------+
| 70.5 | 1 | 1 | 66.61538461538461 |
| 98.5 | 1 | 3 | 66.61538461538461 |
| 98 | 1 | 6 | 66.61538461538461 |
| 68 | 3 | 3 | 66.61538461538461 |
| 99 | 3 | 5 | 66.61538461538461 |
| 67 | 4 | 1 | 66.61538461538461 |
| 72 | 4 | 6 | 66.61538461538461 |
| 81 | 5 | 1 | 66.61538461538461 |
| 79 | 6 | 6 | 66.61538461538461 |
| 80 | 7 | 2 | 66.61538461538461 |
| 92 | 7 | 6 | 66.61538461538461 |
| 70.5 | 1 | 1 | 66.61538461538461 |
| 98.5 | 1 | 3 | 66.61538461538461 |
+------+------------+-----------+-------------------+
13 rows in set (0.00 sec)二. 合并查询
在实际应用中，我们可能需要合并多个查询出来的结果，就需要进行合并查询，使用集合操作符union,unionall
重新创建一个测试数据：
mysql> create table student1 like student;
Query OK, 0 rows affected (0.03 sec)insert into student1 (name, sno, age, gender,class_id) values
('唐三藏', '100001', 18, '男',1),
('刘备', '300001', 18, '女', 3),
('张⻜', '300002', 18, '男', 3),
('关⽻', '300003', 18, '男', 3);mysql> select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 3 | 张⻜ | 300002 | 18 | 男 | 3 |
| 4 | 关⽻ | 300003 | 18 | 男 | 3 |
+----+-----------+--------+------+--------+----------+
4 rows in set (0.00 sec)union all
示例演示：查询student1表中id<3的同学和student1表中的所有同学
-- 查询student1表中id<3的信息
mysql> select *from student1 where id<3;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
+----+-----------+--------+------+--------+----------+
2 rows in set (0.00 sec)-- 查询student1表中的所有同学
mysql> select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 3 | 张⻜ | 300002 | 18 | 男 | 3 |
| 4 | 关⽻ | 300003 | 18 | 男 | 3 |
+----+-----------+--------+------+--------+----------+
4 rows in set (0.00 sec)mysql> select *from student1 where id<3 union all select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 3 | 张⻜ | 300002 | 18 | 男 | 3 |
| 4 | 关⽻ | 300003 | 18 | 男 | 3 |
+----+-----------+--------+------+--------+----------+
6 rows in set (0.01 sec)
(此时就发现两个查询语句的表进行了合并)union
示例演示：查询student1表中id<3的同学和student1表中的所有同学
-- 查询student1表中id<3的信息
mysql> select *from student1 where id<3;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
+----+-----------+--------+------+--------+----------+
2 rows in set (0.00 sec)-- 查询student1表中的所有同学
mysql> select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 3 | 张⻜ | 300002 | 18 | 男 | 3 |
| 4 | 关⽻ | 300003 | 18 | 男 | 3 |
+----+-----------+--------+------+--------+----------+
4 rows in set (0.00 sec)mysql> select *from student1 where id<3 union select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 1 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 2 | 刘备 | 300001 | 18 | 女 | 3 |
| 3 | 张⻜ | 300002 | 18 | 男 | 3 |
| 4 | 关⽻ | 300003 | 18 | 男 | 3 |
+----+-----------+--------+------+--------+----------+
4 rows in set (0.00 sec)
（此时就会发现union过滤的合并表后的重复信息）
union和union all的区别：
union会将合并表中的重复行进行过滤，union all则只负责合并，不进行过滤操作
三.插入查询结果
在插入数据时，MySQL也支持将查询出来的数据作为插入的数据进行插入
示例演示：将student表中软件班的学生复制到student1表中
-- 先删除student1表中的数据
mysql> delete from student1;
Query OK, 4 rows affected (0.01 sec)-- 复制数据
mysql> insert into student1 (name,sno,age,gender,class_id) select s.name,s.sno,s.age,s.gender,s.class_id from student s,class c where s.class_id=c.id and c.name='软件班';
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0-- 查询数据
mysql> select *from student1;
+----+-----------+--------+------+--------+----------+
| id | name | sno | age | gender | class_id |
+----+-----------+--------+------+--------+----------+
| 5 | 唐三藏 | 100001 | 18 | 男 | 1 |
| 6 | 孙悟空 | 100002 | 18 | 女 | 1 |
| 7 | 猪悟能 | 100003 | 18 | 男 | 1 |
| 8 | 沙悟净 | 100004 | 18 | 男 | 1 |
+----+-----------+--------+------+--------+----------+
4 rows in set (0.00 sec)
