單元4高級查詢-5

1、高級查詢高級查詢單元單元4-54-5回顧與作業(yè)點評2-1指出下列語句的錯誤：指出下列語句的錯誤：CREATE TABLE bank( userName VARCHAR(10), balance MONEY) INSERT INTO bank(userName,balance) VALUES(張三張三,500)INSERT INTO bank(userName,balance) VALUES(李四李四,700)DECLARE mymoney INT(4)mymoney=0SELECT mymoney=balance FROM bankDECLARE mymoney INTSET mymoney=

2、0SELECT mymoney=balance FROM bank WHERE userName=張三張三 CREATE TABLE bank( userName VARCHAR(10), balance MONEY) INSERT INTO bank(userName,balance) VALUES(張三張三,500)INSERT INTO bank(userName,balance) VALUES(李四李四,700)DECLARE mymoney INTSET mymoney=0SELECT mymoney=balance FROM bank WHERE userName= =張三張三 回

3、顧與作業(yè)點評2-2IF mymoney100 print 卡上目前余額不足卡上目前余額不足100，請及時充值，請及時充值! print 卡上余額為：卡上余額為：+mymoneyprint 您的年利息為您的年利息為:SELECT 利息利息=CASE WHEN balance 1000 THEN balance*0.20 WHEN ELSE balance*0.10 FROM bank WHERE userName=張三張三GO多條語句添加多條語句添加BEGIN-END去掉去掉WHEN缺少配對的缺少配對的END轉(zhuǎn)換轉(zhuǎn)換:Convert(varchar(10), mymoney)指出下列語句的錯誤：

4、指出下列語句的錯誤：IF mymoney100 BEGIN print 卡上目前余額不足卡上目前余額不足100，請及時充值，請及時充值! print 卡上余額為：卡上余額為：+Convert(varchar(10),mymoney) ENDprint 您的年利息為您的年利息為:SELECT 利息利息=CASE WHEN balance 1000 THEN balance*0.20 ELSE balance*0.10 END FROM bank WHERE userName=張三張三GO本章任務(wù)查詢年齡比查詢年齡比“李斯文李斯文” 大的學(xué)生大的學(xué)生查詢未參加查詢未參加“Java”課程最近一次考試

5、的學(xué)生名課程最近一次考試的學(xué)生名單單檢查是否有一年級的學(xué)生。如果有，將他在讀檢查是否有一年級的學(xué)生。如果有，將他在讀年級更新為二年級年級更新為二年級統(tǒng)計考試缺考情況統(tǒng)計考試缺考情況制作學(xué)生成績單制作學(xué)生成績單本章目標(biāo)掌握簡單子查詢的用法掌握簡單子查詢的用法掌握掌握IN子查詢的用法子查詢的用法掌握掌握EXISTS子查詢的用法子查詢的用法應(yīng)用應(yīng)用SQL進行綜合查詢進行綜合查詢什么是子查詢3-1學(xué)生信息表學(xué)生信息表編寫編寫T-SQL語句，查看年齡比語句，查看年齡比“李斯文李斯文”小小的學(xué)生，要求顯示這些學(xué)生的信息的學(xué)生，要求顯示這些學(xué)生的信息 ？第一步：查詢得到第一步：查詢得到“李斯文李斯文”的出生

6、日期的出生日期第二步：利用第二步：利用WHERE語句，篩選出生日期語句，篩選出生日期比比“李斯文李斯文”大的學(xué)生大的學(xué)生什么是子查詢3-2 實現(xiàn)方法一：采用實現(xiàn)方法一：采用T-SQL變量實現(xiàn)變量實現(xiàn) DECLARE Birthday datetime -定義變量，存放李斯文的出生日期定義變量，存放李斯文的出生日期SELECT Birthday = BornDate FROM student WHERE studentName = 李斯文李斯文 -求出李斯文的出生日期求出李斯文的出生日期SELECT StudentNo, StudentName, Sex, BornDate, Address F

