mysql多表联合查询_MySQL数分：多表查询

上次我们对MySQL的复杂查询进行介绍，包括前期的课程也都是对一个表进行操作。本节，我们继续对MySQL基础知识深入学习：如何对多表进行查询？

目录：

• 表的加法
• 表的联结
• case表达式
• 实战案例

本文我们在前期的例子中先增加一个course_add表。

表的加法

我们现在有两张课程表分别是课程表course 和 课程表course_add，现在我们想要知道所有的课程都有哪些，我们需要把两张表相加并且不显示重复项，这里我们使用union操作符：

union操作符可以合并多个select语句的结果集。

需要注意的2点：

union内部的select语句必须有相同数量的列；列名顺序必须相同且数据类型必须相似。

一般默认union操作符相加的是不同的值，如果允许重复需要全部显示，可以使用union all操作符：

我们实例来看：

-- 把课程表course 和 course_add 结合,不允许重复

-- 把课程表course 和 course_add 结合,允许重复

表的联结

我们现在想要知道每个学生每门功课的成绩，我们需要把学生表student和成绩表score中获取结果，得到一个更加完整的表，从完整表中查询学生的成绩。这里我们介绍新的关键词——join。

join 用于根据多个表中的列之间的关系，从这些表中查询数据。

在我们的数据表实例中，各表之间都存在关系，是因为有主键key将这些表联系起来：

现在，我们把学生表student和成绩表score进行交叉联结：

-- 用join把学生表student和成绩表score交叉联结，显示学生成绩

我们看到，现在每个学生的“学号”“姓名”“课程号”和“成绩”就都显示出来了。

除了上述实例中的join联结，还有其他的联结方式，下面我列出所有的联结方式：

• inner join 内联结：返回两个表可匹配的行；
• left join 左联结：即使左表没有匹配，右表返回所有行；
• right join右联结：即使右表没有匹配，左表返回所有行；
• full join 全联结：只要其中某个表存在匹配九返回所有行；

接下来我们依次实例来说明：

为了可以更明显的显示结果，我们对student表和score表进行补充：

1）inner join 内联结

-- 用inner join把学生表student和成绩表score交叉联结，显示学生成绩

2）left join 左联结

-- 用left join把学生表student和成绩表score交叉联结，显示学生成绩

3）right join 右联结

-- 用right join把学生表student和成绩表score交叉联结，显示学生成绩

4)full join 全联结

-- 用full join把学生表student和成绩表score交叉联结，显示学生成绩

但是，你会发现，报错了···

别慌，这是因为MySQL没有full join这个东东~所以呢，如果你又特别想把两个表全部联结，可以使用union all，但是union又有两个需要注意的事项：“union内部的select语句必须有相同数量的列；列名顺序必须相同且数据类型必须相似”，所以我们需要分两个步骤：

student表的所有行，与score的非匹配部分的行相加，或许不好理解，我们看图：（绿色线条所在面积+灰色线条所在面积）

• ① 使用左联结 left join取绿色部分面积；
• ②使用右联结right union 条件为左列名为null的部分面积；
• ③ 使用union all 相加

