MySQL必知必会——第十三章分组数据
2022/1/23 2:06:47
本文主要是介绍MySQL必知必会——第十三章分组数据,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
分组数据
本章将介绍如何分组数据,以便能汇总表内容的子集。这涉及两个新SELECT语句子句,分别是GROUP BY和HAVING子句。
数据分组
上章(MySQL必知必会——第十二章汇总数据)我们使用SQL聚集函数来汇总数据。这使我们不用检索所有数据就能对行进行计数、计算和平均数等。
目前为止的计算都在表的所有数据或匹配特定的WHERE子句的数据上进行的。
例如,返回供应商1003提供的产品数目:
mysql> SELECT COUNT(*) AS num_prods -> FROM products -> WHERE vend_id = 1003; +-----------+ | num_prods | +-----------+ | 7 | +-----------+ 1 row in set (0.01 sec)
但如果我们想返回每个供应商提供的产品数目,或返回只提供单项产品的供应商提供的产品,或返回提供10个以上产品的供应商怎么办?。
明显这简单的聚集函数很难办到,我们可以使用分组。分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算。
创建分组
分组是在SELECT语句的GROUP BY子句中建立的。
返回每个供应商提供的产品数目:
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM products -> GROUP BY vend_id; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | +---------+-----------+ 4 rows in set (0.00 sec)
此SELECT语句指定了两个列,vend_id包含产品供应商的ID,num_prods为计算字段。GROUP BY子句指示MySQL按vend_id排序并分组数据。即,COUNT(*)对每个vend_id计算一次。
GROUP BY子句指示MySQL分组数据,然后对每个组而不是整个结果集进行聚集。
一些使用GROUP BY子句的重要规定:
- GROUP BY子句可以包含任意数目的列。这能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总(不能从个别的列取回数据)。
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中年指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中出现。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。
- GROUP BY子句必须出现在WHERE子句后,ORDER BY子句前。
使用ROLLUP 使用WITH ROLLUP关键字,可以得到每个分组以及每个分组汇总级别的值,例如
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM products -> GROUP BY vend_id WITH ROLLUP; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | | NULL | 14 | +---------+-----------+ 5 rows in set (0.00 sec)
过滤分组
除了能用GROUP BY分组数据外,MySQL还允许过滤分组,规定包括哪些分组,排除哪些分组。
谈过滤,我们会想到WHERE子句(MySQL必知必会——第六章过滤数据)。但WHERE过滤指定的行,并非分组,所以我们需要另外的子句,HAVING子句。
HAVING非常类似于WHERE。目前所以WHERE子句都可以用HAVING代替,而唯一的区别是,WHERE过滤行,HAVING过滤分组。
- HAVING支持所有WHERE操作符 我们所学过的有关WHERE的所有技术和选项都适用于HAVING。
过滤拥有两个及两个以上订单的分组:
mysql> SELECT cust_id, COUNT(*) AS orders -> FROM orders -> GROUP BY cust_id -> HAVING COUNT(*) >= 2; +---------+--------+ | cust_id | orders | +---------+--------+ | 10001 | 2 | +---------+--------+ 1 row in set (0.01 sec)
- HAVING和WHERE的差别 WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中,这可能改变计算值,从而影响HAVING子句中过滤的分组。
我们可以同时使用WHERE和HAVING子句。
列出具有2个(含)以上、价格为10(含)以上的产品的供应商:
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM products -> WHERE prod_price >= 10 -> GROUP BY vend_id -> HAVING COUNT(*) >= 2; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1003 | 4 | | 1005 | 2 | +---------+-----------+ 2 rows in set (0.00 sec)
此语句中,WHERE子句过滤所有prod_price至少为10的行,然后按vend_id进行分组数据,再通过HAVING子句过滤计数至少为2的分组。
无WHERE的对比:
mysql> SELECT vend_id, COUNT(*) AS num_prods -> FROM products -> GROUP BY vend_id -> HAVING COUNT(*) >= 2; +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | +---------+-----------+ 4 rows in set (0.00 sec)
分组和排序
GROUP BY和ORDER BY经常完成相同的工作,但它们是非常不一样的。
ORDER BY与GROUP BY的差别:
ORDER BY | GROUP BY |
---|---|
排序产生的结果 | 分组行。但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择列) | 只能使用选择列或表达式列,且必须使用选择表达式 |
不一定需要 | 与聚集函数一起使用列(或表达式),则有必要 |
我们经常发现GROUP BY分组的数据是以分组的顺序输出的,但并不总是这样。我们应该明确提供ORDER BY子句,来进行排序。
- 不要忘记ORDER BY 一般在使用GROUP BY子句时,应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。
检索订单总价大于等于50的订单的订单号和订单总价:
mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal -> FROM orderitems -> GROUP BY order_num -> HAVING SUM(quantity*item_price) >= 50; +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20005 | 149.87 | | 20006 | 55.00 | | 20007 | 1000.00 | | 20008 | 125.00 | +-----------+------------+ 4 rows in set (0.00 sec)
我们在按订单总价排序输出:
mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal -> FROM orderitems -> GROUP BY order_num -> HAVING SUM(quantity*item_price) >= 50 -> ORDER BY ordertotal; +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20006 | 55.00 | | 20008 | 125.00 | | 20005 | 149.87 | | 20007 | 1000.00 | +-----------+------------+ 4 rows in set (0.00 sec)
SELECT子句顺序
回顾当前SELECT语句中子句的顺序,列表顺序:
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表中选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在分组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
这篇关于MySQL必知必会——第十三章分组数据的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南