MYSQL基础练习——SQL ZOO习题

任高锋

喜欢折腾，偏爱数据，大数据开发和分析我都要

首先呢来张图，说明下SQL ZOO主要的题目都已经完成啦，亲自练习一遍题目后，觉得部分填空题比较具有代表性，可以分析总结出一些精髓内容。以下是各关卡部分填空题目、答案、思路总结。

SELECT basics:

本节题目都比较简单，考察where语句中 = ，in ， between三种限定方式用法。

SELECT from world:

表world

本节题目整体思路和语句比较容易理解，主要是对where语句中限定条件增加一些限定方式。

比较运算符" <,<=,>,>=,=,!=,<>" 的应用

算数运算符"+,-,*,/"的应用

操作符 like 配合通配符"%(多个)，_(单个)"的应用

逻辑运算符"and","or","not"的应用

函数Round(参数，保留小数位数) , length(参数) , left(字符串 ,左起保留字符个数) 的使用。

第13题：

Find the country that has all the vowels（a,e,i,o,u） and no spaces in its name.

题目解释：找出名字包含有所有元音字母并且名字中间无空格的国家。

题意分析：SELECT 查询name，限制条件为包含元音字母， 但对其顺序无要求 ，因此我们将其拆解为单独的5个并列关系的like '%a%' 语句。

SELECT name FROM world
WHERE name like '%a%' AND name like '%e%' 
AND name like '%i%' AND name like '%o%' 
AND name like '%u%' AND name not like '% %' ;

SELECT from Nobel Tutorial:

表 Nobel

本节题目中简单部分为where语句中多个限制条件同时要求and，或者要求or的用法，重要的是把思路理顺，逻辑清晰。

复杂部分11/12题是考察特殊字符如何输入，此部分可能需学习了解符号编码规则，题目中可以使用通配符_代替快速查询答案。13题使用了降序排序order by 字段 DESC。

第14题

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

题目解释：展示1984年的获奖者，按照科目、获奖者名称排序。并且将化学和物理学奖获得者排在最后。

题意分析：SELECT查询 winner,subject，限制条件为yr=1984,order by 科目、获奖者，化学物理排在最后是难点，采用逻辑值0,1排序，方法为subject in（ 'Chemistry'，‘Physics’），不在即0，在即1，默认升序排列。

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner

SELECT IN SELECT：

表 world

本节题目主要是应用子查询，即从表中依靠一定条件查询出单个值或多个值，然后以此作为新查询的条件限定值或查询范围等，从而形成加强筛选的功能。

一层查询select后的 单个值常 用于二层查询where子句中比较运算符（<、>）后、select语句函数内。

一层查询select后的 多个值常 用于二层查询where子句in后、比较运算符（<、>）加ALL内。

特别地，当一层查询与二层查询同一字段存在相关限制条件时，如题目7之后的in each continent，要求一层查询和二层查询在continent字段相同，此时需对表做别名区分。

子查询的关键是捋顺思路，区分哪部分作为一层查询，哪部分作为二层查询，两者之间的关系是怎样的。当然，如果需要三层查询，可以把一二层查询的结果作为新的一层查询。

第10题

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

题目解释：查询国家名称和洲名称，要求国家人口数量大于所在洲其他国家人口的3倍。

题意分析：一层查询找出各个洲内的国家三倍人口数量的集合，SELECT 3*population，二层查询找出单个国家人口大于所有其他国家三倍人口数量，以洲作连接，SELECT name,continent

PS：ALL函数前不可做算数运算。

SELECT name ,continent FROM world x
WHERE population > ALL (SELECT 3*population FROM world y
                       WHERE x.continent =y.continent
                       AND x.name!=y.name)

SUM AND COUNT

表 world

本节题目主要针对聚合函数、分组、分组后筛选的应用，聚合函数包括SUM求和、COUNT计数、AVG平均值、MAX最大值、MIN最小值，分组即GROUP BY 字段，分组后筛选HAVING 内容不多，但需融会贯通，运用自如。

GROUP BY 后的字段不得少于SELECT中查询的表的原始字段。

HAVING 后的条件必须是对聚合函数的要求。

八道题目都非常简单，以第八题为例。

List the continents that have a total population of at least 100 million.

题目解释：列出拥有总人口超过1亿的洲名称

题意分析：SELECT查询的是洲名称，限制条件是洲的总人口超过一亿，属于分组后筛选，采用HAVING语句加SUM函数。

SELECT continent 
FROM world
GROUP BY continent
HAVING SUM(population) >=100000000

