MySQL學習15：子查詢(二)

標籤：

       二子查詢       3由[NOT] IN/EXISTS引發的子查詢

       使用[NOT] IN引發的子查詢的文法結構：operand comparsion_operator [NOT] IN (subquery)。其中，=ANY運算

符與IN等價；!=ALL或<>ALL運算子與NOT IN等價。

        例子：

        1)查詢所有商品中價格等於超級本價格(任意一個)的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price IN (SELECT 

goods_price FROM tdb_goods WHERE goods_cate = ‘超級本‘);

        2)查詢所有商品中價格不等於超級本價格(任意一個)的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT 

goods_price FROM tdb_goods WHERE goods_cate = ‘超級本‘);

        使用[NOT] EXISTS引發的子查詢的文法結構：operand comparsion_operator [NOT] EXISTS (subquery);如果子

查詢返回任何行，EXISTS將返回TRUE；否則返回FALSE。這種情況我們使用的比較少。

        三子查詢應用        (1)INSERT SELECT命令

        使用INSERT...SELECT插入記錄的文法結構：INSERT [INTO] table_name [(col_name,...)] SELECT ...;

        1)先來建立一個資料表tdb_goods_cates

        CREATE TABLE IF NOT EXISTS tdb_goods_cates(

              cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

              cate_name VARCHAR(40) NOT NULL

        );

        2)再對資料表tdb_goods中的記錄進行商品類型分類

        SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

        3)將查詢的結果寫入到資料表tdb_goods_cates

        INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROm tdb_goods GROUP BY goods_cate;

        SELECT * FROM tdb_goods_cates;

        4)參照商品分類表tdb_goods_cates的商品類型cate_id去更新商品表tdb_goods的商品類型(這裡用到多表更新和

串連)

        UPDATE tdb_goodsINNER JOIN tdb_goods_catesON goods_cate = cate_name SET goods_cate = cate_id;

        SELECT goods_id,goods_cate FROM tdb_goods;

        (2)多表更新

        多表更新的文法結構：

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

 where_condition];

        上述的例子就是多表更新。

        1多表更新一步到位

        我們在上面進行多表更新需要幾個步驟，我們可以將其幾個步驟結合在一起使用，也就是CREATE SELECT命

令哎建立資料表的同時將查詢結果直接寫入到指定的資料表中。

        建立資料表同時將查詢結果寫入到資料表的文法結構：

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

        例子：

        1)尋找資料表tdb_goods中的商品品牌(不屬於步驟，只是一個簡單的查詢)

        SELECT brand_name FROM tdb_goods GROUP BY brand_name;

        2)建立資料表的同時寫入記錄：

        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;

        SHOW TABLES;

        SELECT * FROM tdb_goods_brands;

        3)參照資料表tdb_goods_brands中的brand_id去更新資料表tdb_goods中的brand_name的品牌類型

        UPDATE tdb_goods AS gINNER JOIN tdb_goods_brands AS bON g.brand_name = b.brand_name SET

 g.brand_name = b.brand_id;

        SELECT goods_id,brand_name FROM tdb_goods;

        (3)多表更新帶來的問題

       到這裡我們還有一個問題，就是更新了tdb_goods資料表中的goods_cate欄位和brand_name欄位，更新後的都

是數字類型的，而原來的是字串型的，因此需要修改tdb_goods資料表中的那兩個欄位名稱以及資料類型。

       1)查看tdb_goods資料表的表結構

       DESC tdb_goods;

       2)修改指定列的資料類型和欄位名稱

       ALTER TABLE tdb_goods 

       CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

       CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

       DESC tdb_goods;

       修改之後的檢查操作

       3)分別在tdb_goods_cates和tdb_goods_brands表插入記錄

       INSERT tdb_goods_cates(cate_name) VALUES(‘路由器‘),(‘交換器‘),(‘網卡‘);

       INSERT tdb_goods_brands(brand_name) VALUES(‘海爾‘),(‘清華同方‘),(‘神州‘);

       4)在tdb_goods資料表中寫入任意記錄

       INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(‘LaserJet Pro P1606dn 黑白雷射列印

機‘,12,4,1849);

       我們看到寫入記錄的時候中的cate_id是12，而在資料表tdb_goods_cates中的cate_id不存在12，但是依然可以插

入記錄，這是沒有使用外鍵約束的結果。

        

MySQL學習15：子查詢(二)