數(shù)據(jù)庫第5章資料

第5章

T-SQL程序設(shè)計(jì)基礎(chǔ)1.數(shù)據(jù)類型2.常量、變量和運(yùn)算符3.函數(shù)4.批處理和流程限制

數(shù)據(jù)類型數(shù)據(jù)類型對應(yīng)著SQLServer2008系統(tǒng)在內(nèi)存或磁盤上開拓存儲(chǔ)空間的大小，也確定了訪問、顯示、更新數(shù)據(jù)的方式。除了支持?jǐn)?shù)值型、字符型、日期型、貨幣型等系統(tǒng)供應(yīng)的數(shù)據(jù)類型外，T-SQL還支持用戶自定義數(shù)據(jù)類型。系統(tǒng)供應(yīng)的數(shù)據(jù)類型SQLServer2008中供應(yīng)了豐富的數(shù)據(jù)類型，主要包括二進(jìn)制、精確數(shù)字、近似數(shù)字、字符串、日期時(shí)間等幾大類型。類別數(shù)據(jù)類型類別數(shù)據(jù)類型類別數(shù)據(jù)類型二進(jìn)制字符串BINARYVARBINARYIMAGE近似數(shù)字FLOATREAL日期時(shí)間DATETIMEDATESMALLDATETIME精確數(shù)字BITINTBIGINTSMALLINTTINYINTDECIMALNUMERICMONEYSMALLMONEY字符串CHARVARCHARTEXTNCHARNVARCHARNTEXT其它類型TIMESTAMPSQL_VARIANTTABLECURSORUNIQUEIDENTIFIERXML表5-1SQLServer2008系統(tǒng)供應(yīng)的數(shù)據(jù)類型用戶自定義數(shù)據(jù)類型1．創(chuàng)建用戶自定義數(shù)據(jù)類型系統(tǒng)存儲(chǔ)過程sp_addtype可以創(chuàng)建用戶自定義數(shù)據(jù)類型。【例5-1】在數(shù)據(jù)庫TSG中創(chuàng)建自定義數(shù)據(jù)類型Number，SMALLINT類型，允許空。USETSGGOEXECsp_addtypeNumber,'SMALLINT','NULL'用戶自定義數(shù)據(jù)類型2．查看用戶自定義數(shù)據(jù)類型存儲(chǔ)過程sp_help可以查看用戶自定義數(shù)據(jù)類型。。【例5-2】查看Number的特征。EXECsp_helpNumber圖5-1例5-2的運(yùn)行結(jié)果用戶自定義數(shù)據(jù)類型3．刪除用戶自定義數(shù)據(jù)類型存儲(chǔ)過程sp_droptype可以刪除用戶自定義數(shù)據(jù)類型。【例5-3】刪除自定義的數(shù)據(jù)類型Number。EXECsp_droptypeNumber常量、變量和運(yùn)算符常量是一個(gè)常值，在程序運(yùn)行中保持不變，一經(jīng)定義程序本身不能變更其值。變量是在程序運(yùn)行過程中其值可以變更的量。常量1．字符串常量字符串常量括在單引號內(nèi)，并包含字母數(shù)字字符（a-z、A-Z和0-9）以及特殊字符如感嘆號(!)、at符(@)和數(shù)字號(#)。例如：‘Cincinnati’、‘O‘、'Brien'、'ProcessXis50%complete.'

字符串常量還可以接受Unicode字符編碼格式。例如：N'ASQLString'表示字符串'ASQLString'是一個(gè)Unicode字符串。常量2．二進(jìn)制常量二進(jìn)制常量具有前輟0x并且是十六進(jìn)制數(shù)字字符串。這些常量不運(yùn)用引號括起。例如，0xAE、0x12Ef、0x69048AEFDD010E、0x（空二進(jìn)制串）常量3．日期/時(shí)間常量日期/時(shí)間常量運(yùn)用特定格式的字符日期值來表示，并被單引號括起來。例如，'2010-12-05、'12/05/2010'、''20101205'

其次種依靠于DATEFORMAT，是SQLServer默認(rèn)的處理方式。常量4．?dāng)?shù)值常量1）整型常量整型常量用不包含小數(shù)點(diǎn)的數(shù)字字符串來表示。必需全部為數(shù)字，它們不能包含小數(shù)。例如：2356、8等都是整型常量。常量4．?dāng)?shù)值常量2）數(shù)值常量數(shù)字常量分為精確數(shù)值常量和浮點(diǎn)常量。精確數(shù)值常量由沒有用引號括起來并且包含小數(shù)點(diǎn)的數(shù)字字符串來表示。例如：1894.1204和2.0等。浮點(diǎn)常量運(yùn)用科學(xué)記數(shù)法來表示。例如101.5E5和0.5E-2等常量4．?dāng)?shù)值常量3）貨幣常量貨幣常量以前綴為可選的小數(shù)點(diǎn)和可選的貨幣符號不運(yùn)用引號括起的數(shù)字字符串來表示。例如：$12和$542023.14都為貨幣常量，前面的貨幣符號“$”是可選的。常量4．?dāng)?shù)值常量4）uniqueidentifier常量UNIQUEIDENTIFIE常量是表示GUID的字符串?？梢赃\(yùn)用字符或二進(jìn)制字符串格式指定。'6F9619FF-8B86-D011-B42D-00C04FC964FF'0xff19966f868b11d0b42d00c04fc964ff變量1．局部變量（LocalVariable）局部變量是用戶自定義的變量，它的作用范圍僅在定義它的程序內(nèi)部。局部變量名以“@”開頭，用DECLARE叮囑進(jìn)行定義，語法格式如下：DECLARE@變量名變量類型[,@變量名變量類型]運(yùn)用SELECT或SET叮囑來設(shè)置變量的值，語法格式如下：SELECT@變量名=變量值或者SET@變量名=變量值變量1．局部變量（LocalVariable）【例5-4】聲明一個(gè)存放名稱的變量Name，類型為char，長度為10，賦值“圖書館”并輸出變量的值。DECLARE@Namechar(10)SELECT@Name='圖書館'PRINT@Name圖5-2例5-4的運(yùn)行結(jié)果變量2．全局變量（GlobalVariable）不是由用戶的程序定義的，是在服務(wù)器級定義的系統(tǒng)內(nèi)部運(yùn)用的變量，任何程序均可以隨時(shí)運(yùn)用。引用全局變量必需以標(biāo)記符“@@”開頭。全局變量含義@@CONNECTIONS返回自上次啟動(dòng)以來連接或試圖連接的次數(shù)@@CURSOR_ROWS返回連接上最后打開的由表中當(dāng)前存在的@@ERROR返回最后執(zhí)行的T-SQL語句的錯(cuò)誤代碼@@FETCH_STATUS返回上一次FETCH語句的狀態(tài)值@@IDENTITY返回最后插入的標(biāo)識值@@MAX_CONNECTIONS返回SQL上允許的同時(shí)用戶連接的最大數(shù)。@@PROCID返回當(dāng)前存儲(chǔ)過程的ID值@@OPTIONS返回當(dāng)前SET選項(xiàng)的信息@@ROWCOUNT返回受上一語句影響的行數(shù)，任何不返回行的語句將這一變量設(shè)置為0@@SERVERNAME返回運(yùn)行SQL服務(wù)器名稱@@SERVICENAME返回SQLServer正運(yùn)行于哪種服務(wù)狀態(tài)之下，如MSSQLServer，MSDTC，SQLServerAgent@@SPID返回當(dāng)前用戶進(jìn)程的服務(wù)器進(jìn)程標(biāo)識符@@TRANCOUNT返回當(dāng)前連接的活動(dòng)事務(wù)數(shù)@@VERSION返回SQLServer的版本信息表5-2常用的全局變量變量2．全局變量（GlobalVariable）【例5-5】修改TSG數(shù)據(jù)庫的Book表中某條記錄的書號“CallNo”，用@@ERROR檢測主鍵沖突。USETSGGOUPDATEBookSETCallNo='F121/L612'WHERECallNo='G0/Z810'IF@@ERROR=2627 PRINT'Aconstraintviolationoccurred'圖5-3例5-5的運(yùn)行結(jié)果變量2．全局變量（GlobalVariable）【例5-6】查看SQLServer的版本號。SELECT@@VERSION圖5-4例5-6的運(yùn)行結(jié)果【例5-7】@@ROWCOUNT的運(yùn)用。USETSGGOUPDATEBOOKSETTitle='ZDMA'WHERECallNo='999-888-7777'IF@@ROWCOUNT=0 print'Warning:Norowswereupdated'圖5-5例5-7的運(yùn)行結(jié)果注釋與輸出1．注釋注釋語句不是可執(zhí)行的語句，不參與程序的編譯。作用：說明代碼的功能或?qū)Υa的實(shí)現(xiàn)方式給出簡要的說明或提示。對T-SQL語句進(jìn)行注釋有兩種方法：運(yùn)用“--”進(jìn)行單行注釋。利用“/*…*/”進(jìn)行塊注釋。注釋與輸出2．輸出PRINT語句可干脆顯示表達(dá)式結(jié)果RAISERROR({msg_id|msg_str},SEVERITY,STATE[,argumentl[,…n]])RASERROR函數(shù)將錯(cuò)誤信息顯示在屏幕上，同時(shí)也可以記錄在日志中。RASEERROR函數(shù)的基本語法格式如下：msg_id：錯(cuò)誤號，省略時(shí)系統(tǒng)將產(chǎn)生一個(gè)錯(cuò)誤號為50000的錯(cuò)誤消息；msg_str：錯(cuò)誤信息；SEVERITY：錯(cuò)誤的嚴(yán)峻級別；STATE：說明發(fā)生錯(cuò)誤時(shí)的狀態(tài)信息。注釋與輸出2．輸出【例5-8】查詢索書號為F121/L612的書籍?dāng)?shù)量，假如1本以上，則顯示數(shù)量1本以上，否則輸出庫存不足信息。USETSGGODECLARE@CallNovarchar(9),@Titlevarchar(40),@NumbersmallintSET@CallNo='qq'SELECT@Title=Title,@Number=NumberFromBookWhereCallNo=@CallNoIF(@Number>1)PRINT@Title+'1本以上。'ELSERAISERROR('庫存不足,報(bào)警！',10,1)運(yùn)算符運(yùn)算符用來執(zhí)行數(shù)據(jù)之間的數(shù)學(xué)或比較運(yùn)算，是指定要在一個(gè)或者多個(gè)表達(dá)式中執(zhí)行操作的一種符號。T-SQL中運(yùn)算符主要有6大類：算術(shù)運(yùn)算符賦值運(yùn)算符位運(yùn)算符比較運(yùn)算符邏輯運(yùn)算符字符串串聯(lián)運(yùn)算符運(yùn)算符1．算術(shù)運(yùn)算符包括：加（+）、減（-）、乘（*）、除（/）和取模（%）等?！纠?-9】SELECT+3+2加法SELECT-4-1減法SELECT-5*2乘法SELECT10/2整數(shù)相除,10.0/2小數(shù)除法SELECT10%2取余 圖5-7例5-9的運(yùn)行結(jié)果運(yùn)算符2．賦值運(yùn)算符“=”：將數(shù)據(jù)值指派給特定的對象。3．位運(yùn)算符操作數(shù)：整型或者二進(jìn)制數(shù)據(jù)(IMAGE類型除外)包括：位與（&）、位或（|）、位異或（^）和位非（～）等。運(yùn)算符4．比較運(yùn)算符比較兩個(gè)表達(dá)式的大小或是否相同，其比較的結(jié)果：TRUE（表示表達(dá)式的結(jié)果為真）FALSE（表示表達(dá)式的結(jié)果為假）UNKNOWN除了TEXT、NTEXT或IMAGE數(shù)據(jù)類型的表達(dá)式外，比較運(yùn)算符可以用于全部其它表達(dá)式。運(yùn)算符5．邏輯運(yùn)算符把多個(gè)邏輯表達(dá)式連接起來。包括AND、OR和NOT。運(yùn)算結(jié)果：TRUE或FALSE值的布爾數(shù)據(jù)類型。6．字符串串聯(lián)運(yùn)算符加號(+)，可以進(jìn)行字符串串聯(lián)。例如，表達(dá)式'ab'+'cd'的結(jié)果為'abcd'。函數(shù)函數(shù)是一組編譯好的T-SQL語句，SQLServer支持兩種函數(shù)類型：內(nèi)置函數(shù)：是一組預(yù)定義函數(shù)，是T-SQL語言的一部分，用戶可以干脆運(yùn)用它們實(shí)現(xiàn)希望的功能。用戶定義函數(shù)：是由用戶自行定義并編寫的函數(shù)，用戶可以依據(jù)須要編寫和修改自定義函數(shù)，然后進(jìn)行調(diào)用。常用內(nèi)置函數(shù)SQLServr2008中的函數(shù)或許分為7類：數(shù)學(xué)函數(shù)字符串函數(shù)日期時(shí)間函數(shù)聚集函數(shù)轉(zhuǎn)換函數(shù)系統(tǒng)函數(shù)用戶自定義函數(shù)常用內(nèi)置函數(shù)1．字符串函數(shù)

