自学SQL网题目解答与笔记[亲测有效]

SELECT 查询 101

1. 【简单查询】找到所有电影的名称title

   SELECT title FROM movies;
   

2. 【简单查询】找到所有电影的导演

   select director from movies;
   

3. 【简单查询】找到所有电影的名称和导演

   select title, director from movies;
   

4. 【简单查询】找到所有电影的名称和上映年份

   select title, year from movies;
   

5. 【简单查询】找到所有电影的所有信息

   select * from movies;
   

6. 【简单查询】找到所有电影的名称,Id和播放时长

   select title, id, Length_minutes
   from movies;
   

条件查询 (constraints) (Pt. 1)

1. 【简单条件】找到id为6的电影

   select * from movies
   where id = 6;
   

2. 【简单条件】找到在2000-2010年间year上映的电影

   select * 
   from movies
   where year >= 2000 and year <= 2010;
   

3. 【简单条件】找到不是在2000-2010年间year上映的电影

   select * 
   from movies
   where year < 2000 or year > 2010;
   

4. 【简单条件】找到头5部电影

   select * 
   from movies
   where id <= 5;
   

5. 【简单条件】找到2010（含）年之后的电影里片长小于两个小时的片子

   select * 
   from movies
   where year >= 2010
   and Length_minutes < 120;
   

条件查询(constraints) (Pt. 2)

1. 【复杂条件】找到所有Toy Story系列电影

   select *
   from movies
   where title like "Toy Story%";
   

2. 【复杂条件】找到所有John Lasseter导演的电影

   select *
   from movies
   where director = "John Lasseter";
   

3. 【复杂条件】找到所有不是John Lasseter导演的电影

   select *
   from movies
   where director <> "John Lasseter";
   

4. 【复杂条件】找到所有电影名为"WALL-"开头的电影

   select *
   from movies
   where title like "WALL-%";
   

5. 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

   select *
   from movies
   where year = 1998;
   

查询结果Filtering过滤 和 sorting排序

DISTINCT

DISTINCT 语法介绍，以Movies表为例，可能很多电影都是同一年Year发布的，如果想要按年份排重，一年只能出现一部电影到结果中， 可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回。写作：DISTINCT Year

选取出唯一的结果的语法:

SELECT DISTINCT column, another_column, … 
FROM mytable 
WHERE condition(s);

因为 DISTINCT 语法会直接删除重复的行, 我们还会学习 GROUP BY 语句， GROUP BY 也会返回唯一的行，不过可以对具有相同的 属性值的行做一些统计计算，比如：求和.

结果排序 （Ordering results）

为了解决结果排序问题, 我们可以用 ORDER BY col_name 排序的语法来让结果按一个或多个属性列做排序.

结果排序（ordered results）

SELECT column, another_column, … 
FROM mytable WHERE condition(s) 
ORDER BY column ASC/DESC;

ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序，对数字来说就是升序 1，2，3，… 或降序 … 3,2,1 . 对于文本列，升序和降序指的是按文本的字母序。

通过Limit选取部分结果

LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。你可以想象一下从一条长绳子剪下一小段的过程，我们通过 OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度。

limited查询

SELECT column, another_column, … 
FROM mytable 
WHERE condition(s) 
ORDER BY column ASC/DESC 
LIMIT num_limit OFFSET num_offset;

你可以想象一下一个新闻网站的新闻条目数据，他们在页面上是按热度和时间排序的，每一个页面只显示10条数据，在所有这些属性都是不断变化的情况下。我们可以想见通过SQL的ORDER LIMIT OFFSET 句法，我们可以根据要求从数据库筛选出需要的新闻条目.

题目

1. 【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列

   select distinct director
   from movies;
   

2. 【结果排序】列出按上映年份最新上线的4部电影

   select *
   from movies
   order by year desc 
   limit 4;
   

3. 【结果排序】按电影名字母序升序排列，列出前5部电影

   select *
   from movies
   order by title asc
   limit 5;
   

4. 【结果排序】按电影名字母序升序排列，列出上一题之后的5部电影

   select *
   from movies
   order by title asc
   limit 5 offset 5;
   