/* ① 使用左联结 left join取绿色部分面积；

case表达式

先说一下case表达式的作用，就像Excel中的if语句和Python中的if···else···语句，case表达式是SQL中的逻辑判断语句。

举例来看：

① 我们想要查询出每门课程的及格人数和不及格人数：

翻译大白话：

• 定义条件：成绩>=60分及格，成绩<60分不及格；
• 按课程号进行分组，对分组结果的人数按照上一步的逻辑条件计数；

# 

② 我们对各课程的成绩按照[100-85（含）]、[85-70（含）]、[70-60（含）]、[60以下]分段，并统计各分段数人数和课程名

翻译大白话：

• 定义条件：成绩在100-85（含）为[100-85（含）]段，在85-70（含）为[85-70（含）]段，在70-60（含）为[70-60（含）]段，小于60就是[60以下]分段；
• 把成绩表score和课程表course交叉联结（右联结），显示课程号、课程名、和分数段；
• 按课程号、课程名进行分组，对分组结果的人数按照上上述的逻辑条件计数；

-- 对各课程的成绩按照[100-85（含）]、[85-70（含）]、[70-60（含）]、[60以下]分段，并统计各分段数人数和课程名

这么看来，多变联结是不是也蛮简单呢？

现在，我们就用几个实例来试试吧~

实战案例

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

翻译成大白话：

• 将学生表student和成绩表score交叉联结（左联结），显示学号、姓名、课程号、成绩；
• 按照学生号分组，对分组结果按照课程号计数；并对所有课程成绩求和；

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

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

翻译成大白话：

• 将学生表student和成绩表score交叉联结（左联结），显示学号、姓名、成绩；
• 按照学生号分组，对分组结果按照成绩求均值；
• 对所得结果指定条件要求平均成绩大于85分；

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

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

翻译成大白话：

• 将学生表student和成绩表score交叉联结（内联结），显示学号、姓名、课程号；
• 将上步的结果再与课程表course交叉联结（内联结），显示学号、姓名、课程号、课程名；

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

接下来的案例来自The JOIN operation，数据库里存了2012年欧洲杯赛事和入球信息：

三个表，分别是赛事表game、入球表goal、队伍表eteam，各边联系见下：

我们对下边的练习题一一解答：

1.在进球表（goal）中查找德国球队（teamid = 'GER'）进球的比赛编号（matchid），进球球员姓名（player）：

select 

2. 在比赛信息表（game）查找比赛编号1012的信息：

SELECT 

3.查找德国队进球球员姓名，球队编号（在进球信息表goal）, 比赛地点,比赛日期（在比赛信息表game）:

• 入球表goal和赛事表game交叉连结（内联结），显示球员姓名player、球队编号teamid、比赛地点stadium、比赛日期mdate；
• 对查找结果按照条件“德国GER”来筛选；

select 

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

• 入球表goal和赛事表game根据赛事编号进行交叉连结（内联结），显示符合条件的球队名team1、team2及球员姓名;
• 筛选条件为入球表中player以Mario开头；

select 

5.查找进球球员的姓名、球队编号、教练、多长时间进球。要求多长时间进球<=10分钟:

• 入球表goal和队伍表eteam根据队伍编号（teamid&id）交叉联结（内联结），显示球员姓名player、球队编号teamid、教练coach、入球时间gtime；
• 筛选条件为入球时间<=10；

select 

6.'Fernando Santos'作为team1教练的比赛日期，球队编号有哪些？

• 队伍表eteam和赛事表game通过队伍编号（team1&id）交叉联结（内联结），显示比赛日期mdate和球队编号teamname

select 

7.在比赛地点'National Stadium, Warsaw'有哪些进球球员？

• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（内联结），显示球员姓名player；
• 筛选条件为赛场stadium为'National Stadium, Warsaw'；

select 

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

• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（内联结），显示球员姓名player；
• 筛选条件为team1或者team2中有一个是“GER”；

select 

9.列出球队名称，和每个球队进球数；

• 进球表goal和队伍表eteam通过队伍编号（teamid&id）交叉联结（内联结），显示球队名称teamname和进球数（count(player)）
• 按球队名teamname分组，对分组结果的球员名出线次数计数；

select 

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

• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（内联结），显示赛场stadium和进球数（count(player)）；
• 按赛场stadium分组，对分组结果的球员名出线次数计数；

select 

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

• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（内联结），显示赛事编号matchid、比赛日期mdate、进球数（count(player)）；
• 按赛事编号matchid分组，对分组结果的球员名出线次数计数；
• 筛选条件为队伍team1或者team2中任意一个是'POL'；

select 

12. 对于德国队'GER'得分的每场比赛，显示比赛编号，比赛日期和'GER'得分的进球数：

• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（内联结），显示赛事编号matchid，比赛日期mdate、进球数（count(player)）；
• 按赛事编号matchid分组，对分组结果的球员名出线次数计数；
• 筛选条件为队伍tteamid为'GER'；

select 

13.查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数），并按照结果排序:

• 定义条件：如果team1的球队出线在进球表goal，team1 得分；如果team2的球队出线在进球表goal，team2 得分；
• 赛事表game与进球表goal通过赛事编号（matchid&id）交叉联结（左联结），显示比赛日期mdate、赛事编号matchid、team1、score1（team1得分）、team2，score2（team2得分）；
• 按赛事编号matchid、比赛日期mdate、team1、team2分组；
• 按赛事编号matchid、比赛日期mdate、team1、team2排序；

select 

好啦，本次分享就到这里啦~