The pet table keeps track of which pets you have. pet表记录您养的宠物。If you want to record other information about them, such as events in their lives like visits to the vet or when litters are born, you need another table. What should this table look like? 如果你想记录关于他们的其他信息，比如他们生活中的一些事件，比如去看兽医或者一窝婴儿出生时，你需要另一张桌子。这张桌子应该是什么样子？It needs to contain the following information:它需要包含以下信息：

Given these considerations, the CREATE TABLE statement for the event table might look like this:考虑到这些因素，event表的CREATE TABLE语句可能如下所示：

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

As with the pet table, it is easiest to load the initial records by creating a tab-delimited text file containing the following information.与pet表一样，通过创建包含以下信息的以制表符分隔的文本文件来加载初始记录是最容易的。

Load the records like this:按如下方式加载记录：

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

Based on what you have learned from the queries that you have run on the pet table, you should be able to perform retrievals on the records in the event table; the principles are the same. 根据您从pet表上运行的查询中学到的知识，您应该能够对event表中的记录执行检索；原则是一样的。But when is the event table by itself insufficient to answer questions you might ask?但什么时候event表本身不足以回答您可能提出的问题？

Suppose that you want to find out the ages at which each pet had its litters. 假设你想知道每只宠物产仔的年龄。We saw earlier how to calculate ages from two dates. 我们在前面看到了如何从两个日期计算年龄。The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. 母亲的产仔日期在event表中，但要计算她在该日期的年龄，您需要她的出生日期，该日期存储在pet表中。This means the query requires both tables:这意味着查询需要两个表：

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

There are several things to note about this query:关于此查询，有几点需要注意：

You need not have two different tables to perform a join. 执行联接不需要两个不同的表。Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table. 有时，如果要将表中的记录与同一表中的其他记录进行比较，将表连接到表本身是很有用的。For example, to find breeding pairs among your pets, you can join the pet table with itself to produce candidate pairs of live males and females of like species:例如，要在您的宠物中找到繁殖配对，您可以将宠物表自身加入，以生成同类的活雄、雌配对：

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

In this query, we specify aliases for the table name to refer to the columns and keep straight which instance of the table each column reference is associated with.在这个查询中，我们为表名指定别名以引用列，并明确每个列引用与表的哪个实例关联。