「SQL数据分析系列」5.多表查询

最新推荐文章于 2022-05-23 10:55:50 发布

数据与智能

最新推荐文章于 2022-05-23 10:55:50 发布

阅读量244

点赞数

文章标签：数据库人工智能 python mysql java

本文链接：https://blog.csdn.net/qq_43045873/article/details/116279807

版权

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共9075字，预计阅读时间45分钟。

第五章多表查询

1. 什么是连接

1.1 笛卡尔积

1.2 内连接

1.3 ANSI 连接语法

2. 连接三个及以上的表

2.1 将子查询结果作为查询表

2.2 连续两次使用一个表

3. 自连接

在第二章中，我演示了如何通过规范化过程将相关概念分解成独立的部分，这个示例最终产生两个表格：person和favorite_food。但是如果要创建一个显示某人的姓名、地址和最喜欢的食物的报告，则需要某种机制将这两个表中的数据重新整合在一起，这种机制称为连接（join）。本章主要介绍最简单和最常见的连接，即内连接（inner join）。第十章演示了其他所有连接类型。

1. 什么是连接

尽管对单个表的查询并不少见，但你会发现大多数查询都会涉及两个、三个甚至更多的表。为了举例说明，让我们看一下customer和address表的定义，然后编写一个从这两个表检索数据的查询：

mysql> desc customer; 
+-------------+----------------------+------+-----+-------------------+ 
| Field | Type | Null | Key | Default | 
+-------------+----------------------+------+-----+-------------------+ 
| customer_id | smallint(5) unsigned | NO | PRI | NULL | 
| store_id | tinyint(3) unsigned | NO | MUL | NULL | 
| first_name | varchar(45) | NO | | NULL | 
| last_name | varchar(45) | NO | MUL | NULL | 
| email | varchar(50) | YES | | NULL | 
| address_id | smallint(5) unsigned | NO | MUL | NULL | 
| active | tinyint(1) | NO | | 1 | 
| create_date | datetime | NO | | NULL | 
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | 
+-------------+----------------------+------+-----+-------------------+ 
mysql> desc address; 
+-------------+----------------------+------+-----+-------------------+ 
| Field | Type | Null | Key | Default | 
+-------------+----------------------+------+-----+-------------------+ 
| address_id | smallint(5) unsigned | NO | PRI | NULL | 
| address | varchar(50) | NO | | NULL | 
| address2 | varchar(50) | YES | | NULL | 
| district | varchar(20) | NO | | NULL | 
| city_id | smallint(5) unsigned | NO | MUL | NULL | 
| postal_code | varchar(10) | YES | | NULL | 
| phone | varchar(20) | NO | | NULL | 
| location | geometry | NO | MUL | NULL | 
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | 
+-------------+----------------------+------+-----+-------------------+

假设你要检索每个客户的名字和姓氏，以及他们的街道地址。因此，你的查询需要检索customer.first_name，customer.last_name, 和address.address列。但是如何在同一个查询中从两个表中检索数据呢？答案的关键就在于customer.address_id列，它保存address表中客户记录的ID（更正式的说法是customer.address_id指向address表的外键）。这个查询指示服务器使用customer.address_id列作为customer表和address表之间的桥梁，从而允许在同一查询的结果集中包括两个表中的列，该类操作称为连接（join）。

注意

可以选择创建外键约束来验证一个表中的值是否存在于另一个表中。对于上一个示例，可以在customer表上创建外键约束，以确保插入到customer.address_id列的数据可以在address.address_id列找到。请注意，对两个表执行join操作不一定要有外键约束。

1.1 笛卡尔积

最简单的方法是将customer和address表直接放入查询的from子句中，看看会有怎样的输出。下面的查询检索客户的名字和姓氏以及街道地址，在from子句中包含两个表，并用join关键字分隔：

mysql> SELECT c.first_name, c.last_name, a.address 
 -> FROM customer c JOIN address a; 
