五、表的关联关系

五、表的关联关系

一对多关系为关系数据库中两个表之间的一种关系，该关系中第一个表中的单个行可以与第二个表中的一个或多个行相关，但第二个表中的一个行只可以与第一个表中的一个行相关。

-- 书籍表
CREATE TABLE book(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(32),
price DOUBLE(5,2),    
pub_id INT NOT NULL
)ENGINE=INNODB CHARSET=utf8;


-- 出版社表
CREATE TABLE publisher(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
email VARCHAR(32),
addr VARCHAR(32)
)ENGINE=INNODB CHARSET=utf8;

-- 插入数据
INSERT INTO book(title,price,pub_id) VALUES
('西游记',15,1),
('三国演义',45,2),
('红楼梦',66,3),
('水浒传',21,2),
('红与黑',67,3),
('乱世佳人',44,6),
('飘',56,1),
('放风筝的人',78,3);

INSERT INTO publisher(id,name,email,addr) VALUES
(1,'清华出版社',"123","bj"),
(2,'北大出版社',"234","bj"),
(3,'机械工业出版社',"345","nj"),
(4,'邮电出版社',"456","nj"),
(5,'电子工业出版社',"567","bj"),
(6,'人民大学出版社',"678","bj");

mysql> select * from book;
+----+------------+-------+--------+
| id | title      | price | pub_id |
+----+------------+-------+--------+
|  1 | 西游记     | 15.00 |      1 |
|  2 | 三国演义   | 45.00 |      2 |
|  3 | 红楼梦     | 66.00 |      3 |
|  4 | 水浒传     | 21.00 |      2 |
|  5 | 红与黑     | 67.00 |      3 |
|  6 | 乱世佳人   | 44.00 |      6 |
|  7 | 飘         | 56.00 |      1 |
|  8 | 放风筝的人 | 78.00 |      3 |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

mysql> select * from publisher;
+----+----------------+-------+------+
| id | name           | email | addr |
+----+----------------+-------+------+
|  1 | 清华出版社     | 123   | bj   |
|  2 | 北大出版社     | 234   | bj   |
|  3 | 机械工业出版社 | 345   | nj   |
|  4 | 邮电出版社     | 456   | nj   |
|  5 | 电子工业出版社 | 567   | bj   |
|  6 | 人民大学出版社 | 678   | bj   |
+----+----------------+-------+------+
6 rows in set (0.00 sec)

5.2、多对多

多对多，在数据库中也比较常见，可以理解为是一对多和多对一的组合。要实现多对多，一般都需要有一张中间表（也叫关联表），将两张表进行关联，形成多对多的形式。

比如一本书有多个作者，一个作者可以出版多本书籍。

-- 作者表
CREATE TABLE author(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 作者表和书籍表的多对多关系表
CREATE TABLE book2author(
id INT NOT NULL UNIQUE AUTO_INCREMENT,
author_id INT NOT NULL,
book_id INT NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 插入数据

INSERT INTO author(NAME) VALUES
('yuan'),
('rain'),
('alvin'),
('eric');

-- 插入关系数据
INSERT INTO book2author(author_id,book_id) VALUES
(1,1),
(1,2),
(2,1),
(3,3),
(3,4),
(1,3);

5.3、一对一

一对一是将数据表“垂直切分”，其实是不常见，或不常用的。也就是 A 表的一条记录对应 B 表的一条记录。

场景：

1. 一个系统必然有 Employee（员工表）（包含字段：EmployeeId、姓名、性别、年龄、电话、地址等），每个员工都为一个用户，所以还有张 User 表（包含字段：UserId（关联 EmployeeId）、用户名、密码、角色等），这样你会发现，整合为一张表是否不太妥当？因为，User 的记录只会在登录时用到，感觉有点违背三大范式中的“确保每列都和主键列直接关联，而不是间接关联”。
2. 还有种情况，这就要根据具体的业务来决定了。如果，当一张表的字段过于太多，而很多字段可能只有在某些情况下，才会使用到，这时也可以考虑使用一对一设计。

在我们这个例子中，比如，作者表可以有一张一对一的作者详细信息表。

CREATE TABLE authorDetail(
id INT PRIMARY KEY AUTO_INCREMENT,
tel VARCHAR(32),
addr VARCHAR(32),
author_id INT NOT NULL unique -- 也可以给author添加一个关联字段：   alter table author add authorDetail_id INT NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 插入数据
INSERT INTO authorDetail(tel,addr,author_id) VALUES
("110","北京",1),
("911","成都",2),
("119","上海",3),
("111","广州",4);

区别于一对多，关联字段加唯一约束！