MYSQL学习记录（5）

1.聚合函数

1.1SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

• COUNT：计算表中的记录数（行数）
• SUM：计算表中数值列中数据的合计值
• AVG：计算表中数值列中数据的平均值
• MAX：求出表中任意列中数据的最大值
• MIN：求出表中任意列中数据的最小值

练习：

-- 计算全部数据的行数（包含NULL） 
SELECT COUNT(*) FROM product; 
-- 计算NULL以外数据的行数 
SELECT COUNT(purchase_price) FROM product; 
-- 计算销售单价和进货单价的合计值 
SELECT SUM(sale_price), SUM(purchase_price) FROM product; 
-- 计算销售单价和进货单价的平均值 
SELECT AVG(sale_price), AVG(purchase_price) FROM product; 
-- MAX和MIN也可用于非数值型数据 
SELECT MAX(regist_date), MIN(regist_date) FROM product; 

1.2使用聚合函数剔除重复值

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM product;
 
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;

1.3注意事项

• COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
• 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
• MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
• 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
• 在聚合函数的参数中使用DISTINCT，可以删除重复数据。

2.对表分组

2.1GROUP BY语句（分组）

--group by语法
SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type;
--GROUP BY子句中的列称为聚合列或分组键

 -- 不含GROUP BY
SELECT product_type, COUNT(*)
  FROM product

 2.2GROUP BY语句书写位置

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句书写****顺序为：

1**.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理

SELECT purchase_price, COUNT(*)
  FROM product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

 2.3常见错误

在使用聚合函数及GROUP BY子句时，经常出现的错误有：

1. 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

3.为聚合结果指定条件

3.1利用HAVING得到特定分组

HAVING用法类似WHERE ,但是两者针对对象不同，HAVING针对组，WHERE针对行（记录）。 

3.2HAVING特点

HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）。 

-- 数字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;
-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

4.对查询结果进行排序

4.1  ORDER BY

SQL中的执行结果是随机排列的，当需要按照特定顺序排序时，可已使用ORDER BY子句。

ORDER BY语法：

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……
--默认升序排列 即ASC排列
--降序排列为DESC
-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;
-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

4.2 ORDER BY中可使用别名

SQL在使用 HAVING 子句时 SELECT 语句的执行****顺序为：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

其中SELECT的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在ORDER BY中使用别名时，已经知道了SELECT设置的别名存在，但是在GROUP BY中使用别名时还不知道别名的存在，所以在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名****。

/*练习题5
请指出下述SELECT语句中所有的语法错误。*/
SELECT product_id, SUM（product_name）
--本SELECT语句中存在错误。
  FROM product 
 GROUP BY product_type 
 WHERE regist_date > '2009-09-01';

--错误1：聚合键不对， GROUP BY执行顺序在SELECT之前，product_type不存在于select语句中
--错误2：书写顺序不对，WHERE子句应当在GROUP BY之前

练习题6

请编写一条SELECT语句，求出销售单价（sale_price列）合计值大于进货单价（purchase_price列）合计值1.5倍的商品种类。执行结果如下所示。

 select product_type, sum(sale_price), sum(purchase_price)
 from product
WHERE (select sum(sale_price)>sum(purchase_price)*1.5);
--与给出的结果不相同

 应该是按照日期 regist_date降序排列的

ORDER BY regist_date DESC;