數(shù)據(jù)庫原理與應(yīng)用07.Transact-SQL程序設(shè)計

數(shù)據(jù)庫原理與應(yīng)用07.Transact-SQL程序設(shè)計1注釋2變量3運算符4函數(shù)5流程控制語句1注釋 注釋是程序代碼中不執(zhí)行的文本字符串〔也稱為注解〕。在SQLServer中，可以使用兩種類型的注釋字符：一種是ANSI標(biāo)準(zhǔn)的注釋符“--〞，它用于單行注釋；另一種是與C語言相同的程序注釋符號，即“/**/〞。2變量 變量是一種語言中必不可少的組成局部。Transact-SQL語言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。1.局部變量利用局部變量可以保存程序執(zhí)行過程中的中間結(jié)果，保存由存儲過程返回的數(shù)據(jù)值等?！?〕局部變量的定義必須先用DECLARE命令定義后才可以使用。DECLAER{@local_variabledata_type}[…n]〔2〕局部變量的賦值方法SET{{@local_variable=expression}或者SELECT{@local_variable=expression}[,...n]2.全局變量全局變量是SQLServer系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時調(diào)用。全局變量通常存儲一些SQLServer的配置設(shè)定值和統(tǒng)計數(shù)據(jù)。用戶可以在程序中用全局變量來測試系統(tǒng)的設(shè)定值或者是Transact-SQL命令執(zhí)行后的狀態(tài)值。使用全局變量時應(yīng)該注意以下幾點：①全局變量不是由用戶的程序定義的，它們是在效勞器級定義的。②用戶只能使用預(yù)先定義的全局變量。③引用全局變量時，必須以標(biāo)記符“@@〞開頭。④局部變量的名稱不能與全局變量的名稱相同，否那么會在應(yīng)用程序中出現(xiàn)不可預(yù)測的結(jié)果。常用的全局變量@@Error@@RowCount@@Identity3運算符運算符是一些符號，它們能夠用來執(zhí)行算術(shù)運算、字符串連接、賦值以及在字段、常量和變量之間進行比較。在SQLServer2000中，運算符主要有以下六大類：算術(shù)運算符、賦值運算符、位運算符、比較運算符、邏輯運算符以及字符串串聯(lián)運算符。1．算術(shù)運算符算術(shù)運算符可以在兩個表達式上執(zhí)行數(shù)學(xué)運算，這兩個表達式可以是數(shù)字?jǐn)?shù)據(jù)類型分類的任何數(shù)據(jù)類型。算術(shù)運算符包括加〔+〕、減〔—〕、乘〔*〕、除〔/〕和取?！?〕。2．賦值運算符Transact-SQL中只有一個賦值運算符，即等號〔=〕。賦值運算符使我們能夠?qū)?shù)據(jù)值指派給特定的對象。另外，還可以使用賦值運算符在列標(biāo)題和為列定義值的表達式之間建立關(guān)系。3．位運算符位運算符使我們能夠在整型數(shù)據(jù)或者二進制數(shù)據(jù)〔image數(shù)據(jù)類型除外〕之間執(zhí)行位操作。此外，在位運算符左右兩側(cè)的操作數(shù)不能同時是二進制數(shù)據(jù)。

表2-1位運算符運

算

符

含

義&（按位AND）按位AND（兩個操作數(shù)）。|（按位OR）按位OR（兩個操作數(shù)）。^（按位互斥OR）按位互斥OR（兩個操作數(shù)）。

4．比較運算符比較運算符用于比較兩個表達式的大小或是否相同，其比較的結(jié)果是布爾值，即TRUE〔表示表達式的結(jié)果為真〕、FALSE〔表示表達式的結(jié)果為假〕以及UNKNOWN。除了text、ntext或image數(shù)據(jù)類型的表達式外，比較運算符可以用于所有的表達式。5．邏輯運算符邏輯運算符可以把多個邏輯表達式連接起來。邏輯運算符包括AND、OR和NOT等運算符。邏輯運算符和比較運算符一樣，返回帶有TRUE或FALSE值的布爾數(shù)據(jù)類型。6．字符串串聯(lián)運算符字符串串聯(lián)運算符允許通過加號(+)進行字符串串聯(lián)，這個加號即被稱為字符串串聯(lián)運算符。例如對于語句SELECT‘a(chǎn)bc’+’def’，其結(jié)果為abcdef。運算符的優(yōu)先等級從高到低如下所示括號：〔〕；乘、除、求模運算符：*、/、%；加減運算符：+、-；比較運算符：=、>、<、>=、<=、<>、!=、!>、!<；位運算符：^、&、|；邏輯運算符：NOT；邏輯運算符：AND；邏輯運算符：OR。4函數(shù)在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持SQLServer的標(biāo)準(zhǔn)命令。Transact-SQL編程語言提供了三種函數(shù)：

