1、1,第3章 Transact-SQL語(yǔ)言編程,軟件學(xué)院 陳 鍵,本章教學(xué)內(nèi)容概要,編程基礎(chǔ)知識(shí) 常用函數(shù) 常量與變量 運(yùn)算符 常用系統(tǒng)存儲(chǔ)過(guò)程 批處理和流控制語(yǔ)句,3.1 編程基礎(chǔ)知識(shí),書寫語(yǔ)法規(guī)則,標(biāo)識(shí)符 定義服務(wù)器、數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)對(duì)象和變量等名稱。分為常規(guī)標(biāo)識(shí)符和分隔標(biāo)識(shí)符。 常規(guī)標(biāo)識(shí)符命名規(guī)則： 第一個(gè)字符必須是下列字符之一： Unicode 中定義的字母a-z 和 A-Z，以及來(lái)自其他語(yǔ)言的字母字符、_、 或者數(shù)字符號(hào) #。 后續(xù)字符可以包括： Unicode 標(biāo)準(zhǔn) 2.0 中所定義的字母、其他國(guó)家/地區(qū)字符中的十進(jìn)制數(shù)字、 $、#、_。 標(biāo)識(shí)符不能是 Transact-SQL 保留字

2、。SQL Server 保留其保留字的大寫和小寫形式。 不允許嵌入空格或其他特殊字符。,3.1 編程基礎(chǔ)知識(shí),分隔標(biāo)識(shí)符 對(duì)于不符合常規(guī)標(biāo)識(shí)符定義的標(biāo)識(shí)符，用雙引號(hào)或方括號(hào)括起來(lái)。 數(shù)據(jù)類型 函數(shù) 表達(dá)式 運(yùn)算符 注釋 單行注釋符： 多行注釋符：/* 注釋內(nèi)容 */ 保留關(guān)鍵字,3.1 編程基礎(chǔ)知識(shí),3.1 編程基礎(chǔ)知識(shí),引用數(shù)據(jù)庫(kù)對(duì)象名的規(guī)則 Servername . dbname . ownername . objectname Dbname . ownername . objectname Ownername . objectname Objectname 如：-查看sysservers

3、表中的服務(wù)器名 use MASTER go SELECT * FROM SYSSERVERS -查看學(xué)生表中記錄 SELECT * FROM PC-201004241537.JXGL.dbo.XS,概述 函數(shù)是一個(gè)或多個(gè)T-SQL語(yǔ)句組成的子程序，SQL Server 2005提供了豐富的系統(tǒng)函數(shù)，用戶也可以自定義函數(shù)。 SQL Server提供的函數(shù) 聚合函數(shù)、配置函數(shù)、游標(biāo)函數(shù)、日期和時(shí)間函數(shù)、數(shù)學(xué)函數(shù)、元數(shù)函數(shù)、行集函數(shù)、安全函數(shù)、字符串函數(shù)、系統(tǒng)函數(shù)、文本和圖像函數(shù)。,3.2 常用函數(shù),聚合函數(shù)概述 又稱統(tǒng)計(jì)函數(shù)，對(duì)一組值執(zhí)行計(jì)算并返回單一的值。 常用聚合函數(shù)有： AVG 返回組中值的

4、平均值。忽略空值。 MAX 返回表達(dá)式的最大值。 MIN 返回表達(dá)式的最小值。 SUM 返回表達(dá)式中所有值的和，或只返回 DISTINCT 值。SUM 只能用于數(shù)字列。忽略空值。 COUNT 返回總行數(shù)。 示例,3.2 常用函數(shù),字符串函數(shù) 對(duì)字符和二進(jìn)制字符串進(jìn)行各種操作。 常用的字符串函數(shù) LEN、LEFT 、RIGHT、SUBSTRING 、LTRIM、RTRIM、 ASCII 、CHAR 、SPACE 、LOW、UPPER REPLACE 用第三個(gè)表達(dá)式替換第一個(gè)字符串表達(dá)式中出現(xiàn)的所有第二個(gè)給定字符串表達(dá)式。 REPLICATE 將字符表達(dá)式重復(fù)多次。 CHARINDEX 從第三參數(shù)

