【SQL Server学习笔记】SQL Server系统函数
SQL Server的系统函数，可以让我们实现聚合、数学运算、字符串操作、日期运算、NULL的处理、排序等功能。需要注意的是SQL Server的系统函数和用户自定义函数是不同的，系统函数是SQL Server内部支持的函数，不需要我们先定义，再使用。
我们通过Microsoft SQL Server Management Studio客户端工具，可以看到SQL Server提供的系统函数，如下图：
http://img.blog.csdn.net/20131022152357812?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvc3Fsc2VydmVyZGlzY292ZXJ5/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
下面，通过实例，来说明如何使用SQL Server的内建函数：
1、SQL Server元数据函数
--1.列 --列的长度 select COL_LENGTH('dbo.wct',--表名 'wcid') --列名--列名 select COL_NAME(object_id('dbo.wct'),--表ID 1) --列ID/*============================================ 列属性： ColumnId AllowsNullPrecision ScaleIsComputed IsSparse IsCursorTypeIsColumnSet IsIdentity IsRowGuidCol FullTextTypeColumnIsFulltextIndexedIsIdNotForReplIsIndexable IsOutParam IsXmlIndexable IsDeterministic IsPrecise IsSystemVerifiedSystemDataAccessUserDataAccessUsesAnsiTrim ==============================================*/ --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,以后版本将删除此函数 selectfile_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函数返回指定逻辑文件对应的逻辑文件名称 selectfile_name(8)--FileProperty函数返回指定逻辑文件相应选项的属性 selectfileproperty('wc_data', 'IsPrimaryFile') selectfileproperty('wc_fg5_1', 'IsReadOnly') selectfileproperty('wc_fg5_1', 'SpaceUsed') --返回在文件中分配的页数 selectfileproperty('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')--索引idselect 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 = 0SELECT 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 agocreate table A(Id int, date varchar(30))insert into A select 1, '2013-10-40 00:00:00' union all select 2, '2013-10-0112: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' /*> 如何把负数转化为16进制数？
--1.一开始这么转化,但是有问题,关键在于你的这个数太大,超出了int的范围 select cast(-2161622263693857431 as varbinary) /* 0x1300000097520CFEFE9FFF1D */ --2.这样就行了,转化为bigint,在转为varbianry,但是这个不是字符串,所以还是不行 select cast(cast(-2161622263693857431 as bigint) as varbinary)/* 0xE200600101F3AD69 */ use 你的数据库 goIF 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.objectsinsert 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 7select @@DATEFIRST '一周中的第一天' --设置当前会话的语言 set language '简体中文'select @@LANGID '语言id', @@LANGUAGE '当前会话的语言' --设置当前连接的锁超时设置 set lock_timeout 1000select @@LOCK_TIMEOUT --设置之前语句影响的行数 set rowcount 20select @@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
页:
[1]