sqlzoo刷题（部分题目含解题思路）

1.concat函数用法

（1）12、The capital of Mexico is Mexico City. Show all the countries where the capital has the country together with the word "City".

Find the country where the capital is the country plus "City".

查找首都是国家名字加上”城市”的国家。

SELECT name
FROM world
WHERE capital LIKE concat(name,'_City')

（2）13、Find the capital and the name where the capital includes the name of the country.

查找首都和国家，其中首都中包含国家名称。

SELECT capital, name 
FROM world
WHERE capital LIKE concat(name,'%')

（3）14、Find the capital and the name where the capital is an extension of name of the country.

You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.

查找首都和国家，其中首都是国家名称的延伸

SELECT capital, name 
FROM world
WHERE capital LIKE concat(name,'_%')

（4）Rreplace函数

For Monaco-Ville the name is Monaco and the extension is -Ville.

Show the name and the extension where the capital is an extension of name of the country.

查找首都是国家名称扩展的名称和扩展。

SELECT name, REPLACE(capital,name,'')
FROM world 
WHERE capital LIKE concat(name,'_%')

（5）与ROUND函数组合使用，显示百分比

显示欧洲每个国家的名称和人口。按德国人口的百分比显示人口。

2.having基于聚合结果进行筛选

（1）查询总人口数量至少为1亿（100000000）的大洲

select continent
from world
group by continent
having sum(population) >100000000

（2）标准语法

select 字段名1
from 表格名
[where 条件代码]
[group by 字段名1]
[having 条件代码]
order by 字段名 asc|desc

where和having同时用？如何区分？

答:where用在分组前的条件筛选，having与group by联合使用，用于对分组后的数据进行筛选

3.order by的用法

查询1984年所有获奖者的姓名和奖项。结果将诺贝尔化学奖和物理学奖排在最后，然后按照奖项排序，再按照获奖者姓名排序

select winner,subject
from nobel
where yr=1984
order by 
case when subject in('physics','chemistry') then 1
else 0
end,
subject,winner

先判断'physics','chemistry'是否在列中,在的话为真进行排序,不在为假,不进行排序.

就可以将所有不是'physics','chemistry'的科目先进行排序,最后再排对这两个进行排序

就达到了将化学奖和物理奖排在最后的目的

4.limit用法

查询结果返回x+1行到x+y行

select 字段名1
from 表格名
[where 条件代码]
[group by 字段名1]
[having 条件代码]
[order by 字段名 asc|desc]
limit x,y

例题:

• 分页查询employees表，每5行一页，返回第2页的数据

• 翻译成人话就是查询第6行到第10行的数据，共5行

• 写出代码limit 5,5

• 【运行代码】

5.子查询 [SELECT within SELECT Tutorial]

（1）Find the largest country (by area) in each continent, show the continent, the name and the area:找出每个大洲上最大的国家（按地区），显示该大陆、名称和地区

SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
 WHERE y.continent=x.continent
 AND area>0)
代码解读：
x,y相当于两张一模一样的表
将两张表内相同大洲那一行所对应的area值作比较，找出最大值
即每个大洲上最大的国家

​

 我的思路：

将大洲进行分组后，根据area值降序排序，然后取每个分组内的第一条数据

就是每个大洲上最大的国家

select  continent,area,
substring_index(group_concat(name order by area),',',1)  as Name
from
world
group by  area
​
不晓得对错，求大佬指证
对于‘name’字段些许疑问，是否为可唯一标识字段，其他题目里在此处均用的id列

关于分组排序取每组第一条的解法：

• (55条消息) mysql分组后获取每个组排序后的第一条数据（整行）persistenceヾ(◍°∇°◍)ﾉ的博客-CSDN博客mysql 分组排序取每组第一条

• (55条消息) mysql分组后排序取第一条,mysql分组排序取第一条记录_预付费的博客-CSDN博客

• (55条消息) mysql分组排序取每组第一条c1118l的博客-CSDN博客mysql 分组排序取每组第一条

（2）List each continent and the name of the country that comes first alphabetically.

按字母顺序列出每一大洲和第一个国家的名称。

select continent, name from world a where name <= all
(select name from world b where a.continent=b.continent)
​
解析：字符串比较大小
根据ASCII码，从第一个字母开始进行比较，第一个字母相等则比较下一个，以此类推即可以得到按字母顺序列出的国家名称​

（3）Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

找出所有国家人口小于等于25000000的大陆。然后找出与这些大陆有关的国家的名字。显示姓名、大陆和人口。【注意区分查找人口小于等于25000000的国家，最终结果中，有的国家的人口数也可能大于25000000】

select name,continent,population from world a where 25000000>= all
(select population from world b where a.continent=b.continent)​

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

一些国家的人口是其所有邻国（在同一大陆）的三倍以上。给出这些国家和大陆。

select name,continent from world a where a.population/3>= all
(select population from world b where a.continent=b.continent
and a.name!=b.name)
​
逆向思维：
在自己写这个题的时候不知道如何去表示三倍这个关系
邻国的三倍就相当于要查找的这些国家的三分之一！​
​

6.表连接

（1）from 表1 left join 表2 on 表1.字段A = 表2.字段B 等价于 from 表2 right join 表1 on 表1.字段A = 表2.字段B

（2）查询每个波兰队参加的比赛id、比赛日期、和进球次数

select matchid, mdate, count(teamid)
from game join goal on game.id= goal.matchid
and (team1 = 'POL' or team2 = 'POL')
group by  matchid,mdate
​
疑问：为什么要进行分组
对分组理解还是不够透彻​

（3）查询每个德国队参加的比赛id、比赛日期、德国的进球次数

select matchid,mdate,count(teamid)
from game join goal on game.id = goal.matchid
and (team1 = 'GER' or team2 = 'GER')
and teamid = 'GER'
group by matchid,mdate
​
和上题的区别：
上题的进球次数是只要有波兰队参赛就算进去
而该题是德国进球才进行累加​

（4）列出每场比赛，每个队的进球如图所示。请注意，在给定的查询中，列出了每个目标。如果是团队1的进球，那么分数1中会出现1，否则会出现0。你可以把这一列加起来，算出第1组的进球数。按mdate、matchid、team1和team2对结果进行排序。

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 matchid = id 
group by mdate, matchid, team1,team2​

（5）查询Julie Andrews出演过的所有电影的电影名和该电影的主演

解题思路：
1.查询电影名以及电影主演需要在movie表和actor表中，而这两个表之间并没有直接联系，因此需进行三表连接，通过casting表将movie表和actor表进行连接
2.先找出Julie Andrews出演过的电影
select movieid from  
    actor join casting 
    on actor.id = actorid
    join movie 
    on movieid = movie.id
    where name='Julie Andrews'
3.再查找这些电影的名称和主演
ord=1，则指主演
SELECT title,name
FROM movie
        JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE ord=1 
and movieid in
（2.中的代码）​

（6）查询至少出演过第一主角15次的演员名

解题思路：
1.先查找出演过第一主演的演员名【ord为1，需要casting和actor两张表】
select name 
from casting join actor 
on actor.id=actorid
where ord=1
2.在1的基础上筛选出演过第一主角15次的演员【使用count函数对movieid进行计数】
select name
from casting 
join actor 
on actorid = actor.id
where ord = 1
group by name
having count(movieid)>=15​