7、ROM student WHERE BornDate Birthday -篩選出生日期比李斯文篩選出生日期比李斯文大的學(xué)生大的學(xué)生GO 演示案例演示案例1：用變量查年齡比：用變量查年齡比“李斯文李斯文”大的學(xué)生大的學(xué)生數(shù)據(jù)庫服務(wù)器自上而下逐條執(zhí)行每個數(shù)據(jù)庫服務(wù)器自上而下逐條執(zhí)行每個SQL語句，前面語句的語句，前面語句的執(zhí)行結(jié)果可以作為后面語句的條件執(zhí)行結(jié)果可以作為后面語句的條件什么是子查詢3-3實現(xiàn)方法二：采用子查詢實現(xiàn)實現(xiàn)方法二：采用子查詢實現(xiàn) SELECT StudentNo, StudentName, Sex, BornDate, Address FROM Student WHERE B

8、ornDate (SELECT BornDate FROM Student WHERE StudentName=李斯文李斯文)GO 子查詢在子查詢在WHERE語句中的一般用法：語句中的一般用法： 將子查詢和比較運算符聯(lián)合使用，必須保證子查將子查詢和比較運算符聯(lián)合使用，必須保證子查詢返回的值不能多于一個詢返回的值不能多于一個子查詢是一個嵌套在子查詢是一個嵌套在 SELECT、INSERT、UPDATE 或或 DELETE 語句或其他子查詢中的查語句或其他子查詢中的查詢詢父查詢父查詢子查詢，總是用子查詢，總是用圓括號括起來圓括號括起來演示案例演示案例2：用子查詢查年齡比李斯文大的學(xué)生：用子查詢查年

9、齡比李斯文大的學(xué)生首先，執(zhí)行小括號中的子查詢，返回的結(jié)果是所有來自子查詢的結(jié)首先，執(zhí)行小括號中的子查詢，返回的結(jié)果是所有來自子查詢的結(jié)果果其次，才開始執(zhí)行外圍的其次，才開始執(zhí)行外圍的父父查詢，返回查詢，返回查詢查詢的的最終最終結(jié)果結(jié)果SELECT FROM 表表1 WHERE 字段字段1 比較運算符比較運算符(子查詢子查詢)使用子查詢替換表聯(lián)接3-1學(xué)生信息表和成績表學(xué)生信息表和成績表查詢查詢“Java”課程考試剛好等于課程考試剛好等于60分的學(xué)生分的學(xué)生第一步：查詢第一步：查詢“Java”課程的課程編號課程的課程編號第二步：根據(jù)課程編號查詢成績是第二步：根據(jù)課程編號查詢成績是60分學(xué)生的學(xué)分

10、學(xué)生的學(xué)號號第三步：根據(jù)學(xué)號查詢得到學(xué)生姓名第三步：根據(jù)學(xué)號查詢得到學(xué)生姓名使用子查詢替換表聯(lián)接3-2實現(xiàn)方法一：采用表聯(lián)接實現(xiàn)方法一：采用表聯(lián)接 SELECT StudentName FROM Student stu INNER JOIN Result r ON stu.StudentNO = r.StudentNo INNER JOIN Subject sub ON r.SubjectNo = sub.SubjectNo WHERE StudentResult = 60 AND SubjectName = Java LogicGO內(nèi)聯(lián)接（等值聯(lián)接）內(nèi)聯(lián)接（等值聯(lián)接）演示案例演示案例3：用表

11、聯(lián)接查詢成績是：用表聯(lián)接查詢成績是60分的學(xué)生分的學(xué)生使用子查詢替換表聯(lián)接3-3實現(xiàn)方法二：采用子查詢實現(xiàn)方法二：采用子查詢 SELECT StudentName FROM Student WHERE StudentNo = ( SELECT StudentNo FROM Result INNER JOIN Subject ON Result.SubjectNo= Subject.SubjectNo WHERE StudentResult=60 AND SubjectName=Java Logic )GO子查詢子查詢一般來說，表聯(lián)接都可以用子查詢替換，但有的一般來說，表聯(lián)接都可以用子查詢替換，

