编程知识 cdmana.com

mysql练习

最近闲着就去刷mysql题目了,sqlzoo在知乎上呼声还挺高的,就去做了下啦,感觉还是欠练…
具体的表结构之类或者自己想练习的,请移步官网https://sqlzoo.net/
练习须知:
1.认真读题
2.真的想不到可以看下提示,eg:XX函数
3.有些题可能不是sql语句写错了,而是显示字段顺序错了导致的错误,排错可看下执行结果与正确结果的差异。

这里罗列一些一开始没想到的题目以及涉及的知识点吧。
Q: 找出所有首都和其國家名字,而首都要有國家名字中出現。 

#concat函数可以用来合拼两个或以上的字串。
SELECT capital,name
  FROM world
WHERE  capital like concat('%',name, '%')
Q:"Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".
顯示國家名字,及其延伸詞,如首都是國家名字的延伸。
你可以使用SQL函數 REPLACE 或 MID.

#REPLACE ( '待搜索string' , '待查找string' , '替换string' )
select name , replace(capital,name,'')
from world
where capital like concat(name,'_%')

#MID(column_name,start[,length])--column_name,start 必填
#如下sql报错:execute command denied to user 'scott'@'localhost' for routine 'gisq.LEN'
#网上搜索时权限问题?为什么
select name , MID(capital,len(name)+1)
from world
where capital like concat(name,'_%')
Q: List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order. 

#多个字段排序,用逗号隔开,默认是asc
select winner,yr,subject from nobel 
where winner like 'Sir%' 
order by yr desc , winner asc
Q:Germany (population 80 million) has the largest population of the countries in Europe.
Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

#concat函数 拼接2个及以上字串
#ROUND函数  数值字段舍入为指定的小数位数。eg:round(数值字段,小数位数)
select name , concat(round(population*100/(select population from world where name ='Germany'),0),'%') 
from world where continent ='Europe'
Q: Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values) 

#ALL运算符是一个逻辑运算符,它将单个值与子查询返回的单列值集进行比较。必须以比较运算符开头
SELECT GDP 
  FROM world
 WHERE GDP >= ALL(SELECT GDP 
                           FROM world
                          WHERE GDP >0 and continent='Europe')
Q:List each continent and the name of the country that comes first alphabetically.

# LIMIT子句用于限制由 SELECT 语句返回的数据数量。
#limit 和 offset 的区别 :https://blog.csdn.net/cnwyt/article/details/81945663
#limit y 分句表示: 读取 y 条数据
#limit x, y 分句表示: 跳过 x 条数据,读取 y 条数据
#limit y offset x 分句表示: 跳过 x 条数据,读取 y 条数据

select continent,name from world w1
where w1.name=
(select w2.name from world w2 where w2.continent=w1.continent order by name limit 1)
Q:Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

#ALL函数
select name,continent from world w1
where w1.population > ALL(
select 3*population from world w2
where w2.continent = w1.continent and w1.name <> w2.name
)

这题一开始做错了。。。后来看了下官网关于group by 和 having 的一段介绍:

GROUP BY and HAVING
By including a GROUP BY clause functions such as SUM and COUNT are applied to groups of items sharing values. When you specify GROUP BY continent the result is that you get only one row for each different value of continent. All the other columns must be "aggregated" by one of SUM, COUNT ...

The HAVING clause allows use to filter the groups which are displayed. The WHERE clause filters rows before the aggregation, the HAVING clause filters after the aggregation.(重点,意思是说where子句的执行顺序在聚合函数前面,having子句的执行顺序在聚合函数后面)

If a ORDER BY clause is included we can refer to columns by their position.

补充一点:group by 多个字段,字段顺序对查询结果数据没有影响,只是结果集的展示顺序不一样。eg:

另外插张在知乎看到的图:(侵权删)

Q: Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'

#COALESCE函数:返回包括expression在内的所有参数中的第一个非空表达式。
#COALESCE(expression,value1,……):expression为空则判断value1是否为空。


select name,COALESCE(mobile,'07986 444 2266')
from teacher 
Q:Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

#case

select name ,
case when dept =1 or dept =2 then 'Sci'
     when dept =3 then 'Art'
else 'None'
end
from teacher
Q:Find the routes involving two buses that can go from Craiglockhart to Lochend.
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.

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

#题目的大概意思是Craiglockhart 到 Lochend 需要转一趟车,列出所有的方案:第一辆巴士号、巴士的公司、中转站、第二辆巴士号、巴士的公司。

SELECT m.num,m.company,name,n.num,n.company
FROM (SELECT a.num,a.company,b.stop FROM route a 
JOIN route b ON a.company=b.company AND a.num=b.num AND a.stop!=b.stop WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart')) AS m
JOIN (SELECT d.num,d.company,c.stop FROM route c JOIN route d ON c.company=d.company AND c.num=d.num AND c.stop!=d.stop WHERE d.stop=(SELECT id FROM stops WHERE name='Lochend')) AS n
ON  m.stop=n.stop
JOIN stops 
ON m.stop=stops.id
ORDER BY m.num,stops.name,n.num;
现在还是不太懂的题:
1.join 不给过?left join 才给过?

2. 列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。 
大概对比了下答案,发现是结果排序的问题,怎么解决?
#我的sql(不给过!!)
select title,COUNT(actorid) as num  from casting
join movie on movieid = id and yr = 1978
group by title 
order by num desc

后续:把题目改成英文看,我终于知道问题在哪了……
Q:List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

#过了!
select title,COUNT(actorid) as num  from casting
join movie on movieid = id and yr = 1978
group by title 
order by num desc,title

 

 

 

版权声明
本文为[罗罗罗罗罗]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/3668859/blog/4839539

Tags Mysql clause
Scroll to Top