函數(shù)UPPER(str)ASCII(str)REPLICATE(str,n)CHAR(n)PATINDEX('%pattern%',str)CHARINDEX(str1,str2[,start])LEN(str)STUFF(str1,start,length,str2)REVERSE(str)SUBSTRING(str,start,length)SPACE(n)LTRIM(str)LEFT(str,n)RTRIM(str)RIGHT(str,n)LOWER(str)STR(f[,p[,s]])表5-3常用字符串函數(shù)常用內(nèi)置函數(shù)1．字符串函數(shù)

【例5-11】刪除字符串變量中起始處的空格。DECLARE@Str1VARCHAR(40)SET@Str1='TWOSPACESBEFORETHISSTRING'SELECT@Str1SELECT'THERESULTIS:'+LTRIM(@Str1)圖5-9例5-11的運(yùn)行結(jié)果常用內(nèi)置函數(shù)1．字符串函數(shù)

【例5-12】編寫一段程序輸出數(shù)值147.58的整數(shù)和小數(shù)部分。DECLARE@StrCHAR(18),@PosSMALLINTSET@Str=STR('147.58',6,2)SET@Pos=CHARINDEX('.',@STR)PRINTRTRIM(@Str)+'的整數(shù)部分是：'+LEFT(@Str,@Pos-1)PRINTRTRIM(@Str)+'的小數(shù)部分是：'

