1、數據庫原理與應用數據庫原理與應用第七講第七講 Transact-SQLTransact-SQL程序設計程序設計1 注釋注釋2 變量變量3 運算符運算符4 函數函數5 流程控制語句流程控制語句 1 注釋注釋 注釋是程序代碼中不執行的文本字符串（也注釋是程序代碼中不執行的文本字符串（也稱為注解）。在稱為注解）。在SQL Server中，可以使用兩種類型中，可以使用兩種類型的注釋字符：一種是的注釋字符：一種是ANSI標準的注釋符標準的注釋符“-”，它用，它用于單行注釋；另一種是與于單行注釋；另一種是與C語言相同的程序注釋符語言相同的程序注釋符號，即號，即“/* */”。 2 變量變量變量是一種語言中

2、必不可少的組成部分。變量是一種語言中必不可少的組成部分。Transact-SQL語言中有兩種形式的變量，一種語言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統提是用戶自己定義的局部變量，另外一種是系統提供的全局變量。供的全局變量。1. 局部變量局部變量利用局部變量可以保存程序執行過程中的中間結果，保存由存儲過程返回的數據值等。（1）局部變量的定義 必須先用DECLARE命令定義后才可以使用。DECLAER local_variable data_type n （2）局部變量的賦值方法 SET local_variable = expression 或者SELECT local

3、_variable = expression ,.n 2. 全局變量全局變量 全局變量是全局變量是SQL Server系統內部使用的變量，系統內部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時調用。全局變量通常存儲一些程序均可以隨時調用。全局變量通常存儲一些SQL Server的配置設定值和統計數據。用戶可的配置設定值和統計數據。用戶可以在程序中用全局變量來測試系統的設定值或者以在程序中用全局變量來測試系統的設定值或者是是Transact-SQL命令執行后的狀態值。命令執行后的狀態值。 使用全局變量時應該注意以下幾點：使用全局變量

4、時應該注意以下幾點： 全局變量不是由用戶的程序定義的，它們是在服務全局變量不是由用戶的程序定義的，它們是在服務器級定義的。器級定義的。用戶只能使用預先定義的全局變量。用戶只能使用預先定義的全局變量。引用全局變量時，必須以標記符引用全局變量時，必須以標記符“”開頭。開頭。局部變量的名稱不能與全局變量的名稱相同，否則局部變量的名稱不能與全局變量的名稱相同，否則會在應用程序中出現不可預測的結果。會在應用程序中出現不可預測的結果。 常用的全局變量常用的全局變量ErrorRowCount Identity3 運算符運算符 運算符是一些符號，它們能夠用來執行算術運算、運算符是一些符號，它們能夠用來執行算術

5、運算、字符串連接、賦值以及在字段、常量和變量之間進字符串連接、賦值以及在字段、常量和變量之間進行比較。在行比較。在SQL Server 2000中，運算符主要有以中，運算符主要有以下六大類：算術運算符、賦值運算符、位運算符、下六大類：算術運算符、賦值運算符、位運算符、比較運算符、邏輯運算符以及字符串串聯運算符。比較運算符、邏輯運算符以及字符串串聯運算符。1算術運算符算術運算符 算術運算符可以在兩個表達式上執行數學運算術運算符可以在兩個表達式上執行數學運算，這兩個表達式可以是數字數據類型分類的任何算，這兩個表達式可以是數字數據類型分類的任何數據類型。算術運算符包括加（數據類型。算術運算符包括加（

6、+）、減（）、減（）、）、乘（乘（*）、除（）、除（/）和取模（）和取模（%）。）。 2賦值運算符賦值運算符Transact-SQL 中只有一個賦值運算符，中只有一個賦值運算符，即等號（即等號（=）。賦值運算符使我們能夠將數據）。賦值運算符使我們能夠將數據值指派給特定的對象。另外，還可以使用賦值指派給特定的對象。另外，還可以使用賦值運算符在列標題和為列定義值的表達式之值運算符在列標題和為列定義值的表達式之間建立關系。間建立關系。3位運算符位運算符位運算符使我們能夠在整型位運算符使我們能夠在整型數據或者二進制數據（數據或者二進制數據（image 數數據類型除外）之間執行位操作。據類型除外）之間執

