《數(shù)據(jù)庫原理及應(yīng)用2》課件第6章

第6章Transact-SQL6.1常量6.2變量6.3流程控制語句6.4函數(shù)6.5用戶定義函數(shù)6.6游標(biāo)6.7本章小結(jié)

6.1常量

1．字符串常量

2．整型常量

3．實型常量

4．日期時間型常量

5．貨幣型常量

6．Uniqueidentifier常量

6.2變量

變量對于一種語言來說是必不可少的組成部分。Transact-SQL語言允許使用兩種變量：一種是用戶自己定義的局部變量(LocalVariable)，另一種是系統(tǒng)提供的全局變量(GlobalVariable)。

1．局部變量

1)局部變量的聲明

declare@變量名變量類型[,@變量名變量類型…]

其中，變量類型可以是SQLServer支持的所有數(shù)據(jù)類型，也可以是用戶自定義的數(shù)據(jù)類型。

2)局部變量的賦值

局部變量不能使用“變量=變量值”的格式進(jìn)行初始化，必須使用select或set語句來設(shè)置其初始值。

(1)通過set來賦值。

格式為：

set@局部變量=變量值

【例1】declare@a1int，@a2char(10)

Set@a1=2012

Set@a2=‘china’

(2)通過select來賦值。

格式為：

select@局部變量=變量值

【例2】declare@namechar(10)

Select@name=姓名from學(xué)生表where學(xué)號=‘001’

【例3】將字符串“sqlserver”和“2000”做連接，并輸出。

declare@anvarchar(10),@bnvarchar(10)set@a=‘sqlserver’

set@b=′2000′

select@a+@b

2．全局變量

全局變量是SQLServer2000系統(tǒng)內(nèi)部使用的變量，其作用范圍并不局限于某一程序，而是任何程序均可隨時調(diào)用。全局變量通常存儲一些SQLServer2000的配置設(shè)置值和效能統(tǒng)計數(shù)據(jù)。用戶可在程序中用全局變量來測試系統(tǒng)的設(shè)定值或者Transact-SQL命令執(zhí)行后的狀態(tài)值。引用全局變量時，全局變量的名字前面要有兩個標(biāo)記符“@@”。不能定義與全局變量同名的局部變量。從SQLServer7.0開始，全局變量就以系統(tǒng)函數(shù)的形式使用。

【例4】select*from學(xué)生表。

Print@@rowcount

【例5】select@@version

6.3流程控制語句

1．Print語句

Print語句用于向客戶端返回用戶定義的消息。使用Print可以幫助我們排除Transact-SQL代碼中的故障，檢查數(shù)據(jù)值或生成報告。

【例6】Print'ABCDEFG'

2．begin…end語句

begin…end語句包括一系列Transact-SQL語句，從而可以執(zhí)行一組Transact-SQL語句。begin和end是控制流語言的關(guān)鍵字。

語法：begin

SQL語句1

SQL語句2

…

end

3．if…else語句

1)語法

ifBoolean_expression

{sql_statement|statement_block}

[else

{sql_statement|statement_block}]

2)參數(shù)

(1)Boolean_expression：返回True或False的表達(dá)式。如果布爾表達(dá)式中含有Select語句，則必須用括號將Select語句括起來。

(2)?{sql_statement|statement_block}：任何Transact-SQL語句或用語句塊定義的語句組。除非使用語句塊，否則if或else條件只能執(zhí)行其后的一條Transact-SQL語句。

若要定義語句塊，必須使用控制流關(guān)鍵字begin和end。

【例7】判斷a是正數(shù)還是負(fù)數(shù)。

declare@aint

set@a=10

if@a>0

print‘a(chǎn)為正數(shù)’

else

print'a為負(fù)數(shù)'

【例8】判斷成績表里的成績是否大于90分。

ifexists(select*from成績表where成績>90)

begin

Select*from成績表where成績>90

end

else

Print'nostudent'

4．While語句

While語句用于設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語句。可以使用Break和Continue關(guān)鍵字在循環(huán)內(nèi)部控制While語句的執(zhí)行。

1)語法

WhileBoolean_expression

{sql_statement|statement_block}

[Break]

{sql_statement|statement_block}

