MySql學習(三) —— 子查詢(where、from、exists) 及 串連查詢(left join、right join、inner join、union join)

標籤：

 

同樣的，使用goods表來練習子查詢，表結構如下：

 

 所有資料(cat_id與category.cat_id關聯)：

 

 類別表：

 

 mingoods(串連查詢時作測試)

 

 

 

一、子查詢1、where型子查詢：把內層查詢的結果作為外層查詢的比較條件

1.1 查詢id最大的一件商品(使用排序+分頁實現)

　　：mysql> SELECT goods_id,goods_name,shop_price FROM goods ORDER BY goods_id DESC LIMIT 1;

　　

1.2 查詢id最大的一件商品(使用where子查詢實現)

　　：mysql> SELECT goods_id,goods_name,shop_price FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);

　　

1.3 查詢每個類別下id最大的商品(使用where子查詢實現)

　　：mysql> SELECT goods_id,goods_name,cat_id,shop_price FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);

　　

 

2、from型子查詢：把內層的查詢結果當成暫存資料表，供外層sql重新查詢。查詢結果集可以當成表看待。暫存資料表要使用一個別名。

2.1 查詢每個類別下id最大的商品(使用from型子查詢)

　　：mysql > SELECT goods_id,goods_name,cat_id,shop_price FROM

　　　　　　-> 　　 (SELECT goods_id,goods_name,cat_id,shop_price FROM goods ORDER BY cat_id ASC,goods_id DESC) AS tmp

　　　　　　-> GROUP BY cat_id;

　　子查詢查出的結果集看第二張圖，可以看到每個類別的第一條的商品id都為該類別下的最大值。然後將這個結果集作為一張暫存資料表，巧妙的使用group by 查詢出每個類別下的第一條記錄，即為每個類別下商品id最大。

　　

　　

 

3.exists型子查詢：把外層sql的結果，拿到內層sql去測試，如果內層的sql成立，則該行取出。內層查詢是exists後的查詢。

3.1 從類別表中取出其類別下有商品的類別(如果該類別下沒有商品，則不取出)，[使用where子查詢]

　　：mysql> SELECT c.cat_id,c.cat_name FROM category c WHERE c.cat_id IN (SELECT g.cat_id FROM goods g GROUP BY g.cat_id);

　　

3.2 從類別表中取出其類別下有商品的類別(如果該類別下沒有商品，則不取出)，[使用exists子查詢]

　　：mysql> SELECT c.cat_id,c.cat_name FROM category c WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);

　　exists子查詢，如果exists後的內層查詢能查出資料，則表示存在；為空白則不存在。

　　

4. any, in 子查詢　　

　　

4.1 使用 any 查出類別大於任何一個num值的類別。　

　　ANY關鍵詞必須後面接一個比較操作符。ANY關鍵詞的意思是“對於在子查詢返回的列中的任一數值，如果比較結果為TRUE的話，則返回TRUE”。

　　：mysql> SELECT cat_id,cat_name FROM category WHERE cat_id > ANY (SELECT num FROM nums);

　　

4.2 使用 in 查出cat_id 等於num的類別

　　：mysql> SELECT cat_id,cat_name FROM category WHERE cat_id IN (SELECT num FROM nums);

　　

4.3 in 的效果 跟 =any 的效果是一樣的。

　　

4.4 使用 all 查詢

　　詞語ALL必須接在一個比較操作符的後面。ALL的意思是“對於子查詢返回的列中的所有值，如果比較結果為TRUE，則返回TRUE。”

　　

4.5 not in 和 <> any 的效果是一樣的

　　NOT IN不是<> ANY的別名，但是是<> ALL的別名

　　

 

　　子查詢總結：　　

　　1. where型子查詢：把內層查詢的結果作為外層查詢的比較條件。

　　　 from型子查詢：把內層的查詢結果當成暫存資料表，供外層sql重新查詢。查詢結果集可以當成表看待，暫存資料表需要一個別名。

　　　 exists型子查詢：把外層sql的結果，拿到內層sql去測試，如果內層的sql成立，則該行取出。內層sql是exists後的查詢。

 　　　　

