T-SQL-内置函数(Built-in Functions)

dba 发布于 2024-01-02 阅读(32)

内置函数说明(FUNCTION)

Sever 提供了众多功能强大、方便易用的函数。使用这些函数,可以极大地提高数据库的管理。SQL Server中的函数从功能方面主要分为以下几类:字符串函数、数学函数、数据转换函数、文本和图像函数、日期和时间函数、系统函数等。

内置函数分类

聚合函数、数学函数、字符串函数、日期函数、转换函数、其他函数
image

聚合函数

说明

对一组值进行计算返回一个单一的值
除了COUNT函数,其他聚合函数都会忽略NULL
通常和GROUP BY 一起使用

常用聚合函数:
image

COUNT_BIG()与COUNT类似,但COUNT返回INT类型,COUNT_BIG返回BIGINT类型

其他聚合函数
image

SUM#

说明:返回表达式中所有值的和或非重复值的和

SUM([ALL | DISTINCT] expression)

注意:
会忽略null值
ALL是默认参数,可以使用DISTINCT去除重复值
如果字段使用了索引,将会加快该函数执行
支持所有数值类型,会把smallint和tinyint当作int类型处理

AVG#

说明:返回表达式中数值的平均值

AVG([ALL | DISTINCT] expression)

注意:
会忽略null值
ALL是默认选项,可以使用DISTINCT去除重复值
支持所有数值类型,会把smallint和tinyint当作int类型处理
返回值以参数类型不同而不同

image

MAX#

说明:返回表达式中的最大值

MAX([ALL | DISTINCT] expression)

注意:
会忽略null值
ALL是默认选项,可以使用DISTINCT去除重复值(对MAX无效)
MAX不可以用于bit、TEXT、IMAGE类型字段
如果表达式所有值都是null,则返回null,否则会忽略null值

MIN#

说明:返回表达式中的最小值

MIN([ALL | DISTINCT] expression)

注意:
会忽略null值
ALL是默认选项,可以使用DISTINCT去除重复值(对MIN无效)
MIN不可以用于bit类型字段
如果表达式所有值都是null,则返回null,否则会忽略null值

COUNT#

说明:返回表达式的项数

COUNT([ALL | DISTINCT] expression)

注意:
会忽略null值
ALL是默认选项,可以使用DISTINCT去除重复值
COUNT返回INT数据类型的值
COUNT不可以用于在TEXT、IMAGE、NTEXT类型的列上
COUNT(*)不会忽略null值

DISTINCT#

说明:删除表达式中的重复值

DISTINCT()

STDEV#

所有值的标准偏差

STDEVP#

所有值的总体标准偏差

VAR#

所有值的方差

VARP#

所有值的总体方差

数学函数

说明#

