MySQL學習16：多表串連

標籤：

        一串連概述        (1)串連

        MySQL資料庫在SELECT語句，多表更新以及多表刪除中都支援JOIN操作。多表串連的文法結構為：

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

 condtional_expr;

        (2)資料表參照

        table_reference table_name [[AS] alias] | table_subquery [AS] alias

        資料表可以使用table_name AS alias_name或table_name alias_name賦予別名。table_subquery可以作為子查

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

       我們在兩張資料表中的可能會有相同名稱的欄位，為了區分各個表中的欄位我們給其資料表名稱起了別名，用別

名加以區分。

        (3)連線類型

        INNER JOIN，內串連；在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等價的。

        LEFT [OUTER] JOIN，左外串連。

        RIGHT [OUTER] JOIN，右外串連。
        (4)串連條件

        使用ON關鍵字來設定串連條件，也可以使用WHERE來代替。

        通常使用ON關鍵字來設定串連條件，使用WHERE關鍵字進行結果集記錄的過濾。

        二串連方式        (1)內串連(INNER JOIN)

        顯示左表及右表符合串連條件的記錄：

  

        執行個體：

        使用內串連將資料表tdb_goods和資料表tdb_goods_cates兩個表串連起來進行聯集查詢

        SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON 

tdb_goods.cate_id = tdb_goods_cates.cate_id;

       我們看到查詢的結果只是尋找到22條記錄，我們新添加的第23條記錄並沒有被查詢到，因為不符合查詢的串連的

條件。

        (2)左外串連(LEFT [OUTER] JOIN)

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

        執行個體：

        顯示tdb_goods資料表中全部的記錄以及tdb_goods_cates資料表中合格記錄

        SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON 

tdb_goods.cate_id = tdb_goods_cates.cate_id;

        (3)右外串連(RIGHT [OUTER] JOIN)

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

        執行個體：

        顯示tdb_goods_cates資料表中的所有記錄以及tdb_goods資料表中合格記錄

        SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON 

tdb_goods.cate_id = tdb_goods_cates.cate_id\G;

       三多表串連       (1)多表串連樣本

       我們在這裡使用三張資料表的內串連作為說明：

       SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN 

tdb_goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands AS b ON

 g.brand_id =b.brand_id\G;

       我們得條到了最初的添加的22記錄。

       (2)關於串連的幾點說明       外串連：A LEFT JOIN B join_condition

       1)資料表B的結果集依賴資料表A

       2)資料報A的結果集根據左串連條件依賴所有資料表(B表除外)

       3)左外串連條件決定如何檢索資料表B(在沒有指定WHERE條件的情況下)

       4)如果資料表A的某條記錄符合WHERE條件，但是在資料表B不存在符合串連條件的記錄，將產生一個所有列為

空的額外的B行。

       也就是下面顯示的結果：

       SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON 

tdb_goods.cate_id = tdb_goods_cates.cate_id;

       這個結果我們在上面的例子中已經知道。

       如果使用內串連尋找的記錄在串連資料表中不存在，並且在WHERE子句中嘗試以下操作：col_name IS NULL

時，如果col_name被定義為NOT NULL，MySQL將在找到符合串連條件的記錄後停止搜尋更多的行。

       四無限級分類表設計       (1)設計步驟：       1)首先建立資料表

       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

       ); 

       2)插入記錄

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘家用電器‘,DEFAULT);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘電腦辦公‘,DEFAULT);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘大家電‘,1);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘生活電器‘,1);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘平板電視‘,3);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘空調‘,3);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘電風扇‘,4);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘飲水機‘,4);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘電腦整機‘,2);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘電腦配件‘,2);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘筆記本‘,9);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘超級本‘,9);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘遊戲本‘,9);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘CPU‘,10);

       INSERT tdb_goods_types(type_name,parent_id) VALUES(‘主機‘,10);

       3)查看資料表中的記錄

       SELECT * FROM tdb_goods_types;

       上面的顯示結果的最後一列表示的意思是：0代表頂級分類，沒有父親節點；1到10代表子類。

       (2)自身串連       自身串連指的是同一個資料表對其自身進行串連。

       執行個體：

       1)查看所有分類及其父類

       SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p 

ON s.parent_id = p.type_id;

       2)尋找所有分類及其子類

       SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS 

 s ON s.parent_id = p.type_id;

       3)尋找所有分類及其子類的數目

       SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT 

JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;

       五多表刪除

       多表刪除的文法結構為：

       DELETE tabke_name[.*] [,table_name[.*]] ... FROM table_references [WHERE where_condition];

       SELECT * FROM tdb_goods\G;

       我們尋找到有重複的記錄。那麼下面所做的事情就是將重複的記錄刪除，保留id值較小的記錄。

       (1)尋找重複記錄

       SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 

2;

       (2)重複資料刪除記錄

       DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP

 BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE

 t1.goods_id > t2.goods_id;

        (3)再次查看資料表中的所有記錄是否存在重複記錄

        SELECT * FROM tdb_goods\G;

        SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 

2;

       從上面的結果可以看出資料表中已經沒有重複的記錄，說明我們成功刪除了重複的記錄，並且保留了goods_id值

較小的記錄。

MySQL學習16：多表串連