SQL入门练习（五）

SQL在线练习网址:SQL在线练习

Lesson 5:多表联合查询（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

说明：

1. INNER JOIN 先将两个表数据连接到一起
2. ON描述的是两个表之间的关联关系
3. INNER JOIN 想成两个集合的交集

实践练习

回到之前的Movies表，再给数据库加一张表 BoxOffice ；字段Movie_id 和Movies表的 Id是1-对-1的关系

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

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 DESC

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

SELECT Director,International_sales 
FROM Movies 
INNER JOIN Boxoffice 
ON Movies.id = Boxoffice.Movie_id 
ORDER BY International_sales DESC 
LIMIT 1