SQL基础——聚合与排序

聚合与排序

• ​​前言​​
• ​​思维导图​​
• ​​聚合函数​​

• ​​示例表3-1
• ​​计算表中数据的行数 COUNT函数​​

• ​​示例代码3.1 计算全部数据的行数​​

• ​​执行结果​​

• ​​计算NULL之外的数据的行数​​
• ​​代码示例3.2 计算NULL之外的数据行数​​

• ​​执行结果

• ​​计算合计值SUM​​

• ​​代码示例3.3 计算销售单价的合计值​​

• ​​执行结果​​

• ​​代码示例3.4 计算销售单价和进货单价的合计值​​

• ​​执行结果​​

• ​​计算平均值 AVG​​

• ​​代码示例3.5 计算销售单价的平均值​​

• ​​执行结果​​

• ​​计算最大值和最小值 MAX MIN​​

• ​​代码示例3.6 计算销售单价的最大值和进货单价的最小值​​

• ​​执行结果​​

• ​​代码示例3.7计算登记日期的最大值和最小值​​

• ​​执行结果​​

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

• ​​代码示例3.8 计算去除重复数据后的数据行数​​

• ​​执行结果​​

• ​​代码示例3.8 先计算数据行数再删除重复数据的结果​​

• ​​执行结果​​

• ​​对表进行分组 GROUP BY​​

• ​​GROUP BY子句​​

• ​​语法3.1 使用GROUP BY子句进行汇总​​
• ​​代码示例3.9 按照商品种类统计数据行数​​

• ​​执行结果​​

• ​​聚合键中包含NULL的情况​​

• ​​代码示例3.10 按照进货单价统计数据行数​​

• ​​执行结果​​

• ​​使用WHERE子句时GROUP BY的执行情况​​

• ​​语法3.2 使用WHERE子句和GROUP BY子句进行汇总处理​​
• ​​代码示例3.15 同时使用WHERE子句和GROUP BY子句​​

• ​​执行结果​​

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

• ​​常见错误① ——在SELECT子句中书写了多余的列​​
• ​​常见错误② ——在GROUP BY子句中写了列的别名​​
• ​​常见错误③ —— GROUP BY子句的结果能排序​​
• ​​常见错误④ ——在WHERE子句中使用聚合函数​​

• ​​为聚合结果指定条件 HAVING​​

• ​​语法3.3 HAVING子句​​

• ​​代码示例 3.16 从按照商品种类进行分组的结果中，取出包含的数据行为2行的组​​

• ​​执行结果​​

• ​​HAVING子句的构成要素​​
• ​​相对于HAVING子句，更适合写在WHERE子句中的条件​​

• ​​代码示例3.17 将条件写在HAVING子句中的情况​​

• ​​执行结果​​

• ​​代码示例3.18 将条件写在WHERE子句中的情况​​

• ​​执行结果​​

• ​​对查询结果进行排序​​

• ​​语法3.4 ORDER BY 子句​​
• ​​代码示例3.19 按照销售单价由低到高（升序）进行排序​​

• ​​执行结果​​

• ​​指定升序或降序​​

• ​​代码示例3.20 按照销售单价由高到低（降序）进行排序​​

• ​​执行结果​​

• ​​指定多个排序键​​

• ​​代码示例3.21 按照销售单价和商品编号的升序进行排序​​

• ​​执行结果​​

• ​​NULL的顺序​​
• ​​在排序键中使用显示用的别名​​

• ​​代码示例3.22 ORDER BY子句中使用列的别名​​

• ​​执行结果​​

• ​​ORDER BY子句中可以使用的列​​

• ​​代码示例3.23 SELECT子句中未包含的列也可以在ORDER BY子句中使用​​

• ​​执行结果​​

• ​​如果博主的文章对您有所帮助，可以评论、点赞、收藏，支持一下博主!!!​​

前言

系统学习SQL的笔记，用于记录学习过程。

思维导图

聚合函数

通过SQL对数据进行某种操作或计算是需要使用函数，用于汇总的函数成为聚合函数或聚集函数。通俗理解就是将多行汇总为一行。

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

示例表3-1

计算表中数据的行数 COUNT函数

示例代码3.1 计算全部数据的行数

