3.3.4.9 使用多个 table

pettable 可跟踪您拥有哪些宠物。如果您想记录有关他们的其他信息，例如他们生活中的事件，例如看兽医或出生时产仔，则需要另一张桌子。该 table 应该是什么样的？它需要包含以下信息：

• 宠物的名字，以便您知道每个事件与哪种动物有关。

• 日期，以便您知道事件发生的时间。

• 描述事件的字段。

• 事件类型字段，如果您希望能够对事件进行分类。

考虑到这些考虑因素，eventtable 的CREATE TABLE语句可能看起来像这样：

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

与pettable 一样，通过创建包含以下信息的制 table 符分隔的文本文件，最容易加载初始记录。

name	date	type	remark
Fluffy	1995-05-15	litter	4 只小猫，3 女，1 男
Buffy	1993-06-23	litter	5 只幼犬，2 女，3 男
Buffy	1994-06-19	litter	3 只幼犬，女 3 名
Chirpy	1999-03-21	vet	所需的喙拉直
Slim	1997-08-03	vet	broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	给他一个新的咀嚼玩具
Claws	1998-03-17	birthday	给他新的跳蚤项圈
Whistler	1998-12-09	birthday	First birthday

像这样加载记录：

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

根据您从在pettable 上运行的查询中学到的内容，您应该能够对eventtable 中的记录执行检索；原理是一样的。但是eventtable 本身何时不足以回答您可能会问的问题？

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

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子句连接两个 table，因为查询需要从两个 table 中提取信息。

• 在合并(合并)来自多个 table 的信息时，您需要指定如何将一个 table 中的记录与另一个 table 中的记录匹配。这很容易，因为它们都有name列。该查询使用ON子句根据name值匹配两个 table 中的记录。

该查询使用INNER JOIN组合 table。且仅当两个 table 都满足ON子句中指定的条件时，INNER JOIN才允许其中一个 table 中的行出现在结果中。在此示例中，ON子句指定pettable 中的name列必须与eventtable 中的name列匹配。如果一个名称出现在一个 table 中而不是另一个 table 中，则该行将不会出现在结果中，因为ON子句中的条件失败。

• 因为name列都出现在两个 table 中，所以在引用该列时，必须具体说明要使用的 table。这是通过在 table 名之前添加列名来完成的。

您无需具有两个不同的 table 即可执行联接。如果要将 table 中的记录与同一 table 中的其他记录进行比较，有时将 table 与其自身连接会很有用。例如，要在您的宠物中查找繁殖对，您可以将pettable 自身与它们配对，以产生候选的成对活体雄性和雌性类似物种：

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

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