MySQL學習16：多表連接

        1連接概述

        (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，左外連接。

        (4)連接條件

        使用ON關鍵字來設定連接條件，也能夠使用WHERE來代替。

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

        2連接方式

        (1)內連接(INNER JOIN)

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

        實例：

        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;

       3多表連接

       (1)多表連接示例

       我們在這里使用3張數據表的內連接作為說明：

       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)關于連接的幾點說明

       1)數據表B的結果集依賴數據表A

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

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

空的額外的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將在找到符合連接條件的記錄后停止搜索更多的行。

       4無窮級分類表設計

       (1)設計步驟：

       1)首先創建數據表

       CREATE TABLE tdb_goods_types(

         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('大家電',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('電腦整機',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('主機',10);

       3)查看數據表中的記錄

       SELECT * FROM tdb_goods_types;

       上面的顯示結果的最后1列表示的意思是：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;

       5多表刪除

       多表刪除的語法結構為：

       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值

較小的記錄。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