數(shù)據(jù)庫編程教程(jiàochéng)第一頁，共37頁。目標(mùbiāo)掌握如何(rúhé)定義變量并賦值掌握如何(rúhé)輸出顯示數(shù)據(jù)掌握IF、WHILE、CASE邏輯控制語句理解SQL中批處理的概念第二頁，共37頁。使用(shǐyòng)變量變量分為：局部變量：局部變量必須以標記@作為前綴，如@age局部變量的使用(shǐyòng)也是先聲明，再賦值全局變量：全局變量必須以標記@@作為前綴，如@@version全局變量由系統(tǒng)定義和維護，我們只能讀取，不能修改全局變量的值第三頁，共37頁。局部變量例如(lìrú)：DECLARE@namevarchar(8)DECLARE@seatint聲明(shēngmíng)局部變量DECLARE@變量名數(shù)據(jù)類型賦值

SET@變量名=值

SELECT@變量名=值例如(lìrú)：SET@name=‘張三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘s25302’

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

第四頁，共37頁。局部變量例如(lìrú)問題：編寫T-SQL查找(cházhǎo)李文才的左右同桌？學員(xuéyuán)信息表分析：第一步，找出“李文才“的座位號；第二步，李文才的座位號加1或減1第五頁，共37頁。局部變量例如(lìrú)/*--查找李文才的信息--*/DECLARE@namevarchar(8)--學員(xuéyuán)姓名SET@name='李文才'--使用SET賦值SELECT*FROMstuInfoWHEREstuName=@name/*--查找李文才的左右同桌--*/DECLARE@seatint--座位號SELECT@seat=stuSeatFROMstuInfo--使用SELECT賦值WHEREstuName=@nameSELECT*FROMstuInfoWHERE(stuSeat=@seat+1)OR(stuSeat=@seat-1)GO演示(yǎnshì)：使用局部變量參考語句第六頁，共37頁。全局變量全局變量都使用(shǐyòng)兩個@標志作為前綴

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

SQLServer的版本信息第七頁，共37頁。全局變量例如(lìrú)print'SQLServer的版本'+@@VERSIONprint'效勞器的名稱:'+@@SERVERNAMEINSERTINTOstuInfo(stuName,stuNo,stuSex,stuAge)VALUES('武松','s25328','男','23')--如果大于0表示(biǎoshì)上一條語句執(zhí)行有錯誤print'當前錯誤號'+convert(varchar(5),@@ERROR)print'剛剛報名的學員，座位號為:'+convert(varchar(5),@@IDENTITY)UPDATEstuinfoSETstuAge=85WHEREstuName='李文才'print'當前錯誤號'+convert(varchar(5),@@ERROR)GO演示(yǎnshì)：使用全局變量參考語句SQLServer的版本效勞器名稱座位號〔自動編號〕錯誤號錯誤號第八頁，共37頁。邏輯(luójí)控制語句T-SQL語言提供了一些可用于改變語句執(zhí)行(zhíxíng)順序的命令,稱為流程控制語句.流程控制語句與常見的程序設計語言類似,主要包括以下幾種:begin…end…if…elsecasewhile...continue...breakgoto第九頁，共37頁。邏輯控制(kòngzhì)語句IF－ELSE語句(yǔjù)SQL中的IF-ELSE語句(yǔjù)IF〔條件〕BEGIN語句(yǔjù)1語句(yǔjù)2……ENDELSEBEGIN語句(yǔjù)1；語句(yǔjù)2；……ENDELSE是可選局部如果有多條語句，才需要BEGIN-END語句塊第十頁，共37頁。IF-ELSE例如(lìrú)問題：統(tǒng)計并顯示本班筆試平均分，如果平均分在70以上，顯示“成績優(yōu)秀“，并顯示前三名學員的考試信息(xìnxī)；如果在70以下，顯示“本班成績較差“，并顯示后三名學員的考試信息(xìnxī)。學員(xuéyuán)成績表分析：第一步，統(tǒng)計平均成績存入臨時變量；第二步，用IF－ELSE判斷；第十一頁，共37頁。IF-ELSE例如(lìrú)第十二頁，共37頁。設置(shèzhì)輸出結(jié)果的格式為了文本消息和輸出結(jié)果顯示在同一窗口，需要(xūyào)設置輸出結(jié)果的格式第十三頁，共37頁。邏輯控制(kòngzhì)語句WHILE循環(huán)(xúnhuán)語句SQL中的WHILE語句(yǔjù)WHILE〔條件〕BEGIN語句(yǔjù)1語句(yǔjù)2……BREAKENDBREAK表示退出循環(huán)如果有多條語句，才需要BEGIN-END語句塊第十四頁，共37頁。SELECT*FROMstuMarks應到人數(shù)：SELECTcount(*)FROMstuInfo+convert(varchar(5),@@IDENTITY)SELECT*FROMstuMarksprint'當前錯誤號'+convert(varchar(5),@@ERROR)差：60－69分語句(yǔjù)1CASE－END例如(lìrú)SETlabExam=邏輯(luójí)控制語句A級:90分以上提分后統(tǒng)計學員(xuéyuán)的通過率情況：SELECT*FROMstuInfoWHILE例如(lìrú)WHENwrittenExamBETWEEN80AND89THEN'B'第二步，如果有人沒通過，加分；WHILE例如(lìrú)問題：

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

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

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

