目標掌握如何定義變量并賦值掌握如何輸出顯示數(shù)據(jù)掌握IF、WHILE、CASE邏輯控制語句理解SQL中批處理的概念使用變量變量分為：局部變量：局部變量必須以標記@作為前綴，如@age局部變量的使用也是先聲明，再賦值

全局變量：全局變量必須以標記@@作為前綴，如@@version全局變量由系統(tǒng)定義和維護，我們只能讀取，不能修改全局變量的值局部變量例如：DECLARE@namevarchar(8)DECLARE@seatint聲明局部變量

DECLARE@變量名數(shù)據(jù)類型賦值

SET@變量名=值

SELECT@變量名=值例如：SET@name=‘張三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘s25302’

必須確保篩選出的記錄只有1條

或

全局變量全局變量都使用兩個@標志作為前綴

變量含義@@ERROR最后一個T-SQL錯誤的錯誤號@@IDENTITY最后一次插入的標識值@@LANGUAGE當前使用的語言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時連接的最大數(shù)目@@ROWCOUNT 受上一個SQL語句影響的行數(shù)@@SERVERNAME本地服務器的名稱@@TRANSCOUNT 當前連接打開的事務數(shù)@@VERSION

SQLServer的版本信息SQLServer中的函數(shù)相當于Java語言中的內置包字符串函數(shù)日期函數(shù)數(shù)學函數(shù)系統(tǒng)函數(shù)字符串函數(shù)SELECTSTUFF('ABCDEFG',2,3,'我的音樂我的世界')返回：A我的音樂我的世界EFG在一個字符串中，刪除指定長度的字符，并在該位置插入一個新的字符串STUFFSELECTCHARINDEX('ACCP','MyAccpCourse',1)返回：4用來尋找一個指定的字符串在另一個字符串中的起始位置CHARINDEX舉例描述函數(shù)名SELECTLEN('SQLServer課程')返回：12返回傳遞給它的字符串長度LENSELECTLOWER('SQLServer課程')返回：sqlserver課程把傳遞給它的字符串轉換為小寫LOWERSELECTUPPER('sqlserver課程')返回：SQLSERVER課程把傳遞給它的字符串轉換為大寫UPPERSELECTLTRIM('周智宇')返回：周智宇（后面的空格保留）清除字符左邊的空格LTRIMSELECTRTRIM('周智宇')返回：周智宇（前面的空格保留）清除字符右邊的空格RTRIMSELECTRIGHT('買賣提.吐爾松',3)返回：吐爾松從字符串右邊返回指定數(shù)目的字符RIGHTSELECTREPLACE('莫樂可切.楊可','可','蘭')返回：莫樂蘭切.楊蘭替換一個字符串中的字符REPLACE日期函數(shù)SELECTDATEPART(day,’01/15/2000’)返回：15日期中指定日期部分的整數(shù)形式DATEPARTSELECTGETDATE()返回：今天的日期取得當前的系統(tǒng)日期GETDATE舉例描述函數(shù)名SELECTDATEADD(mm,4,’01/01/99’)返回：以當前的日期格式返回05/01/99將指定的數(shù)值添加到指定的日期部分后的日期DATEADDSELECTDATEDIFF(mm,’01/01/99’,’05/01/99’)返回：4兩個日期之間的指定日期部分的區(qū)別DATEDIFFSELECTDATENAME(dw,’01/01/2000’)返回：Saturday日期中指定日期部分的字符串形式DATENAME數(shù)學函數(shù)SELECTSQRT(9)返回：3取浮點表達式的平方根SqrtSELECTABS(-43)返回：43取數(shù)值表達式的絕對值ABS舉例描述函數(shù)名SELECTCEILING(43.5)返回：44返回大于或等于所給數(shù)字表達式的最小整數(shù)CEILINGSELECTPOWER(5,2)返回：25取數(shù)值表達式的冪值POWERSELECTROUND(43.543,1)返回：43.5將數(shù)值表達式四舍五入為指定精度ROUNDSELECTSIGN(-43)返回：-1對于正數(shù)返回+1，對于負數(shù)返回-1，對于0則返回0SignSELECTFLOOR(43.5)返回：43取小于或等于指定表達式的最大整數(shù)FLOOR系統(tǒng)函數(shù)SELECTUSER_NAME(1)返回：從任意數(shù)據(jù)庫中返回“dbo”從給定的用戶ID返回用戶名USER_NAMESELECTCONVERT(VARCHAR(5)，12345)返回：字符串12345用來轉變數(shù)據(jù)類型CONVERT舉例描述函數(shù)名SELECTCURRENT_USER返回：你登錄的用戶名返回當前用戶的名字CURRENT_USERSELECTDATALENGTH('中國A盟')返回：7返回用于指定表達式的字節(jié)數(shù)DATALENGTHSELECTHOST_NAME()返回：你所登錄的計算機的名字返回當前用戶所登錄的計算機名字HOST_NAMESELECTSYSTEM_USER返回：你當前所登錄的用戶名返回當前所登錄的用戶名稱SYSTEM_USER案例分析1—要求某公司印了一批充值卡，卡的密碼是隨機生成的，現(xiàn)在出現(xiàn)這個問題：

