第5章++TSQL語言1

1、第5章 T-SQL語言5.1 SQL語言與語言與T-SQL語言語言5.2 常量、變量與數(shù)據(jù)類型常量、變量與數(shù)據(jù)類型5.3 運(yùn)算符與表達(dá)式運(yùn)算符與表達(dá)式5.4 流程控制語句流程控制語句5.5 系統(tǒng)內(nèi)置函數(shù)系統(tǒng)內(nèi)置函數(shù)5.6 用戶定義函數(shù)用戶定義函數(shù)5.1 SQL語言與T-SQL語言b 1什么是什么是SQL語言語言SQL語言的全名是結(jié)構(gòu)化查詢語言（Structured Query Language），是用于數(shù)據(jù)庫中的標(biāo)準(zhǔn)數(shù)據(jù)查詢語言，IBM公司最早使用該語言在其開發(fā)的數(shù)據(jù)庫系統(tǒng)中。1986年10月，美國ANSI對 SQL進(jìn)行規(guī)范后，以此作為關(guān)系數(shù)據(jù)庫管理系統(tǒng)的標(biāo)準(zhǔn)語言。作為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，它

2、已被眾多商用數(shù)據(jù)庫管理系統(tǒng)產(chǎn)品所采用，不過，不同的數(shù)據(jù)庫管理系統(tǒng)在其實(shí)踐過程中都對SQL規(guī)范做了某些改變和擴(kuò)充。所以，實(shí)際上，不同數(shù)據(jù)庫管理系統(tǒng)之間的SQL語言不能完全通用。例如，微軟公司的MS SQL-Server支持的是T-SQL，而甲骨文公司的Oracle 數(shù)據(jù)庫所使用的SQL語言則是PL-SQL。b 2什么是什么是T-SQL語言語言T-SQL是SQL語言的一種版本，且只能在微軟MS SQL-Server以及Sybase Adaptive Server系列數(shù)據(jù)庫上使用。T-SQL是ANSI SQL的擴(kuò)展加強(qiáng)版語言，除了提供標(biāo)準(zhǔn)的SQL命令之外，T-SQL還對SQL做了許多補(bǔ)充，提供了類似

3、C、BASIC和Pascal的基本功能，如變量說明、流控制語言、功能函數(shù)等。b 3T-SQL語言的構(gòu)成語言的構(gòu)成在SQL Server數(shù)據(jù)庫中，T-SQL語言由以下幾部分組成。（1）數(shù)據(jù)定義語言（DDL）。DDL用于執(zhí)行數(shù)據(jù)庫的任務(wù)，對數(shù)據(jù)庫以及數(shù)據(jù)庫中的各種對象進(jìn)行創(chuàng)建、刪除、修改等操作。如前所述，數(shù)據(jù)庫對象主要包括表、默認(rèn)約束、規(guī)則、視圖、觸發(fā)器、存儲過程。DDL包括的主要語句及功能如表5.1所示。語 句功 能說 明CREATE創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫對象不同數(shù)據(jù)庫對象，其CREATE語句的語法形式不同ALTER對數(shù)據(jù)庫或數(shù)據(jù)庫對象進(jìn)行修改不同數(shù)據(jù)庫對象，其ALTER語句的語法形式不同DROP刪

4、除數(shù)據(jù)庫或數(shù)據(jù)庫對象不同數(shù)據(jù)庫對象，其DROP語句的語法形式不同表5.1 DDL主要語句及功能DDL各語句的語法、使用方法及舉例請參考相關(guān)章節(jié)。（2）數(shù)據(jù)操縱語言（DML）。DML用于操縱數(shù)據(jù)庫中的各種對象，檢索和修改數(shù)據(jù)。DML包括的主要語句及功能如表5.2所示。語 句功 能說 明SELECT從表或視圖中檢索數(shù)據(jù)是使用最頻繁的SQL語句之一INSERT將數(shù)據(jù)插入到表或視圖中UPDATE修改表或視圖中的數(shù)據(jù)既可修改表或視圖的一行數(shù)據(jù)，也可修改一組或全部數(shù)據(jù)DELETE從表或視圖中刪除數(shù)據(jù)可根據(jù)條件刪除指定的數(shù)據(jù)表5.2 DML主要語句及功能DML各語句的語法、使用方法及舉例請參考相關(guān)章節(jié)。（3

5、）數(shù)據(jù)控制語言（DCL）。DCL用于安全管理，確定哪些用戶可以查看或修改數(shù)據(jù)庫中的數(shù)據(jù)。DCL包括的主要語句及功能如表5.3所示。語 句功 能說 明GRANT授予權(quán)限可把語句許可或?qū)ο笤S可的權(quán)限授予其他用戶和角色REVOKE收回權(quán)限與GRANT的功能相反，但不影響該用戶或角色從其他角色中作為成員繼承許可權(quán)限D(zhuǎn)ENY收回權(quán)限，并禁止從其他角色繼承許可權(quán)限功能與REVOKE相似，不同之處是，除收回權(quán)限外，還禁止從其他角色繼承許可權(quán)限表5.3 DCL主要語句及功能DCL各語句的語法、使用方法及舉例請參考相關(guān)章節(jié)。（4）T-SQL增加的語言元素。這部分不是ANSI SQL所包含的內(nèi)容，而是微軟為了用戶

6、編程的方便而增加的語言元素。這些語言元素包括變量、運(yùn)算符、流程控制語句、函數(shù)等。這些T-SQL語句都可以在查詢分析器中交互執(zhí)行。本章將介紹這部分增加的語言元素。5.2 常量、變量與數(shù)據(jù)類型5.2.1 常量常量根據(jù)常量值的不同類型，常量分為字符串常量、整型常量、實(shí)型常量、日期時間常量、貨幣常量、唯一標(biāo)識常量。各類常量舉例說明如下。b 1字符串常量字符串常量字符串常量分為ASCII字符串常量和Unicode字符串常量。（1）ASCII字符串常量。ASCII字符串常量是用單引號括起來，由ASCII字符構(gòu)成的符號串。ASCII字符串常量舉例如下：ChinaHow do you!OBbaar/*如果單引

7、號中的字符串包含引號，可以使用兩個單引號來表示嵌入的單引號。*/5.2.1 常量（2）Unicode 字符串常量。Unicode 字符串常量與ASCII字符串常量相似，但它前面有一個N標(biāo)識符（N代表 SQL-92標(biāo)準(zhǔn)中的國際語言National Language），N前綴必須為大寫字母。Unicode字符串常量舉例如下：NChina NHow do you!Unicode 數(shù)據(jù)中的每個字符用兩個字節(jié)存儲，而每個ASCII字符用一個字節(jié)存儲。b 2整型常量整型常量按照不同表示方式，整型常量又分為二進(jìn)制整型常量、十六進(jìn)制整型常量和十進(jìn)制整型常量。十六進(jìn)制整型常量的表示：前輟 0 x 后跟十六進(jìn)制數(shù)