㈠行集函數(shù)：行集函數(shù)可以在Transact-SQL語句中當(dāng)作表引用。

㈡聚合函數(shù)：聚合函數(shù)用于對一組值執(zhí)行計算并返回一個單一的值。㈢標(biāo)量函數(shù)：標(biāo)量函數(shù)用于對傳遞給它的一個或者多個參數(shù)值進行處理和計算，并返回一個單一的值。SQLServer中最常用的幾種函數(shù)字符串函數(shù)日期和時間函數(shù)數(shù)學(xué)函數(shù)轉(zhuǎn)換函數(shù)系統(tǒng)函數(shù)聚合函數(shù)其它函數(shù)字符串函數(shù)字符串函數(shù)可以對二進制數(shù)據(jù)、字符串和表達式執(zhí)行不同的運算，大多數(shù)字符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個字符串函數(shù)也可以用于binary和varbinary數(shù)據(jù)類型。此外，某些字符串函數(shù)還能夠處理text、ntext、image數(shù)據(jù)類型的數(shù)據(jù)。字符串函數(shù)的分類：根本字符串函數(shù)：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函數(shù)：CHARINDEX、PATINDEX。長度和分析函數(shù)：DATALENGTH、SUBSTRING、RIGHT。轉(zhuǎn)換函數(shù)：ASCH、CHAR、STR、SOUNDEX、DIFFERENCE。2．日期和時間函數(shù)日期和時間函數(shù)用于對日期和時間數(shù)據(jù)進行各種不同的處理和運算，并返回一個字符串、數(shù)字值或日期和時間值。在SQLServer2000中，日期和時間函數(shù)的類型如表2-5所示；另外，表2-6列出了日期類型的名稱、縮寫形式以及可接受的值。表2-5日期和時間函數(shù)的類型函

數(shù)參

數(shù)DATEADD(datepart,number,date)DATEDIFF(datepart,date1,date2)DATENAME(datepart,date)DATEPART(datepart,date)DAY(date)GETDATE（）MONTH(date)YEAR(date)從GETDATE函數(shù)返回的日期中

提取月份數(shù)。SELECTDATEPART(month,GETDATE())AS'MonthNumber'運行結(jié)果為：MonthNumber------------4從日期04/12/2006中返回月份數(shù)、天數(shù)和年份數(shù)。SELECTMONTH('04/12/2006'),DAY('04/12/2006'),YEAR('04/12/2006')運行結(jié)果為：-----------------4

12

20063．?dāng)?shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于對數(shù)字表達式進行數(shù)學(xué)運算并返回運算結(jié)果。數(shù)學(xué)函數(shù)可以對SQLServer提供的數(shù)字?jǐn)?shù)據(jù)〔decimal、integer、float、real、money、smallmoney、smallint和tinyint〕進行處理。在同一表達式中使用CEILING〔〕、FLOOR〔〕、ROUND〔〕函數(shù)。selectceiling(13.4),floor(13.4),round(13.4567,3)運行結(jié)果為：-------------------------141313.45704．轉(zhuǎn)換函數(shù)一般情況下，SQLServer會自動處理某些數(shù)據(jù)類型的轉(zhuǎn)換。例如，如果比較char和datetime表達式、smallint和int表達式、或不同長度的char表達式，SQLServer可以將它們自動轉(zhuǎn)換，這種轉(zhuǎn)換被稱為隱性轉(zhuǎn)換。但是，無法由SQLServer自動轉(zhuǎn)換的或者是SQLServer自動轉(zhuǎn)換的結(jié)果不符合預(yù)期結(jié)果的，就需要使用轉(zhuǎn)換函數(shù)做顯示轉(zhuǎn)換。轉(zhuǎn)換函數(shù)有兩個：CONVERT和CAST。CONVERT和CAST函數(shù)CAST(expressionASdata_type)CONVERT函數(shù)允許用戶把表達式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式。其語法形式為：CONVERT(data_type[(length)],expression[,style])USEpubsSELECTtitle,ytd_salesFROMtitlesWHERECAST(ytd_salesASchar(20))LIKE'15%'ANDtype='trad_cook'運行結(jié)果為：Title