[Continue]

{sql_statement|statement_block}

2)參數(shù)

(1)?Boolean_expression：

(2)?{sql_statement|statement_block}：

(3)?Break：

(4)?Continue：

【例9】計算1～100的奇數(shù)和。

【例10】計算和小于10的那些數(shù)的和。

5．Case函數(shù)

Case函數(shù)用于計算條件列表并返回多個可能結(jié)果表達(dá)式之一。

格式1：格式2：

【例11】從成績表查詢學(xué)號為2013001號的成績，并將成績轉(zhuǎn)換為等級。

6．Waitfor延遲語句

在達(dá)到指定時間或時間間隔之前，或者指定語句至少修改或返回一行之前，阻止執(zhí)行批處理、存儲過程或事務(wù)。

【例12】要求5秒鐘之后執(zhí)行select語句。

waitfordelay‘00：00：05’

select*from學(xué)生表

6.4函數(shù)

SQLServer2000提供了一些內(nèi)置函數(shù)，用戶可以使用這些函數(shù)方便地實現(xiàn)一些功能。以下舉例說明一些常用的函數(shù)。

1．轉(zhuǎn)換函數(shù)

(1)?cast()：將一種數(shù)據(jù)類型的表達(dá)式轉(zhuǎn)換為另一種數(shù)據(jù)類型的表達(dá)式。

語法：

cast(expressionasdata_type[(length)])

【例13】select姓名+‘(’+cast(成績asvarchar(10))+‘)’from學(xué)生表

下列代碼將成績?yōu)閚ull值的數(shù)據(jù)項顯示為0分。

