编程之路之数据库mysql（五）- 数据库设计范式与外键详解
一、设计范式，Normal Format
设计范式就是设计数据表的规范，如果遵循这个设计规范，则设计出来的数据表是合理而且不会有数据冗余。设计范式有3个，分别是NF1，NF2，NF3。3个范式依次严格。
1、第一范式，NF1，字段的原子性
字段是不能拆分的。
|
老师姓名 |
老师性别 |
班级名称 |
上课教室 |
课程周期 |
|
|
赵老师 |
男 |
计算机1 |
100 |
2020-09-1 |
2024-06-15 |
|
钱老师 |
男 |
计算机2 |
101 |
2020-09-1 |
2024-06-15 |
|
孙老师 |
男 |
计算机3 |
102 |
2020-09-1 |
2024-06-15 |
因为关系型数据库就是由二维表及其之间的联系组成。二维表不能实现这种结构，课程周期字段它是可以再被拆分的，不是原子的。
需要对课程周期字段进行拆分，满足字段的原子性要求，从而达到满足NF1
|
老师姓名 |
老师性别 |
班级名称 |
上课教室 |
开课日期 |
结课日期 |
|
赵老师 |
男 |
计算机1 |
100 |
2020-09-1 |
2024-06-15 |
|
钱老师 |
男 |
计算机2 |
101 |
2020-09-1 |
2024-06-15 |
|
孙老师 |
男 |
计算机3 |
102 |
2020-09-1 |
2024-06-15 |
满足第一范式是很容易做到的。
2、第二范式，NF2，消除部分依赖
|
老师姓名 |
老师性别 |
班级名称 |
上课教室 |
开课日期 |
结课日期 |
|
赵老师 |
男 |
计算机1 |
100 |
2020-09-1 |
2024-06-15 |
|
钱老师 |
男 |
计算机2 |
101 |
2020-09-1 |
2024-06-15 |
|
孙老师 |
男 |
计算机3 |
102 |
2020-09-1 |
2024-06-15 |
老师性别字段是依赖于老师姓名字段
上课教室、开课日期、结课日期3个字段是依赖于班级名称字段的
在这种情况下，请问哪个字段可以代表整条记录？
解决访求：为数据表添加一个ID字段（主键），此ID字段就能够代表整条记录，所有的字段现在可以依赖于ID字段了。就消除了部分依赖。
|
ID |
老师姓名 |
老师性别 |
班级名称 |
上课教室 |
开课日期 |
结课日期 |
|
1 |
赵老师 |
男 |
计算机1 |
100 |
2020-09-1 |
2024-06-15 |
|
2 |
钱老师 |
男 |
计算机2 |
101 |
2020-09-1 |
2024-06-15 |
|
3 |
孙老师 |
男 |
计算机3 |
102 |
2020-09-1 |
2024-06-15 |
此时，数据表就是满足第二范式的要求了。
3、第三范式，NF3，消除传递依赖
消除传递依赖的办法就是对数据表进行拆分
老师实体信息表
|
老师ID |
老师姓名 |
老师性别 |
|
1 |
赵老师 |
男 |
|
2 |
钱老师 |
男 |
班级实体信息表
|
班级ID |
班级名称 |
上课教室 |
开课日期 |
结课日期 |
|
12 |
计算机1 |
100 |
2020-09-1 |
2024-06-15 |
|
21 |
计算机2 |
101 |
2020-09-1 |
2024-06-15 |
|
18 |
计算机3 |
102 |
2020-09-1 |
2024-06-15 |
上课实体信息表
|
ID |
老师ID |
班级ID |
|
1 |
1 |
12 |
|
2 |
2 |
21 |
|
3 |
3 |
18 |
不同的依赖部分拆分成不同的表，从而消除了传递依赖现象，满足了第三范式。
不满足第三范式的一个重要特征就是有数据的冗余现象
4、举个例子
一、把下面的数据表修改成满足关系数据库设计范式的结构
|
会员姓名 |
性别 |
商品名称 |
商品单价 |
购买数量 |
总费用 |
|
|
商品费用 |
送货费用 |
|||||
|
张飞 |
男 |
手机贴膜 |
15.00 |
1 |
15.00 |
8.00 |
- 将数据表修改成满足1NF
|
会员姓名 |
性别 |
商品名称 |
商品单价 |
购买数量 |
商品费用 |
送货费用 |
|
张飞 |
男 |
手机贴膜 |
15.00 |
1 |
15.00 |
8.00 |
对不满足字段的原子性的字段进行拆分
- 将数据表修改成满足2NF
要求：消除部分依赖
通过增加主键的方法以满足第二范式
|
ID |
会员姓名 |
性别 |
商品名称 |
商品单价 |
购买数量 |
商品费用 |
送货费用 |
|
5 |
张飞 |
男 |
手机贴膜 |
15.00 |
1 |
15.00 |
8.00 |
- 将数据表修改成满足3NF
要求：消除传递依赖。部分字段通过依赖的字段而依赖于主键
对数据表进行拆分
不满足第三范式的一个特征就是有数据冗余。
会员实体信息表
|
会员ID |
会员姓名 |
性别 |
|
26 |
张飞 |
男 |
商品实体信息表
|
商品ID |
商品名称 |
商品单价 |
|
17 |
手机贴膜 |
15.00 |
订单实体信息表
|
订单ID |
会员ID |
商品ID |
购买数量 |
商品费用 |
送货费用 |
|
5 |
26 |
17 |
1 |
15.00 |
8.00 |
- 将修改好的数据表在数据库中进行创建，并填充数据
第一个表会员表
注意主键自动增量字段要给出unsigned属性，如果没有，可以插入id为负的记录！
枚举类型字段定义时，不要使用下面的方式
原因：枚举选项有索引，第一个选项的索引值是1，第二个是2，现在的定义容易造成混乱。
建议改成 gender enum(‘1’,’2’) 或直接使用男女，male，female更好。
价格字段一定要用decimal
二、在原来数据库管理程序基础上，增加建库，删除库，
建库需要再获取字符集信息
在创建数据库时，不需要指定存储引擎的！应该是在建表时指定。
删除数据库要进行确认
二、实体间的联系
实体：就是指具体的数据表。
关系型 数据库是由二维表及其之间的联系构成的数据组织。
实体间的联系分为：一对多，多对多，一对一
1、一对多，多对一
这是实体的一种联系方式
2、多对多
我们要想表达出多对多的情况，必须建立一个中间表
实体的联系就通过中间表变成了：一对多加上多对一
3、一对一
比如以内容为主的网站经常有文章上传，内容字段通常会用text的类型，网站设计数据表时经常会将这个字段拆分出去，以扩展表的方式和文章关联
另一个例子，会员表在网站上经常会进行拆分
三、外键约束，foreign key
外键：当B表中的字段是A表中的主键时，B表中的这个字段就是一个外键。
A表可以叫主表。B表可以叫从表。
外键功能只有innodb存储引擎支持。建议在建数据表明确指定存储引擎类型。
约束：
当删除A表中的数据时，如果它是在B表中存在的，则约束不能删除。
当在B表中插入数据时，如果外键字段的值不是A表中存在的值，则约束不允许插入。
1、创建外键
先创建主表
再创建从表，同时创建外键
格式：foreign key (从表中的外键字段的字体名) references 主表名 (主表的主键字段名)
提示，有两个情况要关注
- 创建外键时，如果不指定约束名称，系统会自动分配一个
- 外键字段会自动添加索引
2、查看外键的约束
- 不能够删除或修改主表中被从表正在使用的记录的主键值
- 不能够在从表中添加数据，外键字段的值是主表中不存在的
总结：增加外键可以有效保护数据的合法性。
3、删除外键
格式：alter table 表名 drop foreign key 约束名;
总结：
- 删除外键要使用约束名
- 外键删除后，索引仍然会保留
4、手工添加外键
格式：alter table 从表名 add foreign key (从表中外键字段名) references 主表名 (主表的主键字段名);
如果从表中有非法的数据，则外键无法创建成功。
5、外键的联动方式
外键下，有3 种联动方式
Strict，严格方式，默认的方式。不允许删除及修改
Cascade，级联方式。删除或修改时，从表跟随主表一起变化
Set null，设置为NULL方式，修改删除时从表的外键字段数据设置为null
可以单独为修改或删除指定联动方式
下面的设置也是比较合理一种设置
On update cascade
On delete set null
提示：要注意从表的外键字段，是否有not null的约束，如果有在严格方式没影响，但是增加set null则会失败！
测试修改
测试删除
我是小咖
发个邀请：
如果你正好想学习php，可以与我一起交流，我的VX：feilueze333。下面资料免费赠送。