卡里面的“O和0”（哦和零）“i和1”（哎和一），用戶反映說看不清楚，公司決定，把存儲在數(shù)據(jù)庫中的密碼中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 請編寫SQL語句實現(xiàn)以上要求；數(shù)據(jù)庫表名：Card；密碼字段名：PassWord；案例分析1—分析這是更新語句，需要使用UPDATE語句；因為牽涉到字符串的替換，需要使用到SQLServer中的函數(shù)Replace；案例分析2—要求在數(shù)據(jù)庫表中有以下字符數(shù)據(jù)，如：

13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2

現(xiàn)在希望通過SQL語句進行排序，并且首先要按照前半部分的數(shù)字進行排序，然后再按照后半部分的數(shù)字進行排需，輸出要排成這樣：

13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2數(shù)據(jù)庫表名：SellRecord；字段名：ListNumber；案例分析2—分析這是查詢語句，需要使用SELECT語句需要使用到ORDERBY進行排序，并且在ORDERBY的排序列中，也需要重新計算出排序的數(shù)字來前半部分的數(shù)字，可以從先找到“-”符號的位置，然后，取其左半部分，最后再使用Convert函數(shù)將其轉換為數(shù)字：

Convert(int,Left(ListNumber,CharIndex('-',ListNumber)-1))后半部分的數(shù)字，可以先找到“-”符號的位置，然后把從第一個位置到該位置的全部字符替換為空格，最后再使用Convert函數(shù)將其轉換為數(shù)字：

Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))用戶自定義函數(shù)

標量函數(shù)：返回單個數(shù)據(jù)值。表值函數(shù)：返回值是一個記錄集合——表。在此函數(shù)中，return語句包含一條單獨的select語句。多語句表值函數(shù)：返回值是由選擇的結果構成的記錄集。在SQLServer中，用戶不僅可以使用標準的內置函數(shù)，也可以使用自己定義的函數(shù)來實現(xiàn)一些特殊的功能。用戶自定義函數(shù)可以在對象資源管理器中創(chuàng)建，也可以使用CREATEFUNCTION

語句創(chuàng)建。在創(chuàng)建時需要注意：函數(shù)名在數(shù)據(jù)庫中必須唯一，其可以有參數(shù)，也可以沒有參數(shù)，其參數(shù)只能是輸入?yún)?shù)，最多可以有1024參數(shù)。1.使用CREATEFUNCTION語句創(chuàng)建用戶自定義函數(shù)使用CREATEFUNCTION創(chuàng)建用戶自定義函數(shù)，其語法格式如下：

CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])

RETURNSscalar_return_data_type[AS]

BEGINfunction_body

RETURNscalar_expression

ENDfunction_name：指用戶自定義函數(shù)的名稱。其名稱必須符合標識符的命名規(guī)則，并且對其所有者來說，該名稱在數(shù)據(jù)庫中必須唯一。

