sql三表查询_SQL第五关：多表查询

学习内容：

1. 表的加法
2. 表的联结
3. 联结应用案例
4. case表达式

---

表的加法

学校数据库里的课程表（course），新建课程表（course 1），数据结果一样，新建数据内容不一样。

1. 加法：把两个表的数据按行合并在一起，并删除重复列。

select 课程号,课程名称
from course
union -- 加法
select 课程号,课程名称
from course1；

2. 保留重复行

select 课程号,课程名称
from course
union all -- 保留重复行
select 课程号,课程名称
from course1；

---

表的联结

关系数据库由多张表组成，course，score，student，teacher为存放在school data base里的四张表。

1. 关系数据库里各个表之间如何建立起关系呢？关系：数据库能对应的匹配，在关系数据库中正式的关系叫做联结（join)。

2.

1. 交叉联结（笛卡尔积，cross join）

表中的每一行都与另一张表中的每一行合并，结果为两张表之中的行的乘积。

实际工作中用的较少，没有实际价值，但是其他所有联结是在交叉联结的基础上加了相应的约束条件。

2.内联结（inner join）

同时存在于两张表中的数据。inner join

取出符合条件的行 交叉联结

select a.学号, a.姓名,b.课程号 --列名前加表名
from student as a inner join score as b -- a,b 简化表名
on a.学号=b.学号 -- on 匹配条件

3.左联结

左侧表中的所有数据全部取出来。

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

select a.学号, a.姓名,b.课程号
from student as a left join score as b
on a.学号=b.学号
where b.学号is Null; -- 只取存在于左表的数据

3.右联结（right join）

右侧表中的所有数据全部取出来。

select a.学号, a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号

select a.学号, a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号
where b.课程号 is Null; -- 只取存在于右表的数据

4.全联结（full join）

返回左表与右表中的所有行，若有匹配的行则进行合并，若没有匹配的行，列显示null值。

mysql不支持全联结。

conclusion

生成固定行数的表单或者指明了要特定表里的所有数据，会使用左链接或者右链接。其他情况下一般使用内链接来获取两个表的公关部分。

多个表链接时是在from语句中加入，不会影响之前学习的sql查询顺序。

---

联结应用案例

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

select a.学号, a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b -- 查询所有学生所有需要左链接
on a.学号=b.学号
group by a.学号; -- 左链接需要用左边的列来分组

Q2 查询平均成绩大于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;

Q3 查询学生的选课情况：学号，姓名，课程号，课程名称 （三表联结）

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

---

Case 表达式

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

在查询结果中显示成绩是及格还是不及格

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

case 每次运行只返回一个结果

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

select 课程号,
sum(case when 成绩 >= 60 then 1 -- 应用函数 case 与 sum
else 0
end) as 及格人数,
sum(case when 成绩 < 60 then 1
else 0
end) as 不及格人数

case 表达式 注意事项

1. else 语句可以不写，默认空值，但为了书写规范最好写上去。
2. end 不可以不写，不然语句不完整
3. case 语句可以写在sql语句的任意子句里

case表达式作用:

当有多种条件需要判断时，可以使用case表达式。

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

select a.课程号, b.课程名称,
sum(case when 成绩 between 100 and 86 then 1
else 0
end) as '[100.85)', --加引号为字符串
sum(case when 成绩 between 85 and 71 then 1
else 0
end) as '[85.70)',
sum(case when 成绩 between 70 and 60 then 1
else 0
end) as '[70.60]',
sum(case when 成绩 < 60 then 1
else 0
end) as 不及格人数
from score as a right join course as b --需查询所有课程 右联结
on a.课程号=b.课程号
group by a.课程号 , b.课程名称; -- 多个列分组时，这几个列的值全部相同才算一组 /* 为了显示课程名称，group by 里显示课程名称，而且group by里加入课程名称需要和group by里的返回结果一致
*/

---

sqlzoo

Join '2012年欧洲足球锦标赛'

---

总结：

多表联结时，group by 条件一定要小心分清

新人创作打卡挑战赛发博客就能抽奖！定制产品红包拿不停！