+RIGHT(RTRIM(@Str),LEN(RTRIM(@Str))-@pos+1)圖5-10例5-12的運(yùn)行結(jié)果常用內(nèi)置函數(shù)2．?dāng)?shù)學(xué)函數(shù)函數(shù)函數(shù)ABS(n)MOD(m,n)ACOS(n)PI()ASIN(n)POWER(m,n)ATAN(n)RADIANS(n)CEILING(n)RAND()COS(n)ROUND(m,n)DEGREES(n)SIGN(n)EXP(n)SIN(n)FLOOR(n)SQRT(n)LOG(n)SQUARE(n)LOG10(n)TAN(n)表5-4常用數(shù)學(xué)函數(shù)常用內(nèi)置函數(shù)2．?dāng)?shù)學(xué)函數(shù)

【例5-13】對同一數(shù)值運(yùn)用FLOOR、CEILING和ROUND函數(shù)。SELECTFLOOR(1.2345),CEILING(1.2345),ROUND(1.2345,3)圖5-11例5-13的運(yùn)行結(jié)果常用內(nèi)置函數(shù)3．日期和時(shí)間函數(shù)函數(shù)GETDATE()DATEPART(datepart,date)DATENAME(datepart,date)DAY(date)MONTH(date)YEAR(date)DATEADD(datepart,number,date)DATEDIFF(datepart,date1,date2)表5-5常見的日期和時(shí)間函數(shù)常用內(nèi)置函數(shù)3．日期和時(shí)間函數(shù)【例5-14】獲得系統(tǒng)當(dāng)前日期，并分別提取出月、日和年。SELECTMONTH(GETDATE())月,

