荐 MySql之约束与设计总结

1 数据库表的约束

约束的作用：对表中的数据进行限制，保证数据的争取性、有效性和完整性。一个表如果添加 了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束种类：

约束名	关键字	说明
主键	primary key	非空且唯一
唯一	unique	这一列不能有重复值
非空	not null	这一列必须有值
外键	foreign key	主表中主键列，在从表中外键列
默认	default	如果一列没有值，使用默认值
检查约束	check	mysql不支持

1.1 主键约束

作用：用为唯一标识数据库中的每一条记录

通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据和程序使用的，不是给最终的客户使用的，所以主键没有含义没有关系，只要不重复非空就行。

（1）在创建表时，添加主键约束：

create table 表名（
	id int primary key, -- 添加主键约束
	字段名 varchar(20)
）;

（2）创建表之后，添加主键约束

ALTER TABLE 表名 MODIFY 字段 INT PRIMARY;
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

（3） 删除主键：

-- 错误写法：alter table 表名 modify 字段 int;
ALTER TABLE 表名 DROP PRIMARY KEY；

（4） 自动增长：如果主键是数值类型的，使用auto_increment 可以来完成值的自动增长，默认值为1。注意：自增长只能用在主键上。

-- 在创建表时，添加主键约束，并且添加自增长约束
create table 表名（
	id int primary key auto_increment, -- 给id添加主键自增长约束
	字段名 varchar(20)
）;

-- 在创建表之后，添加自增长约束
ALTER TABLE 表名 MODIFY 字段 INT AUTO_INCREMENT;

-- 删除自动增长
ALTER TABLE 表名 MODIFY 字段 INT;

DELETE:删除所有记录之后，自增长没有影响。

TRUNCATE:删除以后，自增长又重新开始。

1.2 唯一约束

作用：标识某一列中的值不能重复

注意：在mysql中，唯一约束限定的值可以存在null,即多个null值不受唯一约束影响。

（1）在创建表时，添加唯一约束：

create table 表名（
	id int unique;  -- 添加唯一约束
	字段名 varchar(20)
）;

（2） 在创建表之后，添加唯一约束：

ALTER TABLE 表名 MODIFY 字段 类型 UNIQUE;

（3） 删除唯一约束：

ALTER TABLE 表名 DROP INDEX 字段;

1.3 非空约束

作用：标识某一列中的值不能为null。

（1）在创建表时，添加非空约束：

create table 表名(
	id int,
	name varchar(20) NOT NULL -- 添加非空约束 
);

（2） 在创建表之后，添加非空约束：

ALTER TABLE 表名 MODIFY 字段 类型 NOT NULL;

(3) 删除非空约束

ALTER TABLE 表名 MODIFY 字段 类型;

1.4 外键约束

作用：让表与表之间产生关系，从而保证数据的正确性。

外键：在从表中与主表主键对应的那一列。

主表：一方，用来约束别人的表。

从表：多方，被别人约束的表 。

（1） 在创建表时，添加外键约束：

create table 表名（
	-- 省略其他字段
	外键字段 int,
	constraint  外键名称 foreign key (外键列名称) references 主表名称(主表列名称) 
	-- 外键名称自定义，不要重复即可。后面删除外键约束需要用到此外键名称
）;

（2） 在创建表之后，添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

（3） 删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

（4） 级联操作：在修改和删除主表的主键时，同时更新或删除副表的外键值。

-- 添加级联操作
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;

-- 添加级联删除，谨慎使用！
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称) ON DELETE CASCADE;

-- 添加级联更新和删除，谨慎使用！
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称)  ON UPDATE CASCADE ON DELETE CASCADE;

1.5 默认约束

作用：指定某列的默认值。

字段名 字段类型 DEFAULT 默认值
create table stu(
	id int ,
	gender varchar(20) default '男'
);
-- 添加一条记录，使用默认地址
insert into stu values(1, default);

2 数据库的设计

2.1 表之间的关系

关系	说明	关系的维护
一对多	最常用的关系，例如部门和员工，一个部门有多个员工，但一个员工只有一个部门	主外键的关系
多对多	例如选课和学生，一门课程可以有多个学生选择，一个学生也可以选择多门课程	多个中间表，两个一对多
一对一	相对使用较少，例如一个公民只有一个身份证号码	-

2.1.1 一对多

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

2.1.2 多对多

多对多建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

2.1.3 一对一

一对一建表原则：在实际开发中应用不多，因为一对一可以创建成一张表。

2.2 数据规范化

好的数据库设计对数据的存储性能和后期的程序开发都会产生重要的影响。建立科学的、规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

其实目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF） 、第四范式(4NF）和第五范式（5NF，又称完美范式）。
  满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ，其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

范式	特点
1NF	原子性：表中每列不可再拆分
1NF	不产生局部依赖，一张表只描述一件事情
1NF	不产生传递依赖，表中每一列都直接依赖于主键，而不是通过其他列间接依赖于主键

2.2.1 1NF

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。如图：

2.2.2 2NF

   在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
   第二范式的特点：一张表中只描述一件事情，表中的每一列都完全依赖于主键。

所谓完全依赖是指不能存在仅依赖主键一部分的列。第二范式是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况，比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就不符合第二范式。
示例：
（1）借书证表：
（2）分成两张表：

2.2.3 3NF

  在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其他的列来间接依赖于主键。

第三范式就是所有列不依赖于其他非主键列，就是在满足2NF的基础上，任何非主列不得依赖于主键。所谓传递依赖，指的是如果存在“A -> B -> C”的决定关系，则C传递依赖于A。因此，满足第三范式的数据库的数据表应该不存在如下依赖关系：主键列—> 非主键列x —>非主键列y

示例：
（1）学生信息表：
（2）存在传递的决定关系：即学号—>所在学院—>学院地点，拆分成两张表：

本文地址：https://blog.csdn.net/xiaoyong5854/article/details/107285935