MySQL之外键

目录

• 一:MySQL外键
  • 1.1.定义一张员工表
  • 2.上述表不合理之处
  • 3.将上述表拆分成两张表
  • 4.外键
• 二:如何查找表关系
  • 1.查找表的关系
  • 2.多对一：(示例图)
  • 3.查找表关系:换位思考
• 三:多对一(外键)实战
  • 1.创建表(书籍关联表)
  • 2.添加写入数据
• 四:外键约束
  • 1.外键约束问题
  • 2.如何删除或修改被关联表
• 五:级联更新 级联删除
  • 1.创建表必须先创建(被关联表)
  • 2.存储数据(先写入被关联表)
• 六:多对多
  • 1.以书籍表与作者表为例
  • 2.错误的创建方式(创建多对多)
  • 3.原因:
  • 4.解决多对多无法创建问题(第三方关联表)
  • 5.存储数据(写入表内数据)

一:MySQL外键

• 引入(比喻)

1.1.定义一张员工表

id	name	age 	dep_name	dep_desc1	jason	18		外交部		搞外交2	kevin	28		教学部		教书育人3	tony	38		教学部		教书育人4	oscar	48		安保部		保家卫国5	jackson	58		财务部		发工资

2.上述表不合理之处

1.表内部数据混乱(可忽略)2.反复的录入重复数据(可忽略)3.修改数据太过繁琐  浪费磁盘空间(可忽略)4.极大地影响了操作数据的效率

3.将上述表拆分成两张表

id	name	age 1	jason	182	kevin	283	tony	384	oscar	485	jackson	58id	dep_name	dep_desc1	外交部		搞外交2	教学部		教书育人3	安保部		保家卫国4	财务部		发工资

• 拆分完之后解决了上述四个缺陷

4.外键

用来记录表与表之间的关系dep_id

id	name	age	dep_id
1	jason	18	1
2	jevin	28	2
3	tony	38	2
4	oscar	48	3
5	jackson	58	4

二:如何查找表关系

1.查找表的关系

1.多对一2.多对多3.一对一4.没有关系

2.多对一：(示例图)

3.查找表关系:换位思考

书籍表与出版社表1.先站在书籍表问:一本书能够对应多个出版社答:不可以2.再站在出版社表问:一个出版社能否对应多本书答:可以结论:一个可以一个不可以 表关系为"多对一"那么外键字段建在"多"的一方

• 针对具有表关系的SQL建议先写普通字段 最后再考虑外键字段

三:多对一(外键)实战

1.创建表(书籍关联表)

create table book(	id int primary key auto_increment,    title varchar(32),    price int,    pub_id int,    foreign key(pub_id) references publish(id));

foreign key(关联表) references publish(被关联表)作用:book的pud_id字段 跟publish的id字段是(外键)关联的注意:(pud_id里面的字段永远只能写publish里面出现过的值)

创建表(出版社被关联表)create table publish(	id int primary key auto_increment,    name varchar(32));

• 必须先创建被关联表
  

2.添加写入数据

• 在写入数据的时候也需要先写入被关联表

被关联数据(写入数据):insert into publish(name) values('北方出版社'),('东方出版社');关联数据(写入数据):insert into book(title,price,pub_id) values('linux入门',666666,1),('python入门',88888888,1);

四:外键约束

1.外键约束问题

1.在创建表的时候需要先创建被关联表(没有外键的表)详解:    因为没有被关联表的话 无法识别到被关联表的时候就会导致报错2.在写入数据的时候也需要先写入被关联表(没有外键的表)详解:    在存储数据的时候，没有被关联表的外键字段，关联表也无法关联，会导致报错    3.被关联表里面的数据无法直接删除和修改关联字段的操作详解:    如果被关联表被删除了，那么关联表的外键字段就不知道指向谁了，会导致数据混乱 

2.如何删除或修改被关联表

如果要修改或者删除被关联表的字段的话，那么关联表的字段也要同样被修改或删除才合理该方法提供处理 被关联字段的删除与修改关键字(级联更新 级联删除):        on update cascade         on delete cascade

五:级联更新 级联删除

1.创建表必须先创建(被关联表)

关联表create table emp(	id int primary key auto_increment,    name varchar(32),    age int,    dep_id int,    foreign key(dep_id) references dep(id)     on update cascade     on delete cascade);

注意:
on updata cascade与on delete cascade不要加逗号因同属于一条命令

被关联表create table dep(	id int primary key auto_increment,    name varchar(32));

2.存储数据(先写入被关联表)

关联表insert into emp(name,age,dep_id) values('jason',18,1),('kevin',19,2),('jack',29,3),('tom',30,2);被关联表insert into dep(name) values('外交部'),('保安部'),('财务部');

六:多对多

1.以书籍表与作者表为例

1.先站在书籍表的基础之上    问:一本书能否对应多个作者    答:可以2.在站在作者表的基础之上    问:一个作者能否对应多本书    答:可以

结论:两个都可以那么表关系就是"多对多"
外键字段建在第三张关系表中

book_id		: 永远只能出现书籍表的idauthor_id	: 永远只能出现作者表的id

2.错误的创建方式(创建多对多)

create table book1(	id int primary key auto_increment,    title varchar(32),    author_id int,    foreign key(author_id) references author1(id)     on update cascade     on delete cascade);create table author1(	id int primary key auto_increment,    name varchar(32),    book_id int,    foreign key(book_id) references book1(id)     on update cascade     on delete cascade);

3.原因:

以上为错误的方式:    两张表都是关联表，无法进行关联，所以报错

4.解决多对多无法创建问题(第三方关联表)

关联表(书籍表)create table book1(	id int primary key auto_increment,    title varchar(32));关联表(作者表)create table author1(	id int primary key auto_increment,    name varchar(32));第三方被关联表(相当于中转站)create table book2author(	id int primary key auto_increment,    author_id int,    foreign key(author_id) references author1(id)     on update cascade     on delete cascade,    book_id int,    foreign key(book_id) references book1(id)    on update cascade     on delete cascade);

5.存储数据(写入表内数据)

书籍表insert into book1(title) values('python入门'),('java入门'),('linux入门');作者表insert into author1(name) values('ojbk'),(jason);第三方关联表insert into book2author(book_id,author_id) values(1,1),(2,1);insert into book2author(book_id,author_id) calues(2,2),(2,3)

• 总结:多对多的关系，需要建立在第三张表上进行