SQL Server的系统函数，可以让我们实现聚合、数学运算、字符串操作、日期运算、NULL的处理、排序等功能。
需要注意的是SQL Server的系统函数和用户自定义函数是不同的，系统函数是SQL Server内部支持的函数，不需要我们先定义，再使用。
我们通过Microsoft SQL Server Management Studio客户端工具，可以看到SQL Server提供的系统函数，如下图：
下面，通过实例，来说明如何使用SQL Server的内建函数：
1、SQL Server元数据函数
--1.列
--列的长度
select COL_LENGTH('dbo.wct', --表名
'wcid') --列名
--列名
select COL_NAME(object_id('dbo.wct'), --表ID
1) --列ID
/*============================================
列属性：
ColumnId
AllowsNull
Precision
Scale
IsComputed
IsSparse
IsCursorType
IsColumnSet
IsIdentity
IsRowGuidCol
FullTextTypeColumn
IsFulltextIndexed
IsIdNotForRepl
IsIndexable
IsOutParam
IsXmlIndexable
IsDeterministic
IsPrecise
IsSystemVerified
SystemDataAccess
UserDataAccess
UsesAnsiTrim
==============================================*/
--columnProperty函数返回有关列过程参数的信息
select ColumnProperty(object_id('dbo.wct'), --表ID
'wcid', --列名
'precision') --属性名
--2.数据库
--DatabasePropertyEx函数返回指定数据库中指定选项的属性，要返回更多可以用sys.databases目录视图
select DatabasePropertyEx('master', --数据库名
'IsAutoClose') --属性名
--db_id函数返回指定数据库名称对应的id
select db_id('master')
--db_name函数返回指定数据库id号的数据库名称
select db_name(1)
--3.文件与文件组
--file_id函数返回指定逻辑文件名对应的id,以后版本将删除此函数
select file_id('wc_fg5_1') --文件逻辑名
--建议采用此函数
select FILE_IDEX('wc_fg5_1')
--filegroup_id函数返回指定文件组名称对应的id
select filegroup_id('wc_fg5')
--filegroup_name函数返回指定文件组id对应的文件组名称
select filegroup_name(6)
--FilegroupProperty函数返回指定文件组中相应选项的属性，
--要返回更多可以用sys.filegroups目录视图
select FilegroupProperty('wc_fg5',
'IsUserDefinedFG') --是否是用户定义的文件组
select FilegroupProperty('wc_fg5',
'IsDefault') --是否是默认文件组
select FilegroupProperty('wc_fg5',
'IsReadOnly') --是否只读
--file_name函数返回指定逻辑文件对应的逻辑文件名称
select file_name(8)
--FileProperty函数返回指定逻辑文件相应选项的属性
select fileproperty('wc_data',
'IsPrimaryFile')
select fileproperty('wc_fg5_1',
'IsReadOnly')
select fileproperty('wc_fg5_1',
'SpaceUsed') --返回在文件中分配的页数
select fileproperty('wc_log1',
'IsLogFile') --是否日志文件
--4.对象
--object_id函数返回架构范围内对象的数据库对象id
select object_id('master.dbo.spt_values')
select object_id('master.dbo.spt_values','U')
--object_name函数返回架构范围内对象id对应的数据库对象名称
select object_name(1115151018)
--objectPropertyEx函数返回架构范围内对象相应选项的属性，返回更多可以用sys.objects系统目录
select ObjectPropertyex(object_id('spt_values'),
'IsTable')
select ObjectPropertyex(object_id('wct'),
'TableHasForeignKey')--有FOREIGN KEY约束的表
select ObjectPropertyex(object_id('wct'),
'TableUpdateTriggerCount')--update触发器的个数
select ObjectPropertyex(object_id('wct'),
'IsTrigger') --是否是触发器
select ObjectPropertyex(object_id('wct'),
'TableHasPrimaryKey') --对象是否有主键
--5.索引
--索引中指定键id对应的列名
select INDEX_COL('wc.dbo.wcT', --数据库.架构.表
1, --索引id
1) --索引中键的id
--索引键的属性
select INDEXKEY_PROPERTY(OBJECT_ID('wc.dbo.wcT'), --对象id
1, --索引id
2, --键id
'ColumnId') --对应到表中的列id
select INDEXKEY_PROPERTY(OBJECT_ID('wc.dbo.wcT'), --对象id
1, --索引id
2, --键id
'IsDescending') --是否降序
--索引属性
select INDEXPROPERTY(object_id('wc.dbo.wcT'),
'pk_wcid_date',
'IndexID') --索引id
select INDEXPROPERTY(object_id('wc.dbo.wcT'),
'pk_wcid_date',
'IsClustered') --是否聚集
select INDEXPROPERTY(object_id('wc.dbo.wcT'),
'pk_wcid_date',
'IsDisabled') --是否禁用
select INDEXPROPERTY(object_id('wc.dbo.wcT'),
'pk_wcid_date',
'IsRowLockDisallowed') --是否允许行级锁
select INDEXPROPERTY(object_id('wc.dbo.wcT'),
'pk_wcid_date',
'IsStatistics') --是否自动创建统计信息
select INDEXPROPERTY(object_id('perfetti.dbo.wcT'),
'idx_wc',
'IndexDepth') --索引深度
--6.类型AllowsNull
select TYPEPROPERTY('int', --类型名
'Precision') --类型属性
select TYPEPROPERTY('int', --类型名
'AllowsNull') --类型属性
--7.当前模块的对象ID,可以是存储过程,用户定义函数,触发器
select @@PROCID
--8.扩展属性
select * from ::fn_listextendedproperty(default,
'schema', --架构
'dbo', --架构名
'table', --表
'wcT', --表名
default, --列
default --列名
)
DECLARE @V SQL_VARIANT
SET @V = 'ABCEFG'
select SQL_VARIANT_PROPERTY(@V,
'BaseType')
select SQL_VARIANT_PROPERTY(@V,
'TotalBytes')
select SQL_VARIANT_PROPERTY(@V,
'MaxLength')
--9.服务器属性
select SERVERPROPERTY('Collation'),
SERVERPROPERTY('IsClustered'),
SERVERPROPERTY('IsSingleUser'),
SERVERPROPERTY('MachineName'),
SERVERPROPERTY('ProcessID'),
SERVERPROPERTY('NumLicenses'),
SERVERPROPERTY('SqlSortOrder')
--10.会话属性
select SESSIONPROPERTY('ANSI_NULLS'),
SESSIONPROPERTY('ANSI_WARNINGS'),
SESSIONPROPERTY('ANSI_PADDING'),
SESSIONPROPERTY('ARITHABORT'),
SESSIONPROPERTY('NUMERIC_ROUNDABORT'),
SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL'),
SESSIONPROPERTY('QUOTED_IDENTIFIER')
2、聚合函数
--基本聚合函数
SELECT MAX(wcId),
MIN(WCID),
COUNT(WCiD),
COUNT_BIG(wcId),
COUNT(*),
SUM(WCiD),
AVG(WCID)
FROM dbo.wct
use AdventureWorks
go
/*====================================================
x1,x2,...,xn共n个数，m是这n个数的平均数
var：square(x1-m)+square(x2-m)+...+square(xn-m)。
varp：var/n
stdev:sqrt(var)
stdevp:sqrt(varp)
======================================================*/
select VAR(taxAmt),
VARP(taxAmt),
STDEV(taxAmt),
STDEVP(taxAmt),
case when STDEV(taxAmt) = sqrt(VAR(taxAmt))
then 'equal'
else 'not equal'
end,
case when STDEVP(taxAmt) = sqrt(VARP(taxAmt))
then 'equal'
else 'not equal'
end,
checksum_agg(SalesOrderID) --返回组中各值的校验和
from sales.SalesOrderHeader
3、数学函数
SELECT ABS(-1), --绝对值
CEILING(1.56),--大于指定值的最小整数
FLOOR(1.56), --小于指定值的最大整数
RAND(10), --返回0~1之间的浮点数，如果要每次返回相同的数,可以指定seed
--如未指定seed，则SQL Server数据库引擎随机分配种子值
ROUND(28.9,0,0),--第三个参数指定0时表示四舍五入,1表示截断（不会进位）
ROUND(28.9,0,1),
LOG(2), --指定参数的对数
EXP(0.693147180559945), --指定参数的指数
LOG10(2), --指定参数的自然对数
POWER(2,10), --第一个参数对第二个参数的幂
SQUARE(2), --平方
SQRT(2), --开方
PI(), --PI常量3.1415926
DEGREES(PI()), --把弧度转为角
RADIANS(180.0), --把角度转为弧度,返回值的类型与输入参数类型相同
SIN(1), --指定角或弧度的正弦值
ASIN(0.841470984807897),--由参数所指定的正弦值,来计算其所对应的角或弧度
COS(1), --指定角或弧度的余弦值
ACOS(0.54030230586814), --由参数所指定的余弦值,来计算其所对应的角或弧度
TAN(1), --指定角或弧度的正切值
ATAN(1.5574077246549), --由参数所指定的正切值,来计算其所对应的角或弧度
--指定角或弧度的余切值
COT(1),
--返回以弧度表示的角，该角位于正 X 轴和原点至点 (y, x) 的射线之间，
--其中 x 和 y 是两个指定的浮点表达式的值。
ATN2(1,2)
4、字符串函数
--1.字符转化为ASCII,把ASCII转化为字符,注意返回的值是十进制数
select ASCII('A'),ASCII('B'),ASCII('a'),ASCII('b'),ASCII('?')
select CHAR(65),CHAR(66),CHAR(97),CHAR(98),CHAR(63)
--2.unicode字符转化为整数,把整数转化为unicode字符
select UNICODE('A'),UNICODE('B'),UNICODE('a'),UNICODE('b'),UNICODE('你')
select NCHAR(65),NCHAR(66),NCHAR(97),NCHAR(98),NCHAR(20320)
--3.根据英语发音规则来评价字符串的发音相似度
select SOUNDEX('Fleas'),
SOUNDEX('Fleece'),
SOUNDEX('Peace'),
SOUNDEX('Peas')
select DIFFERENCE('Fleece','Fleas') --返回0~4之间的值,返回值越大,那么越相似
--4.字符串大小写转换
select UPPER('the company is very large!'),
LOWER('THE CORPORATION IS VERY LARGE!')
--5.重复一个字符串N次
SELECT REPLICATE('wc ',
5)
--6.重复一个空格N次
select SPACE(20)
--7.获取字符串最左和最右部分
SELECT left('the company is very large!',11),
right('the company is very large!',6)
--8.去掉字符串最左，最右的空格
select RTRIM(' the company is very large!'),
LTRIM('the company is very large! ')
--9.1返回一个字符串在另一个字符串中的起始位置
select CHARINDEX('string to find',
'this is the bigger string to find something in.',
1
)
--9.2使用通配符查找,返回一个字符串在另一个字符串中的起始位置
select PATINDEX('%string%',
'this is the bigger string to find something in.'
)
--10.返回字符串的子串
select SUBSTRING('the company is very large!',
5,
3)
--11.返回字符串的字符个数,返回字符串的字节数
select len('the company is very large!'),
DATALENGTH(N'the company is very large!')
--12.把字符串的一部分替换成另一个字符串
select REPLACE('the company is very large!',
'company',
'corporation')
--13.把字符串中的一部分填充成另一个字符串
select STUFF('the company is very large!',
charindex('large','the company is very large!'),
LEN('large'),
'good'
)
--14.返回逆序的字符串
select REVERSE('the company is very large!')
--15.把数字转化成字符数据,会四舍五入
--要是总长不能满足小数点位数的要求,那么只显示整数部分
select STR(-123456.623,
7 --这个总长度包括小数点、负号,
)
select STR(123456.653,
8, --总长度,会四舍五入
2 --数值范围，小数点后面的位数
)
--16.为输入的UNICODE字符串增加分隔符
select QUOTENAME('abc','"') --分隔符"
select QUOTENAME('abc','''') --分隔符'
5、日期函数
--1.返回当前日期和时间
select GETDATE() '当前日期-精确到33毫秒'
select GETUTCDATE() 'UTC日期和时间-精确到33毫秒'
select SYSDATETIME() '当前日期和时间-精确到100纳秒(高精度)'
select SYSUTCDATETIME() 'UTC-精确到100纳秒(高精度)'
select SYSDATETIMEOFFSET()'当前日期与UTC之间的差值(时分)'
/*======================================================
2.转换偏移，从一个转为另一个,从UTC-05:00转为UTC+03:00:
'2007-08-12 10:20:35.9788989'是UTC减去'05:00'之后算出来的，
所以UTC是'2007-08-12 10:20:35.9788989'加'05:00'，
也就是'2007-08-12 15:20:35.9788989'，
然后'2007-08-12 15:20:35.9788989'加上'03:00'，
也就是'2007-08-12 18:20:35.9788989'
========================================================*/
select SWITCHOFFSET('2007-08-12 10:20:35.9788989 -05:00','+03:00')
--将普通的日期时间型转为日期时间偏移，只是在日期时间后增加了offset值
--返回:2007-08-12 10:20:35.0000000 -05:00
select TODATETIMEOFFSET('2007-08-12 10:20:35','-05:00')
--3.显示日期一部分的字符串
select SYSDATETIME(),
DATENAME(year,SYSDATETIME()),
DATENAME(quarter,SYSDATETIME()),
DATENAME(month,SYSDATETIME()),
DATENAME(dayofyear,SYSDATETIME()),
DATENAME(day,SYSDATETIME()),
DATENAME(week,SYSDATETIME()),
DATENAME(weekday,SYSDATETIME()),
DATENAME(hour,SYSDATETIME()),
DATENAME(minute,SYSDATETIME()),
DATENAME(second,SYSDATETIME()),
DATENAME(millisecond,SYSDATETIME()),
DATENAME(microsecond,SYSDATETIME()),
DATENAME(nanosecond,SYSDATETIME()),
DATENAME(TZoffset,SYSDATETIME()),
DATENAME(ISO_WEEK,SYSDATETIME())
--4.显示日期一部分的整数
select YEAR(GETDATE()),
MONTH(getdate()),
DAY(getdate())
select SYSDATETIME(),
DATEPART(year,SYSDATETIME()),
DATEPART(quarter,SYSDATETIME()),
DATEPART(month,SYSDATETIME()),
DATEPART(dayofyear,SYSDATETIME()),
DATEPART(day,SYSDATETIME()),
DATEPART(week,SYSDATETIME()),
DATEPART(weekday,SYSDATETIME()),
DATEPART(hour,SYSDATETIME()),
DATEPART(minute,SYSDATETIME()),
DATEPART(second,SYSDATETIME()),
DATEPART(millisecond,SYSDATETIME()),
DATEPART(microsecond,SYSDATETIME()),
DATEPART(nanosecond,SYSDATETIME()),
DATEPART(TZoffset,SYSDATETIME()),
DATEPART(ISO_WEEK,SYSDATETIME())
--5.增加、减少日期值
SELECT DATEADD(quarter, 1, getdate()),
DATEADD(quarter,-1, getdate())
--6.计算两个日期的差值
select DATEDIFF(DAY,'2011-08-09',GETDATE()),
DATEDIFF(MONTH,'2011-08-09',GETDATE())
6、处理NULL的函数
DECLARE @V1 INT,@V2 INT,@v3 numeric(10,0)
SELECT @V1 = 10,
@V2 = NULL,
@v3 = 0
SELECT ISNULL(@v1,0), --如果参数一为null,那么返回参数二,否则返回参数一
ISNULL(@v2,0),
coalesce(@V1,@v2,@v3), --返回第一个非NULL值
coalesce(@v2,@v3,@v1),
nullif(@V3,100), --如果参数一等于参数二，那么返回NULL,否则还是返回参数一
100 / nullif(@v3,0) --用来解决除数为0的问题
7、类型转化函数
--1.基本的转化
SELECT CAST(2008 as varchar(4)) + ' year!'
SELECT CONVERT(varchar(4),2008) + ' year!'
--2.把日期转化为文本
SELECT CONVERT(VARCHAR(30),GETDATE(),120) --年-月-日 时:分:秒(24h)
SELECT CONVERT(VARCHAR(10),GETDATE(),120) --年-月-日 时:分:秒(24h)
SELECT CONVERT(VARCHAR(8),GETDATE(),108) -- 时:分:秒(24h)
select CONVERT(varchar(30),getdate(),121) --年-月-日 时:分:秒 时:分:秒.毫秒(24h)
--3.使用不同样式转换二进制和字符数据的结果
SELECT CONVERT(varbinary,
'SQL Server 2008 T-SQL Recipes',
0 --表示把字符串转化为二进制
)
select CONVERT(char(29),
0x53514C20536572766572203230303820542D53514C2052656369706573,
0 --把二进制转化为ASCII格式的字符串
)
--下面两个都是转化为字面量
SELECT CONVERT(CHAR(60),
0x53514C20536572766572203230303820542D53514C2052656369706573,
1 --转化为16进制字面量格式,带0x前缀
)
SELECT CONVERT(CHAR(60),
0x53514C20536572766572203230303820542D53514C2052656369706573,
2
) --转化为16进制字面量格式,不带0x前缀
--4.计算表达式返回的类型
select ISDATE('20000-01-01')
select ISDATE('2000-01-01')
--字符会返回0
SELECT ISNUMERIC('ABC')
--如果数字之间有空格，也会返回0
SELECT ISNUMERIC('12 3')
--如果数字之间有其他符号，也会返回0
SELECT ISNUMERIC('12,3')
--如果数据末尾,开头有空格,不会有影响,还是返回1
SELECT ISNUMERIC(' 12,3 ')
这里需要特别提到的是，在原来的公司，曾经遇到过日期转换的问题，由于把日期数据存储在了varchar类型中，导致转换报错，那么这种情况，如何处理呢？
下面通过一个简单的例子来说明处理方法：
if object_id('a') is not null
drop table a
go
create table A(Id int, date varchar(30))
insert into A
select 1, '2013-10-40 00:00:00' union all
select 2, '2013-10-01 12:00:00' union all
select 3, '2014-01-01 12:09:34'
--报错了
select * from A
where Isdate(date) = 1
and Convert(datetime,date) >= '2014-01-01 00:00:00'
/*
Id date
----------- ------------------------------
消息 242，级别 16，状态 3，第 10 行
从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。
*/
--找到有问题的数据
select *,
case when isdate(date) = 1 then '是日期'
when isdate(date) = 0 then '不是日期'
end
from A
--where Isdate(date) = 1
--and Convert(datetime,date) >= '2014-01-01 00:00:00'
/*
Id date
----------- ------------------------------ --------
1 2013-10-40 00:00:00 不是日期
2 2013-10-01 12:00:00 是日期
3 2014-01-01 12:09:34 是日期
(3 行受影响)
*/
--这里通过convert函数，把字符串格式化为日期格式的字符串
--然后与右边的字符串进行比较,就不会报错了
select * from A
where Isdate(date) = 1 --是日期
and Convert(VARCHAR(20),[date],121) >='2013-11-01 00:00:00'
如何把负数转化为16进制数？
--1.一开始这么转化,但是有问题,关键在于你的这个数太大,超出了int的范围
select cast(-2161622263693857431 as varbinary)
/*
0x1300000097520CFEFE9FFF1D
*/
--2.这样就行了,转化为bigint,在转为varbianry,但是这个不是字符串,所以还是不行
select cast(cast(-2161622263693857431 as bigint) as varbinary)
/*
0xE200600101F3AD69
*/
use 你的数据库
go
IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL
DROP FUNCTION dbo.varbin2hexstr
GO
--这个函数实现了把varbinary类型数据转化为varchar类型的数据
CREATE function varbin2hexstr(
@bin varbinary(max)
)returns varchar(max)
as
begin
declare @re varchar(max),@i int
select @re='',@i=datalength(@bin)
while @i>0
select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
+substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
return @re
end
GO
--3.调用函数dbo.varbin2hexstr,把varbinary转为字符串,这样才行
select dbo.varbin2hexstr(cast(cast(-2161622263693857431 as bigint) as varbinary))
/*
E200600101F3AD69
*/
8、排名函数
select * into objectT
from sys.objects
insert into objectT
select top (10) *
from sys.objects
--生成递增行号
select object_id,
name,
ROW_NUMBER() over(order by object_id)
from objectT
--排名,会有间隔,比如排第1的有2行数据,那么接下来直接就是排在第3的
select OBJECT_ID,
name,
RANK() over(order by object_id)
from objectT
--无间隔排名
select OBJECT_ID,
name,
dense_rank() over(order by object_id)
from objectT
/*===============================================
根据排序,可选的分区，把结果集分成指定数量的分组
ntile(n) partition by x order by y:
按照x分组，然后按y排序，然后把每个分组分成n个分组
=================================================*/
select OBJECT_ID,
name,
type_desc,
NTILE(3) over(partition by type_desc
order by object_id)
from objectT
9、系统统计函数
select @@SPID, --返回当前连接的会话ID:SPID
@@servername, --SQL Server实例名称
@@sevicename, --SQL Server版本信息
APP_NAME(), --客户端连接的应用程序名称
HOST_ID(), --客户端连接的进程ID:936
HOST_NAME(), --客户端连接的主机名称:PC0627JVC
system_user, --登录名:PC0627JVC\Administrator
user --数据库用户:dbo
--设置一周中的第一天
set datefirst 7
select @@DATEFIRST '一周中的第一天'
--设置当前会话的语言
set language '简体中文'
select @@LANGID '语言id',
@@LANGUAGE '当前会话的语言'
--设置当前连接的锁超时设置
set lock_timeout 1000
select @@LOCK_TIMEOUT
--设置之前语句影响的行数
set rowcount 20
select @@ROWCOUNT
--返回打开事务的个数
select @@TRANCOUNT
--显示当前存储过程上下文的嵌套级别
--SQL Server允许存储过程最多32次嵌套调用
--也就是在一个存储过程里调用另一个存储过程，最多32次
select @@NESTLEVEL
--统计信息,属性信息
select
@@CPU_BUSY,
@@IO_BUSY,
@@IDLE,
@@error,
@@PACKET_ERRORS,
@@PACK_RECEIVED,
@@PACK_SENT,
@@TOTAL_ERRORS,
@@TOTAL_READ,
@@TOTAL_WRITE,
@@CURSOR_ROWS,
@@FETCH_STATUS,
@@IDENTITY,
@@OPTIONS,
@@CONNECTIONS,
@@MAX_CONNECTIONS,
@@MAX_PRECISION,
@@MICROSOFTVERSION,
@@VERSION,
@@PROCID,
@@REMSERVER,
@@TEXTSIZE,
@@TIMETICKS,
@@DBTS,
@@DEFAULT_LANGID,
@@DEF_SORTORDER_ID
扫一扫
237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
