1、聚合函数

聚合函数常用于GROUP BY子句，用于聚合分组的数据。所有聚合函数均为确定性函数，也就是说只要使用一组特定输入值调用聚合函数，该函数总是返回同类型的值。例如，计算一组整数型数值的总和或者平均值，结果将同样会返回整数型的数值。

该函数在和GROUP BY子句一起使用时显示出其强大功能，但聚合函数的使用也不是只限于分组查询。如果查询语句中使用了聚合函数，而没有用GROUP BY子句，则聚合函数是用于聚合整个结果集（匹配WHERE子句的所有行）。例如，不使用GROUP BY子句，SELECT列表中AVG只能和SUM对应，但不能对应特定列。

SQL Server 2008中提供了大量的聚合函数，表4-8中列出了一些常用聚合函数。

表4-8  聚合函数

函数名称

含义

AVG

返回组中各值的平均值，如果为空将被忽略

CHECKSUM

用于生成哈希索引，返回按照表的某一行或一组表达式计算出来的校验和值

CHECKSUM_AGG

返回组中各值的校验和，如果为空将被忽略

COUNT

返回组中项值的数量，如果为空也将计数

COUNT_BIG

返回组中项值的数量。与COUNT函数唯一的差别是他们的返回值。COUNT_BIG始终返回bigint数据类型值。COUNT始终返回int数据类型值

GROUPING

当行由CUBE或ROLLUP运算符添加时，该函数将导致附加列的输出值为1；当行不由CUBE或ROLLUP运算符添加时，将导致附加列的输出值为0

MAX

返回组中值列表的最大值

MIN

返回组中值列表的最小值

SUM

返回组中各值的总和

STDEV

返回指定表达式中所有值的标准偏差

STDEVP

返回指定表达式中所有值的总体标准偏差

VAR

返回指定表达式中所有值的方差

VARP

返回指定表达式中所有值的总体方差

 附：

在SQL Server 2008提供的所有聚合函数中，除了COUNT函数以外，聚合函数都会忽略空值。

 

2、数学函数

数学函数用于对数字表达式进行数学运算并返回运算结果。在SQL Server 2008中，数学函数可以对系统提供的数字数据进行运算：decimal、integer、float、real、money、smallmoney、smallint和tinyint。默认情况下，对float数据类型数据的内置运算的精度为六个小数位数。SQL Server提供了20多个用于处理整数与浮点值的数学函数。下面表4-9列出了部分常用的数学函数。

表4-9  数学函数

函数

描述

ABS

返回数值表达式的绝对值

EXP

返回指定表达式以e为底的指数

CEILING

返回大于或等于数值表达式的最小整数

FLOOR

返回小于或等于数值表达式的最大整

LN

返回数值表达式的自然对数

LOG

返回数值表达式以10为底的对

POWER

返回对数值表达式进行幂运算的结果

ROUND

返回舍入到指定长度或精度的数值表达式

SIGN

返回数值表达式的正号(+)、负号(-)或零(0)

SQUARE

返回数值表达式的平方

SQRT

返回数值表达式的平方根

例如，下面创建一个使用ROUND函数的示例，具体代码如图所示：


SELECT 
ROUND(12345.34567,2)精确小数点后2位,
ROUND(12345.34567,-2)精确小数点前2位
GO

 

数学函数（例如 ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS 和 SIGN）返回与输入值具有相同数据类型的值。三角函数和其他函数（包括 EXP、LOG、LOG10、SQUARE 和 SQRT）将输入值转换为float并返回float值。

3、字符串函数

字符串函数用于计算、格式化和处理字符串参数，或将对象转换为字符串。与数学函数一样，SQL Server 2008为了方便用户进行字符型数据的各种操作和运算提供了功能全面的字符串函数。字符串函数也是经常使用的一种函数，常见的字符串函数如表4-10所示。

表4-10  字符串函数

字符串函数

描述

ASCII

ASCII函数，返回字符表达式中最左侧的字符的ASCII代码值

CHAR

ASCII代码转换函数，返回指定ASCII代码的字符

LEFT

左子串函数，返回字符串中从左边开始指定个数的字符

LEN

字符串函数，返回指定字符串表达式的字符（而不是字节）数，其中不包含尾随空格

LOWER

小写字母函数，将大写字符数据转换为小写字符数据后返回字符表达式

LTRIM

删除前导空格字符串，返回删除了前导空格之后的字符表达式

REPLACE

替换函数，用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项

REPLICATE

复制函数，以指定的次数重复字符表达式

RIGHT

右子串函数，返回字符串中从右边开始指定个数的字符

RTRIM

删除尾随空格函数，删除所有尾随空格后返回一个字符串

SPACE

空格函数，返回由重复的空格组成的字符串

STR

数字向字符转换函数，返回由数字数据转换来的字符数据

SUBSTRING

子串函数，返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分

UPPER

大写函数，返回小写字符数据转换为大写的字符表达式

4、日期和时间函数

SQL Server 2008提供了9个日期和时间处理函数。其中的一些函数接受datepart变元，这个变元指定函数处理日期与时间所使用的时间粒度。表5-11列出了datepart变元的可能设置。

表4-11  SQL Server datepart常量

常量

含义

常量

含义

yy或yyyy

年

dy或y

年日期（1到366）

qq或q

季

dd或d

日

mm或m

月

Hh

时

wk或ww

周

mi或n

分

dw或w

周日期

ss或s

