Foreign Key
1.一对多
#创建表
create database day06_db charset="utf8";
use day06_db;
create table dep(id int primary key auto_increment, dep_name char(10), dep_comment char(60));
create table emp(id int primary key auto_increment, name char(16), gender enum("male", "female", "others") not null default "male", dep_id int, foreign key(dep_id) references dep(id));
#插入数据
insert into dep(dep_name, dep_comment) values("教学部", "辅导学生学习,教授python"),("外交部", "形象大使"),("技术部", "技术nb");
![1b8c2ccb164a72fd49538c59a39b220d.png](https://i-blog.csdnimg.cn/blog_migrate/d507c5ff29fde16d7ed911f942a8f9ed.png)
insert into emp(name, gender, dep_id) values("alex", "male", 1),("egon", "male", 2),("lxx", "male", 1),("wenzhou", "female", 3);
![06b1ff03b054fc413b8997d308f0d19b.png](https://i-blog.csdnimg.cn/blog_migrate/88b34be9cb316418345f3ef62a08619f.png)
#删除表dep中的外交时,无法删除
![86c4afaf75345b8b0509a1d1235075ee.png](https://i-blog.csdnimg.cn/blog_migrate/3e391345374e589d950c13c16df70ccb.png)
#方式一:先删除员工表中外交部对应的所有员工,再删除外交部
delete from emp where id=2;
![fe921c7a838001386ecabc414c40150c.png](https://i-blog.csdnimg.cn/blog_migrate/a896624f67ca9e2800cd5713113c8460.png)
delete from dep where id=2;
![deb186db9d520077933bd705cbdb012a.png](https://i-blog.csdnimg.cn/blog_migrate/981e9c53ecffff9d10450ef4eead58d0.png)
#方式二:级联删除(当一张表删除记录时,若绑定给了另一张表中的外键,则一同删除另一张表中关联的记录)
先删除表重现建:
drop table emp;
drop table dep;
插入:
create table dep(id int primary key auto_increment, dep_name char(10), dep_comment char(60));
create table emp(id int primary key auto_increment, name char(16), gender enum("male", "female", "others") not null default "male", dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade);
#级联更新
on update cascade
#级联删除
on delete cascade
插入数据:
insert into dep(dep_name, dep_comment) values("教学部", "辅导学生学习,教授python"),("外交部", "形象大使"),("技术部", "技术nb");
insert into emp(name, gender, dep_id) values("alex", "male", 1),("egon", "male", 2),("lxx", "male", 1),("wenzhou", "female", 3);
删除技术部:
delete from dep where id=3;
![b41dea05e1e2fc2605ad1a2dba16251b.png](https://i-blog.csdnimg.cn/blog_migrate/6f9920a94755dce450512a995993a0da.jpeg)
修改教学部id为4
update dep set id=4 where id=1;
![3492772a65e0c873eafe793af1f06fa5.png](https://i-blog.csdnimg.cn/blog_migrate/69264b238b0e1777cca2013b12063039.jpeg)
2.多对多
如作者和书,一本书可以有多个作者,一个作者也可以写多本书
#先建立第三张表,该表中有一个字段fk左表的id,还有一个字段是fk右表的id
示例:
create table author(id int primary key auto_increment, name char(16));
create table book(id int primary key auto_increment, bname char(16), prise int);
insert into author(name) values("tank"),("jason"),("egon");
insert into book(bname, prise) values("python", 200),("葵花宝典", 800),("九阴真经", 400),("九阳神功", 100);
![6a887ee1414bc16f19a09e194bff7565.png](https://i-blog.csdnimg.cn/blog_migrate/a0d3266f90a03bacedc1d3b3cfd67a79.png)
create table author2book(id int primary key auto_increment, aid int, bid int,
foreign key(aid) references author(id) on update cascade on delete cascade,
foreign key(bid) references book(id) on update cascade on delete cascade);
insert into author2book(aid, bid) values(1,3),(1,4),(2,2),(2,4),(3,1),(3,2),(3,3),(3,4);
![9156dcea249c11e5ba9055771976e52c.png](https://i-blog.csdnimg.cn/blog_migrate/3ef58cdd41b6690ba0c8ea834c67fed8.png)
3.一对一
#左表的一条记录唯一对应右表的一条数据
create table customer(id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(10) not null);
create table student(id int primary key auto_increment, class_name char(20) not null, customer_id int unique, foreign key(customer_id) references customer(id) on update cascade on delete cascade);
insert into customer(name, qq, phone) values("学生1", "123123123", "12345678");
insert into student(class_name, customer_id) values("数据分析班", 1);
![63d391813a99f1c643fdd79010a1388a.png](https://i-blog.csdnimg.cn/blog_migrate/dfd65167b477a17c1134734c84d994ea.png)
4.修改表
1)修改表名
alter table 表名 rename 新表名;
2)增加字段
alter table 表名 add 字段名 数据类型 【约束条件】;
alter table 表名 add 字段名 数据类型 【约束条件】first;
alter table 表名 add 字段名 数据类型 【约束条件】after 字段名;
3)删除字段
alter table 表名 drop 字段名;
4)修改字段
alter table 表名 modify 数据类型;
alter table 表名 change 旧字段名 新字段名 旧字段类型;
alter table 表名 change 旧字段名 新字段名 新字段类型;
5.复制表
#复制表结构+数据
create table new_service select * from service;
#只复制表结构
select * from service where 1=2;
create table new1_service select * from service where 1=2;
create table t4 like employees;