乐趣区

MySQL™-参考手册使用多个表

jiezi

使用多个表

pet表记录你拥有的宠物，如果你想记录关于他们的其他信息，比如他们生活中的事件，比如去看兽医或者当幼崽出生时，需要另一张表，这张表应该是什么样的？它需要包含以下信息：

  • 宠物名称，以便你了解每个事件所属的动物。
  • 日期，以便你知道事件发生的时间。
  • 描述事件的字段。
  • 如果你希望能够对事件进行分类，则有事件类型字段。

考虑到这些因素，event表的 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 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel
Fang 1991-10-12 kennel
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday

像这样加载记录：

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

根据你从 pet 表上运行的查询中学到的内容，你应该能够对 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 来组合表，当且仅当两个表都满足 ON 子句中指定的条件时，INNER JOIN才允许来自任一表的行出现在结果中。在此示例中，ON子句指定 pet 表中的 name 列必须与 event 表中的 name 列匹配。如果 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     |
+--------+------+-------+------+---------+

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

上一篇：计数行

退出移动版