1、第十章,T-SQL編程,目標,掌握如何定義變量并賦值 掌握如何輸出顯示數(shù)據(jù) 掌握IF、WHILE、CASE邏輯控制語句 理解SQL中批處理的概念,使用變量,變量分為： 局部變量： 局部變量必須以標記作為前綴 ，如age 局部變量的使用也是先聲明，再賦值 全局變量： 全局變量必須以標記 作為前綴，如version 全局變量由系統(tǒng)定義和維護，我們只能讀取，不能修改全局變量的值,局部變量,例如： DECLARE name varchar(8) DECLARE seat int,聲明局部變量 DECLARE 變量名 數(shù)據(jù)類型,賦值 SET 變量名 =值 SELECT 變量名 = 值,例如： SET n

2、ame=張三 SELECT name=stuName FROM stuInfo WHERE stuNo=s25302,必須確保篩選出的記錄只有1條,或,局部變量示例,問題： 編寫T-SQL查找李文才的左右同桌 ？,學員信息表,分析： 第一步，找出“李文才“的座位號； 第二步，李文才的座位號加1或減1,局部變量示例,/*-查找李文才的信息-*/ DECLARE name varchar(8) -學員姓名 SET name=李文才 -使用SET賦值 SELECT * FROM stuInfo WHERE stuName = name /*-查找李文才的左右同桌-*/ DECLARE seat in

3、t -座位號 SELECT seat=stuSeat FROM stuInfo -使用SELECT賦值 WHERE stuName=name SELECT * FROM stuInfo WHERE (stuSeat = seat+1) OR (stuSeat = seat-1) GO,演示：使用局部變量,參考語句,全局變量,全局變量都使用兩個標志作為前綴,全局變量示例,print SQL Server的版本+VERSION print 服務器的名稱: +SERVERNAME INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(武松,s

4、25328,男,23) -如果大于0表示上一條語句執(zhí)行有錯誤 print 當前錯誤號+convert(varchar(5),ERROR) print 剛才報名的學員，座位號為: +convert(varchar(5),IDENTITY ) UPDATE stuinfo SET stuAge=85 WHERE stuName=李文才 print 當前錯誤號+convert(varchar(5),ERROR) GO,演示：使用全局變量,參考語句,SQL Server的版本,服務器名稱,座位號（自動編號）,錯誤號,錯誤號,邏輯控制語句,T-SQL語言提供了一些可用于改變語句執(zhí)行順序的命令,稱為流程控

5、制語句. 流程控制語句與常見的程序設計語言類似,主要包括以下幾種:,beginend ifelse case while . continue.break goto,邏輯控制語句,IFELSE語句,SQL中的IF-ELSE語句 IF （條件） BEGIN 語句1 語句2 END ELSE BEGIN 語句1； 語句2； END,ELSE是可選部分 如果有多條語句，才需要BEGIN-END語句塊,IF-ELSE示例,問題： 統(tǒng)計并顯示本班筆試平均分，如果平均分在70以上，顯示“成績優(yōu)秀“，并顯示前三名學員的考試信息；如果在70以下，顯示“本班成績較差“，并顯示后三名學員的考試信息 。,學員成績表

6、,分析： 第一步，統(tǒng)計平均成績存入臨時變量； 第二步，用IFELSE判斷；,IF-ELSE示例,設置輸出結(jié)果的格式,為了文本消息和輸出結(jié)果顯示在同一窗口，需要設置輸出結(jié)果的格式,邏輯控制語句,WHILE循環(huán)語句,SQL中的WHILE語句 WHILE （條件） BEGIN 語句1 語句2 BREAK END,BREAK表示退出循環(huán) 如果有多條語句，才需要BEGIN-END語句塊,WHILE示例,問題：本次考試成績較差，假定要提分，確保每人筆試都通過。提分規(guī)則很簡單，先每人都加2分，看是否都通過，如果沒有全部通過，每人再加2分，再看是否都通過，如此反復提分，直到所有人都通過為止 。,學員成績表,分

