第五章 高级查询.docx

1、第五章 高级查询第五章 高级查询5.1 子查询概述 5.2 无关子查询 5.3 相关子查询 5.4 子查询在INSERT、UPDATE、DELETE中的应用 5.5 集合操作 5.1 子查询概述在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，如将一个查询块嵌套在另一个查询块的子句中，这种查询称为嵌套查询，被嵌套的查询块称为子查询，外面的查询块称为主查询。 子查询一般嵌套在WHERE子句和FROM子句中使用，使用时要用括号括起来，嵌套层数最多为32层。 5.1.1 WHERE子句中嵌套子查询 WHERE子句中嵌套子查询时，子查询放在条件表达式里，一般返回单一数值或数值列

2、表。 子查询嵌套在WHERE子句中的语法格式： SELECT FROM WHERE （SELECT FROM WHERE ） 说明： “”可以是关键字IN、NOT IN、EXISTS、NOT EXISTS，或是比较运算符、ALL+比较符、ANY+比较符。 当使用关键字EXISTS和NOT EXISTS时，“”前不需要“”。 例（加）：查询成绩小于88的学生的学号、姓名。SELECT Stu_ID 学号 , Stu_Name 姓名FROM Student WHERE Stu_ID IN(SELECT Stu_ID FROM Stu_Course WHERE Score3用嵌套子查询：select

3、 Course_ID , Course_name , Course_Credit from Coursewhere Course_ID in(Select Course_ID from Course where Course_Credit3)例5-2：查询成绩超过平均值的所有学生的学号、姓名、课程名称和成绩信息，并按由高到低的成绩顺序排列。 SELECT a.Stu_ID 学号 , a.Stu_Name 姓名 , b.Course_Name 课程名称 , c.Score 成绩 FROM Student AS a JOIN Stu_Course AS c ON a.Stu_ID = c.Stu_

4、ID JOIN Course AS b ON b.Course_ID= c.Course_ID WHERE c.Score( SELECT AVG(Score) FROM Stu_Course) ORDER BY c.Score DESC 思考：查询所有选修了3号课程的学生的姓名select Stu_name from Student where Stu_ID in (select Stu_ID from Stu_Course where Course_ID=3) 5.1.2 FROM子句中嵌套子查询 在FROM子句中嵌套子查询时将子查询的结果作为主查询的查询数据源，即将子查询的结果看作一个虚

5、拟的表或视图。 子查询嵌套在FROM子句中的语法格式： SELECT FROM (SELECT FROM WHERE ) AS WHERE 子查询后的“AS ”用来为子查询的结果指定别名，以便于主查询使用子查询的结果。实际应用中，嵌套在FROM子句中的情况比较少。 例5-3：查询选修了课程“数据结构”和“操作系统”的学生的姓名和学号信息。不用嵌套子查询：SELECT c.Stu_ID,c.Stu_Name, d.Course_NameFROM Student AS c JOIN Stu_Course AS e ON c.Stu_ID = e.Stu_ID JOIN Course AS d ON

6、 d.Course_ID= e.Course_ID WHERE d.Course_Name =数据结构 or d.Course_Name =操作系统SELECT distinct a.Stu_Name 姓名,a.Stu_ID 学号 FROM ( SELECT c.Stu_ID,c.Stu_Name,d.Course_Name FROM Student AS c JOIN Stu_Course AS e ON c.Stu_ID = e.Stu_ID JOIN Course AS d ON d.Course_ID= e.Course_ID)AS a WHERE a.Course_Name =数据结

7、构 or a.Course_Name =操作系统5.2 无关子查询 无关子查询是指在主查询之前运行，并返回结果供主查询使用的子查询。即子查询的运行与主查询没有关系，而主查询的运行要使用子查询的结果。 在WHERE子句中，无关子查询的操作运算符采用IN、NOT IN和比较运算符。 5.2.1 单行子查询 单行子查询即单独采用比较运算符的子查询，子查询返回的结果只有一行数据。 用于单行子查询的比较运算符有等于（=）、大于（）、小于（=）、小于等于（=）、不等于（）。 例5-4：刘振江今年22岁，查询和他同属一个系且年龄比他小的学生的信息。 SELECT Stu_ID , Stu_Name , Sa

8、ge , Sdept FROM Student WHERE Sdept=( SELECT Sdept FROM StudentWHERE Stu_Name=刘振江) AND Sage22等同于：SELECT Stu_ID , Stu_Name , Sage , Sdept FROM Student WHERE Sdept in (select Sdept from Student where Stu_Name=刘振江) and Sage (select Tea_Age from Teacher where Tea_Name=马宁)5.2.2 多行子查询 多行子查询是指查询结果返回的是一行或多行

9、数据的子查询。 当主查询语句的WHERE子句引用子查询结果时，必须采用多行比较符号IN、NOT IN 或ALL+比较符、ANY+比较符来进行比较。 IN 的含义是匹配子查询结果中的任一个值。 NOT IN 是与子查询结果中的任一个值都不匹配， ALL 则必须要符合子查询的所有值， ANY 要符合子查询结果的任何一个值。 ALL 和ANY 操作符不能单独使用，只能与比较运算符配合使用，它们的具体含义如下： ALL，表示所有的。 ALL 比子查询返回的所有结果都大，即大于返回结果中的最大值； = ALL 无意义，逻辑上不成立。 ANY，表示任意的。 ANY 比子查询返回的任意一个结果大即可，即大于

10、返回结果中的最小值； = ANY 和子查询返回的任意一个结果相等即可，相当于IN。 例5-5：查询“物理系”学生的学号、选修课程编号和成绩信息。 SELECT Stu_ID , Course_ID , Score FROM Stu_Course WHERE Stu_ID IN (SELECT Stu_ID FROM Student WHERE Sdept=物理系) 例5-6：查询其他系年龄大于计算机科学与技术系学生年龄的学生信息。 SELECT Stu_ID , Stu_Name , Sage , Sdept FROM StudentWHERE SageALL(SELECT Sage FROM

11、 Student WHERE Sdept=计算机科学与技术系) And Sdept计算机科学与技术系例5-7：查询其他系年龄大于计算机科学与技术系中任意一个学生年龄的学生信息。 SELECT Stu_ID , Stu_Name , Sage , Sdept FROM StudentWHERE SageANY(SELECT Sage FROM Student WHERE Sdept=计算机科学与技术系) and Sdept计算机科学与技术系思考题：查询其他系中比物理系所有学生年龄都小的学生的学号、姓名、年龄，并按学号降序排序。SELECT Stu_ID 学号 , Stu_Name 姓名 , Sa

12、ge 年龄 FROM StudentWHERE Sageall(SELECT Sage FROM Student WHERE Sdept=物理系)/*and Sdept物理系*/Order by Stu_ID5.3 相关子查询 相关子查询是指每次执行查询都需要依赖主查询结果的子查询。由于相关子查询会对主查询的每一个可能结果都执行一次查询，因而又称重复子查询。相关子查询不能单独执行，这一点与无关子查询不同。例5-8：查询年龄大于各系年龄平均值的所有学生的学号、姓名、年龄和所在系信息。 分析：SELECT Stu_ID , Stu_Name , Sage , Sdept FROM Student

13、WHERE Sage(SELECT AVG(Sage) FROM Student)SELECT Sdept 系部 , avg(Sage) 年龄From Student group by SdeptSQL编写：SELECT Stu_ID , Stu_Name , Sage , Sdept FROM Student a WHERE Sage (SELECT AVG(Sage) FROM Student WHERE Sdept=a.Sdept) 说明： 主查询首先查询学生表（Student）的第一行，并将第一行的所在系（Sdept）的列值交给子查询，子查询根据该列值查询所对应系的学生年龄平均值，并返

14、回给主查询；主查询将该行的年龄（Sage）的列值与平均值比较，如果大于平均值，则将该数据行放入查询结果中；依次类推，直到主查询查询到学生表（Student）的最后一行为止。 思考：对Stu_Course表，查询成绩大于各门课程平均分的所有学生的学号、所修课程号、成绩select Stu_ID , Course_ID , Score from Stu_Course as awhere Score(select AVG(Score) from Stu_Course where Course_ID=a.Course_ID)5.3.1 EXISTS子查询 将EXISTS关键字引入子查询后，子查询的作用

15、就相当于进行存在测试。主查询的WHERE子句测试子查询返回的行是否存在，子查询不产生任何数据。 使用EXISTS的子查询的语法格式： WHERE EXISTS（SELECT FROM WHERE ） 由于子查询并不返回具体的值，所以子查询的“”常使用符号“*”。 例5-9：查询选修了课程的学生的学号和姓名信息。 SELECT Stu_ID , Stu_Name FROM Student a WHERE EXISTS ( SELECT * FROM Stu_Course WHERE Stu_ID=a.Stu_ID) 等同于：SELECT Stu_ID , Stu_Name FROM Studen

16、t a WHERE Stu_ID in ( SELECT Stu_ID FROM Stu_Course WHERE Stu_ID=a.Stu_ID)5.3.2 NOT EXISTS子查询 NOT EXISTS与EXISTS的使用方法类似，用于查找在某数据表中不存在的数据行。 使用NOT EXISTS的子查询的语法： WHERE NOT EXISTS（SELECT * FROM WHERE ） 例5-10：查询未选修课程学生的学号和姓名信息。 SELECT Stu_ID , Stu_Name FROM Student a WHERE NOT EXISTS ( SELECT * FROM Stu_

17、Course WHERE Stu_ID =a.Stu_ID) 思考题：查询所有选修了2号课程的学生的学号；查询所有未选2号课程的学生的学号。select Stu_ID from Student as awhere exists (select * from Stu_Course where Stu_ID=a.Stu_ID and Course_ID=2)select Stu_ID from Student as awhere not exists (select * from Stu_Course where Stu_ID=a.Stu_ID and Course_ID=2)5.4 子查询在IN

18、SERT、UPDATE、DELETE中的应用 5.4.1 在INSERT中嵌套子查询 在INSERT语句中嵌套子查询可以将其它表的数据行添加到当前表中。在INSERT语句中嵌套子查询比使用多个单行的INSERT语句效率要高得多。 嵌套子查询的INSERT语句的语法格式： INSERT INTO () SELECT FROM WHERE “”是接受数据的表； “”是提供数据的源表； “”是接受数据的表的列，它应与“”中的各列相对应。 例5-11：表Stu_Sdept结构与学生表（Student）相同，要求将学生表（Student）中的所有物理系学生的数据复制到Stu_Sdept表中。Use te

19、stdbGoCreate table Stu_Sdept( Stu_ID char(12) primary key, Stu_Name varchar(50), Stu_Passwd varchar(50), Ssex char(2) default(男) check(Ssex=男 or Ssex =女), Sage int, Sdept varchar(50)goINSERT INTO Stu_Sdept(Stu_ID, Stu_Name, Stu_Passwd, Ssex , Sage, Sdept) SELECT Stu_ID , Stu_Name , Stu_Passwd , Ssex

20、 , Sage , Sdept FROM Student WHERE Sdept=物理系 5.4.2 在UPDATE中嵌套子查询 在UPDATE语句中嵌套子查询实现根据其它表的信息更新当前表中的数据。 在UPDATE语句中嵌套子查询的语法格式： UPDATE SET = WHERE （SELECT FROM WHERE ） 说明： “”是被更新数据的表，“”是提供更新条件的表； “= ”是为更新列赋值； “”是更新表的列；“”是提供更新条件表的列； “”可以是关键字IN、NOT IN、EXISTS、NOT EXISTS，或是比较运算符、ALL+比较符、ANY+比较符。 例5-12：由于某种原因

21、，所有学生的“高等数学”成绩都少计算了5分，要求采用UPDATE语句对学生的成绩进行修正。 UPDATE Stu_course SET Score= Score+5WHERE Course_ID IN (SELECT Course_ID FROM Course WHERE Course_Name=高等数学) 思考题：将所有学生的“数据库系统概论”课程的成绩置0UPDATE Stu_course SET Score=0Where Course_ID in (select Course_ID from Course where Course_Name=数据库系统概论)5.4.3 在DELETE中嵌

22、套子查询 如果删除数据的条件涉及多个数据表，或删除操作的条件不只一个，则需要借助嵌套子查询来实现删除。 嵌套子查询的DELETE语句的语法格式： DELETE FROM WHERE （SELECT FROM WHERE ）说明： “”是要删除数据的表，“”是提供删除数据条件的表； “”是删除数据表的列；“”是提供删除数据条件表的列； “”可以是关键字IN、NOT IN、EXISTS、NOT EXISTS，或是比较运算符、ALL+比较符、ANY+比较符。 例5-13：删除所有学生“数据结构”课程的成绩。 DELETE FROM Stu_course WHERE Course_ID IN (SEL

23、ECT Course_ID FROM Course WHERE Course_Name=数据结构)select * from Stu_Course 5.5 集合操作 5.5.1 UNION运算符 UNION运算符可以将两个或两个以上SELECT语句的查询结果集合并成一个结果集。UNION运算不同于使用连接查询方式合并两个表中列的运算。 UNION的语法格式： SELECT语句1UNION ALL SELECT语句2UNION ALL UNION ALL SELECT语句n 说明： 关键字ALL表示将所有行（包括重复行）合并到结果集合中。如果缺省该关键字，则结果集合中重复行将只保留一行。 涉及两

24、个以上的SELECT语句的合并，执行顺序是自左至右，可以使用括号改变执行顺序。 所有SELECT语句中的列的数目必须相同； 所有SELECT语句中的列都必须具有相同的数据类型，或是可以自动转换的数据类型（对于数值类型，低精度的数据类型自动转换为高精度的数据类型）； 所有SELECT语句中的列的顺序必须相同，因为UNION运算符在合并时将按照各个SELECT语句给定的列顺序一对一地比较各列。 例5-14：将在课程表（Course）中查询得到的课程号1至4与课程号3至6的结果合并，要求合并的结果中包含课程编号、课程名称和学分信息。 SELECT Course_ID 课程编号 , Course_Na

25、me 课程名称 , Course_Creidt 学分 FROM Course WHERE Course_ID BETWEEN 1 AND 4 UNION ALL SELECT Course_ID 课程编号 , Course_Name 课程名称 , Course_Creidt 学分FROM CourseWHERE Course_ID BETWEEN 3 AND 6 例5-15：将在课程表（Course）中查询得到的课程号1至4与课程号3至6的结果合并，要求合并的结果中不能出现重复信息，结果包含课程编号、课程名称和学分信息。 SELECT Course_ID 课程编号 , Course_Name

26、课程名称 , Course_Creidt 学分 FROM Course WHERE Course_ID BETWEEN 1 AND 4 UNION SELECT Course_ID 课程编号 , Course_Name 课程名称 , Course_Creidt 学分FROM CourseWHERE Course_ID BETWEEN 3 AND 6 思考题：在Stu_Course表中，查询在4号和6号课程中，至少选修了一门课程的学生学号，要求不要出现重复行。SELECT Stu_ID FROM Stu_Course WHERE Course_ID=4UNION SELECT Stu_ID FR

27、OM Stu_Course WHERE Course_ID=6比较：SELECT distinct Stu_ID FROM Stu_Course WHERE Course_ID=4 or Course_ID=65.5.2 EXCEPT运算符 EXCEPT运算符实现两个SELECT语句的查询结果集的集合差操作，即从左查询中返回右查询没有找到的所有非重复数据行。 EXCEPT的语法格式： SELECT语句1EXCEPTSELECT语句2 “SELECT语句1”与“SELECT语句2”的查询结果集必须具有相同的结构，即它们的列数必须相同，相应的结果集列的数据类型必须相同或是可以自动转换的数据类型。 说明： 如果两个SELECT语句的查询返回的可比较列的