MySQL<多表操作>

標籤：嵌套   存在   sts   指定   拒絕   之間   表數   mysq   運算   

多表操作 外鍵

　　什麼是外鍵

　　　　外鍵是指引用另一個表中的一列或多列，被引用的列應該具有主鍵約束或唯一性限制式。

　　　　外鍵用於建立和加強兩個表資料之間的連結。

　　為表添加外鍵約束

　　　　想要真正串連兩個表的資料，就需要為表添加外鍵約束。

　　　　alter table 表名 add constraint FK_ID foreign key(外鍵欄位名) references外表表名(主鍵欄位名);

　　　　(1)建立外鍵的表必須是InnoDB型不能是暫存資料表。因為MySQL中只有InnoDB型的表才支援外鍵。

　　　　(2)定義外鍵名時，不能加引號。如：constraint ‘FK_ID‘ 或 constraint " FK_ID "都是錯誤的。

　　　　建立外鍵是為了保證資料的完整和統一性，即主表和從表中對應的資料保持統一，可以在建立外鍵時添加ON DELETE或ON UPDATE子句。

　　　　　　alter table 表名 add constraint FK_ID foreign key(外鍵欄位名) REFERENCES 外表表名(主鍵欄位名);

　　　　　　[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

　　　　　　[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

 

　　　　　　CASCADE：刪除包含於已刪除索引值有參照關係的所有記錄

　　　　　　SET NULL：修改包含與已刪除索引值有參照關係的所有記錄，使用NULL值替換

　　　　　　NO ACTION：不進行任何操作

　　　　　　RESTRICT：拒絕主鍵刪除或修改外部索引鍵關聯列。

 

　　刪除外鍵約束

　　　　解除兩個表之間的關聯關係時，就需要刪除外鍵約束

　　　　alter table 表名 drop foreign key 外鍵名;

多表建立原則

　　一對多：

　　　　在多的一方建立一個欄位作為外鍵指向一的一方的主鍵.

　　多對多：

　　　　建立一個中間表，中間表中至少兩個欄位，分別作為外鍵指向多對多雙方各自的主鍵

　　一對一：

　　　　唯一外鍵對應，主鍵對應

操作關係表

　　關聯關係

　　　　一對多(部門與員工) 、多對多(老師與學生)、 一對一(身份證)

　　添加資料

　　刪除資料

串連查詢

　　交叉串連

　　　　交叉串連返回的結果是被串連的兩個表中所有資料行的笛卡爾積即行數的乘積。

　　　　SELECT * from 表1 CROSS JOIN 表2;

　　　　在實際開發中這種業務需求是很少見，一般不會使用交叉串連，而是使用具體的條件對資料進行有目的的查詢。

　　內串連

　　　　內串連（INNER JOIN）又稱簡單串連或自然串連，內串連使用比較子對兩個表中的資料進行比較，列出與串連條件匹配的資料行，組合成新的記錄。

　　　　是將多個表連成一個表的查詢。只有那些滿足查詢條件的列才會出現在最後的表中

SELECT 查詢欄位 FROM 表1 [INNER] JOIN 表2 ON 表1.關係欄位 = 表2.關係欄位

　　隱氏內串連

　　　　select * from A,B where 條件

　　顯示內串連

　　　　select * from A inner join B on 條件

　　外串連

    　　左串連的結果包括LEFT JOIN子句中指定的左表的所有記錄，和所有滿足串連條件的記錄。如果左表的某條記錄在右表中不存在，則在右表中顯示為空白。

   　　 右串連與左串連正好相反，返回右表中所有指定的記錄和所有滿足串連條件的記錄。如果右表的某條記錄在左表中沒有匹配，則左表將返回空值。

　　　　SELECT 所查欄位 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2

　　　　ON 表1.關係欄位 = 表2.關係欄位 WHERE 條件

　　左外串連

　　　　select * from A left outer join B on 條件

　　右外串連

　　　　select * from A right outer join B on 條件

　　自串連

　　　　就是一個表與其自己進行串連

　　複合條件串連查詢(巢狀查詢)

　　　　巢狀查詢可以包括帶有IN謂詞的子查詢、帶有比較子的子查詢、帶有ANY或ALL謂詞的子查詢和帶有EXISTS謂詞的子查詢。

　　　　在串連查詢的過程中，通過添加過濾條件來限制查詢結果，使查詢結果更加精確。

子查詢

　　帶IN關鍵字的子查詢

　　帶EXISTS關鍵字的子查詢

　　帶ANY關鍵字的子查詢

　　帶ALL關鍵字的子查詢

　　帶比較子的子查詢

sql執行順序

　　1.from 表名

　　2.where 條件

　　3.group by

　　4.having

　　5.select

　　6.order by

MySQL<多表操作>