1.复习 SELECT 查询

到目前为止,你已经学习基本的SQL查询语法了,是时候综合这些知识,来尝试下能否解决一些实际问题了.

服务SELECT查询语法

<span class="hljs-operator"><span class="hljs-keyword">SELECT</span> column, another_column, … <span class="hljs-keyword">FROM</span> mytable <span class="hljs-keyword">WHERE</span> </span><i><span class="hljs-operator">condition(s)</span></i><span class="hljs-operator"> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> column <span class="hljs-keyword">ASC</span>/<span class="hljs-keyword">DESC</span> <span class="hljs-keyword">LIMIT</span> num_limit <span class="hljs-keyword">OFFSET</span> num_offset;</span>

练习

正如实际工作中,最大的变化不是SQL语法,而是实际数据的表结构和数据。在本节练习中,我们会放出一个新的表,简单介绍一下这个表,这个表存储了北美一些城市的人口信息和经纬度地理位置信息, 信息.

小贴士?

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

试一下按Task任务中的要求,来组合应用之前学到的查询知识. 如果你成功的解决了这些查询问题,那么就可以继续学习 多表数据查询这个主题了。

CityCountryPopulationLatitudeLongitude
GuadalajaraMexico150080020.659699-103.349609
TorontoCanada279506043.653226-79.383184
HoustonUnited States219591429.760427-95.369803
New YorkUnited States840583740.712784-74.005941
PhiladelphiaUnited States155316539.952584-75.165222
HavanaCuba210614623.05407-82.345189
Mexico CityMexico855550019.432608-99.133208
PhoenixUnited States151336733.448377-112.074037
Los AngelesUnited States388430734.052234-118.243685
Ecatepec de MorelosMexico174200019.601841-99.050674
MontrealCanada171776745.501689-73.567256
ChicagoUnited States271878241.878114-87.629798

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

SELECT * FROM north_american_cities where Country='Canada'

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

SELECT * FROM north_american_cities where Longitude<-87.629798;

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

2.用JOINs进行多表联合查询

数据库范式(normalization)

数据库范式是数据表设计的规范,在范式规范下,数据库里每个表存储的重复数据降到最少(这有助于数据的一致性维护),同时在数据库范式下,表和表之间不再有很强的数据耦合,可以独立的增长 (ie. 比如汽车引擎的增长和汽车的增长是完全独立的). 范式带来了很多好处,但随着数据表的分离,意味着我们要查询多个数据属性时,需要更复杂的SQL语句,也就是本节开始介绍的多表连接技术。这样SQL的性能也会面临更多的挑战,特别是当大数据量的表很多的情况下.

如果一个实体(比如Dog)的属性数据被分散到多个数据表中,我们就需要学习如何通过 JOIN连表技术来整合这些数据并找到我们想要查询的数据项.

用JOINs进行多表联合查询

主键(primary key), 一般关系数据表中,都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的,不会重复复(想象你的身份证号码)。一个最常见的主键就是auto-incrementing integer(自增ID,每写入一行数据ID+1, 当然字符串,hash值等只要是每条数据是唯一的也可以设为主键.

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

用INNER JOIN 连接表的语法

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

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

练习

还会用到之前的Movies表,但我们给数据库加了一张表 BoxOffice 存储着市场相关的信息,比如 收视率和销售数量等,这张表里有一个字段Movie_id 和Movies表的 Id 是1-对-1的关系. 尝试下用 INNER JOIN 来解决下面的问题吧!.

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_idRatingDomestic_salesInternational_sales
58.2380843261555900000
147.4268492764475066843
88206445654417277164
126.4191452396368400000
37.9245852179239163000
68261441092370001000
98.5223808164297503696
118.4415004880648167031
18.3191796233170162503
77.2244082982217900167
108.3293004164438338580
48.1289916256272900000
27.2162798565200600000
137.2237283207301700000

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 |

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

SELECT * FROM movies join Boxoffice on movies.id=Movie_id

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

SELECT * FROM movies join Boxoffice on movies.id=Movie_id where International_sales>Domestic_sales

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

SELECT * FROM movies join Boxoffice on movies.id=Movie_id order by rating desc

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

SELECT Director,Boxoffice.International_sales  FROM movies join Boxoffice on movies.id=Movie_id order by International_sales desc limit 1