數據庫應用技術教程 課件 第4章 數據查詢4

課前回顧1.查詢和數據庫同一學期開設的其他課程的課程名和學分。2.查詢沒有選課的學生的學號和姓名。4.5子查詢（嵌套查詢）在SQL語言中，一個SELECT－FROM－WHERE語句稱為一個查詢塊；子查詢是一個SELECT查詢，它嵌套在SELECT、INSERT、UPDATE、DELETE語句的WHERE或HAVING子句內，或其他子查詢中；子查詢的SELECT查詢總是使用圓括號括起來。1.基于集合的子查詢使用子查詢表示查詢的范圍一般格式為：列名[NOT]IN(子查詢)注意使用子查詢進行基于集合的查詢時，由子查詢返回的結果集中的列的個數、數據類型以及語義必須與表達式中的列的個數、數據類型以及語義相同。當子查詢返回結果之后，外層查詢將用這個結果作為篩選條件。示例例60.查詢與劉超華在同一個系的學生。

SELECTSno,Sname,SdeptFROMStudent WHERESdept= (SELECTSdeptFROMStudent WHERESname='劉超華'

)

ANDSname!='劉超華'

②①示例例61.查詢成績為大于80分的學生的學號、姓名。

SELECTSno,SnameFROMStudent WHERESnoIN (SELECTSnoFROMSC WHEREGrade>80)①②示例例62．查詢計算機系選了“2”課程的學生，列出姓名和性別。