+------------+-----------+----------------------+ 
| first_name | last_name | address | 
+------------+-----------+----------------------+ 
| MARY | SMITH | 47 MySakila Drive | 
| PATRICIA | JOHNSON | 47 MySakila Drive | 
| LINDA | WILLIAMS | 47 MySakila Drive | 
| BARBARA | JONES | 47 MySakila Drive | 
| ELIZABETH | BROWN | 47 MySakila Drive | 
| JENNIFER | DAVIS | 47 MySakila Drive | 
| MARIA | MILLER | 47 MySakila Drive | 
| SUSAN | WILSON | 47 MySakila Drive | 
... 
| SETH | HANNON | 1325 Fukuyama Street | 
| KENT | ARSENAULT | 1325 Fukuyama Street | 
| TERRANCE | ROUSH | 1325 Fukuyama Street | 
| RENE | MCALISTER | 1325 Fukuyama Street | 
| EDUARDO | HIATT | 1325 Fukuyama Street | 
| TERRENCE | GUNDERSON | 1325 Fukuyama Street | 
| ENRIQUE | FORSYTHE | 1325 Fukuyama Street | 
| FREDDIE | DUGGAN | 1325 Fukuyama Street | 
| WADE | DELVALLE | 1325 Fukuyama Street | 
| AUSTIN | CINTRON | 1325 Fukuyama Street | 
+------------+-----------+----------------------+ 
361197 rows in set (0.03 sec)

我们有599个客户，address表中有603行数据，那么结果集是如何高达361197行的呢？仔细看，你会发现很多客户的街道地址都是一样的。因为查询没有指定两个表应该如何连接，所以数据库服务器产生笛卡尔积（Cartesian product），即两个表的所有排列组合（599个客户x 603个地址=361197个排列）。该类连接称为交叉连接（cross join），实际应用中很少会使用这种连接，我们会在第十章介绍它。

1.2 内连接

要修改前面的查询，使每个客户只返回一行数据，则需要描述两个表之间的关系。我先前说过customer.address_id列在两个表之间起连接作用，因此需要将此信息添加到from子句的on子句中：

mysql> SELECT c.first_name, c.last_name, a.address 
 -> FROM customer c JOIN address a 
 -> ON c.address_id = a.address_id; 
+-------------+--------------+----------------------------------------+ 
| first_name | last_name | address | 
+-------------+--------------+----------------------------------------+ 
| MARY | SMITH | 1913 Hanoi Way | 
| PATRICIA | JOHNSON | 1121 Loja Avenue | 
| LINDA | WILLIAMS | 692 Joliet Street | 
| BARBARA | JONES | 1566 Inegl Manor | 
| ELIZABETH | BROWN | 53 Idfu Parkway | 
| JENNIFER | DAVIS | 1795 Santiago de Compostela Way | 
| MARIA | MILLER | 900 Santiago de Compostela Parkway | 
| SUSAN | WILSON | 478 Joliet Way | 
| MARGARET | MOORE | 613 Korolev Drive | 
... 
| TERRANCE | ROUSH | 42 Fontana Avenue | 
| RENE | MCALISTER | 1895 Zhezqazghan Drive | 
| EDUARDO | HIATT | 1837 Kaduna Parkway | 
| TERRENCE | GUNDERSON | 844 Bucuresti Place | 
| ENRIQUE | FORSYTHE | 1101 Bucuresti Boulevard | 
| FREDDIE | DUGGAN | 1103 Quilmes Boulevard | 
| WADE | DELVALLE | 1331 Usak Boulevard | 
| AUSTIN | CINTRON | 1325 Fukuyama Street | 
+-------------+--------------+----------------------------------------+ 
599 rows in set (0.00 sec)

现在，由于增加了on子句，结果集输出了预期的599行数据，而不是之前的361197行，该子类指示服务器通过使用address_id列将customer表和address表连接起来。例如，customer表中Mary Smith的行在address_id列中的值是5（示例中未显示），服务器使用此值在address表的address_id列中查找值为5的行，然后从该行的address列中获取值，即'1913 Hanoi Way'。

如果一个表中的address_id列存在一个值，而另一个表中不存在该值，则包含该值的行的连接将失败，并且不会被包括到结果集中。这种类型的连接称为内连接（inner join），它是最常用的连接类型。具体来讲，如果customer表中的某一行的address_id列的值为999，而address表中没有某一行的address_id列的值为999，则该customer行不会包含在结果集中。如果希望不管是否存在匹配项，某表中的所有行都能被包括到结果集中，则需要使用外连接（outer join），但这是我们在第十章才会介绍的内容。

在上一个示例中，我没有在from子句中指定连接类型。但是，当要使用内连接来连接两个表的时候，应该在from子句中显式指明这一点。下面是与上面相同的示例，添加了连接类型（注意关键字inner）：

SELECT c.first_name, c.last_name, a.address
FROM customer c INNER JOIN address a
 ON c.address_id = a.address_id;

