MySQL约束与设计（0323）

1 数据库约束的概述

1.1 约束的作用

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

1.2 约束种类

2 主键约束

2.1 主键的作用

用来唯一标识数据库中的每一条记录

2.2 哪个字段应该作为表的主键

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

2.3 创建主键

主键关键字： primary key
主键的特点：
1)非空 not null
2)唯一

创建主键方式：

1. 在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

2. 在已有表中添加主键

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

-- 创建表学生表st5, 包含字段(id, name, age)将id做为主键
create table st5(
	id int primary key,
	name varchar(20),
	age int
)
desc st5;

将表st5的id作为主键

将表st6的name作为主键

2.4 删除主键

-- 删除st6表的主键
ALTER TABLE st6 DROP PRIMARY KEY;
desc st6;

2.5 主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

2.6 创建自增长的默认值起始值

CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;

-- 指定起始值为1000
create table st4 (
id int primary key auto_increment,
name varchar(20)
) auto_increment = 1000;
insert into st4 values (null, '孔明');
select * from st4;

2.7 DELETE和TRUNCATE对自增长的影响

• DELETE：删除所有的记录之后，自增长没有影响。
• TRUNCATE：删除以后，自增长又重新开始。

3 唯一约束

什么是唯一约束： 表中某一列不能出现重复的值

3.1 唯一约束的基本格式

字段名 字段类型 UNIQUE
如：
name varchar(20) unique

3.2 实现唯一约束

-- 实现唯一约束	unique
-- 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
create table st7 (
	id int,
	name varchar(20) unique
)
-- 添加一个同名的学生
insert into st7 values (1, '张三');
select * from st7;
-- Duplicate entry '张三' for key 'name'
insert into st7 values (2, '张三');
-- 重复插入多个null会怎样？
insert into st7 values (2, null);
insert into st7 values (3, null);

4 非空约束

4.1 非空约束的基本语法格式

字段名 字段类型 NOT NULL
如：
id int NOT NULL,

4.2 默认值

字段名 字段类型 DEFAULT 默认值
如：
address varchar(20) default '广州’

-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
	id int NOT NULL,
	name varchar(20),
	address varchar(20) default '广州'
)
-- 添加一条记录,使用默认地址
insert into st9 values (1, '李四', default);
select * from st9;
insert into st9 (id,name) values (2, '李白');
-- 添加一条记录,不使用默认地址
insert into st9 values (3, '李四光', '深圳')

INSERT INTO st9 VALUES(NULL,'李世民','长安');
INSERT INTO st9 VALUES(4,NULL,'长安');
INSERT INTO st9 VALUES(5,'李世民',NULL);

疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?
1)主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
2)自增长只能用在主键上

5 外键约束

5.1 单表的缺点

创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据：

以上数据表的缺点：
	数据冗余
	后期还会出现增删改的问题

5.2 解决方案

问题：当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，实际应用中不能出现这种情况。employee的dep_id中的数据只能是department表中存在的id

目标: 需要约束dep_id只能是department表中已经存在的id
解决方式: 使用外键约束

5.3 什么是外键约束

5.4 创建约束的语法

新建表时增加外键：

[constraint] [外键约束名称] foreign key(外键字段名) references 主表名(主键字段名)

已有表增加外键：

ALTER TABLE 从表 ADD [constraint] [外键约束名称] foreign key (外键字段名) reference 主表(主键字段名);

-- 外键约束


-- 1) 删除副表/从表 employee
drop table employee;
-- 2) 创建从表 employee 并添加外键约束emp_depid_fk
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 3) 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;
-- 4) 部门错误的数据添加失败
-- 插入不存在的部门
-- Cannot add or update a child row: a foreign key constraint fails
INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);

5.5 删除外键

总结;

外键公司基本不用
但是得会

思考：外键这个东西好不好呢？

好
不好

6 数据约束小结

7 表与表之间的关系

7.1 一对一

7.2 一对多

7.3 多对多

8 数据库设计

8.1 数据库规范化

8.1.1 什么是范式

8.1.2 三大范式：

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

8.2 1NF（原子性：每一列都应该是不可拆分的原子）

8.2 2NF（记录的唯一性：通过主键来体现）

在我们存储数据的时候，每一行都应该是唯一的，在同一个表里面，不能存储重复的数据

8.2 3NF（字段不要冗余）