数据库聚合函数.ppt

SQL Server函数,主讲人：胡兴铭,猜猜这是一个什么地方,第5章 轻轻松松认识函数,本章主要介绍如下几种常用的函数：数学函数字符串函数日期和时间函数数据类型转换函数聚合函数系统函数文本和图像函数,5.5 聚合函数,聚合函数能够对一组值进行计算，并返回一个单一值。主要的聚合函数有如下几种：,5.5.1 COUNT函数,COUNT函数语法,COUNT函数用于计算总数。语法格式：COUNT(ALL|DISTINCT expression|*)ALL：对表达式的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算总数。expression：精确数字或近似数字数据类型的表达式。,COUNT函数举例,求图书馆中的图书是由多少个出版社出版的。图书馆图书的表为books。表如下所示：,COUNT函数注意事项,Count函数举一反三,已知一个学生的信息表studentinfo，其主键为stuID，求一共有多少个学生。Studentinfo表如下：,Select count(*)from studentinfoSelect 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()，并复习了selectFrom查询语句。,解答：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,),课后习题,