156、MySQL入门（六）：JOIN 操作

Sqlzoo习题练习：JOIN

习题链接：<u>http://sqlzoo.net/wiki/The_JOIN_operation</u>

下面是JOIN 习题中要用到数据库中的三张表格game、goal、eteam以及三张表格之间的关系。数据库的表格贮存了在波兰 Poland 和乌克兰 Ukraine的欧洲国家杯2012的赛事和入球资料。 1.表格game.png 2.表格goal.png 3.表格eteam.png 4.三张表格之间的关系.png

下面为JOIN 习题内容：

--#1
/*
列出赛事编号matchid 和球员名 player ,该球员代表德国队Germany入球的。要找出德国队球员，要检查: teamid = 'GER'
*/
SELECT matchid,player FROM goal 
  WHERE teamid = 'GER';

--#2
/*
由以上查询，你可见Lars Bender's 于赛事 1012入球。.现在我们想知道此赛事的对赛队伍是哪一队。
留意在 goal 表格中的栏位 matchid ，是对应表格game的栏位id。我们可以在表格 game中找出赛事1012的资料。
只显示赛事1012的 id, stadium, team1, team2
*/
SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012;

在上面的例子中，我们可以利用JOIN来同时进行以上两个步骤。
SELECT *
FROM game JOIN goal ON (id=matchid)
语句FROM 表示合拼两个表格game 和 goal的数据。语句 ON 表示如何找出 game中每一列应该配对goal中的哪一列 -- goal的 id 必须配对game的 matchid 。 简单来说，就是
ON (game.id=goal.matchid)
知识点：
join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
Join 和 Key
有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。

数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
下面列出了可以使用的 JOIN 类型，以及它们之间的差异。

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

--#3
/*
利用JOIN来显示每一个德国入球的球员名，队伍名，场馆和日期(数据来自goal表格和game表格)。
*/
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid = 'GER';

--#4
/*
使用上题相同的 JOIN语句，
列出球员名字叫Mario (player LIKE 'Mario%')有入球的 队伍1 team1, 队伍2 team2 和 球员名 player
*/
SELECT team1,team2,player
FROM game JOIN goal ON (id = matchid)
WHERE player LIKE 'Mario%';

--#5
/*
表格eteam 贮存了每一国家队的资料，包括教练。你可以使用语句 goal JOIN eteam on teamid=id来合拼 JOIN 表格goal 到 表格eteam。
列出每场球赛中首10分钟gtime<=10有入球的球员 player, 队伍teamid, 教练coach, 入球时间gtime 
*/
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam ON (teamid=id)
WHERE gtime<=10;

--#6
/*
要合拼JOIN 表格game 和表格 eteam，你可以使用
game JOIN eteam ON (team1=eteam.id)
或
game JOIN eteam ON (team2=eteam.id)
注意栏位id同时是表格game 和表格 eteam的栏位，你要清楚指出eteam.id而不是只用id
列出'Fernando Santos'作为队伍1 team1 的教练的赛事日期，和队伍名。
*/
SELECT mdate, teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach = 'Fernando Santos';

--#7
/*
列出场馆 'National Stadium, Warsaw'的入球球员。
*/SELECT player
FROM game JOIN goal ON (game.id=goal.matchid)
WHERE stadium = 'National Stadium, Warsaw';

--#8
/*
只列出全部赛事，射入德国龙门的球员名字。
提示：找非德国球员的入球，德国可以在赛事中作team1 队伍１（主）或team2队伍２（客）。 你可以用teamid!='GER' 来防止列出德国球员。 你可以用DISTINCT来防止球员出现两次以上。
*/
SELECT DISTINCT goal.player
FROM game JOIN goal ON (goal.matchid = game.id)
WHERE (goal.teamid!='GER')
AND (game.team1='GER' OR game.team2='GER');

--#9
/*
列出队伍名称 teamname 和该队入球总数
*/
SELECT eteam.teamname, COUNT(goal.gtime)
FROM eteam JOIN goal ON (eteam.id=goal.teamid)
GROUP BY eteam.teamname;

--#10
/*
列出场馆名和在该场馆的入球数字。
*/
SELECT game.stadium, COUNT(goal.gtime)
FROM game JOIN goal ON (game.id=goal.matchid)
GROUP BY game.stadium;

知识点：AS的用法
AS可以为列名称和表名称指定别名
表的 AS 语法
SELECT column_name(s)
FROM table_name
AS alias_name

列的 AS 语法
SELECT column_name AS alias_name
FROM table_name

--#11
/*
每一场波兰'POL'有参与的赛事中，列出赛事编号 matchid, 日期date 和入球数字。
*/
SELECT goal.matchid,game.mdate, COUNT(goal.teamid) AS balls
FROM game JOIN goal ON (goal.matchid = game.id)
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY goal.matchid,game.mdate;

--#12
/*
每一场德国'GER'有参与的赛事中，列出赛事编号 matchid, 日期date 和德国的入球数字。
*/
SELECT goal.matchid,game.mdate, COUNT(goal.teamid) AS GER_balls
FROM game JOIN goal ON (goal.matchid = game.id)
WHERE goal.teamid = 'GER'
GROUP BY goal.matchid,game.mdate;

知识点：CASE WHEN两种用法
Case具有两种格式。简单Case函数和Case搜索函数。
第一种 格式 : 简单Case函数 :
格式说明
　　　　case 列名
　　　　when 条件值1 then 选择项1
　　　　when 条件值2 then 选项2.......
　　　　else 默认值 end
第二种 格式 :Case搜索函数
格式说明
　　　　case
　　　　when 列名= 条件值1 then 选择项1
　　　　when 列名=条件值2 then 选项2.......
　　　　else 默认值 end
提示:通常我们在写Case When的语句的时候,会容易忘记 end 这个结束,一定要记得加上。
比较: 两种格式，可以实现相同的功能。
简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

--#13
/*
List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.
mdate   team1   score1  team2   score2
1 July 2012 ESP 4   ITA     0
10 June 2012    ESP 1   ITA 1
10 June 2012    IRL 1   CRO 3
...
Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2. 
*/
SELECT game.mdate,
      game.team1,
      SUM(CASE WHEN goal.teamid=game.team1 
          THEN 1 
          ELSE 0 
          END) AS score1,
      game.team2,
      SUM(CASE WHEN goal.teamid=game.team2 
          THEN 1 
          ELSE 0 
          END) AS score2
FROM game LEFT JOIN goal ON (goal.matchid = game.id)
GROUP BY game.mdate,game.team1,game.team2
ORDER BY game.mdate,goal.matchid,game.team1,game.team2;