秒

ms

毫秒

 

 

在表4-12中列出了SQL Server 2008提供的9个日期和时间函数。

表4-12日期和时间函数

日期函数

描述

DATEADD

返回给指定日期加上一个时间间隔后的新datetime值。

DATEDIFF

返回跨两个指定日期的日期边界数和时间边界数。

DATENAME

返回表示指定日期的指定日期部分的字符串。

DATEPART

返回表示指定日期的指定日期部分的整数。

DAY

返回一个整数，表示指定日期的天DATEPART部分。

GETDATE

以datetime值的SQL Server 2008标准内部格式返回当前系统日期和时间。

GETUTCDATE

返回表示当前的UTC时间（通用协调时间或格林尼治标准时间）的datetime值。当前的UTC时间得自当前的本地时间和运行Microsoft SQL Server 2008实例的计算机操作系统中的时区设置。

MONTH

返回表示指定日期的“月”部分的整数。

YEAR

返回表示指定日期的年份的整数。

上述日期函数中，DATENAME、GETDATE和GETUTCDATE具有不确定性。而DATEPART除了用作DATEPART(dw,date)外都具有确定性。其虽dw是weekday的日期部分，取决于设置每周的第一天的SET DATEFIRST所设置的值。除此之外的上述日期函数都具有确定性。

5、自定义函数

除了使用系统函数外，用户还可以创建自定义函数，以实现更独特的功能。自定义函数可以接受零个或多个输入参数，其返回值可以是一个数值，也可以是一个表，但是自定义函数不支持输出参数。在SQL Server 2008中，使用CREATE FUNCTION语句来创建自定义函数，根据函数返回值形式的不同，可以创建三类自定义函数，分别是标量值自定义函数、内联表值自定义函数和多语句表值自定义函数。

1．标量值函数

标量值自定义函数返回一个确定类型的标量值，其返回的值类型为除text、ntext、image、cursor、timestamp和table类型外的其他数据类型。也就是说，标量值自定义函数返回的是一个数值。

标量值自定义函数的语法结构如下所示：


CREATE FUNCTION function_name
([{@parameter_name scalar_parameter_data_type [ = default ]}[,…n]])
RETURNS scalar_return_data_type
[WITH ENCRYPTION]
[AS]
BEGIN
  function_body
  RETURN scalar_expression
END


语法中各参数含义如下：

l   function_name  自定义函数的名称；

l   @parameter_name  输入参数名；

l   scalar_parameter_data_type  输入参数的数据类型；

l   RETURNS scalar_return_data_type  该子句定义了函数返回值的数据类型，该数据类型不能是text、ntext、image、cursor、timestamp和table类型；

l   WITH  该子句指出了创建函数的选项。如果指定了ENCRYPTION参数，则创建的函数是被加密的，函数定义的文本将以不可读的形式存储在syscomments表中，任何人都不能查看该函数的定义，包括函数的创建者和系统管理员；

l   BEGIN…END  该语句块内定义了函数体（function_body），以及包含RETURN语句，用于返回值。

了解了语法格式及参数含义之后，下面来创建一个标量值函数，他使用一个整形参数指定订单号，返回该订单的客户的姓名。


CREATE FUNCTION GetName4(@id INT)
RETURNS varchar(50)
AS
BEGIN
DECLARE @Name varchar(50)
SELECT @Name=(SELECT B.客户名称
FROM 场馆预订信息A INNER JOIN  客户信息B
ON A.场馆编号=B.客户编号
WHERE 订单号=@id
)
RETURN @Name
END


执行上述语句后在【教务管理系统】数据库中创建一个名称为GetName的标量值函数，在查询中调用该函数，具体的代码及结果如图5-10所示。

图4-13  使用自定义标量值函数

2．内联表值函数

内联表值函数以表的形式返回一个返回值，即他返回的是一个表。内联表值自定义函数没有由BEGIN…END语句块中包含的函数体，而是直接使用RETURN子句，其中包含的SELECT语句将数据从数据库中筛选出来形成一个表。使用内联表值自定义函数可以提供参数化的视图功能。

内联表值自定义函数的语法结构如下所示：


CREATE FUNCTION function_name
([{@parameter_name scalar_ parameter_data_type [ = default ]}[,…n]])
RETURNS TABLE
[WITH ENCRYPTION]
[AS]
RETURN (select_statement)


该语法结构中各参数的含义与标量值函数语法机构中参数含义相似。

例如创建一个内联表值函数来返回一个管理员负责的所有场馆信息，代码如下所示：

CREATE FUNCTION GetPalaestra(@Pid INT)
RETURNS TABLE
AS
RETURN
(
SELECT A.场馆名称,A.座位,A.状态,B.管理员名称
FROM 场馆信息A INNER JOIN 管理员信息B
ON A.管理员编号=B.管理员编号
WHERE B.管理员编号=@Pid
)


这里创建的函数名称为GetPalaestra，他的字符串参数@Pid指定要查询的班级编号，RETURNS TABLE指定这是一个内联表值函数。创建完成后，使用SELECT语句来查看管理员编号为102所负责的所有场馆信息，如图5-11所示。

图4-14  使用内联表值函数

3．多语句表值函数

多语句表值自定义函数可以看作标量型和内联表值型函数的结合体。该类函数的返回值是一个表，但他和标量值自定义函数一样，有一个用BEGIN…END语句块中包含起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，他可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值自定义函数的不足。