8、字串。十六進(jìn)制常量舉例：0 xEBF0 x69048AEFDD010E0 x /*空十六進(jìn)制常量*/二進(jìn)制整型常量的表示：即數(shù)字 0 或1，并且不使用引號。如果使用一個大于 1 的數(shù)字，它將被轉(zhuǎn)換為1。十進(jìn)制整型常量即不帶小數(shù)點(diǎn)的十進(jìn)制數(shù)，例如，18942+145345234-2147483648b 3實(shí)型常量實(shí)型常量實(shí)型常量有定點(diǎn)表示和浮點(diǎn)表示兩種方式，舉例如下。定點(diǎn)表示：1894.12042.0+145345234.2234-2147483648.10浮點(diǎn)表示：101.5E50.5E-2+123E-3-12E5b 4日期時間常量日期時間常量日期時間常量：用單引號將表示日期時間的字符串括起來

9、構(gòu)成。SQL Server可以識別如下格式的日期和時間。 字母日期格式，如April 20, 2000；數(shù)字日期格式，如4/15/1998，1998-04-15；未分隔的字符串格式，如20001207；以下是時間常量的例子：14:30:2404:24:PM以下是日期時間常量的例子：April 20, 2000 14:30:24b 5money常量常量money常量是以“$”作為前綴的一個整型或?qū)嵭统Ａ繑?shù)據(jù)。下面是money常量的例子：$12$542023-$45.56+$423456.99b 6uniqueidentifier常量常量uniqueidentifier 常量是用于表示全局唯一標(biāo)識

10、符（GUID）值的字符串?？梢允褂米址蚴M(jìn)制字符串格式指定。例如，6F9619FF-8A86-D011-B42D-00004FC964FF0 xff19966f868b11d0b42d00c04fc964ff5.2.2 數(shù)據(jù)類型b 1系統(tǒng)數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型系統(tǒng)數(shù)據(jù)類型又稱為基本數(shù)據(jù)類型。在第3章已詳細(xì)地介紹了系統(tǒng)數(shù)據(jù)類型，此處不再贅述。b 2用戶自定義數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型可看做系統(tǒng)數(shù)據(jù)類型的別名。在多表操作的情況下，當(dāng)多個表中的列要存儲相同類型的數(shù)據(jù)時，往往要確保這些列具有完全相同的數(shù)據(jù)類型、長度和為空性（數(shù)據(jù)類型是否允許空值）。用戶自定義數(shù)據(jù)類型并不是真正的數(shù)

11、據(jù)類型，它只是提供了一種提高數(shù)據(jù)庫內(nèi)部元素和基本數(shù)據(jù)類型之間一致性的機(jī)制。用戶自定義數(shù)據(jù)類型student_num后，可以重新設(shè)計(jì)學(xué)生成績管理數(shù)據(jù)庫表XSB、CJB結(jié)構(gòu)中的學(xué)號字段，如表5.4、表5.5和表5.6所示。依賴的系統(tǒng)類型值允許的長度為 空 性char6NOT NULL表5.4 自定義類型student_num字 段 名類 型字 段 名類 型學(xué)號student_num 學(xué)號student_num表5.5 表XSB中學(xué)號字段的重新設(shè)計(jì) 表5.6 表CJB中學(xué)號字段的重新設(shè)計(jì)通過上例可知：要使用用戶自定義類型，首先應(yīng)定義該類型，然后用這種類型來定義字段或變量。在創(chuàng)建用戶自定義數(shù)據(jù)類型時首

12、先應(yīng)考慮如下三個屬性： 數(shù)據(jù)類型名稱；新數(shù)據(jù)類型所依據(jù)的系統(tǒng)數(shù)據(jù)類型（又稱為基類型）；為空性。如果為空性未明確定義，系統(tǒng)將依據(jù)數(shù)據(jù)庫或連接的ANSI Null 默認(rèn)設(shè)置進(jìn)行指派。創(chuàng)建用戶自定義數(shù)據(jù)類型的方法如下。（1）使用對象資源管理器定義。步驟如下。第1步：啟動“SQL Server Management Studio”，在“對象資源管理器”中展開“數(shù)據(jù)庫PXSCJ可編程性”，右擊“類型”，選擇“新建”選項(xiàng)，再選擇“新建用戶定義數(shù)據(jù)類型”，彈出“新建用戶定義數(shù)據(jù)類型”窗口。第2步：在“名稱”文本框中輸入自定義的數(shù)據(jù)類型名稱，如student_num。在“數(shù)據(jù)類型”下拉框中選擇自定義數(shù)據(jù)類型所

13、基于的系統(tǒng)數(shù)據(jù)類型，如char。在“長度”欄中填寫要定義的數(shù)據(jù)類型的長度，如6。其他選項(xiàng)使用默認(rèn)值，如圖5.1所示，單擊“確定”按鈕即可完成創(chuàng)建。圖5.1 “新建用戶定義數(shù)據(jù)類型屬性”窗口（2）使用命令定義。在SQL Server 2008中，使用CREATE TYPE語句來實(shí)現(xiàn)用戶數(shù)據(jù)類型的定義。語法格式：CREATE TYPE schema_name. type_name FROM base_type ( precision , scale ) NULL | NOT NULL ; 根據(jù)上述語法，定義描述學(xué)號字段的數(shù)據(jù)類型的語句如下：CREATE TYPE student_numFROM c