　　2. 子查詢也可以嵌套在其它子查詢中，嵌套程度可以很深。子查詢必須要位於圓括弧中。

 

　　3. 子查詢的主要優勢為：

　　　　　　子查詢允許結構化的查詢，這樣就可以把一個語句的每個部分隔離開。

　　　　　　有些操作需要複雜的聯合和關聯。子查詢提供了其它的方法來執行這些操作。

　　

　　4. ANY關鍵詞必須後面接一個比較操作符。ANY關鍵詞的意思是“對於在子查詢返回的列中的任一數值，如果比較結果為TRUE的話，則返回TRUE”。 　　

　　　 詞語 IN 是 ＝ANY 的別名，二者效果相同。

　　　 NOT IN不是  <> ANY  的別名，但是是  <> ALL  的別名。

 　　

　　5. 詞語ALL必須接在一個比較操作符的後面。ALL的意思是“對於子查詢返回的列中的所有值，如果比較結果為TRUE，則返回TRUE。”

 

　　6. 最佳化子查詢

    　　①. 有些子句會影響在子查詢中的行的數量和順序，通過加一些限制條件來限制子查詢查出來的條數。例如：

　　　　　　SELECT * FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2 ORDER BY column1); 

　　　　　　SELECT * FROM t1 WHERE t1.column1 IN (SELECT DISTINCT column1 FROM t2);

　　　　　　SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 LIMIT 1);

 　　　 ②. 用子查詢替換聯合。例如：

　　　　　　SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2);

　　　　　　代替這個：SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

 

 

 二、串連查詢

 學習串連查詢，先瞭解下"笛卡爾積"，看下百度給出的解釋：

　　

 在資料庫中，一張表就是一個集合，每一行就是集合中的一個元素。表之間作聯集查詢即是作笛卡爾乘積，比如A表有5條資料，B表有8條資料，如果不作條件式篩選，那麼兩表查詢就有 5 X 8 = 40 條資料。

 

先看下用到的測試表基本資料：我們要實現的功能就是查詢商品的時候，從類別表將商品類別名稱關聯查詢出來。

　　行數：類別表14條，商品表4條

　　

　　結構：商品表和類別表都有一個cat_id

　　

 　 小類別表(左右串連時做對比)

　　

　　

 

1.全相乘(不是全串連、串連查詢)，全相乘是作笛卡爾積　　

　　兩表全相乘，就是直接從兩張表裡查詢；從查詢的看出，總共查出了 4 X 14 = 56 條記錄，這些記錄是笛卡爾乘積的結果，即兩兩組合；

　　但我們要的是每個商品資訊顯示類別名稱而已，這裡卻查出了56條記錄，其中有52條記錄都是無效的資料，全相乘的查詢效率低。

　　：mysql> SELECT goods_id,goods_name,cat_name FROM mingoods,category;

 　　 

　　如果在兩張表裡有相同欄位，做聯集查詢的時候，要區別表名，否則會報錯誤(模糊不清)

　　：mysql> SELECT goods_name,cat_id,cat_name FROM mingoods,category;　　

　　　　

　　添加條件，使兩表關聯查詢，這樣查出來就是商品和類別一一對應了。雖然這裡查出來4條記錄，但是全相乘效率低，全相乘會在記憶體中產生一個非常大的資料(暫存資料表)，因為有很多不必要的資料。

　　如果一張表有10000條資料，另一張表有10000條資料，兩表全相乘就是100W條資料，是非常消耗記憶體的。而且，全相乘不能好好的利用索引，因為全相乘產生一張暫存資料表，暫存資料表裡是沒有索引的，大大降低了查詢效率。

　　：mysql> SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id, c.cat_name FROM mingoods g, category c WHERE g.cat_id = c.cat_id;

　　

　　

2.左串連查詢 left join ... on ...

　　文法：select A.filed, [A.filed2, .... ,] B.filed, [B.filed4...,] from <left table> as A  left join <right table> as B on <expression>

　　假設有A、B兩張表，左串連查詢即 A表在左不動，B表在右滑動，A表與B表通過一個關係來關聯線，B表去匹配A表。

　　

2.1先來看看on後的條件恒為真的情況

