第三章 聚合与排序
3-1 对表进行聚合查询
学习重点:
- 使用聚合函数对表中的列进行计算合计值或者平均值等汇总操作;
- 通常,聚合函数会对NULL以外的对象进行绘制.但是只有COUNT函数例外,使用COUNT(*)可以查出包含NULL在内的全部数据的行数;
- 使用DISTINCT关键字删除重复值
Ⅰ 聚合函数
- COUNT:计算表中的记录数(行数);
- SUM:计算表中数值列中数据的合计值;
- AVG:计算表中数值列中数据的平均值;
- MAX:求出表中任意列数据的最大值;
- MIN:求出表中任意列数据的最小值.
Ⅱ 计算表中的行数
SELECT COUNT(*)
FROM Product;
Ⅲ 计算NULL之外的数据的行数
SELECT COUNT(purchase_price)
FROM Product;
- 输入(*)时,返回的是包含NULL的行数,即表中所有行数;
- 输入特定列名,返回除NULL之外的行数.
Ⅳ 计算合计值:SUM函数
SELECT SUM(sale_price)
FROM Product;
计算两列的合计值:
SELECT SUM(sale_price),SUM(purchase_price)
FROM Product;
Ⅴ 计算平均值
使用方法和SUM一致,删除NULL再进行计算.
Ⅵ 计算最值
MAX\MIN与SUM\AVG有一点不同,那就是SUM\AVG只对数值型进行计算,MAX\MIN可以应用于任何数据类型.
使用聚合函数删除重复值(关键字DISTINCT)
SELECT COUNT(DISTINCT product_type)
FROM product;
DISTINCT 需要写在聚合函数后,即写在括号里,这样就会先删除重复值,再聚合.
想要计算种类时,可以在COUNT函数的参数中使用DISTINCT.
3-2 对表进行分组
学习重点:
- 使用GROUP BY子句可以像切蛋糕那样将表分割.通过使用聚合函数和GROUP BY子句,可以将表分割后再进行汇总;
- 聚合键中包含NULL时,在结果会以"不确定"行的形式表现出来;
- 使用聚合函数和GROUP BY子句需要注意:
(1) 只能写在SELECT子句之后;
(2)GROUP BY子句不能使用SELECT子句列中的别名;
(3)GROUP BY子句的聚合结果是无序的;
(4)WHERE子句中不能使用聚合函数
ⅠGROUP BY子句
目前为止,我们看到的聚合函数的使用方法,无论是否包含NULL,是否删除重复数据都是针对表中所有数据进行汇总处理.在这一节,我们将表分成几组,然后再进行汇总处理.
GROUP BY子句语法结构:
SELECT <列名1>,<列名2>,.. ...
FROM <表名>
GROUP BY <列名1>,<列名2>,.. ... ;
在GROUP BY子句中指定的列称为聚合键或者分组列;
由于能够决定表的切分方式,所以是非常重要的列;
GROUP BY子句和SELECT 子句一样,可以通过逗号分割指定多列.
目前,子句的书写顺序:
1.SELECT–>2.FROM–>3.WHERE–>3.GROUP BY
Ⅱ 聚合键中包含NULL的情况
聚合键中包含NULL时,在结果中会以"不确定"行(空行)的形式表现出来
Ⅲ 使用WHERE子句时GROUP BY的执行结果
SELECT <列名1>,<列名2>,<列名3>,... ...
FROM<表名>
WHERE
GROUP BY <列名1>,<列名2>,<列名3>,... ...
GROUP BY 和 WHERE 并用时SELECT语句执行顺序:
FROM–>WHERE–>GROUP BY–>SELECT
Ⅳ 与聚合函数和GROUP BY子句有关的常见错误
1 在SELECT子句中书写了多余的列.
在使用COUNT这样的聚合函数时,SELECT子句中的元素有严格的限制.实际上,使用聚合函数时,SELECT子句只能存在以下三种元素.
- 常数
- 聚合函数
- GROUP BY子句中指定的列名(也就是聚合键)
2 在GROUP BY 子句中写了列的别名
在GROUP BY 子句不能使用SELECT子句中定义的别名
3 GROUP BY子句的结果不会有特定排序,结果是随机的,即结果的显示是无序的.
4.在WHERE子句中不能使用聚合函数.
只有SELECT子句和HAVING子句(以及ORDER BY子句)中能使用聚合函数.
3-3 为聚合结果指定条件
学习重点:
1.使用COUNT函数等对象对表中的数据进行汇总操作时,为其指定条件的不是WHERE子句,而是HAVING子句;
2.聚合函数可以在SELECT子句,HAVING子句以及ORDER BY子句中使用
3.HAVING子句要写在GROUP BY之后
4.WHERE子句用来指定数据行的条件,HAVING子句用来指定分组的条件.
Ⅰ HAVING子句
HAVING子句:对集合指定条件就需要使用的子句
SELECT <列名1>,<列名2>,<列名3>... ...
FROM<表名>
GROUP BY<列名1>,<列名2>,<列名3>... ...
HAVING<分组结果对应的条件>
使用HAVING子句时SELECT语句的顺序
SELECT -->FROM–>WHERE–>GROUP BY–>HAVING
Ⅱ HAVING子句的构成要素
HAVING子句能够使用的3种要素:
- 常数
- 聚合函数
- GROUP BY子句中指定的列名(即聚合键)
Ⅲ 相对于HAVING子句,更适合写在WHERE子中的条件
- WHERE子句=指定行所对应的条件
- HAVING子句=指定组所对应的条件
聚合键所对应的条件应写在WHERE当中而不是HAVING
3-4 对查询结果进行排序
学习重点:
- 使用ORDER BY子句对查询结果进行排序;
- 在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序,使用DESC可以进行降序排序
- ORDER BY子句可以指定多个排序键
- 排序键中包含NULL时,会在开头或者末尾进行汇总
- ORDER BY子句可以使用SELECT子句定义的列的别名
- ORDER BY子句可以使用SELECT子句中未出现的列或者聚合函数
- ORDER BY子句不能使用列的编号
ⅠORDER BY子句
ORDER BY子句语法:
SELECT <列名1>,<列名2>,<列名3>... ...
FROM<表名>
ORDER BY<排序基准列1>,<排序基准列2>... ...
子句书写顺序:
1.SELECT–>2.FROM–>3.WHERE–>4.GROUP BY–>5.HAVING–>6.ORDER BY
Ⅱ 指定升序或者降序
降序使用DESC关键字
Ⅲ 指定多个排序键
规则是优先使用左侧的键,如果该列存在相同值的话,再接着参考右侧的键.
Ⅳ NULL顺序
排序键中含有NULL时,会在开头或末尾进行汇总
Ⅴ 在排序键中使用显示用的列名
SELECT product_id AS id,product_name,sale_price AS sp,purchase_price
FROM Product
ORDER BY sp,id;
使用HAVING子句时SELECT语句的顺序
FROM --> WHERE -->GROUP BY -->HAVING -->SELECT–>ORDER BY
Ⅵ ORDER BY子句中可以使用的列
- ORDER BY子句也可以使用存在于表中,但并不包含在SELECT子句之中的列;
- ORDER BY子句可以使用聚合函数
Ⅶ 不要使用列编号
列编号是指SELECT子句中的列按照从左到右的顺序排列时所对应的编号.但并不推荐使用,原因:
- 代码阅读起来困难;
- 该排序功能在将来可能会被删除