SELECTSname,SsexFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno='2')ANDSdept='計算機系'用多表連接實現：SELECTSname,SsexFROMStudentS,ScWHERES.Sno=SC.SnoandSdept='計算機系'ANDCno='2'示例例63.查詢選修了“數據庫”課程的學生的學號和姓名。（1）在Course表中，找出“數據庫”課程名對應的課程號；（2）根據得到的“數據庫”課程號，在SC表中找出選了該課程號的學生的學號；（3）根據得到的學號，在Student表中找出對應的學生的學號和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='數據庫'))例64.在選修了數據庫的這些學生中，統計他們的選課門數和平均成績。SELECTSno學號,COUNT(*)選課門數,AVG(Grade)平均成績FROMSCWHERESnoIN(--選數據庫的學生SELECTSnoFROMSCJOINCourseCONC.Cno=SC.CnoWHERECname=‘數據庫')GROUPBYSno2.進行比較測試帶比較運算符的子查詢指父查詢與子查詢之間用比較運算符連接，當用戶能確切知道內層查詢返回的是單值時，可用>、<、=、>=、<=、<>運算符。示例例65.查詢選了“3”號課程且成績高于此課程的平均成績的學生的學號和成績。首先計算“3”號課程的平均成績：

SELECTAVG(Grade)fromSCWHERECno='3'--79然后，查找“3”號課程所有的考試成績中，高于79的學生：SELECTSno,GradeFROMSCWHERECno='3'ANDGrade>79將兩個查詢語句合起來即為滿足我們要求的查詢語句：SELECTSno,GradeFROMSCWHERECno='3'ANDGrade>(SELECTAVG(Grade)FROMSCWHERECno='3')若無結果，請檢查3前面是否有空格不相關子查詢用子查詢進行基于集合查詢和比較查詢時，都是先執行子查詢，然后再在子查詢的結果基礎之上執行外層查詢。子查詢都只執行一次，子查詢的查詢條件不依賴于外層查詢，我們將這樣的子查詢稱為不相關子查詢或嵌套子查詢。示例例66.查詢計算機系年齡最大的學生的姓名和年齡。SELECTSname,SageFROMStudentWHERESdept='計算機系'ANDSage=(SELECTMAX(Sage)FROMStudentWHERESdept='計算機系')示例例67.查詢數據庫考試成績高于數據庫平均成績的學生的姓名、所在系和數據庫成績。SELECTSname,Sdept,GradeFROMStudentSJOINSCONS.Sno=SC.SnoJOINCourseCONC.Cno=SC.CnoWHERECname='數據庫'ANDGrade>(SELECTAVG(Grade)FROMSCJOINCourseCONC.Cno=SC.CnoWHERECname='數據庫')3.存在性查詢通常使用EXISTS謂詞，其形式為： WHERE[NOT]EXISTS（子查詢）帶EXISTS謂詞的子查詢不返回查詢的數據，只產生邏輯真值（有數據）和假值（沒有數據）。例68.查詢選修了1課程的學生姓名。SELECTSnameFROMStudent WHEREEXISTS (SELECT*FROMSC WHERESno=Student.Sno

ANDCno='1')

注意處理過程為：先外后內；由外層的值決定內層的執行；內層執行次數由外層結果數決定。由于EXISTS的子查詢只能返回真或假值，因此在這里給出列名無意義。所以在有EXISTS的子查詢中，其目標列表達式通常都用*。上句的處理過程

找外層表Student表的第一行，根據其Sno值處理內層查詢由外層的值與內層的結果比較，由此決定外層條件的真、假順序處理外層表Student表中的第2、3…行。示例例69.查詢沒有選修1號課程的學生姓名和所在系。SELECTSname,SdeptFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')或：SELECTSname,SdeptFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno='1')注意：不能用連接查詢和在子查詢中否定的形式實現。4.with子句WITH子句用于定義公共表表達式（CommonTableExpression,CTE），可臨時存儲查詢結果，簡化復雜查詢邏輯。CTE在單個查詢中可被多次引用，提升代碼可讀性。其語法格式如下。WITHCTE名稱AS(SELECT查詢定義)主查詢語句示例：查詢選課門數大于平均選課門數的學生學號及選課門數。with選課統計as(selectsno,count(*)as選課門數fromscgroupbysno)selectsno,選課門數from選課統計where選課門數>(selectavg(選課門數)from選課統計)4.6在數據更新中使用查詢語句4.6.1插入數據4.6.2更新數據4.6.3刪除數據4.6.1插入數據在INSERT語句中使用SELECT子句的語法格式如下。INSERT[INTO]table_name[(column_list)]SELECTselect_listFROMtable_name[WHEREsearch_condition]一次插入多行數據例1：設stu_cs為計算機系的學生表，結構和student一致，但無數據，請將計算機系的學生信息添加進去。Insertintostu_csSelect*fromstudentWheresdept=‘計算機系’注意被插入的表的值列表必須和查詢結果中的值列表的列按位置順序對應，且數據類型一致。如果被插入的表后邊沒有指明列名，則查詢結果的列的順序必須與表中列的定義順序一致，且每一個列均有值（可以為空）。4.6.2更新數據用UPDATE語句實現。格式：

UPDATE<表名>

SET<列名＝表達式>[,…n][WHERE<更新條件>]基于其他表條件的數據更新例2：將計算機系全體學生的成績加5分。（1）用子查詢實現

UPDATESCSETGrade=Grade+5 WHERESnoIN (SELECTSnoFROMStudent WHERESdept='計算機系')（2）用多表連接實現UPDATESCSETGrade=Grade+5FROMSC,StudentWHERESC.Sno=Student.SnoAndSdept='計算機系'基于本表條件的數據更新例3.將學分最低的課程的學分加2分。UPDATECourseSETCredit=Credit+2WHERECredit=(SELECTMIN(Credit)FROMCourse)較復雜的數據更新例4.數學系學生的“信息系統”考試成績增加10分。用子查詢實現：UPDATESCSETGrade=Grade+10WHERECnoIN(SELECTCnoFROMCourseWHERECname='信息系統')ANDSnoIN(SELECTSnoFROMStudentWHERESdept='數學系')用多表連接實現：UPDATESCSETGrade=Grade+10FROMSCJOINCourseCONC.Cno=SC.CnoJOINStudentSONS.Sno=SC.SnoWHERECname='信息系統'ANDSdept='數學系'4.6.3刪除數據用DELETE語句實現格式：DELETE[FROM]<表名>

[WHERE<刪除條件>基于其他表條件的刪除例5．刪除計算機系不及格學生的修課記錄。（1）用子查詢實現

DELETEFROMSCWHEREGrade<60ANDSnoIN(SELECTSnoFROMStudentWHERESdept='計算機系'

)（2）用多表連接實現

DELETEFROMSC FROMSC，StudentWHERESC.Sno=Student.Sno ANDSdept='計算機系'ANDGrade<60

較復雜的數據刪除例6.刪除信息系考試成績不及格學生的不及格課程的選課記錄。用多表連接形式實現。DELETEFROMSCFROMStudentS，SCWHERES.Sno=SC.snoANDSdept='信息系'ANDGrade<60用子查詢形式實現。DELETEFROMSCWHERESnoIN(SELECTSnoFROMStudentWHERESdept='信息系')ANDGrade<604.7窗口函數在SQLServer中，窗口函數（WindowFunction）

是一種特殊的函數，它能在一組與當前行相關的行（稱為“窗口”）上執行計算，同時不減少結果集的行數。與聚合函數（如SUM、COUNT）不同，窗口函數不會將多行數據合并為一行，而是為每行返回一個計算結果，結果取決于當前行所在的“窗口”數據。窗口函數的核心特點保留原始行結構：計算后不會壓縮行數，每行都有對應的結果。基于“窗口”計算：“窗口”是指與當前行存在邏輯關聯的一組行（可通過規則定義）。靈活的分組與排序：可以通過PARTITIONBY劃分窗口（類似分組），通過ORDERBY定義窗口內的排序規則。4.7窗口函數窗口函數的基本語法結構：函數名(參數)OVER（

[PARTITIONBY分區列]--按指定列將數據分區，每個分區為獨立窗口

[ORDERBY排序列[ASC|DESC]]--對分區內數據按指定列排序

[ROWS|RANGEBETWEEN邊界定義]--定義窗口框架范圍（SQLServer2022增強特性）

4.7窗口函數核心組成：函數：包括排名函數（如ROW_NUMBER、RANK）、聚合函數（如SUM、AVG）等。OVER子句：用于定義“窗口”，其中PARTITIONBY將數據劃分為多個獨立分區（類似分組），ORDERBY指定