5、指定位置開始，在表達(dá)式1中查找表達(dá)式2的位置。 STUFF 從第一參數(shù)指定開始位置和長(zhǎng)度的字符串用第4參數(shù)替換。 示例,3.2 常用函數(shù),日期和時(shí)間函數(shù) 實(shí)現(xiàn)對(duì)日期時(shí)間數(shù)據(jù)進(jìn)行操作 常用的日期時(shí)間函數(shù) GETDATE 、DAY 、MONTH 、YEAR DATEADD 日期加上一段時(shí)間返回新日期值。 DATEDIFF 兩個(gè)指定日期差值。 DATENAME 指定日期的日期部分的字符串。 DATEPART 返回日期的指定日期部分的整數(shù)。 GETUTCDATE 返回表示當(dāng)前 UTC 時(shí)間（世界時(shí)間坐標(biāo)或格林尼治標(biāo)準(zhǔn)時(shí)間）的 datetime 值。 示例,3.2 常用函數(shù),系統(tǒng)函數(shù)概述 對(duì) SQL S

6、erver中的值、對(duì)象和設(shè)置進(jìn)行操作并返回有關(guān)信息 常用系統(tǒng)函數(shù) DATALENGTH 返回表達(dá)式所占用的字節(jié)數(shù)。 SYSTEM_USER 當(dāng)前系統(tǒng)用戶名。 HOST_NAME 主機(jī)名稱。 ISDATE 日期判斷。 ISNULL 空值判斷。 ISNUMERIC 數(shù)字類型判斷。 NULLIF 當(dāng)兩個(gè)表達(dá)式相等時(shí)返回空值。 示例,3.2 常用函數(shù),轉(zhuǎn)換函數(shù)概述 將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。 可以使用CAST和CONVERT。 語(yǔ)法 CAST ( 表達(dá)式 AS 數(shù)據(jù)類型 ) CONVERT (數(shù)據(jù)類型(長(zhǎng)度), 表達(dá)式 , 樣式) 示例,3.2 常用函數(shù),數(shù)學(xué)函數(shù) 對(duì)數(shù)字表達(dá)式進(jìn)

7、行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。 常用的數(shù)學(xué)函數(shù) 三角函數(shù)：SIN、COS、TAN、ASIN、ACOS、ATAN 算術(shù)函數(shù)：ABS、EXP、SQRT、POWER、LOG、LOG10 其他：RAND、ROUND、SIGN、PI 示例,3.2 常用函數(shù),元數(shù)據(jù)函數(shù) 返回?cái)?shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息。 常用的元數(shù)據(jù)函數(shù) COL_LENGTH(Table, column):列寬。 COL_NAME(table_id,column_id):列名。其中table_id由OBJECT_ID(TABLE_NAME)獲取。 示例,3.2 常用函數(shù),安全函數(shù) USER:當(dāng)前用戶的數(shù)據(jù)庫(kù)用戶名。 HAS_DBACCESS(D

8、B_NAME)：訪問(wèn)數(shù)據(jù)庫(kù)權(quán)限判斷。 示例 排名函數(shù) ROW_NUMBER() OVER(ORDER BY 列名) 返回查詢結(jié)果每一行的序號(hào)。 RANK() OVER(ORDER BY 列名) 返回查詢結(jié)果中每一行的排名，排名可能間斷。 DENSE_RANK() OVER(ORDER BY 列名) 返回查詢結(jié)果每一行的排名，排名不間斷。 示例,3.2 常用函數(shù),常量 字符串常量 用單引號(hào)括住的多個(gè)字符。如果字符串中包含單引號(hào)，則用兩個(gè)單引號(hào)表示，如：I am a student。Unicode字符串前加字符N。 數(shù)值常量 數(shù)據(jù)類型有：binary、bit、 smalldatetime 、 da

9、tetime、bigint、int、smallint、tinyint、decimal 、numeric、float、real、money、smallmoney。 日期常量 單引號(hào)括住的特定格式的字符日期值。,3.3 常量與變量,變量概述 SQL Server 2005支持兩種類型的變量：局部變量和全局變量。 局部變量一般在一個(gè)批處理中被聲明和使用，批處理結(jié)束后，變量消失。局部變量名以開頭。 全局變量記錄了SQL Server服務(wù)器的一組活動(dòng)狀態(tài)數(shù)據(jù)，已預(yù)先定義好，用戶只能使用，不能重新定義和賦值。 局部變量的聲明 語(yǔ)法：DECLARE 局部變量名 數(shù)據(jù)類型 , , n 局部變量的賦值 語(yǔ)法：S