14、har(6) NOT NULL（3）刪除用戶自定義數(shù)據(jù)類型。在SSMS中刪除用戶自定義數(shù)據(jù)類型的主要步驟如下：在對象資源管理器中展開數(shù)據(jù)庫“PXSCJ可編程性類型”，在“用戶定義數(shù)據(jù)類型”中選擇類型“dbo.student_num”，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“刪除”菜單項(xiàng)，打開“刪除對象”窗口后單擊“確定”按鈕即可（實(shí)際不做操作）。（4）使用命令刪除用戶自定義數(shù)據(jù)類型。使用命令方式刪除自定義數(shù)據(jù)類型可以使用DROP TYPE語句。語法格式：DROP TYPE schema_name. type_name ; 例如，刪除前面定義的student_num類型的語句為DROP TYPE st

15、udent_num（5）利用用戶自定義數(shù)據(jù)類型定義字段。在定義類型后，接著應(yīng)考慮定義這種類型的字段，同樣可以利用對象資源管理器和T-SQL命令兩種方式實(shí)現(xiàn)。讀者可以參照第2章進(jìn)行定義，不同點(diǎn)只是數(shù)據(jù)類型為用戶自定義類型，而不是系統(tǒng)類型。例如，在對象資源管理器中對于XSB表學(xué)號字段的定義如圖5.2所示。定義 student_num 后， 設(shè)計(jì)表結(jié)構(gòu)時此 類型可用 圖5.2 使用用戶自定義數(shù)據(jù)類型定義XSB表利用命令方式定義XSB表結(jié)構(gòu)：CREATE TABLE XSB( 學(xué)號 student_num NOT NULL PRIMARY KEY,/*將學(xué)號定義為student_num類型*/ 姓名

16、char(8) NOT NULL, 性別bit NULL DEFAULT 1, 出生時間 datetime NULL, 專業(yè) char(12) NULL, 總學(xué)分 int NULL, 備注 varchar(500) NULL)b 3用戶自定義表數(shù)據(jù)類型用戶自定義表數(shù)據(jù)類型SQL Server 2008還提供了一種新的用戶自定義數(shù)據(jù)類型，稱為用戶自定義表數(shù)據(jù)類型（User-defined Table Types）。這種數(shù)據(jù)類型也由用戶自行定義，可以作為參數(shù)提供給語句、存儲過程或者函數(shù)。創(chuàng)建自定義表數(shù)據(jù)類型也使用CREATE TYPE語句，語法格式如下：CREATE TYPE schema_nam

17、e. type_name AS TABLE ( ,.n ) ; 【例例5.1】 創(chuàng)建用戶自定義表數(shù)據(jù)類型，包含CJB表中的所有列。CREATE TYPE CJB_tabletypeAS TABLE(學(xué)號char(6)NOT NULL,課程號char(3)NOT NULL,成績intNOT NULL,PRIMARY KEY(學(xué)號, 課程號)5.2.3 變量b 1變量變量變量名必須是一個合法的標(biāo)識符。（1）標(biāo)識符。在SQL Server中標(biāo)識符分為兩類。常規(guī)標(biāo)識符：以ASCII字母、Unicode字母、下劃線（_）、或#開頭，后續(xù)可跟一個或若干個ASCII字符、Unicode字符、下劃線（_）、美

18、元符號（$）、或#，但不能全為下劃線（_）、或#。注意：常規(guī)標(biāo)識符不能是T-SQL的保留字。常規(guī)標(biāo)識符中不允許嵌入空格或其他特殊字符。分隔標(biāo)識符：包含在雙引號（）或者方括號（ ）內(nèi)的常規(guī)標(biāo)識符或不符合常規(guī)標(biāo)識符規(guī)則的標(biāo)識符。標(biāo)識符允許的最大長度為128個字符。符合常規(guī)標(biāo)識符格式規(guī)則的標(biāo)識符可以分隔，也可以不分隔。對不符合標(biāo)識符規(guī)則的標(biāo)識符必須進(jìn)行分隔。（2）變量的分類。SQL Server中變量可分為兩類。全局變量：全局變量由系統(tǒng)提供且預(yù)先聲明，通過在名稱前加兩個“”來區(qū)別于局部變量。T-SQL全局變量作為函數(shù)引用。例如，ERROR返回執(zhí)行的上一個T-SQL語句的錯誤號；CONNECTIONS

19、返回自上次啟動SQL Server以來連接或試圖連接的次數(shù)。全局變量的意義及使用請參考附錄B。局部變量：局部變量用于保存單個數(shù)據(jù)值。例如，保存運(yùn)算的中間結(jié)果，作為循環(huán)變量等。當(dāng)首字母為“”時，表示該標(biāo)識符為局部變量名；當(dāng)首字母為“#”時，此標(biāo)識符為一臨時數(shù)據(jù)庫對象名，若開頭含一個“#”，表示局部臨時數(shù)據(jù)庫對象名；若開頭含兩個“#”，表示全局臨時數(shù)據(jù)庫對象名。b 2局部變量的使用局部變量的使用（1）局部變量的定義與賦值。 局部變量的定義。在批處理或過程中用DECLARE語句聲明局部變量，所有局部變量在聲明后均初始化為NULL。語法格式：DECLARE local_variable data_ty

20、pe = value ,.n說明如下。local_variable：局部變量名，應(yīng)為常規(guī)標(biāo)識符。前面的“”表示是局部變量。data_type：數(shù)據(jù)類型，用于定義局部變量的類型，可為系統(tǒng)類型或自定義類型。=value：為變量賦值，值可以是常量或表達(dá)式，但它必須與變量聲明類型匹配。n：表示可定義多個變量，各變量間用逗號隔開。 局部變量的賦值。當(dāng)聲明局部變量后，可用SET或SELECT語句為其賦值。用SET語句賦值：將DECLARE語句創(chuàng)建的局部變量設(shè)置為給定表達(dá)式的值。語法格式：SET local_variableexpression【例例5.2】 創(chuàng)建局部變量var1、var2并賦值，然后輸出變

21、量的值。DECLARE var1 char(10) ,var2 char(30)SET var1=中國 /*一個SET語句只能為一個變量賦值*/SET var2=var1+是一個偉大的國家SELECT var1, var2GO執(zhí)行結(jié)果如下：【例例5.3】 創(chuàng)建一個名為sex的局部變量，并在SELECT語句中使用該局部變量查找表XSB中所有女同學(xué)的學(xué)號、姓名。USE PXSCJGO DECLARE sex bitSET sex=0SELECT 學(xué)號, 姓名FROM XSBWHERE 性別=sex執(zhí)行結(jié)果如下：【例例5.4】 使用查詢?yōu)樽兞抠x值。DECLARE student char(8)SET

