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

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

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

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

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

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

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

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 非常遗憾 全亚洲都是用日本的...--具体样式请查看联机丛书

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

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

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

--现有cast进行扩展select Try_Cast('2016/6/1' as date)select Try_Cast('2016/6/34' as date)--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') --转换大写

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

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

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

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

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

select max(id) from student

select min(age) from student

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

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

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

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

--与本地变量类似，[email protected]，一般用来存储少量数据。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 --手动删除临时表

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

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

--与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

--循环，如果真一直循环，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