MySQL連表操作之一對多

標籤：ber   rac   最好   關聯   ddd   問題：   pre   tabs   img   

引入

當我們在資料庫中建立表的時候，有可能某些列中值內容量很大，而且重複。

例子：建立一個學生表，按學校年紀班級分，表的內容大致如下：

id	name	partment
1	xxx	x學校x年級x班級
2	ooo	x學校x年級x班級
3	zzz	z學校x年級x班級
4	ddd	y學校x年級x班級

我們看出來對應的partment對應的值很長，而且重複量很大，這樣就很不合適。

 

因此我們考慮將複雜重複的部分單獨拿出來分成2個表：

第一張表：

id	caption
1	x學校x年級x班級
2	y學校x年級x班級
3	z學校x年級x班級

第二張表在之前的基礎上修改的：

id	name	partment
1	xxx	1
2	ooo	2
3	zzz	3
4	ddd	1

這樣看起來就很簡潔了,我們將很長的且重複的部分拿出來，然後規定編號，建立學生表的時候，學生對應的partment只需要取學校對應的id便可，這樣同時這2張表也就會關聯起來。

說明：

1、他們的關聯關係；表2中的partment和表1的id聯絡再一起。

2、表一的資料會對應表2中的多條資料，這就叫一對多。

問題：此時，兩張表是相對獨立的，都可以各自插入自己的資料，這樣做很合適的，並沒實質行的關聯？

因此，必須要將其限制。表二的partment資料必須是表一中有的，不然，就不讓其增加。

這裡（partment）就引入了一個名詞 —- 外鍵

外鍵

說明：
1、外鍵：一個表接收另一個表的主鍵。
2、partment外鍵的是表一中的nid。
3、當我們建立了外鍵，則系統變預設會為我們添加，相應的約束，如：表二中的partment資料必須是表一中nid有的；表二和表一就關聯起來了。

Navicat建立外鍵

建立part表：

建立person表：

外鍵的建立注意點：
建立外鍵時，互相對應的表中的資料類型必須一樣。

建立外鍵

1、首先在要建立外鍵的表上右鍵，選設計表。
2、進入設計表，在右邊顯示表行資訊，然後點擊外鍵：

3、選擇欄位，會出現一個選擇框，選擇你要設定外鍵的列；

4、選擇參考表，選擇要外鍵要關聯的表，

說明：
參數資料庫：因為兩個資料庫在同意目錄下，所以這裡可以不用選擇；預設是同目錄下的。

5、選擇參考欄位；選擇參考表中要設為外鍵的列；

注意：
外鍵的建立，連個表中關聯的列的資料類型必須一樣，不然不能成功。

6、儲存CTRL + S

 

使用外鍵

此時part中沒有資料，如果此時你在person中添加資料，結果：

 

 

在part中添加資料，結果：

 

 

此時再在，person中添加資料：

 

注意：

這裡不能輸入part中nid沒有的值，不然也會報錯。

SQL命令建立外鍵

建立part：

create table part(nid int not null auto_increment primary key,caption varchar(32) not null)

part

建立person：

create table person(  nid int not null auto_increment,  name varchar(32) default null,  email varchar(32) default null,  part_nid int,  primary key(nid),  constraint fk_person_part foreign key (part_nid) references part(nid))

person

建立完之後外鍵對應代碼的關係：

分析：
1、從名字可以看出代碼對應的是什麼位置的。
2、圖中名（C對應代碼中的CONSTRAINT）這行可以不用設，系統會預設幫我設定，但是最好設定，如果要刪除外鍵的時候，就可以通過這個名字進行相應的操作。

代碼刪除外鍵

alter table person1 drop foreign key fk_person1_part1;

代碼增加外鍵

alter table person1 add constraint fk_p1_t1 foreign key (part1_nid) references part1(nid);

通過外鍵進行資料操作

part表：

 

 

person表：

 

需求：要找出person表中屬於x學校的人？

1、之前學的辦法：

• 1、先去part中擷取x對應的nid
• 2、然後再通過這個nid與parson中part_id對用的關係，尋找出對應的name

select name from person where part_id in (select nid form part where caption = "x")

2、鏈表方法 left join

left join

使用連表提供的方法，left join作業碼：

select * from person left join part on person.part_id = part.nid

結果顯示：

分析：
left join：相當於將part表匹配的部分直接移動到person的列後面，組合起來顯示。

既然內容都合并了，那麼此時我們再加上判斷，就可以將要的資料擷取了。

代碼1：

select * from person left join part on person.part_id = part.nid where part.caption = "x"

結果：

說明：

之前我們用的 * 擷取的是全部的資訊，如果我們擷取指定的資訊，可以將其修改。

如：只擷取person的name代碼：

select person.name from person left join part on person.part_id = part.nid where part.caption = "x"

結果：

注意：
join串連的條件，使用 on 進行對接的，條件寫在on後面。

A left join B on a.xx = b.xx

left join的特點：
1、以A為主
2、將A中的所有資料羅列
3、B則只顯示與A相對應的資料

問題：驗證我們說的誰在前就誰為主，誰的資料就全部顯示，我們將person和part換個位置？

select * from part left join person on person.part_id = part.nid;

結果：

right join

在誰顯示所有資料的上來看，他和left join剛好相反，以後面的表為主，顯示其所有的資料。

 

inner join

會將沒有建立關係的資料忽略掉。不管誰在前，結果都是一樣。

select * from person inner join part on person.part_id = part.nid;

結果：

總結：
1、這幾個join可以寫多個的，意思就是一個表可以同時有多個外鍵。

2、當選擇的列名，是所有表中唯一的話，可以不用寫首碼的表名。如：person.part_id就可以直接寫成part_id.

3、上面的part表，有個別名叫，字典表

MySQL連表操作之一對多