查询结果取交集_SQL学习笔记（五）多表查询

从零学会SQL：多表查询​www.zhihu.com

本篇将结合课程内容和《SQL基础教程》中的第7章内容对集合运算进行整理。

之前的学习多为在一张表上的操作，本篇主要通过以行方向（竖）为单位的集合运算符和以列方向（横）为单位的联结，将多张表中的数据进行组合。

什么是集合运算？

集合运算就是对满足同一规则的记录进行的加减法的四则运算。通过集合运算，可以得到几张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录。

一、表的的加减法运算符

1.表的加法——Union

表的加法使用集合运算符中的union来实现，表的加法类似数学中集合的并集运算，在做union运算时会将表中的重复记录去除。

select 学号,姓名
from student1
union
select 学号,姓名
from student2
order by 学号 asc;

该查询得到的结果是student1和student2中的去掉重复记录的所有记录。

• 注意

1. 作为运算对象记录的列数必须相同。
2. 作为运算对象记录中列的类型必须一致。
3. 通过union进行并集运算时，可以使用任何形式的select语句：where、group by、having等子句都可以使用，但order by 子句只能在最后使用。
4. 如果要在union结果中包含重复行，只需在union后加all关键字即可，其他集合运算符也同样适用。

2.选取表中公共部分——intersect

选取表中公共部分使用集合运算符中的intersect(交集)来实现，类似数学中集合的求交集运算。

select 学号,姓名
from student1
intersect
select 学号,姓名
from student2
order by 学号 asc;

该查询得到的结果是student1和student2中的相同的记录。

• 注意事项同union，mysql不支持intersect，所以无法使用。

3.记录的减法——except

对记录做减法的集合运算符是except(差集)，类似于数学中集合的差集运算。

select 学号,姓名
from student1
except
select 学号,姓名
from student2
order by 学号 asc;

该查询得到的结果是student1中去除和student2中相同的记录。

• 注意在集合的减法运算中减数和被减数的位置不同，得到的结果也不同。oracel 不适用except,而是使用其特有的minus运算符。此外，mysql还不支持except,因此也无法使用。

二、联结

联结就是将其他表中的列添加过来，进行“添加列”的集合运算。联结可以分为交叉联结、内联结、左联结、右联结和全联结。

1.交叉联结——cross join

交叉联结也叫笛卡尔集，交叉联结是将表中的每一行都与另一个表中的每一行都联结起来。交叉表中的行数是两张表中行数的乘积。

2.内联结——inner join

内联结是对同时存在于两张表中的数据进行交叉联结。

• 练习

（1）查询选课学生每门课程的成绩

-- 内联结
select a.学号,a.姓名,b.成绩
from student as a INNER JOIN score as b
on a.学号=b.学号;

3.左联结——left join

左联结就是将左侧表中全部数据取出与右边符合条件的行进行交叉联结。

• 练习

（1）查询所有学生的成绩

-- 左联结
select a.学号,a.姓名,b.课程号,b.成绩
from student as a LEFT JOIN score as b
on a.学号=b.学号;

（2）查询未选课学生

-- 左联结
select a.学号,a.姓名,b.课程号,b.成绩
from student as a LEFT JOIN score as b
on a.学号=b.学号
where b.成绩 is null;

4.右联结——right join

右联结就是将右侧表中全部数据取出与右边符合条件的行进行交叉联结。

• 练习

（1）查询所有选课学生的信息

-- 右联结
select a.学号,a.姓名,b.课程号,b.成绩
from student as a RIGHT JOIN score as b
on a.学号=b.学号;

（2）查询选课但未录入个人信息的学生

-- 右联结
select a.学号,a.姓名,b.课程号,b.成绩
from student as a RIGHT JOIN score as b
on a.学号=b.学号
where a.学号 is null;

5.全联结——full join

全联结查询结果会返回左右表中的所有行，当某行和另一行条件相符的时候两个表中的行进行合并，当某行和另一行没有匹配条件的时候用null进行填充。

mysql不支持全联结。

• 练习

-- 全联结
select *
from student as a FULL JOIN score as b
on a.学号=b.学号;

6.总结

• 注意

1. 进行联结时要在from 子句中使用多张表
2. 进行联结时必须要使用on子句，它相当于联结几张表之间的桥梁并且要书写在from子句和where子句之间。
3. 使用联结时select 子句中的列需要按照“<表的别名>.<列名>”的格式进行书写，从语法上来说，只有那些同时存在于两张表中的列需要这样书写，其他的列名直接书写也不会发生错误，但是为了避免发生混乱，建议按照上面的格式书写。

• 练习

（1）查询所有学生的学号、姓名、选课数、总成绩

select a.学号,a.姓名,count(b.课程号)as '选课数',sum(b.成绩)as '总成绩'
from student as a left join score as b
on a.学号=b.学号
group by a.学号;

（2）查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号,a.姓名,avg(b.成绩) as '平均成绩'
from student as a left join score as b
on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85;

（3）查询学生的选课情况，查询结果要显示学号、姓名、课程号和课程名称

select a.学号,a.姓名,b.课程号,c.课程名称
from student as a left join score as b 
on a.学号=b.学号
left join course as c
on b.课程号=c.课程号;

三、case表达式

case 表达式用于区分不同情况，类似于条件判断。

case when<判断表达式>then <表达式>
     when<判断表达式>then <表达式>
     when<判断表达式>then <表达式>
     ...
     else<表达式>
end

• 注意

1. 虽然case表达式中的else子句可以省略，会默认为null，但是为了防止漏读尽量不要省略。
2. case表达式中的end不能省略。
3. case表达式可以写在任意位置。

• 练习

（1）查询课程是否及格

select 学号,课程号,(case when 成绩>=60 then '及格'
else '不及格' end) as '及格与否'
from score;

（2）查询每门课程及格人数和不及格人数

select 课程号,sum(case when 成绩>=60 then 1
else 0 
end) as '及格人数',sum(case when 成绩<60 then 1
else 0
end) as '不及格人数'
from score
group by 课程号;

（3）使用分段[100-85],[84-70],[69-60],[<60]来统计各科成绩，分别统计：各分段人数，课程号和课程名称

select a.课程号,b.课程名称,sum(case when a.成绩>=85 and a.成绩<=100 then 1
else 0
end) as '[100-85]的人数',sum(case when a.成绩>=70 and a.成绩<=84 then 1
else 0
end) as '[84-70]的人数',sum(case when a.成绩>=60 and a.成绩<=69 then 1
else 0
end) as '[69-60]的人数',sum(case when a.成绩<60 then 1
else 0
end) as '[<60]的人数'
from score as a right join course as b 
on a.课程号=b.课程号
group by a.课程号;