MySQL :: MySQL 8.4 Reference Manual :: 5.3.4.9 Using More Than one Table

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Using More Than one Table

5.3.4.9 使用多个表

pet 表中记录了您拥有的宠物。如果您想记录其他关于它们的信息，例如宠物的生活事件，如到兽医诊所或生育小狗，您需要另一个表。这个表需要包含以下信息：

宠物名称，以便知道每个事件所属的动物。
日期，以便知道事件发生的时间。
描述事件的字段。
如果您想能够分类事件，需要一个事件类型字段。

考虑这些因素，CREATE TABLE 语句可能如下所示：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

与 pet 表一样，最容易通过创建一个使用制表符分隔的文本文件来加载初始记录，该文件包含以下信息。

name	date	type	remark
Fluffy	1995-05-15	生育	4只小猫，3只母猫，1只公猫
Buffy	1993-06-23	生育	5只小狗，2只母狗，3只公狗
Buffy	1994-06-19	生育	3只小狗，3只母狗
Chirpy	1999-03-21	兽医	需要修整喙部
Slim	1997-08-03	兽医	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	生日	送给他一个新的啃嚼玩具
Claws	1998-03-17	生日	送给他一个新的 flea collar
Whistler	1998-12-09	生日	第一个生日

使用以下方式加载记录：

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

根据您对pet表的查询结果，您应该能够在event表中执行检索操作；原则相同。但是，当event表单独不足以回答您可能问的问题时，情况又如何？

假设您想知道每只宠物在生下后代时的年龄。我们之前已经看到如何从两个日期中计算年龄。母宠物的生日在event表中，但是要计算她在该日期的年龄，您需要她的出生日期，这个日期存储在pet表中。这意味着查询需要两个表：

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

关于这个查询有几个需要注意的地方：

FROM子句将两个表连接，因为查询需要从这两个表中提取信息。
当从多个表中组合（连接）信息时，您需要指定如何在一个表中的记录与另一个表中的记录进行匹配。这很容易，因为它们都有一个name列。查询使用ON子句将两个表的记录根据name值进行匹配。

该查询使用了INNER JOIN将表格组合起来。一个INNER JOIN允许来自两个表的行出现在结果中，如果且仅如果两个表都满足在ON子句中指定的条件。在这个示例中，ON子句指定了pet表中的name列必须与event表中的name列匹配。如果一个名称只出现在一个表中而不出现在另一个表中，那么该行不会出现在结果中，因为在ON子句中的条件失败。
由于name列出现在两个表中，所以当引用该列时，你需要明确指定是哪个表。通过将表名 prepended 到列名，可以实现这一点。

你不一定需要两个不同的表来执行 join。有时候，join一个表到自己身上，如果你想比较表中的记录到其他记录中，可以很有用。例如，要找到你的宠物之间的配对，你可以将pet表与自己连接，以生产候选配对的活男性和女性同种动物：

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

在这个查询中，我们为表名指定了别名，以便引用列并保持每个列引用关联到的表实例。

PREV HOME UP NEXT