ytd_sales------------------------------FiftyYearsinBuckinghamPalaceKitchens

150965．系統(tǒng)函數(shù)系統(tǒng)函數(shù)用于返回有關(guān)SQLServer系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語句，根據(jù)返回的信息進行不同的操作。與其它函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達式中使用系統(tǒng)函數(shù)。返回Northwind數(shù)據(jù)庫的Employees表中的首列的名稱。USENorthwindSELECTCOL_NAME(OBJECT_ID('Employees'),1)運行結(jié)果為：EmployeeID6．聚合函數(shù)聚合函數(shù)可以返回整個或者幾個列或者一個列的匯總數(shù)據(jù)，它常用來計算SELECT語句查詢的統(tǒng)計值。聚合函數(shù)經(jīng)常與SELECT語句的GROUPBY子句一同使用。用戶自定義函數(shù)CREATEFUNCTION創(chuàng)立用戶定義函數(shù)，它是返回值的已保存的Transact-SQL例程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局?jǐn)?shù)據(jù)庫狀態(tài)的操作。與系統(tǒng)函數(shù)一樣，用戶定義函數(shù)可以從查詢中喚醒調(diào)用。也可以像存儲過程一樣，通過EXECUTE語句執(zhí)行。用戶定義函數(shù)用ALTERFUNCTION修改，用DROPFUNCTION除去。CREATEFUNCTION[owner_name.]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[WITH<function_option>[[,]...n]][AS]BEGIN

function_body

RETURNscalar_expression

END5流程控制語句流程控制語句是指那些用來控制程序執(zhí)行和流程分支的命令，在SQLServer2000中，流程控制語句主要用來控制SQL語句、語句塊或者存儲過程的執(zhí)行流程。使用BEGIN…END可以將多條T-SQL語句封裝起來，構(gòu)成一個獨立的語句塊。BEGIN關(guān)鍵字表示語句塊的開始，END關(guān)鍵字表示語句塊的結(jié)束，它們必須成對出現(xiàn)。BEGIN…END的語法格式如下：BEGIN{sql_statement|statement_block}ENDBegin…EndIF…ELSE

IF…ELSE語句可以控制程序按條件執(zhí)行。當(dāng)IF關(guān)鍵字后的條件滿足〔布爾表達式返回TRUE時〕，那么在執(zhí)行IF關(guān)鍵字及其條件之后的T-SQL語句。否那么，就執(zhí)行ELSE關(guān)鍵字后的T-SQL語句〔假設(shè)ELSE局部存在〕。IF…ELSE語句的語法格式如下：IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]查詢課程號為“105237〞的課程的平均分是否超過了85分，假設(shè)超過，那么輸出考出了高分的信息。USEEducationalGODECLARE@Course_IDvarchar(10)SELECT@Course_ID='105237'/*查詢的課程號*/IF(SELECTAVG(Grade)FROMGradeWHERECourseID=@Course_ID)>85 /*平均分大于85分*/BEGINSELECT@Course_ID=CourseName/*從課程信息表中取課程名*/FROMCourseWHERECourseID=@Course_IDPRINT@Course_ID+'課程'PRINT'考出了高分'ENDELSEPRINT'考的一般'GOTO使用GOTO語句可以使程序的流程無條件地轉(zhuǎn)移到指定的標(biāo)簽處繼續(xù)執(zhí)行。GOTO語句的語法格式如下：GOTOlabel其中l(wèi)abel就是需指向的標(biāo)簽。標(biāo)簽必須符合標(biāo)識符規(guī)那么，且必須存在。求1+2+3+…+100的累加和。DECLARE@iINT,@sumINTSET@i=1SET@sum=0LabelLoop:SET@sum=@sum+@iSET@i=@i+1IF(@i<=100)G