22、 student=(SELECT 姓名 FROM XSB WHERE 學(xué)號= 081101)SELECT student用SELECT語句賦值：語法格式：SELECT local_variable=expression ,n關(guān)于SELECT，需說明以下幾點(diǎn)：lSELECT local_variable 通常用于將單個值返回到變量中。如果expression為列名，則返回多個值，此時將返回的最后一個值賦給變量。l如果SELECT 語句沒有返回行，變量將保留當(dāng)前值。l如果expression是不返回值的標(biāo)量子查詢，則將變量設(shè)為 NULL。l一個SELECT語句可以初始化多個局部變量。【例例5.5】

23、 使用SELECT語句為局部變量賦值。DECLARE var1 nvarchar(30)SELECT var1 =劉豐SELECT var1 AS NAME執(zhí)行結(jié)果如下：【例例5.6】 為局部變量賦空值。DECLARE var1 nvarchar(30)SELECT var1 = 劉豐SELECT var1 =(SELECT 姓名FROM XSBWHERE 學(xué)號= 089999)SELECT var1 AS NAME執(zhí)行結(jié)果如下：（2）局部游標(biāo)變量的定義與賦值。 局部游標(biāo)變量的定義。語法格式：DECLARE cursor_variable_name CURSOR ,.ncursor_varia

24、ble_name是局部游標(biāo)變量名，應(yīng)為常規(guī)標(biāo)識符。前面的“”表示是局部的。CURSOR表示該變量是游標(biāo)變量。 局部游標(biāo)變量的賦值。利用SET語句為一個游標(biāo)變量賦值，有三種情況：將一個已存在的并且賦值的游標(biāo)變量的值賦給另一個局部游標(biāo)變量。將一個已聲明的游標(biāo)名賦給指定的局部游標(biāo)變量。聲明一個游標(biāo)，同時將其賦給指定的局部游標(biāo)變量。上述三種情況的語法描述如下。語法格式：SET cursor_variable = cursor_variable /*將一個已賦值的游標(biāo)變量的值賦給一個目標(biāo)游標(biāo)變量*/ | cursor_name /*將一個已聲明的游標(biāo)名賦給游標(biāo)變量*/ | CURSOR 子句 /*游標(biāo)聲

25、明*/ 游標(biāo)變量的使用步驟如下。定義游標(biāo)變量給游標(biāo)變量賦值打開游標(biāo)利用游標(biāo)讀取行（記錄）使用結(jié)束后關(guān)閉游標(biāo)刪除游標(biāo)的引用?！纠?.7】 使用游標(biāo)變量。USE PXSCJGODECLARE CursorVar CURSOR /*定義游標(biāo)變量*/SET CursorVar = CURSOR SCROLL DYNAMIC /*為游標(biāo)變量賦值*/FORSELECT 學(xué)號, 姓名FROM XSBWHERE 姓名 LIKE 王%OPEN CursorVar /*打開游標(biāo)*/FETCH NEXT FROM CursorVarFETCH NEXT FROM CursorVar /*通過游標(biāo)讀行記錄*/CLO

26、SE CursorVarDEALLOCATE CursorVar /*刪除對游標(biāo)的引用*/（3）表數(shù)據(jù)類型變量的定義與賦值。語法格式：DECLARE table_variable_name AS TABLE ( | ,. ) 【例例5.8】 聲明一個表數(shù)據(jù)類型變量并向變量中插入數(shù)據(jù)。DECLARE var_table AS TABLE(num char(6) NOT NULL PRIMARY KEY,name char(8) NOT NULL,sex bit NULL)/*聲明變量*/INSERT INTO var_table SELECT 學(xué)號,姓名,性別 FROM XSB/*插入數(shù)據(jù)*/S

27、ELECT * FROM var_table/*查看內(nèi)容*/5.3 運(yùn)算符與表達(dá)式b 1算術(shù)運(yùn)算符算術(shù)運(yùn)算符算術(shù)運(yùn)算符在兩個表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個表達(dá)式可以是任何數(shù)字?jǐn)?shù)據(jù)類型 。b算術(shù)運(yùn)算符有+（加）、-（減）、*（乘）、/（除）和%（求模）5種運(yùn)算。+（加）和-（減）運(yùn)算符還可用于對日期時間類型的值進(jìn)行算術(shù)運(yùn)算。b 2位運(yùn)算符位運(yùn)算符位運(yùn)算符在兩個表達(dá)式之間執(zhí)行位操作，這兩個表達(dá)式的類型可為整型或與整型兼容的數(shù)據(jù)類型（如字符型等，但不能為image類型）。位運(yùn)算符如表5.7所示。 運(yùn) 算 符運(yùn) 算 規(guī) 則&兩個位均為1時，結(jié)果為1，否則為0|只要一個位為1，則結(jié)果為1，否則為0

28、兩個位值不同時，結(jié)果為1，否則為0表5.7 位運(yùn)算符【例例5.9】 在master數(shù)據(jù)庫中，建立表bitop，并插入一行，然后將a字段和 b字段列上值進(jìn)行按位與運(yùn)算。USE masterGOCREATE TABLE bitop( a int NOT NULL,b int NOT NULL)INSERT bitop VALUES (168, 73)SELECT a & b, a | b, a bFROM bitopGO執(zhí)行結(jié)果如下：說明：a(168)的二進(jìn)制表示為0000 0000 1010 1000；b(73)的二進(jìn)制表示為 0000 0000 0100 1001。在這兩個值之間進(jìn)行的

29、位運(yùn)算如下。(a &b)： 0000 0000 1010 1000 0000 0000 0100 1001 0000 0000 0000 1000（十進(jìn)制值為8）(a | b)： 0000 0000 1010 1000 0000 0000 0100 1001 0000 0000 1110 1001（十進(jìn)制值為233）(a b)： 0000 0000 1010 1000 0000 0000 0100 1001 0000 0000 1110 0001（十進(jìn)制值為225）b 3比較運(yùn)算符比較運(yùn)算符比較運(yùn)算符（又稱關(guān)系運(yùn)算符）如表5.8所示，用于測試兩個表達(dá)式的值是否相同，其運(yùn)算結(jié)果為邏輯值，

