轻轻松松认识函数.ppt

第5章 轻轻松松认识函数,函数对于任何程序设计语言来说都是非常关键的部分，它给用户提供了强大的功能，帮助用户使用简捷的代码实现复杂的功能。函数在Transact-SQL中代表的是能够完成某个功能并返回值的一个模块或者代码块。Transact-SQL支持两类函数：系统内置函数和用户定义函数。Transact-SQL的内置函数主要包括聚合函数、游标函数、数学函数、字符串函数、系统函数、日期和时间函数、元数据函数以及文本和图像函数等多种本章主要介绍如下几种常用的函数：数学函数字符串函数日期和时间函数数据类型转换函数聚合函数系统函数文本和图像函数,5.1 数学函数,Transact-SQL提供了数学运算的一组函数，能够对数字表达式进行计算，并将结果返回给用户。这些函数是进行数据运算、统计和分析的基础。,5.2 字符串函数,字符串函数可以对字符串执行查找、转换等操作，作用于CHAR、VARCHAR、BINARY、和VARBINARY 数据类型。字符串函数可以在SELECT语句的SELECT和WHERE子句以及表达式中使用字符串函数。,5.2.1 ASCII函数,ASCII函数返回字符串表达式最左端字符的ASCII 码值。语法格式：ASCII(character_expression)character_expression：字符或字符串类型的表达式。实例：查询h的ASCII码值。SELECT ASCII(happy)返回值为104，类型为int。,5.2.2 CHAR函数,与ASCII函数的功能恰好相反，CHAR函数把ASCII码值换算成对应的字符。语法格式：CHAR(integer_expression)integer_expression：0255之间的整数，否则返回NULL。实例：查询104对应的字符。SELECT CHAR(104)返回值为h，类型为char。,5.2.3 CHARINDEX函数,CHARINDEX函数用于返回字符串中指定表达式的开始位置。语法格式：CHARINDEX(expression1,expression2,start_location)expression1：字符串表达式，指定的搜索串。expression2：字符串表达式，包含要查找的字符序列。start_location：搜索的起始位置，是可选项。如不指定或者指定为0或者负值，则搜索从初始位置开始，否则从指定位置开始搜索。,5.2.4 LEFT函数,LEFT函数返回从字符串左边开始指定个数的字符串。语法格式：LEFT(character_expression,integer_expression)character_expression：integer_expression：实例：返回字符串前6个字符以及前20个字符。SELECT LEFT(I hava many friends,6)SELECT LEFT(I hava many friends,20)第一条语句返回值为“I hava”，第二条语句返回值为“I hava many friends”，即如果指定长度超过字符串的长度，并不会出错，而是返回整个字符串。,5.2.5 RIGHT函数,RIGHT函数与LEFT函数功能相反，它返回从字符串右边开始指定个数的字符串。语法格式：RIGHT(character_expression,integer_expression)character_expression：integer_expression：实例：返回给定字符串后7个字符以及该这七个字符的第一个字符。SELECT RIGHT(I hava many friends,7)SELECT LEFT(RIGHT(I hava many friends,7),1)第一条语句返回值为“friends”，第二条语句返回值为f，即先使用RIGHT(I hava many friends,7)求出最后7个字符组成的字符串，然后再使用LEFT函数求出该字符串的首字符。,5.2.6 LEN函数,LEN函数用于返回指定字符串的总长度，即包含的字符的个数。语法格式：LEN(string_expression)string_expression n：指定的需计算长度的字符串。实例：计算给定字符串的长度。SELECT LEN(friends)SELECT LEN(朋友)SELECT LEN(2046)这三条语句的返回值依次为7、2、4，需要注意的是一个汉字也是作为一个字符处理的，同时纯数字组成的串可以不加，但如果是数字和字符混合出现时必须要加上。,5.2.7 LTRIM函数,LTRIM函数用于去除指定字符串左边的空格。语法格式：LTRIM(character_expression)character_expression：指定字符串或二进制表达式。实例：去除指定字符串前的空格。SELECT LTRIM(There are two space in the left)SELECT LTRIM(There are two space and a Tab in the left)第一条语句返回值为“There are two space in the left”，第二条语句返回值为There are two space and a Tab in the left，第二条语句看似空格没有去除掉，实际上在字符串前的输入的Tab键造成的，并非空格。,5.2.8 RTRIM函数,RTRIM函数用于去除指定字符串右边的空格。语法格式：RTRIM(character_expression)character_expression：指定字符串或二进制表达式。实例：去除指定字符串前后的空格。SELECT RTRIM(LTRIM(There are three space in the left and three space in the rignt)该语句的返回值为“There are three space in the left and three space in the rignt”，即先使用LTRIM函数去除字符串左端的空格，然后对得出的字符处去除右端的空格。,5.2.9 LOWER函数,LOWER函数用于把指定的字符串中的大写字符装换为相应的小写字符。语法格式：LOWER(character_expression)character_expression：指定需要进行转换的字符串。实例：将字符串中的大写字符全部转换成小写字符。SELECT LOWER(A became a after this function)该语句的返回值为“a became a after this function”，即经过转换大写字符和小写字符没有任何区别，便于程序的统一处理。,5.2.10 UPPER函数,UPPER函数用于把指定的字符串中的小写字符装换为相应的大写字符。语法格式：UPPER(character_expression)character_expression：指定需要进行转换的字符串。实例：将字符串中的小写字符全部转换成大写字符。SELECT UPPER(a TO A)该语句的返回值为“A TO A”。,5.2.11 PATINDEX函数,PATINDEX函数用于返回指定表达式中某模式的初始位置，如果在字符串中未能匹配该模式，则返回0。语法格式：PATINDEX(%pattern%,expression)%pattern%：expression：实例：返回字符串in在字符串There are many tree in my garden中的初始位置。SELECT PATINDEX(%in%,There are many tree in my garden)该语句的返回值为21。,5.2.12 REPALCE函数,PEPLACE函数用于在一个字符串中用指定的字符串替换另一个字符串。语法格式：REPLACE(string_expresddion1,string_expresddion2,string_expresddion3)string_expresddion1：要搜索的原字符串，可以是字符或二进制数据类型。string_expresddion2：原字符串中将要被替换的字符串。string_expresddion3：指定的进行替换的字符串。,5.2.13 REVERSE函数,REVERSE函数用于返回指定表达式的逆向形式。语法格式：REVERSE(character_expression)character_expression：指定的用于需要进行转换的表达式，可以是字符串或二进制数据实例：将字符串“A fine day”进行整体反转。SELECT REVERSE(A fine day)该语句返回值为“yad enif A”，即原字符串的最后一个字符作为新字符串的第一个字符，原字符串的第一个字符作为新字符串的最后一个字符。,5.2.14 STR函数,STR函数用于将数字数据转换为字符数据。语法格式：STR(float_expression,length,decimal)float_expression：带小数点的近似数字。length：包括小数点、符号、数字或空格的总长度值，默认为10。decimal：小数点右边的位数。,5.2.15 SUBSTRING函数,SUBSTRING函数用于获取指定字符串或二进制数据的一部分。语法格式：SUBSTRING(expression,start,length)expression：字符串表达式。start：指定子串的开始位置。length：指定子串的长度。,5.3 日期和时间函数,日期和时间函数主要用于对时间和日期有关信息的输入、处理及输出操作，基本使用方式与其他函数相同。,5.3.1 DATEADD函数,DATEADD函数用于返回指定日期加上一个时间段后的新日期时间值。语法格式：DATEADD(datepart,number,date)datepart：返回新值的日期的组成部分，可识别的日期部分为year、month、day、hour、minute、second、millsecond等。number：与datepart相加的指定值。如果该值为非整数值，舍弃该值的小数部分。date：表达式，用于返回datetime或日期格式的字符串。,5.3.2 DATENAME函数,DATENAME函数用于返回指定日期指定部分的字符串。语法格式：DATENAME(datepart,date)datepart：返回新值的日期的组成部分。date：表达式，用于返回datetime或日期格式的字符串。实例：查询给定日期的星期值。SELECT DATENAME(weekday,2010-10-10)该语句的返回值为星期日，即指定时间2010-10-10为星期日。,5.3.3 DATEPART函数,DATENPART函数用于返回指定日期指定部分的整数值。语法格式：DATEPART(datepart,date)datepart：返回新值的日期的组成部分。date：表达式，用于返回datetime或日期格式的字符串。实例：查询给定日期的星期值。SELECT DATEPART(weekday,2010-10-10)该语句的返回值为1，此处既可以看出该函数与DATENAME函数的区别。,5.3.4 DAY函数,DAY函数用于返回指定日期的天数，从功能实现上进行比较，该函数等价于DATEPART（dd,date）函数的实现。语法格式：DAY(day)day：日期表达式。实例：查询给定日期天数。SELECT DAY(2010-10-10)该语句的返回值为10。,5.3.5 MONTH函数,MONTH函数用于返回指定日期的月份，从功能实现上进行比较，该函数等价于DATEPART（mm,date）函数的实现。语法格式：MONTH(day)day：日期表达式。实例：查询给定日期月份。SELECT MONTH(2010-10-10)该语句的返回值为10。,5.3.6 YEAR函数,YEAR函数用于返回指定日期的年份，从功能实现上进行比较，该函数等价于DATEPART（yy,date）函数的实现。语法格式：YEAR(day)day：日期表达式。实例：查询给定日期的年份。SELECT YEAR(2010-10-10)该语句的返回值为2010。,5.3.7 GETDATE函数,GETDATE函数用于返回系统的日期和时间。语法格式：GETDATE()参数：无。实例：查询系统当前时间及当前天数。SELECT GETDATE()SELECT DAY(GETDATE()第一条语句返回值为2010-05-30 22:21:45.043，即当前的系统日期和时间；第二条语句在取得系统日期和时间的前提下，使用DAY函数取得当前的天数值。,5.4 数据类型转换函数,同时处理不同数据类型的值时，SQLServer一般会自动进行隐式的类型转换。这种转换对于相近数据类型的值是有效的，比如说int型和float型的数据，而对于其他的类型，如float型数据与字符型数据，隐式转换是行不通的，必须使用显示转换。T-SQL提供两个显示转换函数，分别是CAST函数与CONVERT函数，下面将对这两个类型转换函数详细介绍。,5.4.1 CAST函数,CAST函数用于将某种类型的数据转换为另一种类型的数据。语法格式：CAST(expression AS data_ type)expression：指定的需进行转换的表达式AS：参数分隔符data_ type：目标数据类型,5.4.2 CONVERT函数,CONVERT函数用于按照指定的格式将数据装换成另一种类型的数据。语法格式：CONVERT(data_ type length,expression,style)data_ type：目标系统所提供的数据类型，bigint和sql_variant。length：数据长度expression：指定的需进行转换的表达式style：日期格式实例：输出当前系统的日期。SELECT CONVERT(varchar(12),getdate()该语句的返回值为05 31 2010。,5.5 聚合函数,聚合函数能够对一组值进行计算，并返回一个单一值。聚合函数经常与SELECT语句的GROUPBY字句联合使用。,5.5.1 AVG函数,AVG函数用于计算表达式的平均值。语法格式：AVG(ALL|DISTINCT expression)ALL：对表达式的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算平均值。expression：精确数字或近似数字数据类型的表达式，不允许使用聚合函数与子查询。实例：计算班级中学生的平均年龄。SELECT AVG(stuAge)from studentInfoSELECT AVG(DISTINCT stuAge)from studentInfo 第一条语句返回值为22，第二条语句返回值为23。返回值不同的原因在于第二条语句使用了DISTINCT，去除了数据中的重复值。,5.5.2 COUNT函数,COUNT函数用于计算总数。语法格式：COUNT(ALL|DISTINCT expression|*)ALL：对表达式的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算总数。expression：精确数字或近似数字数据类型的表达式，不允许使用聚合函数与子查询。,5.5.3 MAX函数,MAX函数用于计算组数据中的最大值。语法格式：MAX(ALL|DISTINCT expression)ALL：对表达式中的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算最大值，对于MAX函数来说，使用此设置五任何意义。expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。实例：求出班级中学生的最大年龄是多少。SELECT MAX(stuAge)from studentInfo该语句返回值为24。,5.5.4 MIN函数,MIN函数用于计算组数据中的最小值。语法格式：MAX(ALL|DISTINCT expression)ALL：对表达式中的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算最小值。expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。实例：求出班级中学生的最小年龄是多少。SELECT MIN(stuAge)from studentInfo该语句返回值为20。,5.5.5 SUM函数,SUM函数用于计算表达式中所有数据的和。语法格式：SUM(ALL|DISTINCT expression)ALL：对表达式中的所有值进行计算，为默认设置。DISTINCT：去除重复值后计算数据和。expression：可以是常量、列名、函数等类型，不允许使用聚合函数与子查询。,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 该语句的返回值为诸如0的记录集。,5.7.2 TEXTVALID函数,TEXTVALID函数用于检查特定文本指针是否为有效的text、ntext或image函数。语法格式如下：TEXTVALID(table.column,text_ ptr)table：将要使用表名。column：要使用的列名。text_ ptr：要检查的文本指针。,5.8 小结,Transact-SQL中函数代表的是能够完成某个功能并返回值的一个模块或者代码块，它能够帮助用于使用简单代码实现复杂的功能。Transact-SQL支持系统内置函数和用户定义函数，本章主要介绍了内置函数，使用这些函数已经基本能够满足初级用户的任务业务需求，自定义函数将在后续的章节中详细介绍。,