@parameter_name：用戶自定義函數(shù)的參數(shù)，其可以是一個或多個。每個函數(shù)的參數(shù)僅用于該函數(shù)本身；相同的參數(shù)名稱可以用在其它函數(shù)中。參數(shù)只能代替常量；而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。函數(shù)執(zhí)行時每個已聲明參數(shù)的值必須由用戶指定，除非該參數(shù)的默認值已經(jīng)定義。如果函數(shù)的參數(shù)有默認值，在調用該函數(shù)時必須指定"default"關鍵字才能獲得默認值。scalar_parameter_data_type：參數(shù)的數(shù)據(jù)類型。scalar_return_data_type：是用戶定義函數(shù)的返回值?？梢允荢QLServer支持的任何標量數(shù)據(jù)類型（text、ntext、image和timestamp除外）。function_body：位于begin和end之間的一系列Transact-SQL語句，其只用于標量函數(shù)和多語句表值函數(shù)。scalar_expression：用戶自定義函數(shù)中返回值的表達式。2.表值函數(shù)表值函數(shù)遵循的原則：RETURNS子句僅包含關鍵字table。不必定義返回變量的格式，因為它由RETURN子句中的SELECT語句的結果集決定。function_body不由BEGIN和END分隔。RETURN子句在括號中包含單個SELECT語句。SELECT語句的結果集構成函數(shù)所返回的表。

例：在stuDB庫中創(chuàng)建一個內嵌表值函數(shù)XUESHENG，該函數(shù)可以根據(jù)輸入的系部代碼返回該系學生的基本信息。其代碼如下：CREATEFUNCTIONXUESHENG(@inputdepnvarchar(4))RETURNS

table

AS

RETURN

(SELECT學號,姓名FROM學生WHERE所屬院系=@inputdep)GO建立好該內嵌表值函數(shù)后，就可以象使用表或視圖一樣來使用它：SELECT*FROMDBO.XUESHENG(‘100')GO3.多語句表值函數(shù)多語句函數(shù)的主體中允許使用以下語句：賦值語句DECLARE語句，該語句定義函數(shù)局部的數(shù)據(jù)變量和游標。SELECT語句，該語句包含帶有表達式的選擇列表，其中的表達式將值賦予函數(shù)的局部變量。游標操作，該操作引用在函數(shù)中聲明、打開、關閉和釋放的局部游標。只允許使用以INTO子句向局部變量賦值的FETCH語句；不允許使用將數(shù)據(jù)返回到客戶端的FETCH語句。INSERT、UPDATE和DELETE語句，這些語句修改函數(shù)的局部table變量。EXECUTE語句調用擴展存儲過程。3.多語句表值函數(shù)案例：在stuDB庫中創(chuàng)建一個多語句表值函數(shù)chengji，該函數(shù)可以根據(jù)輸入的課程名稱返回選修該課程的學生姓名和成績。代碼如下：USEstuDBGOCREATEFUNCTIONchengji(@inputkmaschar(20))/*為chengji函數(shù)定義的表結構，名稱變量為@cj*/RETURNS@cjiTABLE(科目編號varchar(10),

姓名varchar(10),

成績int)ASBEGININSERT@cj/*該變量是上面定義的表名稱變量*/

selectb.科目編號,a.學生姓名,(b.筆試成績+b.上機成績)/2

from學生信息表asainnerjoin成績表asb

ona.學號=b.學號whereb.科目編號=@inputkm

RETURNENDGO定義返回表的結構在查詢編輯器中輸入以下查詢命令：SELECT*FROMdbo.chengji(‘9001’)查看、修改和刪除自定義函數(shù)1.查看用戶自定義函數(shù)的屬性在SQLServer中，根據(jù)不同需要，可以使用sp_helptext、sp_help等系統(tǒng)存儲過程來查看用戶自定義函數(shù)的不同信息。每個系統(tǒng)存儲過程的具體作用和語法如下：使用sp_helptext查看用戶定義函數(shù)的文本信息，其語法格式為：

sp_helptext用戶自定義函數(shù)名使用sp_help查看用戶自定義函數(shù)的一般信息，其語法格式為：

sp_help用戶自定義函數(shù)名例：使用有關系統(tǒng)過程查看STUDENT數(shù)據(jù)庫中名為XUEFEN的用戶自定義函數(shù)的文本信息。其程序代碼如下：

SP_HELPTEXTXUEFENGO查看、修改和刪除自定義函數(shù)2.修改用戶自定義函數(shù)的屬性使用SQL命令修改用戶自定義函數(shù)，使用ALTERFUNCTION

命令可以修改用戶自定義函數(shù)。修改由CREATEFUNCTION語句創(chuàng)建的現(xiàn)有用戶定義函數(shù)，不會更改權限，也不影響相關的函數(shù)、存儲過程或觸發(fā)器。其語法格式如下：ALTERFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[AS]BEGINfunction_bodyRETURNscalar_expressionEND其中的參數(shù)與建立用戶自定義函數(shù)中的參數(shù)意義相同。查看、修改和刪除自定義函數(shù)3.使用T-SQL命令刪除用戶自定義函數(shù)使用DROP命令可以一次刪除多個用戶自定義函數(shù)，其語法格式為：DROPFUNCTION[所有者名稱.]函數(shù)名稱[,…n]案例：刪除在student庫上建立的xuefen函數(shù)。代碼如下：