第三步，循環(huán)判斷。第十五頁，共37頁。DECLARE@nintWHILE(1=1)--條件永遠成立(chénglì)BEGINSELECT@n=COUNT(*)FROMstuMarksWHEREwrittenExam<60--統(tǒng)計不及格人數(shù)IF(@n>0)UPDATEstuMarks--每人加2分SETwrittenExam=writtenExam+2ELSEBREAK--退出循環(huán)ENDprint'加分后的成績?nèi)缦拢?SELECT*FROMstuMarksWHILE例如(lìrú)參考(cānkǎo)語句第十六頁，共37頁。邏輯控制(kòngzhì)語句CASE－END多分支(fēnzhī)語句CASEWHEN條件1THEN結(jié)果(jiēguǒ)1WHEN條件2THEN結(jié)果(jiēguǒ)2……ELSE其他結(jié)果(jiēguǒ)END第十七頁，共37頁。CASE-END例如(lìrú)問題：采用美國的ABCDE五級打分制來顯示(xiǎnshì)筆試成績。A級:90分以上B級：80－89分C級:70－79分D級：60－69分E級：60分以下學員(xuéyuán)成績表第十八頁，共37頁。print'ABCDE五級顯示成績(chéngjì)如下：'SELECTstuNo,成績(chéngjì)=CASEWHENwrittenExam<60THEN'E'WHENwrittenExamBETWEEN60AND69THEN'D'WHENwrittenExamBETWEEN70AND79THEN'C'WHENwrittenExamBETWEEN80AND89THEN'B'ElSE'A'ENDFROMstuMarksCASE－END例如(lìrú)參考(cānkǎo)語句第十九頁，共37頁。CASE-END課堂練習課堂練習：

