MySQL必知必会学习笔记（四）

十二、汇总数据

使用聚集函数

运行在行组上，返回单个值

• 函数AVG()可以返回所有列的平均值，也可以返回指定列的平均值，如下1&2。

SELECT AVG(prod_price) AS avg_price
FROM products;

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id=1003;

要注意的是，AVG()函数只能用来确定特定数值列的平均值，列名必须作为参数给出，获得多个列平均值要使用多个函数。该函数忽略值为NULL的行。

• 函数COUNT()用来计数，有两种使用形式。

SELECT COUNT(*) AS num_cust
FROM customers;

使用COUNT(*)对表中行的数目计数，不忽略NULL。

SELECT COUNT(cust_email) AS num_cust
FROM customers;

使用COUNT(column)对特定列中具有值的行计数，不算NULL。

• MAX()函数返回最大值，要求指定列名。

SELECT MAX(prod_price) AS max_price
FROM products;

MAX()也能返回文本列中的最大值，忽略值为NULL的行。

• MIN()函数返回最小值，要求指定列名，和MAX()用法相同。
• SUM()函数返回指定列值得和，忽略值为NULL的行。

SELECT SUM(quntity) AS items_ordered
FROM orderitems
WHERE order_num=20005;

所有的聚集函数都可执行多个列上的计算，如SUM(item_price*quantity) AS total_price。

聚集不同值

想要只包含不同的值，要使用参数DISTINCT。

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id=1003;

使用DISTINCT必须指定列名，故不能用于COUNT(*)。此外，用于MIN()和MAX()也是没有意义的。

组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS min_price, 
       MAX(prod_price) AS max_price.
       AVG(prod_price) AS avg_price
FROM products;

注意在指定列名时，不要使用表中已有的列名。

十三、分组数据

创建分组

SELECT COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

输出为

子句GROUP BY指示MySQL分组数据，然后对每个组进行聚集。需要注意以下几点：

• 如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
• 除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子句中给出
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
  使用GROUP BY vend_id WITH ROLLUP;可以得到每个分组以及每个分组汇总级别的值。

过滤分组

我们引入了HAVING子句，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

上述操作过滤出了两个及以上订单的那些分组。WHERE语句和HAVING语句也可以同时使用。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10;
GROUP BY vend_id
HAVING COUNT(*)>=2;

上述语句过滤出了具有2个（含）以上、价格为10（含）以上的产品的供应商

分组和排序

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

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
ORDER BY ordertotal;

只返回总计订单价格大于50的订单

SELECT子句顺序

十四、使用子查询

子查询过滤

在SELECT语句中，子查询总是从内向外处理

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'));

上述语句列出了订购物品TNT2的所有客户，最里面的子查询返回订单号列表，外面的子查询返回客户ID列表，最外层查询返回所需的数据。

作为计算字段使用子查询

为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询

SELECT cust_name, cust_state, 
       (SELECT COUNT(*)
        FROM orders
        WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

输出

这种涉及到外部查询的子查询叫相关子查询，我们必须使用完全限定的列名。若不限定，则每次子查询的结果都是所有订单数总和，即为5。
我们在建立查询的时候，首先要建立和测试最内层的查询，然后再嵌入。