前端學資料庫之多表操作

目錄

前面的話

　　上一篇博文中介紹了子查詢的相關內容，最後我們將查詢結果存儲到一個新的數據表中。下麵我們將接著子查詢的案例，詳細介紹資料庫中的多表操作

準備工作

　　在上一篇博文，我們將詳細數據存儲到tdb_goods數據表中，將詳細數據中的類別信息存儲到tdb_goods_cates數據表中

　　接下來，我們要研究如何通過tdb_goods_cates數據表來更新tdb_goods表

多表更新

　　多表更新類似於單表更新

UPDATE table_references SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

　　表的參照關係如下：

table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

　　從結果中看出，tdb_goods數據表中goods_cate列中的值已經更新為tdb_goods_cates數據表中對應的cate_id的值。這樣一來，用數字替代字元串，極大地節省了存儲空間

兩步更新

　　在上面的多表更新的操作中，實際上我們經過了兩個步驟，先創建了一個空表，將原數據表的查詢結果寫入空表，再利用寫入結果的表反向更新原數據表

　　如果使用CREATE SELECT語句將可以實現兩步更新，在創建數據表同時將查詢結果寫入到數據表(合併了CREATE和INSERT...SELECT兩個操作步驟)，再利用寫入結果的表反向更新原數據表

CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

　　下麵來處理原數據表tdb_goods中的品牌信息，首先查詢tdb_goods表的"品牌"，並分組

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

　　將品牌信息放入新表tdb_goods_brands中

  CREATE TABLE tdb_goods_brands (
    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(40) NOT NULL
  ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

　　再參照品牌表，更新原商品數據表

　　這裡要註意的是，兩張表中，同時存在brand_name這個欄位。要區分它們，需要給它們起不同的別名或在欄位前面加入表名

　　查看商品數據表的列結構，我們發現，雖然數據被修改為了數字，但數據類型仍然是字元型

　　下麵修改商品數據表中goods_cate和brand_name的列名稱和列類型

　　這樣，我們已經將一個大的數據表分為小的數據表進行存儲了。現在，分別在tdb_goods_cates和tdb_goods_brands表再插入幾條新的記錄

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交換機'),('網卡');
INSERT tdb_goods_brands(brand_name) VALUES('海爾'),('清華同方'),('神舟');

　　在tdb_goods數據表也寫入新的記錄

 INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光印表機','12','4','1849');

連接

　　通過上面的操作，已經把重覆的數據分散到不同的數據表中進行存儲了，儘可能的節省存儲空間了。但是，顯示時，卻需要把原來的數據顯示出來，這就需要使用下麵要介紹的概念——連接

語法結構

　　MySQL在SELECT語句、多表更新、多表刪除語句中支持連接(JOIN)操作

table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

　　數據表參照(table_reference)時，數據表可以使用tbl_name AS alias_name 或tbl_name alias_name賦予別名

　　table_subquery可以作為子查詢使用在FROM子句中，這樣的子查詢必須為其賦予別名

tbl_name[[AS] alias] | table_subquery [AS] alias

連接類型

　　連接類型主要包括內連接(INNER JOIN)、左外連接(LEFT [OUTER] JOIN)、右外連接(RIGHT [OUTER] JOIN)

　　在mysql中，JOIN、CROSS JOIN 和 INNER JOIN是等價的

連接條件

　　使用ON關鍵字來設定連接條件，也可以使用WHERE來代替。一般地，使用ON關鍵字來設定連接條件，使用WHERE關鍵字進行結果集記錄的過濾

內連接

　　內連接顯示左表及右表符合連接條件的記錄

　　下麵通過內連接來查詢所有商品的詳細信息，原來商品表中有24件商品，但只顯示出23件，因為那一件不符合連接條件

　　關於內連接，有以下註意：使用內連接查找的記錄在連接數據表中不存在，並且在WHERE子句中嘗試一下操作：column_name IS NULL 。如果 column_name 被指定為 NOT NULL，MySQL將在找到符合連接著條件的記錄後停止搜索更多的行(查找衝突)

左外連接

　　左外連接指顯示左表的全部記錄及右表符合連接條件的記錄

　　下麵通過左外連接來查詢所有商品的詳細信息，原來商品表中有24件商品，現在也顯示出24件，但最後一件商品的分類為NULL，這是因為右表的這一個分類不符合條件，所以顯示為NULL

右外連接

　　右外連接指顯示右表的全部記錄及左表符合連接條件的記錄

　　下麵通過右外連接來查詢所有商品的詳細信息，原來商品表中有24件商品，現在顯示出26件，多出來的是符合右表但不符合左表的記錄

　　關於外連接，有以下幾點註意，以左外連接為例

A LEFT JOIN B join_condition

　　數據表B的結果集依賴於數據表A，數據表A的結果集根據左連接條件依賴所有數據表(B表除外)

　　左外連接條件決定如何檢索數據表B(在沒有指定WHERE條件的情況下)

　　如果數據表A的某條記錄符合WHERE條件，但是在數據表B不存在符合連接條件的記錄，將生成一個所有列為空的額外的B行

多表連接

　　三張表以上的連接稱為多表連接，原理與兩張表的連接相同

　　下麵通過內連接實現查詢所有商品的詳細信息

無限級表

　　上圖中是tdb_goods_cates表的記錄。但實際的分類並非這10類，而是無限分類。下麵來介紹無限分類的數據表的實現

　　無限級表至少需要三個列，一個是類型id，一個類型名稱，一個是父級id

CREATE TABLE tdb_goods_types(
 type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 type_name VARCHAR(20) NOT NULL,
 parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
); 

　　然後，寫入給定數據

自身連接

　　自身連接指同一個數據表對其自身進行連接。為作區分，需要添加別名。字表別名定義為s，父表別名定義為p

　　下麵來查找所有分類及其父類

　　下麵來查找所有分類及其子類

　　下麵來查找所有分類及其子類的數目

刪除重覆項

　　從記錄中，可以看出24條記錄中存在重覆的項，現在要想辦法把重覆的項刪除

　　首先，先查找到重覆的項

　　然後，需要使用多表刪除來實現刪除操作

DELETE tbl_name[.*][,tbl_name[.*]]...
FROM table_references
[WHERE where_condition]