会见和排序,SQL基础教程

SQL 基础知识梳理(三) - 聚合和排序

【博主】反骨仔    【原文】

 

图片 1

 

3集合与排序

  这是《SQL 基础知识梳理(二) - 查询基础》的下篇。

 

3-1对表展开联谊查询

目录

  • 对表实行联谊查询
  • 对表进行分组
  • 为汇集结果钦点条件
  • 对查询结果开展排序

 

聚合函数

COUNT:总括表中的记录数(行数)
SUM:总计表中数值列中数量的合计数
AVG:总结表中数值列中数量的平均值
MAX:求出表中大肆列中数量的最大值
MIN:求出表中任性列中数据的最小值

用以汇总的函数称为聚合函数;聚合:将多行汇总为一行

一、对表举办联谊查询 

  1.聚合函数

    (1)5 个常用函数:

      ①COUNT:总计表中的记录(行)数。

      ②SUM:计算表中数值列的多寡合计值。

      ③AVG:计算表中数值列的数目平均值。

      ④MAX:求出表中放肆列中数量的最大值。

      ⑤MIN:求出表中肆意列中数量的小小值。

 

    (2)聚合:将多行汇总成一行。

图片 2

图1-1 Shohin 表

 

  2.总括表中数量的行数 

--示例
SELECT COUNT(*)  -- *:参数,这里代表全部列
FROM dbo.Shohin;

图片 3

 

  3.妄图 NULL 以外数据的行数

  将 COUNT(*) 的参数改成钦赐对象的列,就能够收获该列的非 NULL 行数。

SELECT COUNT(shiire_tanka)
FROM dbo.Shohin;

图片 4

只总计非 NULL 的行

  【备注】除了 COUNT 函数,其余函数不能够将星号作为参数。

  【备注】COUNT 函数的结果依附参数的例外而差别。COUNT(*) 会获得包括 NULL 的多少行数,而 COUNT(<列名>) 会获得 NULL 之外的多寡行数。

 

  4.总括合计值

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;

图片 5

  【备注】全体的聚合函数,若是以列名字为参数,会一笑置之 NULL 值所在的行。

 

SELECT MAX(torokubi),  --torokubi 为日期
    MIN(torokubi)
FROM dbo.Shohin

图片 6

  【备注】MAX/MIN 函数差相当的少适用于具备数据类型的列。SUM/AVG 函数只适用于数值类型的列。

 

  5.行使聚合函数删除重复值(关键字 DISTINCT)

--示例1:计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT shohin_bunrui)
FROM dbo.Shohin;

--示例2:先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(shohin_bunrui)
FROM dbo.Shohin;

图片 7

  【备注】在聚合函数的参数中央银行使 DISTINCT(示例1),能够去除重复数据。DISTINCT 不止限于 COUNT 函数,全部的聚合函数都能够选拔。

 

计算表中数量的行数
SELECT COUNT(*)
  FROM Product;

二、对表实行分组

  1.GROUP BY 子句

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...;

--示例
SELECT shohin_bunrui AS '商品种类',
    COUNT(*) AS '数量'
FROM dbo.Shohin
GROUP BY shohin_bunrui;

图片 8

  【备注】GROUP BY 子句中钦定的列称为“聚合键”或“分组列”。

  【子句的书写顺序(暂定)】SELECT --> FROM --> WHERE --> GROUP BY

 

  2.聚合键中隐含 NULL 的事态

SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
GROUP BY shiire_tanka;

图片 9

  【备注】聚合键中带有 NULL 时,在结果中也会以 NULL 行的花样表现出来。

 

  3.WHERE 对 GROUP BY 实行结果的熏陶

--语法
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--WHERE <表达式>
--GROUP BY <列名1>, <列名2>, ...

SELECT shiire_tanka, COUNT(*)
FROM dbo.Shohin
WHERE shohin_bunrui = '衣服'
GROUP BY shiire_tanka

图片 10

  这里是先依据 WHERE 子句钦点的条件举办过滤,然后再开展联谊管理。

  【试行顺序】FROM --> WHERE --> GROUP BY --> SELECT。这里是实践各类,跟以前的书写顺序是不平等的。

 

  4.与聚合函数和 GROUP BY 子句关于的普及错误

  (1)易错:在 SELECT 子句中书写了剩余的列

    SELECT 子句只好存在以下二种因素:

      ①常数

      ②聚合函数

      ③GROUP BY 子句中钦命的列名(即聚合键)

图片 11

易错点1

  【计算】使用 GROUP BY 子句时,SELECT 子句不能够出现聚合键之外的列名。

  (2)易错:在 GROUP BY 子句中写了列的别称   

图片 12

 

易错点2

  回想此前说的试行各类,SELECT 子句是在 GROUP BY 子句之后试行。所以进行到 GROUP BY 子句时无能为力分辨别称。

  【总括】GROUP BY 子句不能够应用 SELECT 子句中定义的外号。

 

  (3)易错:GROUP BY 子句的结果能排序吗?

  【解答】它是任性的。假设想排序,请使用 OLX570DE凯雷德 BY 子句。

  【总计】GROUP BY 子句结果的展现是严节的。

 

  (4)易错:在 WHERE 子句中选拔聚合函数

图片 13

易错点3

  【计算】唯有 SELECT 子句和 HAVING 子句(以至 OCR-VDE揽胜 BY 子句)中可以看到选取聚合函数。

  

计量NULL之外的数指标行数
SELECT COUNT(purchase_price)
  FROM Product;

COUNT函数的结果会依附参数的不等而各异:
COUNT(*) 会获得包括NULL的数目行数;COUNT(<列名>)会取得NULL之外的数码行数。

