数据库聚合函数

Click to edit Master title style,Click to edit Master text styles,SQL Server函数,主讲人：胡兴铭,猜猜这是一个什么地方,第5章 轻轻松松认识函数,本章主要介绍如下几种常用的函数：,数学函数,字符串函数,日期和时间函数,数据类型转换函数,聚合函数,系统函数,文本和图像函数,5.5 聚合函数,聚合函数能够对一组值进行计算，并返回一个单一值。主要的聚合函数有如下几种：,函数名称,函数功能,COUNT,返回指定表达式的数据量,MAX,返回指定表达式的最大值,MIN,返回指定表达式的最小值,SUM,返回指定表达式中所有值的和,AVG,返回指定表达式的平均值,5.5.1 COUNT函数,COUNT函数语法,COUNT函数用于计算总数。语法格式：,COUNT(ALL|DISTINCT expression|*),ALL：对表达式的所有值进行计算，为默认设置。,DISTINCT：去除重复值后计算总数。,expression：精确数字或近似数字数据类型的表达式。,COUNT函数举例,求图书馆中的图书是由多少个出版社出版的。图书馆图书的表为books。表如下所示：,COUNT函数注意事项,Count函数举一反三,已知一个学生的信息表studentinfo，其主键为stuID，求一共有多少个学生。Studentinfo表如下：,Select count(*)from studentinfo,Select count(stuID)from studentinfo,Count函数举一反三,5.5.2 MAX函数,MAX函数用于计算一组数据中的最大值。语法格式：,MAX(ALL|DISTINCT expression),ALL：对表达式中的所有值进行计算，为默认设置。,DISTINCT：去除重复值后计算最大值，对于MAX函数来说，使用此设置五任何意义。,expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。,实例：求出班级中学生的最大年龄是多少。班级表为studentInfo，表如下所示：,SELECT MAX(age)from studentInfo,该语句返回值为23。,MAX函数举一反三,求图书馆中库存量最大的书的库存量是多少。图书馆图书的表为books。表如下所示：,Select max(number)as 最大库存 from books,5.5.3 MIN函数,MIN函数用于计算组数据中的最小值。语法格式：,MAX(ALL|DISTINCT expression),ALL：对表达式中的所有值进行计算，为默认设置。,DISTINCT：去除重复值后计算最小值。,expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。,实例：求出班级中学生的最小年龄是多少。班级表为studentInfo，表如下所示：,SELECT MIN(Age)from studentInfo,该语句返回值为21。,MIN函数举一反三,求图书馆中最便宜的书的价格是多少。图书馆图书的表为books。,Select min(price)as 便宜的书的价格 from books,5.5.4 SUM函数,SUM函数用于计算表达式中所有数据的和。语法格式：,SUM(ALL|DISTINCT expression),ALL：对表达式中的所有值进行计算，为默认设置。,DISTINCT：去除重复值后计算数据和。,expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。,SUM函数,求图书馆中的库存为多少。图书馆图书的表为books。,Select sum(number)as 总库存 from books,5.5.5 AVG函数,AVG函数用于计算表达式的平均值。语法格式：,AVG(ALL|DISTINCT expression),ALL：对表达式的所有值进行计算，为默认设置。,DISTINCT：去除重复值后计算平均值。,expression：精确数字或近似数字数据类型的表达式，不允许使用聚合函数与子查询。,AVG函数举例,已知一个学生的信息表studentinfo，求学生的平均年龄。其中Studentinfo表如下：,方法一:Select avg(age)from studentinfo,AVG函数举例,方法二:Select sum(age)/count(*)from studentinfo,5.6 系统函数,系统函数可以显示T-SQL执行运算后返回的SQL Server服务器和数据库中有关值、对象和设置的特殊信息。,5.6.1 SERVERPROPERTY函数,SERVERPROPERTY函数用于查询有关服务器实例的属性信息。语法格式：,SERVERPROPERTY（argument）,其中，argument指定要查询的信息标志。,实例：查询服务器实例名称。,SELECT CONVERT(char(15),SERVERPROPERTY(servername),该语句返回值为当前服务器所使用的实例名称。,5.6.2 DATABASEPROPERTY函数,DATABASEPROPERTY函数用于查询指定数据库的属性名的属性值。语法格式：,DATABASEPROPERTY（argument1,argument2）,argument：指定数据库。,argument2：要查询的属性名称,实例：查询master数据库的IsTruncLog属性值设置情况。,SELECT DATABASEPROPERTY(master,IsTruncLog),该语句返回值为1，表示该选项设置成了true。,5.6.3 TYPEOFPROPERTY函数,TYPEOFPROPERTY函数用于查询有关数据类型的信息。语法格式：,TYPEOFPROPERTY（date_type,property_name）,date_type：指定数据库。,property_name：要查询的属性名称,实例：查询int与char类型的PRECISION属性。,SELECT TYPEPROPERTY(int,PRECISION),SELECT TYPEPROPERTY(char,PRECISION),两条语句的返回值分别为10、8000。,5.6.4 OBJECT_ID函数,OBJECT_ID函数用于查询数据库对象的ID号。语法格式：,OBJECT_ID（object_name,object_type）,object_name：指定数据库对象。,object_type：要查询的属性名称,实例：查询表studentInfo的ID号。,select object_id(dbo.studentInfo,u),该语句的返回值为2073058421，如该表不存在则返回NULL。,5.7 文本和图像函数,文本和图像函数用于对文本或图像输入值或列执行操作，并且提供有关该值的基本信息。T-SQL中常用的文本函数有两个：TEXTPTR函数和TEXTVALID函数。,5.7.1 TEXTPTR函数,TEXTPTR函数用于返回对应varbinary格式的text、ntext或者image 列的文本指针值。查找到的文本指针值可应用于READTEXT、WRITETEXT和UPDATETEXT语句。语法格式如下：,TEXTPTR(column),其中，column表示将要使用的text、ntext或者image列。,实例：查询表studentInfo中stuAdd的十六字节文本指针。,SELECT TEXTPTR(stuAdd)FROM studentInfo,该语句的返回值为诸如的记录集。,5.7.2 TEXTVALID函数,TEXTVALID函数用于检查特定文本指针是否为有效的text、ntext或image函数。语法格式如下：,TEXTVALID(table.column,text_ ptr),table：将要使用表名。,column：要使用的列名。,text_ ptr：要检查的文本指针。,5.8 小结,Transact-SQL中函数代表的是能够完成某个功能并返回值的一个模块或者代码块，它能够帮助用于使用简单代码实现复杂的功能。Transact-SQL支持系统内置函数和用户定义函数，本章主要介绍了内置函数，使用这些函数已经基本能够满足初级用户的任务业务需求，自定义函数将在后续的章节中详细介绍。,课后习题,1.函数包括的种类。,Transact-SQL的内置函数主要包括聚合函数、游标函数、数学函数、字符串函数、系统函数、日期和时间函数、元数据函数以及文本和图像函数等多种。,课后习题,2.根据题意，使用学到的函数完成习题：获得当前日期的年份，月份以及天数。,分析：此题考察了学生对日期函数的灵活使用。要想知道当前日期的年份、,月份以及天数，我们必须先获取当前系统的时间。此时我们就需要通过getdate()函数来获取当前系统时间，然后再通过year()、month()、Day(),函数分别得到当前日期的年份、月份以及天数。,解答：select year(getdate(),select month(getdate(),select day(getdate(),在SQL Server环境中运行结果如下一页所示,课后习题,课后习题,3.假如有表books，其代表价格的字段为price，使用聚合函数输出图书的总价值。,此题考察了聚合函数中的求和函数sum()，并复习了select,From查询语句。,解答：select sum(price)as 图书总价值 from books,课后习题,课后习题,4.已知字符串str=hello SQL Server 2005,使用截取函数来完成下列操作。,（1）截取字符串左边的4个字符。,（2）截取字符创中的“SQL Server”字符。,此题考察了学生对于left()、right()、substing()函数的使用。LEFT函数返回从字符串左边开始指定个数的字符串。RIGHT函数与LEFT函数功能相反，它返回从字符串右边开始指定个数的字符串。SUBSTRING函数用于获取指定字符串或二进制数据的一部分。,解答：,Select left(hello SQL Server 2005,4),Select right(Select left(hello SQL Server 2005,16),10),Select substring(hello SQL Server 2005,7,10),课后习题,课后习题,5.已知字符串str=hello SQL Server 2005,完成下面操作。,（1）求出次字符串的长度。,（2）去除字符串中的空格。,此题考察学生对于len()函数以及replace()函数的使用。,LEN函数用于返回指定字符串的总长度，即包含的字符的个数。PEPLACE函数用于在一个字符串中用指定的字符串替换另一个字符串。LTRIM函数用于去除指定字符串左边的空格。RTRIM函数用于去除指定字符串右边的空格。,解答：,Select len(hello SQL Server 2005),select replace(hello SQL Server 2005,),课后习题,