MySQL学习笔记（六）

一、分组数据

　　数据分组

1 mysql> SELECT COUNT(*) AS num_prods
2     -> FROM products
3     -> WHERE vend_id = 1003;
4 +-----------+
5 | num_prods |
6 +-----------+
7 |         7 |
8 +-----------+
9 1 row in set (0.03 sec)

　　创建分组

 1 mysql> SELECT vend_id, COUNT(*) AS num_prods
 2     -> FROM products
 3     -> GROUP BY vend_id;
 4 +---------+-----------+
 5 | vend_id | num_prods |
 6 +---------+-----------+
 7 |    1001 |         3 |
 8 |    1002 |         2 |
 9 |    1003 |         7 |
10 |    1005 |         2 |
11 +---------+-----------+
12 4 rows in set (0.03 sec)

***GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

***如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算。

***GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

***除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

***如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

***GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别的值

 1 mysql> SELECT vend_id, COUNT(*) AS num_prods
 2     -> FROM products
 3     -> GROUP BY vend_id WITH ROLLUP;
 4 +---------+-----------+
 5 | vend_id | num_prods |
 6 +---------+-----------+
 7 |    1001 |         3 |
 8 |    1002 |         2 |
 9 |    1003 |         7 |
10 |    1005 |         2 |
11 |    NULL |        14 |
12 +---------+-----------+
13 5 rows in set (0.00 sec)

　　过滤分组

 1 mysql> SELECT cust_id, COUNT(*) AS orders
 2     -> FROM orders
 3     -> GROUP BY cust_id
 4     -> HAVING COUNT(*) >= 2;
 5 +---------+--------+
 6 | cust_id | orders |
 7 +---------+--------+
 8 |   10001 |      2 |
 9 +---------+--------+
10 1 row in set (0.06 sec)

***HAVING支持所有WHERE操作符（WHERE过滤行，HAVING过滤分组）

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

 1 mysql> SELECT vend_id, COUNT(*) AS num_prods
 2     -> FROM products
 3     -> WHERE prod_price >= 10
 4     -> GROUP BY vend_id
 5     -> HAVING COUNT(*) >= 2;
 6 +---------+-----------+
 7 | vend_id | num_prods |
 8 +---------+-----------+
 9 |    1003 |         4 |
10 |    1005 |         2 |
11 +---------+-----------+
12 2 rows in set (0.00 sec)

 1 mysql> SELECT vend_id, COUNT(*) AS num_prods
 2     -> FROM products
 3     -> GROUP BY vend_id
 4     -> HAVING COUNT(*) >= 2;
 5 +---------+-----------+
 6 | vend_id | num_prods |
 7 +---------+-----------+
 8 |    1001 |         3 |
 9 |    1002 |         2 |
10 |    1003 |         7 |
11 |    1005 |         2 |
12 +---------+-----------+
13 4 rows in set (0.00 sec)

　　分组和排序

***一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。

 1 mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
 2     -> FROM orderitems
 3     -> GROUP BY order_num
 4     -> HAVING SUM(quantity*item_price) >= 50;
 5 +-----------+------------+
 6 | order_num | ordertotal |
 7 +-----------+------------+
 8 |     20005 |     149.87 |
 9 |     20006 |      55.00 |
10 |     20007 |    1000.00 |
11 |     20008 |     125.00 |
12 +-----------+------------+
13 4 rows in set (0.00 sec)

为按总计订单价格排序输出，需要添加ORDER BY子句

 1 mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal
 2     -> FROM orderitems
 3     -> GROUP BY order_num
 4     -> HAVING SUM(quantity*item_price) >= 50
 5     -> ORDER BY ordertotal;
 6 +-----------+------------+
 7 | order_num | ordertotal |
 8 +-----------+------------+
 9 |     20006 |      55.00 |
10 |     20008 |     125.00 |
11 |     20005 |     149.87 |
12 |     20007 |    1000.00 |
13 +-----------+------------+
14 4 rows in set (0.00 sec)

SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

二、使用子查询

　　利用子查询进行过滤

　　子查询(subquery)就是嵌套在其他查询中的查询。

 1 mysql> SELECT cust_id
 2     -> FROM orders
 3     -> WHERE order_num IN (SELECT order_num
 4     ->                     FROM orderitems
 5     ->                     WHERE prod_id = 'TNT2');
 6 +---------+
 7 | cust_id |
 8 +---------+
 9 |   10001 |
10 |   10004 |
11 +---------+
12 2 rows in set (0.00 sec)

　　子查询总是从内向外处理。在处理上面的SELECT语句时，MySQL实际上执行了两个操作。

首先，它执行下面的查询：

 SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

此查询返回两个订单号：20005和20007。然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。

 SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

***在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配。

　　作为计算字段使用子查询

 1 mysql> SELECT cust_name,
 2     ->        cust_state,
 3     ->        (SELECT COUNT(*)
 4     ->         FROM orders
 5     ->         WHERE orders.cust_id = customers.cust_id) AS orders
 6     -> FROM customers
 7     -> ORDER BY cust_name;
 8 +----------------+------------+--------+
 9 | cust_name      | cust_state | orders |
10 +----------------+------------+--------+
11 | Coyote Inc.    | MI         |      2 |
12 | E Fudd         | IL         |      1 |
13 | Mouse House    | OH         |      0 |
14 | Wascals        | IN         |      1 |
15 | Yosemite Place | AZ         |      1 |
16 +----------------+------------+--------+
17 5 rows in set (0.06 sec)

　　相关子查询(correlated subquery)　　涉及外部查询的子查询