数据库应用技术教程 课件 第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指定