对表进行聚合查询
聚合函数
-
通过 SQL 对数据进行某种操作或计算时需要使用函数。
-
常用函数
- COUNT： 计算表中的记录数（行数）
- SUM： 计算表中数值列中数据的合计值
- AVG： 计算表中数值列中数据的平均值
- MAX： 求出表中任意列中数据的最大值
- MIN： 求出表中任意列中数据的最小值
-
用于汇总的函数称为聚合函数或者聚集函数
计算表中数据的行数
- 执行结果包括值为 NULL 的行
-- 计算全部数据的行数 SELECT COUNT(*) FROM Product;
计算NULL之外的数据的行数
- 对于 COUNT 函数来说，参数列不同计 算的结果也会发生变化
- COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据 行数，而COUNT(<列名>)会得到NULL之外的数据行数。
-- 计算NULL之外的数据行数 SELECT COUNT(purchase_price) FROM Product;
计算合计值
- 对于 SUM函数来说，即使包含 NULL，也可以计算出合计值
- 聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL
-- 计算销售单价的合计值 SELECT SUM(sale_price) FROM Product;
计算平均值
- 会事先删除NULL 再进行计算，值的个数减少
-- 计算销售单价和进货单价的平均值 SELECT AVG(sale_price), AVG(purchase_price) FROM Product;
计算最大值和最小值
- SUM/ AVG函数只能对数值类型的列使用
- MAX/MIN 函数原则上可以适用 于任何数据类型的列
-- 计算销售单价的最大值和进货单价的最小值 SELECT MAX(sale_price), MIN(purchase_price) FROM Product;
使用聚合函数删除重复值（关键字DISTINCT）
- 这时 DISTINCT 必须写在括号中。这是因为必须要在计算 行数之前删除 product_type 列中的重复数据
- 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
- 所有的聚合函数都可以使用DISTINCT
-- 计算去除重复数据后的数据行数 SELECT COUNT(DISTINCT product_type) FROM Product; -- 先计算数据行数再删除重复数据的结果 SELECT DISTINCT COUNT(product_type) FROM Product;
对表进行分组
GROUP 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 子句的书写位置也有严格要求，一定要写在 FROM 语句之后
-
子句的书写顺序（暂定）
- SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
聚合键中包含NULL的情况
- 当聚合键中包含 NULL 时，也会将 NULL 作为一组特定的数据
使用WHERE子句时GROUP BY的执行结果
-- 使用WHERE子句和GROUP BY子句进行汇总处理 SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> WHERE GROUP BY <列名1>, <列名2>, <列名3>, ……; -- 同时使用WHERE子句和GROUP BY子句 SELECT purchase_price, COUNT(*) FROM Product WHERE product_type = '衣服' GROUP BY purchase_price;
-
GROUP BY 和WHERE 并用时，SELECT 语句的执行顺序
- FROM → WHERE → GROUP BY → SELECT
与聚合函数和GROUP BY子句有关的常见错误
-
常见错误①：在SELECT子句中书写了多余的列
-
SELECT 子句中只能存在以下三种元素
- 常数
- 聚合函数
- GROUP BY子句中指定的列名（也就是聚合键）
-
常会出现的错误就是把聚合键之外的列名书写在SELECT 子 句之中
-
使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名
-
-- 在SELECT子句中书写聚合键之外的列名会发生错误 SELECT product_name, purchase_price, COUNT(*) FROM Product GROUP BY purchase_price; /* 列名product_name 并没有包含在GROUP BY 子句当中。因此，该列名也不能书写在 SELECT 子句之中 */
-
-
常见错误②：在GROUP BY子句中写了列的别名
- SQL 语句在 DBMS 内部的执行顺序造成的——SELECT 子句在GROUP BY 子句之后执行。 在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并 不知道。
- 在GROUP BY子句中不能使用SELECT子句中定义的别名。
-
-- GROUP BY子句中使用列的别名会引发错误 SELECT product_type AS pt, COUNT(*) FROM Product GROUP BY pt
-
常见错误③：GROUP BY子句的结果能排序吗
- 答案是：“ 随机的。”
- GROUP BY子句结果的显示是无序的。
-
常见错误④：在WHERE子句中使用聚合函数
- 只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
-
-- 在WHERE子句中使用聚合函数会引发错误 SELECT product_type, COUNT(*) FROM Product WHERE COUNT(*) = 2 GROUP BY product_type;
为聚合结果指定条件
HAVING子句
-
SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> GROUP BY <列名1>, <列名2>, <列名3>, …… HAVING <分组结果对应的条件>
-
使用 HAVING 子句时 SELECT 语句的顺序
- SELECT → FROM → WHERE → GROUP BY → HAVING
-
HAVING子句要写在GROUP BY子句之后
-
-- 从按照商品种类进行分组后的结果中，取出“包含的数据行数为2 行”的组 SELECT product_type, COUNT() FROM Product GROUP BY product_type HAVING COUNT() = 2;
HAVING子句的构成要素
-
HAVING 子句中 能够使用的 3种要素如下
- 常数
- 聚合函数
- GROUP BY子句中指定的列名（也就是聚合键）
-
-- HAVING子句的不正确使用方法 SELECT product_type, COUNT(*) FROM Product GROUP BY product_type HAVING product_name = '圆珠笔';
相对于HAVING子句， 更适合写在WHERE子句中的条件
- WHERE 子句 = 指定行所对应的条件
- HAVING 子句 = 指定组所对应的条件
- 聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。
对查询结果进行排序
ORDER BY子句
SELECT <列名1>, <列名2>, <列名3>, …… FROM <表名> ORDER BY <排序基准列1>, <排序基准列2>, …… -- 按照销售单价由低到高（升序）进行排列 SELECT product_id, product_name, sale_price, purchase_price
FROM Product ORDER BY sale_price;
-
ORDER BY 子句中书写的列名称为排序键
-
ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行
-
子句的书写顺序
- SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句
指定升序或降序
- 降序排列时，在列名后面使用 DESC 关键字
- 升序进行排列时，正式的书写方式应该是使用关键字ASC
- ASC 和 DESC 是 ascendent（上升的）和 descendent（下降的）这两个单词的缩写。
- 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。
- 由于 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时，会在开头或末尾进行汇总
在排序键中使用显示用的别名
-
ORDER BY 子句中允许使用别名的
-
使用 HAVING 子句时 SELECT 语句的顺序
- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
ORDER BY子句中可以使用的列
- ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT 子句之中的列
- 可以使用聚合函数
-- SELECT子句中未包含的列也可以在ORDER BY子句中使用
SELECT product_name, sale_price, purchase_price FROM Product ORDER BY product_id; -- product_id 不显示在查询结果中
不要使用列编号
- 列编号是指SELECT 子句中的列按照从左到 右的顺序进行排列时所对应的编号（1, 2, 3, …）
- 第一，代码阅读起来比较难
- 第二，该排序功能将来会被删除
-- 通过列名指定 SELECT product_id, product_name, sale_price, purchase_price
FROM Product ORDER BY sale_price DESC, product_id; -- 通过列编号指定 SELECT product_id, product_name, sale_price, purchase_price
FROM Product ORDER BY 3 DESC, 1;