如果不指定连接类型，那么服务器将默认使用内连接。然而后面我们会介绍其他几种类型的连接，所以你最好能养成在使用连接时明确指定类型的习惯，这样也方便其他人在将来使用或维护你的查询。

如果用于连接两个表的列的名称相同（比如上一个查询就是这样），则可以使用using子句替代on子句，如下所示：

SELECT c.first_name, c.last_name, a.address 
FROM customer c INNER JOIN address a 
 USING (address_id);

因为using是一种只能在特定情况下使用的语法简化方法，所以我还是建议使用on子句避免混淆。

1.3 ANSI连接语法

本书中用于连接表的符号是从ANSI SQL标准的SQL92版本中引入的。所有主流数据库（Oracle Database、Microsoft SQL Server、MySQL、IBM DB2 Universal Database和Sybase Adaptive Server）都采用了SQL92的连接语法。由于这些数据库大多数在SQL92标准发布之前就存在了，所以它们也包含一些旧的连接语法。例如，所有这些服务器都可以识别前面查询的以下变化：

mysql> SELECT c.first_name, c.last_name, a.address
 -> FROM customer c, address a
 -> WHERE c.address_id = a.address_id;
+------------+------------+------------------------------------+
| first_name | last_name | address |
+------------+------------+------------------------------------+
| MARY | SMITH | 1913 Hanoi Way |
| PATRICIA | JOHNSON | 1121 Loja Avenue |
| LINDA | WILLIAMS | 692 Joliet Street |
| BARBARA | JONES | 1566 Inegl Manor |
| ELIZABETH | BROWN | 53 Idfu Parkway |
| JENNIFER | DAVIS | 1795 Santiago de Compostela Way |
| MARIA | MILLER | 900 Santiago de Compostela Parkway |
| SUSAN | WILSON | 478 Joliet Way |
| MARGARET | MOORE | 613 Korolev Drive |
...
| TERRANCE | ROUSH | 42 Fontana Avenue |
| RENE | MCALISTER | 1895 Zhezqazghan Drive |
| EDUARDO | HIATT | 1837 Kaduna Parkway |
| TERRENCE | GUNDERSON | 844 Bucuresti Place |
| ENRIQUE | FORSYTHE | 1101 Bucuresti Boulevard |
| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |
| WADE | DELVALLE | 1331 Usak Boulevard |
| AUSTIN | CINTRON | 1325 Fukuyama Street |
+------------+------------+------------------------------------+
599 rows in set (0.00 sec)

这种旧的连接方法不包括on子类，而是在from子句通过逗号将命名的表分隔开，接着在where子句中使用连接条件。可能你不想按照SQL92语法的标准来，而偏向于使用旧连接语法，但是ANSI连接语法具备以下优点：

• 连接条件和过滤条件被分为两个不同的子句（on子句和where子句），使得查询更容易理解；

• 每两个表的连接条件都包含在它们自己的on子句中，这样不容易错误地忽略某些连接条件；

• 使用SQL92连接语法的查询可以跨数据库服务器移植，而旧的语法在不同的服务器上略有不同。

SQL92连接语法的优点在同时包含连接和过滤条件的复杂查询中更加明显。考虑以下查询，它仅返回邮政编码为52137的客户：

mysql> SELECT c.first_name, c.last_name, a.address
 -> FROM customer c, address a
 -> WHERE c.address_id = a.address_id
 -> AND a.postal_code = 52137;
+------------+-----------+------------------------+
| first_name | last_name | address |
+------------+-----------+------------------------+
| JAMES | GANNON | 1635 Kuwana Boulevard |
| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |
+------------+-----------+------------------------+
2 rows in set (0.01 sec)

乍一看，要确定where子句中的哪些条件是连接条件，哪些是过滤条件并不容易。再者，使用哪种类型的连接也不是很明显（要知道连接的类型，就要仔细观察where子句中的连接条件，看是否使用了什么特殊字符）。此外，也不容易确定是否错误地忽略了某些连接条件。下面是使用SQL92连接语法的相同查询：

mysql> SELECT c.first_name, c.last_name, a.address
 -> FROM customer c INNER JOIN address a
 -> ON c.address_id = a.address_id
 -> WHERE a.postal_code = 52137;
+------------+-----------+------------------------+
| first_name | last_name | address |
+------------+-----------+------------------------+
| JAMES | GANNON | 1635 Kuwana Boulevard |
| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |
+------------+-----------+------------------------+
2 rows in set (0.00 sec)

在这条查询中，你能很清楚地看到哪个条件用于连接，哪个条件用于过滤。显然，使用SQL92连接语法的语句会更容易理解。