select姓名+‘(’+(

case

when成績isnullthen‘0’

elsecast(成績asvarchar(10))

end)

+')'from學(xué)生表

(2)?convert()：將一種數(shù)據(jù)類型的表達(dá)式轉(zhuǎn)換為另一種數(shù)據(jù)類型的表達(dá)式。

語法：

convert(data_type[(length)],expression[,style])

【例14】select姓名+'('+convert(nvarchar(10),出生日期,20)+')'from學(xué)生表

2．字符串函數(shù)

(1)?len(character_expression)：返回字符表達(dá)式中的字符數(shù)。如果字符串中包含前導(dǎo)空格和尾隨空格，則函數(shù)會將它們包含在計數(shù)內(nèi)。len對相同的單字節(jié)和雙字節(jié)字符串返回相同的值。

【例15】selectmax(len(姓名))from學(xué)生表

(2)?datalength(expression)：返回用于表示任何表達(dá)式的字節(jié)數(shù)。

【例16】declare@anvarchar(10)

set@a=‘a(chǎn)bc’

selectdatalength(@a)/*顯示6*/

(3)?left(character_expression,integer_expression)：返回字符串中從左邊開始指定個數(shù)的字符。

【例17】declare@stringtestchar(10)

set@stringtest=‘robin’

selectleft(@stringtest,3)/*顯示rob*/

(4)?right(character_expression,integer_expression)：返回字符表達(dá)式中從起始位置(從右端開始)到指定字符位置(從右端開始計數(shù))的部分。

【例18】declare@stringtestchar(10)

set@stringtest=‘robin’

selectright(@stringtest,3)/*顯示in*/

(5)?substring(value_expression,start_expression,length_expression)：返回字符表達(dá)式、二進(jìn)制表達(dá)式、文本表達(dá)式或圖像表達(dá)式的一部分。

【例19】selectx=substring(‘a(chǎn)bcdef’,2,3)/*顯示bcd*/

(6)?upper(character_expression)：返回將小寫字符轉(zhuǎn)換為大寫字符后得到的字符表達(dá)式。

【例20】selectupper('hello')/*顯示HELLO*/

(7)?lower(character_expression)：返回將大寫字符轉(zhuǎn)換為小寫字符后得到的字符表達(dá)式。

【例21】declare@stringtestchar(10)

set@stringtest=‘ROBIN’

selectlower(left(@stringtest,3))/*顯示rob*/

(8)?space(integer_expression)：返回由重復(fù)的空格組成的字符串。

【例22】select姓名+space(3)+性別from學(xué)生表

(9)?replicate(character_expression,times)：返回多次復(fù)制后的字符表達(dá)式。times參數(shù)的計算結(jié)果必須為整數(shù)。

【例23】declare@aint

set@a=3

printreplicate('*',@a)/*顯示****/

【例24】以“*”方式輸出菱形。

(10)?stuff(character_expression,start,length,character_expression)：將字符串插入另一字符串。它在第一個字符串中從開始位置刪除指定長度的字符，然后將第二個字符串插入到第一個字符串的開始位置。

【例25】selectstuff(‘a(chǎn)xyzfg’,2,3,‘bcde’)/*結(jié)果為‘a(chǎn)bcdefg’*/

(11)?reverse(character_expression)：按相反順序返回字符表達(dá)式。

【例26】printreverse('mountainbike')/*輸出ekibniatnuom*/

(12)?ltrim(characterexpression)：返回刪除了前導(dǎo)空格的字符表達(dá)式。

【例27】declare@stringtestchar(10)

set@stringtest=‘robin’

select‘start-’+ltrim(@stringtest),‘start-’+@stringtest/*顯示start-robinstart-robin*/

(13)?rtrim(characterexpression)：返回刪除了尾隨空格的字符表達(dá)式。

【例28】declare@stringtestchar(10)

set@stringtest=‘robin’

select@stringtest+'-end',rtrim(@stringtest)+'-end'/*顯示robin-endrobin-end*/

(14)?trim(character_expression)：返回刪除了前導(dǎo)空格和尾隨空格的字符表達(dá)式。

(15)?charindex(expression1,expression2[,start_location])：在expression2中搜索expression1并返回其起始位置(如果能找到)。搜索的起始位置為start_location。

【例29】declare@documentvarchar(64)

(16)?patindex(‘%pattern%’,expression)：返回指定表達(dá)式中某模式第一次出現(xiàn)的起始位置；如果在全部有效的文本和字符數(shù)據(jù)類型中沒有找到該模式，則返回0。

【例30】selectpatindex(‘%李%’,姓名)from學(xué)生表

(17)?str(float_expression[,length[,decimal]])：返回由數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換來的字符數(shù)據(jù)。其中，float_expression表示帶小數(shù)點(diǎn)的近似數(shù)字(float)數(shù)據(jù)類型的表達(dá)式；length表示總長度，它包括小數(shù)點(diǎn)、符號、數(shù)字以及空格，默認(rèn)值為10；decimal表示小數(shù)點(diǎn)后的位數(shù)。

【例31】selectstr(123.45,6,1)/*輸出123.5*/

(18)?char(integer_expression)：將intASCII代碼轉(zhuǎn)換為字符。其中，integer_expression為介于0和255之間的整數(shù)。如果該整數(shù)表達(dá)式不在此范圍內(nèi)，將返回null值。

(19)?replace(character_expression,searchstring,

replacementstring)：將表達(dá)式中的一個字符串替換為另一個字符串或空字符串后，返回一個字符表達(dá)式。

【例32】printreplace('mountainbike','mountain','allterrain')/*返回allterrainbike*/

3．日期函數(shù)

(1)?getdate()：返回系統(tǒng)的當(dāng)前日期，getdate函數(shù)不使用參數(shù)。

(2)?datepart(datepart,date)：返回一個表示日期的指定日期部分的整數(shù)。其中，datepart參數(shù)指定需要對日期中的哪一部分返回新值，它可取下列值：year、quarter、month、dayofyear、day、week、weekday、hour、minute、second、millisecond等。

【例33】selectdatepart(year,getdate())

(3)?datename(datepart,date)：返回表示指定date的指定datepart的字符串。

【例34】select姓名,datepart(month,出生日期)from學(xué)生表

(4)?dateadd(datepart,number,date)：將指定number時間間隔(有符號整數(shù))與指定date的指定datepart相加后，返回該date。

【例35】selectdateadd(month,1,‘2006-08-30’)/*顯示2006-09-3000:00:00.000*/

(5)?datediff(datepart,startdate,enddate)：返回指定的startdate和enddate之間所跨的指定datepart邊界的計數(shù)(帶符號的整數(shù))。

【例36】declare@startdatetime,@enddatetime

set@start=‘2007-05-0812:10:09’

set@end=‘2007-05-0712:10:09’

selectdatediff(day,@start,@end)/*顯示-1*/

(6)?day(date)、month(date)、year(date)：返回一個整數(shù)，該整數(shù)表示指定date的日、月、年。

【例37】printyear(getdate())

4．?dāng)?shù)學(xué)函數(shù)

