【8.0】MySQL之约束条件
【一】什么是约束条件
- 约束条件：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！
- 约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件
【二】概览
- 非空约束（not null）
- 唯一性约束（unique）
- 组合使用 not null 和 unique
- 主键约束PK（primary key）
- 外键约束FK（foreign key）
- 级联更新与级联删除
ps:创建表结构时，约束条件直接跟在字段后面的为列级约束，若约束条件是用括号时为表级约束。
使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复。
【三】非空约束（not null）
【1】作用
not null约束的字段不能为 NULL 值，必须赋具体数据；
【2】案例
- 创建t_user 表（id，name,email），name不为空
create table t_user(
id int(10) ,
name varchar(32) not null,
email varchar(128)
);
【四】唯一性约束（unique）
【1】作用
- unique约束的字段具有唯一性，不可重复，但是可以为空（null）。
【2】案例
- 修改之前的t_user表结构，把email设置为唯一性
alter table t_user modify email varchar(128) unique;
【五】组合使用
【1】not null 和 unique 单独使用(表级约束）
-
使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复，但是名字和邮箱字段可以单独重复。
-
可以给表级约束起名字，这样可以便于操作这个约束（如，删除，修改等）
【2】not null 和unique同时使用(列级约束)
- 被 not null 和 unique 约束的字段，该字段即不能为 NULL 也不能重复；
- 创建表
create table t_user(
id int(10) ,
name varchar(32) not null unique
);
- 查看表数据结构
desc t_user;
- 插入数据
insert into t_user(id) values(3);
# 由于name字段不能为空，所以会报错
insert into t_user(id,name) values(1,'dream');
insert into t_user(id,name) values(2,'dream');
# 由于name字段唯一，所以重复数据会报错
【六】如何查看当前表的约束条件
- 给约束条件添加名字
show databases;
use information_schema;
show tables;
desc table_constraints;
- table_constraints 该表专门存储约束信息
- 查看某张表存在哪些约束条件？
select constraint_name from table_constraints where table_name='表名';
【七】主键约束PK（primary key）
【1】主键涉及到的术语
- 主键约束
- 主键字段
- 主键值
【2】主键约束、主键字段、主键值三者之间关系
- 表中某个字段添加主键约束之后，该字段被称为主键字段
- 主键字段中出现的每一个数据都被称为主键值；
ps：主键值必须不能为空值，具有唯一性，会自动添加索引，且具有该行数据的唯一标识。一张表只能有一个主键约束。
【3】主键的作用
- 添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同，但本质是不同的，添加主键约束之后，主键不仅会有“not null unique”作用，而且主键字段还会自动添加“索引 — index”；
- 一张表应该有主键，若没有，表示这张表是无效的。“主键值”是当前行 数据的唯一标识，“主键值”可以是当前行数据的身份证号；（即使表中两行数据完全相同，但是由于主键不同，我们也认为这是两行完全不同的数据）
【4】主键根据个数分类
- 单一主键、复合主键
- 给一个字段添加一个主键约束，被称为单一主键
- 给多个字段联合添加一个主键约束，被称为复合主键
ps:不论时单一主键还是复合主键，一张表只能有一个主键约束。
（1）单一主键：列级约束
drop table if exists t_user;
create table t_user(
id int(10) promary key,
name varchar(32)
);
（2）单一主键：表级约束
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32),
primary key(id)
);
- 给主键重命名
# 给主键重命名
create table t_user(
id int(10),
name varchar(32),
constraint t_user_id_pk primary key(id)
);
（3）复合主键：只能为表级约束
drop table if exists t_user;
# 复合主键，表级约束，并且给其重命名
create table t_user(
id int(10);
name varchar(32);
email varcahr(32);
constraint t_user_id_name_pk primary key(id,name)
);
【5】主键根据性质分类
- 自然主键：主键值若是一个自然数，并且这个自然数与业务没有任何关系，这种主键称为自然主键；
- 业务主键：主键值若和当前表中的业务紧密相关，那么这种主键值被业务主键；如果业务发生改变时，业务主键往往会受到影响，所以业务主键使用较少，大多情况使用自然主键。
【6】自动生成主键
- MySQL中自动生成主键值(使用自增字段auto_increment)
- MySQL数据库管理系统中提供了一个自增数字，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。
- 自增数字默认从1开始，以1递增：1、2、3、
drop table if exists t_user;
create table t_user(
id int(10) primary key auto_increment,
name varchar(32)
);
insert into t_user(name) values('dream'),('hope'),('opp');
select * from t_user;
Ps:自动生成后，一个主键自然数只能出现一次，若删除该行纪录，重新递增纪录时，主键自然数会跳过直接+1
【7】重置主键起始位置
（1）方式一：清空表数据
- 将原本的表数据全部清空
- 该操作会将表重置为刚创建时的状态，不建议轻易使用
truncate 表名;
（2）方式二：指定主键起始位置
- 使用表修改语句将带有
AUTO_INCREMENT的字段数字指定为自定义位置
alter table 表名 AUTO_INCREMENT = 指定主键起始位置;
【八】外键约束FK（foreign key）
【0】引入
- 假设我们现在有一张员工表，员工表内有下述字段
id name age dep_name dep_desc
-
但是从上面看有很大的缺陷
- 表的重点不清晰(可以忽略)
- 既可以说是员工表也可以说是部门表
- 表中某些字段对应的数据一直在重复(可以忽略)
- 浪费存储空间
- 表的扩展性极差 牵一发而动全身(不能忽略)
- 耦合度太高 不利于维护
- 表的重点不清晰(可以忽略)
-
解决办法
-
将一张表一分为二
-
员工表
id name age -
部门表
id dep_name dep_desc
-
-
-
拆表后的问题
- 员工与部门之间没有了绑定关系
-
解决办法
- 在员工表中添加一个部门编号字段与部门表中的主键字段对应
一张表中的字段和另一张表中的字段建立联系后，这个字段就是外键字段
外键字段就是用来记录表与表之间数据的关系
【1】外键涉及到的术语
- 外键约束
- 外键字段：某个字段添加外键约束之后，该字段为外键字段
- 外键值：外键字段中的每一个数据都是外键值
【2】外键关系介绍
-
一对一
-
一对多
-
多对多
-
没有关系
【3】外键语法
- 先创建表1
- 先有了需要被建立关系的表才能让建立关系的表去和他建立关系
create table 表名1(
字段名1 字段类型1 约束条件1 comment 注释1,
字段名2 字段类型2 约束条件2 comment 注释2,
);
create table 表名2(
字段名1 字段类型1 约束条件1 comment 注释1,
字段名2 字段类型2 约束条件2 comment 注释2,
foreign key(在表名2中显示的字段名) references 表名1(表明1中需要建立外键关系的字段名)
);
【4】一对多关系
（1）理论分析
- 以员工表与部门表为例
- 先站在员工表的角度
- 问:一个员工能否对应多个部门
- 答:不可以
- 再站在部门表的角度
- 问:一个部门能否对应多个员工
- 答:可以
- 先站在员工表的角度
- 结论:换位思考之后得出的答案是一个可以一个不可以
- 所以关系是"一对多"
- 部门是'一'
- 员工是'多'
- 所以关系是"一对多"
- '''关系表达只能用一对多 不能用多对一'''
- 一对多关系 外键字段建在"多"的一方(员工表)
（2）建表思路
- 如果表中有外键字段 那么建议你先编写普通字段 最后再考虑外键字段
- 创建部门表
create table dep(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
- 创建员工表
-- 在创建表字段的时候也可以给每个字段添加中文注释
create table emp(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep(id)
);
- 插入数据
-- 向部门表中插入数据
insert into dep(dep_name,dep_desc) values("cook","后厨部门");
insert into dep(dep_name,dep_desc) values("hospital","医院部门");
-- 向员工表中插入数据,不带外键字段
insert into emp(name,age) values("dream",18);
-- 向员工表中插入数据，携带部门表中存在的部门ID
insert into emp(name,age,dep_id) values("hope",28,1);
-- 向员工表中插入数据，，携带部门表中不存在的部门ID
insert into emp(name,age,dep_id) values("opp",38,3);
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`day01_test`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
（3）级联更新和级联删除
create table emp1(
id int primary key auto_increment comment '编号',
name varchar(32) comment '姓名',
age int comment '年龄',
dep_id int comment '部门编号',
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep1(
id int primary key auto_increment comment '编号',
dep_name varchar(32) comment '部门名称',
dep_desc varchar(32) comment '部门描述'
);
- 在实际工作中外键也可能不会使用 因为外键会消耗额外的资源并且会增加表的复杂度
- 表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
【4】外键字段的约束效果
- 创建表的时候 应该先创建被关联表(没有外键字段的表)
- 插入数据的时候 应该先插入被关联表(没有外键字段的表)
- 外键字段填入的值只能是被关联表中已经存在的值
- 修改、删除被关联表数据都会出现障碍
update dep set id=200 where id=2;
delete from dep where id=2;
【5】多对多关系
（1）理论分析
- 以图书与作者表为例
- 先站在图书表的角度
- 问:一本书籍能否对应多名作者
- 答:可以
- 再站在作者表的角度
- 问:一名作者能否对应多本书籍
- 答:可以
- 先站在图书表的角度
- 结论:换位思考之后两边都可以 那么就是"多对多"关系
（2）建表思路
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2),
author_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
- 针对多对多关系 需要单独开设第三张表专门存储关系
create table book(
id int primary key auto_increment,
title varchar(32),
price float(10,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others')
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
（3）小结
-
针对多对多表关系
-
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除
【6】一对一关系
- 针对qq用户表 其实里面的数据可以分成两类
- 热数据:经常需要使用的数据
- eg:qq号码 座右铭 个人简介 爱好
- 冷数据:不怎么经常需要使用的数据
- eg:邮箱 电话 学校 ...
- 热数据:经常需要使用的数据
- 为了节省资源并降低数据库压力 会将表一分为二
- 用户表
- 存使用频率较高的数据字段
- 用户详情表
- 存使用频率较低的数据字段
（1）理论分析
- 先站在用户表的角度
- 问:一个用户数据能否对应多个用户详情数据
- 答:不可以
- 再站在用户详情表的角度
- 问:一个用户详情数据能否对应多个用户数据
- 答:不可以
- 结论:换位思考之后两边都不可以 那么关系可能有两种
'没有关系'：用膝盖都能判断出来
'一对一关系'：针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方
（2）建表语句
- 创建用户表
create table User(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others'),
user_detail_id int unique, # 好好体会为什么加unique
foreign key(user_detail_id) references UserDetail(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
);
- 创建用户详细信息表
create table UserDetail(
id int primary key auto_increment,
phone bigint,
age int
);
【补充】
（1）外键根据个数分类
- 单一外键【给一个字段添加外键约束】
- 复合外键【给多个字段联合添加成一个外键约束】
（2）补充
- 注：一张表中可以有多个外键字段。
- 注：外键值可以为空值NULL。
- 注：外键字段引用一张表的某个字段时，被引用的字段必须具有unique约束。
- 注：有了外键引用之后，表分为父表和子表。一对多关系中，一为父表，多为子表。创建表时，先创建父表，再创建子表；插入数据时先插入父表中的数据，再插入子表中的数据；删除数据时，需先删除子表的数据，再删除父表的数据。
- 注：外键约束没有列级约束，只有表级约束。
【案例】设计数据库表用来存储学生和班级信息
（1）需求说明
- 设计数据库表用来存储学生和班级信息
（2）需求分析
- 父表：班级表 t_class；
- 子表：学生表 t_student；
（3）需求实现
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
cno int(3) primary key,
cname varchar(255) not null unique
);
create table t_student(
sno int(3) primary key,
sname varchar(32) not null,
classno int(3),
constraint t_student_class_no_fk foreign key(classno) references t_class(cno)
);
insert into t_class(cno,cname) values(100,'高三1班');
insert into t_class(cno,cname) values(200,'高三2班');
insert into t_class(cno,cname) values(300,'高三3班');
insert into t_student(sno,sname,classno) values(1,"dream",100),
(2,'luck',100),
(3,'king',100),
(4,'lily',200),
(5,'ford',200),
(6,'allen',300),
(7,'teddy',300);
【案例】找出每个学生的班级名称
select s.*,c.* from t_student s join t_class c on s.classno=c.cno;
select sname,cname from t_class join t_student on cno=classno;
【九】级联更新和级联删除
【1】介绍
- 添加级联更新和级联删除时需要在外键约束后面添加
- 在删除父表中的数据时，级联删除子表中的数据 on delete cascade
- 在更新父表中的数据时，级联更新子表中的数据 on update cascade
- 以上的级联更新和级联删除谨慎使用，因为级联操作会将数据改变或删除【数据无价】
- 在修改约束条件时，建议可以将原先的约束删除再重新添加约束条件
【2】使用
- 删除外键约束
alter table t_student drop foreign key t_student_classno_fk;
- 添加级联删除的外键约束
alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on delete cascade;
- 添加级联更新的外键约束
alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on update cascade;
本文来自博客园，作者：Chimengmeng，转载请注明原文链接：https://www.cnblogs.com/dream-ze/p/17995408
