查询去除空值_SQL数据处理(五)：SQL多表查询

一. 表的加法

加法(union)。合并两张数据表course和course1/去除重复行重合

【select 课程号,课程名称 from course union select 课程号,课程名称 from course1;】
输出结果两个表合并的数据

若要保留重复的行数据(union all)

【select 课程号,课程名称 from course union all select 课程号,课程名称 from course1;】
输出结果两个表合并的数据，保留重复数据

二. 表的联结

2.1 交叉联结(cross jion)：笛卡尔积指将表中的每一行逗鱼另一个表中的每一行合并在一起

例如课程表(crouse)和课程表1(crouse1)进行交叉联结得到6行数据

2.2 内联结(inner jion)：

1. 查找出同时存在于两张表中的数据
2. 例如学生表(student)和成绩表(score)同时存在学号0001
3. 内联结运行会取出符合条件的行数据
4. 将取出的数据通过交叉联结进行合并
5. on 表示某个表的某列与另一个表的某列进行联结的

• 学生表与成绩表通过学号进行内联结合并为一张表

【select a.学号,a.姓名,b.课程号 from student as a inner join score as b on a.学号 = b.学号;】
输出结果 0001=李彦宏=0001、0001=李彦宏=0002

2.3 左联结(left jion)：

1. 将左侧的表作为主表，将左侧表中的数据全部读取出来
2. 将右侧的表中只读取与左侧表同时存在的数据行
3. 进行交叉联结(合并数据)，因为0002学号在右侧表没有对应的数据行显示为空值

-- 学生表与成绩表通过学号进行左联结合并为一张表

【select a.学号,a.姓名,b.课程号 from student as a left join score as b on a.学号 = b.学号;】
输出结果 0001=李彦宏=0001、0001=李彦宏=0002、0002=李彦宏=空值

获取因为左联结忽略的数据

【select a.学号,a.姓名,b.课程号 from student as a left join score as b on a.学号 = b.学号 where 课程号 is Null;】
输出结果0002=李彦宏=空值

2.4 右联结(right jion)：

1. 将右侧的表作为主表，将右侧表中的数据全部读取出来
2. 将左侧的表中只读取与左侧表同时存在的数据行
3. 进行交叉联结(合并数据)，因为0005学号在右侧表没有对应的数据行显示为空值

-- 学生表与成绩表通过学号进行右联结合并为一张表

【select a.学号,a.姓名,b.课程号 from student as a right join score as bon a.学号 = b.学号 where b.学号 = Null;】
输出结果 0001=李彦宏=0001、0001=李彦宏=0002、空值=空值=0003

获取因为右联结忽略的数据

【select a.学号,a.姓名,b.课程号 from student as a right join score as b on a.学号 = b.学号 where a.学号 is Null;】
输出结果空值=空值=课程号

2.5 全联结(full jion)：

1. 全联结会返回左表和右表中的所有数据行
2. 当两个表中的数据存在匹配的数据行时会进行合并
3. 若两个表中的数据存在没有匹配的数据行，不存在的值会用空值表示

三. 联结应用案例

• 查询所有学生的学号，姓名，选课数，总成绩

【select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩 
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;】
输出结果0001=李彦宏=2=170、0002=李彦宏=0=空值

• 查询平均成绩大于60的所有学生的学号、姓名和平均成绩

【select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>60;】
输出结果0001=李彦宏=85

• 查询学生的选课情况：学号，姓名，选课号，课程名称

【select a.学号,a.姓名,c.课程号,c.课程名称
from student a inner join score b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;】
输出结果0001=李彦宏=0001=语文、0001=李彦宏=0002=数学

四. case表达式

4.1 定义：

使用case表达式可以解决复杂的条件判断，用来判断每一行数据是否满足指定条件

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

若满足“when判断表达式”条件则执行“then表达式”，若不满足则执行下一个“when判断表达式”。若不满足所有的“when判断表达式”则执行“else表达式”

4.2 注意事项

1. else子句可以不写，此时默认为空值
2. case子句可以写才SQL语句的子句里面
3. 当有多种需要条件判断时使用case表达式

4.3 练习题

• 定义成绩数据表中的成绩数据是否及格

【select 学号,课程号,成绩,
(case when 成绩>60 then '及格'
      when 成绩<=60 then '不及格'
      else null
end) as 是否及格
from score;】
输出结果0001=0001=80=及格、0001=0002=90=及格、0005=0003=60=不及格

• 查询每门课程的及格人数和不及格人数

【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 课程号;】
输出结果0001=1=0、0002=1=0、0003=1=0

• 使用分段[100-85],(85-70],(70,60],(60,-]来统计各科成绩，分别统计：各分数段的人数，课程号，和课程名称

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

四. 联结总结

文氏图：SQL的联结形式