10、ET 局部變量名 = 表達(dá)式 或 SELECT 局部變量名 = 表達(dá)式 , n 局部變量的顯示 語(yǔ)法： SELECT 局部變量名,3.3 常量與變量,如：查詢學(xué)校學(xué)生人數(shù)。顯示內(nèi)容為：學(xué)校共有N個(gè)學(xué)生。 declare i char(50) select i=學(xué)生：+convert(char(3),(select count(*) from xs)+個(gè)學(xué)生 Select i go,3.3 常量與變量,全局變量概述 全局變量記錄了SQL Server服務(wù)器的一組活動(dòng)狀態(tài)數(shù)據(jù)，已預(yù)先定義好，用戶只能使用，不能重新定義和賦值。 全局變量名均以開頭。 常用的全局變量 SERVERNAME 運(yùn)行SQL

11、Server的本地服務(wù)器名稱。 VERSION SQL Server當(dāng)前安裝的版本信息。 ERROR 最后執(zhí)行的 Transact-SQL 語(yǔ)句錯(cuò)誤代碼。 MAX_CONNECTIONS 允許同時(shí)連接最大用戶數(shù)。 ROWCOUNT 返回受上一語(yǔ)句影響的行數(shù)。 PROCID 返回當(dāng)前過(guò)程的存儲(chǔ)過(guò)程標(biāo)識(shí)符 (ID) 。 Language:當(dāng)前使用語(yǔ)言名稱。 LOCK_TIMEOUT會(huì)話鎖定超時(shí)設(shè)置 全局變量的顯示 語(yǔ)法： SELECT 全局變量名,3.3 常量與變量,3.4 運(yùn)算符,運(yùn)算符主要分為： 一元運(yùn)算符、賦值運(yùn)算符、算術(shù)運(yùn)算符、位運(yùn)算符、字符串連接運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符。 一元運(yùn)算

12、符 +（正） 、- （ 負(fù)）（按位取反） 賦值運(yùn)算符 =（賦值） 算術(shù)運(yùn)算符 +（加）、-（減）、*（乘）、/（除）、 %（求余）。 位運(yùn)算符 &(位與)、|(位或)、(異或)、(位非)。,運(yùn)算符,字符串連接運(yùn)算符 + ：將兩個(gè)字符串連接成一個(gè)表達(dá)式。 比較運(yùn)算符 比較兩個(gè)表達(dá)式的關(guān)系，結(jié)果：TRUE、FALSE 、 UNKNOWN。 當(dāng) SET ANSI_NULLS 為 ON 時(shí)，帶有一個(gè)或兩個(gè) NULL 表達(dá)式的運(yùn)算符返回 UNKNOWN。當(dāng) SET ANSI_NULLS 為 OFF 時(shí)，上述規(guī)則同樣適用，只不過(guò)如果兩個(gè)表達(dá)式都為 NULL，那么等號(hào)運(yùn)算符返回 TRUE。 運(yùn)算符：= 、=

13、、!=、! 。,運(yùn)算符,邏輯運(yùn)算符 AND(與)： 、OR (或)、NOT (非) 、ALL 、ANY 、SOME、BETWEEN 、EXISTS 、IN 、LIKE 。 運(yùn)算符有下面這些優(yōu)先等級(jí) +（正）、-（負(fù)）、（按位非） *（乘）、/（除）、%（模） +（加）、+ （字符串連接）、-（減） =, , =, , !=, !, ! 比較運(yùn)算符 （位異或）、&（位與）、|（位或） NOT AND OR、 ALL、ANY、BETWEEN、IN、LIKE、SOME =（賦值）,3.5 常用系統(tǒng)存儲(chǔ)過(guò)程,系統(tǒng)存儲(chǔ)過(guò)程以“SP_”開始，存儲(chǔ)在Master中。常用系統(tǒng)存儲(chǔ)過(guò)程如下； SP_config

14、ure：顯示或修改服務(wù)器配置； Sp_help：返回指定對(duì)象信息； Sp_who：返回用戶和進(jìn)程信息； Sp_dboption：顯示或修改數(shù)據(jù)庫(kù)配置選項(xiàng)； Sp_helpdb：顯示數(shù)據(jù)庫(kù)信息； Sp_helprotect：顯示用戶權(quán)限或語(yǔ)句權(quán)限信息； Sp_lock：顯示鎖信息。 如：顯示服務(wù)器配置，顯示數(shù)據(jù)庫(kù)信息。,3.6 批處理,批處理是包含一個(gè)或多個(gè) T-SQL 語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到 SQL Server執(zhí)行。SQL Server 將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元。 以下規(guī)則適用于批處理： CREATE DEFAULT、CREATE PROCEDURE、CREATE RUL

15、E、CREATE TRIGGER 和 CREATE VIEW 語(yǔ)句不能在批處理中與其它語(yǔ)句組合使用。不能在同一個(gè)批處理中更改表，然后引用新列。 調(diào)用存儲(chǔ)過(guò)程是，如果不是第一個(gè)語(yǔ)句，必須在前面加 EXECUTE 關(guān)鍵字。 批處理一般以GO為結(jié)束標(biāo)記,3.6 控制流語(yǔ)句,BEGIN.END 用于定義語(yǔ)句塊。它包括一系列的 Transact-SQL 語(yǔ)句，使得可以執(zhí)行一組 Transact-SQL 語(yǔ)句。BEGIN.END 語(yǔ)句塊允許嵌套。 PRINT 將用戶定義的消息返回客戶端。 語(yǔ)法 PRINT 消息文本 | 局部變量 | 函數(shù) | 表達(dá)式 示例 PRINT 時(shí)間： + CONVERT(varc

16、har(30), GETDATE(),IF語(yǔ)句 IF語(yǔ)句的語(yǔ)法： IF 條件表達(dá)式 語(yǔ)句 1| 語(yǔ)句塊1 ELSE語(yǔ)句2 | 語(yǔ)句塊2 如：根據(jù)分?jǐn)?shù)值給出相應(yīng)的等級(jí)評(píng)價(jià),3.6 控制流語(yǔ)句,CASE語(yǔ)句概述 計(jì)算條件列表并返回多個(gè)可能結(jié)果表達(dá)式之一。 語(yǔ)法 簡(jiǎn)單 CASE 函數(shù)： CASE 判斷表達(dá)式 WHEN 表達(dá)式 THEN 結(jié)果表達(dá)式1 .n ELSE 結(jié)果表達(dá)n+1 END CASE 搜索函數(shù)： CASEWHEN 條件表達(dá)式 THEN 結(jié)果表達(dá)式1 .n ELSE 結(jié)果表達(dá)n+1 END,3.6 控制流語(yǔ)句,如：使用CASE語(yǔ)句替換查詢結(jié)果中的數(shù)據(jù)。,select XM, 性別= ca

