10、SQL Server 内置函数、临时对象、流程控制

SQL Server 内置函数

日期时间函数

--返回当前系统日期时间select getdate() as [datetime],sysdatetime() as [datetime2]

getdate返回的是datetime类型的数据，而sysdatetime返回的是datetime2数据类型的数据。后者精度更高。

拆分显示日期和时间

select day(getdate()) as 日期,month(getdate()) as 月份,year(getdate()) as 年份

三个函数day,month,year返回的值是显示的。

select datename(d,getdate()) as 日期,datename(M,getdate()) as 月份,datename(YY,getdate()) as 年份,datename(weekday,getdate()) as 星期

datename函数返回nvarchar数据类型的字符串值。

select datepart(D,getdate()) as 日期,datepart(M,getdate()) as 月份,datepart(YYYY,getdate()) as 年份,datepart(weekday,getdate()) as 星期

datepart函数返回的是整型值。

DATEFROMPARTS标量值函数

2012新函数，用来从其他表达式中获取日期数据

select datefromparts(1972,5,26) as datefromparts, --日期datetime2fromparts(1972,5,26,7,14,16,10,3) as datetime2fromparts,--毫秒精确3位datetimefromparts(1972,5,26,7,14,16,10) as datetimefromparts,--同上datetimeoffsetfromparts(1972,5,26,7,14,16,10,12,0,3) as datetimeoffsetfromparts,--带上午下午smalldatetimefromparts(1972,5,26,7,14) as smalldatetimefromparts, --日期+时间timefromparts(7,14,16,10,3) as timefromparts --时间

操作日期

select datediff(dd,getdate(),'2015/8/30') as 距离8月30日还有,--相减 得出天数dateadd(dd,1,getdate()) as 当前日期加一天是, --加日期 得到新日期eomonth(getdate()) as 指定月份最后一天, --SQL Server 2012新增isdate('2015/9/33') as 验证是否有效日期 --正确1 错误0

转换函数

cast(数据 as 类型)

PRint '123' + 123 --结果246，因为sqlserver中以数字优先处理，若想输出字符串需要转换print '123' + cast(123 as varchar)--输出123123print cast('185.12485' as decimal(10,2)) --转换货币格式修改小数位 输出185.12print cast('2015/1/1 19:00:00' as date) --输出2015-01-01

输出：

246123123185.122015-01-01

convert(类型,数据,[样式])

使用convert转换与cast转换的区别是,还可以指定一些样式，省去了程序.ToString("yyyy-MM-dd")等转换。

select convert(varchar,getdate()), --返回默认字符串convert(varchar,getdate(),101), --美国 mm/dd/yyyyconvert(varchar,getdate(),103), --英国/法国 dd/mm/yyconvert(varchar,getdate(),104), --德国 dd.mm.yyconvert(varchar,getdate(),111) --日本 yy/mm/dd 非常遗憾 全亚洲都是用日本的...--具体样式请查看联机丛书

SQL Server 2012中新的转换函数

Parse

--只有将字符串转换为数字或日期时间才可以使用select parse('123' as int) --输出123--select parse('s123' as int) --报错 必须可以转换为整型才可以select parse('2016/10/01' as date) -- 2016-01-01

Try_Parse

--尝试转换 失败返回NULLselect Try_Parse('123' as int) --123select Try_Parse('s123' as int) --NULL--select Try_Parse(123 as varchar) --失败，只能转换字符串到整型

Try_Convert

--现有Convert进行扩展，失败则为nullselect Try_Convert(datetime,'2015/6/1',111)select Try_Convert(datetime,'2015/6/40',111) --NULL

Try_Cast

--现有cast进行扩展select Try_Cast('2016/6/1' as date)select Try_Cast('2016/6/34' as date)--NULL

以上带try前缀的 表示尝试进行转换，如果失败不会报错，而是返回NULL。

字符串函数

select concat('12' , 34 , '56') --2012新函数 将非字符串自动转换字符串select rtrim('asd ') --去右侧空白select ltrim(' asd') --去左侧空白select left('abcedfg',3) --取左三位  abcselect right('abcedfg',3) --取右三位  dfgselect substring('abcdefg',3,5) --从第3位截取5个数字  cdefgselect len('abcdefg') --返回字符串长度select '12' + cast(34 as varchar) + '56' --需要转换 否则按数字计算select ascii('a'); --返回ascii值 select unicode('a'); --返回unicode值 select 'a' + space(2) + 'b'; --输出空格 select difference('hello', 'helloWorld'); --比较字符串相同个数select replace('abcedef', 'e', 'E'); --替换字符串select stuff('hello world', 3, 4, 'ABC'); --指定位置替换字符串 select replicate('abc#', 3); --输出重复字符串 几次 select reverse('sqlServer'); --反转字符串select lower('aBc')  --转换小写select upper('aBc') --转换大写

