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语句)]

参数说明:

1)形式参数的数据类型为系统的基本标量类型,不能为timestamp类型、用户定义数据类型和非标量类型

   (如cursor和table)。

2) TABLE关健字指定此函数返回一个表.

3)  函数返回select语句的结果。

当调用用户自定义函数时,必须提供函数名和参数,标量函数可以在SELECT语句中调用,或用EXEC语句执行调用,调用形式分别为:所有者名.函数名(实参1, 实参2, …实参n)

其中用EXEC语句调用时参数次序可与定义时不同,表型函数只能通过SELECT语句调用。

可有两种方法删除用户已定义的函数即用命令方式和界面方式,

   命令格式为:

DROP FUNCTION {[owner_name]function_name}[,…n]

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏xiaoxi666的专栏

Mybatis foreach标签含义

这种方式非常方便,我们只要把查询条件写出来,剩下的操作都由mysql来处理。而在实际场景中,为了减少底层耦合,我们一般不通过mysql中的子查询方式联表查询,而...

22110
来自专栏xingoo, 一个梦想做发明家的程序员

剑指OFFER之合并有序链表(九度OJ1519)

题目描述: 输入两个单调递增的链表,输出两个链表合成后的链表,当然我们需要合成后的链表满足单调不减规则。 (hint: 请务必使用链表。) 输入: 输入可能包含...

20080
来自专栏java小白

MySQL WHERE子句内使用正则表达式搜索

18550
来自专栏Rgc

sqlalchemy和flask-sqlalchemy几种分页操作

sqlalchemy中使用query查询,而flask-sqlalchemy中使用basequery查询,他们是子类与父类的关系 假设 page_index=1...

41770
来自专栏Python

SQLAlchemy中的自引用

SQLALCHEMY采用adjacency list pattern来表示类的自引用。 例如,对于类Node自引用: class Node(Base): ...

40450
来自专栏GreenLeaves

关于null的操作

空值     空值一般用NULL表示     一般表示未知的、不确定的值,也不是空格     一般运算符与其进行运算时,都会为空     空不与任何值相等   ...

21970
来自专栏吴伟祥

自定义template(Settings-->Live Templates)

psvm=public static void main(String[] args) {}

9620
来自专栏IT杂记

Mapreduce程序中reduce的Iterable参数迭代出是同一个对象

今天在对reduce的参数Iterable进行迭代时,发现一个问题,即Iterator的next()方法每次返回的是同一个对象,next()只是修改了Writa...

20950
来自专栏与神兽党一起成长

[MySQL]查询学生选课的情况(一)

这是我工作遇到的问题,现在自己设计一个简化的类似场景,现实中这样的数据表设计可能有很多不合理的地方。 首先看表结构:

38450
来自专栏猿人谷

Mysql字符串截取总结:left()、right()、substring()、substring_index()

在实际的项目开发中有时会有对数据库某字段截取部分的需求,这种场景有时直接通过数据库操作来实现比通过代码实现要更方便快捷些,mysql有很多字符串函数可以用来处理...

29850

扫码关注云+社区

领取腾讯云代金券