5. 【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可

   select title
   from movies
   where director = "John Lasseter"
   order by Length_minutes desc
   limit 1 offset 2;
   

复习 SELECT 查询

Table(表）: North_american_cities

在这个数据表中，你需要熟悉一下latitudes（纬度）和 longitudes（经度）的概念， latitudes在赤道以北是正数，以南是负数；longitudes在子午线东部是正数，以西是负数， 在查询中需要注意 经纬度和东西南北方向的对应关系。

1. 【复习】列出所有加拿大人的信息(包括所有字段)

   select *
   from north_american_cities
   where country = "Canada";
   

2. 【复习】列出所有在Chicago西部的城市，从西到东排序(包括所有字段)

   select *
   from north_american_cities
   where Longitude < (select Longitude 
                      from north_american_cities 
                      where city = "Chicago")
   order by Longitude asc;
   

3. 【复习】用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

   select *
   from north_american_cities
   where country = "Mexico"
   order by population desc
   limit 2;
   

4. 【复习】列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)

   select *
   from north_american_cities
   where country = "United States"
   order by population desc
   limit 2 offset 2;
   

5. 列出所有美国United States的城市按纬度从北到南排序(包括所有字段)

   select * 
   from north_american_cities
   where country = "United States"
   order by Latitude desc;
   

6. 北美所有城市,请按国家名字母序从A-Z再按人口从多到少排列看下前10位的城市(包括所有字段)

   select * 
   from north_american_cities
   order by Country asc, Population desc
   limit 10;
   

用JOINs进行多表联合查询

用JOINs进行多表联合查询

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同 主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。具体我们用到 JOIN 关键字。我们先来学习 INNER JOIN.

用INNER JOIN 连接表的语法

SELECT column, another_table_column, … 
FROM mytable （主表） 
INNER JOIN another_table （要连接的表）    
	ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。此时，你可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）.

练习

Table: Movies

Table: Boxoffice

1. 【联表】找到所有电影的国内Domestic_sales和国际销售额International_sales

   Movies表+Boxoffice表的两个字段

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id;
   

   Id	Title	Director	Year	Length_minutes	Movie_id	Rating	Domestic_sales	International_sales
   1	Toy Story	John Lasseter	1995	81	1	8.3	191796233	170162503
   2	A Bug”s Life	John Lasseter	1998	95	2	7.2	162798565	200600000

2. 【联表】找到所有国际销售额比国内销售大的电影

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   and Domestic_sales < International_sales;
   

3. 【联表】找出所有电影按市场占有率rating倒序排列

   select *
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   order by rating desc;
   

4. 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

   select director, International_sales
   from Movies
   inner join Boxoffice
   where Movies.id = Boxoffice.Movie_id
   order by International_sales desc
   limit 1;
   

5. John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

   select 
   	title, 
   	(Domestic_sales + International_sales) / Length_minutes as min_val
   from Movies
   inner join Boxoffice
   on Movies.id = Boxoffice.Movie_id
   where Director = "John Lasseter"
   order by min_val desc
   limit 3;
   

外连接（OUTER JOINs）

INNER JOIN 只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.

真实世界中两个表存在差异很正常，所以我们需要更多的连表方式，也就是本节要介绍的左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN. 这几个 连接方式都会保留不能匹配的行。

用LEFT/RIGHT/FULL JOINs 做多表查询

SELECT column, another_column, … 
FROM mytable 
INNER/LEFT/RIGHT/FULL JOIN another_table    
ON mytable.id = another_table.matching_id
WHERE condition(s) 
ORDER BY column, … ASC/DESC 
LIMIT num_limit OFFSET num_offset;

和INNER JOIN 语法几乎是一样的. 我们看看这三个连接方法的工作原理：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

练习

Table: Employees

Table: Buildings

1. 【复习】找到所有有雇员的办公室(buildings)名字

   select distinct building
   from Employees 
   where Building is not null;
   