DAY(GETDATE())日,YEAR(GETDATE())年SELECTDATENAME(MONTH,GETDATE())月,

DATENAME(DAY,GETDATE())日,

DATENAME(YEAR,GETDATE())年SELECTDATEPART(MONTH,GETDATE())月,

DATEPART(DAY,GETDATE())日,

DATEPART(YEAR,GETDATE())年圖5-12例5-14的運(yùn)行結(jié)果常用內(nèi)置函數(shù)3．日期和時(shí)間函數(shù)【例5-15】計(jì)算2006年9月1日到當(dāng)前日期閱歷了多少天數(shù)、多少月和多少周。SELECTDATEDIFF(DAY,'2006-9-1',GETDATE())天數(shù),DATEDIFF(MONTH,'2006-9-1',GETDATE())月數(shù),DATEDIFF(WEEK,'2006-9-1',GETDATE())周數(shù)圖5-13例5-15的運(yùn)行結(jié)果常用內(nèi)置函數(shù)4．轉(zhuǎn)換函數(shù)能夠完成某些數(shù)據(jù)類型的轉(zhuǎn)換。（1）CAST(expression,type)：將表達(dá)式expression轉(zhuǎn)換為指定的type數(shù)據(jù)類型。（2）CONVERT(type[(length)],expression[,style])：type為expression轉(zhuǎn)換后的數(shù)據(jù)類型；length表示轉(zhuǎn)換后的數(shù)據(jù)長度；style將日期時(shí)間類型的數(shù)據(jù)轉(zhuǎn)換為字符型的數(shù)據(jù)時(shí)，該參數(shù)用于指定轉(zhuǎn)換后的樣式。常用內(nèi)置函數(shù)4．轉(zhuǎn)換函數(shù)【例5-16】將π/3轉(zhuǎn)換成相應(yīng)度數(shù)對應(yīng)的字符串。SELECTCONVERT(VARCHAR,DEGREES((PI()/3)))度數(shù)1,CAST(DEGREES((PI()/3))ASVARCHAR)度數(shù)2圖5-14例5-16的運(yùn)行結(jié)果常用內(nèi)置函數(shù)5．系統(tǒng)函數(shù)函數(shù)DB_ID(name)，DB_NAME(id)HOST_ID(name)，HOST_NAME(id)OBJECT_ID(name)，OBJECT_NAME(id)SUSER_ID(name)，SUSER_NAME(id)USER_ID(name)，USER_NAME(id)COL_NAME(table_id,col_id)COL_LENGTH(table,col)INDEX_COL(table,index_id,key_id)DATALENGTH(expression)表5-7部分系統(tǒng)函數(shù)常用內(nèi)置函數(shù)5．系統(tǒng)函數(shù)【例5-17】利用系統(tǒng)函數(shù)獲得一些信息。SELECTCURRENT_USER當(dāng)前用戶,DATALENGTH('數(shù)據(jù)庫')長度,HOST_NAME()計(jì)算機(jī)名,SYSTEM_USER當(dāng)前登錄用戶名,USER_NAME(1)依據(jù)ID返回用戶名圖5-15例5-17的運(yùn)行結(jié)果批處理“批”：是從客戶端傳遞給服務(wù)器的一組完整的數(shù)據(jù)和SQL指令的集合，從應(yīng)用程序一次性的發(fā)送到SQLServer執(zhí)行?！癎O”：是批處理的標(biāo)記，兩個(gè)“GO”之間的T-SQL語句稱為一個(gè)批處理?！纠?-21】創(chuàng)建數(shù)據(jù)庫TSG，并將當(dāng)前數(shù)據(jù)庫切換到TSG。CREATEDATABASETSGUSETSG是否能成功執(zhí)行？CREATEDATABASETSGGOUSETSG正確的做法是：批處理考慮如下程序：DECLARE@MyVarvarchar(50)--該變量聲明僅在該"批"中有效。SELECT@MyVar='今日天氣真不錯(cuò)！'PRINT'第一個(gè)批處理執(zhí)行結(jié)束'GOPRINT@MyVar--將產(chǎn)生錯(cuò)誤，因?yàn)锧MyVar在該"批"中未經(jīng)定義而干脆運(yùn)用。PRINT'其次個(gè)批處理執(zhí)行結(jié)束'GOPRINT'第三個(gè)批處理執(zhí)行結(jié)束'GO批處理批處理要留意以下幾個(gè)問題：（1）并非全部語句都可以和其它語句組合在一個(gè)批中。CREATEDEFAULT，CREATEPROCEDURE，CREATERULE，CREATETRIGGER，CREATEVIEW必需獨(dú)自成批處理，不能夠與其他語句組合運(yùn)用。（2）假如EXECUTE語句是批處理中的第一句，則不須要EXECUTE關(guān)鍵字；否則須要EXECUTE關(guān)鍵字。（3）RULE和DEFAULT不能在同一個(gè)批處理中綁定列同時(shí)又被運(yùn)用。（4）CHECK約束不能在同一批處理中既定義又運(yùn)用。（5）用SET語句變更的項(xiàng)，在批處理結(jié)束時(shí)生效。（6）在同一批處理中，不能修改一個(gè)表的某些列后馬上運(yùn)用這些新列。流程限制流程限制語句：通過推斷指定的某些值來限制程序運(yùn)行方向的語句。包括：BEGIN…ENDIF…ELSECASEWHILEGOTOWAITFOR流程限制1．BEGIN…END：能夠?qū)⒍鄠€(gè)T-SQL語句組合成一個(gè)語句塊，并將它們視為一個(gè)單元處理。