30、可以為TRUE、FALSE 及 UNKNOWN三者之一。運(yùn) 算 符含 義運(yùn) 算 符含 義=相等大于、!=不等于小于!=大于等于!不大于表5.8 比較運(yùn)算符除text、ntext或image類型的數(shù)據(jù)外，比較運(yùn)算符可以用于所有的表達(dá)式，下面的例子用于查詢指定學(xué)號的學(xué)生在XSB表中的信息，其中，IF語句為條件判斷語句，將在5.4節(jié)介紹。USE PXSCJGODECLARE student char(10)SET student = 081101IF (student 0)SELECT *FROM XSBWHERE 學(xué)號= student執(zhí)行結(jié)果如下：b4邏輯運(yùn)算符邏輯運(yùn)算符邏輯運(yùn)算符用于對某個條件

31、進(jìn)行測試，運(yùn)算結(jié)果為TRUE或FALSE。SQL Server提供的邏輯運(yùn)算符如表5.9所示。這里的邏輯運(yùn)算符在SELECT語句的WHERE子句中使用過，此處再做一些補(bǔ)充。運(yùn) 算 符運(yùn) 算 規(guī) 則AND如果兩個操作數(shù)值都為TRUE，則運(yùn)算結(jié)果為 TRUEOR如果兩個操作數(shù)中有一個為TRUE，則運(yùn)算結(jié)果為TRUENOT若一個操作數(shù)值為TRUE，則運(yùn)算結(jié)果為FALSE，否則為TRUEALL如果每個操作數(shù)值都為TRUE，則運(yùn)算結(jié)果為TRUEANY在一系列操作數(shù)中只要有一個為 TRUE，則運(yùn)算結(jié)果為TRUEBETWEEN如果操作數(shù)在指定的范圍內(nèi)，則運(yùn)算結(jié)果為TRUEEXISTS如果子查詢包含一些行，則

32、運(yùn)算結(jié)果為TRUEIN如果操作數(shù)值等于表達(dá)式列表中的一個，則運(yùn)算結(jié)果為TRUELIKE如果操作數(shù)與一種模式相匹配，則運(yùn)算結(jié)果為TRUESOME如果在一系列操作數(shù)中，有些值為TRUE，則運(yùn)算結(jié)果為TRUE表5.9 邏輯運(yùn)算符（1）ANY、SOME、ALL、IN的使用?？梢詫LL或ANY關(guān)鍵字與比較運(yùn)算符組合進(jìn)行子查詢。SOME的用法與ANY相同。以 比較運(yùn)算符為例：lALL表示大于每一個值，即大于最大值。例如，ALL(5, 2, 3)表示大于5。因此，使用ALL的子查詢也可用MAX集函數(shù)實(shí)現(xiàn)。lANY表示至少大于一個值，即大于最小值。例如，ANY (7, 2, 3)表示大于2。因此，使用ANY

33、的子查詢也可用MIN集函數(shù)實(shí)現(xiàn)。l=ANY運(yùn)算符與IN等效。lALL與NOT IN等效?！纠?.10】 查詢成績高于“林一帆”最高成績的學(xué)生姓名、課程名及成績。USE PXSCJGOSELECT 姓名, 課程名, 成績FROM XSB, CJB, KCBWHERE 成績 ALL( SELECT b.成績FROM XSB a, CJB bWHERE a.學(xué)號= b.學(xué)號 AND a.姓名= 林一帆)AND XSB.學(xué)號=CJB.學(xué)號AND KCB.課程號=CJB.課程號AND 姓名林一帆（2）BETWEEN的使用。語法格式：test_expression NOT BETWEEN begin_e

34、xpression AND end_expression如果test_expression的值大于或等于begin_expression的值并且小于或等于 end_expression的值，則運(yùn)算結(jié)果為TRUE，否則為FALSE。test_expression為測試表達(dá)式，begin_expression和end_expression指定測試范圍，三個表達(dá)式的類型必須相同。NOT關(guān)鍵字表示對謂詞BETWEEN的運(yùn)算結(jié)果取反。【例例5.11】 查詢總學(xué)分在 4050之間的學(xué)生學(xué)號和姓名。SELECT 學(xué)號, 姓名, 總學(xué)分FROM XSBWHERE 總學(xué)分 BETWEEN 40 AND 50使用

35、 = 和 = 40 AND 總學(xué)分, =, =, , !=, !, !b。若表達(dá)式的結(jié)果是由不同類型數(shù)據(jù)組成的一行值，則這種表達(dá)式叫做行表達(dá)式。例如，（學(xué)號，王林，計(jì)算機(jī)，50*10）；當(dāng)學(xué)號列的值為081101時，這個行表達(dá)式的值就為（081101，王 林，計(jì)算機(jī)，500）。若表達(dá)式的結(jié)果為0個、1個或多個行表達(dá)式的集合，那么這個表達(dá)式就叫做表表達(dá)式。表達(dá)式一般用在SELECT以及SELECT語句的WHERE子句中。5.4 流程控制語句在設(shè)計(jì)程序時，常常需要利用各種流程控制語句，改變計(jì)算機(jī)的執(zhí)行流程以滿足程序設(shè)計(jì)的需要。在SQL Server中提供了如表5.11所示的流程控制語句。控 制 語

36、 句說 明控 制 語 句說 明BEGINEND語句塊CONTINUE用于重新開始下一次循環(huán)IFELSE條件語句BREAK用于退出最內(nèi)層的循環(huán)CASE分支語句RETURN無條件返回GOTO無條件轉(zhuǎn)移語句WAITFOR為語句的執(zhí)行設(shè)置延遲WHILE循環(huán)語句表5.11 SQL Server流程控制語句【例例5.16】 以下程序用于查詢總學(xué)分大于42的學(xué)生人數(shù)。USE PXSCJGODECLARE num intSELECT num=(SELECT COUNT(姓名) FROM XSB WHERE 總學(xué)分42)IF num0SELECT num AS 總學(xué)分42的人數(shù)5.4.1 BEGINEND語句塊

