mysql五表查询_5、MySQL多表查询

1、union 和 union all

还记得高中课本上学到的 交集 和 并集 的概念吗？

union 就是并集的概念，而下面即将要提到的join，就是交集的概念。区别：

union：合并时，会自动删除重复行

union all：就是无脑拼接，不去重

select * from student1

union

select * from student2

2、笛卡尔积(交叉连接)

笛卡尔积是没有连接条件返回的结果。

2行3列的数据，和3行2列的数据，进行笛卡尔积后，产生一个6×6的数据表。

这就是笛卡尔积。

一般不太建议使用笛卡尔积，因为非常好资源，比如2个100×100的表进行笛卡尔积，你会得到一个10000×10000的数据表。

3、各种 join

join就是高中数据中，交并集中的交集概念。

基本语法就是 A join B on A.key = B.key，这里 on 后面就是返回2个表的关联条件；

此处多提一句，通过建立外键索引，并使用外键进行关联，可以提升SQL的运行效率；

在多提一句，建立索引对查询是有利的，但对于维护数据库(增删改)是有弊的。一张图看懂SQL join 的逻辑：

join的逻辑上图已经讲的很清楚了，重点是多练习；4

该题的思路2(有点意思昂)：

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 课程号;

该题的解法2：

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 a

right join course b

on a.课程号 = b.课程号

group by a.课程号,b.课程名称;

练习题：原网页的问题描述简直一言难尽。。。

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

SELECT matchid, player

FROM goal

WHERE teamid = 'GER';

2. 在比赛信息表(game)查找比赛编号1012的信息

SELECT id, stadium, team1, team2

FROM game

where id = 1012;

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

SELECT b.player, b.teamid, a.stadium, a.mdate

FROM game a

join goal b ON a.id = b.matchid

where teamid = 'GER';

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

select a.player, b.team1, b.team2

from goal a

join game b on a.matchid = b.id

where player like 'Mario%';

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

SELECT player, teamid, coach, gtime

FROM goal a

join eteam b on a.teamid = b.id

WHERE gtime <= 10;

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

select b.mdate, a.teamname

from eteam a

join game b on a.id = b.team1

where coach = 'Fernando Santos';

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

select b.player

from game a

join goal b on a.id = b.matchid

where stadium = 'National Stadium, Warsaw';

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

select distinct b.player

from game as a inner join goal as b on a.id = b.matchid

where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');

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

select b.teamname, count(teamid)

from goal a

join eteam b on a.teamid = b.id

group by teamid

order by teamname;

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

select a.stadium, count(b.player) '进球数'

from game a

left join goal b on a.id = b.matchid

group by a.stadium;

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

select a.id, a.mdate, count(b.player) '进球数'

from game a

left join goal b on a.id = b.matchid

where team1 = 'POL' or team2 = 'POL'

group by a.id;

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

select a.id, a.mdate, count(b.player) '进球数'

from game a

left join goal b on a.id = b.matchid

where b.teamid = 'GER'

group by a.id

having count(b.player) > 0 ;

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

select a.mdate,

a.team1,

sum(case when a.team1 = b.teamid then 1 else 0 end) '得分1',

a.team2,

sum(case when a.team2 = b.teamid then 1 else 0 end) '得分2'

from game a

left join goal b on a.id = b.matchid

group by a.mdate, a.team1, a.team2

order by a.mdate asc, a.team1, a.team2;

总结

整体来说，多表连接查询这部分，最重要的是：

一定要熟悉各个表之间的关系！

一定要熟悉各个表之间的关系！

一定要熟悉各个表之间的关系！

只要掌握join的逻辑，并且熟悉表之间的关系，就不会很难！