請根據(jù)平均分和下面的評分規(guī)那么，編寫T-SQL語句(yǔjù)查詢學員的成績，如上圖所示。優(yōu)：90分以上良：80－89分中：70－79分差：60－69分不及格：60分以下學員成績(chéngjì)分析第二十頁，共37頁。CASE-END練習(liànxí)答案USEstuDBGOSELECT考號=ExamNo,學號=stuNo,筆試(bǐshì)=writtenExam,機試=labExam,平均分=(writtenExam+labExam)/2,等級=CASEWHEN(writtenExam+labExam)/2<60THEN'不及格'WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN'差'WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN'中'WHEN(writtenExam+labExam)/2BETWEEN80AND89THEN'良'ElSE'優(yōu)'ENDFROMstuMarks第二十一頁，共37頁。批處理語句(yǔjù)批處理是包含一個或多個SQL語句的組，從應用程序一次性地發(fā)送到SQLServer執(zhí)行(zhíxíng)SQLServer將批處理語句編譯成一個可執(zhí)行(zhíxíng)單元，此單元稱為執(zhí)行(zhíxíng)方案。執(zhí)行(zhíxíng)方案中的語句每次執(zhí)行(zhíxíng)一條客戶端應用程序SQLServer效勞(xiàoláo)器批處理語句：語句1語句2……GO第二十二頁，共37頁。批處理語句(yǔjù)例如SELECT*FROMstuInfoSELECT*FROMstuMarksUPDATEstuMarksSETwrittenExam=writtenExam+2GOGO是批處理的標志，表示SQLServer將這些T-SQL語句編譯為一個執(zhí)行單元，提高執(zhí)行效率一般是將一些邏輯相關的業(yè)務操作語句，放置在同一批中，這完全(wánquán)由業(yè)務需求和代碼編寫者決定第二十三頁，共37頁。批處理語句(yǔjù)例如--例如建表語句(yǔjù)的末尾必須添加GOCREATETABLEstuInfo(....)GOSQLServer規(guī)定：如果是建庫、建表語句、以及我們后面學習的存儲過程和視圖等，那么(nàme)必須在語句末尾添加GO批處理標志第二十四頁，共37頁。課堂(kètáng)綜合練習課堂練習：那么根據(jù)(gēnjù)如下規(guī)那么對機試成績進行反復加分，直到平均分超過85分為止。請編寫T-SQL語句實現(xiàn)。90分以上：不加分80－89分：加1分70－79分：加2分60－69分：加3分60分以下：加5分加分前加分后第二十五頁，共37頁。課堂綜合練習(liànxí)答案SELECT*FROMstuMarks--原始(yuánshǐ)成績DECLARE@labAvgINTWHILE(1=1)BEGINUPDATEstuMarksSETlabExam=CASEWHENlabExam<60THENlabExam+5WHENlabExambetween60AND69THENlabExam+3WHENlabExambetween70AND79THENlabExam+2WHENlabExambetween80AND89THENlabExam+1ELSElabExamENDSELECT@labAvg=AVG(labExam)FROMstuMarksIF@labAvg>=85BREAKENDSELECT*FROMstuMarks--加分后的成績聲明變量，用戶臨時(línshí)存放平均分循環(huán)加分根據(jù)機試成績酌情加分，整個是一個UPDATE語句獲取目前的平均分，判斷是否還繼續(xù)加分第二十六頁，共37頁。T-SQL語句(yǔjù)的綜合應用學員(xuéyuán)信息表和成績表應到人數(shù)：5人實到人數(shù)4人，缺考1人第二十七頁，共37頁。T-SQL語句的綜合(zōnghé)應用如何(rúhé)實現(xiàn)？本次考試(kǎoshì)的缺考情況比較筆試平均分和機試平均分，較低者進行循環(huán)提分，但提分后最高分不能超過97分。加分后重新統(tǒng)計通過情況統(tǒng)計通過率第二十八頁，共37頁。T-SQL語句的綜合(zōnghé)應用1.提示(tíshì):使用子查詢統(tǒng)計缺考情況：應到人數(shù)：SELECTcount(*)FROMstuInfo實到人數(shù)：SELECTcount(*)FROMstuMarks2.提取學員的成績信息并保存結(jié)果，包括學員姓名、學號、筆試成績、機試成績、是否通過1)提取的成績信息包含兩表的數(shù)據(jù)，所以考慮兩表連接，使用左連接〔LEFTJOIN〕；SELECTstuName…FROMstuInfoLEFTJOINstuMarks…2)要求新加一列“是否通過〔isPass〕〞，可采用(cǎiyòng)CASE…END。為了便于后續(xù)的通過率統(tǒng)計，通過那么為1，沒通過為0SELECT…isPass=CASEWHENwrittenExam>=60……THEN1ELSE0END……3)要求保存提取〔查詢〕的結(jié)果，可以使用我們曾學習過的SELECT…INTOnewTable語句，生成新表并保存數(shù)據(jù)第二十九頁，共37頁。T-SQL語句(yǔjù)的綜合應用3.比較筆試平均分和機試平均分，對較低者進行循環(huán)提分，但提分后最高分不能超過97分：1)使用IF語句判斷筆試還是機試偏低，決定對筆試還是機試提分；2)使用WHILE循環(huán)給每個學員加分，缺考的除外，當最高分超過97分時退出循環(huán)；3)因為(yīnwèi)給每位學員的筆試或機試提分了，有的學員可能提分后剛好通過了，所以需要更新isPass〔是否通過〕列。UPDATEnewTableSETisPass=CASEWHENwrittenExam>=60andlabExam>=60THEN1ELSE0END第三十頁，共37頁。T-SQL語句(yǔjù)的綜合應用4.提分后，統(tǒng)計學員(xuéyuán)的成績和通過情況：1〕使用別名實現(xiàn)中文字段名，即SELECT姓名=stuName,學號=stuNo…2〕如果某個學員(xuéyuán)的成績?yōu)镹ULL(空)，那么替換為〞缺考〞，否那么原樣顯示；3〕isPass列中的1替換為是，0替換為否；SELECT……,機試成績=CASEWHENlabExamISNULLTHEN'缺考'ELSEconvert(varchar(5),labExam)END,是否通過=CASEWHENisPass=1THEN'是'ELSE'否'END……第三十一頁，共37頁。T-SQL語句(yǔjù)的綜合應用5.提分后統(tǒng)計學員(xuéyuán)的通過率情況：1〕通過人數(shù)：因為通過用1表示，沒通過用0表示，所以isPass列的累加和即是通過人數(shù)；2〕通過率：同理，isPass列的平均值*100即是通過率；第三十二頁，共37頁。T-SQL參考(cānkǎo)語句/*--本次考試(kǎoshì)的原始數(shù)據(jù)--*/--SELECT*FROMstuInfo--SELECT*FROMstuMarks/*--------------統(tǒng)計考試(kǎoshì)缺考情況----------------------*/SELECT應到人數(shù)=(SELECTcount(*)FROMstuInfo),--應到人數(shù)為子查詢表達式的別名實到人數(shù)=(SELECTcount(*)FROMstuMarks),缺考人數(shù)=((SELECTcount(*)FROMstuInfo)-(SELECTcount(*)FROMstuMarks))第三十三頁，共37頁。T-SQL參考(cānkǎo)語句/*----統(tǒng)計考試通過(tōngguò)情況，并將結(jié)果存放在新表newTable中---*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='newTable')DROPTABLEnewTableSELECTstuName,stuInfo.stuNo,writtenExam,labExam,isPass=CASEWHENwrittenExam>=60andlabExam>=60THEN1ELSE0ENDINTOnewTableFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo--SELECT*FROMnewTable--查看統(tǒng)計結(jié)果，可用于調(diào)試第三十四頁，共37頁。T-SQL參考(cānkǎo)語句/*-酌情加分：比較筆試(bǐshì)和機試平均分，決定加哪門---*/DECLARE@avgWrittennumeric(4,1)DECLARE@avgLabnumeric(4,1)SELECT@avgWritten=AVG(writtenExam)FROMnewTableWHEREwrittenExamISNOTNULLSELECT@avgLab=AVG(labExam)FROMnewTableWHERElabExamISNOTNULLIF@avgWritten<@avgLabWHILE(1=1)--循環(huán)給筆試(bǐshì)加分，最高分不能超過97分BEGINUPDATEnewTabl