37、在T-SQL中可以定義BEGINEND語句塊。當(dāng)要執(zhí)行多條T-SQL語句時，就需要使用BEGINEND將這些語句定義成一個語句塊，作為一組語句來執(zhí)行。語法格式如下：BEGIN sql_statement | statement_block END 關(guān)鍵字BEGIN是T-SQL語句塊的起始位置，END標(biāo)識同一個T-SQL語句塊的結(jié)尾。sql_statement是語句塊中的T-SQL語句。BEGINEND可以嵌套使用，statement_block表示使用BEGINEND定義的另一個語句塊。例如，USE PXSCJGOBEGINSELECT * FROM XSBSELECT * FROM KCBE

38、ND5.4.2 條件語句在程序中如果要對給定的條件進(jìn)行判定，當(dāng)條件為真或假時分別執(zhí)行不同的 T-SQL 語句，可用IFELSE語句實(shí)現(xiàn)。語法格式：IF Boolean_expression /*條件表達(dá)式*/ sql_statement | statement_block /*條件表達(dá)式為真時執(zhí)行*/ ELSE sql_statement | statement_block /*條件表達(dá)式為假時執(zhí)行*/由上述語法格式可看出，條件語句分帶ELSE部分和不帶ELSE部分兩種使用形式。（1）帶ELSE部分：IF 條件表達(dá)式A /* T-SQL語句或語句塊*/ELSEB /*T-SQL語句或語句塊*/

39、當(dāng)條件表達(dá)式的值為真時執(zhí)行A，然后執(zhí)行IF語句的下一語句；條件表達(dá)式的值為假時執(zhí)行B，然后執(zhí)行IF語句的下一語句。（2）不帶ELSE部分：IF 條件表達(dá)式A /*T-SQL語句或語句塊*/當(dāng)條件表達(dá)式的值為真時執(zhí)行A，然后執(zhí)行IF語句的下一條語句；當(dāng)條件表達(dá)式的值為假時直接執(zhí)行IF語句的下一條語句。IF語句的執(zhí)行流程如圖5.3所示。圖5.3 IF語句的執(zhí)行流程【例例5.17】 如果“計(jì)算機(jī)基礎(chǔ)”課程的平均成績高于75分，則顯示“平均成績高于75分”。IF( SELECT AVG(成績) FROM XSB, CJB, KCBWHERE XSB.學(xué)號= CJB.學(xué)號 AND CJB.課程號=KCB

40、.課程號AND KCB.課程名=計(jì)算機(jī)基礎(chǔ) ) 75SELECT 平均成績低于75ELSESELECT 平均成績高于75【例例5.18】 IFELSE語句的嵌套使用。IF( SELECT AVG(成績) FROM XSB, CJB, KCBWHERE XSB.學(xué)號= CJB.學(xué)號 AND CJB.課程號=KCB.課程號AND KCB.課程名=計(jì)算機(jī)基礎(chǔ) ) 75SELECT 平均成績高于755.4.3 CASE語句CASE語句在4.2.1小節(jié)介紹選擇列的時候已經(jīng)涉及過。這里介紹CASE語句在流程控制中的用法，與之前略有不同。語法格式：CASE input_expression WHEN whe

41、n_expression THEN result_expression .n ELSE else_result_expression END 或者CASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END第一種格式中input_expression是要判斷的值或表達(dá)式，接下來是一系列的WHEN-THEN塊，每一塊的when_expression參數(shù)指定要與input_expression比較的值，如果為真，就執(zhí)行result_expression中的T-SQL語句。如果前面的每一塊

42、都不匹配，就會執(zhí)行ELSE塊指定的語句。CASE語句最后以END關(guān)鍵字結(jié)束。第二種格式中CASE關(guān)鍵字后面沒有參數(shù)，在WHEN-THEN塊中，Boolean_expression指定了一個比較表達(dá)式，表達(dá)式為真時執(zhí)行THEN后面的語句。與第一種格式相比，這種格式能夠?qū)崿F(xiàn)更為復(fù)雜的條件判斷，使用起來更方便?！纠?.19】 使用第一種格式的CASE語句，根據(jù)性別值輸出“男”或“女”。SELECT 學(xué)號, 姓名, 專業(yè), SEX= CASE 性別WHEN 1 THEN 男WHEN 0 THEN 女ELSE 無ENDFROM XSBWHERE 總學(xué)分48若使用第二種格式的CASE語句則可以使用以下T

43、-SQL語句。SELECT 學(xué)號, 姓名, 專業(yè), SEX= CASE WHEN 性別=1 THEN 男WHEN 性別=0 THEN 女ELSE 無ENDFROM XSBWHERE 總學(xué)分485.4.4 無條件轉(zhuǎn)移語句無條件轉(zhuǎn)移語句將執(zhí)行流程轉(zhuǎn)移到標(biāo)號指定的位置。語法格式：GOTO labellabel是指向的語句標(biāo)號，標(biāo)號必須符合標(biāo)識符規(guī)則。標(biāo)號的定義形式為label : 語句5.4.5 循環(huán)語句b 1WHILE循環(huán)語句循環(huán)語句如果需要重復(fù)執(zhí)行程序中的一部分語句，則可使用WHILE循環(huán)語句實(shí)現(xiàn)。語法格式：WHILE Boolean_expression /*條件表達(dá)式*/ sql_state

44、ment | statement_block /*T-SQL語句序列構(gòu)成的循環(huán)體*/WHILE語句的執(zhí)行流程如圖5.4所示。圖5.4 WHILE語句的執(zhí)行流程從WHILE循環(huán)的執(zhí)行流程可看出其使用形式：WHILE 條件表達(dá)式循環(huán)體 /*T-SQL語句或語句塊*/ 【例例5.20】 將學(xué)號為081101的學(xué)生的總學(xué)分使用循環(huán)修改到60，每次只加2，并判斷循環(huán)了多少次。USE PXSCJGODECLARE num INTSET num=0WHILE (SELECT 總學(xué)分 FROM XSB WHERE 學(xué)號=081101)60BEGINUPDATE XSB SET 總學(xué)分=總學(xué)分+2 WHERE

