第三章聚合与排序

3.1.1 聚合函数

用于汇总的函数称为聚合函数. 所谓聚合, 就是将多行汇总为一行. 实际上, 所有的聚合函数都是这样, 输入多行输出一行. 常用的聚合函数

COUNT: 计算表中的记录数 (行数)
SUM: 计算表中数值列中数据的合计值
AVG: 计算表中数值列中数据的平均值
MAX: 求出表中任意列中数据的最大值
MIN: 求出表中任意列中数据的最小值

Product表中的全部数据的行数基本语法:

SELECT COUNT(*)  
    FROM Product;

结果是8

3.1.2 计算NULL之外的数据的行数

COUNT函数的结果根据参数的不同而不同.

COUNT(*)会得到包含NULL的数据行数
COUNT(<列名>)会得到NULL之外的数据行数.

NullTal表

将包含NULL的列作为参数时, COUNT(*) 和COUNT(<列名>)的结果并不相同

SELECT COUNT(*), COUNT(col_1)  
    FROM NullTbl;

该特性是COUNT函数所特有的, 其他函数并不能将星号作为参数(如果使用星号会出错).

3.1.3 计算合计值

SUM函数

聚合函数会将NULL排除在外. 但COUNT(*)例外, 并不会排除NULL.

3.1.4 计算平均值

AVG函数

3.1.5 计算最大值和最小值

MAX, MIN

3.1.6 使用聚合函数删除重复值(关键字DISTINCT)

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

COUNT(DISTINCT product_type) SUM(DISTINCT sale_price)

Part23.2 对表的分组

3.2.1 GROUP BY 子句

基本语法:

SELECT <列名1>, <列名2>, <列名3>, ……  
    FROM <表名>  
  GROUP BY <列名1>, <列名2>, <列名3>, ……;

SELECT product_type, COUNT(*)
 FROM Product
GROUP BY product_type;

结果

在GROUPBY子句中指定的列称为==聚合键或者分组列==. 由于能够决定表的切分方式, 所以是非常重要的列. 当然, GROUP BY子句也和SELECT子句一样, 可以通过逗号分隔指定多列.

此外, GROUP BY子句的书写位置也有严格要求, ==一定要写在FROM语句之后==(如果有WHERE子句的话需要写在WHERE子句之后)

子句的书写顺序==1.SELECT→2.FROM→3.WHERE→4.GROUP BY==

聚合键中包含==NULL==时, 在结果中会以=="不确定"行(空行)==的形式表现出来

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

在使用了GROUP BY子句的SELECT语句中, 也可以正常使用WHERE子句. 子句的排列顺序如前所述,语法结果如下所示:

SELECT <列名1>, <列名2>, <列名3>, ……  
    FROM <表名>  
  WHERE  
  GROUP BY <列名1>, <列名2>, <列名3>, ……;

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

GROUP BY和WHERE并用时SELECT语句的==执行顺序==FROM→WHERE→GROUP BY→SELECT

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

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

使用聚合函数时,SE工ECT子句中只能存在以下三种元素:

==常数==
==聚合函数==
==GROUP BY子句中指定的列名(也就是聚合键)==

这里经常会出现的错误就是==把聚合键之外的列名书写在SE工ECT子句之中==.

如下书写会发生错误.

SELECT product_name, purchase_price, COUNT(*)  
 FROM Product  
GROUP BY purchase_price;

结果:

列名 product_name 并没有包含在 GROUP BY 子句当中. 因此, 该列名也不能书写在 SELECT 子句之中.

MYSQL可以支持以上写法, 但其他DBMS都不支持这样的语法, 不要使用这样的写法.

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

2 常见错误2--在GROUP BY子句种写了列的别名

在 GROUP BY 子句中是不能使用别名的.

在 PostgreSQL 和 MySQL 都不会发生执行错误, 但是这并不是通常的使用方法

ps: 原因就是内部执行顺序, 先执行group by, 再执行select

3 常见错误3--GROUP BY子句的结果

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

如果要排序, 需要在SELECT语句中指定

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

只有==SELECT子句==和==HAVING子句== (以及==ORDER BY子句==) 中能够使用==聚合函数==

Part33.3 为聚合结果指定条件

3.3.1 HAVING子句

==语法:==

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

HAVING子句必须写在GROUP BY子句之后

例如: 使用HAVING子句的情况

未使用HAVING子句的情况

3.3.2 HAVING子句的构成要素

HAVING子句和包含GROUP BY子句时的SELECT子句一样, 能够使用的要素有一定的限制, 限制内容也是完全相同的. HAVING子句中能够使用的3种要素如下所示.

==常数==
==聚合函数==
==GROUP BY子句中指定的列名(即聚合键)==

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

有些条件既可以写在 HAVING 子句当中, 又可以写在 WHERE 子句当中. ==这些条件就是聚合键所对应的条件==

代码清单:

SELECT product_type, COUNT(*)  
 FROM Product  
 GROUP BY product_type  
HAVING product_type = '衣服';

和代码清单:

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

得到的结果是一致的:

product_type | count  
--------------+------  
衣服          | 2

结论建议:==聚合键所对应的条件还是应该书写在 WHERE 子句之中==

聚合键所对应的条件不应该书写在HAVING子句当中, 而应该书写在WHERE子句当中

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

Part43.4 对查询结果进行排序

3.4.1 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 语句的末尾== ORDER BY 子句中书写的列名称为==排序键==

ORDER BY子句书写顺序:==1.SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →5. HAVING 子句 → 6. ORDER BY 子句==

3.4.2 指定升序或降序