数学函数主要用来处理数值数据,主要的数学函数有:绝对值函数、三角函数(包括正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等。在产生错误时,数学函数将会返回空值NULL。

ABS#

说明:返回数值表达式的绝对值

ABS()

注意:
返回值的类型就是参数的类型
如果参数为空,则ABS返回结果为空

PI#

说明:圆周率函数

PI()

POWER#

说明:返回幂运算

POWER(number,power)

参数:power表示乘方次数

RAND#

说明:返回0-1的FLOAT类型值随机数

RAND([seed])

实例:

SELECT CAST(RAND() * 100 AS INT) + 1 AS "1 to 100",  CAST(RAND()* 1000 AS INT) + 900 AS "900 to 1900",  CAST(RAND() * 5 AS INT)+ 1 AS "1 to 5";

ROUND#

说明:数值指定精度四舍五入

ROUND(number, length, [function])

参数:length表示小数个数精度
返回类型:同输入参数number的类型

SQUARE#

说明:返回数值的平方

SQUARE(number)

SQRT#

说明:返回数值的平方根

SQRT(number)

SIGN#

说明:返回参数的符号,的值为负、零或正时,返回结果依次为-1、0或1.
参数:
注意:

SIGN(x)

CEILING#

说明:
参数:
注意:

FLOOR#

说明:
参数:
注意:

SIN#

说明:三角正弦

SIN(float)

COS#

说明:三角函数

COS(float)

COT#

说明:三角余切

COT(float)

TAN#

说明:正切值

TAN(float)

字符串函数

说明#

字符串函数用于对字符和二进制字符串进行各种操作,它们返回对字符数据进行操作时通常所需要的值。大多数字符串函数只能用于char、nchar、varchar和nvarchar数据类型,或隐式转换为上述数据类型。某些字符串函数还可用于 binary 和 varbinary 数据类型。字符串函数可以用在SELECT 或者WHERE语句中。

ASCII#

说明:

ASCII(char)

返回值:返回的数据类型为INT类型

CHAR#

说明:
参数:
返回值:
注意:

CHAR(115)

NCHAR#

说明:
参数:
返回值:
注意:

UNICODE#

说明:
参数:
返回值:
注意:

CHARINDEX#

说明:返回指定表达式的起始位置

CHARINDEX(expression1,expression2,[start_location])

参数:expression1表示要被查找的字符串
参数:expression2表示要查找的字符串
参数:start_location表示搜索的起始位置,未指定或参数为负数则为0
返回值:int或bigint
注意:没有查找到返回整数0

LEFT#

说明:从左边取字符串N个字符

LEFT(expression,int)

返回值的类型依参数的类型而不同

说明:取右边字符串N个字符

RIGHT(expression, int)

返回值的类型依参数的类型而不同s

LOWER#

说明:字符串转为小写

LOWER(expression)

参数:
返回值:
注意:

UPPER#

说明:字符串转为大写

UPPER(expression)

参数:
返回值:
注意:

LEN#

说明:返回字符串表达式中的字符数

LEN(expression)

注意:不包含尾随空格
注意:返回的字符数,不是字节数

DATALENGTH#

说明:返回字符串表达式中的字节数

DATALENGH(expression)

注意:不包含尾随空格
注意:返回的字节数

TRIM#

SQL Server 2017起可用

TRIM(<string>)

LTRIM#

说明:删除左边起始空格

LTRIM(expression)

参数:字符串
返回值:删除空格后的字符串
注意:

RTRIM#

说明:删除右边起始空格

RTRIM(expression)

参数:字符串
返回值:删除空格后的字符串
注意:

REPLICATE#

说明:指定字符串重复N次

REPLICATE(string_expression, interger)

SPACE#

说明:返回指定个数的空格

SPACE(integer)

参数:
返回值:
注意:

REPLACE#

说明:用一个字符串替换指定字符串

REPLACE(expression, search_pattern, replacement_string)

REVERSE#

说明:反转字符串

REVERSE(expression)

SUBSTRING#

说明:截取指定长度的字符串

SUBSTRING(expression, start_location, length)

返回值:根据参数的不同,返回值的类型不同
image

STRING_SPLIT#

说明:将字符串分割为列

STRING_SPLIT(str_val)

实例:

SELECT valueFROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10',',');

结果:
image

实例:

SELECT valueFROM STRING_SPLIT('dog cat fish bird lizard',' ');

结果:
image

STRING_AGG#

说明:将选中的多个列合并到一个行中

STRING_AGG(ColumnName, ConcatString)

实例:

SELECT STRING_AGG(Name, ', ') AS ListFROM Production.ProductCategory;

STUFF#

说明:删除指定长度的字符串并且在指定位置插入字符串

STUFF(被处理字符串,截取开始位置,截取长度,插入的字符串)

返回值:字符串类型

PATINDEX#

说明:获得字符串在另一个字符串中的起始位置

PATINDEX(查找字符串,被查找字符串)

参数:
返回值:
注意:PATINDEX支持使用通配符查找,比如:PATINDEX(‘%BC%’,’ABCD’);

STR#

说明:数值转为字符串

STR(float_expression,[length],[decimal])

参数:float_expression表示要被转换的浮点数
参数:length,表示转换后的长度,包括小数点、符号以及空格,默认为10
参数:decimal表示小数点后的位数,必须小于等于16

COALESCE#

说明:返回第一个不是NULL的值

COALESCE(<value1>,<value2>,...,<valueN>)

CHOOSE#

select a value in an array based on an index

CHOOSE ( index, val_1, val_2 [, val_n ] )

注意:基于1开始

实例:结果’d’

SELECT CHOOSE (4, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i');

日期和时间

说明#

日期和时间函数主要用于处理日期和时间值,本节将介绍各种日期和时间函数的功能和用法。一般的日期函数除了使用 date类型的参数外,也可以使用 datetime 类型的参数,但会忽略这些值的时间部分。相同的,以time类型值为参数的函数,可以接受 datetime 类型值的参数,但会忽略日期部分。

GETDATE#

说明:返回系统当前日期时间

GETDATE()

GETUTCDATE#

说明:返回当前国际标准日期时间

GETUTCDATE()

SYSDATETIME#

说明:返回系统当前日期时间

SYSDATETIME()

返回:datetime2(7)类型

实例:

SELECT SYSDATETIME()

YEAR#

说明:获得日期字符串中’年’部分

YEAR(date)

MONTH#

说明:获得日期字符串中’月’部分

MONTH(date)

DAY#

说明:获得日期字符串中’日’部分

DAY(date)

DATEDIFF#

说明:获得日期时间的时间差

DATEDIFF(datepart,start_date,end_date)

参数:datepart表示结果的单位,可取值如下:
image

实例:

SELECT DATEDIFF(year,'2008-12-31','2009-01-01') AS YearDiff
Select DATEDIFF(d,'2008-12-31','2009-01-01') AS DayDiff;

DATEADD#

说明:添加日期时间

DATEADD(datepart,number,date)

参数:datepart表示要与数值相加的日期部分参数,可取值如下:
image

参数:number表示与datepart相加的值(包含小数将被忽略)
参数:date表示参与运算的日期时间

实例:

SELECT DATEADD(month,1,'2009-01-29') AS FebDate;

DAYNAME#

说明:获得日期时间字符串中指定的部分的字符串值
参数:
返回值:字符串类型
注意:datepart可取值部分如下表

DATENAME(datepart,date)

image

DATEPART#

说明:获得日期时间字符串中指定的部分的数值值

DATEPART(datepart,date)

返回值:数值类型

CURRENT_TIMESTAMP#

当前日期时间

FORMAT#

FORMAT(value, format [, culture ])

Format支持
image

实例:

SELECT FORMAT( GETDATE(), 'dd', 'en-US' ) AS Result;
SELECT FORMAT( GETDATE(), 'MM/dd/yyyy', 'en-US' ) AS Result;

DATEFROMPARTS#

将数值拼接为字符串

实例:

SELECT DATEFROMPARTS(2012, 3, 10) AS RESULT;
SELECT TIMEFROMPARTS(12, 10, 32, 0, 0) AS RESULT;
SELECT DATETIME2FROMPARTS (2012, 3, 10, 12, 10, 32, 0, 0) AS RESULT;

EOMONTH#

返回一个月最后的一天日期

实例:

SELECT EOMONTH(GETDATE()) AS [End of this month],
  EOMONTH(GETDATE(),1) AS [End of next month],
  EOMONTH('2020-01-01') AS [Another month];

获得:
image

转换函数

说明#

在同时处理不同数据类型的值时,SQL Server一般会自动进行隐式类型转换,这种隐式类型。转换对于数据类型相近的数值是有效的,比如int和float,但是对于其他数据类型,例如整数类型和字符数据类型,这种隐式转换就无法实现了,此时必须使用显式转换。为了实现这种转换,Transact-SQL提供了两个显式转换的函数,分别是CAST()函数和CONVERT()函数。

转换分为两种:隐式转换 和 显式转换,显式转换需要使用CAST函数和CONVERT函数。

CAST(expression AS data_type)#

说明:用于将某种数据类型显式转为另一种数据类型

CAST(expression AS data_type)

实例:

SELECT CAST('1314.1314' AS FLOAT(2));

CONVERT(data_type[(Length)],expression,[style]);#

说明:用于将某种数据类型显式转为另一种数据类型

CONVERT(data_type[(Length)],expression,[style]);

Style用于定义转换后的样式,不同的数据类型不同:
image

image

实例:

SELECT CONVERT(VARCHAR,GETDATE(),111)

排序函数

ROW_NUMBER()#

说明:为每条结果条件一个递增的顺序数值列

ROW_NUMBER() OVER (ORDER BY 列名 ASC|DESC)

实例:

SELECT ROW_NUMBER() OVER (ORDER BY name) AS 'ROW',id,nameFROM panda_table;

结果:
image

RANK()#

说明:和ROW_NUMBER()类似,但是如果排序值相同的情况下,生成的值是相同的。如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。例如,如果两名学生
具有相同的s_score值,则他们将并列第一。由于已有两行排名在前,因此具有下一个最高

实例:使用RANK(函数对根据s_id字段查询的结果进行分组排序

SELECT RANK() OVER (ORDER BY s id ASC) AS RankID,s id,f nameFROM fruits;

系统函数

COL_LENGTH#

说明:返回列的定义长度

COL_LENGTH('table_name','column_name')

COL_NAME#

说明:返回列名

COL_NAME(table_id,column_id)

DATALENGTH#

说明:获得表达式的实际长度

DATALENGTH(expression);

实例:

DECLARE @panda INT;SELECT @panda = 123;SELECT DATALENGTH(@panda); //4

DB_ID()#

说明:获得数据库的编号

DB_ID([dtabase_name])

注意:如果不提供参数,将返回当前数据库的编号
实例:

SELECT DB_ID();

DB_NAME()#

说明:获得当前数据库的名称

SELECT DB_NAME() AS "Database Name"

HOST_ID()#

说明:获得当前服务器端的计算机标识号

HOST_ID();

实例:

SELECT HOST_ID(), HOST_NAME();

HOST_NAME()#

说明:获得当前服务器的计算机名

HOST_NAME();

实例:

SELECT HOST_ID(), HOST_NAME();

DB_NAME()#

说明:获得数据库名
注意:如果不提供参数,将返回当前数据库名

DB_NAME([database_id])

NEWID()#

说明:获得一个GUID

SELECT NEWID();

ISNULL()#

说明:如果第一个参数为NULL,则返回第二个参数

SELECT ISNULL(NULL,'Panda');

USER_ID()#

说明:获得指定用户名的用户ID

USER_ID(user_name)

USER_NAME()#

说明:获得用户名

USER_NAME([user_id])

实例:

SELECT USER_NAME() AS "User Name",

CURRENT_USER#

获得当前用户

SELECT CURRENT_USER AS "Current User"

SUSER_NAME()#

SUSER_NAME()

实例:

SELECT SUSER_NAME()

SUSER_SID()#

说明:获得当前用户的安全标识号(SID,Security Identification Number)
返回值:INT类型

SUSER_SID(user_name)

实例:

SELECT SUSER_SID('sa');

SUSER_SNAME()#

说明:获得当前用户的用户名
返回值:字符串类型

SUSER_SNAME([user_id]);

实例:

SELECT SUSER_SNAME(SUSER_SID('sa'));

APP_NAME()#

获得当前连接数据库的应用名称
实例:

SELECT APP_NAME() AS "App Name";

OBJECT_ID()#

说明:获得数据库中的对象Id

OBJECT_ID('object_name')

OBJECT_NAME()#

说明:获得数据库中对象的名称

OBJECT_NAME(object_id)

实例:

NEWID()#

说明:创建新GUID
参数:
返回值:
注意:

SELECT NEWID();

GETANSINULL()#

返回当前数据库默认的NULL值。GETANSINULLO函数对ANSI空值NULL返回1;如果没有定义ANSI空值,则返回0。

GETANSINULL(database_name)

实例:

SELECT GETANSINULL('test db')