2. 【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

   select distinct 
   	Buildings.building_name, 
       Employees.Role
   from Buildings
   left join Employees
   on Employees.Building = Buildings.Building_name; 
   

3. 【难题】找到所有有雇员的办公室(buildings)和对应的容量

   select distinct 
   	Building, 
       Capacity
   from Buildings
   left join Employees
   on Employees.Building = Buildings.Building_name
   where Building is not null;
   

on & where 的区别

在使用left jion时，on和where条件的区别如下：

1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2. where条件是在临时表生成 好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

关于特殊关键字 NULLs

Table: Employees

Table: Buildings

1. 【复习】找到雇员里还没有分配办公室的(列出名字和角色就可以)

   select distinct Name, Role
   from Employees
   left join Buildings 
   where Building is null;
   

2. 【难题】找到还没有雇员的办公室

   select Building_name
   from Buildings
   left join Employees
   on Buildings.Building_name = Employees.Building	
   where name is null;
   

   思路：

   先进行联表查询

   Building_name	Capacity	Role	Name	Building	Years_employed
   1e	24	Engineer	Becky A.	1e	4
   1e	24	Engineer	Dan B.	1e	2
   1e	24	Engineer	Dan M.	1e	4
   1e	24	Engineer	Malcom S.	1e	1
   1e	24	Engineer	Sharon F.	1e	6
   1e	24	Manager	Scott K.	1e	9
   1e	24	Manager	Shirlee M.	1e	3
   1w	32	null	null	null	null
   2e	16	null	null	null	null
   2w	20	Artist	Brandon J.	2w	7
   2w	20	Artist	Jakob J.	2w	6
   2w	20	Artist	Lillia A.	2w	7
   2w	20	Artist	Sherman D.	2w	8
   2w	20	Artist	Tylar S.	2w	2
   2w	20	Manager	Daria O.	2w	6

   可看到一些行Building存在而name为空，这就是没有雇员的房间

   Building_name	Capacity	Role	Name	Building	Years_employed
   1w	32	null	null	null	null
   2e	16	null	null	null	null

在查询中使用表达式

Table: Movies (Read-Only)

Table: Boxoffice (Read-Only)

1. 【计算】列出所有的电影ID,名字和销售总额(以百万美元为单位计算)

   select 
   	id, title, 
       (Domestic_sales + International_sales) / 1000000 as total
   from movies
   inner join Boxoffice 
   on movies.Id = Boxoffice.Movie_id;
   

2. 【计算】列出所有的电影ID,名字和市场指数(Rating的10倍为市场指数)

   select 
   	id, title, 
       Rating * 10 MarketIndex
   from movies
   inner join Boxoffice
   on movies.Id = Boxoffice.Movie_id;
   

3. 【计算】列出所有偶数年份的电影，需要电影ID,名字和年份

   select 
   	id, title, year
   from movies
   inner join Boxoffice
   on movies.Id = Boxoffice.Movie_id
   where year % 2 = 0;
   

4. 【难题】John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以

   select 
   	title, 
   	(Domestic_sales + International_sales) / Length_minutes as min_val
   from Movies
   inner join Boxoffice
   on Movies.id = Boxoffice.Movie_id
   where Director = "John Lasseter"
   order by min_val desc
   limit 3;
   

在查询中进行统计I (Pt. 1)

对全部结果数据做统计

SELECT 
AGG_FUNC(column_or_expression) AS aggregate_description, … FROM mytable 
WHERE constraint_expression;

如果不指明如何分组，那统计函数将对查询结果全部数据进行统计，当然每一个统计也可以像之前用AS来取一个别名，以增加可读性。

常见统计函数

下面介绍几个常用统计函数:

分组统计

GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了.
GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数.

用分组的方式统计

SELECT 
AGG_FUNC(column_or_expression) AS aggregate_description, … FROM mytable 
WHERE constraint_expression
GROUP BY column;

练习

