1、SQL数据库函数应用SQL函数说明大全 Posted on 2010-08-23 23:37 moss_tan_jun 阅读(26235) 评论(13) 编辑 收藏 一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。下表给出了T-SQL函数的类别和描述。函数类别作用聚合函数 执行的操作是将多个值合并为一个值。例如 COUNT、SUM、MIN 和 MAX。配置函数是一种标量函数，可返回有关配置设置的信息。转换函数将值从一种数据类型转换为另一种。加密函数 支持加密、解密、数字签名和数字
2、签名验证。游标函数返回有关游标状态的信息。日期和时间函数可以更改日期和时间的值。数学函数执行三角、几何和其他数字运算。元数据函数返回数据库和数据库对象的属性信息。排名函数是一种非确定性函数，可以返回分区中每一行的排名值。行集函数返回可在 Transact-SQL 语句中表引用所在位置使用的行集。安全函数返回有关用户和角色的信息。字符串函数可更改 char、varchar、nchar、nvarchar、binary 和 varbinary 的值。系统函数对系统级的各种选项和对象进行操作或报告。系统统计函数 返回有关 SQL Server 性能的信息。文本和图像函数可更改 text 和 image
3、 的值。函数的组成函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value)，标量值代表一个数据单元或一个简单值。实际上，函数可以返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度，第12章将讲解如何创建和使用用户自定义函数，以返回更复杂的数据。函数己经存在很长时间了，它的历史比SQL还要长。在几乎所有的编程语言中，函数调用的方式都是相同的：Result=Function() 在T-SQL中，一般用SELECT语句来返回值。如果需要从查询中返回一个值，就可以把SELECT当成输出运算符，而不用使用等号：SELECT Function()一
4、个论点对于SQL函数而言，参数表示输入变量或者值的占位符。函数可以有任意个参数，有些参数是必须的，而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾，以便于在函数调用中去除不需要的参数。在SQL Server在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的CONVERT()函数例子中，数据类型的length和style参数是可选的:CONVERT (data-type (length), expression,style) 可将它简化为如下形式，因为现在不讨论如何使用数据类型：CONVERT(date_type, expression,style) 根据上面的定义，
5、CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是正确的：SELECT CONVERT(Varchar(20), GETDATE()SELECT CONVERT(Varchar(20), GETDATE(), 101)这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写
6、上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。确定性函数由于数据库引擎的内部工作机制，SQL Server必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全
7、局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。以下这些函数是确定性的：AVG()(所有的聚合函数都是确定性的)CAST()CONVERT()DATEADD()DATEDIFF()ASCII()CHAR()SUBSTRING()以下这些函数与变量是非确定性的：GETDATE()ERRORSERVICENAMECURSORSTATUS()RAND()在函数中使用用户变量变量既可用于输入，也可用于输出。在T-SQL中，用户变量以符号开头
8、，用于声明为特定的数据类型。可以使用SET或者SELECT语句给变量赋值。以下的例子用于将一个int类型的变量MyNumber传递给SQRT()函数：DECLARE MyNumber int SET MyNumber=144SELECT SQRT(MyNumber)结果是12，即144的平方根。用SET给变量赋值以下例子使用另一个int型的变量MyResult，来捕获该函数的返回值。这个技术类似于过程式编程语言中的函数调用样式，即把SET语句和一个表达式结合起来，给参数赋值：DECLARE MyNumber int, MyResult int SET MyNumber = 144- Assig
9、n the function result to the variable:SET MyResult = SQRT(MyNumber)- Return the variable valueSELECT MyResult用SELECT给变量赋值使用SELECT的另一种形式也可以获得同样的结果。对变量要在赋值前要先声明。使用SELECT语句来替代SET命令的主要优点是，可以在一个操作内同时给多个变量赋值。执行下面的SELECT语句，通过SELECT语句赋值的变量就可以用于任何操作了。DECLARE MyNumber1 int, MyNumber2 int, MyResult1 int, MyRes
10、ult2 int SELECT MyNumber1 = 144, MyNumber2 = 121 - Assign the function result to the variable:SELECT MyResult1 = SQRT(MyNumber1), MyResult2 = SQRT(MyNumber2) - Return the variable valueSELECT MyResult1, MyResult2上面的例子首先声明了4个变量，然后用两个SELECT语句给这些变量赋值，而不是用4个SELECT语句给变量赋值。虽然这些技术在功能上是相同的，但是在服务器的资源耗费上，用一个S
11、ELECT语句给多个变量赋值一般比用多个SET命令的效率要高。将一个甚至多个值选进参数的限制是，对变量的赋值不能和数据检索操作同时进行。这就是上面的例子使用SELECT语句来填充变量，而用另外一个SELECT语句来检索变量中数据的原因。例如，下面的脚本就不能工作:DECLARE RestockName varchar(50)SELECT ProductId ,RestockName = Name + : + ProductNumberFROM Production.Product这个脚本会产生如下错误:消息141，级别15，状态1，第2 行向变量赋值的SELECT 语句不能与数据检索操作结合使
12、用。在查询中使用函数函数经常和查询表达式结合使用来修改列值。这只需将列名作为参数传递给函数即可，随后函数将引用插入到SELECT查询的列的列表中，如下所示：SELECT Title, NationalIDNumber, YEAR(BirthDate) AS BirthYearFROM HumanResources.Employee在这个例子中，BirthDate列的值被作为参数传递给YEAR()函数。函数的结果是别名为BirthYear的列。嵌套函数我们需要的功能常常不能仅由一个函数来实现。根据设计，函数应尽量简单，用于提供特定的功能。如果一个函数要执行许多不同的操作，就变得复杂和难以使用。因
13、此，每个函数通常仅执行一个操作，要实现所有的功能，可以将一个函数的返回值传递给另一个函数，这称为嵌套函数调用。以下是一个简单的例子：GETDATE()函数的作用是返回当前的日期与时间，但不能返回经过格式化的数据，因为这是CONVERT()函数的功能。要想同时使用这两个函数，可以把GETDATE()函数的输出作为CONVERT()函数的输入参数。SELECT CONVERT(Varchar(20), GETDATE(), 101)聚合函数报表的典型用途是从全部数据中提取出代表一种趋势的值或者汇总值，这就是聚合的意义。聚合函数回答数据使用者的如下问题:上个月鸡雏的总销售量是多少?1924岁之间的巴
14、西男性在食品调味品上的平均支出是多少?上季度所有订单中从订购到运输的最长时间是多少?收发室里仍在工作的最老的员工是谁?聚合函数应用特定的聚合操作并返回一个标量值(单一值)。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累积以及透视等表运算一起使用，生成数据分析结果。第7章将详细介绍这个主题，这里仅讨论简单SELECT查询中的一些常用函数。聚合函数不仅可用在SELECT查询中，还可以和标量输入值一起使用。那么，这样做的意义是什么呢？在下列代码中，将值15传递给下列聚合函数，每个函数的返回值都相同：SELECT AVG(15)SELECT SUM(15)SELECT MIN(15)
15、SELECT MAX(15)它们都返回15。虽然，对同一个值求平均、求和、求最小值、求最大值，所得的结果还是那个值。如果对一个值计数，又会产生什么结果呢？SELECT COUNT(15)得到的值是1，因为函数只计数了一个值。现在做一些有意义的事。聚合函数只有在处理结果集合中的一组数据时才有意义。每个函数都处理某列的非空值。除非使用分组操作(详见第7章)，否则不能在同一个SELECT语句中既返回聚合的值，又返回常规的列值。AVG()函数AVG()函数用于返回一组数值中所有非空数值的平均值。例如，表6-2包含了体操成绩。表 6-2体操运动员项 目成 绩Sara跳马9.25Cassie跳马8.75D
16、elaney跳马9.25Sammi跳马8.05Erika跳马8.60Sara平衡木9.70Cassie平衡木9.00Delaney平衡木9.25Sammi平衡木8.95Erika平衡木8.85对这些数据执行以下查询:SELECT AVG(Score) 结果是8.965。如果有三个女孩没有完成一些项目，在表中没有记录成绩，则可用NULL来表示(见表6-3)。表 6-3体操运动员项 目成 绩Sara跳马9.25Cassie跳马8.75Delaney跳马NULLSammi跳马8.05Erika跳马8.60Sara平衡木9.70Cassie平衡木NULLDelaney平衡木9.25Sammi平衡木NU
17、LLErika平衡木8.85脚本：create table #GymEvent(Player varchar(10),Subject nvarchar(5),Score decimal(4,2)goinsert into #GymEvent values(Sara,跳马,9.25)insert into #GymEvent values(Cassie,跳马,8.75)insert into #GymEvent values(Delaney,跳马,NULL)insert into #GymEvent values(Sammi,跳马,8.05)insert into #GymEvent value
18、s(Erika,跳马,8.60)insert into #GymEvent values(Sara,平衡木,9.70)insert into #GymEvent values(Cassie,平衡木,NULL)insert into #GymEvent values(Delaney,平衡木,9.25)insert into #GymEvent values(Sammi,平衡木,NULL)insert into #GymEvent values(Erika,平衡木,8.85)godrop table #GymEvent在这种情况下，计算平均值时只考虑实际的数值，NULL不参与运算，结果是8.921
19、429。 但是，如果把缺少的成绩也算在内，即用数值0代替NULL，则会严重影响最终成绩(6.245)，她们能不能进入国家级的比赛就难说了。COUNT()函数COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。比如，在上一个例子中，体操数据被保存在#GymEvent表中，要确定Sammi参加的项目数，则可以执行下列查询：SELECT COUNT(Score) FROM #GymEvent WHERE Player=Sammi结果是1，因为Sammi只参加了跳马比赛，她的平衡木成绩是NULL。如果需要确定表中的行数，无论这些行是不是NULL值，都可以使用以下语法：SELECT CO
20、UNT (*) FROM #GymEvent以Sammi为例，COUNT(*)查询如下所示:SELECT COUNT(*) FROM #GymEvent WHERE Player=Sammi由于COUNT(*)函数会忽略NULL值，所以这个查询的结果是2。MIN()与MAX()函数MIN()函数用于返回一个列范围内的最小非空值；MAX()函数用于返回最大值。这两个函数可以用于大多数的数据类型，返回的值根据对不同数据类型的排序规则而定。为了说明这两个函数，假设有一个表包含了两列值，一列是整型值，另一列是字符型值，如表6-4所示。表 6-4IntegerColumn(int类型)VarCharCo
21、lumn(varChar类型)224412121919脚本：create table #Temp(IntegerColumn int,VarCharColumn varchar(10)goinsert into #Temp values(2,2)insert into #Temp values(4,4)insert into #Temp values(12,12)insert into #Temp values(19,19)godrop table #Temp如果分别调用MIN()与MAX()函数将会返回什么值呢？select MIN(IntegerColumn),MAX(IntegerCol
22、umn) from #Tempselect MIN(VarCharColumn),MAX(VarCharColumn) from #Temp因为VarCharColumn中值的存储类型为字符类型，而不是数字，所以结果以每个字符的ASCII值为顺序从左到右排序。这就是12比其他值小、而4比其他值大的原因。SUM()函数SUM()函数是最常用的聚合函数之一，它的功能很容易理解：和AVG()函数一样，它用于数值数据类型，返回一个列范围内所有非空值的总和。配置变量配置变量不是函数，不过它们的用法和系统函数相同。每个全局变量都能够返回SQL Server执行环境的标量信息。以下是一些常见的例子。ERRO
23、R变量这个变量包含当前连接发生的最后一次错误的代码。在执行的语句没有错误时，ERROR变量的值是0。出现标准错误时，错误是由数据库引擎引发的。所有的标准错误代码与消息都保存在sys.messages系统视图中，可以使用如下脚本查询：SELECT * FROM sys.messages定制错误可以通过调用RAISERROR语句来手动引发，并调用sp_addmessage系统存储过程将其添加到sysmessages表中。以下是一个ERROR变量的简单例子。先试着将一个数除以0，数据库引擎会引发标准错误号为8134的错误。注意查看Results选项卡中的查询结果。在发生错误时，Management
24、Studio的Messages选项卡将默认显示在Results选项卡的上面：SELECT 5 / 0SELECT ERROR在成功检索ERROR的值后，ERROR的值将返回0，因为ERROR只保存了上次执行的语句的错误代码。如果希望检索更多的错误信息，可以使用如下脚本从sysmessages视图中得到：SELECT 5 / 0SELECT * FROM master.dbo.sysmessages WHERE error = ERROR本节的后面部分内容将说明如何通过使用错误函数来更高效地返回错误数据。除了美国英语之外，SQL Server还默认安装了其他语言。每种语言专用的错误消息都有一个语
25、言标识符(mslangid)，对应于syslanguages表中的一种语言，如下图所示。errorseveritydleveldescriptionmsglangid8134160Divide by zero error encountered.10338134160Fehler aufgrund einer Division durch Null.10318134160Division par zro.103681341600 除算発生。10418134160Error de divisin entre cero.30828134160Errore di divisione per zero
26、.10408134160 : .10498134160Erro de diviso por zero.10468134160發現除以零的錯誤。102881341600 .10428134160遇到以零作除数错误。2052属性名mslangid被非正式地定义为Microsoft Global Language Identifier。微软公司用这个标识符来标识一种语言或语言和国家的组合，微软公司把语言和国家的组合定义为地区。例如，在随SQL Server安装的英语中，美国英语的mslangid是1033，英国英语的mslangid是2057。要检索出所有已安装的、支持的语言，可以执行下面的查询：S
27、ELECT alias, name, msglangidFROM sys.syslanguagesSERVICENAME变量这个变量是用于执行和维护当前SQL Server实例的Windows服务名。它通常返回SQL Server默认实例MSSQLSERVER，但SQL Server的指定实例有唯一的服务名。例如在名为WoodVista的计算机上有两个SQL Server实例：默认实例和指定实例AughtEight。如在默认实例上检索SERVICENAME全局变量的内容，将返回MSSQLSERVER，但在指定实例上检索，会返回AUGHTEIGHT。TOTAL_ERRORS变量这个变量用于记录从
28、打开当前连接开始发生的总错误次数。和ERROR变量一样，它对每个用户会话是唯一的，并将在连接关闭时被重置。TOTAL_READ变量这个变量记录从打开当前连接时开始计算的磁盘读取总数。DBA使用这个变量查看磁盘读取活动的情况。VERSION变量这个变量包含当前SQL Server实例的完整版本信息。SELECT VERSION比如，对于运行在Windows 7上的SQL Server 2008开发版实例，以上脚本能够返回如下信息:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul9 2008 14:43:34 Copyri
29、ght (c) 1988-2008 Microsoft CorporationEnterprise Edition on Windows NT 6.1 (Build 7600: )实际的版本号是一个简单的整型值，它在微软公司内部使用。而发行的产品可能有其他的商标名。在本例中，SQL Server 2005的版本是9，SQL Server 2008的版本是10。Windows XP Professional显示为Windows NT 5.l版，而Vista显示为6.0版。构建号用于内部控制，反映beta版和预览版以及正式发行后的补丁包的变化。错误函数前面学习了如何使用ERROR全局变量来检索错误信息。而返回所有错误数据的更好方法是使用错误函数。这些函数返回的信息可以存储在错误跟踪表中，以供错误审核。错误函数嵌套在错误处理例程中。第11章将详细讨论错误处理，其实通过使用嵌套在TRY和END TRY语句中的代码块，后跟一个放在CATCH和END CATCH语句中的代码块就可以实现错误处理。-Try to do somethingBEGIN TRY SELECT 5 / 0END TRY-If it causes an error, do thisBEGIN CATCH PRINT E