7、析：第一步，統(tǒng)計沒通過的人數(shù) ；第二步，如果有人沒通過，加分；第三步，循環(huán)判斷。,DECLARE n int WHILE(1=1) -條件永遠成立 BEGIN SELECT n=COUNT(*) FROM stuMarks WHERE writtenExam0) UPDATE stuMarks -每人加2分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循環(huán) END print 加分后的成績?nèi)缦拢?SELECT * FROM stuMarks,WHILE示例,參考語句,邏輯控制語句,CASEEND多分支語句,CASE WHEN 條件1 THEN 結(jié)果1

8、 WHEN 條件2 THEN 結(jié)果2 ELSE 其他結(jié)果 END,CASE-END示例,問題： 采用美國的ABCDE五級打分制來顯示筆試成績。 A級: 90分以上 B級： 8089分 C級: 7079分 D級： 6069分 E級： 60分以下,學員成績表,print ABCDE五級顯示成績?nèi)缦拢?SELECT stuNo, 成績= CASE WHEN writtenExam60 THEN E WHEN writtenExam BETWEEN 60 AND 69 THEN D WHEN writtenExam BETWEEN 70 AND 79 THEN C WHEN writtenExam B

9、ETWEEN 80 AND 89 THEN B ElSE A END FROM stuMarks,CASEEND示例,參考語句,CASE-END課堂練習,課堂練習：請根據(jù)平均分和下面的評分規(guī)則，編寫T-SQL語句查詢學員的成 績，如上圖所示。 優(yōu) ：90分以上 良 ：8089分 中 ：7079分 差 ：6069分 不及格 ：60分以下,學員成績分析,CASE-END練習答案,USE stuDB GO SELECT 考號=ExamNo,學號=stuNo,筆試=writtenExam,機試=labExam, 平均分=(writtenExam+labExam)/2, 等級=CASE WHEN (wr

10、ittenExam+labExam)/260 THEN 不及格 WHEN (writtenExam+labExam)/2 BETWEEN 60 AND 69 THEN 差 WHEN (writtenExam+labExam)/2 BETWEEN 70 AND 79 THEN 中 WHEN (writtenExam+labExam)/2 BETWEEN 80 AND 89 THEN 良 ElSE 優(yōu) END FROM stuMarks,批處理語句,批處理是包含一個或多個 SQL 語句的組，從應用程序一次性地發(fā)送到SQL Server執(zhí)行 SQL Server 將批處理語句編譯成一個可執(zhí)行單元，此

11、單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條,客戶端應用程序,SQL Server服務器,批處理語句： 語句1 語句2 GO,批處理語句示例,SELECT * FROM stuInfo SELECT * FROM stuMarks UPDATE stuMarks SET writtenExam=writtenExam+2 GO,GO是批處理的標志，表示SQL Server將這些T-SQL語句編譯為一個執(zhí)行單元，提高執(zhí)行效率 一般是將一些邏輯相關的業(yè)務操作語句，放置在同一批中，這完全由業(yè)務需求和代碼編寫者決定,批處理語句示例,-例如建表語句的末尾必須添加GO CREATE TABLE stuI

12、nfo ( . ) GO,SQLServer規(guī)定：如果是建庫、建表語句、以及我們后面學習的存儲過程和視圖等，則必須在語句末尾添加 GO 批處理標志,課堂綜合練習,課堂練習： 則根據(jù)如下規(guī)則對機試成績進行反復加分，直到平均分超過85分為止。請編寫T-SQL語句實現(xiàn)。 90分以上： 不加分 8089分： 加1分 7079分： 加2分 6069分： 加3分 60分以下： 加5分,加分前,加分后,課堂綜合練習答案,SELECT * FROM stuMarks -原始成績 DECLARE labAvg INT WHILE(1=1) BEGIN UPDATE stuMarks SET labExam= C

13、ASE WHEN labExam=85 BREAK END SELECT * FROM stuMarks -加分后的成績,聲明變量，用戶臨時存放平均分,循環(huán)加分,根據(jù)機試成績酌情加分，整個是一個UPDATE語句,獲取目前的平均分，判斷是否還繼續(xù)加分,T-SQL語句的綜合應用,學員信息表和成績表,應到人數(shù)：5人,實到人數(shù)4人，缺考1人,T-SQL語句的綜合應用,如何實現(xiàn)？,本次考試的缺考情況,比較筆試平均分和機試平均分，較低者進行循環(huán)提分，但提分后最高分不能超過97分 。加分后重新統(tǒng)計通過情況,統(tǒng)計通過率,T-SQL語句的綜合應用,1.提示: 使用子查詢統(tǒng)計缺考情況： 應到人數(shù)：SELECT c