2. 连接三个及以上的表

连接三个表和连接两个表的方法类似，但有些微不同。对于两个表的连接，from子句中包含两个表名和一个连接类型，以及一个用于定义如何连接两个表的on子句。对于三个表的连接，在from子句中将包含三个表和两种连接类型，以及两个on子句。

为了说明这一点，我们将前面的查询改为返回客户的城市而不是街道地址。注意城市名不存储在address表中，而是通过city表的外键进行访问。以下是表格定义：

mysql> desc address;
+-------------+----------------------+------+-----+-------------------+
| Field | Type | Null | Key | Default |
+-------------+----------------------+------+-----+-------------------+
| address_id | smallint(5) unsigned | NO | PRI | NULL |
| address | varchar(50) | NO | | NULL |
| address2 | varchar(50) | YES | | NULL |
| district | varchar(20) | NO | | NULL |
| city_id | smallint(5) unsigned | NO | MUL | NULL |
| postal_code | varchar(10) | YES | | NULL |
| phone | varchar(20) | NO | | NULL |
| location | geometry | NO | MUL | NULL |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+
mysql> desc city;
+-------------+----------------------+------+-----+-------------------+
| Field | Type | Null | Key | Default |
+-------------+----------------------+------+-----+-------------------+
| city_id | smallint(5) unsigned | NO | PRI | NULL |
| city | varchar(50) | NO | | NULL |
| country_id | smallint(5) unsigned | NO | MUL | NULL |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+

为了打印出每个客户的城市，需要使用address_id列从customer转移到address，然后通过city_id列由address表到city表。查询如下：

mysql> SELECT c.first_name, c.last_name, ct.city
 -> FROM customer c
 -> INNER JOIN address a
 -> ON c.address_id = a.address_id
 -> INNER JOIN city ct
 -> ON a.city_id = ct.city_id;
+-------------+--------------+----------------------------+
| first_name | last_name | city |
+-------------+--------------+----------------------------+
| JULIE | SANCHEZ | A Corua (La Corua) |
| PEGGY | MYERS | Abha |
| TOM | MILNER | Abu Dhabi |
| GLEN | TALBERT | Acua |
| LARRY | THRASHER | Adana |
| SEAN | DOUGLASS | Addis Abeba |
...
| MICHELE | GRANT | Yuncheng |
| GARY | COY | Yuzhou |
| PHYLLIS | FOSTER | Zalantun |
| CHARLENE | ALVAREZ | Zanzibar |
| FRANKLIN | TROUTMAN | Zaoyang |
| FLOYD | GANDY | Zapopan |
| CONSTANCE | REID | Zaria |
| JACK | FOUST | Zeleznogorsk |
| BYRON | BOX | Zhezqazghan |
| GUY | BROWNLEE | Zhoushan |
| RONNIE | RICKETTS | Ziguinchor |
+-------------+--------------+----------------------------+
599 rows in set (0.03 sec)

对于这个查询，在from子句中有三个表、两个连接类型和两个on子句，所以看起来蛮复杂的。乍一看，表在from子句中出现的顺序好像很重要，但是其实如果你调换表的顺序，也能得到相同的结果。下面三种查询语句返回的结果都相同：

SELECT c.first_name, c.last_name, ct.city 
FROM customer c 
 INNER JOIN address a 
 ON c.address_id = a.address_id 
 INNER JOIN city ct 
 ON a.city_id = ct.city_id; 
  
SELECT c.first_name, c.last_name, ct.city 
FROM city ct 
 INNER JOIN address a 
 ON a.city_id = ct.city_id 
 INNER JOIN customer c 
 ON c.address_id = a.address_id; 
  
SELECT c.first_name, c.last_name, ct.city 
FROM address a 
 INNER JOIN city ct 
 ON a.city_id = ct.city_id 
 INNER JOIN customer c 
 ON c.address_id = a.address_id;

因为没有使用order by子句指定结果的排序方式，所以唯一的不同就是返回行的顺序了。

连接顺序重要吗

如果你不明白为什么customer/address/city查询的所有三个版本都会产生相同的结果，那么请记住一点：SQL是一种非过程化的语言，也就是说，只要描述需要检索的内容以及涉及的数据库对象，而以何种方式执行查询则是由数据库服务器负责的。数据库服务器使用从数据库对象收集的信息，从三张表中选择一个作为起点（被选择的表称为驱动表(driving table)），然后确定连接其他表的顺序。因此表在from子句中出现的顺序并不重要。

