工信版（中职）计算机组装与维护A0508教学课件.pptx

YCF（中职）计算机组装与维护A0508教学课件SQL Server数据库项目教程教学课件项目5：操作数据我们毕业啦其实是答辩的标题地方任务8 高级查询之嵌套子查询1.知识准备2.使用算术运算符的子查询3.使用SOME和ALL谓词的子查询4.使用IN和EXISTS谓词的子CONTANTS思维导图1.知识准备1.知识准备1.1嵌套子查询在SQL语言中，一个 SELECT-FROM-WHERE 语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为嵌套查询。前者为子查询或内查询，后者为父查询或外查询。子查询又分为嵌套子查询和相关子查询。本任务主要介绍嵌套子查询。嵌套子查询的求解方法为：由里向外。即在每个子查询在其上一级查询处理之前求解，且子查询的结果不显示出来，而是作为其外部查询的条件。1.知识准备1.1外层查询和内层查询关系的举例SELECT Sname /*外层查询或父查询*/FROM StudentWHERE Sno IN (SELECT Sno /*内层查询或子查询*/FROM SC WHERE Cno=2);1.知识准备1.2使用比较运算符的嵌套子查询在SQL语言中通过比较运算符将父查询和子查询进行连接，当子查询返回的是单值时，可以使用=、=、=、!=等比较运算符。1.知识准备1.3使用SOME、ANY和ALL的嵌套子查询子查询只能返回单一值。如果子查询返回多个值，则可以带上SOME、ANY和ALL来表示，它们都是父查询中WHERE条件指定的列值与子查询的结果进行比较，并返回满足条件的行。其中SOME和ANY意义相同，可以互相替换，注重子查询是否有返回的值能满足搜索条件。若是在与多值序列的比较中，只需要满足与多值序列中的一个值满足比较关系就返回TRUE，则用ANY(SOME)。ALL要求子查询的所有查询结果列都要满足搜索条件。若是在与多值序列的比较中，需要满足与多值序列中的全部值满足比较关系才返回TRUE，则用ALL。1.知识准备1.3使用SOME、ANY和ALL的嵌套子查询序号符号语义1ANY大于子查询结果中的某个值2ALL大于子查询结果中的所有值3ANY小于子查询结果中的某个值4=ANY大于等于子查询结果中的某个值6=ALL大于等于子查询结果中的所有值7=ALL小于等于子查询结果中的所有值8=ANY大于等于子查询结果中的某个值9=ANY等于子查询结果中的某个值10=ALL等于子查询结果中的所有值（通常没有实际意义）11!=（或）ANY不等于子查询结果中的某个值12!=（或）ALL不等于子查询结果中的任何一个值1.知识准备1.3 使用IN和NOT IN的嵌套子查询在嵌套查询中，子查询的结果往往是一个集合，所以谓词 IN或者NOT IN 是嵌套查询中最经常使用的谓词。这里子查询里面没有依赖父查询，此种查询也叫做不相关子查询。1.知识准备1.4使用EXISTS和NOTEXISTS的嵌套子查询EXISTS代表存在量词，带有EXISTS的子查询不返回任何数据，只产生逻辑真值“TRUE”或者逻辑假值“FALSE”。使用EXISTS的嵌套语句，若子查询结果不为空，则EXISTS返回TRUE，否则返回FALSE。使用EXISTS引出的子查询，其目标表达式列都使用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际含义。它与IN子查询的区别：在带IN的子查询中，会遍历子查询表中所有记录进行筛选，带EXISTS的查询找到一条记录就返回，不会遍历整个表，所以带EXISTS的查询是一个优质查询。2.使用算术运算符的子查询2.使用算术运算符的子查询在数据库dbStudentScore中，完成下面的查询操作。（1）在班级表tbClass，查询与“13应用化学2班”班级是同一个专业的班级信息，显示班级代码、班级名称、教室名称、校区、入学年份和专业代码。（2）在成绩表tbScore和tbStudent中，对课程代码为kc90042，班级代码为C00010的数据，查询所有高于平均成绩（平均成绩以总评成绩来认定，不以平时成绩和期中成绩认定）的学生成绩数据，显示学生姓名，学号，班级代码，课程代码，总评成绩。本案例配备微课视频2.1 任务描述2.使用算术运算符的子查询经分析，可以使用嵌套子查询完成。（1）对于子任务1：在班级表tbClass关键是找出“13应用化学2班”班级对应的专业代码MajorID。子任务只给出了“13应用化学2班”班级名称，并没有直接给出专业代码。因此可以使用这个条件先查询出专业代码。这个查询结果作为子查询。通过上面的子查询语句查到到专业代码后，再放到父查询中就可以关联查询出相应的数据。（2）对于子任务2：这里先使用AVG（）函数计算出课程代码为kc90042，班级代码为C00010的数据的平均成绩，再以此为查询结果放到父查询中就可以关联查询出相应的数据。2.2 任务描述2.使用算术运算符的子查询-（1）在班级表tbClass,查询与“13应用化学2班”班级是同一个专业的班级信息，显示班级代码、班级名称、教室名称、校区、入学年份、专业代码。select ClassID,ClassName,ClassRoomName,Campus,RegisteredYear,MajorID from tbClass where MajorID=(select MajorID from tbClass where ClassName=13应用化学2班 )go2.3 参考答案2.使用算术运算符的子查询-（2）在成绩表tbScore和tbStudent中，对课程代码为kc90042，班级代码为C00010的数据，查询所有高于平均成绩（平均成绩以总评成绩来认定，不以平时成绩和期中成绩认定）的学生成绩数据，显示学生姓名，学号，班级代码，课程代码，总评成绩select StudentName,tbStudent.StudentID,ClassID,CourseID,ScoreAllfrom tbScore,tbStudent where tbScore.StudentID=tbStudent.StudentID and ClassID=C00010 and CourseID=kc90042 and ScoreAll(select avg(ScoreAll)from tbScore,tbStudent where tbScore.StudentID=tbStudent.StudentID and ClassID=C00010 and CourseID=kc90042)go2.3 参考答案3.使用SOME和ALL谓词的子查询3.使用SOME和ALL谓词的子查询在数据库dbStudentScore中，完成下面的查询操作。（3）使用SOME谓词，在班级表tbClass中“13应用化学2班”班级所在专业的专业代码为m00005，它们的班级人数都不一样，查询比“13应用化学2班”班级所在专业某个班级学生人数多的班级信息，显示所有字段的信息。（4）使用ALL谓词，在班级表tbClass中“13应用化学2班”班级所在专业有数个班级，它们的班级人数都不一样，查询比“13应用化学2班”班级所在专业所有班级学生人数多的班级信息，显示所有字段的信息。本案例配备微课视频3.1任务描述3.使用SOME和ALL谓词的子查询经分析，可以使用SOME、ANY和ALL的嵌套子查询完成。如果子查询返回多个值，则可以带上SOME、ANY和ALL来表示，它们都是父查询中WHERE条件指定的列值与子查询的结果进行比较，并返回满足条件的行。SOME和ANY意义相同，可以互相替换：若是在与多值序列的比较中，只需要满足与多值序列中的一个值满足比较关系就返回TRUE。ALL要求子查询的所有查询结果列都要满足搜索条件。若是在与多值序列的比较中，需要满足与多值序列中的全部值满足比较关系才返回TRUE3.2 任务分析3.使用SOME和ALL谓词的子查询-（1）使用SOME谓词，在班级表tbClass中“13应用化学2班”班级所在专业的专业代码为m00005，它们的班级人数都不一样，查询比“13应用化学2班”班级所在专业某个班级学生人数多的班级信息，显示所有字段的信息。select*from tbClass where StudentTotals SOME (select StudentTotals from tbClass where MajorID=m00005 )go-（2）使用ALL谓词，在班级表tbClass中“13应用化学2班”班级所在专业的专业代码为m00005，它们的班级人数都不一样，查询比“13应用化学2班”班级所在专业所有班级学生人数多的班级信息，显示所有字段的信息。select*from tbClass where StudentTotals ALL (select StudentTotals from tbClass where MajorID=m00005 )go3.3参考答案4.使用IN和EXISTS谓词的子查询4.使用IN和EXISTS谓词的子查询在数据库dbStudentScore中，完成下面的查询操作。（5）使用IN谓词，查询考试总评成绩不合格的学生，显示学号，姓名、性别、班级代码。（6）使用EXISTS谓词，查询选修了2018-2019学年第1学期课程代码为kc90021课程任务的学生信息，显示学号，姓名、性别、班级代码。4.1 任务描述4.使用IN和EXISTS谓词的子查询在嵌套查询中，子查询的结果往往是一个集合，所以谓词 IN或者NOT IN 是嵌套查询中最经常使用的谓词。这里子查询里面没有依赖父查询，此种查询也叫做不相关子查询。EXISTS代表存在量词，带有EXISTS的子查询不返回任何数据，只产生逻辑真值“TRUE”或者逻辑假值“FALSE”。使用EXISTS的嵌套语句，若子查询结果不为空，则EXISTS返回TRUE，否则返回FALSE。使用EXISTS引出的子查询，其目标表达式列都使用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际含义。5.2 任务分析3.使用SOME和ALL谓词的子查询3.3EXISTS与IN子查询的区别在带IN的子查询中，会遍历子查询表中所有记录进行筛选，带EXISTS的查询找到一条记录就返回，不会遍历整个表，所以带EXISTS的查询是一个优质查询。4.使用IN和EXISTS谓词的子查询-（3）使用IN谓词，查询考试总评成绩不合格的学生，显示学号，姓名、性别、班级代码。select StudentID,StudentName,sex,ClassID from tbStudentwhere StudentID in (select StudentID from tbScore where ScoreAll60)-（4）使用EXISTS谓词，查询选修了2018-2019学年第1学期课程代码为kc90021课程任务的学生信息，显示学号，姓名、性别、班级代码。select StudentID,StudentName,sex,ClassID from tbStudentwhere EXISTS (select*from tbTeachingTasks where tbStudent.StudentID=tbTeachingTasks.StudentID and SchoolYear=2018-2019 and Semester=1 and CourseID=kc90021)参考答案THANKS