SELECT
    COUNT(*) 
FROM
    Product

执行结果

计算NULL之外的数据的行数

想要计算表中的全部数据的行数时，可以像SELECT COUNT(*)这样使用星号。如果想要得到purchase_price列中非空行数的话，可以像代码示例3.2那样。

代码示例3.2 计算NULL之外的数据行数

SELECT
    count( purchase_price ) 
FROM
    Product

执行结果

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

计算合计值SUM

代码示例3.3 计算销售单价的合计值

SELECT
    SUM(sale_price)
FROM
    Product

执行结果

代码示例3.4 计算销售单价和进货单价的合计值

SELECT
    SUM( sale_price ),
    SUM( purchase_price ) 
FROM
    Product

执行结果

通过SUM(purchase_price)

将进货单价的合计值也一起计算出来了，但有一点需要大家注意。具体的计算过程如下所示。

由于在“四则运算中如果存在NULL，结果一定是NULL，但是对于所有的聚合函数，如果以列名为参数，那么在计算之前就已经把NULL排除在外了。因此，无论有多少个NULL都会被无视。

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

计算平均值 AVG

代码示例3.5 计算销售单价的平均值

SELECT
    AVG( sale_price ) 
FROM
    Product

执行结果

计算最大值和最小值 MAX MIN

想要计算出多条记录中的最大值或最小值，可以分别使用MAX和MIN函数，它们是英语maximam（最大值）和minimum（最小值）的缩写。

代码示例3.6 计算销售单价的最大值和进货单价的最小值

SELECT
    MAX( sale_price ),
    MIN( purchase_price ) 
FROM
    Product;

执行结果

注意MAX/MIN函数和SUM/AVG函数有一点不同，SUM/AVG函数只能对数值类型的列使用，而MAX/MIN函数原则上可以使用与任何数据类型的列。

例如对日期类型的列regist_date使用MAX/MIN函数进行计算

代码示例3.7计算登记日期的最大值和最小值

SELECT
    MAX( regist_date ),
    MIN( regist_date ) 
FROM
    Product;

执行结果

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

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

在示例表3-1中我们可以看到product_type列和销售单价sale_price列的数据中，存在多行数据相同的情况。
拿商品种类来说，表中总共有3种商品共8行数据，其中衣服2行，办公用品2行，厨房用具4行。如果想要计算出商品种类的个数，怎么做比较好呢？可以通过删除重复数据然后再计算数据行。

代码示例3.8 计算去除重复数据后的数据行数

SELECT
    count( DISTINCT product_type ) 
FROM
    Product

执行结果

注意：这时DISTINCT必须卸载括号中。这时因为必须要在计算行数之前删除product_type列中的重复数据。如果卸载括号外的话，就会先计算数据行数，然后再删除数据，结果就得到了product_type列的所有行数。

代码示例3.8 先计算数据行数再删除重复数据的结果

SELECT DISTINCT
    count( product_type ) 
FROM
    Product

执行结果

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

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

对表进行分组 GROUP BY

使用GROUP BY子句可以像切蛋糕那样将表分割。通过使用聚合函数和GROUP BY子句，可以根据“商品种类”或者“登记日期”等将表分割后再进行汇总。

GROUP BY子句

语法3.1 使用GROUP BY子句进行汇总

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

代码示例3.9 按照商品种类统计数据行数

select  product_type, count(*)
FROM Product
GROUP BY product_type

执行结果

GROUP BY子句就向切蛋糕那样将表进行了分组，分组之后在进行计算行数。在GROUP BY子句中指定的列成为聚合键或分组列。

注意：GROUP BY子句的书写位置也有严格要求，一定要写在FROM语句之后（如果有WHERE子句的话需要写在WHERE子句之后）
SQL子句的顺序不能改变，也不能互相替换。

聚合键中包含NULL的情况

将进货单价purchase_price作为聚合键对表进行切分。

代码示例3.10 按照进货单价统计数据行数

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

执行结果

当聚合键中包含NULL时，也会将NULL作为一组特定的数据。

使用WHERE子句时GROUP BY的执行情况

语法3.2 使用WHERE子句和GROUP BY子句进行汇总处理

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