14、ount(*) FROM stuInfo 實到人數(shù)：SELECT count(*) FROM stuMarks,2.提取學員的成績信息并保存結(jié)果，包括學員姓名、學號、筆試成績、機試成績、是否通過 1)提取的成績信息包含兩表的數(shù)據(jù)，所以考慮兩表連接，使用左連接（ LEFT JOIN ）； SELECT stuNameFROM stuInfo LEFT JOIN stuMarks 2)要求新加一列“是否通過（isPass）”，可采用CASE END。為了便于后續(xù)的通過率統(tǒng)計，通過則為1，沒通過為0 SELECT isPass=CASE WHEN writtenExam=60 THEN 1 ELSE

15、 0 END 3)要求保存提?。ú樵儯┑慕Y(jié)果，可以使用我們曾學習過的SELECT INTO newTable語句，生成新表并保存數(shù)據(jù),T-SQL語句的綜合應用,3.比較筆試平均分和機試平均分，對較低者進行循環(huán)提分，但提分后最高分不能超過97分： 1) 使用IF語句判斷筆試還是機試偏低，決定對筆試還是機試提分； 2) 使用WHILE循環(huán)給每個學員加分，缺考的除外，當最高分超過97分時退出循環(huán)； 3)因為給每位學員的筆試或機試提分了，有的學員可能提分后剛好通過了，所以需要更新isPass（是否通過）列。 UPDATE newTable SET isPass=CASE WHEN writtenExa

16、m=60 and labExam=60 THEN 1 ELSE 0 END,T-SQL語句的綜合應用,4.提分后，統(tǒng)計學員的成績和通過情況： 1）使用別名實現(xiàn)中文字段名，即SELECT 姓名=stuName,學號=stuNo 2）如果某個學員的成績?yōu)镹ULL(空)，則替換為”缺考”，否則原樣顯示； 3）isPass列中的1替換為是，0替換為否； SELECT ,機試成績=CASE WHEN labExam IS NULL THEN 缺考 ELSE convert(varchar(5),labExam) END ,是否通過=CASE WHEN isPass=1 THEN 是 ELSE 否 END

17、,T-SQL語句的綜合應用,5.提分后統(tǒng)計學員的通過率情況： 1）通過人數(shù)：因為通過用1表示，沒通過用0表示，所以isPass列的累加和即是通過人數(shù)； 2）通過率：同理，isPass列的平均值*100即是通過率；,T-SQL參考語句,/*-本次考試的原始數(shù)據(jù)-*/ -SELECT * FROM stuInfo -SELECT * FROM stuMarks /*-統(tǒng)計考試缺考情況-*/ SELECT 應到人數(shù)=(SELECT count(*) FROM stuInfo) , -應到人數(shù)為子查詢表達式的別名 實到人數(shù)=(SELECT count(*) FROM stuMarks) , 缺考人數(shù)=

18、(SELECT count(*) FROM stuInfo)-(SELECT count(*) FROM stuMarks),T-SQL參考語句,/*-統(tǒng)計考試通過情況，并將結(jié)果存放在新表newTable中-*/ IF EXISTS(SELECT * FROM sysobjects WHERE name=newTable) DROP TABLE newTable SELECT stuName,stuInfo.stuNo,writtenExam ,labExam , isPass=CASE WHEN writtenExam=60 and labExam=60 THEN 1 ELSE 0 END

19、INTO newTable FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo -SELECT * FROM newTable -查看統(tǒng)計結(jié)果，可用于調(diào)試,T-SQL參考語句,/*-酌情加分：比較筆試和機試平均分，決定加哪門-*/ DECLARE avgWritten numeric(4,1) DECLARE avgLab numeric(4,1) SELECT avgWritten=AVG(writtenExam) FROM newTable WHERE writtenExam IS NOT NULL SELECT avgLab=AVG(labExam)FROM newTable WHERE labExam IS NOT NULL IF avgWritten=97 BREAK END ELSE 略 -循環(huán)給筆試加分，最高分不能超過97分,T-SQL參考語句,-因為提分，所以需要更