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