SQL基础教程第三章笔记-白红宇

SQL基础教程第三章笔记

阅读量：4983 次

发布时间：2019-06-12

本文共 7003 字，大约阅读时间需要 23 分钟。

第三章聚合和排序

3.1 队标进行聚合查询

3.1.1 聚合函数

通过SQL对数据进行某种操作或计算时需要使用函数，下面是5个常用的函数。

COUNT:计算表中的记录数（行数）。

SUM：计算表中数值列的数据合计值。

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

MAX:求出表中任意列中数据的最大值。

MIN:求出表中任意列中数据的最小值。

用于合计的函数称为聚合函数或者集合函数。聚合就是指将多行汇总成一行。所有的聚合函数都是输入多行输出一行。

3.1.2 计算表中数据的行数

使用COUNT函数时，输入表的列就能输出数据行数。

代码1.计算全部数据行数

SELECT COUNT(*)  FROM Shohin;

此处的输入值称为参数或者parameter，输出值称为返回值。

输出结果：

3.1.3 计算NULL以外数据的行数

代码2.如果想得到shiire_tanka列中非空行数，可以进行如下代码：

SELECT COUNT(shiire_tanka)   FROM Shohin;

输出结果：

代码3.将包含NULL的列（即整个一列都是NULL）作为参数时，COUNT(*)和COUNT(<列名>)的结果并不相同

SELECT COUNT(*),COUNT(col_1)    FROM NullTb1;

其他函数不能将*作为参数。

输出结果（需要先创建一个NullTb1表，然后输入NULL）：

3.1.4 计算合计值

代码4.计算销售单价的合计值

SELECT SUM(hanbai_tanka)   FROM Shohin;

输出结果：

代码5.计算销售单价和进货单价的合计值

SELECT SUM(hanbai_tanka),SUM(shiire_tanka)    FROM Shohin;

输出结果：

对于sum函数来说，即使包括NULL也可以计算出合计值。

所有的聚合函数，如果以列名为参数，那么在计算之前就已经将NULL排除在外了。但COUNT（*）除外，并不会排除NULL。

3.1.5 计算平均值

代码6.计算销售单价的平均值

SELECT AVG(hanbai_tanka),AVG(shiire_tanka)    FROM Shohin;

输出结果：

在不含NULL值时，得到的平均值的计算公式就是值的合计/值的个数；在含有NULL时，会事先剔除NULL的数据。

3.1.6 计算最大值和最小值

使用的函数是MAX和MIN

代码7.计算销售单价的最大值和进货单价的最小值

SELECT MAX(hanbai_tanka),MIN(shiire_tanka)    FROM Shohin;

输出结果：

MAX/MIN函数与SUM/AVG函数的不同是SUM/AVG函数只能对数值类型使用，而MAX/MIN函数原则上可以适用于任何数据类型。

代码8.计算登记日期的最大值和最小值

SELECT MAX(torokubi)，MIN(torokubi)    FROM Shohin;

输出结果：

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

代码9.计算去除重复数据后的数据行数

SELECT COUNT(DISTINCT shohin_bunrui)     FROM Shohin;

输出结果：

代码10.DISTINCT必须写在括号中；写在括号外面的含义是先计算出数据的行数，再删除重复的数据，结果得到了shohin_bunrui的所有行数，如下面的代码

SELECT DISTINCT COUNT(shohin_bunrui)    FROM Shohin;

输出结果：

要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。

DISTINCT不仅局限于COUNT函数，所有的聚合函数都可以使用

代码11.使不使用DISTINCT时的动作差异（SUM函数）

SELECT SUM(hanbai_tanka),SUM(DISTINCT hanbai_tanka)    FROM Shohin;

输出结果；

得到的输出结果不一样

在聚合函数的参数中使使用DISTINCT，可以删除重复数据。

3.2 对表进行分组

3.2.1 GROUP BY子句

代码12.GROUP BY子句的语法结构

SELECT 
     <列名1>
      ,
      <列名2>
       ,
       <列名3>
        ,.......   FROM 
        <表名>
          GROUP BY 
         <列名1>
          ,
          <列名2>
           ,
           <列名3>
            ,......;