三、为汇集结果钦定条件

  1.HAVING 子句

  WHERE 子句智能钦定记录(行)的口径,而不能够用来钦赐组的口径。

  【备注】HAVING 是 HAVE(具备)的现行反革命分词。

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--GROUP BY <列名1>, <列名2>, ...
--HAVING <分组结果对应的条件>

  【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING

SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
HAVING COUNT(*) = 2

图片 14

从通过商品连串实行联谊分组后的结果中,抽出“富含数据的行数为 2 行”的数据

  2.HAVING 子句的咬合要素

    (1)3 要素:

      ①常数

      ②聚合函数

      ③GROUP BY 子句中钦定的列名(即聚合键)

图片 15

易错用法

  3.HAVING 与 WHERE

  某些条件得以写在 HAVING 子句中,又能够写在 WHERE 子句中。这个原则正是聚合键所对应的口径。

  图片 16

结果一律

  【建议】纵然结果一律,聚合键对应的标准应该写在 WHERE 子句中,不是 HAVING 子句中。

  【理由】①WHERE 子句的施行进程比 HAVING 快。

      ②意义:WHERE 子句 = 钦赐行所对应的口径,HAVING 子句 = 钦定组所对应的尺度。

 

计量合计值
SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;

图片 17

3-1.png

SUM函数应用于含有NULL的列时,会一笑置之个中的NULL。
聚合函数会将NULL排除在外,但COUNT(*)例外,不会免去NULL。

四、对查询结果开展排序

  1.ORDER BY 子句

--语法:
--SELECT <列名1>, <列名2>, ...
--FROM <表名>
--ORDER BY <排序基准列1>, <排序基准列2>, ...

SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka;    --升序排列

图片 18

发售单价由低到高(升序)

  排序键:O冠道DE帕杰罗 BY 子句中书写的列名。

  【书写顺序】SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> O猎豹CS6DEHighlander BY

 

   2.升序(ASC)和降序(DESC):

SELECT shohin_id, hanbai_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka DESC;    --降序排列

图片 19

  【备注】O揽胜极光DE库罗德 BY 子句中排列顺序时会私下认可使用升序(ASC)进行排列。

 

  3.点名两个排序键

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka
FROM dbo.Shohin
ORDER BY hanbai_tanka, shohin_id;

图片 20

  4.NULL 值的逐个:排序键中包涵 NULL 时,会在起头或最终进行汇总。

 

  5.在排序键中选拔 SELECT 子句中的小名

SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht
FROM dbo.Shohin
ORDER BY ht, id;

图片 21

 

  【试行顺序】FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> OHighlanderDE揽胜极光 BY

  【备注】OEvoqueDEEvoque BY 子句能够接纳 SELECT 子句中定义的别称,GROUP BY 子句无法利用外号。

 

  6.OEscortDESportage BY 子句中动用聚合函数

SELECT shohin_bunrui, COUNT(*)
FROM dbo.Shohin
GROUP BY shohin_bunrui
ORDER BY COUNT(*);

图片 22

  7.不建议利用列的号子举行排序,即便能够

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;  --这里使用列的编号,由于阅读不便,不推荐使用

图片 23

结果是一样的

  【备注】在 O奔驰M级DEEscort BY 子句中不用使用列的编号。

 

计量平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

图片 24

3-2.png

purchase_price一列总共有8个数据,在那之中2个为NULL值,总结平均值时,忽视NULL,求平均值时分母是6并非8。

传送门

  《SQL 基础知识梳理(一) - 数据库与 SQL》

  《SQL 基础知识梳理(二) - 查询基础》

 

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

MAX/MIN函数大约适用于具备数据类型的列;SUM/AVG函数只适用于数值类型的列。

备注

  这里运用 MS SQL Server 进行验证,不保证全数的 DBMS 推行结果正确。

 


【参考】《SQL ゼロからはじめるデータベース操作》

 

使用聚合函数删除重复值(关键字DISTINCT)

总计值的花色,可以在COUNT函数的参数里采用DISTINCT

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

3-2对表举办分组

GROUP BY子句

坚守商品种类对表进行划分

SELECT product_type, COUNT(*)
  FROM Product
GROUP BY product_type;

图片 25

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;

图片 26

3-4.png

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

运用WHERE子句时GROUP BY的实施结果
SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

图片 27

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;

图片 28

3-5.png

要是大家想抽出总个数为2的货物类别,也便是前两行,能或不可能用WHERE子句来采纳呢?

SELECT product_type, COUNT(*)
  FROM Product
 WHERE COUNT(*) = 2
 GROUP BY product_type;

图片 29

3-6.png

实施错误,那是由于在SQL中聚合函数(COUNT等)只可以在SELECT子句和HAVING子句(以致O大切诺基DER BY子句)中设有。

3-3为汇聚结果钦命条件

HAVING子句

眼前所争论难题的不错解法

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING COUNT(*) = 2;

图片 30

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 = '圆珠笔';

图片 31

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;

图片 32

3-9.png

OSportageDEEscort 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;

图片 33

3-10.png

急需留意第2、3行,当价格一样一时候依照商品编号的升序排列

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

图片 34

3-11.png

排序键中带有NULL时,NULL值汇集焦在起来或然结尾

在排序键中运用突显用的别称
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price 
  FROM Product
 ORDER BY sp, id;

图片 35

3-12.png

事先在GROUP BY子句中不可能使用外号,然则在O安德拉DER BY子句中允许利用外号,这是由于SQL语句在DBMS内部的实行各种形成的:
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
故此,在执行GROUP BY子句时,SELECT语句中定义的外号不能够被识别,对于在SELECT子句之后施行的O福特ExplorerDER BY子句来讲,就从未这么些标题。

OPAJERODE奥迪Q7 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(*);

图片 36

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;

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:会见和排序,SQL基础教程

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。