表与表之间的关系
表与表之间的关系
一: 如何找出两张表之间的关系
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
二、表关系之多对一
举例说明:
出版社 与 书 的关联关系
站在出版社的角度去找一个多对一书的关系,反之站在书的角度找与出版社的一个多对一的关系
出版社 书
一个出版社 多本书
新增加字段press_id
关联方式：foreign key
先创建被关联表:
create table press(
id int primary key auto_increment,
name varchar(20)
);
在创建关联表:
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
插入数据:
- 先往被关联表插入记录
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社');
- 在往关联表插入数据:
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3);
- 显示结果:
mysql> select * from press;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| 1 | 北京工业地雷出版社 |
| 2 | 人民音乐不好听出版社 |
| 3 | 知识产权没有用出版社 |
+----+--------------------------------+
3 rows in set (0.00 sec)
mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| 1 | 九阳神功 | 1 |
| 2 | 九阴真经 | 2 |
| 3 | 九阴白骨爪 | 2 |
| 4 | 独孤九剑 | 3 |
| 5 | 降龙十巴掌 | 2 |
| 6 | 葵花宝典 | 3 |
+----+-----------------+----------+
6 rows in set (0.00 sec)
三: 多对多（即双向的多对一）
举例说明:作者与书
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
| author | #作者表 | fk |
|---|---|---|
| id | name | book_id |
| 1 | egon | |
| 2 | alex | |
| 3 | wpq |
| book | #书表 | fk |
|---|---|---|
| id | python2 | author_id |
| 1 | python3 | |
| 2 | liunx | |
| 3 | php |
将共同关联的单独建一张表,存放作者表与书表的关系，即查询二者的关系查这表就可以了:
| author2book | fk_author(id) | fk_book(id) |
|---|---|---|
| id | name | |
| 1 | egon | |
| 2 | alex | |
| 3 | wpq |
实例如下:
- 创建表
建立author表:
create table author(
id int primary key auto_increment,
name varchar(20));
建立book表:
create table press(
id int primary key auto_increment,
name varchar(20));
这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id));
- 插入数据:
#往作者表插入数据:
insert into author(name) values
('egon'),
('alex'),
('yuanhao'),
('wpq);
#往书里面插入数据:
insert into book(name) values
('九阳神功'),
('九阴真经'),
('九阴白骨爪'),
('独孤九剑'),
('降龙十巴掌'),
('葵花宝典');
- 那么每个作者与自己的代表作如下:
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
了解了对应关系之后就往 author2book表里插入数据;
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1);
- 查询结果:
#根据author2book找对应关系,就可找出那个作者写了那几本书:
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 2 | 1 |
| 8 | 2 | 6 |
| 9 | 3 | 4 |
| 10 | 3 | 5 |
| 11 | 3 | 6 |
| 12 | 4 | 1 |
+----+-----------+---------+
12 rows in set (0.00 sec)
mysql> select * from author;
+----+---------+
| id | name |
+----+---------+
| 1 | egon |
| 2 | alex |
| 3 | yuanhao |
| 4 | wpq |
+----+---------+
4 rows in set (0.00 sec)
mysql> select * from book;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | 九阳神功 |
| 2 | 九阴真经 |
| 3 | 九阴白骨爪 |
| 4 | 独孤九剑 |
| 5 | 降龙十巴掌 |
| 6 | 葵花宝典 |
+----+-----------------+
6 rows in set (0.00 sec)
四: 一对一
举例:
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：foreign key+unique
一定是student来foreignkey表customer，这样就保证了(先有客户)
1 学生一定是一个客户，
2 客户不一定是学生，但有可能成为一个学生
- 建表:
#先创建客户表
create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null);
#再创建学生表:
create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade);
#先加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230);
#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5);
#查询结果:
mysql> select * from student;
+----+-------------+-------------+
| id | class_name | customer_id |
+----+-------------+-------------+
| 1 | 脱产3班 | 3 |
| 2 | 周末19期 | 4 |
| 3 | 周末19期 | 5 |
+----+-------------+-------------+
3 rows in set (0.00 sec)
mysql> select * from customer;
+----+-----------+-----------+-------------+
| id | name | qq | phone |
+----+-----------+-----------+-------------+
| 1 | 李飞机 | 31811231 | 13811341220 |
| 2 | 王大炮 | 123123123 | 15213146809 |
| 3 | 守榴弹 | 283818181 | 1867141331 |
| 4 | 吴坦克 | 283818181 | 1851143312 |
| 5 | 赢火箭 | 888818181 | 1861243314 |
| 6 | 战地雷 | 112312312 | 18811431230 |
+----+-----------+-----------+-------------+
6 rows in set (0.00 sec)
