乘风原创程序

  • Chapter 3 - 聚合与排序
  • 2020/7/7 10:43:42
  • Part 1. 对表进行聚合查询

    1. 聚合函数

    • 通过SQL对数据进行某种操作或计算时需要使用函数
    常用函数
    COUNT 计算表中的记录数
    SUM 计算表中数据列中数据的合计值
    AVG 计算表中数据列中数据的平均数
    MAX 求出表中数据列中数据的最大值
    MIN 求出表中数据列中数据的最小值
    • 用于汇总的函数称为:聚合函数或者聚集函数(聚合)

    2. 计算表中数据的行数

    # 使用COUNT(*) 计算表中的全部行数
    SELECT COUNT(*) # COUNT(参数)
      FROM Product;
      
    # 输出值为返回值
    +-------------------+
    | COUNT(sale_price) |
    +-------------------+
    |                 8 |
    +-------------------+
    

    3. 计算 NULL之外的数据的行数

    # 将具体的对象作为参数,传给COUNT()
    SELECT COUNT(purchase_price)
      FROM Product;
    
    • 对于COUNT函数来说,参数列不同计算结果也会发生变化
    SELECT COUNT(*), COUNT(purchase_price)
      FROM Product;
     
    # 输出结果
    +----------+-----------------------+
    | COUNT(*) | COUNT(purchase_price) |
    +----------+-----------------------+
    |        8 |                     5 |
    +----------+-----------------------+
    
    • 【总结】
      • COUNT函数的结果根据参数的不同而不同
      • COUNT(*)会得到包含NULL的数据行数
      • COUNT(<列名>)会得到NULL之外的数据行数
      • 其他函数不能将星号作为参数

    4. 计算合计值

    SELECT SUM(sale_price) 
      FROM Product;
      
    +-----------------+
    | SUM(sale_price) |
    +-----------------+
    |           16780 |
    +-----------------+
    
    • 对于SUM函数来说,即使包含NULL,也可以计算出合计值
    • 聚合函数如果以列名为参数,在计算之前已经把NULL排除在外;COUNT(*)例外

    5. 计算平均值

    SELECT AVG(sale_price)
      FROM Product;
      
    +-----------------+
    | AVG(sale_price) |
    +-----------------+
    |       2097.5000 |
    +-----------------+ 
    
    • 计算时除去NULL行,值的个数 = 总行数 - NULL的行数

    6. 计算最大值和最小值

    SELECT MAX(sale_price)
      FROM Product;
      
    +------------------+
    | MAX(sale_price ) |
    +------------------+
    |             6800 |
    +------------------+ 
    
    • 【区别】
    • SUM/AVG函数只能对数值类型的列使用
    • MAX/MIN函数原则上可以适用于任何数据类型的列
    SELECT MAX(product_name)
      FROM Product;
      
    +-------------------+
    | MAX(product_name) |
    +-------------------+
    | 高压锅            |
    +-------------------+
    

    7. 使用聚合函数删除重复值

    # 去除重复数据后的数据行数
    SELECT COUNT(DISTINCT product_type)
      FROM Product;
      
    +------------------------------+
    | COUNT(DISTINCT product_type) |
    +------------------------------+
    |                            3 |
    +------------------------------+
    
    # 先计算数据行数再删除重复数据的结果
    SELECT DISTINCT COUNT(product_type)
      FROM Product;
    
    • 不限于COUNT,所有聚合函数都可以使用DISTINCT

    Part 2. 对表进行分组

    1. GROUP BY子句

    # 语法
    SELECT <列名1>, <列名2>, <列名3>, ...
      FROM <表名>
     GROUP BY <列名1>, <列名2>, ...; # 组合键 / 分组列
    
    • 示例
    SELECT product_type, COUNT(*)
      FROM Product
     GROUP BY product_type;
     
    +--------------+----------+
    | product_type | COUNT(*) |
    +--------------+----------+
    | 衣服         |        2 |
    | 办公用品     |        2 |
    | 厨房用具     |        4 |
    +--------------+----------+
    
    • 子句书写顺序(暂定)
    SELECT -> FROM -> WHERE -> GROUP BY
    

    2. 聚合键中包含NULL的情况

    SELECT purchase_price, COUNT(*)
      FROM Product
     GROUP BY purchase_price;
     
    +----------------+----------+
    | purchase_price | COUNT(*) |
    +----------------+----------+
    |            500 |        1 |
    |            320 |        1 |
    |           2800 |        2 |
    |           5000 |        1 |
    |           NULL |        3 |
    +----------------+----------+
    
    • 聚合键中包含NULL时,在结果中会以"不确定"行的形式表现出来

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

    SELECT purchase_price, COUNT(*)
      FROM Product
     WHERE product_type = '衣服'
     GROUP BY purchase_price;
    +----------------+----------+
    | purchase_price | COUNT(*) |
    +----------------+----------+
    |            500 |        1 |
    |           2800 |        1 |
    +----------------+----------+
    

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

    • 法则1:使用GROUP BY子句时,SELECT子句中不能出现聚合键之外的列名

    • 法则2:在GROUP BY子句中不能使用SELECT子句中定义的别名

    • 法则3:GROUP BY子句结果的显示是无序的

    • 法则4:只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数

    • 【说明】:

      • 想要删除结果中的重复记录,使用DISTINCT
      • 想要计算汇总结果,使用GROUP BY

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

    1. HAVING子句

    • WHERE子句只能指定(行)的条件,而不能用来指定组的条件
    • HAVING子句的语法
    SELECT <列名1>, <列名2>, <列名3>, ...
      FROM <表名>
     GROUP BY <列名1>, <列名2>, <列名3>, ...
    HAVING <分组结果对应的条件>;
    
    • 使用HAVING子句时SELECT语句的顺序
    SELECT -> FROM -> WHERE -> GROUP BY -> HAVING
    
    SELECT product_type, COUNT(*)
      FROM Product
     GROUP BY product_type
    HAVING COUNT(*) = 2;
    
    +--------------+----------+
    | product_type | COUNT(*) |
    +--------------+----------+
    | 衣服         |        2 |
    | 办公用品     |        2 |
    +--------------+----------+
    

    2. HAVING子句的构成要素

    • HAVING子句中能够使用的3中要素
      • 常数
      • 聚合函数
      • GROUP BY子句中指定的列名(即聚合键)

    3. WHERE和HAVING子句

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

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

    1. ORDER BY子句

    SELECT <列名1>, <列名2>, ...
      FROM <表名>
     ORDER BY <排序基准列1>, <排序基准列2>,... # 排序键
    
    • 使用ORDER BY子句时SELECT语句的顺序
    SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
    
    SELECT product_id, product_name, sale_price, purchase_price
      FROM Product
     ORDER BY sale_price;
     
    # 按照 sale_price进行升序排列
    +------------+--------------+------------+----------------+
    | product_id | product_name | sale_price | purchase_price |
    +------------+--------------+------------+----------------+
    | 0008       | 圆珠笔       |        100 |           NULL |
    | 0002       | 打孔器       |        500 |            320 |
    | 0006       | 叉子         |        500 |           NULL |
    | 0007       | 擦彩板       |        880 |           NULL |
    | 0001       | T恤衫        |       1000 |            500 |
    | 0004       | 菜刀         |       3000 |           2800 |
    | 0003       | 运动T恤      |       4000 |           2800 |
    | 0005       | 高压锅       |       6800 |           5000 |
    +------------+--------------+------------+----------------+
    

    2. 指定升序或降序

    # 默认指定为升序,正式书写可用关键字 ASC
    # 在列名后使用 DESC关键字,指定降序排列
    SELECT product_id, product_name, sale_price, purchase_price
      FROM Product
     ORDER BY sale_price DESC;
     
    +------------+--------------+------------+----------------+
    | product_id | product_name | sale_price | purchase_price |
    +------------+--------------+------------+----------------+
    | 0005       | 高压锅       |       6800 |           5000 |
    | 0003       | 运动T恤      |       4000 |           2800 |
    | 0004       | 菜刀         |       3000 |           2800 |
    | 0001       | T恤衫        |       1000 |            500 |
    | 0007       | 擦彩板       |        880 |           NULL |
    | 0002       | 打孔器       |        500 |            320 |
    | 0006       | 叉子         |        500 |           NULL |
    | 0008       | 圆珠笔       |        100 |           NULL |
    +------------+--------------+------------+----------------+
    

    3. 指定多个排序键

    • 可在ORDER BY子句中同时指定多个排序键;规则是优先使用左侧的键
    # 优先按照sale_price进行升序排列
    # 在sale_price相同的情况下,按照product_id排序
    SELECT product_id, product_name, sale_price, purchase_price
      FROM Product
     ORDER BY sale_price, product_id;
     
    +------------+--------------+------------+----------------+
    | product_id | product_name | sale_price | purchase_price |
    +------------+--------------+------------+----------------+
    | 0008       | 圆珠笔       |        100 |           NULL |
    | 0002       | 打孔器       |        500 |            320 |
    | 0006       | 叉子         |        500 |           NULL |
    | 0007       | 擦彩板       |        880 |           NULL |
    | 0001       | T恤衫        |       1000 |            500 |
    | 0004       | 菜刀         |       3000 |           2800 |
    | 0003       | 运动T恤      |       4000 |           2800 |
    | 0005       | 高压锅       |       6800 |           5000 |
    +------------+--------------+------------+----------------+
    

    4. NULL的顺序

    • 不能对NULL和数字进行排序;也不能与字符串和日期比较大小
    • 使用含有NULL的列作为排序键时,NULL会在结果的开头或末尾汇总显示
    SELECT product_id, product_name, sale_price, purchase_price
      FROM Product
     ORDER BY purchase_price;
    

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

    SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
      FROM Product
     ORDER BY sp, id;
     
    +------+--------------+------+----------------+
    | id   | product_name | sp   | purchase_price |
    +------+--------------+------+----------------+
    | 0008 | 圆珠笔       |  100 |           NULL |
    | 0002 | 打孔器       |  500 |            320 |
    | 0006 | 叉子         |  500 |           NULL |
    | 0007 | 擦彩板       |  880 |           NULL |
    | 0001 | T恤衫        | 1000 |            500 |
    | 0004 | 菜刀         | 3000 |           2800 |
    | 0003 | 运动T恤      | 4000 |           2800 |
    | 0005 | 高压锅       | 6800 |           5000 |
    +------+--------------+------+----------------+
    

    6. ORDER BY子句中可以使用的列

    SELECT product_name, sale_price, purchase_price
      FROM Product
     ORDER BY product_id; # 可以使用 SELECT中未包含的列
     
    SELECT product_name, sale_price, purchase_price
      FROM Product
     GROUP BY product_type
     ORDER BY COUNT(*); # 可以使用 聚合函数
    
    • 【总结】在ORDER BY子句中可使用SELECT子句中未使用的列和聚合函数
    • ORDER BY子句中不要使用列编号
    SELECT product_id, SUM(product_name)
    -- 本SELECT语句中存在错误
      FROM Product
     GROUP BY product_type;
     WHERE regist_date > '2009-09-01'
    /* 
    	1. WHERE 应该在 GROUP之前
      2. SELECT中不能出现 非聚合键
     */
     
     SELECT sale_price, purchase_price
       FROM Product
      GROUP BY product_type
     HAVING SUM(sale_price) >= 1.5 * SUM(purchase_price);
    

    本文地址:https://blog.csdn.net/GrayD1419/article/details/107170736