　　：mysql> SELECT g.goods_name,g.cat_id, c.cat_id ,c.cat_name FROM mingoods g LEFT JOIN category c ON 1;

　　跟全相乘相比，從可以看出，總記錄數仍然不變，還是 4 X 14 = 56 條記錄。但這次是商品表不動，類別表去匹配，因為每次都為真，所以將所有的記錄都查出來了。左串連，其實就可以看成左表是主表，右表是從表。

　　

2.2 根據cat_id使兩表關聯線

　　：mysql> SELECT g.goods_name,g.cat_id,c.cat_id,c.cat_name FROM mingoods g LEFT JOIN category c ON g.cat_id = c.cat_id;

　　使用左串連查詢達到了同樣的效果，但是不會有其它冗餘資料，查詢速度快，消耗記憶體小，而且使用了索引。左串連查詢效率相比於全相乘的查詢效率快了10+倍以上。

　　左串連時，mingoods表(左表)不動，category表(右表)根據條件去一條條匹配，雖說category表也是讀取一行行記錄，然後判斷cat_id是否跟mingoods表的相同，但是，左串連使用了索引，cat_id建立了索引的話，查詢速度非常快，所以整體效率相比於全相乘要快得多，全相乘沒有使用索引。

　　　　

2.3 查詢出第四個類別下的商品，要求顯示商品名稱

　　：mysql> SELECT g.goods_name,g.cat_id,c.cat_name,g.shop_price FROM goods g LEFT JOIN category c ON g.cat_id = c.cat_id WHERE g.cat_id = 4;

　　

2.4 對於左串連查詢，如果右表中沒有滿足條件的行，則預設填充NULL。

　　：mysql> SELECT g.goods_name,g.cat_id AS g_cat_id, c.cat_id AS c_cat_id,c.cat_id FROM mingoods g LEFT JOIN mincategory c ON g.cat_id = c.cat_id;

　　

 

3.右串連查詢 right join ... on ...

　　文法：select A.field1,A.field2,..., B.field3,B.field4  from <left table> A right join <right table> B on <expression>

　　右串連查詢跟左串連查詢類似，只是右串連是以右表為主表，會將右表所有資料查詢出來，而左表則根據條件去匹配，如果左表沒有滿足條件的行，則左邊預設顯示NULL。左右串連是可以互換的。

　　：mysql> SELECT g.goods_name,g.cat_id AS g_cat_id,  c.cat_id AS c_cat_id,c.cat_name FROM mingoods g RIGHT JOIN mincategory c ON g.cat_id = c.cat_id;

　　

 

4. 內串連 inner join ... on ...

　　文法：select A.field1,A.field2,.., B.field3, B.field4 from <left table> A inner join <right table> B on <expression>

 　　內串連查詢，就是取左串連和右串連的交集，如果兩邊不能匹配條件，則都不取出。

　　：mysql> SELECT g.goods_name,g.cat_id, c.* from mingoods g INNER JOIN mincategory c ON g.cat_id = c.cat_id;

　　

 

 5. 聯集查詢 union

　　文法：select A.field1 as f1, A.field2 as f2 from <table1> A union (select B.field3 as f1, field4 as f2 from <table2> B)

 　　union是求兩個查詢的並集。union合并的是結果集，不區分來自於哪一張表，所以可以合并多張表查詢出來的資料。

　　

5.1 將兩張表的資料合併查詢出來

　　：mysql> SELECT id, content, user FROM comment UNION (SELECT id, msg AS content, user FROM feedback);

　　

5.2 union查詢，列名不一致時，以第一條sql語句的列名對齊

　　：mysql> SELECT id, content, user FROM comment UNION (SELECT id, msg, user FROM feedback);

　　

5.3 使用union查詢會將重複的行過濾掉

　　：mysql> SELECT content,user FROM comment UNION (SELECT msg, user FROM feedback);

　　

5.4 使用union all查詢所有，重複的行不會被過濾

　　：mysql> SELECT content,user FROM comment UNION ALL (SELECT msg, user FROM feedback);

　　

5.5 union查詢，如果列數不相等，會報列數不相等錯誤

　　