7、行位操作。此外，在位運算符左右兩側的操此外，在位運算符左右兩側的操作數不能同時是二進制數據。作數不能同時是二進制數據。 表表2-1 位運算符位運算符 運運 算算 符符 含含 義義 &（按位（按位 AND）按位按位 AND（兩個操作數）。（兩個操作數）。|（按位（按位 OR）按位按位 OR（兩個操作數）。（兩個操作數）。（按位互斥（按位互斥 OR）按位互斥按位互斥 OR（兩個操作數）。（兩個操作數）。4比較運算符比較運算符比較運算符用于比較兩個表達式的大小或比較運算符用于比較兩個表達式的大小或是否相同，其比較的結果是布爾值，即是否相同，其比較的結果是布爾值，即TRUE（表示表達式的結果為

8、真）、（表示表達式的結果為真）、FALSE（表示表（表示表達式的結果為假）以及達式的結果為假）以及UNKNOWN。除了。除了 text、ntext 或或 image 數據類型的表達式外，比較運算數據類型的表達式外，比較運算符可以用于所有的表達式。符可以用于所有的表達式。 5邏輯運算符邏輯運算符邏輯運算符可以把多個邏輯表邏輯運算符可以把多個邏輯表達式連接起來。邏輯運算符包括達式連接起來。邏輯運算符包括AND、OR和和NOT等運算符。邏輯運等運算符。邏輯運算符和比較運算符一樣，返回帶有算符和比較運算符一樣，返回帶有 TRUE 或或 FALSE 值的布爾數據類型。值的布爾數據類型。6字符串串聯運算符

9、字符串串聯運算符字符串串聯運算符允許通字符串串聯運算符允許通過加號過加號 (+) 進行字符串串聯，進行字符串串聯，這個加號即被稱為字符串串聯這個加號即被稱為字符串串聯運算符。例如對于語句運算符。例如對于語句SELECT abc+def，其結果，其結果為為abcdef。運算符的優先等級從高到低如下所示運算符的優先等級從高到低如下所示 括號：（）；括號：（）；乘、除、求模運算符：乘、除、求模運算符：*、/、%；加減運算符：加減運算符：+、- ；比較運算符：比較運算符：=、=、=、!=、!、!；位運算符：位運算符：、&、|；邏輯運算符：邏輯運算符：NOT；邏輯運算符：邏輯運算符：AND；邏輯

10、運算符：邏輯運算符：OR。4 函數函數在在Transact-SQL語言中，函數被用來執行一些特殊的運算以語言中，函數被用來執行一些特殊的運算以支持支持SQL Server的標準命令。的標準命令。Transact-SQL 編程語言提供了三種編程語言提供了三種函數：函數： 行集函數：行集函數可以在行集函數：行集函數可以在Transact-SQL語句中當作表引語句中當作表引用。用。 聚合函數：聚合函數用于對一組值執行計算并返回一個單一聚合函數：聚合函數用于對一組值執行計算并返回一個單一的值。的值。 標量函數：標量函數用于對傳遞給它的一個或者多個參數值標量函數：標量函數用于對傳遞給它的一個或者多個參數

11、值進行處理和計算，并返回一個單一的值。進行處理和計算，并返回一個單一的值。 SQL Server中最常用的幾種函數中最常用的幾種函數 字符串函數字符串函數日期和時間函數日期和時間函數數學函數數學函數轉換函數轉換函數系統函數系統函數聚合函數聚合函數其它函數其它函數 字符串函數字符串函數字符串函數可以對二進制數據、字符串和表達式字符串函數可以對二進制數據、字符串和表達式執行不同的運算，大多數字符串函數只能用于執行不同的運算，大多數字符串函數只能用于char和和varchar數據類型以及明確轉換成數據類型以及明確轉換成char和和varchar的數據類型，少數幾個字符串函數也可以用于的數據類型，少數

