表完整性约束(表相关操作)
一. 介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性 主要分为：
null # 标识该字段可以为空
not null # 标识该字段不能为空
default # 为该字段设置默认值
primary key # 标识该字段为该表的主键，可以唯一的标识记录
foreign key # 标识该字段为该表的外键
auto_increment # 标识该字段的值自动增长(只能用于主键)
unsigned # 无符号
zerofill # 使用0填充
二. null & not null & default
1. null
null: 被指定的字段的可空. 没有指定默认约束条件就是null.
验证:
drop table t1;
create table t1(name char(13));
insert into t1 values();
select * from t1;
insert into t1 values(null);
select * from t1;
insert into t1 values(not null);
select * from t1;
2. not null
not null: 被指定的字段的值不可空.
验证:
drop table t1;
create table t1(name char(15) not null);
insert into t1 values();
select * from t1;
insert into t1 values(null);
select * from t1;
insert into t1 values('yangyang');
select * from t1;
3. default
default: 被指定的字段在插入的值时无论null还是not null，都可以插入空，插入空默认填入default指定的默认值. 注意: 当已经制定了约束条件not null时default后面就不能指定null作为默认值.
验证:
drop table t1;
create table t1(name char(15) not null default 'yangyang');
insert into t1 values();
select * from t1;
insert into t2 values(null);
select * from t1;
create table t2(name char(15) not null default null);
insert into t2 values();
4. 小结
# null: 可以为空. values(), values(null), values('值')
# not null: 不能为空. values('值'), values('not null')
# default: 当与not null连用. values不指定值会采用default后面的默认值. 但是需要注意的是default后面这个时候不能指定null.
三. unique
# unique /juˈniːk/ 独特 唯一 独一无二的
# 两种唯一的情况
"""
单列唯一: 一个字段唯一
联合唯一: 多个字段联合以后唯一
"""
# ==================== 一个字段唯一: id ====================
drop table t1;
create table t1(id int unique, name varchar(16));
desc t1;
insert into t1 values(1 ,'jsaon'),(1,'egon'); # 报错: ERROR 1062 (23000): Duplicate entry '1' for key 'id'(密钥“id”的重复条目“1”)
insert into t1 values(1, 'jsaaon'),(2, 'egon');
select * from t1;
# ==================== 多个字段联合以后唯一: host + port ====================
# 示例:
'''
ip和port
单个都可以重复 但是加载一起必须是唯一的
id host port
1 127.0.0.1 8080
2 127.0.0.1 8081
'''
drop table t1;
create table t1(
id int,
host varchar(16),
port int,
unique(host, port),
username varchar(16)
);
desc t1;
insert into t1 values(1, '127.0.0.1', 8080, 'root');
insert into t1 values(1, '127.0.0.1', 8081, 'yangyang');
insert into t1 values(1, '127.0.0.1', 8080, 'root'); # 报错: ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'host'(复制条目'127.0.0.1-8080'用于关键'主机')
select * from t1;
四. primary key
1. 主键
# primary key称之为主键. 那么什么是键?
"""
索引是mysql提供的专门的数据结构, 这种数据结构在mysql中被称之为键, 索引其实就是键.
"""
2. primary key介绍
"""
一. 单单从约束效果上来看primary key等价于not null + unique. 表示非空且唯一
"""
# ==================== 验证 ====================
drop table t1;
create table t1(id int primary key);
insert into t1 values(null); # 报错: Column 'id' cannot be null
insert into t1 values(1),(1); # 报错: Duplicate entry '1' for key 'PRIMARY'
insert into t1 values(1),(2); # 正常
"""
二. 它除了有约束效果之外, 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key.
因为它类似于书的目录, 能够提升查询效率并且它也是建表的依据.
"""
# <1> 一张表中有且只有一个主键如果你没有设置主键那么会从上往下搜索直到遇到一个非空且唯一的字段, 该字段就会被自动升级为主键
# ==================== 验证 ====================
drop table t1;
create table t1(
id int,
name varchar(16),
age int null unique,
addr varchar(32) not null unique
);
desc t1;
# <2> 如果表中没有主键也没有其他任何的非空且唯一字段那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它就无法提升查询速度
# <3> 一张表中通常都应该有一个主键字段并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t1(
id int primary key,
name varchar(16)
);
# 联合主键(多个字段联合起来作为表的主键本质还是一个主键) -> 了解
create table t1(
host varchar(16),
port int,
primary key(host, port)
);
# 总结
"""
<1> 以后但凡建表必须指定只能有一个主键存在, 通常是id字段被设置为主键
<2> 为字段指定了唯一的主键后, 因为InnoDB存储引擎查找机制就是不为空且唯一的字段, 所以指定了主键就会默认相当于默认执行你当前的字段不为空且唯一
"""
五. auto_increment
1. 自增介绍
increment /ˈɪŋkrəmənt 增量 定期增加 增值
"""1. 当编号特别多的时候 人为的去维护太麻烦"""
# ==================== 演示 ====================
drop table t1;
create table t1(
id int primary key auto_increment,
name varchar(16)
);
desc t1;
insert into t1(name) values('jason'),('egon'),('kevin');
select * from t1;
"""注意: auto_increment通常 都是加在主键上的不能给普通字段加"""
# ==================== 验证auto_increment加在主键上 ====================
drop table t1;
create table t1(
id int primary key auto_increment,
cid int auto_increment # 报错: ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key(不正确的表定义;只能有一个自动列，它必须被定义为键)
);
2. 小结
"""
<1> 以后在创建表的id(数据的唯一标识id、 uid、sid)字段的时候就使用id int primary key auto_ _increment
<2> 注意: auto_increment通常 都是加在主键上的不能给普通字段加
"""
3. 补充: 删除的记录的2种方式介绍
# 于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
delete from 表名;
# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它(提示: 它的本质就是清空表, 所以没有where等条件)
truncate 表名;
4. 了解知识: auto_increment修改及创建表时可以指定
# 在创建完表后，修改自增字段的起始值
alter table student auto_increment=3;
# 创建表时指定auto_increment的初始值. (注意: 初始值的设置为表选项，应该放到括号外)
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
) auto_increment=3;
六. foreign key
强调: 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
1. 定义一张员工表的三个缺点
员工表: emp
employee /ɪmˈplɔɪiː/ 雇员 员工 雇员
| id | emp_name | emp_gender | dep_name | dep_desc |
|---|---|---|---|---|
| 1 | jason | male | 外交部 | 漂泊游荡 |
| 2 | egon | female | 教学部 | 教书育人 |
| 3 | tank | male | 教学部 | 教书育人 |
| 4 | kevin | male | 教学部 | 教书育人 |
| 5 | oscar | female | 技术部 | 技术能力有限部门 |
# 定义一张员工表 表中有很多字段. 我们从中可以看出以上表的三个缺点
"""
<1> 该表的组织结构不是很清晰(可忽视)
<2> 浪费硬盘空间(可忽视)
<3> 数据的扩展性极差(无法忽视的)
"""
2. 优化: 将员工表拆分. 拆分成员工表和部门表
员工表: emp
employee /ɪmˈplɔɪiː/ 雇员 员工 雇员
| id | emp_name | emp_gender |
|---|---|---|
| 1 | jason | male |
| 2 | egon | female |
| 3 | tank | male |
| 4 | kevin | male |
| 5 | oscar | female |
部门表: dep
department /dɪˈpɑːtmənt/ 部门
| id | dep_name | dep_desc |
|---|---|---|
| 1 | 外交部 | 漂泊游荡 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限部门 |
3. 外键foreign key介绍
"""
外键就是用 来帮助我们建立表与表之间关系的
"""
4. 四种表关系介绍
# 表与表之间最多只有四种关系:
"""
# 注意!!: 得出表之间的关系结论一定要进行双向的对比.
<1> 一对多关系
<2> 多对多关系
<3> 一对一关系
<4> 没有关系(了解)
"""
5. 使用foreign key建立一对多关系
员工表: emp
employee /ɪmˈplɔɪiː/ 雇员 员工 雇员
| id | emp_name | emp_gender | dep_id |
|---|---|---|---|
| 1 | jason | male | 1 |
| 2 | egon | female | 2 |
| 3 | tank | male | 2 |
| 4 | kevin | male | 2 |
| 5 | oscar | female | 3 |
部门表: dep
department /dɪˈpɑːtmənt/ 部门
| id | dep_name | dep_desc |
|---|---|---|
| 1 | 外交部 | 漂泊游荡 |
| 2 | 教学部 | 教书育人 |
| 3 | 技术部 | 技术能力有限部门 |
# 先确定表与表之间的关系
"""
先站在员工表考虑: 员工表里面的一个员工能否对应部门表里面的多个部门. --> 不能!!
再站在部门表考虑: 部门表里面的一个部门能否对应员工表里面的多个员工. --> 可以!!
结论: 员工表与部门表只是单向的一对多，那么员工表和部门表就是"一对多"的表关系.
"""
"""
注意1: 一对多表关系 外键字段建在多的一方. --> emp
注意2: 在创建表的时候 一定要先建立被关联表. --> dep
注意3: 在插入数据的时候 必须先往被关联表插值. --> dep
# foreign key使用完整语法
foreign key(关联表字段) references 表名(被关联表字段) on update cascade on delete cascade
"""
# =================== 使用foreign key建立关联 ===================
create table dep(
id int primary key auto_increment,
dep_name varchar(16),
dep_desc varchar(32)
);
insert into dep(dep_name, dep_desc) values('外交部', '漂泊游荡'), ('教学部', '教书育人'), ('技术部', '技术能力有限部门');
create table emp(
id int primary key auto_increment,
emp_name varchar(16),
emp_gender enum('male', 'female'),
dep_id int,
foreign key(dep_id) references dep(id)
);
insert into emp(emp_name, emp_gender, dep_id) values('jason', 'male', 1), ('egon', 'female', 2), ('tank', 'male', 2), ('kevin', 'male', 2), ('oscar', 'female', 3);
desc dep;
desc emp;
select * from dep;
select * from emp;
# 使用外键关联以后2表被外键关联的字段所对应的记录都不能进行更新. 员工表的: dep_id 部门部门表的: id
update emp set dep_id=200 where dep_id=2;
update dep set id=200 where id=2;
# 使用外键的部门表不能进行记录的删除.
delete from dep where id=2;
delete from emp where id=2; # 员工表可以
"""删除的2种方式"""
# 第一种: 先删除关联表(emp), 再删除被关联表(dep). 无论是争对记录的删除delete, 还是表的删除规则都是不变.
delete from emp where id=1;
delete from dep where id=1;
# 第二种: 使用级联更新, 级联删除
'''
级联更新(同步更新) on update cascade
级联删除(同步删除) on delete cascade
'''
drop table emp;
drop table dep;
create table dep(
id int primary key auto_increment,
dep_name varchar(16),
dep_desc varchar(32)
);
insert into dep(dep_name, dep_desc) values('外交部', '漂泊游荡'), ('教学部', '教书育人'), ('技术部', '技术能力有限部门');
create table emp(
id int primary key auto_increment,
emp_name varchar(16),
emp_gender enum('male', 'female'),
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
insert into emp(emp_name, emp_gender, dep_id) values('jason', 'male', 1), ('egon', 'female', 2), ('tank', 'male', 2), ('kevin', 'male', 2), ('oscar', 'female', 3);
desc dep;
desc emp;
select * from dep;
select * from emp;
# 更新部门表的id. 员工表中得dep_id也会发生同样的改变
update dep set id=200 where id=2;
select * from dep;
select * from emp;
# 删除某一个部门表. 与部门表所关联的所有员工都会被清除.
delete from dep where id=1;
select * from dep;
select * from emp;
6. 使用foreign key建立多对多关系
注意: 争对多对多的表关系, 不能在两张原有的表中创建外键进行关联. 需要建立中间表来关联2表之间的关系.
图书表: book
| id | title | price | author_id |
|---|---|---|---|
| 1 | python入门到放弃 | 21000 | 1,2 |
| 2 | 葵花宝典 | 6666 | 1,2 |
| 3 | 前端 | 9 | 1 |
| 4 | 水浒传 | 123 | 2 |
作者表: author
| id | name | age | book_id |
|---|---|---|---|
| 1 | jsaon | 18 | 1,2 |
| 2 | egon | 78 | 3,4 |
# 先确定表与表之间的关系
"""
先站在书籍表考虑: 一本书可不可以有多个作者. --> 可以!!
再站在作者表考虑: 一个作者可不可以写多本书. --> 可以!!
结论: 书籍表和作者表是双向的多对多, 那么表关系就是"多对多"关系.
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(autor_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table author(
id int primary key auto_increment,
title varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
"""按照上述的方式创建 一个都别想成功！！"""
中间表: book2author
注意1: 如果一本书有多个作者必须重新开启一行.
注意2: 建立了中间表以后中间表与上面2表是多对一的关系. 新建的中间表是多的一方, 上面2表是一的一方. 所以因该先创建上面2表, 再创建需要建立外键关联中间表
- 一个book2author中的id可以对应多个作者. --> 不成立
- 一个作者可以对应多个book2author中的多个id --> 成立
| id | book_id | author_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
create table book(
id int primary key auto_increment,
title varchar(16) not null,
price int not null
);
insert into book(title, price) values('python入门到放弃', 21000), ('葵花宝典', 6666), ('前端', 9), ('水浒传', 123);
create table author(
id int primary key auto_increment,
name varchar(16) not null,
age int not null
);
insert into author(name, age) values('jsaon', 18), ('egon', 78);
# 针对多对多字段表关系 不能在两张原有的表中创建外键 需要你单独再开设一张 专门用来存储两张表数据之间的关系
create table book2author(
id int primary key auto_increment,
book_id int not null,
author_id int not null,
foreign key(book_id) references book(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key(author_id) references author(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
insert into book2author(book_id, author_id) values(1, 1), (1, 2), (2, 1), (2, 2), (2, 2), (3, 2), (4, 1);
select * from book;
select * from author;
select * from book2author;
7. 使用foreign key建立一对一关系
作者表: author
注意: 对于一对一关系表authordetail_id使用过就不能使用, 应该在外键的基础之上使用unique.
detail /ˈdiːteɪl/ 细节 详情 零件
| id | name | age | authordetail_id |
|---|---|---|---|
| 1 | jsaon | 18 | 1 |
| 2 | egon | 73 | 2 |
作者详情表: authordetail
| id | postcode | address |
|---|---|---|
| 1 | 335000 | 鹰潭市 |
| 2 | 200050 | 上海市 |
# 先确定表与表之间的关系
"""
站在作者的角度:
一个作者可以对象多个用户详情. --> 不成立
站在详情的角度:
一个用户详情可以对应多个作者. --> 不成立
结论: 一对一
"""
# 强调!!!: 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中(author)
create table authordetail(
id int primary key auto_increment,
postcode int not null,
address varchar(64) not null
);
insert into authordetail(postcode, address) values(335000, '鹰潭市'), (200050, '上海市');
create table author(
id int primary key auto_increment,
name varchar(16) not null,
age int not null,
authordetail_id int not null unique, # 注意: 对于一对一关系表authordetail_id使用过就不能使用, 应该在外键的基础之上使用unique.
foreign key(authordetail_id) references authordetail(id)
on update cascade
on delete cascade
);
insert into author(name, age, authordetail_id) values('jsaon', 18, 1), ('egon', 73, 2);
select * from author;
select * from authordetail;
8. 总结
# 表关系的建立需要用到foreign key
"""
一对多: 外键字段建立在多的一方
多对多: 开设中间表存储
一对一: 外键字段建立在任意一方都可以, 但是推荐建立在查询频率比较高的一方
"""
# 判断表之间关系的方式
"""
从多个角度切入. 如果是2表之间的关系那么只有在2表都换位思考了以后, 才能进行判断表与表之间的关系.
"""
# 使用
foreign key(关联表字段) references 被关联表表名(被关联字段)
on update cascade 级联更新(同步更新)
on delete cascade 级联删除(同步删除)
