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) 涉及外部查询的子查询