12、但有的子查詢卻不能用表聯(lián)接替換子查詢卻不能用表聯(lián)接替換子查詢比較靈活、方便，常作為增刪改查的篩選子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合于操縱一個表的數(shù)據(jù)條件，適合于操縱一個表的數(shù)據(jù)表聯(lián)接更適合于查看多表的數(shù)據(jù)表聯(lián)接更適合于查看多表的數(shù)據(jù)演示案例演示案例4：用子查詢查詢成績是：用子查詢查詢成績是60分的學(xué)生分的學(xué)生指導(dǎo)使用子查詢查特定學(xué)生成績2-1訓(xùn)練要點：訓(xùn)練要點：使用子查詢返回單條記錄使用子查詢返回單條記錄需求說明：需求說明：查詢參加最近一次查詢參加最近一次“C#”考試成績最高分和最低分考試成績最高分和最低分講解需求說明講解需求說明指導(dǎo)使用子查詢查特定學(xué)生成績2-2實現(xiàn)思路：實

13、現(xiàn)思路：查詢獲得查詢獲得“C# OOP”課程的課程編號課程的課程編號查詢獲得查詢獲得“C# OOP”課程最近一次的考試日期課程最近一次的考試日期根據(jù)課程編號查詢考試成績的最高分和最低分根據(jù)課程編號查詢考試成績的最高分和最低分SELECT MAX(StudentResult) AS 最高分最高分, MIN(StudentResult) AS 最低分最低分FROM ResultWhere SubjectNo= (SELECT SubjectNo FROM Subject WHERE SubjectName=C# OOP ) AND ExamDate=(select max(ExamDate) fr

14、om Result)完成時間：完成時間：20分鐘分鐘小結(jié)什么是子查詢？什么是子查詢？簡述子查詢的執(zhí)行步驟簡述子查詢的執(zhí)行步驟如何將子查詢的查詢結(jié)果與父查詢條件匹配？如何將子查詢的查詢結(jié)果與父查詢條件匹配？IN子查詢3-1子查詢返回不止子查詢返回不止1個值時，個值時，使用比較運算符會出錯使用比較運算符會出錯解決方法：采用解決方法：采用 IN 子查詢子查詢 SELECT StudentNo, StudentName FROM Student WHERE StudentNo IN ( SELECT StudentNo FROM Result WHERE SubjectNo = ( SELECT Su

15、bjectNo FROM Subject WHERE SubjectName=Java Logic ) -課程課程 AND StudentResult=60 -成績成績)將號改為將號改為ININ后面的子查詢可以返回多條記錄后面的子查詢可以返回多條記錄常用常用IN替換等于（）的比較子查詢替換等于（）的比較子查詢這是一個三層嵌套的這是一個三層嵌套的子查詢子查詢查詢查詢“Java”課程考試成績?yōu)檎n程考試成績?yōu)?0分的學(xué)分的學(xué)生名單生名單IN子查詢3-2查詢參加查詢參加“Java”課程考試的課程考試的在讀學(xué)生名單在讀學(xué)生名單第一步：獲得第一步：獲得Java Logic課程的課程編號課程的課程編號SEL

16、ECT SubjectNo FROM Subject WHERE SubjectName=Java LogicSELECT MAX(ExamDate) FROM Result WHERE SubjectNo=( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic )第二步：根據(jù)課程編號查詢得到第二步：根據(jù)課程編號查詢得到Java課程最近課程最近一次的考試日期一次的考試日期第三步：根據(jù)課程編號和最近一次的考試日期第三步：根據(jù)課程編號和最近一次的考試日期查詢出在讀學(xué)生信息查詢出在讀學(xué)生信息IN子查詢3-3 參考語句參考語句SELEC

17、T StudentNo, StudentName FROM Student WHERE StudentNo IN ( SELECT StudentNo FROM Result WHERE SubjectNo IN ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic )

18、 )獲得獲得“Java Logic”課課程的課程編號程的課程編號獲得獲得“Java Logic”課課最近一次的考試日期最近一次的考試日期獲得參加獲得參加“Java Logic”課最近一次考課最近一次考試的學(xué)生學(xué)號試的學(xué)生學(xué)號查詢出參加查詢出參加“Java Logic”課最近一次考課最近一次考試的在讀學(xué)生姓名試的在讀學(xué)生姓名這是一個四層嵌套這是一個四層嵌套的子查詢的子查詢演示案例演示案例5：使用：使用IN子查詢子查詢 NOT IN子查詢SELECT StudentNo, StudentName FROM Student WHERE StudentNo NOT IN ( SELECT Studen

19、tNo FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) ) )演示案例演示案例6：使用：使用NOT IN子查詢子查詢 AND GradeId = ( SELECT GradeId FROM Subje

20、ct WHERE SubjectName = Java Logic)限定限定“Java Logic”課課程所在學(xué)期程所在學(xué)期查詢未參加查詢未參加“Java Logic”課程最近一次考試的課程最近一次考試的在讀學(xué)生名單在讀學(xué)生名單第一步：查詢參加第一步：查詢參加“Java Logic”課程最近一次考課程最近一次考試的學(xué)生名單試的學(xué)生名單第二步：在第二步：在IN關(guān)鍵字之前增加否定詞關(guān)鍵字之前增加否定詞NOT第三步：限定第三步：限定“Java Logic”課程所在學(xué)期課程所在學(xué)期指導(dǎo)使用IN關(guān)鍵字的子查詢2-1訓(xùn)練要點：訓(xùn)練要點：使用子查詢返回多條記錄使用子查詢返回多條記錄需求說明：需求說明：查詢查

21、詢一年級開設(shè)的課程一年級開設(shè)的課程講解需求說明講解需求說明指導(dǎo)使用IN關(guān)鍵字的子查詢2-2實現(xiàn)思路：實現(xiàn)思路：查詢獲得年級名稱是查詢獲得年級名稱是S1的所有課程的課程編號的所有課程的課程編號根據(jù)課程編號查詢課程表得到課程名稱根據(jù)課程編號查詢課程表得到課程名稱SELECT SubjectName FROM Subject WHERE GradeId IN ( SELECT GradeId FROM Grade WHERE GradeName=S1)完成時間：完成時間：20分鐘分鐘練習(xí)使用NOT IN關(guān)鍵字的子查詢需求說明：需求說明：查詢未參加查詢未參加“SQL Base”課程最近一次考試的課程最

22、近一次考試的在讀學(xué)在讀學(xué)生名單生名單提示：提示：獲得獲得SQL Base課程的課程編號課程的課程編號根據(jù)課程編號查詢得到根據(jù)課程編號查詢得到SQL Base課程最近一次的考課程最近一次的考試日期試日期根據(jù)課程編號和最近一次的考試日期查詢出學(xué)生名單根據(jù)課程編號和最近一次的考試日期查詢出學(xué)生名單通過通過NOT IN關(guān)鍵字查出沒有參加最近一次考試的在關(guān)鍵字查出沒有參加最近一次考試的在讀學(xué)生名單讀學(xué)生名單限定限定SQLBase課程所在學(xué)期課程所在學(xué)期完成時間：完成時間：20分鐘分鐘SELECT StudentName FROM Student WHERE StudentNo NOT IN ( SELE

23、CT StudentNo FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=SQL Base ) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo =( SELECT SubjectNo FROM Subject WHERE SubjectName=SQL Base ) )AND GradeId = ( SELECT SubjectNo FROM Subject WHERE SubjectName=SQL

24、 Base)共性問題講解n常見調(diào)試問題及解決辦法常見調(diào)試問題及解決辦法n代碼規(guī)范問題代碼規(guī)范問題共性問題集中講解共性問題集中講解EXISTS子查詢4-1如何用如何用SQL語句檢測數(shù)據(jù)庫語句檢測數(shù)據(jù)庫MySchool是否已是否已經(jīng)創(chuàng)建？經(jīng)創(chuàng)建？IF EXISTS（SELECT * FROM sysDatabases WHERE name = MySchool） DROP DATABASE MySchoolGOCREATE DATABASE MySchool - 建庫代碼略建庫代碼略 GOEXISTS子查詢4-2IF EXISTS （子查詢）（子查詢） 語句語句EXISTS子查詢的語法：子查詢的語

25、法：如果子查詢的結(jié)果非空，即記錄條數(shù)如果子查詢的結(jié)果非空，即記錄條數(shù)1條以上，條以上，則則EXISTS （子查詢）將返回真（子查詢）將返回真（true），否），否則返回假則返回假(false) EXISTS也可以作為也可以作為WHERE 語句的子查詢，語句的子查詢，但一般都能用但一般都能用IN子查詢替換子查詢替換EXISTS子查詢4-3檢查檢查“Java Logic”課程最近一次考試課程最近一次考試。如果有如果有 80分以上分以上的成績，則每人提的成績，則每人提2分分；否則，；否則，每人每人提提5分分。最終的成績不得大于。最終的成績不得大于100分分第一步：采用第一步：采用EXISTS檢測是否