45、學(xué)號= 081101SET num=num+1ENDSELECT num AS 循環(huán)次數(shù)執(zhí)行結(jié)果如下：b 2BREAK語句語句語法格式：BREAKBREAK語句一般用在循環(huán)語句中，用于退出本層循環(huán)。當(dāng)程序中有多層循環(huán)嵌套時，使用BREAK語句只能退出其所在的這一層循環(huán)。b 3CONTINUE語句語句語法格式：CONTINUECONTINUE語句一般用在循環(huán)語句中，用于結(jié)束本次循環(huán)，重新轉(zhuǎn)到下一次循環(huán)條件的判斷。5.4.6 返回語句用于從存儲過程、批處理或語句塊中無條件退出，不執(zhí)行位于RETURN之后的語句。語法格式：RETURN integer_expression 如果不提供integer_

46、expression，則退出程序并返回一個空值；如果用在存儲過程中，則可以返回整型值integer_expression?！纠?.21】 判斷是否存在學(xué)號為081128的學(xué)生，如果存在則返回，不存在則插入081128號學(xué)生的信息。IF EXISTS(SELECT * FROM XSB WHERE 學(xué)號=081128)RETURNELSEINSERT INTO XSB VALUES(081128, 張可, 1, 1990-08-12, 計(jì)算機(jī),52, NULL)5.4.7 等待語句等待語句指定觸發(fā)語句塊、存儲過程或事務(wù)執(zhí)行的時刻或需等待的時間間隔。語法格式：WAITFOR DELAY time

47、_to_pass | TIME time_to_execute 【例例5.22】 如下語句設(shè)定在早上8點(diǎn)執(zhí)行查詢語句。BEGINWAITFOR TIME 8:00SELECT * FROM XSBEND5.4.8 錯誤處理語句在SQL Server 2008中，可以使用TRYCATCH語句進(jìn)行T-SQL語言中的錯誤處理。語法格式：BEGIN TRY sql_statement | statement_block END TRYBEGIN CATCH sql_statement | statement_block END CATCH ; 5.5 系統(tǒng)內(nèi)置函數(shù)5.5.1 系統(tǒng)內(nèi)置函數(shù)介紹系統(tǒng)內(nèi)置函

48、數(shù)介紹b 1行集函數(shù)行集函數(shù)行集函數(shù)是返回值為對象的函數(shù)，該對象可在T-SQL語句中作為表引用。所有行集函數(shù)都是非確定性的，即每次用一組特定參數(shù)調(diào)用它們時，所返回的結(jié)果不總是相同的。SQL Server 2008主要提供了如下行集函數(shù)：（1）CONTAINSTABLE：對于基于字符類型的列，按照一定的搜索條件進(jìn)行精確或模糊匹配，然后返回一個表，該表可能為空。（2）FREETEXTTABLE：為基于字符類型的列返回一個表，其中的值符合指定文本的含義，但不符合確切的表達(dá)方式。（3）OPENDATASOURCE：提供與數(shù)據(jù)源的連接。（4）OPENQUERY：在指定數(shù)據(jù)源上執(zhí)行查詢?？梢栽诓樵兊?FR

49、OM 子句中像引用基本表一樣引用OPENQUERY函數(shù)，雖然查詢可能返回多個記錄，但 OPENQUERY 只返回第一個記錄。（5）OPENROWSET：包含訪問OLE DB數(shù)據(jù)源中遠(yuǎn)程數(shù)據(jù)所需的全部連接信息?？稍诓樵兊腇ROM子句中像引用基本表一樣引用OPENROWSET函數(shù)，雖然查詢可能返回多個記錄，但OPENROWSET只返回第一個記錄。（6）OPENXML函數(shù)：通過XML文檔提供行集視圖。5.5.1 系統(tǒng)內(nèi)置函數(shù)介紹b 2聚合函數(shù)聚合函數(shù)聚合函數(shù)對一組值操作，返回單一的匯總值。聚合函數(shù)在以下情況下，允許作為表達(dá)式使用：（1）SELECT語句的選擇列表（子查詢或外部查詢）。（2）COMPU

50、TE或COMPUTE BY子句。（3）HAVING子句。T-SQL語言提供的常用聚合函數(shù)的應(yīng)用請參考第4章相關(guān)內(nèi)容。b 3標(biāo)量函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù)的特點(diǎn)：輸入?yún)?shù)的類型為基本類型，返回值也為基本類型。SQL Server包含如下幾類標(biāo)量函數(shù)：（1）配置函數(shù)；（2）系統(tǒng)函數(shù)；（3）系統(tǒng)統(tǒng)計(jì)函數(shù)；（4）數(shù)學(xué)函數(shù)；（5）字符串函數(shù)；（6）日期和時間函數(shù)；（7）游標(biāo)函數(shù)；（8）文本和圖像函數(shù)；（9）元數(shù)據(jù)函數(shù)；（10）安全函數(shù)。5.5.2 常用系統(tǒng)標(biāo)量函數(shù)b 1配置函數(shù)配置函數(shù)配置函數(shù)用于返回當(dāng)前配置選項(xiàng)設(shè)置的信息。全局變量是以函數(shù)形式使用的，配置函數(shù)一般都是全局變量名，詳細(xì)介紹請參見附錄B。b 2數(shù)

51、學(xué)函數(shù)數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)可對SQL Server提供的數(shù)字?jǐn)?shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。在默認(rèn)情況下，對float數(shù)據(jù)類型數(shù)據(jù)的內(nèi)置運(yùn)算的精度為6個小數(shù)位。下面給出幾個例子說明數(shù)學(xué)函數(shù)的使用，其他的數(shù)學(xué)函數(shù)請參考附錄B。（1）ABS函數(shù)。語法格式：ABS(numeric_expression) 返回給定數(shù)字表達(dá)式的絕對值。參數(shù)numeric_expression為數(shù)字型表達(dá)式（bit數(shù)據(jù)類型除外），返回值類型與numeric_expression相同。【例例5.23】