(1)?abs(numeric_expression)：返回數(shù)值表達(dá)式的絕對值。

(2)?ASCII(character_expression)：返回字符表達(dá)式中最左側(cè)的字符的ASCII代碼值。

【例38】declare@stringtestchar(10)

set@stringtest=ASCII(‘robin’)

select@stringtest/*顯示82*/

(3)?ceiling(numeric_expression)：返回大于或等于指定數(shù)值表達(dá)式的最小整數(shù)。

(4)?floor(numeric_expression)：返回小于或等于指定數(shù)值表達(dá)式的最大整數(shù)。

(5)?power(numeric_expression,power)：返回對數(shù)值表達(dá)式進(jìn)行冪運(yùn)算的結(jié)果。power參數(shù)的計算結(jié)果必須為整數(shù)。

(6)?pi()：返回pi的常量值。

(7)?sqrt(float_expression)：返回指定浮點(diǎn)值的平方根。

(8)?square(float_expression)：返回指定浮點(diǎn)值的平方。

(9)?rand([seed])：返回從0到1之間的隨機(jī)float值。

(10)?round(numeric_expression,length[,function])：返回一個數(shù)值，四舍五入到指定的長度或精度。

5．其他常用函數(shù)

(1)?isdate(expression)：如果expression是datetime或smalldatetime數(shù)據(jù)類型的有效日期或時間值，則返回1；否則，返回0。

【例39】selectisdate(‘2009/2/29’)/*顯示0*/

(2)?isnull(check_expression,replacement_value)：使用指定的替換值替換null。

【例40】select姓名,isnull(成績,0)from學(xué)生表

(3)?nullif(expression,expression)：如果兩個指定的表達(dá)式相等，則返回空值。

(4)?isnumeric(expression)：確定表達(dá)式是否為有效的數(shù)值類型。

(5)?coalesce(expression[,...n])：返回其參數(shù)中第一個非空表達(dá)式。

6.5用戶定義函數(shù)

與編程語言中的函數(shù)類似，SQLServer的用戶定義函數(shù)用于接受參數(shù)、執(zhí)行操作(例如復(fù)雜計算)并將操作結(jié)果以值的形式返回。返回值可以是單個標(biāo)量值或結(jié)果集。

在SQLServer中使用用戶定義函數(shù)有以下優(yōu)點(diǎn)：

(1)允許模塊化程序設(shè)計。

(2)執(zhí)行速度更快。

(3)減少網(wǎng)絡(luò)流量。

1．標(biāo)量函數(shù)

(1)創(chuàng)建標(biāo)量函數(shù)。

【例41】

【例42】

(2)調(diào)用標(biāo)量函數(shù)。

可以在Transact-SQL語句中允許使用標(biāo)量表達(dá)式的任何位置調(diào)用返回標(biāo)量值(與標(biāo)量表達(dá)式的數(shù)據(jù)類型相同)的用戶定義函數(shù)。必須使用至少由兩部分組成名稱的函數(shù)來調(diào)用標(biāo)量值函數(shù)，即架構(gòu)名.對象名。

【例43】selectdbo.f1(23,28)

2．表值函數(shù)

(1)創(chuàng)建內(nèi)聯(lián)表值函數(shù)。

【例44】

(2)調(diào)用內(nèi)聯(lián)表值函數(shù)。

【例45】select*fromf1(‘男’)

注：調(diào)用時不需指定架構(gòu)名。

3．多語句表值函數(shù)

(1)創(chuàng)建多語句表值函數(shù)。

【例46】

(2)調(diào)用多語句表值函數(shù)。

【例47】select*fromf1(1)

6.6游標(biāo)

游標(biāo)的作用如下：

(1)允許定位在結(jié)果集的特定行。

(2)從結(jié)果集的當(dāng)前位置檢索一行或一部分行。

(3)支持對結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。

(4)為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同