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

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

表2-1位運(yùn)算符運(yùn)

算

符

含

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

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

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

㈡聚合函數(shù)：聚合函數(shù)用于對(duì)一組值執(zhí)行計(jì)算并返回一個(gè)單一的值。㈢標(biāo)量函數(shù)：標(biāo)量函數(shù)用于對(duì)傳遞給它的一個(gè)或者多個(gè)參數(shù)值進(jìn)行處理和計(jì)算，并返回一個(gè)單一的值。SQLServer中最常用的幾種函數(shù)字符串函數(shù)日期和時(shí)間函數(shù)數(shù)學(xué)函數(shù)轉(zhuǎn)換函數(shù)系統(tǒng)函數(shù)聚合函數(shù)其它函數(shù)字符串函數(shù)字符串函數(shù)可以對(duì)二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式執(zhí)行不同的運(yùn)算，大多數(shù)字符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個(gè)字符串函數(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í)間函數(shù)用于對(duì)日期和時(shí)間數(shù)據(jù)進(jìn)行各種不同的處理和運(yùn)算，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。在SQLServer2000中，日期和時(shí)間函數(shù)的類型如表2-5所示；另外，表2-6列出了日期類型的名稱、縮寫形式以及可接受的值。表2-5日期和時(shí)間函數(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'運(yùn)行結(jié)果為：MonthNumber------------4從日期04/12/2006中返回月份數(shù)、天數(shù)和年份數(shù)。SELECTMONTH('04/12/2006'),DAY('04/12/2006'),YEAR('04/12/2006')運(yùn)行結(jié)果為：-----------------4

12

20063．?dāng)?shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于對(duì)數(shù)字表達(dá)式進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。數(shù)學(xué)函數(shù)可以對(duì)SQLServer提供的數(shù)字?jǐn)?shù)據(jù)〔decimal、integer、float、real、money、smallmoney、smallint和tinyint〕進(jìn)行處理。在同一表達(dá)式中使用CEILING〔〕、FLOOR〔〕、ROUND〔〕函數(shù)。selectceiling(13.4),floor(13.4),round(13.4567,3)運(yùn)行結(jié)果為：-------------------------141313.45704．轉(zhuǎn)換函數(shù)一般情況下，SQLServer會(huì)自動(dòng)處理某些數(shù)據(jù)類型的轉(zhuǎn)換。例如，如果比較char和datetime表達(dá)式、smallint和int表達(dá)式、或不同長度的char表達(dá)式，SQLServer可以將它們自動(dòng)轉(zhuǎn)換，這種轉(zhuǎn)換被稱為隱性轉(zhuǎn)換。但是，無法由SQLServer自動(dòng)轉(zhuǎn)換的或者是SQLServer自動(dòng)轉(zhuǎn)換的結(jié)果不符合預(yù)期結(jié)果的，就需要使用轉(zhuǎn)換函數(shù)做顯示轉(zhuǎn)換。轉(zhuǎn)換函數(shù)有兩個(gè)：CONVERT和CAST。CONVERT和CAST函數(shù)CAST(expressionASdata_type)CONVERT函數(shù)允許用戶把表達(dá)式從一種數(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'運(yùn)行結(jié)果為：Title

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

150965．系統(tǒng)函數(shù)系統(tǒng)函數(shù)用于返回有關(guān)SQLServer系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語句，根據(jù)返回的信息進(jìn)行不同的操作。與其它函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達(dá)式中使用系統(tǒng)函數(shù)。返回Northwind數(shù)據(jù)庫的Employees表中的首列的名稱。USENorthwindSELECTCOL_NAME(OBJECT_ID('Employees'),1)運(yùn)行結(jié)果為：EmployeeID6．聚合函數(shù)聚合函數(shù)可以返回整個(gè)或者幾個(gè)列或者一個(gè)列的匯總數(shù)據(jù)，它常用來計(jì)算SELECT語句查詢的統(tǒng)計(jì)值。聚合函數(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)用。也可以像存儲(chǔ)過程一樣，通過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語句、語句塊或者存儲(chǔ)過程的執(zhí)行流程。使用BEGIN…END可以將多條T-SQL語句封裝起來，構(gòu)成一個(gè)獨(dú)立的語句塊。BEGIN關(guān)鍵字表示語句塊的開始，END關(guān)鍵字表示語句塊的結(jié)束，它們必須成對(duì)出現(xiàn)。BEGIN…END的語法格式如下：BEGIN{sql_statement|statement_block}ENDBegin…EndIF…ELSE

IF…ELSE語句可以控制程序按條件執(zhí)行。當(dāng)IF關(guān)鍵字后的條件滿足〔布爾表達(dá)式返回TRUE時(shí)〕，那么在執(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}]查詢課程號(hào)為“105237〞的課程的平均分是否超過了85分，假設(shè)超過，那么輸出考出了高分的信息。USEEducationalGODECLARE@Course_IDvarchar(10)SELECT@Course_ID='105237'/*查詢的課程號(hào)*/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)識(shí)符規(guī)那么，且必須存在。求1+2+3+…+100的累加和。DECLARE@iINT,@sumINTSET@i=1SET@sum=0LabelLoop:SET@sum=@sum+@iSET@i=@i+1IF(@i<=100)G