SQLZOO总结3-5

文章目录

• SQLZOO总结3-5
• • 3 SELECt from Nobel
  • • 1 or 与 and
    • 2 not in
    • 3 转义单引号
    • 4 order by(desc)
    • *5 表达式主题 IN
  • 4 SELECt within SELECT
  • • 1 WHERe 判断里面再进行查找
    • • 1 使用=
      • 2 使用in
      • 3 在标题中
    • 2 ALL
    • • IFNULL
    • *3 相关子查询
    • 4 LIMIT x
  • 5 SUM and COUNT
  • • 1 SUM函数
    • • Aggregates
      • • COUNT
      • Distinct
    • *2 GROUP BY 和 HAVINg

SQLZOO总结3-5 3 SELECT from Nobel

显示 1980 年至 1989 年（含）文学奖获得者的所有详细信息（年份、主题、获奖者）。
PS：between and 是左闭右闭的

select yr,subject,winner from nobel where subject = 'literature' and yr between 1980 and 1989

1 or 与 and

or与and在使用时要注意先后关系
如果有括号先判断括号里面，如果没有括号并且or和and同时出现，先判断and再判断or

select yr,subject,winner from nobel where (subject = 'physics' and yr = '1980') 
or (subject = 'chemistry' and yr = '1984')

显示早年（1910 年之前，不包括 1910 年）获得“医学”奖的人的年份、学科和姓名以及后来一年（2004 年之后，包括 2004 年）获得“文学”奖的人

select * from nobel where (yr < 1910 and subject = 'Medicine') or 
(yr >= 2004 and subject = 'Literature')

2 not in

显示 1980 年获奖者的年份、学科和姓名，不包括化学和医学
Not in 不包括

select * from nobel where yr = '1980' and subject not in ('chemistry','medicine')

3 转义单引号

查找 EUGENE O’NEILL 获奖的所有详细信息
转义单引号
您不能将单引号直接放在引号字符串中。您可以在带引号的字符串中使用两个单引号
ps：因为EUGENE O’NEILL 中包含一个单引号，所以查询时需要在O’的旁边再加一个’

select * from nobel where winner = 'EUGENE O''NEILL'

4 order by(desc)

Knights in order
列出获奖者、年份和主题，获奖者以 Sir 开头。首先显示最新的，然后按名称顺序显示。
Order by 默认是升序排列，如果要降序排列的话需要在字段名后面加上关键字 DESC
ps：order by后面的字段名可以有几个 按顺序进行排序

select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc,winner

*5 表达式主题 IN

表达式主题 IN (‘chemistry’,‘physics’) 可以用作值 - 它将是 0 或 1。
按主题和获奖者姓名排序显示1984年获奖者和主题；但最后列出化学和物理。
如果subject的值在（‘Chemistry’,‘Physics’）里面，返回1，否则返回0，所以，满足条件的数据，因为返回值是1，进行asc排序的时候，就被放在最后，然后再进行后面的排序

SELECt winner, subject FROM nobel WHERe yr=1984 
 ORDER BY subject IN ('physics','chemistry'),subject,winner

4 SELECt within SELECT 1 WHERe 判断里面再进行查找 1 使用=

列出人口多于“俄罗斯”的每个国家/地区名称。

select name from world
where population > (select population from world where name = 'Russia')

2 使用in

ps：In直接包含后面的条件
列出包含阿根廷或澳大利亚的大陆的国家的名称和大陆。按国家名称排序。

select name,continent from world
where continent in 
(select continent from world where name ='Argentina' or name = 'Australia')
order by name

3 在标题中

德国（人口 8000 万）是欧洲国家中人口最多的国家。奥地利（人口 850 万）占德国人口的 11%。
显示欧洲每个国家的名称和人口。显示人口占德国人口的百分比。
格式应为名称、百分比，例如：
姓名百分比
阿尔巴尼亚3%
安道尔0%
奥地利11%
……
小数位
您可以使用函数 ROUND 删除小数位。
百分比符号 %
您可以使用函数 CONCAT 添加百分比符号。

ps：这道题也使用将两个函数一起使用

select name,
CONCAt(ROUND((population/(select population from world where name= 'Germany')
)*100,0),'%') 
as percentage from world where continent = 'Europe'

2 ALL

SQL ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

IFNULL

IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

select name from world 
where gdp > ALL(select IFNULL(gdp,0) from world
where continent = 'Europe')

*3 相关子查询

使用相关子查询
相关子查询的工作方式类似于嵌套循环：子查询一次只能访问与外部查询中的单个记录相关的行。该技术依赖表别名来识别同一个表的两种不同用途，一种在外部查询中，另一种在子查询中。
解释引用两个表的 WHERe 子句中的行的一种方法是“…其中相关值相同”。
在提供的示例中，您会说“从人口大于或等于大陆相同的所有国家的人口的世界中选择国家详细信息”。

找到每个大陆最大的国家（按区域），显示大陆、名称和区域：

SELECt continent, name, area FROM world x
  WHERe area>= ALL
    (SELECt area FROM world y
        WHERe y.continent=x.continent 
          AND area>0)

找出所有国家的人口 <= 25000000 的大陆。然后找出与这些大陆相关的国家的名称。显示名称、大陆和人口。

select name,continent,population from world x
where  25000000 >= ALL(select population from world y
where x.continent = y.continent )

一些国家的人口是所有邻国（在同一大陆）的三倍以上。给国家和大洲。

select name,continent from world x
where population > ALL(select population*3 from world y
where x.continent=y.continent and x.name<>y.name)

ps：我对于相关子查询的理解为：y在world表中查询area时需要满足和外面x在world表查询area时的continent相同，又因为外面的area大于等于整个子查询的集合，所以就可以得到一个大陆面积最大的国家，依次类推查完所有

4 LIMIT x

列出条件中的第x个数

select continent,name from world x 
where name = (select name from world y
where x.continent = y.continent order by name
limit 1)
order by continent

5 SUM and COUNT 1 SUM函数

显示世界总人口

select SUM(population) from world

Aggregates

函数 SUM、COUNT、MAX 和 AVG 是“聚合”，每个都可以应用于数字属性，从而导致查询返回单行。 （这些函数在与 GROUP BY 子句一起使用时更加有用。）

COUNT

有多少国家的面积至少为100万
count(*) 函数返回由SELECt语句返回的结果集中的行数。

select count(*) from world
where area >= 1000000

Distinct

默认情况下，SELECt 的结果可能包含重复的行。我们可以使用 DISTINCT 关键字删除这些重复项。
语法：SELECT DISTINCT

列出所有大洲——每个大洲一次。

select Distinct continent from world 

*2 GROUP BY 和 HAVINg

通过包含 GROUP BY 子句，SUM 和 COUNT 等函数可应用于共享值的项目组。当您指定 GROUP BY 大陆时，结果是每个不同的大陆值仅获得一行。所有其他列必须由 SUM、COUNT … 之一“聚合”
HAVINg 子句允许用于过滤显示的组。 WHERe 子句在聚合之前过滤行，HAVINg 子句在聚合之后过滤。
如果包含 ORDER BY 子句，我们可以通过列的位置来引用它们。

对于每个大洲，显示大洲和国家数量。

select continent,count(*) from world
group by continent

列出总人口至少为 1 亿的大陆。

select continent from world
group by continent
having sum(population) >= 100000000