Table(表）: Employees 全表查看

1. 【统计】找出就职年份最高的雇员(列出雇员名字+年份）

   select Name, max(Years_employed)
   from Employees;
   

2. 【分组】按角色(Role)统计一下每个角色的平均就职年份

   select Role, avg(Years_employed)
   from Employees
   group by Role;
   

3. 【分组】按办公室名字总计一下就职年份总和

   select Building, sum(Years_employed)
   from Employees
   group by Building;
   

4. 【难题】每栋办公室按人数排名,不要统计无办公室的雇员

   select Building, count(*) as count
   from Employees
   where Building is not null
   group by Building;
   

在查询中进行统计II (Pt. 2)

在 GROUP BY 分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出>100万的年份？）

一个不常用的语法 HAVING 语法将用来解决这个问题，他可以对分组之后的数据再做SELECT筛选.

用HAVING进行筛选

SELECT group_by_column, 
AGG_FUNC(column_expression) AS aggregate_result_alias, … FROM mytable 
WHERE condition
GROUP BY column 
HAVING group_condition;

HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉 HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .

Table(表）: Employees 全表查看

1. 【统计】统计一下Artist角色的雇员数量

   select count(*)
   from Employees
   where Role = "Artist";
   

2. 【分组】按角色统计一下每个角色的雇员数量

   select Role, count(*)
   from Employees
   group by Role;
   

3. 【分组】算出Engineer角色的就职年份总计

   select sum(Years_employed)
   from Employees 
   where Role = "Engineer";
   

4. 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

   select 
   	Role, 
       count(Role) as count,
       case
       	when Building is null then 0
           else 1
       end 
       as bn
   from Employees 
   group by Role, bn;
   

查询执行顺序

介绍完了所有查询相关的语法，我们来把之前的所有语法集中到一个句子中.

这才是完整的SELECT查询

SELECT DISTINCT 
	column, 
	AGG_FUNC(column_or_expression), … 
FROM mytable    
JOIN another_table 
ON mytable.column = another_table.column    
WHERE constraint_expression   
GROUP BY column    
HAVING constraint_expression    
ORDER BY column ASC/DESC    
LIMIT count OFFSET COUNT;

一个查询SQL的执行总是先从数据里按条件选出数据，然后对这些数据再次做一些整理处理，按要求返回成结果，让结果尽可能是简单直接的。因为一个 查询SQL由很多部分组成，所以搞清楚这些部分的执行顺序还挺重要的，这有助于我们更深刻的理解SQL执行过程.

查询执行顺序

1. FROM 和 JOIN

   FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

2. WHERE

   我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式

3. GROUP BY

   如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

4. HAVING

   如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

5. SELECT

   确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.

6. DISTINCT

   如果数据行有重复DISTINCT 将负责排重.

7. ORDER BY

   在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.

8. LIMIT / OFFSET

   最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

练习

Table: Movies (Read-Only)

Table: Boxoffice (Read-Only)

1. 【复习】统计出每一个导演的电影数量（列出导演名字和数量）

   select Director, count(*) as count
   from Movies 
   group by Director;
   

2. 【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)

   select 
   	Director, 
       sum(Domestic_sales + International_sales) as total
   from Movies 
   inner join Boxoffice 
   on Movies.id = Boxoffice.Movie_id
   group by Director;
   

3. 【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量)

   select 
   	Director, 
       sumSales,
       countMovies,
       sumSales / countMovies as avgSales
   from (
   	select 
       	Director, 
           sum(Domestic_sales + International_sales) as sumSales,
           count(title) as countMovies
       from movies
       inner join Boxoffice 
       on movies.id = Boxoffice.Movie_id
       group by director
       having count(title) <> 1
       )
   order by avgSales desc
   limit 1;
   

   Director	Sum_a	Count_a	Sum_a/Count_a
   Pete Docter	1294159000	2	647079500

4. 【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

   select (
     	select
     		(Domestic_sales + International_sales) as sumSales
     	from movies
    	inner join Boxoffice 
     	on Movies.id = boxoffice.movie_id
     	order by sumSales desc 
     	limit 1
   ) -
   (Domestic_sales+International_sales) as saleDiff,
   title
   from movies
   inner join Boxoffice 
   on Movies.id = boxoffice.movie_id
   order by saleDiff desc;