case when 子查询_SQL学习（五）多表查询

一.表的加法（union)

• UNION：把两张表合并起来

如下图：

select 课程号,课程名称 from course
union 
select 课程号,课程名称 from course1;

运行结果如下：

• UNION ALL 合并表格并保留重复行数据

select 课程号,课程名称 from course
union all
select 课程号,课程名称 from course1;

二.表的联结

下面是数据库中的四张表格，存放着对应的数据

• 交叉联结 cross join

又称为笛卡尔积，把两个表里的每行数据两两结合，如下图

• 内联结 inner join

查找同时存在于两张表里的数据，也就是两张表的交集。

我们通过下表的演示，简单说明：

案例：

select s1.学号,s1.姓名,s2.成绩 from student as s1 
inner join score as s2 on s1.学号=s2.学号;

• 左联结 left join

以左边的表为标准联结右表取出指定数据，对于没有匹配上的用null值显示。

我们还是以上面的学生表和成绩表为例：

select s1.学号,s1.姓名,s2.成绩 from student s1
left join score s2 on s1.学号=s2.学号;

• 右联结 right join

指以右边的表为标准联结左表取出指定数据，对于没有匹配的用NULL值显示。

select s1.学号,s1.姓名,s2.成绩 from student s1
right join score s2 on s1.学号=s2.学号;

• 全联结 full join

把两张表按照对应关系连接起来，返回2张表的所有行，没有匹配的值用NULL表示。

select s1.学号,s1.姓名,s2.成绩 from student s1
full join score s2 on s1.学号=s2.学号;

mysql不支持全联结，运行会报错，所以我们理解一下即可。

总结上述几个联结：

我们的各种联结都是在from子句中，所以不会影响sql的整体运行顺序，具体顺序如下图：

三.联结应用案例

我们依旧按照sql解决业务问题的思路来解决实际问题，如下几个案例：

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

翻译问题：

每个学生的学号、姓名；

每个学生的课程总数，且按学号分组，对课程号计数count；

每个学生的总成绩，且按学号分组，对成绩求和sum；

分析思路：

select 查询结果：学号、姓名、选课数COUNT(*)、总成绩SUM（成绩）；

from 从哪张表取出数据：student表和score表，用左联结；

where 指定条件：无

group by 分组：学号

having 对分组结果指定条件：无

order by 对查询结果排序：无

limit 从查询结果中选取指定行：无

运行结果：

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

翻译问题：

每个学生的学号、姓名；

每个学生的平均成绩，且按学号分组，求平均成绩avg；

对分组得到的平均成绩进行判断：平均成绩大于85；

分析思路：

select 查询结果：学号、姓名、平均成绩avg（成绩）；

from 从哪张表选取数据：student表左联结score表 ；

where 查询条件：无

group by 分组：对学号分组，得到每个学生的平均成绩；

having 对分组结果进行条件查询：对平均成绩进行判断，大于85的保留；

order by 对查询结果排序：无

limit 从查询结果中选取指定行：无

运行结果：

3.查询学生的选课情况：学号、姓名、课程号、课程名称

翻译问题：

每个学生的学号、姓名，来源于student表；

每个学生的课程号、课程名称，来源于course表；

分析思路：

select 查询结果：student表-学号、student表-姓名、course表-课程号、course-课程名称

from 从哪张表查询数据：student表内联结score表内联结course表；

where 查询条件：无

group by 分组：无

having 对分组结果设置查询条件：无

order by 对查询结果进行排序：无

limit 从查询结果中选取指定行：无

运行结果：

四.case表达式

case表达式是一个条件判断函数，判断每一行子句是否符合条件，符合就执行then子句，不符合就执行else子句，最后end结束，代码如下：

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

什么情况需使用case表达式：

1.多种情况需要条件判断时

2.自定义分组时

练习：

1.查询出每门课程的及格人数和不及格人数

代码及运行结果如下：

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 课程号;

2.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数、课程id和课程名称

代码及运行结果如下：

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.成绩 < 85 then 1 else 0 end) as '[85-70]',
sum(case when a.成绩 >= 60 and a.成绩 < 70 then 1 else 0 end) as '[70-60]',
sum(case when a.成绩 <60 then 1 else 0 end) as '[<60]'
from score a right join course b on a.课程号=b.课程号
group by a.课程号,b.课程名称；

注意事项：

1.else子句可以省略不写，为了良好的sql书写习惯，建议不要省略；

2.end不能省略，必须写出来；

3.case表达式可以写在sql语句的任意子句中。