pet表记录了你拥有的宠物,如果你想记录其他关于它们的信息,例如,它们生活中的事件,你需要另一个表,这个表需要包含下列信息:

  • 宠物的名字,这样你就可以知道每个事件属于哪个宠物;
  • 日期,这样你就可以知道每个事件是什么时候发生的;
  • 描述事件的区域;
  • 事件的类型,如果你想将事件归类.

考虑这些,对于event表的CREATE TABLE命令可能会像这样:

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

通过创建一个包含下列信息的文本文件,可以很容易地将记录导入表中

1
2
3
4
5
6
7
8
9
10
11
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

像这样导入记录:

1
2
mysql> LOAD DATA LOCAL INFILE 'C:/SQL/event.txt' INTO TABLE event
-> LINES TERMINATED BY '\r\n';

你可以像检索pet表的记录那样,检索event表.但是如果仅靠event表无法有效地回答你的问题呢?

假设你想了解宠物有宝宝时候的年龄.宝宝的出生的日期存放在event表中,但是宠物自己的出生日期存放在pet表中.这意味着查询需要两个表:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) 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值配对,一个表中出现的名字未出现在另一个表中,根据ON语句的条件,这一行将不会出现在结果中;
  • 因为name列同时出现在两个表中,当引用这一列时,你就必须指明是哪个表中的name列.

你不一定要在两个不同的表中实现连接,有时如果你想将同一表中的记录相比较,将一个表连接至其自身也是很有用的.例如,查询宠物中的繁殖配对,你可以将pet表连接至其自身:

1
2
3
4
5
6
7
8
9
10
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 p2.sex = 'm';
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+

在这个查询中,我们为表起了别名来引用列.

英文原文:

Using More Than one Table