SQLZoo刷题系列 1

模糊检索

定义

 模糊检索是指搜索系统自动按照用户输入关键词的同义词进行模糊检索，从而得出较多的检索结果。与之相反的是“精准搜索”。

like关键字

 模糊查询中 like 是模糊查询的关键字，%是通配符，代表多个任意匹配，比如 ‘%米饭’ 就是搜索前半段是任意字符，结尾是米饭的记录，可以将 % 放在条件字段的任意位置。
 _ 表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

习题

下面是SQLZoo中的关于模糊检索的习题，网址为：SELECT from WORLD Tutorial

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
…	…	…	…	…

In this tutorial you will use the SELECT command on the table world:

题目一

You can use WHERE name LIKE ‘B%’ to find the countries that start with “B”.
The % is a wild-card it can match any characters
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'

题目三

Luxembourg has an x - so does one other country. List them both.
Find the countries that contain the letter x

SELECT name FROM world
  WHERE name LIKE '%x%'

题目四

Iceland, Switzerland end with land - but are there others?
Find the countries that end with land

SELECT name FROM world
  WHERE name LIKE '%land'

题目五

Columbia starts with a C and ends with ia - there are two more like this.
Find the countries that start with C and end with ia

SELECT name FROM world
  WHERE name LIKE 'C%ia'

题目六

Greece has a double e - who has a double o?
Find the country that has oo in the name

SELECT name FROM world
  WHERE name LIKE '%oo%'

题目七

Bahamas has three a - who else?
Find the countries that have three or more a in the name

SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

题目八

India and Angola have an n as the second character. You can use the underscore as a single character wildcard.
SELECT name FROM world
WHERE name LIKE '_n%'
ORDER BY name
Find the countries that have “t” as the second character.

SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

题目九

Lesotho and Moldova both have two o characters separated by two other characters.
Find the countries that have two “o” characters separated by two others.

SELECT name FROM world
 WHERE name LIKE '%o__o%'

题目十

Cuba and Togo have four characters names.
Find the countries that have exactly four characters.

SELECT name FROM world
 WHERE name LIKE '____'

题目十一

The capital of Luxembourg is Luxembourg. Show all the countries where the capital is the same as the name of the country
Find the country where the name is the capital city.

SELECT name
  FROM world
 WHERE name = capital

题目十二

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')

注：
 这里的concat函数的作用是将一个或多个字符串参数连接成一个字符串，语法是CONCAT(string1,string2, ... );

题目十三

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.
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,'%') and capital > name

题目十五

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.
You can use the SQL function REPLACE.

SELECT name, REPLACE(capital, name, '') as extension 
FROM world 
WHERE capital LIKE concat('%',name,'%') AND capital > name;

注：
REPLACE函数的格式如下：REPLACE(对象字符串，替换前的字符串，替换后的字符串)

在此例中，针对capital字符串，将其中的name替换为空字符，剩下了就是其后缀。