SQLZOO-SELECT names

最新推荐文章于 2025-01-24 06:00:00 发布

Netooo

最新推荐文章于 2025-01-24 06:00:00 发布

阅读量462

点赞数

CC 4.0 BY-SA版权

分类专栏： SQLZOO 文章标签： MySQL SQLZOO

本文链接：https://blog.youkuaiyun.com/Netooo/article/details/83656394

1 篇文章

订阅专栏

本文精选了16个实用的SQL查询技巧，涵盖了从基础到高级的查询需求，如按国家名称筛选人口、查找特定字母开头或结尾的国家、以及复杂的字符串匹配等。通过这些实例，读者可以快速掌握如何使用SQL的LIKE和IN子句进行精确和模糊搜索。

1.Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.

SELECT name, population FROM world
  WHERE name IN ( 'Sweden', 'Norway' ,'Denmark');

2.Find the country that start with Y

SELECT name FROM world
  WHERE name LIKE 'Y%'

3.Find the countries that end with y

SELECT name FROM world
  WHERE name LIKE '%y'

4.Find the countries that contain the letter x

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

5.Find the countries that end with land

SELECT name FROM world
  WHERE name LIKE '%land'

6.Find the countries that start with C and end with ia

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

7.Find the country that has oo in the name

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

8.Find the countries that have three or more a in the name

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

9.Find the countries that have "t" as the second character.

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

10.Find the countries that have two "o" characters separated by two others

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

11.Find the countries that have exactly four characters.

SELECT name FROM world
 WHERE length(name)=4

12.Find the country where the name is the capital city.

SELECT name
  FROM world
 WHERE name = capital

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

SELECT name
  FROM world
 WHERE capital = concat(name,' City');

14.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,'%')

15.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 name != capital;

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

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

select name,replace(capital,name,'') as ext from world where capital like concat(name,'_%');