3.3.4.9 使用多个表

宠物表会记录您拥有的宠物。如果您想记录有关它们的其他信息，例如它们生活中的事件，例如去看兽医或产仔时，您需要另一张表。这张表应该是什么样子的？它需要包含以下信息：

• 宠物名称，以便您知道每个事件与哪种动物有关。
• 一个日期，以便您知道事件发生的时间。
• 描述事件的字段。
• 一个事件类型字段，如果您希望能够对事件进行分类。

鉴于这些考虑，事件表的 CREATE TABLE 语句可能如下所示：

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

与 pet 表一样，通过创建包含以下信息的制表符分隔的文本文件来加载初始记录是最简单的。

像这样加载记录：

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

根据您从对宠物表运行的查询中了解到的信息，您应该能够对事件表中的记录执行检索；原理是一样的。但是，事件表本身何时不足以回答您可能提出的问题？

假设您想了解每只宠物的产仔年龄。我们之前看到了如何从两个日期计算年龄。母亲的产仔日期在事件表中，但要计算她在该日期的年龄，您需要她的出生日期，该日期存储在宠物表中。这意味着查询需要两个表：

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 子句连接两个表，因为查询需要从这两个表中提取信息。
• 当组合（连接）来自多个表的信息时，您需要指定一个表中的记录如何与另一个表中的记录匹配。这很容易，因为它们都有一个名称列。该查询使用 ON 子句根据名称值匹配两个表中的记录。该查询使用 INNER JOIN 来组合表。当且仅当两个表都满足 ON 子句中指定的条件时，INNER JOIN 才允许任一表中的行出现在结果中。在此示例中，ON 子句指定宠物表中的名称列必须与事件表中的名称列匹配。如果名称出现在一个表中，而另一个表中没有，则该行不会出现在结果中，因为 ON 子句中的条件失败。
• 因为 name 列出现在两个表中，所以在引用该列时必须具体说明您指的是哪个表。这是通过将表名添加到列名来完成的。

您不需要有两个不同的表来执行连接。如果要将表中的记录与同一表中的其他记录进行比较，有时将表连接到自身很有用。例如，要在您的宠物中找到繁殖对，您可以将宠物表与自身连接起来以生成相似物种的活雄性和雌性候选对：

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     |
+--------+------+-------+------+---------+

在此查询中，我们为表名指定别名以引用列，并保持每个列引用与表的哪个实例相关联。