两个表查询有重复_Part 5 多表查询

一、表的加法

UNION

表的加法会把两个表中重复的数据删除

UNION ALL

如果想要保留两个表中重复的行，使用UNION ALL

二、表的联结

表与表之间是通过对应的列产生关系的，联结是通过表之间的关系将表合并在一起的操作

常用的联结有交叉联结cross join、内联结inner join、左联结left join、右联结right join、全联结full join

1.交叉联结cross join

交叉联结也称笛卡尔积，将表中的每一行都与另一个表中的每一行合并在一起，联结后的行数是两张表的行数的乘积

日常生活中扑克牌就是典型的交叉联结

13*4=52

交叉联结在日常生活中运用较少，原因是结果太多，运算麻烦

2.内联结inner join

是同时找出存在于两张表的数据

运行：

如上图，找出两个表都包含的行，再对行进行交叉联结

可以看出，列出了每个学生每门课程的成绩

选出想要的列

3.左联结left join

将左侧表的数据全部取出来

运行：

左联结将左侧的表作为主表，左侧表的数据全部读取出来，右侧的表只选出左侧表含有的数据，再将两个表取出的数据进行合并（交叉联结），如果左表的行在右表中没有对应的行，那么合并后该行的右表的列为空值（如图0002）

这里右表也就是成绩表中没有0004的课程号和成绩，因此显示空值

在左表中去掉右表的部份，即只要左表比右表多出来的部份

4.右联结right join

将右侧表的数据全部取出来

运行：

右侧表为主表，右侧表取出全部数据，左侧表取出右侧表有的数据，再进行交叉联结，右侧表有但左侧表没有的显示为空值，如图0005

在右表中去掉左表包含的数据

5.全联结full join

会返回左表和右表的所有行，当某一行和另一行相同时会进行匹配，如果没有匹配的行，没有匹配数据的表的地方用空值填充

MySQL不支持全联结

6.总结

三、联结应用案例

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

分析思路：

通过score表找出每个学生对应的学号、选课数、总成绩

联结student表，按学号找到学生的姓名

这里，因为题目是查询所有学生，我们以学生表student中的学生为准，要查到全部学生，应该使用左联结

简便写法：

这里使用左联结，保留左表的全部数据，也就是学生表的全部数据，因此分组以左表的分

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

分析思路：

通过score表找到平均分大于85的学生的学号和平均成绩

联结表student找到对应学生姓名

图中，方法二思路是先找到每个学生的学号、平均成绩与学生表联结，在此基础上再使用having条件

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

分析思路：

学号、姓名在学生表中

课程号在成绩表中

课程名称在课程表中

四、case表达式

case表达式：条件判断的函数，用来判断每一行是否满足某个条件

例1：判断每个学生成绩是否及格

只要符合条件，直接到end，不会进行下面的when和else

case中的子句不管有多少，每次运行只会返回一个结果

例2：查询每门课程的及格人数和不及格人数

CASE WHEN `成绩`>=60 THEN 1 ELSE 0 END 意思为如果成绩大于等于60则算为1（1人）否则为0，这样之后再运行SUM算出的就是及格人数，因为及格的都为1，不及格的都为0

运行过程：

注意事项

1.else子句可以不写，默认为空值

2.end不能省略

3.case表达式可以写在SQL的任意子句里面

4.当有多种情况需要条件判断时使用case表达式

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

分析思路：

多种情况条件判断需要使用case表达式

这里查询结果要求显示课程名称，需要课程号课程名称对应，这里加上或者删去课程名称是对分组结果没有影响的，因此为了显示课程名称将课程名称加在分组中，如果课程名称和课程号不是一对一的关系，这里加上就会改变分组结果不能随便加

当group by中有多个列进行分组的时候，只有这几个列的值全部相同时才算一组

五、SQLZOO

1.查找姓名中以Mario开头的进球球员，符合条件球员参加比赛的对战双方

查找出进球球员姓名（进球信息表goal：进球球员姓名player），比赛的对战双方（比赛信息表game：对战双方team1,team2）

加入条件：进球球员姓名中以Mario开头，模糊查询：like 'Mario%'

加入条件使用WHERE

2.射入德国球门的球员姓名

这里要找出对战双方有一方是德国的，并且被进了球

1）where子句中的条件：

主队是德国或者客队是德国，比如

德国 和 A对比赛（德国是主队），进球的是A队

A队和德国比赛（德国是客队），进球的是A队

所以条件是(b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER')

2）内联结的结果中入门球员有重复值，用distinct去掉重复值

3.查找出球队名称，和每个球队进球人数

找到球队名、进球数量，按球队名分组

这里进球数统计使用count()函数

4.查找出所有比赛地点，每个比赛地点的进球数

这里因为是要全部的比赛地点，因此比赛地点的表的数据是需要全要的，因此使用左联结

5.查找出有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数

有波兰队参加，team1 = 'POL' OR team2 = 'POL'

6.查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）

当比赛信息表game中的球队编号(team1)也出现在进球信息表(goal)中时，表示这个球队进球了，使用case语句来统计