JOIN

本节题目主要是表与表之间的连接，依靠join...on将A表与B表进行连接，通过on后的字段对应，利用A表中的条件筛选，查询B表中的字段。

join默认是内连接inner join，即只保留两张表完全匹配的结果。

连接过程中存在字段名称相同的情况时，需要在字段前附加表名。

当三个或三个表以上连接时，只需在前一个join 表 on 连接字段语句后继续添加 join 表 on 连接字段即可。

第八题

show the nameof all players who scored a goal against Germany.

题目解释：展示所有在对阵德国时赢得进球的球员名字。

题意解释：SELECT查询球员名字，限制条件时对阵德国，并且进球，对阵德国的条件是在表game里，球员名字和赢得进球的条件是在表goal里，所以需要连接，连接条件为goal.matchid=game.id。

PS：存在同一球员进两次球情况，需使用DISTINCT去重

SELECT DISTINCT goal.player
FROM goal JOIN game
ON game.id=goal.matchid
WHERE (team1='GER' AND team2=teamid)
OR (team2='GER' AND team1=teamid)

第10题

Show the stadium and the number of goals scored in each stadium.

题目解释：展示体育场的名称和在每个体育场的进球数量。

题意分析：SELECT体育场名称和进球数量，名称即表game中stadium字段，进球数量需用COUNT函数对表goal中进球时间进行计数(一一对应的其他字段也可以）；查询字段分别在表game和表goal中，需要用join连接，连接条件为goal.matchid=game.id；题目中要求区分每个体育场，所以需要GROUP BY stadium。

SELECT stadium , COUNT(goal.gtime)
FROM goal JOIN game
ON goal.matchid=game.id
GROUP BY stadium

第11.12题类似满足相应的限制条件即可。

第13题

List every match with the goals scored by each team as shown.Sort your result by mdate, matchid, team1 and team2.

题目解释：如图中样式列出所有队伍在每一场比赛中的得分。并将结果按日期，比赛id，队伍1，队伍2分类

题意分析：SELECT查询比赛日期，队伍1，队伍1得分，队伍2，队伍2得分。表goal中记录着每场比赛的所有得分，所以想获得比赛中队伍1，队伍2各自的得分，需要使用CASE　WHEN...THEN...ELSE...END分情况计数，并求和；mdate、team1、team2在game表中，得分计数项在goal表中，所以需要join连接，连接条件为goal.matchid=game.id。

SELECT mdate,team1,SUM(CASE WHEN teamid=team1 THEN 1 
                       ELSE 0
                       END) AS score1,
team2,SUM(CASE WHEN teamid=team2 THEN 1 
          ELSE 0
          END) AS score2
FROM game LEFT JOIN goal
ON game.id=goal.matchid
GROUP BY mdate,team1,team2

PS :由于存在比赛中无进球的情况，即goal中无进球记录，game中有比赛记录的情况，所有需要对表game连接表goal时采用外连接LEFT JOIN，反过来表goal连接表game时采用外连接RIGHT JOIN。

MORE JOIN

本节主要是关于3个及以上表的连接，关键点为识别查询项，限制条件项等字段分别在哪个表内，找到连接条件movie.id=casting.movieid，actor.name=casting.actorid，然后将各表连接起来。

题目大同小异，将三个表连接起来后，针对要求，对各关键字段做限制条件。特别地，12题和15题有嵌套一处内查询，此处以12题为例。

第12题

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

题目解释：列出所有Julie Andrews参演过的电影，以及电影的主角。

题意分析：找出查询项、限制条件分别是哪些字段在哪些表内，SELECT查询电影名movie.title，主角actor.name ，限制条件主角casting.ord=1，Julie Andrews参演actor.name='Julie Andrews'；此处共用到movie，casting，actor三个表，连接条件为movie.id=casting.movieid，actor.name=casting.actorid；先找出'J'参演过哪些电影，再找出对应的英名和主角名。

SELECT title,name
FROM movie JOIN casting ON movie.id=movieid
           JOIN actor ON actorid=actor.id
WHERE movieid IN (SELECT movieid FROM casting
    JOIN actor ON actor.id=actorid
    WHERE name = 'Julie Andrews')
AND casting.ord=1

USING NULL

本节内容主要是对表格中存在NULL时，需要注意地方加以举例联系，如dept is NULL标识dept字段为空的状态，不可用'='符号；内连接对NULL值的舍弃，外连接对NULL值的保留；查询到NULL值时，如何对应统一输出新的字符（coalesce 或者case when）。

题目思路比较简单，针对NULL做舍弃，保留，作为限制条件等。此处以第6题为例，使用coalesce和case when两种方法。

第6题

Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.

题目解释：使用COALESCE和LEFT JOIN去显示教师和部门的名称。在无部门名称位置使用字符串'None'代替。

题意分析：SELECT查询teacher.name，dept.name，限制条件为保证显示所有教师名称，无部门名称的使用None代替。保证所有教师名称需要利用外连接，连接条件为teacher.dept=dept.id ，NULL值替代考虑COALESCE 或CASE WHEN。

SELECT teacher.name,COALESCE(dept.name,'None')
FROM teacher LEFT JOIN dept ON teacher.dept=dept.id

使用CASE WHEN的方法

SELECT teacher.name ,
CASE WHEN dept.name is NULL 
THEN 'None'
ELSE dept.name
FROM teacher LEFT JOIN dept ON teacher.dept=dept.id

SELF JOIN

本节内容主要是自连接：即对一个表设立别名，针对自身内容连接，设定表中某字段作为连接条件，区分其他字段作为不同条件下的查询。

self join在逻辑上与单表的select in select有一些类似，都是对同一表的内容进行两次或多次的使用筛选，经常包含对同一字段进行多条件限制筛选。两者之间经常可以替换使用，不过针对数据量较大，限制条件层次较多时，用自连接查询性能会更好一些。

通俗理解起来，可以把self join 想象为把表横向排列，以字段连接分类，把多个限制条件分解为对多个表的独立限制条件，整行筛选，最后剩下的记录会包含需要查询的内容，选择性查询表一、表二、表三等内容即可。

select in select 则可以想象为将表中的一部分内容单独查询拿出来，作为对原表下一次查询的条件限定值或查询范围，且两次是对同一字段进行限制（非同一字段可不使用），理解为对单个表的一层层筛选。

本节题目主要是描述车站编号、车站名称、路线、所属公司之间的关系，由stops站点、routes路线两张表组成。

第4题和第5题分别用两种方式查询出同时经过53号和149号站点的公交车编号及所属公司。

第4题

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2

思路分析：查询出经过149号站点或53号站点的线路编号及所属公司，由于没有去重，两者同时满足的线路会出现两次，利用此查询出结果。

第5题

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 and b.stop=149

思路分析：将两个表以所属公司和线路编号为连接条件进行自连接（类似于第4题中GROUP BY作用），可以把a、b两个表理解为同一线路的车运行两次的路线，第一次要求经过编号为53的车站，第二次要求经过编号为149的车站，同时满足即同时经过两个车站。

之前有说过，大多数self join 可与select in select互相替换转化，以下为采用select in select的形式。

SELECT company, num
FROM route
WHERE stop=53 
AND company in (SELECT company FROM route WHERE stop=149)
AND num in (SELECT num FROM route WHERE stop=149)

查询满足经过站点149的company，num，作为对原表的company，num的限制范围，并补充上满足经过站点53的要求，即可完成对同时经过149和53站点线路的查询。

后面的题目大多是增加了表stop和route之间的连接，调整限制条件为站名进而转换到车站编号，在转换到4/5题中的公司、线路编号。需要注意的就是对route自连接形成的表route a ，表route b，在用stop与其内连接时也要有对应的stop a ，stop b。

第10题

Find the routes involving two buses that can go from Craiglockhart to Sighthill.

Show the bus no. and company for the first bus, the name of the stop for the transfer,

and the bus no. and company for the second bus.

题目解释：找出从Craiglockhart 到Sighthill 包含两趟公交的路线。显示第一趟公交的编号和所属公司，换乘的站点，第二趟公交的编号和所属公司。

题意分析：设定经过Craiglockhart和bus1.stop的路线表为bus1，经过bus2.stop和Sighthill的路线表为bus2，其中作为中转站点bus1.stop=bus2.stop。SELECT查询bus1.num, bus1.company, name, bus2.num, bus2.company；其中表route中stop为id，变换为站点名称还需要与表stop之间的连接。

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company 
FROM (SELECT start1.num, start1.company, stop1.stop 
FROM route AS start1 JOIN route AS stop1 
ON start1.num = stop1.num 
AND start1.company = stop1.company 
AND start1.stop != stop1.stop 
WHERE start1.stop =(SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1 
JOIN (SELECT start2.num, start2.company, start2.stop 
FROM route AS start2 
JOIN route AS stop2 
ON start2.num = stop2.num