代码13.按照商品种类统计数据行数

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

输出结果：

在GROUP BY子句中指定的列称为聚合键或者分组列

SQL子句的书写顺序：SELECT-> FROM -> WHERE -> GROUP BY ，这个顺序不能互相替换或改变。

3.2.2 聚合键中包含NULL的情况

代码14.按照进货单价统计数据行数

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

输出结果：

　当聚合键包含NULL时，NULL也会作为特定的一组，这个可以理解为不确定。

3.2.3 使用WHERE子句时GROUP BY 的执行结果

代码15.使用WHERE子句和GROUP BY子句进行聚合处理的语法

SELECT 
     <列名1>
      ,
      <列名2>
       ,
       <列名3>
        ,......   FROM 
        <表名>
          WHERE  GROUP BY 
         <列名1>
          ,
          <列名2>
           ,
           <列名3>
            ,......;

使用WHERE子句进行聚合处理时，会先根据WHERE子句指定条件进行过滤，然后再进行聚合处理。

代码16.同时使用WHERE子句和GROUP BY子句

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

输出结果：

GROUP BY和WHERE并用时，SELECT语句的执行顺序：

FROM -> WHERE -> GROUP BY -> SELECT

3.2.4 与聚合函数和GROUP BY子句有关的常见错误

错误1：在SELECT子句中书写了多余的列

使用聚合函数时，SELECT子句中只能存在一下三种元素：常数、聚合函数、GROUP BY子句中指定的列名（也就是聚合键）

这里常出现的错误是把聚合键之外的列名书写在SELECT子句之中

代码17.在SELECT子句中书写聚合键之外的列名会发生错误

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

输出结果：

列名shohin_mei并没有包含在GROUP BY子句之中，因此，该列名也不能书写在SELECT子句之中。

错误2：在GROUP BY子句中谢啦列的别名

代码18.GROUP BY子句中使用列的别名会引发错误

SELECT shohin_bunrui AS sb, COUNT(*)   FROM ShohinGROUP BY sb;

输出结果：

（PostgreSQL不会出现错误，但是这样的写法在其他DBMS并不通用。）

错误3：GROUP BY子句的结果的顺序是无序的

错误4：在WHERE子句中使用聚合函数

代码19.按照商品种类统计数据行数

SELECT shohin_bunrui,COUNT(*)   FROM ShohinGROUP BY shohin_bunrui;

输出结果：

代码20.在WHERE子句中使用聚合函数会引发错误

SELECT shohin_bunrui,COUNT(*)   FROM Shohin WHERE COUNT(*)=2 GROUP BY shohin_bunrui;

输出结果：

3.3 为聚合结果指定条件

3.3.1 HAVING子句

对集合指定条件需要用到HAVING子句。

代码21.对集合指定条件使用HAVING子句，其语法是：

SELECT 
     <列名1>
      ,
      <列名2>
       ,
       <列名3>
        ,......   FROM 
        <表名>
          WHERE  GROUP BY 
         <列名1>
          ,
          <列名2>
           ,
           <列名3>
            ,......HAVING 
            <分组结果对应的条件>
             ;

使用HAVING子句时SELECT语句的顺序：

SELECT -> FROM -> GROUP BY -> HAVING

HAVING子句要写在GROUP BY子句之后。

代码22.从通过商品种类进行聚合分组后的结果中，取出‘包含数据的行数为2行’的组

SELECT shohin_bunrui,COUNT(*)  FROM Shohin GROUP BY shohin_bunruiHAVING COUNT(*)=2;

输出结果：

代码23.使用HAVING子句设定条件的情况

SELECT shohin_bunrui,AVG(hanbai_kanta)    FROM Shohin GROUP BY shohin_bunruiHAVING AVG(hanbai_tanka) >=2500;

输出结果：

3.3.2 HAVING子句的构成要素

HAVING子句有3种要素构成：常数，聚合函数和GROUP BY子句中指定的列名（即聚合键）。如HAVING COUNT(*)=2这样的条件中，COUNT(*)是聚合函数，2是常数

代码24.HAVING子句的不正确使用方法