但是，如果你想在查询中以特定的顺序连接表，则可以按照需要的顺序将表排列好，然后指定MySQL关键字straight_join，或者使用SQL Server的force order选项，抑或是在Oracle Database中使用ordered或者leading优化提示。例如，要告诉MySQL服务器使用city表作为驱动表，然后连接address和customer表，可以使用以下语句：

SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city
FROM city ct
 INNER JOIN address a
 ON a.city_id = ct.city_id
 INNER JOIN customer c
 ON c.address_id = a.address_id

2.1 将子查询结果作为查询表

前面介绍了几个包含多个表的查询示例，但是还有一点值得思考：如果某些数据集是由子查询生成的，该怎么办？子查询是第九章的重点，但我在上一章的from子句中已经介绍过子查询的概念。以下查询将customer表与address和city表的子查询相连接：

mysql> SELECT c.first_name, c.last_name, addr.address, addr.city 
 -> FROM customer c 
 -> INNER JOIN 
 -> (SELECT a.address_id, a.address, ct.city 
 -> FROM address a 
 -> INNER JOIN city ct 
 -> ON a.city_id = ct.city_id 
 -> WHERE a.district = 'California' 
 -> ) addr 
 -> ON c.address_id = addr.address_id; 
+------------+-----------+------------------------+----------------+ 
| first_name | last_name | address | city | 
+------------+-----------+------------------------+----------------+ 
| PATRICIA | JOHNSON | 1121 Loja Avenue | San Bernardino | 
| BETTY | WHITE | 770 Bydgoszcz Avenue | Citrus Heights | 
| ALICE | STEWART | 1135 Izumisano Parkway | Fontana | 
| ROSA | REYNOLDS | 793 Cam Ranh Avenue | Lancaster | 
| RENEE | LANE | 533 al-Ayn Boulevard | Compton | 
| KRISTIN | JOHNSTON | 226 Brest Manor | Sunnyvale | 
| CASSANDRA | WALTERS | 920 Kumbakonam Loop | Salinas | 
| JACOB | LANCE | 1866 al-Qatif Avenue | El Monte | 
| RENE | MCALISTER | 1895 Zhezqazghan Drive | Garden Grove | 
+------------+-----------+------------------------+----------------+ 
9 rows in set (0.00 sec)

第四行是第一个子查询，别名为addr，它查找位于加利福尼亚的所有地址。外部查询将子查询结果连接到customer表，以返回居住在加利福尼亚的所有客户的名字、姓氏、街道地址和城市。虽然可以不使用子查询，而是通过简单地连接三个表来编写这个查询，但有时使用一个或多个子查询对于性能和可读性的提升有好处。

想知道内部到底发生了什么，可以单独运行子查询并查看结果。下面是上一个示例的子查询结果：

mysql> SELECT a.address_id, a.address, ct.city 
 -> FROM address a 
 -> INNER JOIN city ct 
 -> ON a.city_id = ct.city_id 
 -> WHERE a.district = 'California'; 
+------------+------------------------+----------------+ 
| address_id | address | city | 
+------------+------------------------+----------------+ 
| 6 | 1121 Loja Avenue | San Bernardino | 
| 18 | 770 Bydgoszcz Avenue | Citrus Heights | 
| 55 | 1135 Izumisano Parkway | Fontana | 
| 116 | 793 Cam Ranh Avenue | Lancaster | 
| 186 | 533 al-Ayn Boulevard | Compton | 
| 218 | 226 Brest Manor | Sunnyvale | 
| 274 | 920 Kumbakonam Loop | Salinas | 
| 425 | 1866 al-Qatif Avenue | El Monte | 
| 599 | 1895 Zhezqazghan Drive | Garden Grove | 
+------------+------------------------+----------------+ 
9 rows in set (0.00 sec)

此结果集包含所有九个位于加利福尼亚的地址。当通过address_id列连接到customer表时，结果集将包含有关这些地址的客户信息。

2.2 连续两次使用一个表

如果要连接多个表，可能会需要多次连接同一个表。例如，在示例数据库中，演员通过film_actor表与他们出演的电影相关联。如果要查找出现两个指定演员的影片，可以编写查询如下：将film表连接到film_actor表，将film_actor表连接到actor表：

mysql> SELECT f.title 
 -> FROM film f 
 -> INNER JOIN film_actor fa 
 -> ON f.film_id = fa.film_id 
 -> INNER JOIN actor a 
 -> ON fa.actor_id = a.actor_id 
 -> WHERE ((a.first_name = 'CATE' AND a.last_name = 'MCQUEEN') 
 -> OR (a.first_name = 'CUBA' AND a.last_name = 'BIRCH')); 