5.6 union 後的結果集還可以再做篩選

　　：mysql> SELECT id,content,user FROM comment UNION ALL (SELECT id, msg, user FROM feedback) ORDER BY id DESC; 

　　

　　union查詢時，order by放在內層sql中是不起作用的；因為union查出來的結果集再排序，內層的排序就沒有意義了；因此，內層的order by排序，在執行期間，被mysql的程式碼分析器給最佳化掉了。

　　：mysql> (SELECT id,content,user FROM comment ORDER BY id DESC) UNION ALL (SELECT id, msg, user FROM feedback ORDER BY id DESC);

　　

　　order by 如果和limit一起使用，就顯得有意義了，就不會被最佳化掉

　　mysql> ( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 3 ORDER BY shop_price DESC LIMIT 3 )
　　　　 -> UNION
　　　　 -> ( SELECT goods_name,cat_id,shop_price FROM goods WHERE cat_id = 4 ORDER BY shop_price DESC LIMIT 2 );

　　

5.7 練習

　　：mysql> SELECT name, SUM(money) FROM ( ( SELECT * FROM A ) UNION ALL ( SELECT * FROM B ) ) tmp GROUP BY name;

　　

　　

 

 

 串連查詢總結：

　　1.在資料庫中，一張表就是一個集合，每一行就是集合中的一個元素。串連查詢即是作笛卡爾積，比如A表有1W條資料，B表有1W條資料，那麼兩表查詢就有 1W X 1W = 100W 條資料

 　　

 　  2.如果在兩張表裡有相同欄位，做聯集查詢的時候，要區別表名，否則會報錯誤(ambiguous 模糊不清)

 

　　3.全相乘效率低，全相乘會在記憶體中產生一個非常大的資料(暫存資料表)，因為有很多不必要的資料。

　　   如果一張表有10000條資料，另一張表有10000條資料，兩表全相乘就是100W條資料，是非常消耗記憶體的。

　　   而且，全相乘不能好好的利用索引，因為全相乘產生一張暫存資料表，暫存資料表裡是沒有索引的，大大降低了查詢效率。

　　 

　　4.左串連查詢時，以左表為主表，會將左表所有資料查詢出來；左表不動，右表根據條件去一條條匹配，如果沒有滿足條件的記錄，則右邊返回NULL。

　　   右串連查詢值，以右表為主表，會將右表所有資料查詢出來，右表不動，左表則根據條件去匹配，如果左表沒有滿足條件的行，則左邊返回NULL。　

　　　左右串連是可以互換的：A left join B  ==  B right join A (都是以A為主表) 。

　　　左右串連既然可以互換，出於移植相容性方面的考慮，盡量使用左串連。

 

　　5.串連查詢時，雖說也是讀取一行行記錄，然後判斷是否滿足條件，但是，串連查詢使用了索引，條件列建立了索引的話，查詢速度非常快，所以整體效率相比於全相乘要快得多，全相乘是沒有使用索引的。

　　   使用串連查詢，查詢速度快，消耗記憶體小，而且使用了索引。串連查詢效率相比於全相乘的查詢效率快了10+倍以上。

　　

　　6.內串連查詢，就是取左串連和右串連的交集，如果兩邊不能匹配條件，則都不取出。

 

　　7.MySql不支援外串連，相應的，MySql可以用union(聯集查詢)來查出左串連和右串連的並集。

　　　union查詢會過濾重複的行，union all 不會過濾重複的行。

　　　union查詢時，union之間的sql列數必須相等，列名以第一條sql的列為準；列類型可以不一樣，但沒太大意義。

 　　  union查詢時，order by放在內層sql中是不起作用的；因為union查出來的結果集再排序，內層的排序就沒有意義了；因此，內層的order by排序，在執行期間，被mysql的程式碼分析器給最佳化掉了。

 　　  但是，order by 如果和limit一起使用，就顯得有意義了，會影響最終結果集，就不會被最佳化掉。order by會根據最終是否會影響結果集而選擇性的最佳化。

 

^_^

 

MySql學習(三) —— 子查詢(where、from、exists) 及 串連查詢(left join、right join、inner join、union join)