Loading

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

更新记录
转载请注明出处:
2022年8月1日 发布。
2022年7月2日 从笔记迁移到博客。

内置函数说明(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 value
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10',',');

结果:
image

实例:

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

结果:
image

STRING_AGG

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

STRING_AGG(ColumnName, ConcatString)

实例:

SELECT STRING_AGG(Name, ', ') AS List
FROM 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,name
FROM panda_table;

结果:
image

RANK()

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

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

SELECT RANK() OVER (ORDER BY s id ASC) AS RankID,s id,f name
FROM 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')
posted @ 2022-08-01 09:36  重庆熊猫  阅读(691)  评论(0编辑  收藏  举报