MySQL 系列第三天

MySQL 系列第三天

本篇文章讲了实体之间的三种关系，范式基础以及高级 SQL 操作。

实体之间的关系

MySQL 数据库是一个管理存储在磁盘上的数据的媒介，这里的数据必然是由自然世界中产生。而我们自然世界中的数据关系错综复杂，有时很难理清他们的关系。

但是，无论他们有多复杂，实体之间的关系都可以用三种关系来概括，分别是一对一、一对多以及多对多。

实体之间的具体关系是通过一条条记录来表现的，这就好比实体（也就是表）是一个类，而具体的记录就是它的对象。OOP 中讨论的不一直都是对象之间的关系吗？

一对一的关系

一张表中的某条记录只能对应另一种表中的一条记录，反之亦然。比如，存储一个人的信息表中，我们为了效率更高，把常用信息和不常用信息分为两张表存储。然后，给第二张表添加一个第一张表的唯一字段，这样两张表的记录可以互相找到对方。

p_id	username	pwd
1	rightaligned	xxxx
2	centered	xxxxx
3	areneat	xxxx

---

id	adress	tel	p_id
1	杭州	13412433675	1
2	深圳	13512433675	2
3	瑞昌	13612433675	3

---

一对多的关系

表 A 中的一条记录对应表 B 中的多条记录，反过来，就是表 B 中的每一条记录都只能对应着表 A 中的一条记录。比如，省份表和市表，同样是表 B 中添加一条表 A 的唯一字段作为标识。

ProId	name	proNumber
1	江西	16
2	广东	12
3	浙江	5

---

CitId	name	proNumber
1	南昌	16
2	广州	12
3	杭州	5
4	九江	16

---

多对多的关系

更复杂的就是多对多的关系了，比如老师和学生关系。一个学生可以有多个老师，一个老师可以教多个学生。

一个字段显然不能表示出这种多对多的关系，那如何表示呢？一般多对多的关系都是添加一个新表，实际开发当中或更为复杂，但这也是一种解决方案。所以，这里可以在学生表和老师表之外添加一个学生老师关系表来维护他们的关系。

teaId	name	teaNumber
1	王浩	17
2	王爽	12
3	陈康	5
4	李贺	16

---

stuId	name	stuNumber
1	张三	01
2	李四	10
3	王五	58
4	李六	15

---

Id	stunumber	teaNumber
1	01	16
2	10	12
3	10	5
4	15	16

---

什么是范式？

在设计数据库时，做到减少数据冗余是非常重要的，也就是避免相同的数据重复多次。这时候就出现了一种减少数据冗余的规范，名字就叫做「范式」（英文名 Normal Format）。

范式是一种分层的数据规范，一共分为六层。满足下一层范式的前提是上一层范式必须成立。六层范式可以记作：1NF，2NF … 6NF 。它是一种指导规范，而不是必须强制执行的规定。

范式减少冗余度的同时也降低了数据存取的效率，所以很多时候我们并不会严格遵照范式去设计数据库，毕竟时间更宝贵，牺牲点空间算什么呢，能用钱解决的问题都不是问题呢。

第一范式

第一层范式规定，表中的字段必须具有原子性，不可再分。比如，下面第一张表就不符合 1NF 的要求，应该改为第二张表的形式。

Id	time
1	2014-1-4 - 2014-5-4
2	2014-5-4 - 2014-7-4
3	2014-8-4 - 2014-11-4
4	2014-1-4 - 2014-3-4

---

Id	start	end
1	2014-1-4	2014-5-4
2	2014-5-4	2014-7-4
3	2014-8-4	2014-11-4
4	2014-1-4	2014-3-4

---

第二范式

第二层范式规定，不允许出现某字段依赖部分主键的问题，这通常是因为表中存在复合主键。比如，表一中的 tel 和 username 共同作为主键时，gender 只依赖于 username ，这就是部分依赖。解决方法可以是放弃复合主键，添加一个逻辑主键，不过这个是治标不治本。

tel	username	gender
13476452345	rightaligned	男
18223244234	centered	女
17383652833	areneat	女

---

第三范式

第三层范式规定，不得出现传递依赖。比如，下表中的 gender 依赖于 username ，username 又依赖于主键，room 依赖于 calss ，class 又依赖于主键。这就是典型的部分依赖的问题。

id	username	gender	class	room
1	rightaligned	男	1501	218
2	centered	女	1504	213
3	areneat	女	1505	402

---

可以修改为以下三张表的形式

id	username	gender
1	rightaligned	男
2	centered	女

---

id	class	room
1	1501	218
2	1504	213
3	1505	402

---

id	usernameId	classId
1	1	1
2	2	2
3	3	3

---

这里解决第三范式的问题，自然符合了范式的规定，但这会带来效率低下的问题。很多时候我们更希望效率高一点，这时就会不会完全遵照范式的要求来设计数据库。上述最好的方式还是第一种设计，尽管它存在传递依赖的问题，但他换来了效率的提升，是很值得的。