sqlzoo-SELECT within SELECT Tutorial练习记录

最新推荐文章于 2024-07-18 15:45:25 发布

sibyl-k

最新推荐文章于 2024-07-18 15:45:25 发布

阅读量446

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/qq_41787956/article/details/113527377

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.嵌套select

1.1 List each country in the same continent as ‘Brazil’.

-- 找出和巴西在同一大洲的国家
SELECT name 
FROM world 
WHERE continent = (SELECT continent FROM world WHERE name = 'Brazil')

有些版本的sql需要为子查询添加别名。只需在子查询结束的括号后加上 as 别名即可：

SELECT name 
FROM world 
WHERE continent = 
  (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent

当子查询返回多个结果时，需用到关键字in：

SELECT name, continent 
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name='Brazil' OR name='Mexico')

1.2 List each country and its continent in the same continent as ‘Brazil’ or ‘Mexico’.

-- 列出所有与巴西或者墨西哥同属于一个洲的国家及其所属洲
SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name = 'Brazil' OR name = 'Mexico')

1.3 Show the population of China as a multiple of the population of the United Kingdom

-- 将中国人口显示为英国人口的倍数，即以英国人口为基数
SELECT population/(SELECT population FROM world WHERE name = 'United Kingdom')
FROM world
WHERE name = 'China'
-- 当确定select子查询只返回一个结果时，则可在select所在行使用该查询。

1.4 Show each country that has a population greater than the population of ALL countries in Europe.

-- 找出符合以下条件的国家：其人口数大于欧洲的每个国家
SELECT name 
FROM world
WHERE population > ALL (SELECT population FROM world WHERE continent='Europe')
-- 返回多结果时，ALL、ANY的使用

2.‘SELECT within SELECT Tutorial’练习记录

练习在select语句中嵌套select语句进行更复杂的查询。

练习表格world：

world(name, continent, area, population, gdp):

2.1 List each country name where the population is larger than that of ‘Russia’.

-- 列出人口比俄罗斯多的国家
SELECT name
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'Russia')
-- 考察select嵌套

2.2 Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

-- 查找人均GDP比英国高的欧洲国家
SELECT name 
FROM world
WHERE continent = 'Europe' AND gdp/population > (SELECT gdp/population FROM world WHERE name = 'United Kingdom')
-- 多条件查询

2.3 List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

-- 查找阿根廷或澳大利亚所属洲中的国家，并按名字排序，显示其名字和所属大洲
SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name in ('Argentina', 'Australia'))
ORDER BY name
-- 考察select嵌套、排序

2.4 Which country has a population that is more than Canada but less than Poland? Show the name and the population.

-- 查找人口数多于加拿大，但少于波兰的国家，显示其名字和人口
SELECT name, population
FROM world
WHERE population > (SELECT population FROM world WHERE name = 'Canada') 
	and population <  (SELECT population FROM world WHERE name = 'Poland') 
-- 使用多个select嵌套

2.5 Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

-- 显示欧洲每个国家的名字和人口，其中人口表示为德国人口的百分比形式
SELECT name, concat(round((population/(SELECT population FROM world WHERE name = 'Germany'))*100, 0), '%')
FROM world
WHERE continent = 'Europe'
-- 考察round()、concat()函数
-- round():确定小数位数
-- concat():串联连接两个或多个字符。
-- ？？但是不知道为什么，虽然用了round()函数，但在sqlzoo上提交后显示的结果中还是显示小数：例如3%显示为3.000000000000%。但是经过在本地建表验证，表示上述sql语句正确！

？？不知道为什么，虽然用了round()函数，但在sqlzoo上提交后显示的结果中还是显示小数：例如3%显示为3.000000000000%。后经过在本地建表验证，表示上述sql语句正确！

验证过程如下：

