MySQL學習筆記-子查詢和連接配接

MySQL學習筆記-子查詢和連接配接

使用戶端進入gbk編碼方式顯示：

1.子查詢

子查詢的定義：

子查詢（Subquery)是指出現在其他SQL語句内的SELECT子句。

例如：

其中SELECT * FROM t1 稱為Outer Query / Outer Statement （外部查詢）

SELECT col2 FROM t2 ， 被稱為SubQuery  （子查詢）

子查詢的條件：

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

子查詢可以包含多個關鍵字或條件，

                如：DISTINCT，GROUP BY，ORDER BY，LIMIT函數等。

子查詢的外部查詢可以是：SELECT , INSERT , UPDATE , SET 或 DO

子查詢的傳回值：

子查詢可以傳回标量、一行、一列或子查詢

2.使用比較運算符的子查詢

使用比較運算符的子查詢：

=、>、<、>=、<=、<>、！=、 <=>

文法結構：

operand（操作數）、comparison_operator （比較運算符）、subquery（子查詢）

用ANY  、SOME 或ALL修飾的比較運算符

operand comparison_operator ANY (subquery)

operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

1.文法結構  操作數  比較運算符 any（子查詢）

            操作數  比較運算符 some（子查詢）

            操作數  比較運算符 all（子查詢）

2.适合于子查詢有多個結果

3.any 和some結果一緻 all與any、some 相反

例，從tdb_goods表中 查詢平均價格，小數點保留2位，：

查詢平均價格以上的商品：

查詢超極本的清單，按價格升序排列：

查詢比超極本的最低價格高的清單，按價格降序的方式排列：

查詢比超極本的最高價格高的清單，按價格降序的方式排列：

3.使用[NOT]IN/EXISTS引發的子查詢

子查詢形式：

1.使用IN的子查詢

2.使用[NOT]IN的子查詢

  文法結構：operand comparison_operator [NOT] IN (subquery)

  =ANY 運算符與IN 等效，!=ALL或<>ALL運算符與NOT IN等效

3.使用[NOT]EXISTS的子查詢(用的相對較少)

  如果子查詢傳回任何行，EXISTS将傳回TRUE，否則為FALSE

例，查找不是超極本的商品清單：

4.使用INSERT...SELECT插入記錄

例：在tdb_goods_cates表中插入tdb_goods表中的goods_cate分類；

5.多表更新

多表更新：

其中，table_references即連接配接的文法結構

連接配接類型：

INNER JOIN，内連接配接

   在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等價的

LEFT [OUTER] JOIN，左外連接配接

RIGHT [OUTER] JOIN，右外連接配接

連接配接—文法結構

例，将tdb_goods中的goods_cate更新為tdb_goods_cate表中的對應cate_id。 

6.多表更新之一步到位

CREATE...SELECT

建立資料表同時将查詢結果寫入到資料表（合并了CREATE 和 INSERT...SELECT兩個操作步驟）

例：建立品牌分類資料表tdb_goods_brand，并将tdb_goods表中的brand_name寫入

例，将tdb_goods中的brand_name更新為tdb_goods_brand表中的對應brand_id。錯誤寫法（程式分别不出哪個brand_name屬于哪個表）：

解決方法是給表起别名：

查詢tdb_goods的資料表結構

goods_cate和brand_name任然是varchar，現在我們修改字段名goods_cate為cate_id，brand_name修改為brand_id,  為了節省空間，我們修改資料類型為smallant

7.連接配接的文法結構

連接配接在MySQL在SELECT語句、多表更新、多表删除語句中支援JOIN操作。

資料表參照

table_reference

tbl_name [[AS] alias] | table_subquery [AS] alias

資料表可以使用tbl_name AS alias_name

或tbl_name alias_name 賦予别名

table_subquery可以作為子查詢使用FROM子句中，

這樣的子查詢必須賦予别名。

8.内連接配接INNER JOIN

在MySQL中，JOIN，CROSS JOIN 和 INNER JOIN 是等價的

連接配接條件：

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

通常使用ON關鍵字來設定連接配接條件

使用WHERE關鍵字進行結果集記錄的過濾

内連接配接和外連接配接的差別

内連接配接 ，顯示左表及右表符合連接配接條件的記錄，即交集

例如 插入幾條記錄

-- 分别在tdb_goods_cates和tdb_goods_brands表插入記錄

-- 在tdb_goods資料表寫入任意記

例，檢視符合内連接配接的記錄，表中隻出現商品表和品牌表都有的記錄，這就是内連接配接（這裡商品表為左表，分類表為右表）：

9.外連接配接OUTER JOIN

左外連接配接

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

例，檢視符合左外連接配接的記錄，表中出現商品表的全部和2表表都有的記錄，這就是左外連接配接（這裡商品表為左表，分類表為右表）

右外連接配接

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

例，檢視符合右外連接配接的記錄，表中出現品牌表的全部和2表表都有的記錄，這就是右外連接配接（這裡商品表為左表，分類表為右表）

10.多表連接配接

/**  

  多表的連接配接跟兩張表的連接配接一樣

  表的連接配接實質就是外鍵的逆向限制

*/

例，檢視符合内連接配接的記錄，表中隻出現商品表和品牌表都有的記錄，這就是内連接配接的多表連接配接（這裡商品表為左表，品牌表和分類表為右表）：

11.無限級分類表設計

自身連接配接

例，查找所有分類及其父類：

例，查找所有分類及其子類：

例，查找所有分類及其子類的數目

為tdb_goods_types添加child_count字段

11.多表删除

例，查找重複記錄：

删除重複記錄

12.複制記錄

複制編号為19,20的兩條記錄

      本文轉自謝育政 51CTO部落格，原文連結：http://blog.51cto.com/kurolz/1929134，如需轉載請自行聯系原作者