MySQL中的數據分組和子查詢
一、數據分組
1、創建分組
分組是在SELECT語句的GROUP BY子句中建立的。
示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id
上述語句首先根據book_id進行分組，把所有book_id相同的條目放在一起，然后使用COUNT(*)統計每一組的數目。最有進行顯示。
因為使用了GROUP BY，就不必指定要計算和估值的每個組了。系統會自動完成。 GROUP BY子句指示MySQL分組數據，然后對每個組而不是整個結果集進行聚集。
注意：
1）GROUP BY子句可以包含任意數目的列。這使得能對分組進行嵌套，為數據分組提供更細致的控制。
2）GROUP BY子句中列出的每個列都必須是檢索列或有效的表達式（但不能是聚集函數）。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用別名。
3）如果分組列中具有NULL值，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
4）GROUP BY子句必須出現在WHERE子句之后， ORDER BY子句之前。
2、過濾分組
除了能用GROUP BY分組數據外， MySQL還允許過濾分組，規定包括哪些分組，排除哪些分組。可以通過 HAVING實現。HAVING非常類似于WHERE。事實上，之前所有類型的WHERE子句都可以用HAVING來替代。唯一的差別是WHERE過濾行，而HAVING過濾分組。HAVING支持所有WHERE操作符。
示例：SELECT book_id,COUNT(*) AS book_num FROM book GROUP BY book_id HAVING COUNT(*)>10
上述語句的最后增加了HAVING子句，它過濾COUNT(*) >=2（兩個以上的訂單）的那些分組。這里WHERE子句不起作用，因為這里需要的過濾是基于分組聚集值而不是特定行值的。
HAVING和WHERE的差別：
WHERE在數據分組前進行過濾， HAVING在數據分組后進行過濾。 WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響HAVING子句中基于這些值過濾掉的分組。
3、分組和排序的區別
雖然GROUP BY和ORDER BY經常完成相同的工作，但它們是有很大區別的。
order by 是按表中某字段排列表中數據。group by 是按某些字段分類。
例如：
按年齡排序表中的記錄
select * from users order by age
按年齡分類表中數據（就是求各個年齡的人數）
select age,count(*) as number1 from users group by age
一般在使用GROUP BY子句時，應該也給出ORDER BY子句。這是保證數據正確排序的唯一方法。千萬
不要僅依賴GROUP BY排序數據。
4、SELECT子句的書寫順序和執行順序
Select子句書寫順序：
Select … from tbl1, tbl2 where … group by … having ……order by (asc | desc )
當SELECT語句被DBMS執行時，其子句會按照固定的先后順序執行：
（1）FROM 子句。
（2）WHERE 子句。
（3）GROUP BY 子句。
（4）HAVING 子句。
（5）SELECT 子句。
（6）ORDER BY 子句。
基本的工作原理：FROM子句先被執行，通過FROM子句獲得一個虛擬表，然后通過WHERE子句從虛擬表中獲取滿足條件的記錄，生成新的虛擬表。將新虛擬表中的記錄通過GROUP BY子句分組后得到更新的虛擬表，而后HAVING子句在最新的虛擬表中篩選出滿足條件的記錄組成另外一個虛擬表中，SELECT子句會將指定的列提取出來組成更新的虛擬表，最后ORDER BY子句對其進行排序得出最終的虛擬表。通常這個最終的虛擬表被稱為查詢結果集。
二、子查詢
1、利用子查詢進行過濾
子查詢（ subquery） ，即嵌套在其他查詢中的查詢。
現在假設一個訂單存儲的情境。orders表存儲一行包含訂單號、客戶ID、訂單日期。各訂單的物品存儲在相關的orderitems表中。 orders表不存儲客戶信息。它只存儲客戶的ID。實際的客戶信息存儲在customers表中。
三個表的數據如下：
orders
orderitems
customers
現在，假如需要列出訂購物品TNT2的所有客戶，應該怎樣檢索？下面列出具體的步驟。
(1) 檢索包含物品TNT2的所有訂單的編號。
(2) 檢索具有前一步驟列出的訂單編號的所有客戶的ID。
(3) 檢索前一步驟返回的所有客戶ID的客戶信息。
上述每個步驟都可以單獨作為一個查詢來執行。可以把一條SELECT語句返回的結果用于另一條SELECT語句的WHERE子句。也可以使用子查詢來把3個查詢組合成一條語句。
SELECT
cust_name,
cust_contact
FROM
customers
WHERE cust_id IN
(SELECT
cust_id
FROM
orders
WHERE order_num IN
(SELECT
order_num
FROM
orderitems
WHERE prod_id = 'TNT2'))
為了執行上述SELECT語句， MySQL實際上必須執行3條SELECT語句。最里邊的子查詢返回訂單號列表，此列表用于其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表，此客戶ID列表用于最外層查詢的WHERE子句。最外層查詢確實返回所需的數據。
2、作為計算字段使用子查詢
使用子查詢的另一方法是創建計算字段。假如需要顯示customers表中每個客戶的訂單總數。訂單與相應的客戶ID存儲在orders表中。為了執行這個操作，遵循下面的步驟。
(1) 從customers表中檢索客戶列表。
(2) 對于檢索出的每個客戶，統計其在orders表中的訂單數目。
SELECT
cust_name,
cust_state,
(SELECT
COUNT(*)
FROM
orders
WHERE orders.`cust_id` = customers.`cust_id`) AS orders
FROM
customers
ORDER BY cust_name
這條SELECT語句對customers表中每個客戶返回3列 ：cust_name、 cust_state和orders。 orders是一個計算字段，它是由圓括號中的子查詢建立的。該子查詢對檢索出的每個客戶執行一次。子查詢中的WHERE子句與前面使用的WHERE子句稍有不同，因為它使用了完全限定列名。這種類型的子查詢稱為相關子查詢。任何時候只要列名可能有多義性，就必須使用這種語法（表名和列名由一個句點分隔）。例如orders和customers表中都有cust_id這個字段，這樣就存在歧義。