-- navicat上验证如下：
-- 1.建表
create table world(
	name varchar(20),
	continent varchar(20),
	area varchar(20),
	population varchar(20),
	gdp varchar (20)
);
-- 2.查看表
show tables
-- 3.插入数据
insert into world (name, continent, area, population, gdp) values('Albania', 'Europe', '652230', '25500100', '20343000000');
insert into world (name, continent, area, population, gdp) values('Andorra', 'Europe', '468', '78115', '3712000000');
insert into world (name, continent, area, population, gdp) values('Germany', 'Europe', '357114', '80716000', '3425956000000');
-- 4.查看建好的表，共三条记录
select * from world;
-- 5.验证
SELECT name, concat(round((population/(SELECT population FROM world WHERE name = 'Germany'))*100, 0), '%')
FROM world
WHERE continent = 'Europe';

扩展：

concat()： 串联连接两个或多个字符。

substring(s, i, n)： 子字符串，从第i个字符开始提取字符串s的n个字符。

SUBSTRING('Hello world', 2, 3) -> 'ell' -- 从第2个字符开始，提取3个字符。

left(s, n)： 从字符串s的开头提取n个字符。
```
LEFT('Hello world', 4) -> 'Hell' 
```
right(s, n)： 从字符串s的结尾提取n个字符。
```
RIGHT('Hello world', 4) -> 'orld'
```
position(s1 in s2)： 返回子字符串s1在较大字符串s2中的第一个字符位置。如果s1未在s2中出现，则返回0。
```
POSITION('ll' IN 'Hello world') -> 3
```
trim(s)： 返回除去前后空格的字符串s’。使用char字段时，此功能特别有用，因为通常，char字段用空格填充。
```
TRIM('Hello world  ') -> 'Hello world' 
```

2.6 Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

-- 查找国家，其GDP大于欧洲每个国家的GDP，显示其名字
SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp>0 AND continent = 'Europe')
-- 当gdp字段含有空值时，需要添加gdp>0的条件，来筛选。(为什么要用>0来筛选，应该是world建表时，不允许gdp字段为空，当不允许空的情况下，数值型字段默认为0）
-- 用'gdp is not null'也可以。

扩展：sql之null vs 空字符串 vs 0

’’： 表示空字符串，判断’’ 用 =’’ 或 <>’’

null：表示空值，数值未知，没有两个相等的空值，判断用 is null 或 is not null

想筛选出所有score不是5的记录：
但score!=5并不能筛出score为null的记录 （因为null是值不确定的情况），需要用is null筛选。

0：表示值为‘0’

2.7 Find the largest country (by area) in each continent, show the continent, the name and the area:

-- 查找每个洲中面积最大的国家，显示其所属洲、国家名字、占地面积
SELECT continent, name, area
FROM world x
WHERE area >= ALL(select area FROM world y WHERE y.continent=x.continent AND area is not null)
-- 考察相关子查询（给表起别名）：依靠表别名来标识同一表的两种不同用途，一种用于外部查询，另一种用于子查询。
-- 这里的x和y是表world的别名，x用于外部查询，y用于子查询
-- area is not null 在这里等价于 area>0

2.8 List each continent and the name of the country that comes first alphabetically.

-- 列出每个洲及其洲内按字母排在首位的国家的名字
-- 解决方案一：(使用相关子查询)
SELECT continent, name
FROM world x
WHERE name <= ALL(SELECT name FROM world y WHERE y.continent=x.continent)
-- 解决方案二：(使用分组：group by和min()函数)
SELECT continent, min(name)
FROM world
GROUP BY continent

2.9 Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

-- 查找所有国家人口均≤25000000的大洲，然后查找这些大洲中的国家，显示国家名称，所属洲和人口。
SELECT name, continent, population
FROM world x
WHERE 25000000 >= ALL(SELECT population FROM world y WHERE y.continent=x.continent)
-- 相关子查询
-- 注意：ALL要放在操作符之后，即ALL(...) <= 25000000会报错

2.10 Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

-- 找出满足条件'其人口数大于其同一洲内相邻国家的3倍人口数'的国家，显示国家名字和所属洲。
SELECT name, continent
FROM world x
WHERE x.population/3 > ALL(SELECT population FROM world y WHERE y.continent = x.continent AND y.name <> x.name)
-- 考察相关子查询
-- ？？但是上述sql语句相当于找出每个洲中，人口数是其它所有国家人口数3倍以上的国家，而不是相邻国家。