3.3.4.9使用多个表

 

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

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

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

• 描述事件的字段。

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

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

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

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

名称	日期	类型	备注
蓬松	1995-05-15	垃圾	4只小猫，3女，1男
巴菲	1993-06-23	垃圾	5只幼犬，2女，3男
巴菲	1994-06-19	垃圾	3只幼犬，3只母
扢	1999-03-21	兽医	所需的喙拉直
瘦	1997-08-03	兽医	肋骨断裂
鲍泽	1991-10-12	狗窝
ang	1991-10-12	狗窝
ang	1998-08-28	生日	给他一个新的咀嚼玩具
爪子	1998-03-17	生日	给他新的跳蚤项圈
惠斯勒	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子句指定 name列中的 pet表必须的匹配 name列 event表。如果一个名称出现在一个表中而不是其他表中，则该行将不会出现在结果中，因为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     |
+--------+------+-------+------+---------+

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