其語法格式為：BEGIN{SQL語句|語句塊}END流程限制2．IF…ELSE：條件推斷語句，ELSE子句可選。語法格式為：IF<條件表達(dá)式><SQL語句>|BEGIN<代碼序列>END[ELSE<SQL語句>|BEGIN<代碼序列>END]流程限制3．CASE：可以計(jì)算多個(gè)條件式，并將其中一個(gè)符合條件的結(jié)果表達(dá)式返回。CASE語句只能嵌入到SELECT語句的SELECT子句中。CASE語句有兩種格式。語法格式1：CASE表達(dá)式

WHEN表達(dá)式_11THEN表達(dá)式_12…WHEN表達(dá)式_n1THEN表達(dá)式_n2[else表達(dá)式_m]END流程限制3．CASE：語法格式1【例5-22】在Patron表中，選取姓王讀者的Name（讀者姓名）和Gender（性別），假如Gender的值為‘男’，則輸出M；為‘女’，則輸出‘F’。USETSGGOSELECTName,性別=CASEGender

WHEN'男'THEN'M'WHEN'女'THEN'F'ENDFROMPatronWHERENameLIKE'王%'圖5-17例5-22的運(yùn)行結(jié)果流程限制3．CASE語法格式2：CASEWHEN條件表達(dá)式_1THEN表達(dá)式_1…WHEN條件表達(dá)式_nTHEN表達(dá)式_n[else表達(dá)式_m]END流程限制3．CASE：語法格式2【例5-23】在Book表中，選取姓“周”的作者所編著圖書的Title（書名）和Number（數(shù)量），假如Number的值大于3，則輸出“多”，否則輸出“少”。USETSGGOSELECTTi