sql server 使用函数辅助查询
函数是所有语言系统下都具备的内部数据处理过程，SQL SERVER也同样内置了许多函数。在SQL SERVER中，函数是由一个或多个T-SQL语句组成的子程序。利用函数可以简化数据的处理操作。
函数分为内置函数和用户定义函数两种。用户定义函数接受零个或多个输入参数，并返回标量值或表。
一、数据类型转换函数
1、CAST(expression, AS date_type)
将表达式值转换为指定的数据类型。
例如：
SELECT CAST ('2015-10-15' AS datetime)
SELECT CAST(GETDATE() AS char)
SELECT CAST('123' AS int)
2、CONVERT(date_type[(length)], expression[,style])
与CAST函数相似，
date_type(length) 规定目标数据类型。
style 规定日期/时间的输出格式。
下表为日期型与字符型转换时 style的取值。
例如：
SELECT CONVERT (char, GETDATE(),101)
|
style取值 无世纪值 |
style取值 有世纪值 |
标准 |
输入/输出 |
|
0或100 |
默认值 |
mm dd yyyy hh:miAM(或)PM |
|
|
1 |
101 |
美国 |
mm/dd/yyyy |
|
2 |
102 |
ANSI |
mm dd yyyy hh:miAM(或)PM |
|
9或109 |
默认值＋毫秒 |
mm-dd-yy |
|
|
10 |
110 |
美国 |
yymmdd |
|
12 |
112 |
ISO |
二、 日期函数
1、GETDATE()
该函数返回当前系统日期时间。
例如：
SELECT GETDATE()
返回结果：2009-02-22 23:05:52.483
2 、DATEPART(datepart,date_expression)
返回日期表达式值的指定部分，
返回值为数值型数据。
例如：
SELECT DATEPART(YEAR,GETDATE())
SELECT DATEPART(MONTH,GETDATE())
SELECT DATEPART(DAY,GETDATE())
date型数据日期部分的可能取值:
|
datepart |
缩 写 |
说 明 |
|
year |
yy, yyyy |
年 |
|
quarter |
qq, q |
季度 |
|
month |
mm, m |
月 |
|
Day of year |
dy, y |
一年中的第几天 |
|
day |
dd, d |
一月中的第几天 |
|
week |
wk, ww |
一年中的第几周 |
|
hour |
hh |
小时 |
|
minute |
mi, n |
分 |
|
second |
ss, s |
秒 |
|
millisecond |
ms |
千分之一秒 |
3、DATENAME(datepart,date_expression)
该函数返回日期表达式值的指定部分的名称,
返回值为字符型数据。例如：
SELECT DATENAME (YEAR,GETDATE())
SELECT DATENAME(WEEKDAY, GETDATE())
4、DATEADD(datepart, interge_expression, date_expression )
该函数返回日期表达式值的指定部分， 加上整数表达式值后的日期时间。
SELECT DATEADD(day,10,GETDATE())
5、DATEDIFF(datepart, date_expression1, date_expression2)
该函数返回日期表达式1的值和日期表达式2的
值在指定部分的差值。例如：
1 DECLARE @t1 datetime,@t2 datetime 2 SET @t1=GETDATE() 3 WAITFOR delay '00:00:02' 4 SET @t2=GETDATE() 5 SELECT DATEDIFF(SECOND,@t1, @t2)
6、DAY(date_expression)
该函数返回日期表达式值的“日”部分。
例如：
SELECT DAY(GETDATE())
7、MONTH(date_expression)
该函数返回日期表达式值的“月”部分。
例如：
SELECT MONTH (GETDATE())
8、YEAR(date_expression)
该函数返回日期表达式值的“年”部分。
例如：
SELECT YEAR(GETDATE())
9、getutcdate
返回当前utc时间(世界标准时间)。
例如：
select getutcdate()
三、 聚合函数
1、COUNT([ALL | DISTINCT]expression | * )
2、AVG( ), MAX( ), MIN( ), SUM( )
3、VAR( ), VARP( ), STDEV( ), STDEVP( )
四、 数学函数
1、ABS(numeric_expression)
该函数返回表达式值（bit型除外）的绝对值，返回值的数据类型与原数据类型一致。
例如：
SELECT ABS(-3.0), ABS(2.0),ABS(0.0)
2、AVG([ALL|DISTINCT]numeric_expression)
该函数返回查询出的一组数据的平均值。
例如：
SELECT AVG(grade) from score where cno=1
3、COUNT([ALL | DISTINCT]expression | * )
该函数返回查询出的表达式数。
例如：
SELECT count(grade) from score where cno=1
4、CEILING(numeric_expression)
返回最小的大于或等于表达式值的整数值。
例如：
SELECT CEILING($99.99), CEILING($-99.99) ,CEILING($0.0)
5、FLOOR(numeric_expression)
返回最大的小于或等于表达式值的整数值。
例如：
SELECT FLOOR ($99.99), FLOOR($-99.99) , FLOOR($0.0)
6、RAND([integer_expression])
该函数返回一个位于0与1之间的随机数。表达式值作为产生随机数的起始值，返回值为浮点型数。
例如：
1 DECLARE @number smallint 2 SET @number=1 3 WHILE (@number<=3) 4 BEGIN 5 SELECT RAND(@number) 6 SET @number=@number+1 7 END 8 GO
7、ROUND(numeric_expression, int_expression1, [integer_expression2])
当 int_expression1 为正数时，numeric_expression 四舍五入为 int_expression1所指定的小数位数。
当 int_expression1为负数时，numeric_expression 则按 int_expression1所指定的位数在小数点的左边四舍五入.
当 int_expression2 非零时，表示用int_expression1表示的精度对numeric_expression进行截短。
SELECT ROUND（2456.12582,3） 返回结果为2456.12600。
SELECT ROUND（2456.12582,3,1） 返回值为2456.12500。
ROUND(748.58, -1) 返回值为750.00
ROUND(748.58, -2) 返回值为700.00
ROUND(748.58, -4) 返回值为0
五、 字符串函数
1、ASCII(chracter_expression)
返回字符的ASCII码值，返回值为整型数据。
例如：
SELECT ASCII('a'), ASCII(‘Z’) 返回结果为：97 90
2、CHAR（inter_expression）
该函数返回ASCII码值代表的字符。
例如：
SELECT CHAR(97), CHAR(90) 返回结果为：a Z
3、LEN(charater_expression)
该函数返回字符串的长度，即字符的个数，
注意1个汉字计为一个字符。
例如：
SELECT len('张三'), len('abc') 返回结果为：2 3
4、DATALENGTH(expression)
返回表达式所占用的字节数，
常用于查看变长数据类型的长度。
select datalengh(‘100’),datalength(100)
5、LEFT(chracter_expression, integer_expression)
返回字符串从左边开始指定个数的字符。
select LEFT('sdf',1)+LEFT('qlsdf',2)
6、RIGHT(chracter_expression, integer_expression)
返回字符串从右边开始指定个数的字符。
7、SUBSTRING(chracter_expression, begin_integer_expression, lenth_integer_expression )
返回字符串在起始位置开始的指定长度的子串。
例如：
SELECT SUBSTRING('traffic',3,4)
8、UPPER(chracter_expression)
该函数返回字符的大写形式。
例如：
SELECT upper('traffic’)
9、LOWER(chracter_expression)
该函数返回字符的小写形式。
10、SPACE(integer_expression)
该函数返回指定长度的空格字符串。
SELECT'放假'+SPACE(6)+'美呀！'
11、REPLICATE(chracter_expression, integer_expression)
该函数将字符串复制指定的遍数。
例如：
SELECT REPLICATE(‘SQL’,3) 返回结果为：SQLSQLSQL
12、STUFF(chracter_expression1, begin_integer_expression,length, chracter_expression2)
该函数将字符串1从开始位置到结束位置中的
字符删去然后将字符串2填充进去。
SELECT STUFF(‘SQlver’,3,1,’L Ser’)
13、REVERSE(chracter_expression)
该函数返回字符串的反序字符串。
SELECT REVERSE(‘SQL’)
14、LTRIM(chracter_expression)
该函数返回删除字符串左端空格后的字符串。
SELECT LEN(‘ SQL‘),LEN(LTRIM(‘ SQL‘))
15、RTRIM(chracter_expression)
该函数返回删除字符串右端空格后的字符串。
16、STR(float_expression[ integer_expression1[,integer_expression2]])
该函数返回浮点表达式值的字符串形式。
表达式1为字符串长度，表达式2为小数位数。
若无表达式2，默认为0；
若无表达式1，默认为浮点数的整数部分长度。
例如：
SELECT STR(123.456), STR(123.456,4,1),STR(123.456,6,4)
返回结果为：123 123 123.46
六、 判定函数
1、ISDATE(expression)
该函数判断表达式是否为一个合法的日期型
数据，是则返回1，否则返回0。
1 IF ISDATE('2009-05-12 10:19:41.177')=1 PRINT 'VALID' 2 ELSE 3 PRINT 'INVALID'
2、ISNUMERIC(expression)
该函数判断表达式是否为一个合法的数值型
数据（包括整数型、数值型和浮点型），
是则返回1，否则返回0。
SELECT ISNUMERIC(56.6)
SELECT ISNUMERIC('hello')
3、ISNULL(expression1,expression2)
该函数判断表达式1的值是否为NULL，
是则返回表达式2的值，
不是则返回表达式1的值。例如：
SELECT SNO,CNO,ISNULL(grade,0) FROM SCORE
4、NULLIF(expression1,expression2)
该函数判断表达式1的值是否与表达式2的值相等，是则返回NULL，否则返回表达式1的值。
SELECT NULLIF(‘ABc’,’AB’)
SELECT NULLIF(‘AB’,’AB’)
七、 用户自定义函数
1、标量值函数的定义
CREATE FUNCTION [所有者名.]函数名
([{形式参数[AS]类型[=默认值]}[,…n]])
RETURNS 返回值类型
[AS]
BEGIN
函数体
RETURN 标量表达
END
参数说明：
1) 形式参数的数据类型为系统的基本标量类型，不能为timestamp类型、用户定义数据类型和非标量类型
（如cursor和table）。
2) 返回值类型为系统的基本标量类型，但text、ntext、image和timestamp除外。
3) 函数体由T-SQL语句序列构成。
4) 函数返回标量表达式的值。
2、表值函数的定义
CREATE FUNCTION [所有者名.]
函数名([{形式参数[AS]类型[=默认值]}[,…n]])
RETURNS TABLE
[AS]
RETURN [(select语句)]
参数说明：
（如cursor和table）。
2) TABLE关健字指定此函数返回一个表.
3) 函数返回select语句的结果。
当调用用户自定义函数时，必须提供函数名和参数，标量函数可以在SELECT语句中调用，或用EXEC语句执行调用，调用形式分别为：所有者名.函数名（实参1, 实参2, …实参n）
其中用EXEC语句调用时参数次序可与定义时不同，表型函数只能通过SELECT语句调用。
可有两种方法删除用户已定义的函数即用命令方式和界面方式，
命令格式为：
DROP FUNCTION {[owner_name]function_name}[,…n]