17、se XB when 0 then 男 when 1 then 女 end from js,select XM,性別= case when XB=0 then 男 when XB=1 then 女 end from js,3.6 控制流語(yǔ)句,WHILE語(yǔ)句 設(shè)置重復(fù)執(zhí)行 SQL 語(yǔ)句或語(yǔ)句塊的條件。 語(yǔ)法 WHILE 條件表達(dá)式 語(yǔ)句塊 語(yǔ)句塊中可以使用 BREAK 和 CONTINUE 關(guān)鍵字在循環(huán)內(nèi)部控制 WHILE 循環(huán)中語(yǔ)句的執(zhí)行。 BREAK導(dǎo)致從最內(nèi)層的 WHILE 循環(huán)中退出。將執(zhí)行出現(xiàn)在 END 關(guān)鍵字后面的任何語(yǔ)句，END 關(guān)鍵字為循環(huán)結(jié)束標(biāo)記。 CONTINUE使 WHILE 循環(huán)重新開始執(zhí)行，忽略 CONTINUE 關(guān)鍵字后的任何語(yǔ)句。 如：計(jì)算1到100之間的和,3.6 控制流語(yǔ)句,GOTO 標(biāo)號(hào) 將執(zhí)行流變更到標(biāo)簽處。跳過(guò) GOTO 之后的語(yǔ)句，在標(biāo)簽處繼續(xù)處理。GOTO 語(yǔ)句和標(biāo)簽可在過(guò)程、批處理或語(yǔ)句塊中的任何位置使用。GOTO 語(yǔ)句可嵌套使用。 GOTO語(yǔ)句語(yǔ)法 標(biāo)簽名 : 語(yǔ)句塊 GOTO 標(biāo)簽名 如：計(jì)算1到100之間的和,3.6 控制流語(yǔ)句,RETURN語(yǔ)句 從查詢或過(guò)程中無(wú)條件退出?？稍谌魏螘r(shí)候用于從過(guò)程、批處理或語(yǔ)句塊中退出。不執(zhí)行位于 RETURN 之后的語(yǔ)句。RETURN語(yǔ)句可以帶有返回值。 語(yǔ)法 RETURN 返回的整