1 数据集介绍
本次仅需使用表world,表项如下:
在线运行:
https://sqlzoo.net/wiki/SELECT_names
2 练习题
2.1 知识储备
- The
%
is a wild-card it can match any characters _
可以用于替换单字符concat
可用于拼接字符串,示例用法concat(name, 'town')
2.2 %的使用
Find the country that start with Y
select name from world where name like 'Y%';
Find the countries that end with y
select name from world where name like '%y';
Find the countries that contain the letter x
select name from world where name like '%x%';
Find the countries that end with land
select name from world where name like '%land';
Find the countries that start with C and end with ia
select name from world where name like 'C%ia';
Find the country that has oo in the name
select name from world where name like '%oo%';
Find the countries that have three or more a in the name
select name from world where name like '%a%a%a%';
2.3 _ 的使用
Find the countries that have “t” as the second character
select name from world where name like '_t%' order by name;
Find the countries that have two “o” characters separated by two others
select name from world where name like '%o__o%';
Find the countries that have exactly four characters
select name from world where name like '____';
2.4 较难
Find the country where the name is the capital city.
select name from world where capital = name;
Find the country where the capital is the country plus “City”
select name from world where capital = concat(name,' City');
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,'%');
Find the capital and the name where the capital is an extension of name of the country
select capital,name from world where capital like concat('%',name,'_%')
Show the name and the extension where the capital is an extension of name of the country
select name,replace(capital,name,'') as extension from world where capital like concat(name,'_%');