MySQL資料庫多表操作

jacklove原創: 2018-06-11 23:15:243261瀏覽

一、外鍵

1. 什麼是外鍵：外鍵是指引用另一個表中的一列或多列，被引用的列應具有主鍵約束或唯一性約束。外鍵用於建立和加强两表資料之間的連接。

研究表中的gid是學生所在班級id，是引進了grade表中的主鍵id。那麼gid就可以當作表student的外鍵。被引用的表，即表grade是主表；引用外鍵的表，即表student是從表，兩個表是主從關係。表student用gid可以連接表grade中的信息，從而建立了兩個表資料之間的連接。

引人外鍵後，外鍵列只能插入參考列存在的值，參考列被參考的值不能刪除，這就保證了資料的參考完整性。

2. 為表格新增外鍵限制

想要真正連接兩個表格的數據，就需要為表格新增外鍵約束。為表格新增外鍵約束的語法格式如下：alter table 表名add constraint FK_ID foreign key(外鍵欄位名稱) references 外鍵表名（主鍵欄位名稱）

#在為表格新增外鍵約束時，有些需要注意的地方，如下：

（1）建立外鍵的表格必須是InnoDB型，不能是臨時表。因為在MySQL中只有InnoDB類型的表才支援外鍵；

（2）定義外鍵名時，因此不能加引號，如constraint ‘FK_ID’或constraint “FK_ID”都是錯誤的。

在主表中資料被刪除時，從表的資料也應被刪除，否則會參數很多無意義的垃圾資料。 Mysql可以在建立外鍵時加入on delete或on update子句來告訴資料庫，如何避免垃圾資料的產生。具體語法格式如下：

Alter table 表名addconstraint 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值替換（不能用於已標記為not null的欄位）
No action	不進行任何動作
#Restrict

################### ##拒絕主表刪除或修改外鍵關聯列。（在不定義on delete和on update子句時，這是預設設置，也是最安全的設定）##############

3. 刪除外鍵限制: alter table 表名drop foreign key 外鍵名稱;

#二、作業關聯表

1. 關聯關係

（1）多對一：在

多

對一的表關係中，應該將外鍵建在

多的一方，否則會造成資料的冗餘。

（2）多對多：如學生表及課程表。通常情況下，為了實現這種關係需要定義一張中間表（稱為連接表），該表會存在兩個外鍵，分別參照課程表和學生表。在多對多的關係中，需要注意的是，連接表的兩個外鍵都是可重複的，但是兩個外鍵之間的關係是不能重複的，所以這兩個外鍵又是表的聯合主鍵。

（3）一對一：首先要分辨主從關係，從表需要主表的存在才有意義，如人為主表，身分證為從表，外鍵建立在從表。需注意的時，這種關係在資料庫中並不常見，因為以這種方式儲存的資訊通常會放在一個表中。在實際開發中，一對一關聯關係可以應用於以下幾個方面。

Ø 分割具有很多列的表；

Ø 由於安全原因而隔離表的一部分；

Ø 保存臨時的數據，並且可以毫不費力地通過刪除該表而刪除這些資料。

2. 新增資料

#上述語句執行成功後，兩個表格之間的資料就具有關聯性。假如要查詢軟體一班有哪些學生，首先要查詢軟體一班的id，然後根據這個id在student表中查詢該班級有哪些學生。

3. 刪除資料：由於grade表和student表之間具有關聯關係，因此參考列被參考的值是不能刪除的。因此，在刪除軟體一班時，一定要先刪除該班級的所有學生，然後再刪除班級。

（1）刪除軟體一班的所有學生刪除

（2）在grade表中，軟體一班移除

若直接移除軟體中，請移除軟體一班」並不需要刪除'軟體一班的學生'，可以將表student中gid=1地方改成gid為null只要主表中該列沒有被從表參照就可以刪除。但是在建表時，gid欄位有非空約束，所以這個範例只能刪除學生。

三、連接查詢：當兩個或多個表中存在相同意義的欄位時，便可以透過這些欄位對不同的表進行連接查詢。
1. 交叉連結：傳回的結果是連接的兩個表格中所有資料行的笛卡兒積，也就是傳回第一個表格中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。語法格式如下：

Select * from 表1 cross join 表2;

##################### #############################################從上面的結果可以看出，交叉連結的結果就是兩個表中所有資料的組合，需要注意的是，在實際研發中，這種需求是很少見的，一般不會使用，而是使用具體的條件對資料進行有目的的查詢。 ######2. 內連結：又稱為簡單連結或自然連結。使用比較運算子對兩個表格的資料進行比較，並列出與連接條件相符的資料行，組合成新的記錄。文法格式如下：###

Select 查詢字段from 表1 [inner] join 表2 on 表1.關係字段=表2.關係字段

也可以使用where條件語句來實現同樣的功能。

雖然這兩種方式的查詢結果是一樣的，但是inner join是內連接語句，where是條件判斷語句，在where後面可以直接加入其他條件，而inner join語句不可以。

#如果在一個連接查詢中，涉及兩個表是同一個表，這種查詢稱為自連接查詢。自連接是一種特殊的連接，它是指相互連接的表在物理上為同一個表，但邏輯上分為兩個表，例如要查詢王紅所在部門有哪些員工，就可以使用自連接查詢。

3. 外連結：關鍵字左邊的表稱為座標，右邊的字稱為右表

Select 所查字段from 表1 left|right [outer] join 表2

On 表1.關係欄位=表2.關係欄位where條件

（1） left join(左連結)：傳回包含左表中的所有記錄和右表中符合連接條件的記錄。

如果左表的某筆記錄在右表中不存在，則在右表中顯示為空。

（2） Right join(右連接)：傳回包含右表中所有的記錄和左表中符合連接條件的記錄。

4. 複合條件連結查詢

四、子查詢：是指一個查詢語句嵌套在另一個查詢語句內部的查詢。它可以嵌套在一個select、select … into語句、insert…into等語句中。在執行查詢語句時，首先會執行子查詢中的語句，然後將傳回結果作為外層查詢的篩選條件，在子查詢中通常可以使用in、exists、any、all操作符

（ 1）帶in關鍵字的子查詢：內層查詢語句只傳回一個資料列，這個資料列中的值將供外層查詢語句進行比較操作。

例如：查詢存在年齡為20歲的員工部門。

（2）帶exists關鍵字的子查詢：exists關鍵字後面的參數可以是任一子查詢，而這個子查詢的作用相當於測試，它不會產生任何數據，只傳回True或False，當傳回值為True時，外層查詢才會執行。

下例中，子查詢的回傳結果為True，所以外層查詢語句會執行，也就是查詢出所有部門資訊。要注意的是，exists關鍵字比in關鍵字的運作效率高，所以在實際開發中，特別是大數據量時，建議使用exists關鍵字。

（3）帶any關鍵字的子查詢：any表示滿足其中任一個條件，它允許建立一個表達式對子查詢的回傳值清單進行比較，只要滿足內層子查詢中的任一個比較條件，就回傳一個結果作為外層查詢條件。

（4）帶all關鍵字的子查詢：和any有些類似，只不過是帶all關鍵字的子查詢回傳的結果需同時滿足所有內層查詢條件。

（5）帶比較運算子的子查詢

本文解譯了MySQL資料庫多表操作，更多相關內容請關注php中文網。

MySQL資料庫多表操作

相關文章