逻辑函数

2012有两种新的逻辑函数，choose 和 iif。

choose

--根据一个指定的索引返回列表中的一个值declare @choosevar int = 3select choose(@choosevar,'one','two','baibai','three') as [choose]--返回baibai

IIF

--根据布尔表达式计算true还是false，返回其中一个值select iif(datename(month,getdate())='08','嗯 是8月','不是8月')--类似三元运算符 如果成立 返回第一个参数 否则返回第二个

聚合函数

SUM求和

--必须是整数select sum(age) from student select sum(distinct age) from student--去重

AVG平均

select avg(age) from studentselect avg(distinct age) from student--去重

COUNT统计

--统计select count(*) from student --所有行数select count(id) from student --所有个数select count(distinct name) from student --去重的个数

MAX最大值

select max(id) from student

MIN最小值

select min(age) from student

分组聚合GROUP BY

select name,count(age) from student group by name--注意:任何一个未被聚合函数所用到，但却出现在select语句的列名，必须包含在group by 子句中--select id,name,avg(age) from student group by name --报错 因为没group by idselect name,avg(age) from student group by name

分组筛选HAVING

select name,avg(age) from student group by name having avg(age) > 25

开窗|分区函数OVER

select avg(age) from student; --只能返回一条平均年龄信息select *,avg(age) over() as avgAge from student; --新开一个窗口显示平均成绩select *,row_number() over(order by age desc) as [row_number] from student; --根据over子句的排序，生成新的自增整数 123456select *,rank() over(order by age desc) as [rank] from student; --如果两行排名相同 则生成的值也一样，并且不连续 11 33 555 8select *,dense_rank() over(order by age desc) as [dense_rank] from student; --同上，但是值会连续 11 2222 33

SQL Server临时对象

对于一些经常需要多表联查，分组聚合，一些查询复杂非常耗时间的脚本 可以临时存储在数据集中。

公用表表达式CTE

--公用表表达式CTE--CTE是一个临时的结果集，不会存储在数据库中，且只在查询期间有效。with cte(id,name,age) --定义cteas(    select id,name,age from student --可定义复杂的多表联查等)select* from cte where age > 24 --基于临时表的操作在此完成

表变量

--与本地变量类似，用@定义，一般用来存储少量数据。declare @local_table as table --定义表变量(    localId int,    localMoney money)insert into @local_table select id,age from student --将查询结果插入到表变量select * from @local_table --查询表变量中的数据--在整个批处理或会话期间都可以访问表变量,CTE则不可

临时表

--本地临时表，仅在当前会话期间有效，当会话结束后会被删除。前缀#--全局临时表，可被所有会话引用，当创建临时表的会话与所有引用该临时表的会话都关闭后才会被删除。前缀##create table #t --创建临时表(    tId int,    tName varchar(20))insert into #t select id,name from studentselect * from #tdrop table #t --手动删除临时表

处理T-SQL错误

begin tryselect 1/0 --不会报错 而是到catch块处理end trybegin catchprint '错误了'end catch--2012引入throw 抛出异常 返回更详细的错误信息begin tryselect 1/0 --不会报错 而是到catch块处理end trybegin catchthrowend catch--自定义错误提示begin tryselect 1/0 --不会报错 而是到catch块处理end trybegin catchthrow 50000,'用1除以0了',1;  --参1消息:50000-2147483647之间,参2消息,参3状态0-255之间end catch

消息 8134，级别 16，状态 1，第 11 行遇到以零作除数错误。

控制流关键字

begin end

成对使用，包含一组T-SQL语句，begin end可以嵌套 类似{}

begindeclare @beginDate date = '2015/8/16'declare @endDate date = '2015/8/30'print datediff(dd,@beginDate,@endDate)end--输出14

if else

--与c#一样 如果成立则， 否则 declare @beginDate date = '2015/8/16'declare @endDate date = '2015/8/30'declare @diff int = datediff(dd,@beginDate,@endDate)if(@diff > 0)    begin        print '相差天数为' + cast(@diff as varchar)    endelse    begin        print '开始日期小于结束日期'    end--输出相差天数为14

while

--循环，如果真一直循环，false则退出循环declare @index int = 1while(@index<10)begin    set @index = @index + 1    if(@index < 5)        begin            continue --小于5继续循环        end    else        begin            break --否则终止循环        endendprint @index --5