《SQL基础教程》学习笔记Ch3

3聚合与排序

3-1对表进行聚合查询

聚合函数

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

用于汇总的函数称为聚合函数；聚合：将多行汇总为一行

计算表中数据的行数
SELECT COUNT(*)
  FROM Product;
计算NULL之外的数据的行数
SELECT COUNT(purchase_price)
  FROM Product;

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

计算合计值
SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;
3-1.png

SUM函数应用于包含NULL的列时，会无视其中的NULL。
聚合函数会将NULL排除在外，但COUNT(*)例外，不会排除NULL。

计算平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;
3-2.png

purchase_price一列总共有8个数据，其中2个为NULL值，计算平均值时，忽略NULL，求平均值时分母是6而不是8。

计算最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;

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

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

计算值的种类，可以在COUNT函数的参数里使用DISTINCT

--计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
  FROM Product;

3-2对表进行分组

GROUP BY子句

按照商品种类对表进行划分

SELECT product_type, COUNT(*)
  FROM Product
GROUP BY product_type;
3-3.png

GROUP BY子句的书写位置有严格要求，目前已经出现的各子句的暂定顺序如下：
1.SELECT->2.FROM->3.WHERE->4.GROUP BY

聚合键包含NULL的情况
SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;
3-4.png

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

使用WHERE子句时GROUP BY的执行结果
SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
3-5.png

GROUP BY和WHERE并用时SELECT语句的执行顺序：
FROM->WHERE->GROUP BY->SELECT
需要注意的是：SQL书写顺序与实际执行顺序不同

与聚合函数和GROUP BY子句有关的常见错误
  • 1.在SELECT子句中书写的多余的列

在使用聚合函数时，SELECT子句只能出现3种元素：
常数
聚合函数
GROUP BY子句中指定的列名（聚合键）

--发生错误
SELECT product_name, purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

原因：通过purchase_price将表分组以后，结果中的一行数据就代表一组取不同价格的商品。聚合键和商品名不是一对一的。

  • 2.在GROUP BY子句中写了列的别名
--发生错误
SELECT product_type AS pt, COUNT(*)
  FROM product
 GROUP BY pt;

错误原因：SELECT子句在GROUP BY子句之后执行，pt别名还没有被定义

  • 3GROUP BY子句的结果能排序吗
    不能，顺序随机
  • 4在WHERE子句中使用聚合函数
    首先我们按照商品种类统计数据行数：
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;
3-5.png

假设我们想取出总个数为2的商品类别，也就是前两行，能不能用WHERE子句来选择呢？

SELECT product_type, COUNT(*)
  FROM Product
 WHERE COUNT(*) = 2
 GROUP BY product_type;
3-6.png

执行错误，这是由于在SQL中聚合函数（COUNT等）只能在SELECT子句和HAVING子句（以及ORDER BY子句）中存在。

3-3为聚合结果指定条件

HAVING子句

前面所讨论问题的正确解法

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING COUNT(*) = 2;
3-7.png

使用HAVING子句时SELECT语句的顺序（书写顺序）：
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING

SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type;

--按照商品种类分组，条件是“销售单价的平均值大于等于2500日元”
SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
HAVING子句的构成要素

HAVING子句同包含GROUP BY子句时的SELECT子句一样，只能够使用下列3种元素：

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

--错误示例
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_name = '圆珠笔';
3-8.png
相对于HAVING子句，更适合写在WHERE子句中的条件

聚合键所对应的条件，既可以写在HAVING子句中，也可以写在WHERE子句中

--将条件写在HAVING子句里
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_type = '衣服';

--将条件写在WHERE子句里
SELECT product_type, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY product_type;

WHERE子句=指定行所对应的条件
HAVING子句=指定组所对应的条件
作者建议写在WHERE子句中，节省运行时间（WHERE子句先进行筛选）

3-4对查询结果进行排序

ORDER BY子句
--查询结果按照sale_price升序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price;
3-9.png

ORDER BY子句中书写的列名称是排序键
子句的书写顺序：
1.SELECT子句 -> 2.FROM子句 -> 3.WHERE子句 -> 4.GROUP BY 子句 -> 5.HAVING子句 -> 6.ORDER BY子句

指定升序或降序
--按照销售单价由高到低降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price DESC;
指定多个排序键
--按照销售单价和商品编号的升序进行排序
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price, product_id;
3-10.png

需要注意第2、3行，当价格相同时按照商品编号的升序排列

NULL的顺序
--按照进货单价的升序进行排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY purchase_price;
3-11.png

排序键中包含NULL时，NULL值会集中在开头或者结尾

在排序键中使用显示用的别名
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price 
  FROM Product
 ORDER BY sp, id;
3-12.png

之前在GROUP BY子句中不可以使用别名，但是在ORDER BY子句中允许使用别名，这是由于SQL语句在DBMS内部的执行顺序造成的：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
因此，在执行GROUP BY子句时，SELECT语句中定义的别名无法被识别，对于在SELECT子句之后执行的ORDER BY子句来说，就没有这个问题。

ORDER BY子句中可以使用的列
--SELECT子句中未包含的列也可以在ORDER BY子句中使用
SELECT product_name, sale_price, purchase_price
  FROM Product
 ORDER BY product_id;

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
 ORDER BY COUNT(*);
3-13.png
不要使用列编号
--两段代码作用相同
--通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price DESC, product_id;

--通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY 3 DESC, 1;

不推荐方式2

习题3

3.2
--不确定正确性
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type
HAVING SUM(sale_price) > 1.5 * SUM(purchase_price)
3.3
SELECT *
FROM Product
ORDER BY regist_date DESC, sale_price;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,607评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,047评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,496评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,405评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,400评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,479评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,883评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,535评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,743评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,544评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,612评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,309评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,881评论 3赞 306
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,891评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,136评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,783评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,316评论 2赞 342