SELECT shohin_bunrui,COUNT(*)   FROM Shohin GROUP BY shohin_bunruiHAVING shohin_mei='圆珠笔';

输出结果：

shohin_mei列并不包含在GROUP BY子句之中，因此不可以写在HAVING子句之中。

3.3.3 相对于HAVING子句，更适合写在WHERE子句中的条件

有些条件既可以写在HAVING子句中，又可以写在WHERE子句中，这些条件就是聚合键所对应的条件。

代码25.将条件写在HAVING子句的情况

SELECT shohin_bunrui,COUNT(*)   FROM Shohin  GROUP BY shohin_bunruiHAVING shohin_bunrui='衣服';

输出结果：

代码26.将条件写在WHERE子句之中的情况

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

输出结果：

虽然条件分别写在WHERE和HAVING子句之中，但是条件的内容以及返回的结果完全相同。但是聚合键所对应的条件还是应该写在WHERE子句之中，原因如下：

1. WHERE 和HAVING子句的作用不同。WHERE子句=指定行所对应的条件，HAVING行=指定组所对应的条件

2.执行速度

3.4 对查询结果进行排序

3.4.1 ORDER BY子句

查询结果是无序的

代码27.ORDER BY子句的语法

SELECT 
     <列名1>
      ,
      <列名2>
       ,
       <列名3>
        ,......  FROM 
        <表名>
         ORDER BY 
         <排序基准列1>
          ,
          <排序基准列2>
           ,......

代码28.按照销售单价由低到高（升序）进行排序

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

输出结果：

不论何种情况，ORDER BY字句都需要写在SELECT语句的末尾

子句的书写顺序：

SELECT子句->FROM 子句-> WHERE 子句-> GROUP BY子句-> HAVING子句->ORDER BY子句

不想指定数据行的排列顺序时，SELECT语句中可以不写ORDER BY子句

3.4.2 指定升序或者降序

降序排列时，需要在列名后面使用DESC关键字。

代码29.按照销售单价由高到低（降序）进行排列

SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka    FROM ShohinORDER BY hanbai_tanka DESC;

输出结果：

其实使用升序排列时，正确的书写方式应该是使用关键字ASC，但省略关键字会默认使用升序排列。

3.4.3 指定多个排序键

代码30.按照销售单价和商品编号的升序进行排序

SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka  FROM ShohinORDER BY hanbai_tanka,shohin_id;

输出结果：

3.4.4 NULL的顺序

不能对NULL和数字进行排序，所以NULL会在结果的开头和末尾汇总显示

代码31.按照进货单价的升序进行排序

SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka   FROM ShohinORDER BY shiire_tanka;

输出结果：

3.4.5 对排序键中使用显示用别名

代码32.ORDER BY子句中可以使用列的别名

SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht, shiire_tanka   FROM ShohinORDER BY ht,id;

输出结果：

使用HAVING子句时SELECT语句的顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

在ORDER BY子句中可以使用SELECT子句中定义的别名

3.4.6 ORDER BY子句中可以使用的列

ORDER BY子句可以使用存在于表中，但并不包括在SELECT子句之中的列

代码33.SELECT子句中未包含的列也可以在ORDER BY子句中使用

SELECT shohin_mei,hanbai_tanka,shiire_tanka  FROM ShohinORDER BY shohin_id;

输出结果：

代码34.ORDER BY 子句中也可以使用聚合函数

SELECT shohin_bunrui,COUNT(*)   FROM Shohin GROUP BY shohin_bunruiORDER BY COUNT(*);

输出结果：

在ORDER BY子句中也可以使用SELECT 子句中未使用的列和聚合函数。

3.4.7 不要使用列编号

列编号是指SELECT子句中的列按照从左到右的顺序进行排列时所对应的编号。

代码35.ORDER BY子句中可以使用列编号

--使用列名SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka FROM  ShohinORDER BY hanbai_tanka DESC,shohin_id;--使用列编号SELECT shohin_id,shohin_mei,hanbai_tanka,shiire_tanka   FROM ShohinORDER BY 3 DESC,1;

输出结果：