?

?

1 表關系之外鍵

1.1 前戲之一對多關系

# 定義一張部門員工表 id name gender 　dep_name dep_desc 1 jason male 教學部 教書育人 2 egon male 外交部 漂泊游蕩 3 tank male 教學部 教書育人 4 kevin male 教學部 教書育人 5 owen female 　　技術部 技術能力有限部門


把所有數據都存放于一張表的弊端
1.組織結構不清晰
2.浪費硬盤空間
3.擴展性極差(無法忽略的缺點)

1.2 解決方案

# 上述的弊端產生原因類似于把代碼全部寫在一個py文件中，我們應該怎么做？>>>解耦合！將上述一張表拆成員工和部門兩張表！ # 類似的表關系學生與班級，也是如此，一張學生表和一張班級表 # 分析表數據之間的關系：多個用戶對應一個部門，一個部門對應多個用戶。禁止一個用戶對應多個部門這種情況是另外一張表關系

?

1.3 如何查找表與表之間的關系

老師與課程表 1.站在老師表的角度：一名老師能否教授多門課程(限制死，不能，一名老師只能教python，不能同時教python和linux) 2.站在課程表的角度：一門課程能否可以被多個老師教,完全可以！ 那就是課程表多對一老師表，如何表示這種關系？在課程表中創建一個字段(tea_id)指向老師表的id字段學生與班級表 1.站在學生表的角度：？？？ 2.站在班級表的角度：？？？ 那就是學生表多對一班級表，如何表示這種關系？在學生表中創建一個字段(class_id)指向班級表的id字段

?

# 再回過頭來看員工與部門表，我員工表里面的dep_id我可以隨意更改，但是應該有一個強制限制，限制dep_id字段必須只是部門表已有的id字段才合理

?

2 一對多(Foreign Key)

foreign key會帶來什么樣的效果？
2.1 在創建表時，先建被關聯的表dep，才能建關聯表emp

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') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) );

?

2.2 在插入記錄時，必須先插被關聯的表dep，才能插關聯表emp

insert into dep(dep_name,dep_comment) values ('sb教學部','sb輔導學生學習，教授python課程'), ('外交部','老男孩上海校區駐張江形象大使'), ('nb技術部','nb技術能力有限部門');insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);

?

2.2.2 刪除

# 當我想修改emp里的dep_id或dep里面的id時返現都無法成功 # 當我想刪除dep表的教學部的時候，也無法刪除 # 方式1:先刪除教學部對應的所有的員工，再刪除教學部 # 方式2:受限于外鍵約束，導致操作數據變得非常復雜，能否有一張簡單的方式，讓我不需要考慮在操作目標表的時候還需要考慮關聯表的情況，比如我刪除部門，那么這個部門對應的員工就應該跟著立即清空# 先把之前創建的表刪除，先刪員工表，再刪部門表，最后按章下面的方式重新創建表關系

?

2.3 更新于刪除都需要考慮到關聯與被關聯的關系>>>同步更新與同步刪除

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') not null default 'male', dep_id int, foreign key(dep_id) references dep(id) on update cascade on delete cascade ); insert into dep(dep_name,dep_comment) values ('sb教學部','sb輔導學生學習，教授python課程'), ('外交部','老男孩上海校區駐張江形象大使'), ('nb技術部','nb技術能力有限部門');insert into emp(name,gender,dep_id) values ('alex','male',1), ('egon','male',2), ('lxx','male',1), ('wxx','male',1), ('wenzhou','female',3);

?

# 刪除部門后，對應的部門里面的員工表數據對應刪除
# 更新部門后，對應員工表中的標示部門的字段同步更新

?

3 多對多

# 圖書表與作者表之間的關系

仍然站在兩張表的角度： 1.站在圖書表：一本書可不可以有多個作者，可以！那就是書多對一作者 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), price int );insert into author(name) values ('egon'), ('alex'), ('wxx') ; insert into book(bname,price) values ('python從入門到入土',200), ('葵花寶典切割到精通',800), ('九陰真經',500), ('九陽神功',100) ;create table author2book( id int primary key auto_increment, author_id int, book_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, foreign key(book_id) references book(id) on update cascade on delete cascade );insert into author2book(author_id,book_id) values (1,3), (1,4), (2,2), (2,4), (3,1), (3,2), (3,3), (3,4);

?

?

4 一對一

客戶表和學生表(老男孩的客戶與學生之間,報名之前都是客戶，只有報了名的才能是學生)

```python
# 左表的一條記錄唯一對應右表的一條記錄，反之也一樣

create table customer( id int primary key auto_increment, name char(20) not null, qq char(10) not null, phone char(16) 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) #外鍵的字段一定要保證unique on delete cascade on update cascade );

?

# 三種外鍵關系都是用foreign key，區別在于如何使用以及其他條件限制即可做出三種關系
```

## 修改表

```mysql
# mysql對大小寫不敏感！！！
語法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 數據類型 [完整性約束條件…],
ADD 字段名 數據類型 [完整性約束條件…];
ALTER TABLE 表名
ADD 字段名 數據類型 [完整性約束條件…] FIRST;
ALTER TABLE 表名
ADD 字段名 數據類型 [完整性約束條件…] AFTER 字段名;
3. 刪除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段數據類型完整約束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 數據類型 [完整性約束條件…];
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 舊數據類型 [完整性約束條件…];
ALTER TABLE 表名
CHANGE 舊字段名 新字段名 新數據類型 [完整性約束條件…];
```

## 復制表

```mysql
# 查詢語句執行的結果也是一張表，可以看成虛擬表

# 復制表結構＋記錄 （key不會復制: 主鍵、外鍵和索引）
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;
```

## 作業布置

練習：賬號信息表，用戶組，主機表，主機組

```python
#用戶表
create table user(
id int not null unique auto_increment,
username varchar(20) not null,
password varchar(50) not null,
primary key(username,password)
);

#用戶組表
create table usergroup(
id int primary key auto_increment,
groupname varchar(20) not null unique
);

#主機表
create table host(
id int primary key auto_increment,
ip char(15) not null unique default '127.0.0.1'
);

#業務線表
create table business(
id int primary key auto_increment,
business varchar(20) not null unique
);

#建關系：user與usergroup

create table user2usergroup(
id int not null unique auto_increment,
user_id int not null,
group_id int not null,
primary key(user_id,group_id),
foreign key(user_id) references user(id),
foreign key(group_id) references usergroup(id)
);

#建關系：host與business
create table host2business(
id int not null unique auto_increment,
host_id int not null,
business_id int not null,
primary key(host_id,business_id),
foreign key(host_id) references host(id),
foreign key(business_id) references business(id)
);

#建關系：user與host
create table user2host(
id int not null unique auto_increment,
user_id int not null,
host_id int not null,
primary key(user_id,host_id),
foreign key(user_id) references user(id),
foreign key(host_id) references host(id)
);
```

練習:

```python
# 班級表
cid caption
# 學生表
sid sname gender class_id
# 老師表
tid tname
# 課程表
cid cname teacher_id
# 成績表
sid student_id course_id number
```

?

轉載于:https://www.cnblogs.com/Ryan-Yuan/p/11382900.html

總結

以上是生活随笔為你收集整理的day36-表关系(外键)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。