欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Chapter 3 - 聚合与排序

程序员文章站 2022-09-14 14:06:18
Part 1. 对表进行聚合查询1. 聚合函数通过SQL对数据进行某种操作或计算时需要使用函数 常用函数 COUNT 计算表中的记录数 SUM 计算表中数据列中数据的合计值 AVG 计算表中数据列中数据的平均数 MAX 求出表中数据列中数据的最大值 MIN 求出表中数据列中数据的最小值 用于汇总的函数称为：聚合函数或者聚集函数（聚合）2. 计算表...

文章目录

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

上一篇： 女生可要注意了 皮肤好不好关键在养脾胃

下一篇： 从一个存储过程说说mysql中日期函数的参数是否必须是日期格式

推荐阅读