初始MySQL之约束与数据库范式

约束

约束的作用

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

约束种类

约束名	约束关键字
主键	primary key（PK）
唯一	unique（UQ）
非空	not null（NN)
外键	foreign key
检查约束	check

注：mysql不支持检查约束

主键约束

主键的作用

在添加数据时，约束重复数据的行为
需要一个字段保证数据是唯一的，所以这个字段的值是不能为null的

主键的特点

非空并且唯一

主键的分类

自然主键：数据中本身存在一个非空并且唯一的字段 - 不建议使用
代理主键：字段和数据没有直接联系 id
	int(6) - 自动增长(mysql自带特性) max(id)+1
		   - 序列(oracle特性 sequence)
    varchar(32) - UUID()[Java中也有UUID.randomUUID()]

具体操作

select replace(UUID(),'-','');

-- 表存在，添加主键
alter table user1 add primary key(id);

-- 表存在，删除主键
alter table user1 drop primary key;

-- 创建表时添加主键
create table user (
id int(6) primary key auto_increment,
username varchar(20),
password varchar(20)
);

主键自增

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

• AUTO_INCREMENT表示自动增长（字段类型必须是整数类型）

DELETE和TRUNCATE对自增长的影响

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

唯一性约束

 自动在这一列中生成索引 - index

具体操作

-- 在已有的表中添加唯一约束 user2 - username
alter table user2 add unique(username);

-- 在已有的表中删除唯一约束（删除对应的索引）
-- 先查询表中的索引
show index from user2;
-- username 在这里表示索引名
alter table user2 drop index username;

-- 建表时添加唯一索引
create table user3 (
	username varchar(20) unique not null default 'mike'
);

非空约束

某一列不能为null

具体操作

-- 在已有的表中添加非空约束
alter table user2 modify password varchar(20) not null;

-- 删除非空约束
alter table user2 modify password varchar(20);

-- 添加非空约束 同时设置默认值
alter table user2 modify password varchar(20) default '12345';

-- 虽然设置默认值，但是不能主动传null
insert into user2 values (2,'mike',null); -- 错误

-- 忽略掉非空字段
insert into user2 (id,username) values (2,'mike');

外键约束

什么是外键约束

• 什么是外键：在从表中与主表主键对应的那一列
• 主表：一方，用来约束别人的表
• 从表：多方，被别人约束的表

具体操作

-- 创建一张表order
create table order(
id int(6) primary key auto_increment,
name varchar(20),
detail varchar(255),
userId int(6),
foreign key (userId) references user(id)
);

-- 添加外键 constraint（约束）
alter table order
add constraint user_order_fk
foreign key (userId) references user(id);

alter table order
add foreign key (userId) references user(id);

-- 错误，不能在从表存在的情况下直接删除主表
drop table user;

-- 错误，不能引用一个不存在的外键值
insert into order values(null,'手机','手机真好看',2);

-- 正确
insert into order values(null,'手机','手机真好看',1);

检查约束

mysql不支持

约束小结

约束名	约束关键字	说明
主键	primary key（PK）	1.唯一2.非空
唯一	unique（UQ）	这一列不能有重复值
非空	not null（NN)	这一列必须有值
外键	foreign key	主表中主键列，在从表中外键列
默认	default	如果一列没有值，使用默认值

表与表之间的关系

表与表的关系	关系的维护
一对一	1）特殊一对多，从表中的外键设置为唯一 2）从表中的主键又是外键
一对多	主外键的关系
多对多	中间表，两个一对多

数据库设计

数据规范化

什么是范式

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

三大范式

1NF：符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分
2NF：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF的基础上消除非主属性对主属性的部分完全依赖）
3NF：在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

函数依赖与主码

函数依赖：通过一个属性（组）A，能确定唯一的B属性的值，称B函数依赖于
		例如：学号 -> 姓名 学号 + 课程名称 -> 分数
部分函数依赖：通过属性组A中的部分属性，就能确定唯一的B属性的值，称B部分函数依赖于A
		例如：学号 + 课程名称 -> 姓名
完全函数依赖：通过属性组A中的每一个属性一起确定唯一的B属性的值，称B完全函数依赖于A
		例如：学号 + 课程名称 -> 分数
传递依赖：通过属性（组）A确定唯一的B属性的值，再通过B属性，确定唯一C属性的值，称C传递依赖于A
		例如：学号 -> 系名 -> 系主任
主码：也叫主属性，表中其他所有属性的值，都能通过一个属性（组）来确定，这个属性（组）就是主码
		例如：学号 + 课程名称

三大范式小结

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