表的关系

表的关系

mysql中的表与表之间大致可以分成3种关系, 分别是一对多, 一对一以及多对多关系, 而如何区分这几种关系便变得至关重要了.  在mysql中定义表之间的关系需要用到关键字foreign key. 下面就是详细介绍这三种关系.

1. 一对多

　　现在假设我们需要记录员工的信息表,  那么让我们先零零洒洒罗列出员工大致应该有的属性, 大致如下面所示

create table emp(
    id int primary key auto_increment,
    name char(20),
    age int,
    gender enum('male', 'female'),
    depart_name varchar(30),
    depart_comment varchar(30)
);

　　分析1:

　　假如当我们按这样的表结构添加了许多记录后, 现在有一个需求, 需要把部门的名称和描述更改一下, 那么这样就需要我们每次都频繁的修改整个员工表,这样就非常的麻烦, 此外, 上面这个表结构不是很清晰, 员工表的信息还掺杂了部门的信息, 如果员工还有其他生日, 联系方式等等, 这个表就会很庞大, 整个表的结构就显得杂乱, 这时候我们就可以考虑把上面这个员工表拆分为两个表, 员工表和部门表,  这样整个记录信息就明了很多, 且之间也实现了解耦, 表的扩展性也强了很多. 

　　分析2:

　　选择了拆分为2个表后, 就说明这两个表之间存在一种关系, 但具体是什么关系,  还需要我们来仔细分析. 此时我们一般可以采用"换位思考法", 即分别站在这两个表的角度上,  看待和另一个表的关系.

　　例如: 现在站在员工表的角度上看, 我们可以知道,  员工表里的多条记录可以对应于一条部门信息表的记录.  即多个员工可以属于一个部门;  再反过来站在部门表的角度上看,  同样很容易的就可以知道一个部门可以包含多个员工, 或者说多个部门不能属于一个员工, 这样这两个表的关系就可以确定了, 属于一对多的关系.  

　　用同样的方法我们还可以分析下面的多对多一对一的关系.

　　分析完后我们就可以建表了, 我们需要利用外键来明确两表之间的关系. 在员工表中我们就可以利用这个外键来找到部门表中属于自己的数据, 这个外键需要指向对方唯一的列.

create table dep(
    id int primary key auto_increment,
    dep_name varchar(25),
    dep_comment varchar(50)
);

create table emp(
    id int primary key auto_increment,
    name char(20), 
    age int,
    gender enum('male', 'female'),
    dep_id int,
    foreign key(dep_id) references dep(id)
);

　　注意:  上面的建表顺序必须要先建部门表, 即一对多关系中的"一", 然后在员工表中,  指定外键, 用来指向部门的id字段, 确认两表的关系.

　　分析3:

　　 有了外键的约束, 上面员工信息添加的部门字段在部门表中必须存在, 但是这也带来了一个问题, 就是当我们需要删除某一个部门或是更新部门id的时候,  外键约束的关系使得我们只能够先删除这个部门所属的所有员工才能删除或更新部门,  这就使得操作变得繁琐和麻烦, 这个时候我们可以在建立外键关系的指定级联删除和级联更新的特点, 如此删除对应部门的时候, 会把所有属于该部门的员工一起删除, 更新id的时候, 也会一起更改所有属于这个部门的员工相应的信息. 

　　下面就是外键导致的报错信息...

(1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`db_8_20`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))')

　　我们只需要在外键添加on delete cascade on update cascade 这样的约束就允许直接删除了, 新建的表的信息如下所示.

create table dep1(
    id int primary key auto_increment,
    dep_name varchar(25),
    dep_comment varchar(50)
);

create table emp1(
    id int primary key auto_increment,
    name char(20), 
    age int,
    gender enum('male', 'female'),
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on delete cascade
    on update cascade
);


insert into dep1(dep_name, dep_comment) values ('销售', '销售销售'), ('开发', '埋头开发中...'), ('管理', '管理部门权力大');

insert into emp1(name, age, gender, dep_id) values('a', 12, 'male', 1), ('b', 15, 'female', 2), ('c', 16, 'male', 2), ('d', 18, 'male', 3);

delete from dep1 where id = 2;

select * from emp1;
+----+------+-----+--------+--------+
| id | name | age | gender | dep_id |
+----+------+-----+--------+--------+
| 1  | a    | 12  | male   | 1      |
| 4  | d    | 18  | male   | 3 　　　|
+----+------+-----+--------+--------+

　　可以清晰的看到删除了部门id为2的所有员工记录了. 这就是级联删除.

2. 多对多

　　多对多的关系在生活中也很常见, 例如一篇论文可以拥有多位作者, 而这些作者又可以写很多论文,  在学校里, 教师岗位可以拥有许多人, 但是一个人可以既是教师又是管理层人员等等...

　　下面举的例子是书和作者的关系, 建表之前的关系分析利用"换位思考"的角度同样可以清晰的知道,  站在书的角度, 可以拥有多个联合作者, 站在作者的角度. 可以写了许多书. 这就是典型的多对多关系.

　　在mysql的代码层面,  这种关系就需要利用第三个表来存储他们的关系, 因为按照外键的规则, 建立外键的时候, 必须先建立它所引用的那张表, 而这时候是相互引用的关系, 就只能借助第三张关系表来确立明确的外键关系了.

create table book(
    id int primary key auto_increment,
    title varchar(20),
    price float
);

create table author(
    id int primary key auto_increment,
    name varchar(21),
    age int
);

create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id)
    on delete cascade
    on update cascade,
    author_id int,
    foreign key(author_id) references author(id)
    on delete cascade
    on update cascade
);

insert into book(title, price) values('三国演义', 50.9), ('水浒传', 59), ('西游记', 80), ('流浪记', 30.1);
insert into author(name, age) values('罗贯中', 50), ('施耐庵', 39), ('吴承恩', 78);
insert into book2author(book_id, author_id) values(1, 1), (2, 2), (3, 3), (4, 1);

select * from author;
select * from book;
select * from book2author;

delete from author where id = 1;

select * from author;
select * from book;
select * from book2author;

+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 2  | 施耐庵 | 39  |
| 3  | 吴承恩 | 78  |
+----+--------+-----+
2 rows in set
Time: 0.012s

+----+----------+-------+
| id | title    | price |
+----+----------+-------+
| 1  | 三国演义 | 50.9  |
| 2  | 水浒传   | 59.0  |
| 3  | 西游记   | 80.0  |
| 4  | 流浪记   | 30.1  |
+----+----------+-------+
4 rows in set
Time: 0.011s

+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 2  | 2       | 2         |
| 3  | 3       | 3         |
+----+---------+-----------+
2 rows in set
Time: 0.011s

　　多对多种的关系通过级联删除和更新操作,  同样就不会报因为外键所引起的错误了.

3. 一对一

　　一对一应用的场景大致就是这了, 在表的数据数据字段特别多的时候, 我们可以拆分为多个表, 这些表之间的关系就是一对一, 然后把常用的数据放在一张表中.

　　用严格的话来讲, 就是表一的一条记录只能在表2找到唯一的一条记录, 反之亦然.

create table stu_detail(
    id int primary key auto_increment,
    home varchar(20),
    hobby varchar(20),
    email varchar(20)
);


create table student(
    id int primary key auto_increment,
    name varchar(20),
    class varchar(20), 
    d_id int unique,
    foreign key(d_id) references stu_detail(id)
);

insert into stu_detail(home, hobby, email) values('山水沟子', '吃喝', '12345@163.com'),('三室一厅', '睡觉', '22225@163.com');
insert into student(name, class, d_id) values('小红', '3班', 1), ('小张', '8班', 2);