12、幾個字符串函數也可以用于binary和和varbinary數據類型。此外，某些字符串函數據類型。此外，某些字符串函數還能夠處理數還能夠處理text、ntext、image數據類型的數據。數據類型的數據。 字符串函數的分類：字符串函數的分類：基本字符串函數：基本字符串函數：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函數：字符串查找函數：CHARINDEX、PATINDEX。長度和分析函數：長度和分析函數：DATALENGTH、SUBSTRING、RIGHT。轉換函數：轉換函數：ASCH、CHAR、STR、SOUNDEX、

13、DIFFERENCE。 2日期和時間函數日期和時間函數日期和時間函數用于對日期和時間日期和時間函數用于對日期和時間數據進行各種不同的處理和運算，數據進行各種不同的處理和運算，并返回一個字符串、數字值或日期并返回一個字符串、數字值或日期和時間值。在和時間值。在SQL Server 2000中，中，日期和時間函數的類型如表日期和時間函數的類型如表2-5所所示；另外，表示；另外，表2-6列出了日期類型列出了日期類型的名稱、縮寫形式以及可接受的值。的名稱、縮寫形式以及可接受的值。表表2-5 日期和時間函數的類型日期和時間函數的類型函函 數數參參 數數DATEADD(datepart,number,da

14、te)DATEDIFF(datepart,date1,date2)DATENAME(datepart,date)DATEPART(datepart,date)DAY(date)GETDATE（）MONTH(date)YEAR( date )從從GETDATE函數返回的日期中函數返回的日期中提取月份數。提取月份數。 SELECT DATEPART(month, GETDATE() AS Month Number運行結果為：運行結果為：Month Number-4 從日期從日期 04/12/2006 中返回月份數、天數和年份中返回月份數、天數和年份數。數。SELECT MONTH(04/12/20

15、06), DAY(04/12/2006),YEAR(04/12/2006)運行結果為：運行結果為：- - -4 12 20063數學函數數學函數數學函數用于對數字表達式進行數學函數用于對數字表達式進行數學運算并返回運算結果。數學數學運算并返回運算結果。數學函數可以對函數可以對SQL Server提供的數提供的數字數據（字數據（decimal、integer、float、real、money、smallmoney、smallint 和和 tinyint）進行處理。）進行處理。 在同一表達式中使用在同一表達式中使用CEILING（）、（）、FLOOR（）、（）、ROUND（）函數。（）函數。sel

16、ect ceiling(13.4), floor(13.4), round(13.4567,3)運行結果為：運行結果為：- - -14 13 13.45704轉換函數轉換函數一般情況下，一般情況下，SQL Server會自動處理某些數據類型會自動處理某些數據類型的轉換。例如，如果比較的轉換。例如，如果比較 char 和和 datetime 表達式、表達式、smallint 和和 int 表達式、或不同長度的表達式、或不同長度的 char 表達式，表達式，SQL Server 可以將它們自動轉換，這種轉換被稱為可以將它們自動轉換，這種轉換被稱為隱性轉換。但是，無法由隱性轉換。但是，無法由SQL

17、Server自動轉換的或自動轉換的或者是者是SQL Server自動轉換的結果不符合預期結果的，自動轉換的結果不符合預期結果的，就需要使用轉換函數做顯示轉換。轉換函數有兩個：就需要使用轉換函數做顯示轉換。轉換函數有兩個：CONVERT和和CAST。CONVERT和和CAST函數函數C A S T ( e x p r e s s i o n A S data_type )CONVERT函數允許用戶把表達式從一函數允許用戶把表達式從一種數據類型轉換成另一種數據類型，還種數據類型轉換成另一種數據類型，還允許把日期轉換成不同的樣式。其語法允許把日期轉換成不同的樣式。其語法形式為：形式為：CONVERT

18、 (data_type(length),expression ,style)USE pubsSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20) LIKE 15% AND type = trad_cook運行結果為：運行結果為：Title ytd_sales-Fifty Years in Buckingham Palace Kitchens 150965系統函數系統函數系統函數用于返回有關系統函數用于返回有關SQL Server系統、用戶、數據庫和數據庫對象的系統、用戶、數據庫和數據庫對象的信息。系統函數可以讓用戶

