sql SELECT LIKE介绍

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,'_%');