52、 顯示ABS 函數(shù)對三個不同數(shù)字的效果。SELECT ABS(-5.0), ABS(0.0), ABS(8.0)運(yùn)行結(jié)果如下：（2）RAND函數(shù)。語法格式：RAND ( seed ) 返回01之間的一個隨機(jī)值。參數(shù)seed是指定種子值的整型表達(dá)式，返回值類型為float。如果未指定seed，則隨機(jī)分配種子值。對于指定的種子值，返回的結(jié)果始終相同。【例例5.24】 以下程序通過RAND函數(shù)返回隨機(jī)值。DECLARE count intSET count = 5SELECT RAND(count) b 3字符串處理函數(shù)字符串處理函數(shù)字符串函數(shù)用于對字符串進(jìn)行處理。在此介紹一些常用的字符串處理函數(shù)，

53、其他的字符串處理函數(shù)請參考附錄B。（1）ASCII函數(shù)。語法格式：ASCII ( character_expression ) 返回字符表達(dá)式最左端字符的ASCII值。參數(shù)character_expression的類型為字符型的表達(dá)式，返回值為整型。【例例5.25】 查找字符串sql最左端字符的ASCII值。SELECT ASCII(sql)執(zhí)行結(jié)果如下：（2）CHAR函數(shù)。語法格式：CHAR ( integer_expression ) 將ASCII碼轉(zhuǎn)換為字符。integer_expression為介于0255之間的整數(shù)，返回值為字符型。（3）LEFT函數(shù)。語法格式：LEFT ( char

54、acter_expression , integer_expression ) 返回從字符串左邊開始指定個數(shù)的字符。參數(shù)character_expression為字符型表達(dá)式，integer_expression為整型表達(dá)式，返回值為varchar型?！纠?.26】 返回課程名最左邊的4個字符。SELECT LEFT(課程名, 4) FROM KCBORDER BY 課程號（4）LTRIM函數(shù)。語法格式：LTRIM ( character_expression )刪除character_expression字符串中的前導(dǎo)空格，并返回字符串?！纠?.27】 使用LTRIM字符刪除字符變量中的

55、起始空格。DECLARE string varchar(40)SET string = 中國，一個古老而偉大的國家 SELECT LTRIM(string)SELECT string（5）REPLACE函數(shù)。語法格式：REPLACE ( string_expression1 , string_expression2 , string_expression3 )用第三個字符串表達(dá)式替換第一個字符串表達(dá)式中包含的第二個字符串表達(dá)式，并返回替換后的表達(dá)式。參數(shù)string_expression1、string_expression2和string_expression3均為字符串表達(dá)式。返回值為字符

56、型。（6）SUBSTRING函數(shù)。語法格式：SUBSTRING ( expression , start , length ) 返回expression中指定的部分?jǐn)?shù)據(jù)。參數(shù)expression可為字符串、二進(jìn)制串、text、image字段或表達(dá)式；start、length均為整型，前者指定子串的開始位置，后者指定子串的長度（要返回字節(jié)數(shù)）。如果expression是字符類型和二進(jìn)制類型，則返回值類型與expression的類型相同。其他情況參考表5.12。給定的表達(dá)式返回值類型給定的表達(dá)式返回值類型給定的表達(dá)式返回值類型textvarcharimagevarbinaryntextnvarch

57、ar表5.12 SUBSTRING函數(shù)返回值不同于給定表達(dá)式的情況【例例5.28】 如下程序在一列中返回XSB表中的姓氏，在另一列中返回表中學(xué)生姓名。SELECT SUBSTRING(姓名, 1,1), SUBSTRING(姓名, 2, LEN(姓名)-1)FROM XSBORDER BY 姓名【例例5.29】 顯示字符串China中每個字符的ASCII值和字符。DECLARE position int, string char(8)SET position = 1SET string=ChinaWHILE position =50/*使用 CONVERT實(shí)現(xiàn)*/SELECT 姓名, 總學(xué)分F

58、ROM XSBWHERE CONVERT(char(20), 總學(xué)分) LIKE 5_ AND總學(xué)分=50（2）COALESCE函數(shù)。語法格式：COALESCE ( expression ,.n ) 返回參數(shù)表中第一個非空表達(dá)式的值，如果所有自變量均為NULL，則COALESCE返回NULL值。參數(shù)expression可為任何類型的表達(dá)式。n表示可以指定多個表達(dá)式。所有表達(dá)式必須是相同類型的，或者可以隱性轉(zhuǎn)換為相同的類型。COALESCE(expression1,.n) 與如下形式的CASE 語句等價：CASE WHEN (expression1 IS NOT NULL) THEN expre

59、ssion1 . WHEN (expressioN IS NOT NULL) THEN expressionN ELSE NULL（3）ISNUMBRIC函數(shù)。ISNUMBRIC函數(shù)用于判斷一個表達(dá)式是否為數(shù)值類型。語法格式：ISNUMBRIC(expression)b 5日期時間函數(shù)日期時間函數(shù)日期時間函數(shù)可用在SELECT語句的選擇列表或查詢的WHERE子句中。在附錄B中列出了大多數(shù)的日期時間函數(shù)。（1）GETDATE函數(shù)。語法格式：GETDATE ()按SQL Server標(biāo)準(zhǔn)內(nèi)部格式返回當(dāng)前系統(tǒng)日期和時間。返回值類型為datetime。（2）YEAR、MONTH、DAY函數(shù)。這三個函數(shù)

60、分別返回指定日期的年、月、天部分，返回值都為整數(shù)。語法格式：YEAR(date)MONTH(date)DAY(date)b 6游標(biāo)函數(shù)游標(biāo)函數(shù)游標(biāo)函數(shù)用于返回有關(guān)游標(biāo)的信息。主要的游標(biāo)函數(shù)如下。（1）CURSOR_ROWS函數(shù)。語法格式：CURSOR_ROWS返回最后打開的游標(biāo)中當(dāng)前存在的滿足條件的行數(shù)。返回值為0表示游標(biāo)未打開；為-1表示游標(biāo)為動態(tài)游標(biāo)；為-m表示游標(biāo)被異步填充，返回值（-m）是鍵集中當(dāng)前的行數(shù)；為n表示游標(biāo)已完全填充，返回值（n）是游標(biāo)中的總行數(shù)。【例例5.31】 聲明一個游標(biāo)，并用SELECT顯示CURSOR_ROWS 的值。USE PXSCJGOSELECT CURSOR_ROWSDECLA