19、在得到信信息。系統函數可以讓用戶在得到信息后，使用條件語句，根據返回的信息后，使用條件語句，根據返回的信息進行不同的操作。與其它函數一樣，息進行不同的操作。與其它函數一樣，可以在可以在SELECT語句的語句的SELECT和和WHERE子句以及表達式中使用系統子句以及表達式中使用系統函數。函數。 返回返回 Northwind 數據庫的數據庫的 Employees 表表中的首列的名稱。中的首列的名稱。USE NorthwindSELECT COL_NAME(OBJECT_ID(Employees), 1)運行結果為：運行結果為：EmployeeID6聚合函數聚合函數 聚合函數可以返回整個或者幾個列

20、聚合函數可以返回整個或者幾個列或者一個列的匯總數據，它常用來或者一個列的匯總數據，它常用來計算計算SELECT語句查詢的統計值。語句查詢的統計值。聚合函數經常與聚合函數經常與 SELECT 語句的語句的 GROUP BY 子句一同使用。子句一同使用。用戶自定義函數CREATE FUNCTION 創建用戶定義函數，它是返回值的已保存的創建用戶定義函數，它是返回值的已保存的 Transact-SQL 例程。用戶定義函數不例程。用戶定義函數不能用于執行一組修改全局數據庫狀態的操作。與系統函數一樣，用戶定義函數可以從能用于執行一組修改全局數據庫狀態的操作。與系統函數一樣，用戶定義函數可以從查詢中喚醒調

21、用。也可以像存儲過程一樣，通過查詢中喚醒調用。也可以像存儲過程一樣，通過 EXECUTE 語句執行。語句執行。用戶定義函數用用戶定義函數用 ALTER FUNCTION 修改，用修改，用 DROP FUNCTION 除去。除去。CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type WITH , .n AS BEGIN function_body RETURN scalar_ex

22、pressionEND5 流程控制語句流程控制語句流程控制語句是指那些用來控制程序流程控制語句是指那些用來控制程序執行和流程分支的命令，在執行和流程分支的命令，在SQL Server 2000中，流程控制語句主要中，流程控制語句主要用來控制用來控制SQL語句、語句塊或者存儲語句、語句塊或者存儲過程的執行流程。過程的執行流程。 使用BEGINEND可以將多條T-SQL語句封裝起來，構成一個獨立的語句塊。BEGIN關鍵字表示語句塊的開始，END關鍵字表示語句塊的結束，它們必須成對出現。BEGINEND的語法格式如下：BEGIN sql_statement | statement_block END

23、Begin EndIFELSE IFELSE語句可以控制程序按條件執行。當IF關鍵字后的條件滿足（布爾表達式返回TRUE 時），則在執行IF關鍵字及其條件之后的T-SQL 語句。否則，就執行ELSE關鍵字后的T-SQL語句（若ELSE部分存在）。IFELSE語句的語法格式如下：IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 查詢課程號為“105237”的課程的平均分是否超過了85分，若超過，則輸出考出了高分的信息。USE EducationalGODECLARE Course_ID varchar(10)SELECT Course_ID = 105237 /* 查詢的課程號 */ IF ( SELECT AVG(Grade) FROM Grade WHERE CourseID = Course_ID ) 85/* 平均分大于85分 */BEGIN SELECT Course_ID = CourseName /* 從課程信息表中取課程名 */ FRO