聚合函数count count字段 sum字段 avg字段 min字段 max字段Word格式文档下载.docx
- 文档编号:16575285
- 上传时间:2022-11-24
- 格式:DOCX
- 页数:9
- 大小:18.77KB
聚合函数count count字段 sum字段 avg字段 min字段 max字段Word格式文档下载.docx
《聚合函数count count字段 sum字段 avg字段 min字段 max字段Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《聚合函数count count字段 sum字段 avg字段 min字段 max字段Word格式文档下载.docx(9页珍藏版)》请在冰豆网上搜索。
SELECTSUM(SAL)ASOLDSAL
WHEREAGE>
=40
运行结果如图8.2所示。
图8.2TEACHER表中所有年龄大于40岁的教师的工资总数
当对某列数据进行求和时,如果该列存在NULL值,则SUM函数会忽略该值。
8.2.2计数函数——COUNT()
COUNT()函数用来计算表中记录的个数或者列中值的个数,计算内容由SELECT语句指定。
使用COUNT函数时,必须指定一个列的名称或者使用星号,星号表示计算一个表中的所有记录。
两种使用形式如下。
COUNT(*),计算表中行的总数,即使表中行的数据为NULL,也被计入在内。
COUNT(column),计算column列包含的行的数目,如果该列中某行数据为NULL,则该行不计入统计总数。
1.使用COUNT(*)函数对表中的行数计数
COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。
实例3COUNT(*)函数的使用
查询TEACHER表中的所有记录的行数。
SELECTCOUNT(*)ASTOTALITEM
运行结果如图8.3所示。
图8.3使用COUNT(*)函数对表中的行数计数
在该例中,SELECT语句中没有WHERE子句,那么认为表中的所有行都满足SELECT语句,所以SELECT语句将返回表中所有行的计数,结果与5.2.1节的表5-1列出的TEACHER表的数据相吻合。
如果DBMS在其系统表中存储了表的行数,COUNT(*)将很快地返回表的行数,因为这时,DBMS不必从头到尾读取表,并对物理表中的行计数,而直接从系统表中提取行的计数。
而如果DBMS没有在系统表存储表的行数,将具有NOTNULL约束的列作为参数,使用COUNT()函数,则可能更快地对表行计数。
注意
COUNT(*)函数将准确地返回表中的总行数,而仅当COUNT()函数的参数列没有NULL值时,才返回表中正确的行计数,所以仅当受NOTNULL限制的列作为参数时,才可使用COUNT()函数代替COUNT(*)函数。
2.使用COUNT()函数对一列中的数据计数
COUNT()函数可用于对一列中的数据值计数。
与忽略了所有列的COUNT(*)函数不同,COUNT()函数逐一检查一列(或多列)中的值,并对那些值不是NULL的行计数。
实例4查询多列中所有记录的行数
查询TEACHER表中的TNO列、TNAME列以及SAL列中包含的所有数据行数。
SELECTCOUNT(TNO)ASTOTAL_TNO,COUNT(TNAME)ASTOTAL_TNAME,
COUNT(SAL)ASTOTAL_SAL
运行结果如图8.4所示。
图8.4使用COUNT()函数对一列中的数据计数
可见,TNO列与TNAME列由于其中不含有NULL值,所以其计数与使用COUNT(*)函数对TEACHER表中的记录计数结果相一致,而SAL列由于其中有两行数据为NULL,所以这两列没有被计入在内,计数结果也就是8。
3.使用COUNT()函数对多列中的数据计数
COUNT()函数不仅可用于对一列中的数据值计数,也可以对多列中的数据值计数。
如果对多列计数,则需要将要计数的多列通过连接符连接后,作为COUNT()函数的参数。
下面将结合具体的多列计数的实例,说明其使用过程。
说明
关于如何使用连接符连接多列可参见本书的7.2节。
实例5使用COUNT()函数对多列中的数据计数
统计TEACHER表中的TNO列、TNAME列和SAL列中分别包含的数据行数,以及TNO列和TNAME列、TNAME列和SAL列一起包含的数据行数。
COUNT(SAL)ASTOTAL_SAL,
COUNT(CAST(TNOASVARCHAR(5))+TNAME)AST_NONAME,
COUNT(TNAME+CAST(SALASVARCHAR(5)))AST_NAMESAL
运行结果如图8.5所示。
图8.5使用COUNT()函数对多列中的数据计数
在进行两列的连接时,由于它们的数据类型不一致,因此要使用CAST表达式将它们转换成相同的数据类型。
在7.2.1节已经讲过,如果在被连接的列中的任何一列有NULL值时,那么连接的结果为NULL,则该列不会被COUNT()函数计数。
COUNT()函数只对那些传递到函数中的参数不是NULL的行计数。
4.使用COUNT函数对满足某种条件的记录计数
也可以在SELECT语句中添加一些子句约束来指定返回记录的个数。
实例6使用COUNT函数对满足某种条件的记录计数
查询TEACHER表中女教师记录的数目。
SELECTCOUNT(*)ASTOTALWOMEN
女'
运行结果如图8.6所示。
图8.6使用COUNT函数对满足某种条件的记录计数
这时结果为6而不是前面的所有记录10。
之所以可以通过WHERE子句定义COUNT()函数的计数条件,这与SELECT语句各个子句的执行顺序是分不开的。
前面已经讲过,DBMS首先执行FROM子句,而后是WHERE子句,最后是SELECT子句。
所以COUNT()函数只能用于满足WHERE子句定义的查询条件的记录。
没有包括在WHERE子句的查询结果中的记录,都不符合COUNT()函数。
8.2.3最大/最小值函数—MAX()/MIN()
当需要了解一列中的最大值时,可以使用MAX()函数;
同样,当需要了解一列中的最小值时,可以使用MIN()函数。
SELECTMAX(column_name)/MIN(column_name)
列column_name中的数据可以是数值、字符串或是日期时间数据类型。
MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。
实例7MAX()函数的使用
查询TEACHER表中教师的最大年龄。
SELECTMAX(AGE)ASMAXAGE
运行结果如图8.7所示。
图8.7TEACHER表中教师的最大年龄
然而,在实际应用中得到这个结果并不是特别有用,因为经常想要获得的信息是具有最大年龄的教师的教工号、姓名、性别等信息。
然而SQL不支持如下的SELECT语句。
SELECTTNAME,DNAME,TSEX,MAX(AGE)
因为聚合函数处理的是数据组,在本例中,MAX函数将整个TEACHER表看成一组,而TNAME、DNAME和TSEX的数据都没有进行任何分组,因此SELECT语句没有逻辑意义。
同样的道理,下面的代码也是无效的。
SELECTTNAME,DNAME,TSEX,SAL,AGE
WHEREAGE=MAX(AGE)
解决这个问题的方法,就是在WHERE子句中使用子查询来返回最大值,然后再基于这个返回的最大值,查询相关信息。
实例8在WHERE子句中使用子查询返回最大值
查询TEACHER表中年纪最大的教师的教工号、姓名、性别等信息。
SELECTTNAME,DNAME,TSEX,SAL,AGE
WHEREAGE=(SELECTMAX(AGE)FROMTEACHER)
运行结果如图8.8所示。
图8.8在WHERE子句中使用子查询返回最大值
MAX()和MIN()函数不仅可以作用于数值型数据,也可以作用于字符串或是日期时间数据类型的数据。
实例9MAX()函数用于字符型数据
如下面代码:
SELECTMAX(TNAME)ASMAXNAME
运行结果如图8.9所示。
图8.9在字符串数据类型中使用MAX的结果
可见,对于字符串也可以求其最大值。
对字符型数据的最大值,是按照首字母由A~Z的顺序排列,越往后,其值越大。
当然,对于汉字则是按照其全拼拼音排列的,若首字符相同,则比较下一个字符,以此类推。
当然,对与日期时间类型的数据也可以求其最大/最小值,其大小排列就是日期时间的早晚,越早认为其值越小,如下面的实例。
实例10MAX()、MIN()函数用于时间型数据
从COURSE表中查询最早和最晚考试课程的考试时间。
其中COURSE表的结构和数据可参见本书6.1节的表6-1。
SELECTMIN(CTEST)ASEARLY_DATE,
MAX(CTEST)ASLATE_DATE
FROMCOURSE
运行结果如图8.10所示。
图8.10COURSE表中最早和最晚考试课程的考试时间
可见,返回结果的数据类型与该列定义的数据类型相同。
确定列中的最大值(最小值)时,MAX()(MIN())函数忽略NULL值。
但是,如果在该列中,所有行的值都是NULL,则MAX()/MIN()函数将返回NULL值。
8.2.4均值函数——AVG()
函数AVG()用于计算一列中数据值的平均值。
SELECTAVG(column_name)
AVG()函数的执行过程实际上是将一列中的值加起来,再将其和除以非NULL值的数目。
所以,与SUM()函数一样,AVG()函数只能作用于数值型数据,即列column_name中的数据必须是数值型的。
实例11AVG()函数的应用
从TEACHER表中查询所有教师的平均年龄。
SELECTAVG(AGE)ASAVG_AGE
运行结果如图8.11所示。
图8.11TEACHER表中所有教师的平均年龄
在计算平均值时,AVG()函数将忽略NULL值。
因此,如果要计算平均值的列中有NULL值,计算均值时,要特别注意。
实例12AVG()函数对NULL值的处理
从TEACHER表中查询所有教师的平均工资。
SELECTAVG(SAL)ASAVG_AGE1,SUM(SAL)/COUNT(*)ASAVG_AGE2,
SUM(SAL)/COUNT(SAL)ASAVG_AGE3
运行结果如图8.12所示。
图8.12TEACHER表中所有教师的平均工资
可以发现得到了不同的结果。
实际上,“AVG(SAL)”与“SUM(SAL)/COUNT(SAL)”语句是等价的。
因为AVG(SAL)语句的执行过程实际上是将SAL列中的值加起来,再将其和(也就等价于SUM(SAL))除以非NULL值的数目(也就等价于COUNT(SAL))。
而语句“SUM(SAL)/COUNT(*)”则不然,因为COUNT(*)返回的是表中所有记录的个数,而不管SAL列中的数值是否为NULL。
AVG()函数在计算一列的平均值时,忽略NULL值。
但是,如果在该列中,所有行的值都是NULL,则AVG()函数将返回NULL值。
如果不想对列中的所有值求平均,则可在WHERE子句中使用搜索条件来限制用于计算均值的行。
实例13在WHERE子句中使用搜索条件来限制用于计算均值的行
从TEACHER表中查询所有计算机系教师的平均年龄。
SELECTAVG(AGE)ASAVGCOMPUTER_AGE
WHEREDNAME='
计算机'
运行结果如图8.13所示。
图8.13TEACHER表中所有计算机系教师的平均年龄
当执行SELECT语句时,DBMS将表中的每行对WHERE子句中的搜索条件“DNAME='
”求值。
只有那些搜索条件为True时,行中的AGE值才传到均值函数AVG(AGE)中。
当然,除了显示表中某列的平均值,还可用AVG()函数作为WHERE子句的一部分。
与前面介绍的MAX()函数一样,不能直接用于WHERE子句,必须以子查询的形式。
实例14AVG()函数作为WHERE子句中搜索条件的一部分
从TEACHER表中查询所有年龄高于平均年龄的教师的信息。
SELECT*
WHEREAGE>
=(SELECTAVG(AGE)FROMTEACHER)
ORDERBYAGE
运行结果如图8.14所示。
图8.14TEACHER表中所有年龄高于平均年龄的教师的信息
8.2.5聚合分析的重值处理
前面介绍的5种聚合函数,可以作用于所选列中的所有数据(不管列中的数据是否有重置),也可以只对列中的非重值进行处理,即把重复的值只取一次进行聚合分析。
当然,对于MAX()/MIN()函数来讲,重值处理意义不大。
可以使用ALL关键字指明对所选列中的所有数据进行处理,使用DISTINCT关键字指明对所选列中的非重值数据进行处理。
以AVG()函数为例,语法如下。
SELECTAVG([ALL/DISTINCT]column_name)
[ALL/DISTINCT]在缺省状态下,默认是ALL关键字,即不管是否有重值,处理所有数据。
其他聚合函数的用法与此相同。
MicrosoftAccess数据库不支持在聚合函数中使用DISTINCT关键字。
实例15聚合分析的重值处理
从TEACHER表中查询工资SAL列中存在的所有记录数。
SELECTCOUNT(ALLSAL)ASALLSAL_COUNT
运行结果如图8.15所示。
图8.15TEACHER表中工资SAL列中存在的所有记录数
当然,在代码中去除ALL关键字,也可以得到相同的结果。
而如果从TEACHER表中,查询工资SAL列中存在的不同记录的数目,可采用如下代码。
SELECTCOUNT(DISTINCTSAL)ASDISTINCTSAL_COUNT
运行结果如图8.16所示。
图8.16TEACHER表中SAL列存在的不同记录的数目
对比两个结果,使用DISTINCT关键字后,工资SAL列中的重值并没有列入统计的范围之内。
另外还要强调一点,在所有5种聚合函数中,除了COUNT(*)函数外,其他的函数在计算过程中都忽略NULL值,即把NULL值的行排除在外,不进行分析。
8.2.6聚合函数的组合使用
前面介绍的实例中,聚合函数都是单独使用的。
聚合函数也可以组合使用,即在一条SELECT语句中,可以使用多个聚合函数。
实例16使用多个聚合函数
如下面的代码:
SELECTCOUNT(*)ASnum_items,
MAX(SAL)ASmax_sal,
Min(AGE)ASmin_age,
SUM(SAL)/COUNT(SAL)ASavg_sal,
AVG(DISTINCTSAL)ASdisavg_sal
运行结果如图8.17所示。
图8.17聚合函数的组合应用
该例在一条SELECT语句中,几乎用到了所有的聚合函数。
其中num_items为TEACHER表所有记录的条目,max_sal为TEACHER表中记录的最高工资,min_age为TEACHER表中记录的最小年龄,avg_sal为所有TEACHER表中的工资记录的平均值,disavg_sal为TEACHER表中所有不同的工资记录的平均值。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 聚合函数count count字段 sum字段 avg字段 min字段 max字段 聚合 函数 count 字段 sum avg min max
链接地址:https://www.bdocx.com/doc/16575285.html