MySQL學習15：子查詢(一)

標籤：

       一資料準備       (1)建立資料庫       CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
       (2)建立資料表

       CREATE TABLE IF NOT EXISTS tdb_goods(

         goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

         goods_name  VARCHAR(150) NOT NULL,

         goods_cate  VARCHAR(40)  NOT NULL,

         brand_name  VARCHAR(40)  NOT NULL,

         goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,

         is_show     BOOLEAN NOT NULL DEFAULT 1,

         is_saleoff  BOOLEAN NOT NULL DEFAULT 0

       );

       (3)查看資料表結構

       SHOW COLUMNS FROM tdb_goods;

       (4)寫入記錄

      設定MySQL資料庫用戶端顯示的中文字元碼格式：

      SET NAMES gbk;

      插入記錄：

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘R510VC 15.6

英寸筆記本‘,‘筆記本‘,‘華碩‘,‘3399‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘Y400N 14.0

英寸膝上型電腦‘,‘筆記本‘,‘聯想‘,‘4899‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘G150TH 15.6

英寸遊戲本‘,‘遊戲本‘,‘雷神‘,‘8499‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘X550CC 15.6

英寸筆記本‘,‘筆記本‘,‘華碩‘,‘2799‘,DEFAULT,DEFAULT);

 

INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘X240(20ALA0

EYCD) 12.5英寸超極本‘,‘超級本‘,‘聯想‘,‘4999‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘U330P 13.3

英寸超極本‘,‘超級本‘,‘聯想‘,‘4299‘,DEFAULT,DEFAULT);

INSERTtdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘SVP13226SC

B 13.3英寸觸控超極本‘,‘超級本‘,‘索尼‘,‘7999‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘iPad mini 

MD531CH/A 7.9英寸平板電腦‘,‘平板電腦‘,‘蘋果‘,‘1998‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘iPad Air 

MD788CH/A 9.7英寸平板電腦 （16G WiFi版）‘,‘平板電腦‘,‘蘋果‘,‘3388‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘ iPad mini 

ME279CH/A 配備 Retina 顯示屏 7.9英寸平板電腦（16G WiFi版）‘,‘平板電腦‘,‘蘋果‘,‘2788‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘IdeaCentre 

C340 20英寸一體電腦 ‘,‘台式機‘,‘聯想‘,‘3499‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘Vostro 3800-

R1206 台式電腦‘,‘台式機‘,‘戴爾‘,‘2899‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘iMac 

ME086CH/A 21.5英寸一體電腦‘,‘台式機‘,‘蘋果‘,‘9188‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff)VALUES(‘AT7-7414LP

 台式電腦 （i5-3450四核 4G 500G 2G獨顯 DVD 鍵鼠 Linux ）‘,‘台式機‘,‘宏碁‘,‘3699‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘Z220SFF 

F4F06PA工作站‘,‘伺服器/工作站‘,‘惠普‘,‘4288‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘PowerEdge 

T110 II伺服器‘,‘伺服器/工作站‘,‘戴爾‘,‘5388‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘Mac Pro 

MD878CH/A 專業級台式電腦‘,‘伺服器/工作站‘,‘蘋果‘,‘28888‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘ HMZ-T3W 

頭戴顯示裝置‘,‘筆記本配件‘,‘索尼‘,‘6999‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘商務雙肩背

包‘,‘筆記本配件‘,‘索尼‘,‘99‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘X3250 M4機

架式伺服器 2583i14‘,‘伺服器/工作站‘,‘IBM‘,‘6888‘,DEFAULT,DEFAULT);

 

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘玄龍精英版 

筆記本散熱器‘,‘筆記本配件‘,‘九州風神‘,‘134‘,DEFAULT,DEFAULT);

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(‘商務雙肩背

包‘,‘筆記本配件‘,‘索尼‘,‘99‘,DEFAULT,DEFAULT);

       SELECT * FROM tdb_goods\G;

       二子查詢       (1)子查詢相關概念       1子查詢

       子查詢(Subquery)是指出現在其他SQL語句內的(也就是另一個查詢語句中的)SELECT子句。  

       例句：

       SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

       其中，SELECT * FROM t1，稱為Outer Query(外查詢或者Outer Statement)，SELECT column1 FROM t2，稱

為Sub Query(子查詢)。

       2子查詢組成

       子查詢指嵌套在查詢內部，且必須始終出現在圓括弧內。

       子查詢可以包含多個關鍵字或條件，如DISTINCT、GROUP BY、ORDER BY、LIMIT和相關函數等。

       子查詢的外層查詢可以是：SELECT、INSERT、UPDATE、SET或DO。

       所以，我們說子查詢是嵌套在外查詢內部。而事實上它有可能在子查詢內部再嵌套子查詢，子查詢必須出現在圓

括弧之間。

       3子查詢返回值

       子查詢可以返回標量、一行、一列或子查詢。

       (2)三類子查詢       1由比較子引發的子查詢

       比較子包括：=、>、<、>=、<=、<>、!=、<=>。使用比較子引發的子查詢的文法結構為；operand

 comparsion_operator subquery。

        例子：

        首先設定MySQL資料庫用戶端資料顯示的編碼格式：

        SET NAMES gbk;

        1)查詢所有商品價格的平均值：

        SELECT AVG(goods_price) FROM tdb_goods;

        2)查詢所有商品價格的平均值，並且結果保留兩位小數

        SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;

        3)查詢所有商品價格中大於平均價格的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5324.32;

        4)通過子查詢查詢所有商品價格中大於平均價格的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT

 ROUND(AVG(goods_price),2) FROM tdb_goods);

        由此看出3)和4)的查詢結果是一致的，這說明子查詢起作用了。

        5)查詢商品類型是超極本的商品價格

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

        6)查詢商品類型是超級本的商品

        SELECT * FROM tdb_goods WHERE goods_cate = ‘超級本‘\G;

        2使用ANY、SOME或ALL修飾的比較子的子查詢

        當子查詢返回多個結果時可以使用ANY、SOME或ALL修飾的比較子，其中ANY和SOME是等價的，也就

是只需要符合其中一個即可，而ALL表示符合全部返回結果。其中文法結構為：

        operand comparsion_operator ANY (subquery);

        operand comparsion_operator SOME (subquery);

        operand comparsion_operator ALL (subquery);

        比較子使用ANY、SOME或ALL關鍵字的不同結果：

        1)查詢所有商品中價格不小於超級本價格(最小价格)的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ANY (SELECT

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

        2)查詢所有商品中價格不小於超級本價格(最大價格)的商品

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ALL (SELECT

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

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

        SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT

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

 

MySQL學習15：子查詢(一)