目錄
一:MySQL外來鍵
- 引入(比喻)
1.1.定義一張員工表
id name age dep_name dep_desc
1 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 18
2 kevin 28
3 tony 38
4 oscar 48
5 jackson 58
id dep_name dep_desc
1 外交部 搞外交
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 : 永遠只能出現書籍表的id
author_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)
- 總結:多對多的關係，需要建立在第三張表上進行