【MySQL】第3回 主键与外键
1. 约束条件之主键
1.1 primary key主键
1.1.1 单从约束角度上而言主键等价于非空且唯一 not null unique
create table t1(
id int primary key,
name varchar(32)
);
# 验证非空
insert into t1(name) values('jason');
# 验证唯一
insert into t1(id,name) values(1,'jaosn');
insert into t1(id,name) values(1,'zhang');
1.1.2 InnoDB存储引擎规定一张表必须有且只有一个主键
- 如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键（主键可以加快数据查询：新华字典的目录）
- 如果创建的表中没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设置为主键
create table t2(
id int not null unique,
cid int not null unique,
name varchar(32)
);
desc t2;
1.1.3 创建表的时候都应该有一个'id'字段，并且该字段应该作为主键
- 命名不限 可以是id,cid,aid,pid....
- 补充说明
| 单列主键 | 联合主键 |
|---|---|
| id int primary key | sid int, nid int, primary key(sid,nid) 联合主键 |
| 最常见 | 联合主键也属于单列主键 |
1.2 auto_increment自增
1.2.1 该约束添加不能单独使用，必须跟在键后面(主要配合主键一起使用)
create table t3(
id int auto_increment
);
create table t3(
id int primary key auto_increment,
name varchar(32)
);
desc t3;
insert into t3(name) values('jason'),('zhang'),('li');
select * from t3;
1.2.2 自增特点
- 自增的操作不会因为执行删除数据的操作而回退或者重置
delete from t3 where id='3';
insert into t3(name) values('zhang'),('wang');
select * from t3;
delete from t3;
insert into t3(name) values('jason'),('li');
select * from t3;
2. 如果非要重置主键，需要格式化表，truncate 表名； 删除表数据并重置主键值
truncate t3;
insert into t3(name) values('jason'),('li');
select * from t3;
2. 约束条件之外键
2.1 外键前戏
2.1.1.需要创建一张员工表
- 上说表的缺陷
- 表结构不清晰，到底是员工表还是部门表
- 字段数据反复存取，浪费存储空间
- 表的扩展性极差，牵一发动全身
- 优化操作>>>:拆表
- id name gender
- id dep_name dep_desc
- 拆表之后解决了上述的三个问题，但是出现了一个致命的缺陷，不知道数据是那个部门，也不知道部门有那个员工
- 解决措施，添加一个部门编号字段填写部门数据的主键值dep_id
- 外键字段：专用于记录表于表之间数据的关系
2.2 外键字段的创建
2.2.1 外键字段是用来记录表与表之间数据的关系，而数据的关系有四种
- 一对多关系
- 多对多关系
- 一对一关系
- 没有关系
2.2.2 Foregin key
- 步骤
- 先写普通字段
- 然后再写外键字段
- 理解（一对多代码演示）
- 创建表的时候需要先创建被关联的表(没有外键)，然后再是关联表(有外键)
- 插入表数据的时候，针对外键字段只能填写被关联表字段已经出现过的数据值
- 被关联字段无法修改和删除，有点不太好，操作限制性太强
- 级联更新，级联删除（一对多代码演示）
- 被关联数据一旦变动，关联的数据同步变动
- 级联更新 on update cascade
- 级联删除 on delete cascade
- 扩展
- 在实际工作中，很多时候可能并不会使用外键，因为外键增加了表之间的耦合度，不便于单独操作，资源消耗增加
- 我们为了能够描述出表数据的关系，又不想使用外键，自己通过写SQL,建立代码层面的关系
3. 外键数据关系（换位思考）
3.1 一对多
3.1.1 针对员工表和部门表判断数据关系
- 先站在员工表的角度
问：一条员工数据能否对应多条部门数据
翻：一名员工能否属于多个部门
答：不可以 - 再站在部门表的角度
问:一条部门数据能否对应多条员工数据
翻:一个部门能否拥有多个员工
答:可以 - 结论
- 完成换位思考之后得出的答案 一个可以一个不可以
- 那么表关系就是"一对多"，部门是一 员工是多
- 针对'一对多'的关系 外键字段建在多的一方
- 注意
没有多对一，统一称为一对多
3.1.2 语句实现
# 一对多
# 先创建没外键的表
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
# 在创建有外键的表
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
# 针对外键字段只能填写被关联表字段已经出现过的数据值,插入空时也成功
insert into emp(name) values('jason')
select * from emp:
insert into emp(name,dep_id) values('li',10)
# 格式化emp
truncate emp;
insert into dep(dep_name,dep_desc) values('讲师部',1),('安保部',2);
# 报错只能填写被关联表字段已经出现过的数据值
insert into emp(name,dep_id) values('jason',4);
insert into emp(name,dep_id) values('jason',1);
# 被关联字段无法修改和删除
delete from dep where id=2;
delete from dep where id=1;
update dep set id= 200 where id=1;
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
create table emp1(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female') default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
insert into dep1(dep_name,dep_desc) values('讲师部','教书'),('安保部','维护');
insert into emp1(name,dep_id) values('jason',1),('wang',2),('zhao',2);
update dep1 set id=20 where id=1;
delete from dep1 where id=20;
select * from dep1;
select * from emp1;
3.2 多对多
3.2.1 以书籍表和作者表为例
- 先站在书籍表的角度
问:一条书籍数据能否对应多条作者数据
答:可以 - 再站在作者表的角度
问:一条作者数据能否对应多条书籍数据
答:可以 - 总结
- 两边都可以 那么表数据关系就是'多对多'
- 针对多对多表关系 外键字段不能建在任意一方。
- 需要单独开设第三张关系表 存储数据关系
3.2.2 语句实现
# 针对多对多表关系 外键字段不能建在任意一方。
create table book(
id int primary key auto_increment,
title varchar(32),
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),
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)
);
create table author(
id int primary key auto_increment,
name varchar(32)
);
create table book2author(
id int primary key auto_increment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
3.3 一对一
3.3.1 以用户表和用户详情表
- 先站在用户表的角度
问：一条用户表数据能否对应多条用户详情表数据
答：不可以 - 再站在用户详情表的角度
问：一条用户详情表数据能否对应多条用户表数据
答：不可以 - 总结
- 两边都不可以，那么先考虑是不是没有关系
- 如果有关系那么肯定就是'一对一'
- 针对'一对一'的表关系 外键字段建在任何一张表都可以，但是建议建在查询频率较高的表中便于后续查询
3.3.2 语法实现
# 唯一性，要不然和一对多没区别
create table userdetail(
id int primary key auto_increment,
phone bigint
);
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique, # 唯一性，要不然和一对多没区别
foreign key(detail_id) references userdetail(id)
on update cascade
on delete cascade
);
4. 作业
4.1 服务器表与应用程序表 一对多
create table server(
id int primary key auto_increment,
name varchar(32)
);
create table program(
id int primary key auto_increment,
name varchar(32),
server_id int,
foreign key(server_id) references server(id)
on update cascade
on delete cascade
);
4.2 课程表与班级表 多对多
create table course(
id int primary key auto_incerment,
name varchar(32)
);
create table class(
id int primary key auto_incerment,
name varchar(32)
);
create table unite(
id int primary key auto_incerment,
course_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
class_id int,
foreign key(class_id) referenxces class(id)
on update cascade
on delete cascade
);
4.3 学生表与班级表 一对多
create table clss(
id int primary key auto_increment,
name varchar(32)
);
create table student(
id int primary key auto_increment,
name varchar(32),
class_id int,
foreign key(class_id) references class(id)
on update cascade
on delete cascade
);
4.4 老师表与课程表 多对多
create table course(
id int primary key auto_incerment,
name varchar(32)
);
create table teacher(
id int primary key auto_incerment,
name varchar(32)
);
create table unite(
id int primary key auto_incerment,
course_id int,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
teacher_id int,
foreign key(teacher_id) referenxces teacher(id)
on update cascade
on delete cascade
);
4.5 书籍表与出版社表 多对多
create table book(
id int primary key auto_incerment,
name varchar(32)
);
create table publish(
id int primary key auto_incerment,
name varchar(32)
);
create table unite(
id int primary key auto_incerment,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
publish_id int,
foreign key(publish_id) referenxces publish(id)
on update cascade
on delete cascade
);
