SQLZOO笔记-SELECT基础

SQLZoo网站是一个SQL在线交互课程，适合初学者入门。

优点：

• 学习梯度平稳，带着实例，从最基础的一小块知识点开始
• 交互实时反应。能够模拟「键入输入命令」-「获得输出结果」的实战情景，同时无需另新手沮丧的繁琐的环境配置安装。
• 符号「必要难度」理论。比如，第一部分给出示例代码，到下一部分，便让学生复现一样的代码。这样，学习者在「进阶区」，而非「恐慌区」和「舒适区」。

传送门：https://sqlzoo.net/wiki/SQL_Tutorial

SELECT基础

介绍 World表格（数据包含：各国的名称、人口、面积以及GDP）

1. WHERE子句——条件匹配

示例

SELECT population FROM world
  WHERE name = 'France'

答案：查询name为'Germany'数据行的population

SELECT population FROM world
  WHERE name = 'Germany'

注: 字符串用单引号

2.IN子句——项目是否在列表中

示例

SELECT name, population FROM world
  WHERE name IN ('Brazil', 'Russia', 'India', 'China');

答案：查询name为'Sweden', 'Norway', 'Denmark'数据行的population

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

3.BETWEEN子句——按照值的范围边界来筛选数据
示例

SELECT name, area FROM world
  WHERE area BETWEEN 250000 AND 300000

答案：查询area的数值介于'200,000——250,000'数据行的name, area

SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000

SELECT小测验

传送门：https://sqlzoo.net/wiki/SELECT_Quiz

SELECT name, continent, population FROM world

SELECT name, continent, population FROM world

SELECT name FROM world
WHERE population = 64105700

SELECT name FROM world
WHERE population > 200000000

SELECT name, GDP/population FROM world
WHERE population >= 200000000

SELECT name, population/1000000 FROM world
WHERE continent='South America'

SELECT name FROM world
WHERE name LIKE '%United%';

Ref：
https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words

SELECT name, population, area FROM world
WHERE area > 3000000
OR population >250000000

SELECT name, population, area FROM world
WHERE (area > 3000000 AND population < 250000000)
OR (area < 3000000 AND population>250000000)

Ref：
https://stackoverflow.com/questions/41034292/xor-in-sql-server

SELECT name, ROUND(population/1000000,2),ROUND(GDP/1000000000,2) 
AS ROUNDValue
FROM world
WHERE continent='South America'

Ref：
https://www.w3schools.com/sql/func_sqlserver_round.asp

https://www.quora.com/Is-it-possible-to-round-up-a-figure-to-the-nearest-1000-in-SQL-Server

SELECT name,  capital, LENGTH(name),LENGTH(capital)
FROM world
WHERE LENGTH(name)=LENGTH(capital)

SELECT name, capital
FROM world
WHERE LENGTH(name)=LENGTH(capital);

SELECT name, capital
FROM world
WHERE LEFT(name,1)=LEFT(capital,1)
AND name<>capital;

SELECT name
FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %'

wildcard 通配符

SELECT name
FROM world
WHERE name LIKE '%a%'
OR name LIKE '%e%'
OR name LIKE '%i%'
OR name LIKE '%o%'
OR name LIKE '%u%'
AND name NOT LIKE '% %'

--------------------分隔线-------------------------------

SELECT基础2

介绍 Nobel表格（诺贝尔奖得主的数据表格，数据包含：获奖者姓名、获奖年份、专业领域）

nobel(yr, subject, winner)

1. WHERE子句——条件匹配

示例

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1960

答案：查询 1950年诺贝尔奖的数据

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

2. WHERE子句——条件匹配

示例

SELECT winner
FROM nobel
WHERE yr = 1960
AND subject = 'Physics'

答案：查询谁在 1962 年获得了诺贝尔文学奖

SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature';

3. 爱因斯坦

示例:无

答案：查询爱因斯坦获得的诺贝尔奖的年份和专业

SELECT yr, subject 
FROM nobel
WHERE winner='Albert Einstein'

4. 近年的诺贝尔和平奖

示例:无

答案：查询从 2000 年（包括千禧年）以来所有的诺贝尔和平奖得主

SELECT winner
FROM nobel
WHERE yr>=2000
AND subject='Peace';

5. 20世纪 80 年代的诺贝尔文学奖

示例:无

答案：查询从 2000 年（包括千禧年）以来所有的诺贝尔和平奖得主

SELECT yr, subject, winner
FROM nobel
WHERE subject='Literature'
AND yr BETWEEN 1980 AND 1989;

6. 哪些诺贝尔奖得主是总统？
   示例:

SELECT * FROM nobel
 WHERE yr = 1970
  AND subject IN ('Cookery',
                  'Chemistry',
                  'Literature')

答案：

查询以下总统的获奖细节

• Theodore Roosevelt
• Woodrow Wilson
• Jimmy Carter
• Barack Obama

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter','Barack Obama')

7. 名字叫约翰 John 的诺贝尔奖得主

示例:无

答案：名字叫约翰 John 的诺贝尔奖得主

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

8. 物理与化学诺贝尔奖

示例:无

答案：查询1984 年的诺贝尔化学奖得主及 1980 年的诺贝尔物理学将得主

SELECT *
FROM nobel
WHERE (subject='Physics'  AND yr=1980)
OR  (subject='Chemistry'  AND yr=1984);

9. 排除化学家和医学家

示例:无

答案：查询1980年的诺贝尔奖得主，排除化学和医学领域的得主。

SELECT *
FROM nobel
WHERE yr=1980
AND NOT subject='Medicine'
AND NOT subject='Chemistry';

10. 早期的诺贝尔医学奖及近期的诺贝尔文学奖

示例:无

答案：查询1910年之前(不包括1910)的诺贝尔医学奖数据，以及2004年之后(包括2004)的诺贝尔文学奖数据。

SELECT *
FROM nobel
WHERE (yr<1910 AND subject='Medicine')
OR (yr>=2004 AND subject='Literature');

更难的题目

11. 查询 PETER GRÜNBERG的获奖细节

Non-ASCII characters—— 获奖得主的名字有一个特殊字符——Ü
参考如何输入非ASCII字符
https://en.wikipedia.org/wiki/%C3%9C#Keyboarding

Ü的macOS的键盘输入组合：箭头向上+Shift+U

答案：

SELECT *
FROM nobel
WHERE winner='PETER GRÜNBERG';

12. Apostrophe 引号

查询EUGENE O'NEILL的获奖细节

「字符串中含有单引号」的处理方法

答案：

SELECT *
FROM nobel
WHERE winner='EUGENE O''NEILL'

注意：在字符串「O'」之后添加一个「'」单引号

13. 国王的骑士们

查询所有以「Sir.」开头的诺贝尔奖得主，获奖时间近的得主排在前列，若获奖年份相同，安装名字的的字母顺序排序。

答案：

SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner ASC;

注意：ORDER BY语法

14. 化学家&物理学家列在最后

查询1984年的诺贝尔奖得主，按照专业（学科）及获奖者名称来排列，但是专业是化学和物理的，要排列在最后。

答案：

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry'),subject,winner;