第九章 T-SQL 設計 9-1 關於 T-SQL 9-1-1 關於程式語言 9-1-2 T-SQL 的組成 9-1-3 測試及執行 9-2 基本語法說明 9-2-1 變數及常數 9-2-2 資料型別優先順序 9-2-3 運算子 9-2-4 取得工作狀態 9-2-5 設定工作狀態 9-2-6 流程控制 9-2-7 錯誤處理 9-3 實務說明 9-3-1 日期處理 9-3-2 小數資料處理 9-3-3 自動編號的處理 9-3-4 PIVOT TABLE 1/37
9.1 關於 T-SQL 9-1-1 關於程式語言 T-SQL 專指內附於 SQL Server 的程式語 言 所有 SQL Server 的處理動作都可藉由 T-SQL (Transact-SQL) 來完成 每一程式的基本語法概念都相同 除了基本語法外, 尚必須了解每一程式語法提供的資源, 包括函數 可用物件等 2/37
9-1-2 T-SQL 的組成 1. 基本語法 ( 參考 9-2) 2. 系統資源 - 以 sp_ 為名稱字首的系統預存程序 3. 資料定義語言 - DDL, 功能是定義及管理資料庫結構, 包括資料表, 索引, 檢視等 4. 資料操作語言 - DML, 功能是針對資料表中的記錄進行各式處理, SELECT, INSERT, UPDATE, DELETE DDL: Data Definition Language DML: Data Manipulation Language 3/37
9-1-3 測試及執行 T-SQL 語法都在查視視窗中測試及執行 按下按鈕, 執行程式, 結果會顯示在下半部的 訊息 視窗中 4/37
9-2 基本語法說明 9-2-1 變數及常數 何謂變數 變數就是在執行時可變動的資料, 數 不是數字, 而是資料, 變 之意就是在執行時可因需要而改變 型態可以是 SQL Server 提供的資料型態 名稱必須以 @ 為首表示區域變數, 另 @ @ 為首表示全域變數 ( 系統函數, 表 9-7) 以 DECLARE 予以宣告 5/37
使用變數 使用兩個變數 變數 變數值的來源不同, 分別以 SET 及 SELECT 取得 6/37
常數 常數指的是固定不變的資料 設定常數 : SET 文字二進位數字日期及時間 在前後各加上英文單引號 ( ), 若是 Unicode 文字, 請加上 N 表示法以 0x 為首, 其後的長度不定, 不必使用單引號 數字資料在表示時, 不需使用單引號 小數 :money, decimal, real, float 日期及時間資料必須加上單引號, 可以是各種合法的格式 SET @PID=N'F153342401' DECLARE @x binary SET @x=0x123 DECLARE @x int DECLARE @y money SET @x=12 SET @y=$10.5 DECLARE @x datetime,@y datetime,@z datetime SET @x='2005/11/23 12:00' SET @y='11/23/2005' SET @z='11 23, 2005' 7/37
8/37 9-2-2 資料型別優先順序資料型別優先順序資料型別優先順序資料型別優先順序 優先順序 : 當一個運算式有兩個不同型態的資料時, 優先順序相對較低者會嚐試轉換為另一個優先順序相對較高者 binary 24 Tinyint 12 varbinary 23 Smallint 11 char 22 int 10 varchar 21 bigint 9 nchar 20 smallmoney 8 nvarchar 19 money 7 uniqueidentifier 18 decimal 6 timestamp 17 real 5 image 16 float 4 text 15 smalldatetime 3 ntext 14 datetime 2 bit 13 sql_variant 1 型態型態型態型態優先順序優先順序優先順序優先順序型態型態型態型態優先順序優先順序優先順序優先順序
9-2-3 運算子 算術運算子 運算子 意義 + ( 加法 ) 加法 - ( 減法 ) 減法 * ( 乘 ) 乘法 / ( 除 ) 除法 % ( 餘數 ) 傳回除法的整數餘數 如 12 % 5 = 2 9/37
位元運算子 位元運算子的特色是只可使用在整數型態的資料 位元比較之意是以資料中各個位元的實際資料做比較,& 可比較是否相同, 可比較是否相異,^ 可比較互斥 運算子 & ( 位元 AND) ( 位元 OR) ^ ( 位元互斥 OR) 意義執行 AND 比較 執行 OR 比較 執行互斥比較 二者不相同為 TRUE. 10/37
比較運算式 比較的結果只有兩種, 不是 True 就是 False 運算子 = ( 等於 ) 等於 意義 > ( 大於 ) 大於 < ( 小於 ) 小於 >= ( 大於或等於 ) 大於或等於 <= ( 小於或等於 ) 小於或等於 <> ( 不等於 ) 不等於!= ( 不等於 ) 不等於!< ( 不小於 ) 不小於!> ( 不大於 ) 不大於 11/37
邏輯運算子 邏輯運算子與比較運算子類似, 都是傳回 True 或 False 的結果, 差別是邏輯運算子通常使用在 SELECT 語法的 WHERE 之後, 也就是做為篩選條件 12/37
ALL AND 運算子 意義 如果所有的比較集都為 TRUE 則為 TRUE 如果兩個布林運算式都為 TRUE 則為 TRUE ANY BETWEEN...AND... EXISTS IN LIKE NOT OR SOME 如果任何比較集為 TRUE 則為 TRUE 如果運算元在某範圍之間則為 TRUE 如果子查詢 (Subquery) 包含任何資料列則為 TRUE 如果運算元等於運算式清單中的任何一個則為 TRUE 如果運算元符合某個模式則為 TRUE 反轉任何其他布林運算子的值如果其中一個布林運算式為 TRUE 則為 TRUE 如果某些比較集為 TRUE 則為 TRUE 13/37
9-2-4 取得工作狀態 SQL Server 提供許多以 @@ 符號為首的系統環境變數, 可以取得工作狀態的資訊 14/37
設定工作狀態, 語法是使用 SET 大部份都是 ON 及 OFF 二選一, 非開即閉的選擇 9-2-5 設定工作狀態 15/37
9-2-6 流程控制 IF...ELSE 01 use ebook 02 Declare @a int, @b int 03 Select @a=sum(totals) from Orders 04 where Totals is not null 05 Select @b=sum(totals) from Orders 06 where Totals is not null and PayOK = 1 07 if @a >0 08 BEGIN 09 print @a-@b -- 列印相減結果 10 END 11 ELSE 12 BEGIN 13 print ' 無資料可計算 ' 14 END 16/37
流程控制 WHILE 01 DECLARE @Start smallint, @End smallint 02 SET @End=round(RAND()*10,0) -- 取得迴圈執行次數 03 SET @Start = 1 04 WHILE @Start < @End 05 BEGIN 06 SELECT @Start Number 07 SET @Start = @Start + 1 -- 累計加 1 08 END 17/37
流程控制 CONTINUE 及 BREAK 此二者均使用在迴圈內, 在迴圈內任意處使用 Break, 表示離開迴圈 01 DECLARE @Az smallint, @End smallint 02 SET @Az=1 03 SET @End = round(rand()*10,0) 04 WHILE @Az < @End 05 BEGIN 06 SET @Az = @Az + 1 07 IF @Az=3 08 BREAK 09 ELSE 10 CONTINUE 11 END 18/37
流程控制 RETURN 可使用在程式內任意處, 功能是中止執行 GOTO 01 USE ebook 02 GO 03 DECLARE @tablename sysname 04 SET @tablename = N'members' 05 t_loop: 06 IF (@@FETCH_STATUS <> -2) 07 BEGIN 08 SELECT @tablename = RTRIM(UPPER(@tablename)) 09 EXEC ('SELECT ' + @tablename + ' = COUNT(*) FROM ' + @tablename ) 10 END 11 IF (@@FETCH_STATUS <> -1) GOTO t_loop 19/37
9-2-7 錯誤處理 可使用兩種錯誤處理的設計, 一是傳統的 @@ERROR 01 Declare @InputID int 02 SET @InputID=1 03 INSERT INTO Producttype (id,productcategoryname, LevelID) VALUES (@InputID,' 散文 ',1) 04 IF @@ERROR <>0 05 BEGIN 06 print ' 發生錯誤 ' 07 END 20/37
新增 TRY CATCH 敘述 01 Declare @InputID int 02 SET @InputID=1 03 BEGIN TRY 04 INSERT INTO Producttype (id,productcategoryname,levelid) VALUES (@InputID,' 散文 ',1) 05 END TRY 06 BEGIN CATCH 07 print 'error' 08 END CATCH 21/37
9.3 實務說明 9-3-1 日期處理 格式設定 可以使用 SET DATEFORMAT 敘述 SET DATEFORMAT: 只有在將字元字串轉換成日期值時, 才會在字元字串的解譯中使用這項設定 它不會影響日期值的顯示 也可使用 CONVERT 函數 22/37
SET DATEFORMAT 三種顯示相同結果 23/37
CONVERT 函數 24/37
DATENAME 函數 DATENAME ( datepart, date ) datepart 參數 25/37
26/37
日期加減處理 加減處理 可使用 DateAdd 函數 使用三個引數, 分別代表單位 加減數字及基準日等 27/37
9-3-2 小數資料處理 藉由兩種方式執行四捨五入, 分別是型態的自動處理及 ROUND 函數 28/37
9-3-3 自動編號的處理 一個資料表只有一個型態為 int 及 IsIdentity 屬性為是的欄位, 也就是自動編號 輸入資料至自動編號 USE ebook SET IDENTITY_INSERT Category ON GO INSERT INTO Category (CateID, CateName, CateType) VALUES (38,' 日文 ',' 語言別 ') 29/37
自動編號的處理 取得剛產生的自動編號 - 取出 @@IDENTITY 30/37
9-3-4 PIVOT TABLE 製作類似 Access 的交叉資料表或 Excel 的樞紐分析表 31/37
View: eb_vw_productordername 32/37
Pivot: eb_vw_productordername 33/37
View: eb_vw_productmember 34/37
PIVOT: eb_vw_productmember 35/37
[ 補充篇 ]PIVOT 範例 :T-SQL USE AdventureWorks GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture -- Pivot table with one row and five columns SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] FROM (SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable 36/37
[ 補充篇 ]PIVOT 範例 : 結果 37/37