【MySQL】联合查询（下）
目录
上期我们讲了内连接、外连接、自连接查询，今天我们继续讲其他联合查询，没看过的之前的可以先去看看上期博客：
一. 子查询
子查询就是将一个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) 
