使用多个表
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 |
+--------+------+-------+------+---------+
在此查询中,我们为表名指定别名以引用列,并保持每个列引用与表的哪个实例相关联。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。