DROPFUNCTIONdbo.xuefenGO邏輯控制語句IF－ELSE語句SQL中的IF-ELSE語句IF

（條件）

BEGIN

語句1

語句2……

ENDELSE

BEGIN

語句1；語句2；

……

ENDELSE是可選部分如果有多條語句，才需要BEGIN-END語句塊

IF-ELSE示例問題：統(tǒng)計并顯示本班筆試平均分，如果平均分在70以上，顯示“成績優(yōu)秀“，并顯示前三名學員的考試信息；如果在70以下，顯示“本班成績較差“，并顯示后三名學員的考試信息。學員成績表分析：第一步，統(tǒng)計平均成績存入臨時變量；第二步，用IF－ELSE判斷；IF-ELSE示例設置輸出結果的格式為了文本消息和輸出結果顯示在同一窗口，需要設置輸出結果的格式邏輯控制語句WHILE循環(huán)語句SQL中的WHILE語句WHILE

（條件）

BEGIN

語句1

語句2……

BREAK

ENDBREAK表示退出循環(huán)如果有多條語句，才需要BEGIN-END語句塊

WHILE示例問題：

本次考試成績較差，假定要提分，確保每人筆試都通過。提分規(guī)則很簡單，先每人都加2分，看是否都通過，如果沒有全部通過，每人再加2分，再看是否都通過，如此反復提分，直到所有人都通過為止。學員成績表分析：

第一步，統(tǒng)計沒通過的人數(shù)；

第二步，如果有人沒通過，加分；

第三步，循環(huán)判斷。DECLARE@nintWHILE(1=1)--條件永遠成立

BEGINSELECT@n=COUNT(*)FROM成績表

WHEREwrittenExam<60

--統(tǒng)計不及格人數(shù)IF(@n>0)UPDATE成績表--每人加2分

SET

writtenExam=writtenExam+2

ELSEBREAK

--退出循環(huán)ENDprint'加分后的成績如下：'SELECT*FROM成績表WHILE示例參考語句邏輯控制語句CASE－END多分支語句CASE

WHEN

條件1THEN

結果1

WHEN

條件2THEN

結果2……

ELSE

其他結果ENDCASE-END示例問題：采用美國的ABCDE五級打分制來顯示筆試成績。

A級:90分以上

B級：80－89分

C級:70－79分

D級：60－69分

E級：60分以下學員成績表print

'ABCDE五級顯示成績如下：'SELECT學號,

成績=CASE

WHENwrittenExam<60THEN'E'

WHENwrittenExamBETWEEN60AND69THEN'D'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExamBETWEEN80AND89THEN'B'

ElSE

'A'

END

FROM成績表CASE－END示例參考語句CASE-END練習練習：

請根據(jù)平均分和下面的評分規(guī)則，編寫T-SQL語句查詢學員的成績，如上圖所示。優(yōu)：90分以上良：80－89分中：70－79分差：60－69分不及格：60分以下學員成績分析CASE-END練習答案USEstuDBGOselecta.學生姓名,a.學號,b.筆試成績,b.上機成績,總成績=(b.筆試成績+b.上機成績)/2,等級=casewhen(b.筆試成績+b.上機成績)/2<60then'不及格'when(b.筆試成績+b.上機成績)/2between60and69then'及格'when(b.筆試成績+b.上機成績)/2between70and79then'中'when(b.筆試成績+b.上機成績)/2between80and89then'良'else'優(yōu)'endfrom學生信息表asa,成績表asbwherea.學號=b.學號批處理語句批處理是包含一個或多個SQL語句的組，從應用程序一次性地發(fā)送到SQLServer執(zhí)行SQLServer將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條客戶端應用程序SQLServer服務器批處理語句：語句1語句2……GO批處理語句示例SELECT*FROMstuInfoSELECT*FROMstuMarksUPDATEstuMarksSETwrittenExam=writtenExam+2GOGO是批處理的標志，表示SQLServer將這些T-SQL語句編譯為一個執(zhí)行單元，提高執(zhí)行效率