序
这是《SQL 基础知识梳理(二) - 查询基础》的下篇。
目录
- 对表进行聚合查询
- 对表进行分组
- 为聚合结果指定条件
- 对查询结果进行排序
一、对表进行聚合查询
1.聚合函数
(1)5 个常用函数:
①COUNT:计算表中的记录(行)数。
②SUM:计算表中数值列的数据合计值。
③AVG:计算表中数值列的数据平均值。
④MAX:求出表中任意列中数据的最大值。
⑤MIN:求出表中任意列中数据的最小值。
(2)聚合:将多行汇总成一行。
图1-1 Shohin 表
2.计算表中数据的行数
<code>--示例
SELECT COUNT(*) -- *:参数,这里代表全部列
FROM dbo.Shohin;/<code>
3.计算 NULL 以外数据的行数
将 COUNT(*) 的参数改成指定对象的列,就可以得到该列的非 NULL 行数。
<code>SELECT COUNT(shiire_tanka)
FROM dbo.Shohin;/<code>
只计算非 NULL 的行
【备注】除了 COUNT 函数,其它函数不能将星号作为参数。
【备注】COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数,而 COUNT() 会得到 NULL 之外的数据行数。
4.计算合计值
<code>SELECT SUM(hanbai_tanka) AS sum_hanbai_tanka, --总和
AVG(hanbai_tanka) AS avg_hanbai_tanka, --平均
MAX(hanbai_tanka) AS max_hanbai_tanka, --最大
MIN(hanbai_tanka) AS min_hanbai_tanka --最小
FROM dbo.Shohin;/<code>
【备注】所有的聚合函数,如果以列名为参数,会无视 NULL 值所在的行。
<code>SELECT MAX(torokubi), --torokubi 为日期
MIN(torokubi)
FROM dbo.Shohin/<code>
【备注】MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。
5.使用聚合函数删除重复值(关键字 DISTINCT)
<code>--示例1:计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT shohin_bunrui)
FROM dbo.Shohin;
--示例2:先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(shohin_bunrui)
FROM dbo.Shohin;/<code>
【备注】在聚合函数的参数中使用 DISTINCT(示例1),可以删除重复数据。DISTINCT 不仅限于 COUNT 函数,所有的聚合函数都可以使用。
二、对表进行分组
1.GROUP BY 子句
<code>--语法:
--SELECT , , ...
--FROM
--GROUP BY , , ...;/<code>
<code>--示例
SELECT shohin_bunrui AS '商品种类',
COUNT(*) AS '数量'
FROM dbo.Shohin
GROUP BY shohin_bunrui;/<code>
【备注】GROUP BY 子句中指定的列称为“聚合键”或“分组列”。
【子句的书写顺序(暂定)】SELECT --> FROM --> WHERE --> GROUP BY
2.聚合键中包含 NULL 的情况
<code>SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
GROUP BY shiire_tanka;/<code>
【备注】聚合键中包含 NULL 时,在结果中也会以 NULL 行的形式表现出来。
3.WHERE 对 GROUP BY 执行结果的影响
<code>--语法
--SELECT , , ...
--FROM
--WHERE
--GROUP BY , , .../<code>
<code>SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka/<code>
这里是先根据 WHERE 子句指定的条件进行过滤,然后再进行聚合处理。
【执行顺序】FROM --> WHERE --> GROUP BY --> SELECT。这里是执行顺序,跟之前的书写顺序是不一样的。
4.与聚合函数和 GROUP BY 子句有关的常见错误
(1)易错:在 SELECT 子句中书写了多余的列
SELECT 子句只能存在以下三种元素:
①常数
②聚合函数
③GROUP BY 子句中指定的列名(即聚合键)
易错点1
【总结】使用 GROUP BY 子句时,SELECT 子句不能出现聚合键之外的列名。
(2)易错:在 GROUP BY 子句中写了列的别名
易错点2
回顾之前说的执行顺序,SELECT 子句是在 GROUP BY 子句之后执行。所以执行到 GROUP BY 子句时无法识别别名。
【总结】GROUP BY 子句不能使用 SELECT 子句中定义的别名。
(3)易错:GROUP BY 子句的结果能排序吗?
【解答】它是随机的。如果想排序,请使用 ORDER BY 子句。
【总结】GROUP BY 子句结果的显示是无序的。
(4)易错:在 WHERE 子句中使用聚合函数
易错点3
【总结】只有 SELECT 子句和 HAVING 子句(以及 ORDER BY 子句)中能够使用聚合函数。
三、为聚合结果指定条件
1.HAVING 子句
WHERE 子句智能指定记录(行)的条件,而不能用来指定组的条件。
【备注】HAVING 是 HAVE(拥有)的现在分词。
<code>--语法:
--SELECT , , ...
--FROM
--GROUP BY , , ...
--HAVING /<code>
【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING
<code>SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2/<code>
从通过商品种类进行聚合分组后的结果中,取出“包含数据的行数为 2 行”的数据
2.HAVING 子句的构成要素
(1)3 要素:
①常数
②聚合函数
③GROUP BY 子句中指定的列名(即聚合键)
易错用法
3.HAVING 与 WHERE
有些条件可以写在 HAVING 子句中,又可以写在 WHERE 子句中。这些条件就是聚合键所对应的条件。
结果一样
【建议】虽然结果一样,聚合键对应的条件应该写在 WHERE 子句中,不是 HAVING 子句中。
【理由】①WHERE 子句的执行速度比 HAVING 快。
②意义:WHERE 子句 = 指定行所对应的条件,HAVING 子句 = 指定组所对应的条件。
四、对查询结果进行排序
1.ORDER BY 子句
<code>--语法:
--SELECT , , ...
--FROM
--ORDER BY , , .../<code>
<code>SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka; --升序排列/<code>
销售单价由低到高(升序)
排序键:ORDER BY 子句中书写的列名。
【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY
2.升序(ASC)和降序(DESC):
<code>SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC; --降序排列/<code>
【备注】ORDER BY 子句中排列顺序时会默认使用升序(ASC)进行排列。
3.指定多个排序键
<code>SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka, shohin_id;/<code>
4.NULL 值的顺序:排序键中包含 NULL 时,会在开头或末尾进行汇总。
5.在排序键中使用 SELECT 子句中的别名
<code>SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht
FROM dbo.Shohin
ORDER BY ht, id;/<code>
【执行顺序】FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY
【备注】ORDER BY 子句可以使用 SELECT 子句中定义的别名,GROUP BY 子句不能使用别名。
6.ORDER BY 子句中使用聚合函数
<code>SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
ORDER BY COUNT(*);/<code>
7.不建议使用列的编号进行排序,虽然可以
<code>SELECT shohin_id ,
shohin_mei ,
shohin_bunrui ,
hanbai_tanka ,
shiire_tanka ,
torokubi
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC, shohin_id;
SELECT shohin_id ,
shohin_mei ,
shohin_bunrui ,
hanbai_tanka ,
shiire_tanka ,
torokubi
FROM dbo.Shohin
ORDER BY 4 DESC, 1; --这里使用列的编号,由于阅读不便,不推荐使用/<code>
结果是一样的
【备注】在 ORDER BY 子句中不要使用列的编号。
传送门
《SQL 基础知识梳理(一) - 数据库与 SQL》
《SQL 基础知识梳理(二) - 查询基础》
备注
这里采用 MS SQL Server 进行验证,不保证所有的 DBMS 执行结果正确。
閱讀更多 帥氣十足的陳杰 的文章