《SQL函数使用大全》由会员分享，可在线阅读，更多相关《SQL函数使用大全（84页珍藏版）》请在金锄头文库上搜索。
1、一旦成功地从表中检索出数据，就需要进一步操纵这些数据，以获得有用或有意义的结果。这些要求包括：执行计算与数学运算、转换数据、解析数值、组合值和聚合一个范围内的值等。下表给出了 T-SQL 函数的类别和描述。函数类别函数类别作用作用聚合函数执行的操作是将多个值合并为一个值。例如COUNT、SUM、MIN 和 MAX。配置函数是一种标量函数，可返回有关配置设置的信息。转换函数将值从一种数据类型转换为另一种。加密函数支持加密、解密、数字签名和数字签名验证。游标函数返回有关游标状态的信息。日期和时间函数可以更改日期和时间的值。数学函数执行三角、几何和其他数字运算。元数据函数返回数据库和数据库对象的属性
2、信息。排名函数是一种非确定性函数，可以返回分区中每一行的排名值。行集函数返回可在 Transact-SQL 语句中表引用所在位置使用的行集。安全函数返回有关用户和角色的信息。字符串函数可更改 char、varchar、nchar、nvarchar、binary和 varbinary 的值。系统函数对系统级的各种选项和对象进行操作或报告。系统统计函数返回有关 SQL Server 性能的信息。文本和图像函数可更改 text 和 image 的值。函数的组成函数的组成函数的目标是返回一个值。大多数函数都返回一个标量值(scalar value)，标量值代表一个数据单元或一个简单值。实际上，函数可以
3、返回任何数据类型，包括表、游标等可返回完整的多行结果集的类型。本章不准备讨论到这个深度，第 12 章将讲解如何创建和使用用户自定义函数，以返回更复杂的数据。函数己经存在很长时间了，它的历史比 SQL 还要长。在几乎所有的编程语言中，函数调用的方式都是相同的：Result=Function()Result=Function()在 T-SQL 中，一般用 SELECT 语句来返回值。如果需要从查询中返回一个值，就可以把 SELECT 当成输出运算符，而不用使用等号：SELECTSELECT Function()Function()一个论点一个论点对于 SQL 函数而言，参数表示输入变量或者值的占位
4、符。函数可以有任意个参数，有些参数是必须的，而有些参数是可选的。可选参数通常被置于以逗号隔开的参数表的末尾，以便于在函数调用中去除不需要的参数。在 SQL Server 在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的 CONVERT()函数例子中，数据类型的length 和 style 参数是可选的:CONVERTCONVERT (data-type(data-type (length),(length), expression,style)expression,style)可将它简化为如下形式，因为现在不讨论如何使用数据类型：CONVERT(date_type,CONVER
5、T(date_type, expression,style)expression,style)根据上面的定义，CONVERT()函数可接受 2 个或 3 个参数。因此，下列两个例子都是正确的：SELECT CONVERT(Varchar(20), GETDATE()SELECT CONVERT(Varchar(20), GETDATE(), 101)这个函数的第一个参数是数据类型 Varchar(20)，第 2 个参数是另一个函数 GETDATE()。GETDATE()函数用 datetime 数据类型将返回当前的系统日期和时间。第 2 条语句中的第 3 个参数决定了日期的样式。这个例子中的
6、101 指以 mm/dd/yyyy 格式返回日期。本章后面将详细介绍 GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如 GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。确定性函数确定性函数由于数据库引擎的内部工作机制，SQL Server 必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，
7、这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是 SQL Server 缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。以下这些函数是确定性的：AVG()(所有的聚合函数都是确定性的)CAST()CONVERT()DATEADD()DATEDIFF()ASCII()CHAR()SUBSTRING()以下这些函数与变量是非确定性的：GETDA
8、TE()ERRORSERVICENAMECURSORSTATUS()RAND()在函数中使用用户变量在函数中使用用户变量变量既可用于输入，也可用于输出。在 T-SQL 中，用户变量以符号开头，用于声明为特定的数据类型。可以使用 SET 或者 SELECT语句给变量赋值。以下的例子用于将一个 int 类型的变量MyNumber传递给 SQRT()函数：DECLARE MyNumber intSET MyNumber=144 SELECT SQRT(MyNumber)结果是 12，即 144 的平方根。用用 SETSET 给变量赋值给变量赋值以下例子使用另一个 int 型的变量MyResult，来
9、捕获该函数的返回值。这个技术类似于过程式编程语言中的函数调用样式，即把SET 语句和一个表达式结合起来，给参数赋值：DECLARE MyNumber int, MyResult intSET MyNumber = 144 - Assign the function result to the variable: SET MyResult = SQRT(MyNumber) - Return the variable value SELECT MyResult用 SELECT 给变量赋值使用 SELECT 的另一种形式也可以获得同样的结果。对变量要在赋值前要先声明。使用 SELECT 语句来替代
10、SET 命令的主要优点是，可以在一个操作内同时给多个变量赋值。执行下面的 SELECT 语句，通过 SELECT 语句赋值的变量就可以用于任何操作了。DECLARE MyNumber1 int, MyNumber2 int,MyResult1 int, MyResult2 intSELECT MyNumber1 = 144, MyNumber2 = 121 - Assign the function result to the variable: SELECT MyResult1 = SQRT(MyNumber1),MyResult2 = SQRT(MyNumber2) - Return th
11、e variable value SELECT MyResult1, MyResult2上面的例子首先声明了 4 个变量，然后用两个 SELECT 语句给这些变量赋值，而不是用 4 个 SELECT 语句给变量赋值。虽然这些技术在功能上是相同的，但是在服务器的资源耗费上，用一个 SELECT 语句给多个变量赋值一般比用多个 SET 命令的效率要高。将一个甚至多个值选进参数的限制是，对变量的赋值不能和数据检索操作同时进行。这就是上面的例子使用 SELECT 语句来填充变量，而用另外一个 SELECT 语句来检索变量中数据的原因。例如，下面的脚本就不能工作:DECLARE RestockName
12、varchar(50) SELECT ProductId ,RestockName = Name + : + ProductNumber FROM Production.Product这个脚本会产生如下错误:消息 141，级别 15，状态 1，第 2 行向变量赋值的 SELECT 语句不能与数据检索操作结合使用。在查询中使用函数在查询中使用函数函数经常和查询表达式结合使用来修改列值。这只需将列名作为参数传递给函数即可，随后函数将引用插入到 SELECT 查询的列的列表中，如下所示：SELECT Title, NationalIDNumber, YEAR(BirthDate) AS BirthY
13、ear FROM HumanResources.Employee在这个例子中，BirthDate 列的值被作为参数传递给 YEAR()函数。函数的结果是别名为 BirthYear 的列。嵌套函数嵌套函数我们需要的功能常常不能仅由一个函数来实现。根据设计，函数应尽量简单，用于提供特定的功能。如果一个函数要执行许多不同的操作，就变得复杂和难以使用。因此，每个函数通常仅执行一个操作，要实现所有的功能，可以将一个函数的返回值传递给另一个函数，这称为嵌套函数调用。以下是一个简单的例子：GETDATE()函数的作用是返回当前的日期与时间，但不能返回经过格式化的数据，因为这是 CONVERT()函数的功能。
14、要想同时使用这两个函数，可以把 GETDATE()函数的输出作为 CONVERT()函数的输入参数。SELECT CONVERT(Varchar(20), GETDATE(), 101)聚合函数聚合函数报表的典型用途是从全部数据中提取出代表一种趋势的值或者汇总值，这就是聚合的意义。聚合函数回答数据使用者的如下问题:上个月鸡雏的总销售量是多少?1924 岁之间的巴西男性在食品调味品上的平均支出是多少?上季度所有订单中从订购到运输的最长时间是多少?收发室里仍在工作的最老的员工是谁?聚合函数应用特定的聚合操作并返回一个标量值(单一值)。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累
15、积以及透视等表运算一起使用，生成数据分析结果。第 7 章将详细介绍这个主题，这里仅讨论简单 SELECT 查询中的一些常用函数。聚合函数不仅可用在 SELECT 查询中，还可以和标量输入值一起使用。那么，这样做的意义是什么呢？在下列代码中，将值 15 传递给下列聚合函数，每个函数的返回值都相同：SELECT AVG(15)说明平均值SELECT SUM(15) 说明和SELECT MIN(15) 说明 最小值SELECT MAX(15) 说明 最大值它们都返回 15。虽然，对同一个值求平均、求和、求最小值、求最大值，所得的结果还是那个值。如果对一个值计数，又会产生什么结果呢？SELECT CO
16、UNT(15) 得到的值是 1，因为函数只计数了一个值。现在做一些有意义的事。聚合函数只有在处理结果集合中的一组数据时才有意义。每个函数都处理某列的非空值。除非使用分组操作(详见第 7 章)，否则不能在同一个 SELECT 语句中既返回聚合的值，又返回常规的列值。AVG()AVG()函数函数AVG()函数用于返回一组数值中所有非空数值的平均值。例如，表 6-2 包含了体操成绩。表 6-2体操运动员体操运动员项项 目目成成 绩绩SaraSara跳马跳马9.259.25CassieCassie跳马8.75DelaneyDelaney跳马跳马9.259.25SammiSammi跳马8.05ErikaErika跳马跳马8.608.60SaraSara平衡木9.70CassieCassie平衡木平衡木9.009.00DelaneyDelaney平衡木9.25SammiSammi平衡木平衡
