JOIN 表联结
这次练习处理的是两个以上表格数据，来自 2012 年波兰和乌克兰联合举办的欧足联男足锦标赛的所有比赛和进球的记录。对应数据的 mysql 版本传送门： http://sqlzoo.net/euro2012.sql
知识点：SUM /COUNT/
Aggregates
1.统计世界总人口
例子：查询——所有姓式是「Bender」的球员的所有进球记录。
SELECT * FROM goal
WHERE player LIKE '%Bender'
* 指的是列出表格的所有列，更简洁地语法代替——matchid, teamid, player, gtime
练习：查询—— 德国队的所有进球队员及匹配的比赛 id
SELECT matchid, player FROM goal
WHERE teamid = 'GER'
根据之前的查询，我们知道，「Lars Bender」在 id 为 1012 的比赛中进球。
查询：这个比赛的对阵双方是什么球队？
SELECT id,stadium,team1,team2
FROM game
WHERE id='1012'
3.JOIN
FROM：从 game 表合并，合并过来的表是 goal
ON：根据哪一行 game表是根据 goal 的哪一行来合并
更具体的写法是game.id=goal.matchid
查询：德国每个进球所对应的球员、球队 id、体育场和比赛日期。
SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER'
查询：以「Mario 」为名字开头的每个进球球员所对应的球员&比赛队伍
SELECT team1, team2, player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'
SELECT player, teamid ,coach, gtime
FROM goal JOIN eteam on teamid=id
WHERE gtime<=10
SELECT mdate, teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach='Fernando Santos'
SELECT player
FROM game JOIN goal ON(id=matchid)
WHERE stadium='National Stadium, Warsaw'
以下是更难的问题:
SELECT DISTINCT(player)
FROM game JOIN goal ON (matchid = id)
WHERE (team1='GER' OR team2='GER')
AND teamid != 'GER'
SELECT teamname, COUNT(player)
FROM eteam JOIN goal ON (id=teamid)
GROUP BY teamname
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON (matchid = id)
WHERE (teamid='GER')
GROUP BY matchid,mdate
知识点：CASE
CASE语法能够让你根据不同的条件，返回不同的值。
如果没有匹配的条件，也没有ELSE,将会返回RETURN
CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE def_value
END
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON (matchid = id)
WHERE (teamid='GER')
GROUP BY matchid,mdate
SELECT name, population
,CASE WHEN population<1000000
THEN 'small'
WHEN population<10000000
THEN 'medium'
ELSE 'large'
END
FROM bbc
SELECT mdate,
team1,
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
FROM game JOIN goal ON matchid = id
SELECT stadium, COUNT(player)
FROM game JOIN goal ON (id=matchid)
GROUP BY stadium
