mysql-设计表时遵循的范式总结

MySQL 基础知识

• Mysql设计成C/S客户端服务器模型，应用作为MySQL Client 向 MySQL Server发送请求，获取响应，因此MySQL适用于集群环境，方便做主从复制，读写分离操作。
• 为了提高效率，MySQL Client 和MySQL Server如何在不同主机上，当然是通过Socket进行网络通信的；如果在一台机器上，那么Client和Server之间是通过共享内存进行通信的，效率比Socket通信更高。
• MySQL的服务器模块采用的是I/O复用+可伸缩的线程池，是实现网络高并发服务器的经典模型。
   

SQL基础

SQL是结构化查询语言的缩写（Structure Query Language），它是关系型数据库的通用语言，非常强大，可以非常高效的进行数据库的增删改查操作，SQL+索引更是可以实现带各种附加条件的高效率查询操作。

SQL 语句主要可以划分为以下 3 个类别：

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、update 和select 等。
DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

关于数据库范式的知识

应用数据库范式可以带来很多好处，但是最重要的好处可以总结为三点:

1)  ，减少数据冗余(这是最关键的好处，其他的好处也是基于此点附带的)

2），消除异常(插入异常，更新异常，删除异常)

3），让数据组织的更加和谐。。。

但是我们需要注意的是范式绝对不是越高越好，范式越高，意味着表越多，多表联合查询的机率就越大，SQL的效率就变低了。

 

第一范式（1NF）:每一列保持原子特性。

列都是基本数据项，不能够再进行分割，否则设计成一对多的实体关系。例如表中的地址字段，可以再细分为省，市，区等不可再分割(即原子特性)的字段。

上图的表就是把地址字段分为更为详细的city,country,street三个字段。

不符合第一范式的数据库我们不能称之为关系型数据库。

第二范式(2NF)属性完全依赖于主键(主要针对联合主键)

范式二主要是针对联合主键说的，意思是非主属性，一定要依赖联合主键的每一个字段，而不能只依赖一部分主键字段，否则设计出来的表存在严重的数据冗余。

举例：

 

学号，姓名，年龄，课程名称，成绩，学分

100      张        22      C++           80.0    10

101      刘        23      C++            90.0     10

 

上面的这个表的设计就没有符合第二范式的设计原理！！！！！！！！

因为我们的成绩的这一个非主属性的它即依赖于课程名称还依赖于学号。

它依赖于俩个属性。而且这种设计造成了数据关系的严重冗余。

这种情况下我们的设计一般是这样:

 

(由于这个例子中的课程和学生的关系为多对多的关系，所以我们需要增加一个关系表studentscore来存取关系。)

学号，姓名，年龄，考试总成绩   =》user表//说明这里存取考试总成绩是因为这个记录可能经常被查询到，所以可以在这个表中存取这个记录，增加搜索效率。

课程id，课程名称，学分    =》course表

userid ,course，score   =》studentscore表

 

查询uid为100总成绩等信息。

select a.name,a.age,sum(b.score) from user a join studentscore b on a.id = b.userid where a.uid = 100;

 

范式三(3NF) 属性不依赖于其他非主属性

要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

 

 

表中所有的非主属性，都必须依赖于主键，而不能依赖于其它的非主属性，否则又要造成数据冗余。

 

下例子中我们的只需要把学院id存储在学生表中即可，不需要将学院的所有的信息都存储进来。

学号（primary key）, 姓名, 年龄, 学院id

 

学院id 学院地址, 学院地点, 学院电话

 

 

一般来说关系型数据库满足第三范式就可以了。

遵守范式设计，设计出来的表，其数据冗余程度是比较低的，在表进行CURD的时候，效率也是比较高的，一般设计能达到范式三就可以了。并不是说范式越高越好，范式越高，拆分的表也就越来越多，意味着每一次的CURD都需要经常做多表的联合操作，比直接单表操作效率肯定要低一些，所以表的设计，要综合数据冗余成都和CURD的效率，所以说一般达到范式三就可以了。

 

范式4：消除表中的多值依赖

 

建立某个表的某个属性需要用户填写某些信息，这些信息

1) 每个用户的填写的信息可能相同，也可能不同。

2）由于每个用户的习惯的问题可能填写的内容的意义相同，但是字段实际内容差距较大的属性。

 

例如，我们填写自己的个人的技能的时候的属性如果设计为一个属性的话那么就会造成以下的情况

 

name      age     school    skill

张三        20        xxx大学   MySQL/C++/Java

李四        21        xxx大学   C++语言/数据库MySQL

 

这种情况下，造成了一定程度上的数据冗余。

正确的做法是将其单独用一张表来存储，又由于其是多对多的关系，我们需要额外设计一个关系表。

 

id name age sex

 

1 张 22 男

 

2 刘 23 女

 

skillid skillname

 

1         MySQL

 

2       C++

 

3        Java

 

userid      skillid

 

1               3

 

1                2

 

1                 1

 

这样就消除了多值依赖的问题。但是我们看到这样的后果是多增加了一张表的设计，相对于当表查询我们搜索地效率降低了。

 

BC范式 每个表只有一个候选键

候选键地意思就是表中每一行地记录该字段地值都不一样。

userid     username    email/phonenum

 

这里第三个字段既可以填写邮箱又可以填写电话号码，这样是不符合BC范式的。

 

范式总结

从上面对于数据库范式进行分解的过程中不难看出，应用的范式越高，表越多。表多会带来很多问题：

1 查询时需要连接多个表，增加了SQL查询的复杂度

2 查询时需要连接多个表，降低了数据库查询性能

因此，并不是应用的范式越高越好，要看实际情况而定。第三范式已经很大程度上减少了数据冗余，并且基本预防了数据插入异常，更新异常，和删除异常了。