SQL Server 2008 的 Transact-SQL 語言增強
Microsoft SQL Server 2008 對 Transact-SQL 語言進行了進一步增強,主要包括:ALTER DATABASE 相容級別設定、複合運算子、CONVERT 函式、日期和時間功能、GROUPING SETS、MERGE 語句、SQL 依賴關係報告、表值引數和 Transact-SQL 行建構函式。
1.ALTER DATABASE 相容級別設定
某些資料庫行為與 SQL Server 版本有關,通過 ALTER DATABASE 下面新增的語法,可以設定資料庫相容級別,它取代了以前版本中的 sp_dbcmptlevel 過程。
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
可用的設定值80、90、100分別代表 SQL Server 2000、2005和2008。
2.複合運算子
SQL Server 2008 現在支援如下複合運算子,可執行操作並將變數設定為結果。
運算子
操作
+=
將原始值加上一定的量,並將原始值設定為結果
-=
將原始值減去一定的量,並將原始值設定為結果
*=
將原始值乘上一定的量,並將原始值設定為結果
/=
將原始值除以一定的量,並將原始值設定為結果
%=
將原始值除以一定的量,並將原始值設定為餘數
&=
對原始值執行位與運算,並將原始值設定為結果
^=
對原始值執行位異或運算,並將原始值設定為結果
|=
對原始值執行位或運算,並將原始值設定為結果
如:
DECLARE @x1 int = 27;
SET @x1 += 2 ;
SELECT @x1 -- 返回29
3.CONVERT 函式
CONVERT 函式現在允許在二進位制和字元十六進位制值之間進行轉換。函式語法格式如下:
CONVERT ( data_type [ ( length ) ] , expression [ , style. ] )
expression 是被轉換的有效的表示式,data_type 目標資料型別(不能使用別名資料型別),length 指定目標資料型別長度的可選整數,style. 指定 CONVERT 函式如何轉換 expression 的整數表示式。
如果 expression 為 binary(n)、varbinary(n)、char(n) 或 varchar(n),則 style. 可以為下表中顯示的值之一。
值
輸出
0(預設值)
將 ASCII 字元轉換為二進位制位元組,或者將二進位制位元組轉換為 ASCII 字元。每個字元或位元組按照 1:1 進行轉換。
如果 data_type 為二進位制型別,則會在結果左側新增字元 0x。
1, 2
對於 style. 1,將在轉換後的結果左側新增字元 0x。作為要轉換的二進位制表示式,字元 0x 必須為表示式中的前兩個字元。
在style為2的情況下,生成的二進位制值不會包含字元 0x。作為要轉換的二進位制表示式,也不需要在字元前面包含字元 0x。
如果 data_type 為二進位制型別,則表示式必須為字元表示式。
如果轉換後的表示式長度大於 data_type 長度,則會在右側截斷結果。
如果固定長度 data_types 大於轉換後的結果,則會在結果右側新增零。
如果 data_type 為字元型別,則表示式必須為二進位制表示式。每個二進位制字元均轉換為兩個十六進位制字元。如果轉換後的表示式長度大於 data_type 長度,則會在右側截斷結果。
如果 data_type 為固定大小的字元型別,並且轉換後的結果長度小於其 data_type 長度,則會在轉換後的表示式右側新增空格,以使十六進位制數字的個數保持為偶數。
參考下面的示例程式碼:
--轉換二進位制值 0x4E616d65 到一個字元值
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style. 0, 二進位制到字元'
--下面的示例演示了 Style. 為 1 的情況下,如何強行截斷結果值。
--產生的結果值由於包含字元 0x ,所以被截斷
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style. 1, 二進位制到字元'
--下面的示例演示了 Style. 為 2 的情況下,沒有截斷結果值。
--這是因為 0x 字元未包含在結果中
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style. 2, 二進位制到字元'
--轉換字元值 Name 到一個二進位制值
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style. 0, 字元到二進位制'
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style. 1, 字元到二進位制'
SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style. 2, 字元到二進位制'
結果如下:
4.日期和時間功能
DATEPART ( datepart , date )函式用於返回 date中的指定 datepart 的整數。如:
SELECT DATEPART(YEAR,'2007-05-10') --返回2007
SQL Server 2008 包含對 ISO 周-日期系統的支援,即周的編號系統。每週都與該周內星期四所在的年份關聯。例如,2004 年第 1 周 (2004W01) 從 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期天。一年中最大的週數可能為 52 或 53。這種編號方式通常用於歐洲國家,但其他國家/地區很少用到。
下面分別是2010年和2009年1月份的日曆。由於2010年第一個星期中的星期四是2010-1-7日,所以2010-1-3日及之前的日期會作為2009年的第53個星期,而不是2010年的第一個星期。而對於2009年1月份的日曆,由於星期四是2009-1-1,所以該星期會作為2009年的第一個星期。當然,該星期也包含了2008-12-28至31的4天。
參考下面的程式碼:
SELECT DATEPART(ISO_WEEK,'2010-1-3') --返回53
SELECT DATEPART(ISO_WEEK,'2010-1-4') --返回1
SELECT DATEPART(ISO_WEEK,'2009-1-1') --返回1
5.ROLLUP、CUBE 和GROUPING SETS
在SQL Server 2008之前,進行分組統計彙總,可以在GROUP BY子句中使用WITH ROLLUP和WITH CUBE引數。ROLLUP指定在結果集內不僅包含由GROUP BY提供的行,還包含彙總行。按層次結構順序,從組內的最低階別到最高階別彙總組。而CUBE引數則在使用ROLLUP引數所返回結果集的基礎上,再將每個可能的組和子組組合在結果集內返回。
例如,假設dbo.T1表中存在下列資料:
執行下面的查詢語句:
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH CUBE
ORDER BY CustName,ProductID;
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH ROLLUP
ORDER BY CustName,ProductID;
得到下面的結果集合,可以看出,使用WITH CUBE多出了對子組ProductID的兩行彙總。
而在SQL Server 2008中,GROUPING SETS、ROLLUP 和 CUBE 運算子已新增到 GROUP BY 子句中。不再推薦使用不符合 ISO 的 WITH ROLLUP、WITH CUBE 和 ALL 語法。在SQL Server 2008中,可以將上面的WITH CUBE語句改寫為如下的形式:
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CUBE(CustName,ProductID)
ORDER BY CustName,ProductID;
如果不需要獲得由完備的 ROLLUP 或 CUBE 運算子生成的全部分組,則可以使用 GROUPING SETS 僅指定所需的分組。例如,下面的語句將得到分別按CustName和ProductID分組彙總結果集的並集。
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY GROUPING SETS(CustName,ProductID)
ORDER BY CustName,ProductID;
結果集如下:
上面的語句等同於下面的UNION ALL語句:
SELECT CustName,NULL AS ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName
UNION ALL
SELECT NULL AS CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY ProductID
1.ALTER DATABASE 相容級別設定
某些資料庫行為與 SQL Server 版本有關,通過 ALTER DATABASE 下面新增的語法,可以設定資料庫相容級別,它取代了以前版本中的 sp_dbcmptlevel 過程。
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
可用的設定值80、90、100分別代表 SQL Server 2000、2005和2008。
2.複合運算子
SQL Server 2008 現在支援如下複合運算子,可執行操作並將變數設定為結果。
運算子
操作
+=
將原始值加上一定的量,並將原始值設定為結果
-=
將原始值減去一定的量,並將原始值設定為結果
*=
將原始值乘上一定的量,並將原始值設定為結果
/=
將原始值除以一定的量,並將原始值設定為結果
%=
將原始值除以一定的量,並將原始值設定為餘數
&=
對原始值執行位與運算,並將原始值設定為結果
^=
對原始值執行位異或運算,並將原始值設定為結果
|=
對原始值執行位或運算,並將原始值設定為結果
如:
DECLARE @x1 int = 27;
SET @x1 += 2 ;
SELECT @x1 -- 返回29
3.CONVERT 函式
CONVERT 函式現在允許在二進位制和字元十六進位制值之間進行轉換。函式語法格式如下:
CONVERT ( data_type [ ( length ) ] , expression [ , style. ] )
expression 是被轉換的有效的表示式,data_type 目標資料型別(不能使用別名資料型別),length 指定目標資料型別長度的可選整數,style. 指定 CONVERT 函式如何轉換 expression 的整數表示式。
如果 expression 為 binary(n)、varbinary(n)、char(n) 或 varchar(n),則 style. 可以為下表中顯示的值之一。
值
輸出
0(預設值)
將 ASCII 字元轉換為二進位制位元組,或者將二進位制位元組轉換為 ASCII 字元。每個字元或位元組按照 1:1 進行轉換。
如果 data_type 為二進位制型別,則會在結果左側新增字元 0x。
1, 2
對於 style. 1,將在轉換後的結果左側新增字元 0x。作為要轉換的二進位制表示式,字元 0x 必須為表示式中的前兩個字元。
在style為2的情況下,生成的二進位制值不會包含字元 0x。作為要轉換的二進位制表示式,也不需要在字元前面包含字元 0x。
如果 data_type 為二進位制型別,則表示式必須為字元表示式。
如果轉換後的表示式長度大於 data_type 長度,則會在右側截斷結果。
如果固定長度 data_types 大於轉換後的結果,則會在結果右側新增零。
如果 data_type 為字元型別,則表示式必須為二進位制表示式。每個二進位制字元均轉換為兩個十六進位制字元。如果轉換後的表示式長度大於 data_type 長度,則會在右側截斷結果。
如果 data_type 為固定大小的字元型別,並且轉換後的結果長度小於其 data_type 長度,則會在轉換後的表示式右側新增空格,以使十六進位制數字的個數保持為偶數。
參考下面的示例程式碼:
--轉換二進位制值 0x4E616d65 到一個字元值
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style. 0, 二進位制到字元'
--下面的示例演示了 Style. 為 1 的情況下,如何強行截斷結果值。
--產生的結果值由於包含字元 0x ,所以被截斷
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style. 1, 二進位制到字元'
--下面的示例演示了 Style. 為 2 的情況下,沒有截斷結果值。
--這是因為 0x 字元未包含在結果中
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style. 2, 二進位制到字元'
--轉換字元值 Name 到一個二進位制值
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style. 0, 字元到二進位制'
SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style. 1, 字元到二進位制'
SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style. 2, 字元到二進位制'
結果如下:
4.日期和時間功能
DATEPART ( datepart , date )函式用於返回 date中的指定 datepart 的整數。如:
SELECT DATEPART(YEAR,'2007-05-10') --返回2007
SQL Server 2008 包含對 ISO 周-日期系統的支援,即周的編號系統。每週都與該周內星期四所在的年份關聯。例如,2004 年第 1 周 (2004W01) 從 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期天。一年中最大的週數可能為 52 或 53。這種編號方式通常用於歐洲國家,但其他國家/地區很少用到。
下面分別是2010年和2009年1月份的日曆。由於2010年第一個星期中的星期四是2010-1-7日,所以2010-1-3日及之前的日期會作為2009年的第53個星期,而不是2010年的第一個星期。而對於2009年1月份的日曆,由於星期四是2009-1-1,所以該星期會作為2009年的第一個星期。當然,該星期也包含了2008-12-28至31的4天。
參考下面的程式碼:
SELECT DATEPART(ISO_WEEK,'2010-1-3') --返回53
SELECT DATEPART(ISO_WEEK,'2010-1-4') --返回1
SELECT DATEPART(ISO_WEEK,'2009-1-1') --返回1
5.ROLLUP、CUBE 和GROUPING SETS
在SQL Server 2008之前,進行分組統計彙總,可以在GROUP BY子句中使用WITH ROLLUP和WITH CUBE引數。ROLLUP指定在結果集內不僅包含由GROUP BY提供的行,還包含彙總行。按層次結構順序,從組內的最低階別到最高階別彙總組。而CUBE引數則在使用ROLLUP引數所返回結果集的基礎上,再將每個可能的組和子組組合在結果集內返回。
例如,假設dbo.T1表中存在下列資料:
執行下面的查詢語句:
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH CUBE
ORDER BY CustName,ProductID;
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName,ProductID
WITH ROLLUP
ORDER BY CustName,ProductID;
得到下面的結果集合,可以看出,使用WITH CUBE多出了對子組ProductID的兩行彙總。
而在SQL Server 2008中,GROUPING SETS、ROLLUP 和 CUBE 運算子已新增到 GROUP BY 子句中。不再推薦使用不符合 ISO 的 WITH ROLLUP、WITH CUBE 和 ALL 語法。在SQL Server 2008中,可以將上面的WITH CUBE語句改寫為如下的形式:
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CUBE(CustName,ProductID)
ORDER BY CustName,ProductID;
如果不需要獲得由完備的 ROLLUP 或 CUBE 運算子生成的全部分組,則可以使用 GROUPING SETS 僅指定所需的分組。例如,下面的語句將得到分別按CustName和ProductID分組彙總結果集的並集。
SELECT CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY GROUPING SETS(CustName,ProductID)
ORDER BY CustName,ProductID;
結果集如下:
上面的語句等同於下面的UNION ALL語句:
SELECT CustName,NULL AS ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY CustName
UNION ALL
SELECT NULL AS CustName,ProductID,SUM(Sales) AS 'SalesTotal'
FROM dbo.T1
GROUP BY ProductID
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-629938/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL SERVER 2008的top增強SQLServer
- SQL Server 2008中SQL增強之一:Values新用途SQLServer
- Sql Server系列:Transact-SQL概述SQLServer
- SQL Server 2008中SQL增強之二:Top新用途 (轉)SQLServer
- Sql Server系列:Transact-SQL變數SQLServer變數
- SQL Server 2008中SQL增強之三:Merge(在一條語句中使用Insert,Update,Delete)SQLServerdelete
- SQL Server 2008技術內幕:T-SQL語言基礎 筆記SQLServer筆記
- SQL Server 2008 MERGE語法SQLServer
- 用 Transact-SQL 語句監視SQL
- TIOBE 2013年12月程式語言排行榜:Transact-SQL衝刺年度語言SQL
- SQL Server 2008中的新語句:MERGESQLServer
- 【Microsoft SQL Server 2008 技術內幕:T-SQL語言基礎】二、查詢篇ROSSQLServer
- SQL Server 2008中Analysis Services的新特性——深入SQL Server 2008SQLServer
- RAISERROR (Transact-SQL)AIErrorSQL
- SQL Server 2008 優化MERGE語句效能SQLServer優化
- SQL Server 2008 sqlcmd 的使用SQLServer
- SQL SERVER 日期和時間資料型別及函式 (Transact-SQL)SQLServer資料型別函式
- TIOBE:2014年1月程式語言排行榜 Transact-SQL成為2013年度程式語言SQL
- SQL Server 2008稀疏列的使用SQLServer
- SQL SERVER 2008安全配置SQLServer
- SQL Server 2008 過期SQLServer
- 安裝sql server 2008SQLServer
- SQL Server 2008 安全更改SQLServer
- 微軟之日 --- SQL Server 2008微軟SQLServer
- 關閉Windows server 2003&2008 IE增強安全配置WindowsServer
- SQL Server 值亂碼問題 (多國語言)SQLServer
- SQL SERVER日期格式與多國語言問題SQLServer
- Sql Server2008 格式化 Sql的 工具SQLServer
- Java 語言是強型別語言語言(轉)Java型別
- 怎樣從SQL Server2008升級到SQL Server 2008 r2SQLServer
- 微軟SQL Server 2008的精妙之處微軟SQLServer
- SQL Server 2008的效能優化工具SQLServer優化
- SQL Server 2008的新壓縮特性SQLServer
- 建立SQL Server 2008的效能基線SQLServer
- 識別SQL Server 2008的瓶頸SQLServer
- 建立SQL Server 2008的工作負荷SQLServer
- 建立SQL Server 2008的計劃指南SQLServer
- SQL Server 2008登入名的建立SQLServer