第五章高级查询

第五章高级查询5.1子查询概述5.2无关子查询5.3相关子查询5.4子查询在INSERT、UPDATE、DELETE中的应用5.5集合操作5.1子查询概述在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，如将一个查询块嵌套在另一个查询块的子句中，这种查询称为嵌套查询，被嵌套的查询块称为子查询，外面的查询块称为主查询。子查询一般嵌套在WHERE子句和FROM子句中使用，使用时要用括号括起来，嵌套层数最多为32层。5.1.1WHERE子句中嵌套子查询WHERE子句中嵌套子查询时，子查询放在条件表达式里，一般返回单一数值或数值列表。子查询嵌套在WHERE子句中的语法格式：SELECT目标列名FROM表名WHERE[列名]操作运算符（SELECT目标列名FROM表名WHERE条件表达式）说明：“操作运算符”可以是关键字IN、NOTIN、EXISTS、NOTEXISTS，或是比较运算符、ALL+比较符、ANY+比较符。当使用关键字EXISTS和NOTEXISTS时，“操作运算符”前不需要“列名”。例（加）：查询成绩小于88的学生的学号、姓名。SELECTStu_ID'学号',Stu_Name'姓名'FROMStudentWHEREStu_IDIN(SELECTStu_IDFROMStu_CourseWHEREScore88)操作运算符IN判断运算符左面的值是否属于右面的集合，是则返回值为TRUE，否则返回值为FALSE。例5-1：查询学分大于3的所有课程的课程编号、课程名称和学分信息。不用嵌套子查询：selectCourse_ID,Course_Name,Course_CreditfromCoursewhereCourse_Credit3用嵌套子查询：selectCourse_ID,Course_name,Course_CreditfromCoursewhereCourse_IDin(SelectCourse_IDfromCoursewhereCourse_Credit3)例5-2：查询成绩超过平均值的所有学生的学号、姓名、课程名称和成绩信息，并按由高到低的成绩顺序排列。SELECTa.Stu_ID'学号',a.Stu_Name'姓名',b.Course_Name'课程名称',c.Score'成绩'FROMStudentASaJOINStu_CourseAScONa.Stu_ID=c.Stu_IDJOINCourseASbONb.Course_ID=c.Course_IDWHEREc.Score(SELECTAVG(Score)FROMStu_Course)ORDERBYc.ScoreDESC思考：查询所有选修了3号课程的学生的姓名selectStu_namefromStudentwhereStu_IDin(selectStu_IDfromStu_CoursewhereCourse_ID='3')5.1.2FROM子句中嵌套子查询在FROM子句中嵌套子查询时将子查询的结果作为主查询的查询数据源，即将子查询的结果看作一个虚拟的表或视图。子查询嵌套在FROM子句中的语法格式：SELECT目标列名FROM(SELECT目标列名FROM表名WHERE条件表达式)AS别名WHERE条件表达式子查询后的“AS别名”用来为子查询的结果指定别名，以便于主查询使用子查询的结果。实际应用中，嵌套在FROM子句中的情况比较少。例5-3：查询选修了课程“数据结构”和“操作系统”的学生的姓名和学号信息。不用嵌套子查询：SELECTc.Stu_ID,c.Stu_Name,d.Course_NameFROMStudentAScJOINStu_CourseASeONc.Stu_ID=e.Stu_IDJOINCourseASdONd.Course_ID=e.Course_IDWHEREd.Course_Name='数据结构'ord.Course_Name='操作系统'SELECTdistincta.Stu_Name'姓名',a.Stu_ID'学号'FROM(SELECTc.Stu_ID,c.Stu_Name,d.Course_NameFROMStudentAScJOINStu_CourseASeONc.Stu_ID=e.Stu_IDJOINCourseASdONd.Course_ID=e.Course_ID)ASaWHEREa.Course_Name='数据结构'ora.Course_Name='操作系统'5.2无关子查询无关子查询是指在主查询之前运行，并返回结果供主查询使用的子查询。即子查询的运行与主查询没有关系，而主查询的运行要使用子查询的结果。在WHERE子句中，无关子查询的操作运算符采用IN、NOTIN和比较运算符。5.2.1单行子查询单行子查询即单独采用比较运算符的子查询，子查询返回的结果只有一行数据。用于单行子查询的比较运算符有等于（=）、大于（）、小于（）、大于等于（=）、小于等于（=）、不等于（）。例5-4：刘振江今年22岁，查询和他同属一个系且年龄比他小的学生的信息。SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHEREStu_Name='刘振江')ANDSage22等同于：SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentWHERESdeptin(selectSdeptfromStudentwhereStu_Name='刘振江')andSage22思考：查询比马宁年纪大的教师编号、姓名selectTea_ID,Tea_NamefromTeacherwhereTea_Age(selectTea_AgefromTeacherwhereTea_Name='马宁')5.2.2多行子查询多行子查询是指查询结果返回的是一行或多行数据的子查询。当主查询语句的WHERE子句引用子查询结果时，必须采用多行比较符号IN、NOTIN或ALL+比较符、ANY+比较符来进行比较。IN的含义是匹配子查询结果中的任一个值。NOTIN是与子查询结果中的任一个值都不匹配，ALL则必须要符合子查询的所有值，ANY要符合子查询结果的任何一个值。ALL和ANY操作符不能单独使用，只能与比较运算符配合使用，它们的具体含义如下：ALL，表示所有的。ALL比子查询返回的所有结果都小，即小于返回结果中的最小值；ALL比子查询返回的所有结果都大，即大于返回结果中的最大值；=ALL无意义，逻辑上不成立。ANY，表示任意的。ANY比子查询返回的任意一个结果小即可，即小于返回结果中的最大值；ANY比子查询返回的任意一个结果大即可，即大于返回结果中的最小值；=ANY和子查询返回的任意一个结果相等即可，相当于IN。例5-5：查询“物理系”学生的学号、选修课程编号和成绩信息。SELECTStu_ID,Course_ID,ScoreFROMStu_CourseWHEREStu_IDIN(SELECTStu_IDFROMStudentWHERESdept='物理系')例5-6：查询其他系年龄大于计算机科学与技术系学生年龄的学生信息。SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept='计算机科学与技术系')AndSdept'计算机科学与技术系'例5-7：查询其他系年龄大于计算机科学与技术系中任意一个学生年龄的学生信息。SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept='计算机科学与技术系')andSdept'计算机科学与技术系'思考题：查询其他系中比物理系所有学生年龄都小的学生的学号、姓名、年龄，并按学号降序排序。SELECTStu_ID学号,Stu_Name姓名,Sage年龄FROMStudentWHERESageall(SELECTSageFROMStudentWHERESdept='物理系')/*andSdept'物理系'*/OrderbyStu_ID5.3相关子查询相关子查询是指每次执行查询都需要依赖主查询结果的子查询。由于相关子查询会对主查询的每一个可能结果都执行一次查询，因而又称重复子查询。相关子查询不能单独执行，这一点与无关子查询不同。例5-8：查询年龄大于各系．．年龄平均值的所有学生的学号、姓名、年龄和所在系信息。分析：SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentWHERESage(SELECTAVG(Sage)FROMStudent)SELECTSdept系部,avg(Sage)年龄FromStudentgroupbySdeptSQL编写：SELECTStu_ID,Stu_Name,Sage,SdeptFROMStudentaWHERESage(SELECTAVG(Sage)FROMStudentWHERESdept=a.Sdept)说明：主查询首先查询学生表（Student）的第一行，并将第一行的所在系（Sdept）的列值交给子查询，子查询根据该列值查询所对应系的学生年龄平均值，并返回给主查询；主查询将该行的年龄（Sage）的列值与平均值比较，如果大于平均值，则将该数据行放入查询结果中；依次类推，直到主查询查询到学生表（Student）的最后一行为止。思考：对Stu_Course表，查询成绩大于各门课程平均分的所有学生的学号、所修课程号、成绩selectStu_ID,Course_ID,ScorefromStu_CourseasawhereScore(selectAVG(Score)fromStu_CoursewhereCourse_ID=a.Course_ID)5.3.1EXISTS子查询将EXISTS关键字引入子查询后，子查询的作用就相当于进行存在测试。主查询的WHERE子句测试子查询返回的行是否存在，子查询不产生任何数据。使用EXISTS的子查询的语法格式：WHEREEXISTS（SELECT目标列名FROM表名WHERE条件表达式）由于子查询并不返回具体的值，所以子查询的“目标列名”常使用符号“*”。例5-9：查询选修了课程的学生的学号和姓名信息。SELECTStu_ID,Stu_NameFROMStudentaWHEREEXISTS(SELECT*FROMStu_CourseWHEREStu_ID=a.Stu_ID)等同于：SELECTStu_ID,Stu_NameFROMStudentaWHEREStu_IDin(SELECTStu_IDFROMStu_CourseWHEREStu_ID=a.Stu_ID)5.3.2NOTEXISTS子查询NOTEXISTS与EXISTS的使用方法类似，用于查找在某数据表中不存在的数据行。使用NOTEXISTS的子查询的语法：WHERENOTEXISTS（SELECT*FROM表名WHERE条件表达式）例5-10：查询未选修课程学生的学号和姓名信息。SELECTStu_ID,Stu_NameFROMStudentaWHERENOTEXISTS(SELECT*FROMStu_CourseWHEREStu_ID=a.Stu_ID)思考题：查询所有选修了2号课程的学生的学号；查询所有未选2号课程的学生的学号。selectStu_IDfromStudentasawhereexists(select*fromStu_CoursewhereStu_ID=a.Stu_IDandCourse_ID='2')select