SQL 聚合与排序

目录

第三章 聚合与排序

3-1 对表进行聚合查询

一、聚合函数

二、计算表中数据的行数

三、计算NULL以外数据的行数

四、计算合计值（SUM）

五、计算平均值（AVG）

六、计算最大值和最小值（MAX MIN）

七、使用聚合函数删除重复值（关键字DISTINCT）

3-2 对表进行分组

一、GROUP BY 子句

二、聚合键中包含NULL的情况

三、使用WHERE子句时GROUP BY的执行结果

四、与聚合函数和GROUP BY子句有关的常见错误

3-3 为聚合结果指定条件

一、HAVING 子句

二、相对于HAVING 子句更适合写在WHERE子句中的条件

3-4 对查询结果进行排序

一、ORDER BY 子句（排序子句）

二、指定升序或降序

三、指定多个排序键

四、排序键中使用显示用别名

五、ORDER BY子句中可以使用的列

第三章 聚合与排序

3-1 对表进行聚合查询

一、聚合函数

1.五个常用的聚合函数：

COUNT:计算表中的记录数（行数）。

SUM:计算表中数值列的数据和值。

AVG:计算表中数值列的平均值。

MAX:计算表中数值列的最大值。

MIN:计算表中数值列的最小值。   

二、计算表中数据的行数

1.计算全部数据的行数

SEELECT COUNT(*)   
  FROM(Shohin);

这里的星号（*）我们在上边已经提过代表的是全部列的意思。

注意：

在对全部列使用COUNT()函数时，这里是不会忽略NULL行的。

三、计算NULL以外数据的行数

当COUNT（）函数，括号中是对象列的时候会跳过NULL不进行计数。

总结来说：

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(列名)会得到NULL之外的数据行数。

四、计算合计值（SUM）

1.语法如下：

SELECT SUM(列名)   
  FROM 表名；

在SUM函数中计算和值时会发生不一样的效果，从前面我们学习算术运算符时知道，任何数和NULL做运算时都会等于NULL。但在这里我们要特别注意，但使用SUM()函数时，列中有NULL值会把NULL值当作0来计算和值。   

这里有一个结论：

聚合函数会将NULL值排除在外。但COUNT()函数例外，并不会排除NULL   

五、计算平均值（AVG）

1.语法如下：

SELECT AVG(列名)
   FROM  表名；

这里的聚合函数AVG也会忽略NULL值来进行计算。

六、计算最大值和最小值（MAX MIN）

1.语法如下：

SELECT MAX(列名)，MIN(列名)
   FROM 表名；

MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只是用数值类型的列。

七、使用聚合函数删除重复值（关键字DISTINCT）

1.代码：计算去除重复数据后的数据行数

SELECT COUNT(DISTINCT 列名)
   FROM 表名；

2.代码：先计算行数再删除重复数据的结果

SELECT DISTINCT COUNT(列名)
   FROM 表名；

想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT.

3.DISTINCT 不仅限于COUNT 函数，所有聚合函数都可以使用。

在聚合函数的参数中使用DISTINCT,可删除重复数据。

3-2 对表进行分组

一、GROUP BY 子句

1.语法：

SELECT 列名1，列名2，列名3，……
   FROM 表名 
GROUP BY 列名1，列名2，列名3，……；

翻译过来就是按照某一列名，来统计行数

在GROUP BY 子句中指定的列称为聚合键或者分组列。

2.书写格式要求

GROUP BY 子句一定要卸载FROM语句之后（如果有WHERE子句的话需要卸载WHERE子句之后）。

SQL子句的顺序不能改变也不能替换。

二、聚合键中包含NULL的情况

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

三、使用WHERE子句时GROUP BY的执行结果

1.使用WHERE子句和GROUP BY子句进行聚合处理

SELECT 列名1，列名2，列名3，…… 
  FROM 表名 
WHERE 
GROUP BY列名1，列名2，列名3，……；

2.在GROUP BY和WHERE子句共同使用时，SELECT语句的执行顺序：

FROM->WHERE->GROUP BY->SELECT

四、与聚合函数和GROUP BY子句有关的常见错误

常见错误（1）---在SELECT子句中书写了多余的列

错误：把聚合键之外的列名书写在SELECT之中。

举个栗子：

SELECT shohin_mei,shiire_tanka,COUNT(*)
 FROM Shohin 
GROUP BY shiire_tanka;

这里列名shohin_mei并没有包含在GROUP BY子句当中因此也不能书写在SELECT子句之中。

使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。

常见错误（2）---在GROUP BY子句中书写了列名

举个栗子：

SELECT shohin_bunrui AS sb,COUNT(*) 
  FROM Shohin 
GROUP BY sb;

这样的写法在大多数的DBMS中是不通用的，所以尽量少使用。

常见错误（3）---GROUP BY子句的结果能排序么？

GROUP BY子句结果的显示是无序的。

常见的错误（4）---WHERE子句中使用聚合函数

举个栗子：

SELECT shohin_bunrui,COUNT(*)
 FROM Shohin 
WHERE 
COUNT(*) = 2 
GROUP BY shohin_bunrui;

实际上，只有SELECT子句和HAVING子句以及ORDER BY子句中能够使用COUNT等聚合函数。

3-3 为聚合结果指定条件

一、HAVING 子句

1.HAVING 子句的语法如下：

SELECT 列名1，列名2，列名3，……
 FROM 表名 
GROUP BY 列名1，列名2，列名3，…… 
HAVING 分组结果对应的条件

HAVING 子句是用来写条件筛选的

HAVING 子句要写在GROUP BY子句之后

二、相对于HAVING 子句更适合写在WHERE子句中的条件

区分：

WHERE 子句 = 指定行所对应的条件

HAVING 子句 = 指定组所对应的条件

聚合键对应的条件不应该书写在HAVING 子句中，而应该书写在WHERE子句中。

3-4 对查询结果进行排序

一、ORDER BY 子句（排序子句）

语法如下：

SELECT 列名1，列名2，列名3，……
 FROM 表名 
ORDER BY 排序基准列1，排序基准列2，……；

默认为从低到高的顺序排列

ORDER BY 子句通常写在SELECT 子句的末尾。

二、指定升序或降序

在列名后面使用DESC关键字

语法如下：

SELECT 列名1，列名2，列名3，……
 FROM 表名 
ORDER BY 排序基准列1，排序基准列2，…… DESC；

未指定ORDER BY子句中排列顺序时会默认为使用升序进行排列。

三、指定多个排序键

1.当同时排序多个键时会优先使用左侧的键，如果存在相同值的话，会直接参考右侧的键。

2.排序键中包含NULL时，会在开头或末尾进行汇总。

四、排序键中使用显示用别名

举个栗子：

SELECT shohin_id AS id,shohin_mei,hanbai_tanka AS ht,shiire_tanka
 FROM Shohin 
ORDER BY ht,id;

在ORDER BY子句中可以使用SELECT子句中定义的别名。

五、ORDER BY子句中可以使用的列

1.在ORDER BY 子句中可以使用SELECT 子句中未使用的列和聚合函数。

2.同时ORDER BY 中同时也可以使用列编号

举个栗子：

SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka
 FROM Shohin 
ORDER BY 3 DESC,1;

并不建议使用列编号来写，一个是阅读起来比较苦难，另外这种排序功能将来会被删除。