升序--在列名后面使用ASC(不写,==默认是升序==)
降序--在列名后面使用==DESC==

例如: 按照销售单价由高到低 (降序) 进行排列

SELECT product_id, product_name, sale_price, purchase_price  
 FROM Product  
 ORDER BY sale_price DESC;

3.4.3 指定多个排序键

ORDER BY 子句中同时指定多个排序键了. 规则是优先使用左侧的键, 如果该列存在相同值的话, 再接着参考右侧的键

例如: 按照销售单价和商品编号的升序进行排序

SELECT product_id, product_name, sale_price, purchase_price  
 FROM Product  
 ORDER BY sale_price, product_id;

3.4.4 NULL的顺序

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

3.4.5 在排序键中使用显示用的别名

在 ==GROUP BY 子句中不能使用SELECT 子句中定义的别名==, 但是在 ==ORDER BY 子句中却是允许使用别名==的.

如:

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
 FROM Product
 ORDER BY sp, id;

执行顺序:==FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY==

记住 SELECT 子句的执行顺序在 GROUP BY 子句之后, ORDER BY 子句之前

因此, 在执行 GROUP BY 子句时, SELECT 语句中定义的别名无法被识别.对于在SELECT 子句之后执行的 ORDER BY 子句来说, 就没有这样的问题了.

3.4.6 ORDER BY子句中可以使用的列

ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT 子句之中的列

例如: SELECT子句中未包含的列也可以在ORDER BY子句中使用

SELECT product_name, sale_price, purchase_price  
 FROM Product  
 ORDER BY product_id;

此外, 还可以使用聚合函数

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

3.4.7 不要使用列编号

在ORDER BY子句中不要使用列编号

Part53.5 易错点小结

3.5.1 关于别名的使用

在SELECT定义的别名, ==ORDER BY子句中可以使用别名==, 但是, GROUP BY子句和HAVING子句中不能使用SELECT子句中定义的别名

3.5.2 子句使用要素的限制

1. HAVING子句和包含GROUP BY 子句时的SELECT子句使用限制一样HAVING子句和包含GROUP BY子句时的SELECT子句一样, 能够使用的要素有一定的限制, 限制内容也是完全相同的. HAVING子句中能够使用的3种要素如下所示:

==常数==
==聚合函数==
==GROUP BY子句中指定的列名(即聚合键)==

2. ORDER BY子句使用要素的限制ORDER BY 子句中也可以使用存在于表中、但并不包含在 SELECT子句之中的列==在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数==

3.5.3 聚合函数在子句的使用

只有在SELECT子句和HAVING子句以及ORDER BY子句中能够使用聚合函数
==WHERE子句中不能使用聚合函数==, GROUP BY也不能

3.5.4 WHERE子句与HAVING子句使用场景的区分

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

3.5.5 书写顺序和执行顺序

书写顺序:==1.SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 →5. HAVING 子句 → 6. ORDER BY 子句==

执行顺序:==FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY==

posted on 2023-09-09 22:46 欢笑一声阅读(39) 评论(0) 收藏举报

刷新页面返回顶部

程序猿技术

第三章聚合与排序

3.1.1 聚合函数

3.1.2 计算NULL之外的数据的行数

3.1.3 计算合计值

3.1.4 计算平均值

3.1.5 计算最大值和最小值

3.1.6 使用聚合函数删除重复值(关键字DISTINCT)

Part23.2 对表的分组

3.2.1 GROUP BY 子句

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

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

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

2 常见错误2--在GROUP BY子句种写了列的别名

3 常见错误3--GROUP BY子句的结果

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

Part33.3 为聚合结果指定条件

3.3.1 HAVING子句

3.3.2 HAVING子句的构成要素

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

Part43.4 对查询结果进行排序

3.4.1 ORDER BY子句

3.4.2 指定升序或降序

3.4.3 指定多个排序键

3.4.4 NULL的顺序

3.4.5 在排序键中使用显示用的别名

3.4.6 ORDER BY子句中可以使用的列

3.4.7 不要使用列编号

Part53.5 易错点小结

3.5.1 关于别名的使用

3.5.2 子句使用要素的限制

3.5.3 聚合函数在子句的使用

3.5.4 WHERE子句与HAVING子句使用场景的区分

3.5.5 书写顺序和执行顺序

导航

公告

程序猿技术

第三章 聚合与排序

3.1.1 聚合函数

3.1.2 计算NULL之外的数据的行数

3.1.3 计算合计值

3.1.4 计算平均值

3.1.5 计算最大值和最小值

3.1.6 使用聚合函数删除重复值(关键字DISTINCT)

Part23.2 对表的分组

3.2.1 GROUP BY 子句

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

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

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

2 常见错误2--在GROUP BY子句种写了列的别名

3 常见错误3--GROUP BY子句的结果

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

Part33.3 为聚合结果指定条件

3.3.1 HAVING子句

3.3.2 HAVING子句的构成要素

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

Part43.4 对查询结果进行排序

3.4.1 ORDER BY子句

3.4.2 指定升序或降序

3.4.3 指定多个排序键

3.4.4 NULL的顺序

3.4.5 在排序键中使用显示用的别名

3.4.6 ORDER BY子句中可以使用的列

3.4.7 不要使用列编号

Part53.5 易错点小结

3.5.1 关于别名的使用

3.5.2 子句使用要素的限制

3.5.3 聚合函数在子句的使用

3.5.4 WHERE子句与HAVING子句使用场景的区分

3.5.5 书写顺序和执行顺序

导航

公告

第三章聚合与排序