MySQL :: MySQL 8.4 参考手册 :: 5.3.4.9 使用多个表

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 使用多个表

5.3.4.9 使用多个表

pet 表跟踪你拥有的宠物。如果你想记录有关它们的更多信息，例如兽医的访问记录或幼崽出生时间等事件，你需要另一个表。这个表应该是什么样的呢？它需要包含以下信息

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

考虑到这些因素，CREATE TABLE 语句用于 event 表可能如下所示

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

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

名称	日期	类型	备注
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	兽医	肋骨骨折
Bowser	1991-10-12	犬舍
Fang	1991-10-12	犬舍
Fang	1998-08-28	生日	给了他一个新的咀嚼玩具
Claws	1998-03-17	生日	给了他一个新的跳蚤项圈
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 列出现在两个表中，所以你必须在引用该列时明确指定是指哪个表。这是通过在列名前加上表名来完成的。

你无需拥有两个不同的表才能执行连接。有时，如果你想将表中的记录与该表中的其他记录进行比较，将表连接到自身很有用。例如，要查找你宠物中的繁殖对，你可以将 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     |
+--------+------+-------+------+---------+

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

上一个首页向上下一个