26、有人考試成績達到檢測是否有人考試成績達到80分以上分以上第二步：如果成績有第二步：如果成績有80分以上的，分以上的，UPDATE語句語句為參加本次考試的每名學(xué)生加為參加本次考試的每名學(xué)生加2分；否則加分；否則加5分分/*-采用采用EXISTS子查詢，進行酌情加分子查詢，進行酌情加分-*/IF EXISTS ( SELECT * FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) AND ExamDate = ( SELECT MAX(ExamDate) FROM

27、 Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic) ) AND StudentResult80) EXISTS子查詢4-4 參考語句參考語句/*-采用采用EXISTS子查詢，進行酌情加分子查詢，進行酌情加分-*/IF EXISTS ( ) BEGIN UPDATE Result SET StudentResult=StudentResult+2 WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectN

28、ame=Java Logic ) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) ) AND StudentResult=60分分”NOT EXISTS子查詢2-2參考語句參考語句IF NOT EXISTS ( SELECT * FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectNam

29、e=Java Logic) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic) ) AND StudentResult=60 ) BEGIN END ELSE BEGIN END IF NOT EXISTS ( ) BEGIN PRINT 本次無人通過考試，試題偏難，每人加本次無人通過考試，試題偏難，每人加3分，加分后的成績?yōu)椋悍?，加分后的成績?yōu)椋?UPDATE Result SET St

30、udentResult=StudentResult + 3 WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic ) ) AND StudentResult=97 END ELSE BEGIN END IF NOT EXISTS ( )

31、 BEGIN END ELSE BEGIN PRINT 本次考試成績一般，每人只加本次考試成績一般，每人只加1分，加分后的成績?yōu)椋悍?，加分后的成績?yōu)椋?UPDATE Result SET StudentResult=StudentResult + 1 WHERE SubjectNo = ( SELECT SubjectNo FROM Subject WHERE SubjectName=Java Logic) AND ExamDate = ( SELECT MAX(ExamDate) FROM Result WHERE SubjectNo = ( SELECT SubjectNo FROM Su

32、bject WHERE SubjectName=Java Logic ) ) AND StudentResult=60 THEN 1 ELSE 0 END 3)要求保存提?。ú樵儯┑慕Y(jié)果，可以使用我們曾學(xué)習(xí)過的要求保存提取（查詢）的結(jié)果，可以使用我們曾學(xué)習(xí)過的SELECT INTO TempResult 語句，生成新表并保存數(shù)據(jù)語句，生成新表并保存數(shù)據(jù) 指導(dǎo)SQL語句的綜合應(yīng)用5-4需求說明：需求說明：根據(jù)考試平均分為低于平均分的學(xué)生加分根據(jù)考試平均分為低于平均分的學(xué)生加分顯示提分后學(xué)生最終成績和通過率顯示提分后學(xué)生最終成績和通過率實現(xiàn)思路：實現(xiàn)思路：獲取平均分獲取平均分判斷平均分是否低于判斷

33、平均分是否低于6060分。如果低于分。如果低于6060分，設(shè)置平均分分，設(shè)置平均分為為6060分分提分提分對成績低于平均分的學(xué)生進行循環(huán)提分，每次每人提對成績低于平均分的學(xué)生進行循環(huán)提分，每次每人提1 1分，提分后最高分不能超過分，提分后最高分不能超過9797分分輸出提分處理后學(xué)生的最終成績輸出提分處理后學(xué)生的最終成績統(tǒng)計提分處理后學(xué)生的通過率情況統(tǒng)計提分處理后學(xué)生的通過率情況指導(dǎo)SQL語句的綜合應(yīng)用5-54. 獲取提分前的平均分獲取提分前的平均分DECLARE avg numeric(4,1) -定義變量存放平均分定義變量存放平均分SELECT avg=AVG(StudentResult) FROM TempResult WHERE StudentResult IS NOT NULL5. 比較考試平均分，對低于提分前平均分的學(xué)生進行循環(huán)提分，每次加比較考試平均分，對低