SQL学习（自学SQL网xuesql.cn​）

用来学习xuesql.cn网站的内容，包含全部前12讲内容以及参考的解答,多数为sql的查询语句

SQL Lesson 0: 让我给SQL做个自我介绍

SQL, 全称为Structured Query Language（结构化查询语言)。 要讲SQL就绕不开database(数据库), 平时所说的数据库，一般就是指的 Relational database(关系型数据库).

大家知道数据库是用来存储大量数据的一种软件，那么SQL呢是用来操作数据里的数据，具体来说SQL可以做数据查询，数据更新，写入数据等等。

如果把数据库比作盘子，那数据就是盘子里的菜，SQL则是你的筷子。

因为SQL和数据库是天生的一对，又相对简单，目前世界上大部分网站和APP背后的数据都是建立在SQL数据库基础之上

SQL Lesson 1: SELECT 查询 101

Select 查询某些属性列（specific columns）的语法
SELECT column（列名）, another_column, …
FROM mytable（表名）;

Table(表）: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

练习 do it — 请完成如下任务

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

SQL Lesson 2: 条件查询 (constraints) (Pt. 1)

条件查询语法

SELECT column, another_column… 
FROM mytable 
WHERE condition AND/OR another_condition AND/OR …;

Table(表）: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

练习 do it — 请完成如下任务

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

   SELECT * FROM movies where id =6

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

   SELECT * FROM movies where year between 2000 and 2010

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

   SELECT * FROM movies where year not between 2000 and 2010

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

   SELECT * FROM movies where id <=5

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

   SELECT * FROM movies where length_minutes <120 and year >=2010

SQL Lesson 3: 条件查询(constraints)(Pt.2)

学习模糊查询LIKE和通配符%

LIKE	Case insensitive exact string comparison 没有用通配符等价于 =	col_name LIKE "ABC"
NOT LIKE	Case insensitive exact string inequality comparison 没有用通配符等价于 !=	col_name NOT LIKE "ABCD"
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符	col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符

Table(表）: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

练习 do it — 请完成如下任务

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

   SELECT * FROM movies where title like "toy story%"

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

   SELECT * FROM movies where director like "john lasseter"

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

   SELECT * FROM movies where director not like "john lasseter"

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

   SELECT * FROM movies where title like "wall-%"

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

   SELECT * FROM movies where year like "%98"

SQL Lesson 4: 查询结果Filtering过滤 和 sorting排序

选取出唯一的结果的语法
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

结果排序（ordered results）
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

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

Table(表）: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

练习 do it — 请完成如下任务

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

   SELECT distinct director FROM movies order by director

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

   SELECT * FROM movies order by year desc limit 4

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

   SELECT * FROM movies order by title limit 5

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

   SELECT * FROM movies order by title limit 5 offset 5

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

   SELECT title FROM movies 
   where director like "john lasseter" 
   order by length_minutes desc limit 1 offset 2

SQL Lesson 5: 复习 SELECT 查询

Table(表）: North_american_cities 

City	Country	Population	Latitude	Longitude
Guadalajara	Mexico	1500800	20.659699	-103.349609
Toronto	Canada	2795060	43.653226	-79.383184
Houston	United States	2195914	29.760427	-95.369803
New York	United States	8405837	40.712784	-74.005941
Philadelphia	United States	1553165	39.952584	-75.165222
Havana	Cuba	2106146	23.05407	-82.345189
Mexico City	Mexico	8555500	19.432608	-99.133208
Phoenix	United States	1513367	33.448377	-112.074037
Los Angeles	United States	3884307	34.052234	-118.243685
Ecatepec de Morelos	Mexico	1742000	19.601841	-99.050674
Montreal	Canada	1717767	45.501689	-73.567256
Chicago	United States	2718782	41.878114	-87.629798

练习 do it — 请完成如下任务

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

   SELECT * FROM north_american_cities where country like "canada"

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

   SELECT * FROM north_american_cities order by longitude limit 6

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

   SELECT * FROM north_american_cities 
   where country like "mexico" 
   order by population desc limit 2

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

   SELECT * FROM north_american_cities 
   where country like "united states" 
   order by population desc limit 2 offset 2

SQL Lesson 6: 用JOINs进行多表联合查询

用INNER JOIN 连接表的语法

SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

Table: Movies (Read-Only)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

练习 do it — 请完成如下任务

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

   SELECT * FROM movies 
   inner join Boxoffice on movies.id = boxoffice.movie_id

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

   SELECT * FROM movies inner 
   join Boxoffice on movies.id = boxoffice.movie_id 
   where international_sales >domestic_sales

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

   SELECT * FROM movies 
   inner join Boxoffice on movies.id = boxoffice.movie_id order by rating

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

   SELECT director,international_sales 
   FROM movies 
   inner join Boxoffice on movies.id = boxoffice.movie_id 
   order by international_sales desc limit 1

   SQL Lesson 7: 外连接（OUTER JOINs）

   用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;
   

   Table: Employees (Read-Only)

   Role	Name	Building	Years_employed
   Engineer	Becky A.	1e	4
   Engineer	Dan B.	1e	2
   Engineer	Sharon F.	1e	6
   Engineer	Dan M.	1e	4
   Engineer	Malcom S.	1e	1
   Artist	Tylar S.	2w	2
   Artist	Sherman D.	2w	8
   Artist	Jakob J.	2w	6
   Artist	Lillia A.	2w	7
   Artist	Brandon J.	2w	7
   Manager	Scott K.	1e	9
   Manager	Shirlee M.	1e	3
   Manager	Daria O.	2w	6
   Engineer	Yancy I.	null	0
   Artist	Oliver P.	null	0

   Table: Buildings (Read-Only)

   Building_name	Capacity
   1e	24
   1w	32
   2e	16
   2w	20

   练习 do it — 请完成如下任务

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

SELECT distinct building 
FROM Buildings 
LEFT JOIN Employees ON Buildings.Building_name = Employees.Building 
where building not like "null"

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

SELECT distinct building_name,role 
FROM Buildings 
LEFT JOIN Employees ON Buildings.Building_name = Employees.Building

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

SELECT distinct building,capacity 
FROM Buildings 
LEFT JOIN Employees ON Buildings.Building_name = Employees.Building 
where building not like "null"

SQL Lesson 8: 关于特殊关键字 NULLs

SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;

Table: Employees (Read-Only)

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

Table: Buildings (Read-Only)

Building_name	Capacity
1e	24
1w	32
2e	16
2w	20

练习 do it — 请完成如下任务

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

SELECT name,role 
FROM employees 
left join buildings on buildings.Building_name = employees.building
where building is null

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

和上面一个题对比感悟一下两个表互相left join的不同

SELECT building_name 
FROM buildings 
left join employees on buildings.Building_name = employees.building
where name is null

SQL Lesson 9: 在查询中使用表达式

包含表达式的例子
SELECT  particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2）
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500
            （条件要求这个属性绝对值乘以10大于500）;

AS使用别名
SELECT col_expression AS expr_description, …
FROM mytable;

属性列和表取别名的例子
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
  ON mywidgets.id = widget_sales.widget_id;

Table: Movies (Read-Only)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

练习 do it — 请完成如下任务

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

SELECT id,title,(domestic_sales+international_sales)/1000000 
FROM movies 
inner join boxoffice on movies.id=boxoffice.movie_id

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

SELECT id,title,rating*10 
FROM movies 
inner join boxoffice on movies.id=boxoffice.movie_id

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

偶数年份，year%2=0即可；year%2=1为奇数年份

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 sales
FROM movies 
inner join boxoffice on movies.id=boxoffice.movie_id
where director like "john lasseter"
order by sales desc
limit 3

SQL Lesson 10: 在查询中进行统计I (Pt. 1)

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

Function	Description
COUNT(*), COUNT(column)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行.
MAX(column)	找column最大的一行.
AVG(column)	对column所有行取平均值.
SUM(column)	对column所有行求和.

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 

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

练习 do it — 请完成如下任务

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(building) AS counts
   FROM employees
   where building is not null
   group by building
   order by counts

 SQL Lesson 11: 在查询中进行统计II (Pt. 2)

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;

Table(表）: Employees 

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

练习 do it — 请完成如下任务

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

SELECT count(role) 
FROM employees
where role like "artist"
group by role

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

SELECT role,count(role)
FROM employees
group by role

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

SELECT sum(years_employed)
FROM employees
where role like "engineer"
group by role

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

SELECT role,count(*)as counts,
case when building is null then 0 else 1 end as bn
FROM Employees 
GROUP BY role,bn

SQL Lesson 12: 查询执行顺序

这才是完整的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;

Table: Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

练习 do it — 请完成如下任务

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

   SELECT director,count(title)
   FROM movies
   group by director

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

   SELECT director,sum(domestic_sales+international_sales)
   FROM movies join Boxoffice on movies.id = boxoffice.movie_id
   group by director

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

   SELECT director,sum(domestic_sales+international_sales) AS sales,count(*),sum(domestic_sales+international_sales)/count(*) as ave
   FROM movies join Boxoffice on movies.id = boxoffice.movie_id
   group by director
   having count(*)>1
   order by ave desc
   limit 1
   

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

   ​SELECT title,((select MAX(Domestic_sales+International_sales) 
   from Boxoffice)-(Domestic_sales+International_sales))as li
   FROM movies left join Boxoffice on  movies.id=Boxoffice.movie_id
   order by li desc