+----------------------+ 
| title | 
+----------------------+ 
| ATLANTIS CAUSE | 
| BLOOD ARGONAUTS | 
| COMMANDMENTS EXPRESS | 
| DYNAMITE TARZAN | 
| EDGE KISSING | 
... 
| TOWERS HURRICANE | 
| TROJAN TOMORROW | 
| VIRGIN DAISY | 
| VOLCANO TEXAS | 
| WATERSHIP FRONTIER | 
+----------------------+ 
54 rows in set (0.00 sec)

此查询返回所有出现了演员Cate McQueen或Cuba Birch的电影。但是如果你要检索同时出现这两个演员的电影，则需要在film表中找到在film_actor表中有两行数据的所有行，其中一行与Cate McQueen关联，另一行与Cuba Birch关联。因此，你需要两次包含film_actor和actor表，每个表都要有不同的别名，以便服务器知道你在不同的子句中引用的是哪一个表：

mysql> SELECT f.title
 -> FROM film f
 -> INNER JOIN film_actor fa1
 -> ON f.film_id = fa1.film_id
 -> INNER JOIN actor a1
 -> ON fa1.actor_id = a1.actor_id
 -> INNER JOIN film_actor fa2
 -> ON f.film_id = fa2.film_id
 -> INNER JOIN actor a2
 -> ON fa2.actor_id = a2.actor_id
 -> WHERE (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN')
 -> AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');
+------------------+
| title |
+------------------+
| BLOOD ARGONAUTS |
| TOWERS HURRICANE |
+------------------+
2 rows in set (0.00 sec)

可以看到，两位演员出演了54部不同的电影，但他们只合作过两部电影。因为多次使用了同一个表，所以示例中用到了表别名。

3. 自连接

不仅可以在同一个查询中多次包含同一个表，而且还可以将一个表连接到它本身。刚开始你可能会觉得这样做很奇怪，但其实这样也是合理的。有些表包含指向自身的外键，这意味着它包含指向同一表中主键的列。虽然示例数据库中并不包含这样的关系，但是让我们假设film表包含prequel_film_id列，它指向电影的父级（例如，电影Fiddler Lost II将使用此列指向父级电影Fiddler Lost）。如果我们添加该附加列，表结构将如下：

mysql> desc film;
+----------------------+-----------------------+------+-----+-------------------+
| Field | Type | Null | Key | Default |
+----------------------+-----------------------+------+-----+-------------------+
| film_id | smallint(5) unsigned | NO | PRI | NULL |
| title | varchar(255) | NO | MUL | NULL |
| description | text | YES | | NULL |
| release_year | year(4) | YES | | NULL |
| language_id | tinyint(3) unsigned | NO | MUL | NULL |
| original_language_id | tinyint(3) unsigned | YES | MUL | NULL |
| rental_duration | tinyint(3) unsigned | NO | | 3 |
| rental_rate | decimal(4,2) | NO | | 4.99 |
| length | smallint(5) unsigned | YES | | NULL |
| replacement_cost | decimal(5,2) | NO | | 19.99 |
| rating | enum('G','PG','PG-13',
 'R','NC-17') | YES | | G |
| special_features | set('Trailers',...,
 'Behind the Scenes')| YES | | NULL |
| last_update | timestamp | NO | | CURRENT_
 TIMESTAMP |
| prequel_film_id | smallint(5) unsigned | YES | MUL | NULL |
+----------------------+-----------------------+------+-----+-------------------+

使用自连接（self-join），你可以编写查询检索有前传的电影，以及电影前传的名字：

mysql> SELECT f.title, f_prnt.title prequel 
 -> FROM film f 
 -> INNER JOIN film f_prnt 
 -> ON f_prnt.film_id = f.prequel_film_id 
 -> WHERE f.prequel_film_id IS NOT NULL; 
+-----------------+--------------+ 
| title | prequel | 
+-----------------+--------------+ 
| FIDDLER LOST II | FIDDLER LOST | 
+-----------------+--------------+ 
1 row in set (0.00 sec)

此查询使用prequel_film_id外键将film表自连接，并指定表别名f和f_prnt，以区分各个表的用途。

数据与智能

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
「SQL数据分析系列」5.多表查询

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition作者 | Alan Beaulieu译者 ...
复制链接

扫一扫