第五关课程大纲：
1.表的加法
2.表的联结
3.联结应用案例
4.case表达式
1.表的加法
union、union all（保留重复行）
例1.加法（union）
例2.加法（union all）
练习：合并两个表
创建待合并的course1表：
2.表的联结
表的联结是通过表和表的关系，将两个表合并在一起的操作，常用的联结有以下几种类型：
交叉联结（cross join）、内联结（inner join）、左联结（left join）、右联结（right join）、全联结（full join）
2.1交叉联结cross join（笛卡儿积）
交叉联结是将表中的每一行都与另一个表中的每一行合并在一起。图中表1和表2交叉联结的过程是这样：表1的第一行分别与表2中的两行合并在一起，这样就产生了两行数据，表1的第二行分别与表2中的两行合并在一起，这样又产生了两行数据，同理表1的第三行也产生两行数据。交叉联结结果的行数是两个表行数的乘积，图中产生3*2=6行数据。如扑克牌是交叉联结，13*4+2大小王=54张牌
交叉联结是其他联结的基础。
2.2内联结inner join
内联结是查找出同时存在于两张表中的数据。学生表和成绩表是按学号产生了匹配关系，两个表都有0001，如下图，内联结运行：第一步两个表通过学号内联结，会将两个表中都有的学号所在的行取出来，第一个表0001有1行，第2个表学号0001有两行，第二步将两个表取出的数据用交叉联结合并，将表中的每一行都与另一个表中的每一行两两合并在一起，这里将学生表的0001这一行分别和成绩表中的两行合并，最终产生两行数据，合并后的表有了两个表的全部字段信息。
2.3左联结left join
将左侧表中的数据全部取出来，下图中将左侧是表作为主表，第一步将主表中的数据全部读取出来，右表中只选出和左边表相同学号的行，也就是学号0001的所在的两行，第二步进行交叉联结合并两表，学号0002在右边没有对应的行，这一行在右表中对应的列为空值。
2.4右联结right join
将右侧表中的数据全部取出来，右联结如何运行：第一步通过学号右联结，会将右侧表的数据全部取出来，左边表只选择右边表相同学号的行，学号0001的行，第二步交叉联结合并，由于0005左边没有对应的行，这列对应的值为空。
2.5全联结full join（mysql不支持，了解即可）
返回左表和右表所有行，当某行和另一个表中有匹配时候，两行进行合并，没有匹配时候，另一个表中对应的行用空值来填充，在图片中多了俩行空值，分别是左联结结果和右联结结果的空值行，这样两个表的数据都在结果中了。
几种联结图示总结：
3.联结应用案例
例1：查询所有学生的学号、姓名、选课数、总成绩
sql解决问题三步曲：翻译大白话、写出分析思路、写出对应的sql子句
翻译大白话：
写出分析思路：
写出对应的sql语句：
例2：查询平均成绩大于85的所有学生的学号、姓名和平均成绩
例3：查询所有学生的选课情况：学号，姓名，课程号，课程名称
4.case表达式
4.1 什么是case表达式
case中的when 的判断表达式若符合条件 则运行then，不符合条件运行下一个when……
直到最后when子句没有找到符合条件表达式，那么执行else子句
4.2 如何使用case表达式
例1.查询出每门课程的及格人数和不及格人数
例2：使用分数段[100-85]，[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数，课程号和课程名称
4.3 case表达式有什么用
当有多种情况需要条件判断时候，就可以使用case表达式了
4.4 case表达式注意事项
else子句尽量不要省略，end一定不能省；case可以放在sql任意子句里面
sqlzoo练习：
网址：
The JOIN operationold.sqlzoo.net这部分题目使用的是‘2012年欧洲足球锦标赛’数据库里的表。
1.比赛信息表game
id比赛编号，mdate比赛日期， stadium比赛地点， team1对战双方（球队编号1）， team2对战双方（球队编号2）
2.进球信息表goal
matchid比赛编号，teamid球队编号， player进球球员的姓名，gtime多长时间进的球（从开始比赛到进球多长时间，单位：分钟）
3.球队信息表eteam
id球队编号（联结2） teamname 球队名称 coach教练
三张表的关系：比赛信息表game（id比赛编号）和进球信息表goal（matchid比赛编号）通过‘比赛编号’联结，进球信息表goal（teamid球队编号）球队信息表eteam（球队编号id）通过‘球队编号’联结。
共1-13道练习题：
1. 在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）
2. 在比赛信息表（game）查找比赛编号1012的信息
3. 查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）
【分析】：两个表如何联结？通过比赛编号；哪一种联结？需要两个表中共同部分，内联结
4. 查找姓名中以Mario开头的进球球员（goal表），符合条件球员参加比赛的对战双方（game表）
【分析】：哪一种联结？需要两个表中共同部分，内联结
5. 查找进球球员的姓名、球队编号（goal表）、教练（eteam表）、多长时间进球（goal表）。要求多长时间进球<=10分钟
【分析】：哪一种联结？需要两个表中共同部分，内联结
6. 'Fernando Santos'作为教练的比赛日期（game表），球队名有哪些（eteam表）
【分析】：哪一种联结？需要两个表中共同部分，内联结
7. 在比赛地点'National Stadium, Warsaw'有哪些进球球员？（game、goal表）
【分析】：哪一种联结？需要两个表中共同部分，内联结
（以下题目难度增加：）
8.射入德国球门的球员姓名
【分析】：内联结的结果中入门球员有重复值，用distinct去掉重复值
9. 查找出球队名称（eteam表），和每个球队进球人数（goal表）
【分析】：哪一种联结？需要两个表中共同部分，内联结；
inner join之后再进行group by 分组统计。
10. 查找出所有比赛地点（game表），每个比赛地点的进球数（goal表）
【分析】：联结方式是什么？要查出比赛信息表game中全部比赛地点，所以是左联结；
left join之后再进行group by 分组统计
11. 查找出有波兰球队'POL'参加的比赛编号（goal表），比赛日期（game表），对应这场比赛的进球数
【分析】哪一种联结？需要两个表中共同部分，内联结；
inner join之后再进行group by 分组统计
12. 对于德国队'GER'得分的每场比赛，显示比赛编号（goal表），比赛日期（game表）和'GER'得分的进球数
【分析】哪一种联结？需要两个表中共同部分，内联结；
inner join之后再进行group by 分组统计
13. 查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）
【分析】：统计每场比赛（game）信息：左联结；
多种情况需要条件判断，使用case语句来统计
10题、13题要查询比赛信息表game中全部比赛，所以是左联结
扫一扫
1131
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