像这样使用WHERE子句进行汇总处理时，会先根据WHERE子句指定的条件进行过滤，然后再进行汇总处理。

代码示例3.15 同时使用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子句中写了列的别名

GROUP BY子句中使用列的别名会引发错误，原因是SQL语句在DBMS内部执行顺序造成的，由于SELECT子句在GROUP BY子句之后执行，在执行GROUP BY时，并不知道SELECT中的别名。

常见错误③ —— GROUP BY子句的结果能排序

GROUP BY 子句的结果都是无序的

常见错误④ ——在WHERE子句中使用聚合函数

只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。

为聚合结果指定条件 HAVING

使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING
子句。
WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件。

语法3.3 HAVING子句

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

注意：HAVING子句必须写在GROUP BY子句之后，其在DBMS内部的执行顺序也排在GROUP BY子句之后。

代码示例 3.16 从按照商品种类进行分组的结果中，取出包含的数据行为2行的组

SELECT
    product_type,
    COUNT(*) 
FROM
    Product 
GROUP BY
    product_type
HAVING COUNT(*) = 2

执行结果

HAVING子句的构成要素

HAVING子句和包含GROUP BY子句时的SELECT子句一样，能够使用的要素有一定的限制，限制内容也是完全相同的。HAVING子句中能够使用的3中要素。

常数、聚合函数、GROUP BY 子句中制定的列名（聚合键）

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

有些条件既可以写在HAVING子句中，又可以写在WHERE子句中，这些条件就是聚合键所对应的条件。例如：

代码示例3.17 将条件写在HAVING子句中的情况

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

执行结果

代码示例3.18 将条件写在WHERE子句中的情况

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

执行结果

注意：从结果上看，两种书写方式都没有问题，但是WHERE子句和HAVING子句的作用不同，HAVING子句用来指定组的条件，行所对应的条件还是应该写在WHRER子句当中。这样书写出来的SELECT语句不但可以分清两者各自的功能，理解起来也更加容易。
WHERE子句 = 指定行所对应的条件
HAVING子句 = 指定组所对应的条件

注意：WHERE子句和HAVING子句的执行速度
通过WHERE子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但HAVING子句是在排序之后才对数据进行分组的，因此与在WHERE子句中指定条件比起来，需要排序的数据量就会多得多此外，WHERE子句更具速度优势的另一个理由是，可以对WHERE子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。

对查询结果进行排序

使用ORDER BY子句对查询结果进行排序。在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序，使用DESC关键字可以进行降序排序。

通常，从表中选取数据时，如果没有特别指定顺序，最终排列顺序便无从得知。及时同一条SELECT语句，每次执行时排列顺序很磕碜发生变化。可以在SELECT语句末尾添加ORDER BY来明确指定排序。

语法3.4 ORDER BY 子句

SELECT
    <列名 1 >,
    <列名 2 >,
    <列名 3 >,
    …… 
FROM
    <表名> 
ORDER BY
    <排序基准列 1 >,
    <排序基准列 2 >,
    ……

代码示例3.19 按照销售单价由低到高（升序）进行排序

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

执行结果

注意：不论何种情况，ORDER BY子句都需要写在SELECT语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。ORDER BY子句中书写的列名称为排序键。

注意：子句的书写顺序
SELECT子句 → 2. FROM子句 → 3. WHERE子句 → 4. GROUP BY子句 → 5. HAVING子句 → 6. ORDER BY子句

指定升序或降序

ORDER BY 默认升序（ASC），在列名后面使用DESC关键字表示降序

代码示例3.20 按照销售单价由高到低（降序）进行排序

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

执行结果

指定多个排序键

如果想要对该顺序的商品进行更细致的排序的话，就需要再添加一个排序键。

代码示例3.21 按照销售单价和商品编号的升序进行排序

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

执行结果

可以在ORDER BY

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

NULL的顺序

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

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

ORDER BY子句中却是允许使用别名的

代码示例3.22 ORDER BY子句中使用列的别名

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

执行结果

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

ORDER BY子句中可以使用的列

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

代码示例3.23 SELECT子句中未包含的列也可以在ORDER BY子句中使用

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

执行结果

注意：除此之外，在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数

如果博主的文章对您有所帮助，可以评论、点赞、收藏，支持一下博主!!!