mysql内连接查询练习_SQL练习4-多表查询

1 表的加法

1)union 自动去重

2)union all 保留重复

2 表的联结

1)内联结 inner join

根据关系(on)内容取交集：同时存在于两个表中的数据。

2)左联结 left join

取左侧数据，右侧选择与左侧一样的数据，通过where语句选择是否包含交集部分。

3)右联结 right join

取右侧数据，左侧选择与右侧一样的数据，通过where语句选择是否包含交集部分。

4)全联结 full join(mySQL不支持)

3 联结应用案例

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

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

3)查询学生的选课情况：学号、姓名、课程号、课程名称(三表联结)

4 case表达式

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

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

5 sqlzoo 练习

1) 列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的

select matchid,player

from goal

where teamid = 'GER';

2) 顯示賽事1012的 id, stadium, team1, team2

select id,stadium,team1,team2

from game

where id = 1012;

3) 顯示每一個德國入球的球員名，隊伍名，場館和日期

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

from goal as a left join game as b

on a.matchid = b.id

where a.teamid = 'GER';

4) 列出球員名字叫Mario 有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

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

from game as a inner join goal as b

on a.id = b.matchid

where b.player like 'Mario%';

5) 列出每場球賽中首10分鐘gtime<=10有入球的球員player, 隊伍teamid, 教練coach, 入球時間gtime

分析思路：每场球赛，首十分钟入球：goal表，条件：gtime<10 ;

入球的球员：goal表，player

入球队伍ID：goal表，teamid

教练：eteam表，coach

表联结：goal表、eteam表，通过teamID和ID，内联结

select b.player,b.teamid,c.coach,b.gtime

from goal as b inner join eteam as c

on b.teamid = c.id

where b.gtime <= 10;

6) 列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。

分析思路：'Fernando Santos'教练：eteam表，条件：coach='Fernando Santos'；

参赛日期：game表，mdate；

队伍名：eteam表，teamname；

表联结：game表、eteam表，通过team1与ID，实现内联结

select a.mdate,c.teamname

from game as a inner join eteam as c

on a.team1 = c.id

where c.coach = 'Fernando Santos';

7) 列出場館 'National Stadium, Warsaw'的入球球員。场馆：game表，条件：stadium='National Stadium, Warsaw'；

入球球员：goal表，player；

表联结：game、goal，通过id、matchid，实现内联结

select b.player

from game as a inner join goal as b

on a.id = b.matchid

where a.stadium = 'National Stadium, Warsaw';

8) 列出全部賽事，射入德國龍門的球員名字。球员名字：goal表，player；

表联结：game、goal，通过id、matchid，实现内联结；

射入德国龙门：条件：1)goal中teamid不等于'GER'；2)对应game中 team1 or team2 有'GER'；

distinct去掉重复球员

select distinct b.player

from game as a inner join goal as b

on a.id = b.matchid

where b.teamid <> 'GER'

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

9) 列出隊伍名稱teamname和該隊入球總數队伍名称：eteam表，teamname

入球总数：goal表，count(gtime)

每个队伍：分组，goal表的teamid

表联结：goal、eteam，通过teamid、id，实现内联结

select c.teamname,count(b.gtime)

from goal as b inner join eteam as c

on b.teamid = c.id

group by c.teamname;

10) 列出場館名和在該場館的入球數字。场馆名：game表，stadium

入球数字：goal表，count(入球时间)

每个场馆入球数字：分组：game表stadium

表联结：game、goal表，通过id、matchid，实现左联结(所有场馆)

select a.stadium,count(gtime)

from game as a left join goal as b

on a.id = b.matchid

group by a.stadium;

11) 每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字每一场'POL'参与的赛事：game表，team1='POL' 或 team2='POL'

赛事编号：goal表，matchid

日期：game表，mdate

每场赛事入球数字：根据matchid、mdate分组，计算入球总数count(gtime)

表联结：game、goal，通过id、matchid，实现内联结

select a.id,a.mdate,count(b.gtime)

from game as a inner join goal as b

on a.id = b.matchid

where a.team1='POL' or a.team2='POL'

group by a.id, a.mdate;

12) 每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字赛事编号：goal表，matchid

日期：game表，mdate

德国入球数字：条件筛选德国队伍，teamid = 'GER'；根据matchid分组，计算入球总数count(gtime)

表联结：game、goal，通过id、matchid，实现内联结

select a.id, a.mdate, count(gtime)

from game as a inner join goal as b

on a.id = b.matchid

where b.teamid = 'GER'

group by a.id, a.mdate;

13) 列出每一场比赛以及每支球队的进球数，结果按照mdate, matchid, team1 and team2排序每一场比赛每支球队的进球数：每场比赛有两支球队，team1、team2，分别汇总team1、team2 进球数；

- 构想联结后的表格，根据赛事分组后，当team1=teamid时，表示team1在该场赛事进球1次，使用case语句计数求和，得到该场比赛team1的进球总和：

sum(case when team1=goal.matchid then 1 else 0 end) as 'score1'

sum(case when team2=goal.matchid then 1 else 0 end) as 'score2'

- 每一场比赛：按赛事id、mdate、team1、team2分组

表联结：game、goal，通过id、matchid，实现左联结(所有赛事)

排序：order by mdate, matchid, team1 and team2

select a.mdate,a.team1,

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

team2,

sum(case when a.team2=b.teamid then 1 else 0 end) as 'score2'

from game as a left join goal as b

on a.id = b.matchid

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

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