目录
一、表设计三范式
关系数据库中的关系必须满足一定的要求，即满足不同的范式(Normal Format, 简单NF)。范式为设计数据库中表内关系，表与表之间的关系提供了规范和标准，任何按照范式设计的表结构将是最优结构，同时也可以避免数据冗余，减少数据库的存储空间，减轻维护数据完整性的麻烦。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、第四范式（4NF）、第五范式（5NF）、第六范式（6NF）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式（3NF）就行了。
一、范式三约定
1、第一范式:确保每列保持原子性
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算 满足了数据库的第一范式
2、第二范式:确保每行的唯一性
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。即要求每个实体都必须具有主键进行区分。
要求实体的属性完全依赖于主关键字,不是依赖部分关键字
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
下述表结构实现了第二范式要求：
3、第三范式：确保每列都和主键列直接相关,而不是间接相关
第三范式需要确保数据表除外键外，其他非主键属性不得重复出现在第二张表上。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
二、范式应用举例分析
下面以一个学校的学生系统为例分析说明，这几个范式的应用：
-
第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。
这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统（DBMS）中，傻瓜也不可能做出不符合第一范式的数据库，因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此，你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。
首先我们确定一下要设计的内容包括那些。学号、姓名、年龄、性别、课程名称、课程学分、系别、学科成绩，系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。对于这些信息，关心的问题有如下几个方面：学生有那些基本信息？学生选了那些课，成绩是什么？每个课的学分是多少？学生属于那个系，系的基本信息是什么？
-
第二范式（2NF）实例分析：
首先我们考虑，把所有这些信息放到一个表中(学号，姓名、年龄、性别、课程名称、课程学分、系别、学科成绩，系办地址、系办电话)，互相存在如下的依赖关系：(学号) → (姓名，年龄，性别，系别，系办地址、系办电话)
(课程名称) → (学分)
(学号，课程) → (学科成绩)
问题分析：
因此不满足第二范式的要求，会产生如下问题数据冗余：同一门课程由n个学生选修，”学分”就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。
更新异常：
-
若调整了某门课程的学分，数据表中所有行的”学分”值都要更新，否则会出现同一门课程学分不同的情况。
-
假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有”学号”关键字，课程名称和学分也无法记录入数据库。
-
删除异常：假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。
解决方案：
把选课关系表StudentCourse改为如下三个表：
学生：Student (学号，姓名, 年龄，性别，系别，系办地址、系办电话)
课程：Course (课程名称, 学分)
选课关系：Score (学号, 课程名称, 成绩)
-
-
第三范式（3NF）实例分析：
接着看上面的学生表Student(学号，姓名, 年龄，性别，系别，系办地址、系办电话)，关键字为单一关键字”学号”，因为存在如下决定关系：
(学号)→ (姓名，年龄，性别，系别，系办地址、系办电话)
但是还存在下面的决定关系
(学号)→ (所在学院)→(学院地点，学院电话)
即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。它也会存在数据冗余、更新异常、插入异常和删除异常的情况。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了：
学生：Student (学号，姓名，年龄，性别，系别)
系别：Dept (系别，系办地址、系办电话)。
上面的数据库表就是符合I,II,III范式的，消除了数据冗余、更新异常、插入异常和删除异常。
三、School数据库举例
1、学生表
| 编号 | 学号 | 姓名 | 年龄 | 学历 | 班级编号 |
|---|---|---|---|---|---|
| 1 | WN001 | 靳小杰 | 23 | 大专 | T03 |
| 2 | WN002 | 刘小畅 | 24 | 本科 | T03 |
| 3 | WN003 | 姜小泊 | 25 | 大专 | T03 |
| 4 | WN004 | 毛小东 | 22 | 本科 | T03 |
| 5 | WN005 | 王小鹏 | 21 | 大专 | T03 |
| 6 | WN006 | 文小玉 | 25 | 本科 | T03 |
| 7 | WN007 | 周小钊 | 23 | 本科 | T02 |
| 8 | WN008 | 王小杰 | 26 | 大专 | T02 |
| 9 | WN009 | 何小松 | 30 | 本科 | T02 |
| 10 | WN010 | 杨小宁 | 28 | 本科 | T02 |
2、学生详细信息表
| 编号 | 学号 | 身份证 | 地址 | 毕业院校 |
|---|---|---|---|---|
| 1 | WN001 | 1111 | 西安 | 交大 |
| 2 | WN002 | 222 | 宝鸡 | 科大 |
| 3 | WN003 | 333 | 汉中 | 电大 |
| 4 | WN004 | 444 | 榆林 | 农大 |
| 5 | WN005 | 555 | 延安 | 林大 |
3、班级表
| 编号 | 班级编号 | 班级名称 | 班主任 |
|---|---|---|---|
| 1 | T01 | 一期班 | 李小华 |
| 2 | T02 | 二期班 | 秦小超 |
| 3 | T03 | 三期班 | 王小斌 |
| 4 | T04 | 四期班 | 张小三 |
| 5 | T05 | 五期班 | 周小四 |
4、课程表
| 编号 | 课程编号 | 课程名称 | 任课老师 |
|---|---|---|---|
| 1 | C01 | 外语 | 李小华 |
| 2 | C02 | 数学 | 秦小超 |
| 3 | C03 | 语文 | 王小斌 |
| 4 | C04 | 编程 | 张小三 |
| 5 | C05 | 艺术 | 周小四 |
| 6 | C06 | 体育 | 王小五 |
| 7 | C07 | 历史 | 赵小六 |
5、成绩表
| 编号 | 学号 | 课程编号 | 成绩 | 选课日期 |
|---|---|---|---|---|
| 1 | WN002 | C01 | 70 | 2018 |
| 2 | WN002 | C03 | 75 | 2017 |
| 3 | WN002 | C04 | 90 | 2019 |
| 4 | WN005 | C02 | 70 | 2018 |
| 5 | WN005 | C01 | 75 | 2017 |
| 6 | WN008 | C03 | 90 | 2019 |
| 7 | WN008 | C04 | 70 | 2017 |
小结
-
数据库表设计时，需要遵从“范式三约定”
-
第一范式：列具有原子性，不可再分；
-
第二范式：遵循第一范式基础上，表中的列必须要与主键相关；
-
第三范式：遵循第一，二范式基础上，表中的列必须要与主键直接相关，不能间接相关。间接相关的，应该分到其它表，使用外键建立关系维护
二、主键约束和外键约束
所谓约束：就是指我们在创建表的过程中，给表的字段添加一些条件！在这些条件的配合下，可以保证数据的唯一性，完整性。有的约束针对整行有效 ，有的约束只针对某一列有效。
一、关于完整性约束
-
域完整性：限制数据类型、外键约束、默认值、非空约束。
-
实体完整性：唯一约束、主键约束、自增列。
-
参照完整性：主外键关联。
-
自定义完整性：规则、存储过程、触发器。
二、主键约束
表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列的组合叫做表的主键。如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号，课程号）作为成绩表的主键（组合键）。
一个表只能有一个主键，主键约束确保了表中的行是唯一的。
表中可以没有主键，但是通常情况下应当为表设置一个主键。
主键选择原则：
-
最少性：尽量选择一个键作为主键
-
稳定性：尽量选择数值更新少的值作为主键
-
去业务性：尽量不选择具备业务含义的列作为主键，例如：身份证，电话号码……
创建主键的3种方式：
-
直接在字段定义后面声明主键
create table student_info (id bigint primary key, student_name varchar(20), age tinyint, gender tinyint, telphone varchar(11), study_direction varchar(20)) engine=innodb character set = utf8mb4;id bigint primary key 直接在id列后面，添加主键约束
-
用constraint声明主键
create table student_info (id bigint, student_name varchar(20), age tinyint, gender tinyint, telphone varchar(11), study_direction varchar(20), CONSTRAINT pk_student_info primary key (id)) engine=innodb character set = utf8mb4; # pk_student_info 规范：pk_表名 或者： create table student_info (id bigint, student_name varchar(20), age tinyint, gender tinyint, telphone varchar(11), study_direction varchar(20), PRIMARY KEY (student_name, telphone)) engine=innodb character set = utf8mb4; # 复合主键 -
用ALTER语句补充声明主键
create table student_info ( id bigint, student_name varchar(20), age tinyint, gender tinyint, telphone varchar(11), study_direction varchar(20)) engine=innodb character set = utf8mb4; ALTER TABLE student_info add CONSTRAINT pk_student_info PRIMARY KEY (id);
删除主键的方式：
ALTER TABLE student_info DROP PRIMARY KEY;三、外键约束
什么是外键：简单地说，就是“子表”中对应于“主表”的列，在子表中称为外键或者引用键。
它的值要求与主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为外键。一个表可以有多个外键。
创建学生表：
create table student_info (
id bigint primary key,
student_name varchar(20),
age tinyint,
gender tinyint,
telphone varchar(11),
study_direction varchar(20))
engine=innodb character set = utf8mb4;创建课程表：
create table course_info (
id bigint primary key,
course_name varchar(20))
engine=innodb character set = utf8mb4;指定外键更新或删除的行为：
-
如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作
-
如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT
-
CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持
-
SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持
-
NO ACTION: InnoDB拒绝删除或者更新父表
-
RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的
创建外键的2种方式：
-
直接在建表时使用foreign key来声明
create table student_course_info (id bigint primary key, fk_student_id bigint, fk_course_id bigint, score int, choice_date date, foreign key (fk_student_id) references student_info(id) on delete cascade on update cascade, foreign key (fk_course_id) references course_info(id) on delete cascade on update cascade) engine=innodb character set = utf8mb4; -
通过CONSTRAINT添加外键
create table student_course_info (id bigint primary key, fk_student_id bigint, fk_course_id bigint, score int, choice_date date) engine=innodb character set = utf8mb4; ALTER TABLE student_course_info ADD CONSTRAINT fk_student_id FOREIGN KEY (fk_student_id) REFERENCES student_info (id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE student_course_info ADD CONSTRAINT fk_course_id FOREIGN KEY (fk_course_id) REFERENCES course_info (id) ON DELETE CASCADE ON UPDATE CASCADE;
删除外键的方式：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称小结
-
数据库中约束可分为：列/域完整性，实体完整性，参照完整性，自定义完整性。
-
列完整性，主要控制列的条件。而实例完整性，主要控制行的条件。参照完整性，主要描述表与表之间的相关性
-
主键约束的目的：主要保证行数据的完整性，以及唯一性
-
外键约束的目的：主要保证“子表”能根据外键，找到“主表”的数据
-
当然外键约束，可能存在一定的级联操作，特别是更新或删除数据时。
三、其他约束
一、自增长
自增长，也是一种约束。但是这种约束，只能定义在主键上，且一张表只能有1个自增长！
自增长约束的目的，让主键列可以实现自动变化，程序员减少对主键的生成性的操作。
create table student_info (id bigint primary key auto_increment,
student_name varchar(20),
age tinyint,
gender tinyint,
telphone varchar(11),
study_direction varchar(20)) engine=innodb character set = utf8mb4;但是：auto_increment 只针对数值类型有效，其他类型将无法使用！
二、唯一约束
唯一约束，同样也是保证数据行完整性的一种。
唯一约束的目的：保证某一列数据的唯一性，例如：身份证，电话号码…… ，如果重复添加，将抛出异常！
create table student_info (id bigint primary key auto_increment,
student_name varchar(20),
age tinyint,
gender tinyint,
telphone varchar(11) unique,
study_direction varchar(20)) engine=innodb character set = utf8mb4;三、非空约束
非空约束，主要保证某一列的值，一定要有值，不能为Null。
create table student_info (
id bigint primary key auto_increment,
student_name varchar(20) not null,
age tinyint,
gender tinyint,
telphone varchar(11) unique,
study_direction varchar(20))
engine=innodb character set = utf8mb4;默认情况下，如果列名后未加not null，该列数据是可以添加null数据的
四、检查约束
检查约束：检查某一个列的值，是否是自己规定的值，如果不是将抛出异常！
create table student_info (
id bigint primary key auto_increment,
student_name varchar(20) not null,
age tinyint,
gender set('男','女'),
telphone varchar(11) unique,
study_direction varchar(20))
engine=innodb character set = utf8mb4;
或者
create table student_info (
id bigint primary key auto_increment,
student_name varchar(20) not null,
age tinyint,
gender enum('男','女'),
telphone varchar(11) unique,
study_direction varchar(20))
engine=innodb character set = utf8mb4;五、默认值约束
默认值约束：主要为某一列进行默认值设置，当用户未录入数据时，就填充默认值！
create table student_info (id bigint primary key auto_increment,
student_name varchar(20) not null,
age tinyint,
gender set('男','女'),
telphone varchar(11) unique,
study_direction varchar(20),
password varchar(32) default '123456') engine=innodb character set = utf8mb4;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
