一，汇总数据
1，聚集函数
经常需要汇总数据而不是直接使用它们实际检索出来的结果，为此，MySQL给出了5个聚集函数。使用这些函数， MySQL查询可用于检索数据，以便分析和报表生成。聚集函数（ aggregate function） 运行在行组上，计算和返回单个值的函数.
- AVG() 返回某列的平均值
- COUNT() 返回某列的行数
- MAX() 返回某列的最大值
- MIN() 返回某列的最小值
- SUM() 返回某列值之和
1，AVG()函数：
AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。
AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
mysql> # 返回products表中所有产品的平均价格
select avg(prod_price) as avg_price from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.04 sec)
mysql> # 返回特定供应商所提供产品的平均价格
select avg(prod_price) as avg_price from products where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.04 sec)
mysql> # avg()只能作用于单列，多列使用多个avg()
select avg(item_price) as avg_itemprice,avg(quantity) as avg_quantity from orderitems;
+---------------+--------------+
| avg_itemprice | avg_quantity |
+---------------+--------------+
| 12.904545 | 15.8182 |
+---------------+--------------+
1 row in set (0.04 sec)- AVG()函数忽略列值为NULL的行。
2， COUNT()函数：
COUNT()函数进行计数。 可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式：
- 使用COUNT(*)对表中行的数目进行计数， 不管表列中包含的是空值（ NULL）还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
mysql> # COUNT(*)对表中行的数目进行计数，不忽略空值
select count(*) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 5 |
+----------+
1 row in set (0.17 sec)
mysql> # 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
select count(cust_email) as num_cust from customers;
+----------+
| num_cust |
+----------+
| 3 |
+----------+
1 row in set (0.04 sec)3，MAX()函数：
MAX()返回指定列中的最大值。 MAX()要求指定列名。
mysql> # MAX()返回products表中最贵的物品的价格
select max(prod_price) as max_price from products;
+-----------+
| max_price |
+-----------+
| 55.00 |
+-----------+
1 row in set (0.03 sec)- 虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。
- MAX()函数忽略列值为NULL的行。
4，MIN()函数：
它返回指定列的最小值。与MAX()一样， MIN()要求指定列名。
mysql> # MIN()返回products表中最便宜物品的价格
select min(prod_price) as min_price from products;
+-----------+
| min_price |
+-----------+
| 2.50 |
+-----------+
1 row in set (0.03 sec)5，SUM()函数：
SUM()用来返回指定列值的和（总计）。
mysql> # 检索所订购物品的总数（所有quantity值之和）
select sum(quantity) as items_ordered from orderitems;
+---------------+
| items_ordered |
+---------------+
| 174 |
+---------------+
1 row in set (0.03 sec)
mysql> select sum(quantity) as items_ordered from orderitems where order_num = 20005;
+---------------+
| items_ordered |
+---------------+
| 19 |
+---------------+
1 row in set (0.03 sec)
mysql> # 订单20005的总订单金额
select sum(quantity * item_price) as total_price from orderitems where order_num = 20005;
+-------------+
| total_price |
+-------------+
| 149.87 |
+-------------+
1 row in set (0.03 sec)- 所有聚集函数都可用来执行多个列上的计算。
- SUM()函数忽略列值为NULL的行。
2，聚集不同值
以上5个聚集函数都可以如下使用：
- 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）。
- 只包含不同的值，指定
DISTINCT参数。
mysql> # 使用了DISTINCT参数，因此平均值只考虑各个不同的价格
select avg(distinct prod_price) as avg_price from products where vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.27 sec)- 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。
- DISTINCT必须使用列名，不能用于计算或表达式。
3，组合聚集函数
实际上SELECT语句可根据需要包含多个聚集函数。
mysql> # 4个聚集计算:物品的数目，产品价格的最高、最低以及平均值
SELECT
COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM
products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.32 sec)二，分 组 数 据
1，数据分组
分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。
如果要返回每个供应商提供的产品数目，或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商，这就是分组显身手的时候了。
2，创建分组
分组是在SELECT语句的GROUP BY子句中建立的。
mysql> # 按vend_id排序并分组数据
select vend_id, count(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
4 rows in set (0.03 sec)- GROUP BY子句指示MySQL按vend_id排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。
- GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。
- 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。
3，过滤分组
除了能用GROUP BY分组数据外， MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。HAVING子句非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
mysql> # COUNT(*) >=2（两个以上的订单）的那些分组
select cust_id, count(*) as orders from orders group by cust_id having count(*)>=2;
+---------+--------+
| cust_id | orders |
+---------+--------+
| 10001 | 2 |
+---------+--------+
1 row in set (0.13 sec)- 过滤是基于分组聚集值而不是特定行值的。
- WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。
mysql> #列出具有2个（含）以上、价格为10（含）以上的产品的供应商
select vend_id,count(*) as num_prods from products where prod_price >=10 group by vend_id having count(*)>=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
2 rows in set (0.04 sec)- 第一行是使用了聚集函数的基本SELECT，它与前面的例子很相像。
- WHERE子句过滤所有prod_price至少为10的行。
- 按vend_id分组数据， HAVING子句过滤计数为2或2以上的分组。
4，分组和排序
- 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。
mysql> # 检索总计订单价格大于等于50的订单的订单号和总计订单价格
select order_num,sum(quantity * item_price) as ordertotal from orderitems group by order_num having sum(quantity * item_price) >=50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20005 | 149.87 |
| 20006 | 55.00 |
| 20007 | 1000.00 |
| 20008 | 125.00 |
+-----------+------------+
4 rows in set (0.06 sec)
mysql> # 按总计订单价格排序输出
SELECT
order_num, SUM(quantity * item_price) AS ordertotal
FROM
orderitems
GROUP BY order_num
HAVING SUM(quantity * item_price) >= 50
ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
| 20006 | 55.00 |
| 20008 | 125.00 |
| 20005 | 149.87 |
| 20007 | 1000.00 |
+-----------+------------+
4 rows in set (0.04 sec)- GROUP BY子句用来按订单号（order_num列）分组数据，以便SUM(*)函数能够返回总计订单价格。
- HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。
- 用ORDERBY子句排序输出。
5，SELECT子句顺序
三，使用子查询
1，子查询
SQL还允许创建子查询（ subquery） ，即嵌套在其他查询中的查询。
2，利用子查询进行过滤
回顾表关系：
假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤：
- 检索包含物品TNT2的所有订单的编号。
- 检索具有前一步骤列出的订单编号的所有客户的ID。
- 检索前一步骤返回的所有客户ID的客户信息
mysql> SELECT
order_num
FROM
orderitems
WHERE prod_id='TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
2 rows in set (0.04 sec)
mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.16 sec)也可以使用子查询来把3个查询组合成一条语句.:
mysql> SELECT cust_id FROM orders WHERE order_num IN (
SELECT
order_num
FROM
orderitems
WHERE prod_id='TNT2'
);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.13 sec)- 在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。
- 代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。
3，作为计算字段使用子查询
作为计算字段使用子查询。
假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。为了执行这个操作，遵循下面的步骤：
- 从customers表中检索客户列表。
- 对于检索出的每个客户，统计其在orders表中的订单数目。
可使用SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的客户ID， 可仅对该客户的订单进行计数：
mysql> # 对客户10001的订单进行计数
select count(order_num) from orders where cust_id = 10001;
+------------------+
| count(order_num) |
+------------------+
| 2 |
+------------------+
1 row in set (0.03 sec)为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询：
mysql> # 显示customers 表中每个客户的订单总数
select cust_name,cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders
from customers order by cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
5 rows in set (0.14 sec)- where orders.cust_id = customers.cust_id告诉SQL比较orders表中的cust_id与当前正从customers表中检索的cust_id，这是涉及外部查询的子查询，被称为
相关子查询（correlated subquery），任何时候只要列名可能有多义性，就必须使用这种语法。
用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。 用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
