MySQL學(xué)習(xí)16：多表連接

        1連接概述

        (1)連接

        MySQL數(shù)據(jù)庫在SELECT語句，多表更新和多表刪除中都支持JOIN操作。多表連接的語法結(jié)構(gòu)為：

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

 condtional_expr;

        (2)數(shù)據(jù)表參照

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

        數(shù)據(jù)表可使用table_name AS alias_name或table_name alias_name賦予別名。table_subquery可以作為子查

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

       我們在兩張數(shù)據(jù)表中的可能會(huì)有相同名稱的字段，為了辨別各個(gè)表中的字段我們給其數(shù)據(jù)表名稱起了別名，用別

名加以辨別。

        (3)連接類型

        INNER JOIN，內(nèi)連接；在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等價(jià)的。

        LEFT [OUTER] JOIN，左外連接。

        (4)連接條件

        使用ON關(guān)鍵字來設(shè)定連接條件，也能夠使用WHERE來代替。

        通常使用ON關(guān)鍵字來設(shè)定連接條件，使用WHERE關(guān)鍵字進(jìn)行結(jié)果集記錄的過濾。

        2連接方式

        (1)內(nèi)連接(INNER JOIN)

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

        實(shí)例：

        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;

       我們看到查詢的結(jié)果只是查找到22條記錄，我們新添加的第23條記錄并沒有被查詢到，由于不符合查詢的連接的

條件。

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

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

        實(shí)例：

        顯示tdb_goods數(shù)據(jù)表中全部的記錄和tdb_goods_cates數(shù)據(jù)表中符合條件的記錄

        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)

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

        實(shí)例：

        顯示tdb_goods_cates數(shù)據(jù)表中的所有記錄和tdb_goods數(shù)據(jù)表中符合條件的記錄

        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張數(shù)據(jù)表的內(nèi)連接作為說明：

       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)關(guān)于連接的幾點(diǎn)說明

       1)數(shù)據(jù)表B的結(jié)果集依賴數(shù)據(jù)表A

       2)數(shù)據(jù)報(bào)A的結(jié)果集根據(jù)左連接條件依賴所有數(shù)據(jù)表(B表除外)

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

空的額外的B行。

       也就是下面顯示的結(jié)果：

       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;

       這個(gè)結(jié)果我們在上面的例子中已知道。

       如果使用內(nèi)連接查找的記錄在連接數(shù)據(jù)表中不存在，并且在WHERE子句中嘗試以下操作：col_name IS NULL

時(shí)，如果col_name被定義為NOT NULL，MySQL將在找到符合連接條件的記錄后停止搜索更多的行。

       4無窮級(jí)分類表設(shè)計(jì)

       (1)設(shè)計(jì)步驟：

       1)首先創(chuàng)建數(shù)據(jù)表

       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('空調(diào)',3);

       INSERT tdb_goods_types(type_name,parent_id) VALUES('飲水機(jī)',4);

       INSERT tdb_goods_types(type_name,parent_id) VALUES('電腦整機(jī)',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('主機(jī)',10);

       3)查看數(shù)據(jù)表中的記錄

       SELECT * FROM tdb_goods_types;

       上面的顯示結(jié)果的最后1列表示的意思是：0代表頂級(jí)分類，沒有父親節(jié)點(diǎn)；1到10代表子類。

       (2)本身連接

       實(shí)例：

       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)查找所有分類及其子類的數(shù)目

       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多表刪除

       多表刪除的語法結(jié)構(gòu)為：

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

       SELECT * FROM tdb_goods\G;

       我們查找到有重復(fù)的記錄。那末下面所做的事情就是將重復(fù)的記錄刪除，保存id值較小的記錄。

       (1)查找重復(fù)記錄

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

2;

       (2)刪除重復(fù)記錄

       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)再次查看數(shù)據(jù)表中的所有記錄是不是存在重復(fù)記錄

        SELECT * FROM tdb_goods\G;

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

2;

       從上面的結(jié)果可以看出數(shù)據(jù)表中已沒有重復(fù)的記錄，說明我們成功刪除重復(fù)的記錄，并且保存了goods_id值

較小的記錄。

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)