交通信息系统第八章结构化查询语言SQL2

1、1 主要内容主要内容vSQLSQL概述概述 vSQLSQL的数据定义的数据定义vSQLSQL的数据查询的数据查询 v数据更新数据更新 v嵌入式嵌入式SQL SQL vTransact SQLTransact SQL语言概述语言概述 24.3 SQL的数据查询的数据查询 主要内容主要内容 SELECTSELECT语句格式语句格式 单表查询单表查询 聚集函数聚集函数 数据分组数据分组 多表查询多表查询 集合操作集合操作3学号学号SNO姓名姓名SNAME性别性别SEX年龄年龄AGE所在系所在系SDEPT200915121200915122200915123200915125李小勇李小勇刘刘 晨晨王洪

2、敏王洪敏张张 力力MFFM20191819CSCSMAIS学生表学生表：S(S(SNOSNO,SNAME,SEX,AGE,SDEPT) ,SNAME,SEX,AGE,SDEPT) 课程表：课程表：C(C(CNOCNO,CNAME,CPNO,CCREDIT) ,CNAME,CPNO,CCREDIT) 学生选课表学生选课表：SC(SC(SNO,CNOSNO,CNO,GRADE) ,GRADE) 例例4.14.1 定义一个定义一个教务管理数据库教务管理数据库JWGLJWGL，它包含，它包含3 3个表：个表：4课程号课程号CNO课程名课程名CNAME先修课先修课CPNO学分学分CCREDIT12345

3、67数据库数据库数学数学信息系统信息系统操作系统操作系统数据结构数据结构数据处理数据处理C语言语言516764243424课程表实例课程表实例学号学号SNO课程号课程号CNO成绩成绩GRADE2009151212009151212009151212009151222009151221232392858890805 4.3.1 SELECT 4.3.1 SELECT语句语句格式格式1 1、SELECTSELECTFROMFROMWHEREWHERE句型句型在关系代数中最常用的式子是下列表达式：在关系代数中最常用的式子是下列表达式： A1,An(F(R1Rm) 这里这里R1、Rm为关系，为关系，F

4、是公式，是公式，A1、An为属性。为属性。针对上述表达式，针对上述表达式，SQL为此设计了为此设计了SELECTFROMWHERE句型：句型：SELECT A1，AnFROM R1，RmWHERE F 这个句型是从关系代数表达式演变来的，但这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达子句中的条件表达式式F要比关系代数中公式更灵活要比关系代数中公式更灵活。6v在在WHERE子句的条件表达式子句的条件表达式F中可使用下列运算符中可使用下列运算符n算术比较运算符：算术比较运算符：、 、 = 、 、 !=n逻辑运算符：逻辑运算符：AND、OR、NOTn集合成员资格运算符：集合成员资

5、格运算符：IN、NOT INn谓词：谓词：EXISTS、ALL、SOME、UNIQUEn聚合函数：聚合函数：AVG、MIN、MAX、SUM、COUNTnF中运算对象还可以是另一个中运算对象还可以是另一个SELECT语句，即语句，即SELECT句型可以嵌套句型可以嵌套2 2、SELECTSELECT语句完整的句法如下：语句完整的句法如下： SELECTSELECT 目标表的列名或列表达式序列目标表的列名或列表达式序列 FROMFROM 基本表名和（或）视图序列基本表名和（或）视图序列 WHEREWHERE 行条件表达式行条件表达式 GROUP BYGROUP BY 列名序列列名序列 HAVING

6、 HAVING 组条件表达式组条件表达式 ORDER BYORDER BY 列名列名 ASC|DESC ASC|DESC ， 主语句主语句SELECT-FROM-WHERE的含义是：的含义是： 根据根据WHERE子句的条件表达式，从子句的条件表达式，从FROM子句指定的基本表子句指定的基本表或视图中找出满足条件的元组，再按或视图中找出满足条件的元组，再按SELECT子句中的目标列表达子句中的目标列表达式，选出元组中的属性值形成结果表。式，选出元组中的属性值形成结果表。 8整个语句的执行过程如下：整个语句的执行过程如下：(1) (1) 读取读取FROMFROM子句中基本表、视图的数据，执行笛卡尔

7、积操作。子句中基本表、视图的数据，执行笛卡尔积操作。(2) (2) 选取满足选取满足WHEREWHERE子句中给出的条件表达式的元组。子句中给出的条件表达式的元组。(3) (3) 按按GROUPGROUP子句中指定列的值分组，同时提取满足子句中指定列的值分组，同时提取满足HAVINGHAVING子句中子句中组条件表达式的那些组。组条件表达式的那些组。(4) (4) 按按SELECTSELECT子句中给出的列名或列表达式求值输出。子句中给出的列名或列表达式求值输出。(5) ORDER(5) ORDER子句对输出的目标表进行排序，按附加说明子句对输出的目标表进行排序，按附加说明ASCASC升序排列

8、升序排列，或按，或按DESCDESC降序排列。降序排列。9根据查询条件不同，可分为：根据查询条件不同，可分为：v单表查询单表查询v连接查询连接查询v嵌套查询嵌套查询v集合查询集合查询104.3.2 单表查询单表查询查询仅涉及一个表，是一种最简单的查询操作。查询仅涉及一个表，是一种最简单的查询操作。一、选择表中的若干列一、选择表中的若干列二、选择表中的若干元组二、选择表中的若干元组三、对查询结果排序三、对查询结果排序四、使用聚合函数四、使用聚合函数五、对查询结果分组五、对查询结果分组 111、查询表中的若干列、查询表中的若干列 查询指定列查询指定列 在很多情况下，用户只对表中的一部分属性列感兴趣

9、在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在，这时可以通过在SELECT子句的子句的中指中指定要查询的属性列。定要查询的属性列。 例例4.21 查询全体学生的学号与姓名。查询全体学生的学号与姓名。 SELECT SNO,SNAME FROM S; 执行过程：从执行过程：从S表中取出一个元组，再取出该元组在属表中取出一个元组，再取出该元组在属性性SNO和和SNAME上的值，形成一个新的元组作为输出。上的值，形成一个新的元组作为输出。对对S表中的所有元组做相同的处理，最后形成一个结果关表中的所有元组做相同的处理，最后形成一个结果关系作为输出。系作为输出。12 查询全部列查询全部列

10、 将表中的所有属性列都选出来，可以有两种方法。一将表中的所有属性列都选出来，可以有两种方法。一种方法就是在种方法就是在SELECTSELECT关键字后面列出所有列名；另一种方关键字后面列出所有列名；另一种方法是如果列的显示顺序与其在基表中的顺序相同，也可以法是如果列的显示顺序与其在基表中的顺序相同，也可以简单地将简单地将 指定为指定为“* *”。 例例4.23 4.23 查询全体学生的详细记录。查询全体学生的详细记录。 SELECT SELECT * * FROM S; FROM S; 等价于：等价于： SELECT SNO,SNAME,SEX,AGE,SDEPT SELECT SNO,SNA

11、ME,SEX,AGE,SDEPT FROM S; FROM S; 13 (3) 查询经过计算的值查询经过计算的值 SELECTSELECT子句的子句的 不仅可以是表中的属性列，也可不仅可以是表中的属性列，也可以是表达式。以是表达式。 例例4.24 4.24 查询全体学生的姓名及其出生年份。查询全体学生的姓名及其出生年份。 SELECT SNAME,2009-AGESELECT SNAME,2009-AGE FROM S; FROM S; 输出结果：输出结果： Sname Sname _ _ 李小勇李小勇 19891989 刘晨刘晨 19901990 王名王名 19911991 张立张立 199

12、0 1990 142、选择表中的若干元组、选择表中的若干元组 消除取值重复的行消除取值重复的行 两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行了，可以用相同的行了，可以用DISTINCT取消它们。取消它们。 例例4.26 查询选修了课程的学生学号。查询选修了课程的学生学号。 SELECT SNO FROM SC; 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定必须指定DISTINCT关键词：关键词： SELECT DISTINCT

13、SNO FROM SC; 15(1) SELECT Sno(1) SELECT Sno FROM SC; FROM SC;或或( (默认默认 ALL)ALL) SELECT ALL Sno SELECT ALL Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 95001 95001 95001 95001 95001 95002 95002 95002 95002 (2) SELECT DISTINCT Sno(2) SELECT DISTINCT Sno FROM SC; FROM SC; 结果：结果： Sno Sno - - 95001 950

14、01 95002 95002 16 查询满足条件的元组查询满足条件的元组查询满足指定条件的元组可以通过查询满足指定条件的元组可以通过WHERE子句实现。子句实现。查查 询询 条条 件件谓谓 词词比较比较确定范围确定范围确定集合确定集合字符匹配字符匹配空空 值值多重条件（逻辑运算）多重条件（逻辑运算）= =， ， =，=，!=!=，!，!=20 AND AGE =23；19确定集合确定集合 谓词谓词IN可以用来查找属性值属于指定集合的元组。可以用来查找属性值属于指定集合的元组。 例例4.32 查询计算机科学系（查询计算机科学系（CS）、数学系（）、数学系（MA）和信息系（）和信息系（IS）学）学

15、生的姓名和性别。生的姓名和性别。 SELECT SNAME,SEX FROM S WHERE SDEPT IN(CS,MA,IS); 与与IN相对的谓词是相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。，用于查找属性值不属于指定集合的元组。 例例4.33 查询既不是计算机科学系、数学系，也不是信息系的学生的姓查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。名和性别。 SELECT SNAME,SEX FROM S WHERE SDEPT NOT IN(CS,MA,IS); 20字符匹配谓词字符匹配谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：可以用来进

16、行字符串的匹配。其一般语法格式如下： NOT LIKE ESCAPE其含义是查找指定的属性列值与其含义是查找指定的属性列值与相匹配的元组。相匹配的元组。可以是一个完整的字符串，也可以含有通配符可以是一个完整的字符串，也可以含有通配符%和和_。其中：。其中： %（百分号）（百分号）代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串。）的字符串。例如例如a%b表示以表示以a开头，以开头，以b结尾的任意长度的字符串。如结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。等都满足该匹配串。_（下划线）（下划线）代表任意单个字符。例如代表任意单个字符。例如a_b表示以表示以a开

17、头，以开头，以b结尾的长结尾的长度为度为3的任意字符串。如的任意字符串。如acb，afb等都满足该匹配串。等都满足该匹配串。21例例4.34 查询学号为查询学号为200915121的学生的详细情况。的学生的详细情况。 SELECT * FROM S WHERE SNO LIKE 200915121; 等价于：等价于： SELECT * FROM S WHERE SNO=200915121;如果如果LIKE后面的匹配串中不含通配符，则可以用后面的匹配串中不含通配符，则可以用=（等于）运算符取代（等于）运算符取代LIKE谓词，用谓词，用!=或或（不等于）运算符取代（不等于）运算符取代NOT LIK

18、E谓词。谓词。例例4.35 查询所有姓刘的学生的姓名、学号和性别。查询所有姓刘的学生的姓名、学号和性别。 SELECT SNAME,SNO,SEX FROM S WHERE SNAME LIKE 刘刘%; 22 涉及空值的查询例涉及空值的查询例例例4.41 某些学生选修课程后没有参加考试，所以有选课记录，但没有某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT SNO,CNO FROM SC WHERE GRADE IS NULL; /* 分数分数GRADE是空值是空值 */

19、多重条件查询多重条件查询可用逻辑运算符可用逻辑运算符AND和和OR来联结多个查询条件。来联结多个查询条件。AND的优先级高于的优先级高于OR，但可以用括号改变优先级。，但可以用括号改变优先级。例例4.43 查询计算机科学系年龄在查询计算机科学系年龄在20岁以下的学生姓名。岁以下的学生姓名。 SELECT SNAME FROM S WHERE SDEPT=CS AND AGE20; 233、ORDER BY子句（子句（对查询结果排序对查询结果排序）使用使用ORDER BY子句子句v 可以按一个或多个属性列排序可以按一个或多个属性列排序v 升序：升序：ASC；降序：；降序：DESC；缺省值为升序；

20、缺省值为升序当排序列含空值时当排序列含空值时vASC：排序列为空值的元组最后显示：排序列为空值的元组最后显示vDESC：排序列为空值的元组最先显示：排序列为空值的元组最先显示24 例例4.44 查询选修了查询选修了3号课程的学生的学号及其成绩，查询结号课程的学生的学号及其成绩，查询结果按分数的降序排列。果按分数的降序排列。 SELECT SNO,GRADE FROM SC WHERE CNO=3 ORDER BY GRADE DESC; Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55

21、查询结果查询结果25 聚合函数是涉及整个关系的另一类运算操作聚合函数是涉及整个关系的另一类运算操作, ,通过聚合函通过聚合函数，可以把某一列中的值形成单个值。数，可以把某一列中的值形成单个值。 5类主要聚合函数：类主要聚合函数：n计数计数COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）n计算总和计算总和SUM（DISTINCT|ALL ）n 计算平均值计算平均值AVG（DISTINCT|ALL ）4.3.3 聚合函数聚合函数26n求最大值求最大值MAX（DISTINCT|ALL ）n求最小值求最小值 MIN（DISTINCT|ALL ）DISTINCT短语：在

22、计算时要取消指定列中的重复值短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值短语：不取消重复值ALL为缺省值为缺省值27例例4.47 查询选修了课程的学生人数。查询选修了课程的学生人数。 SELECT COUNT(DISTINCT SNO) FROM SC;学生每选修一门课，在学生每选修一门课，在SC中都有一条相应的记录。一个学生要选修多中都有一条相应的记录。一个学生要选修多门课程，为避免重复计算学生人数，必须在门课程，为避免重复计算学生人数，必须在COUNT函数中用函数中用DISTINCT短短语。语。例例4.48 计算选修计算选修l号课程的学生平均成绩。号课程的学生平均成绩。 S

23、ELECT AVG(GRADE) FROM SC WHERE CNO=1;例例4.49 查询选修查询选修l号课程的学生最高分数。号课程的学生最高分数。 SELECT MAX(GRADE) FROM SC WHER CNO=1; 28使用使用GROUP BY子句分组子句分组 细化聚合函数的作用对象细化聚合函数的作用对象n 未对查询结果分组，聚合函数将作用于整个查询结果未对查询结果分组，聚合函数将作用于整个查询结果n 对查询结果分组后，聚合函数将分别作用于每个组对查询结果分组后，聚合函数将分别作用于每个组 4.3.4 对查询结果分组对查询结果分组 29这三个数取平均值第4-6分数取平均值最后三个数

24、取平均值有一个学员参加考试例如，求每门课的平均成绩。例如，求每门课的平均成绩。30分组查询分组查询GROUP BYSELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID31 例例4.51 求各个课程号及相应的选课人数。求各个课程号及相应的选课人数。 SELECT CNO,COUNT(SNO) FROM SC GROUP BY CNO; 该语句对查询结果按该语句对查询结果按CNO的值分组，所有具有相同的值分组，所有具有相同CNO值的元组为一组，然后对每一组作用聚合函数值的元组为一组，然后对每一组作用聚合函数COUNT计算，

25、以求得该组的学生人数。计算，以求得该组的学生人数。 结果 Cno COUNT(Sno)1 22 2 34 3 44 4 33 5 4832注意：注意：vGROUP BY子句的作用对象是查询的中间结果表子句的作用对象是查询的中间结果表v分组方法：按指定的一列或多列值分组，值相等的为一组分组方法：按指定的一列或多列值分组，值相等的为一组v使用使用GROUP BY子句后，子句后，SELECT子句的列名列表中只能子句的列名列表中只能出现分组属性和聚合函数。出现分组属性和聚合函数。33v使用使用HAVING短语筛选最终输出结果。短语筛选最终输出结果。例例 查询选修了查询选修了3门以上课程的学生学号。门以

26、上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；34例例 查询有查询有3门以上课程是门以上课程是90分以上的学生的学号及（分以上的学生的学号及（90分以上分以上的）课程数。的）课程数。 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;35使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果v只有满足只有满足HAVING短语指定条件的组才输出。短语指定条件的组才输出。vHAVING短语与短语与WHERE子句的区别：作

27、用对象不同子句的区别：作用对象不同nWHERE子句作用于基表或视图，从中选择满足条件的元子句作用于基表或视图，从中选择满足条件的元组。组。nHAVING短语作用于组，从中选择满足条件的组短语作用于组，从中选择满足条件的组。 WHEREGROUP BYHAVING36在多表查询中，如果要引用不同关系中的同名属性，则需要在属性名在多表查询中，如果要引用不同关系中的同名属性，则需要在属性名前加关系名，即用前加关系名，即用“关系名关系名.属性名属性名”的形式表示，以便区分。的形式表示，以便区分。1、连接查询、连接查询主要类型：主要类型：n 广义笛卡尔积广义笛卡尔积n 等值连接等值连接(含自然连接）与含

28、自然连接）与 非等值连接查询非等值连接查询n 自身连接查询自身连接查询n 外连接查询外连接查询n 复合条件连接查询复合条件连接查询4.3.5 多表查询多表查询37广义笛卡尔积广义笛卡尔积 v不带连接谓词的连接不带连接谓词的连接v 很少使用很少使用例：例： SELECT Student.* , SC.* FROM Student, SC38 等值与非等值连接查询等值与非等值连接查询 连接查询的连接查询的WHERE子句中用来连接两个表的条件称为子句中用来连接两个表的条件称为连接条件或连接谓词，连接条件或连接谓词，其一般格式为：其一般格式为： . .其中比较运算符主要有：其中比较运算符主要有：=、=

29、、=、!=（或（或）等。）等。 此外连接谓词还可以使用下面形式：此外连接谓词还可以使用下面形式： . BETWEEN . AND . 39 例例4.53 查询每个学生及其选修课程的情况。查询每个学生及其选修课程的情况。 学生情况存放在学生情况存放在S表中，学生选课情况存放在表中，学生选课情况存放在SC表中，所表中，所以本查询实际上涉及以本查询实际上涉及S与与SC两个表。这两个表之间的联系是通两个表。这两个表之间的联系是通过公共属性过公共属性SNO实现的。实现的。 SELECT S.*,SC.* FROM S,SC WHERE S.SNO=SC.SNO; /* 将将S与与SC中同一学生的元组连接

30、起来中同一学生的元组连接起来 */ 执行步骤：首先在表S中找到第一个元组，然后从头开始扫描SC表，逐一查找与S第一个元组的SNO相等的SC元组，找到后就将S中的第一个元组与该元组拼接起来，形成结果表中一个元组。SC全部查找完后，再找S中第二个元组，然后再从头开始扫描SC，逐一查找满足连接条件的元组，找到后就将S中的第二个元组与该元组拼接起来，再形成结果表中一个元组。重复上述操作，直到S中的全部元组都处理完毕为止。这就是循环嵌套算法的基本思想 。40(2) 自身连接自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连

31、接，称为表的连接，称为表的自身连接自身连接。例例4.55 查询每一门课的间接先修课（即先修课的先修课）。查询每一门课的间接先修课（即先修课的先修课）。在在C表中，只有每门课的直接先修课信息，而没有先修课的先修课。要表中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就将查找它的先修课程。这就将C表与其自身连接。为此，要为表与其自身连接。为此，要为C表取两个别名表取两个别名，一个是，一个是FIRST，另一个是，另一个是SECOND。该查询的该查

32、询的SQL语句为：语句为： SELECT FIRST.CNO,SECOND.CPNO FROM C FIRST,C SECOND WHERE FIRST.CPNO=SECOND.CNO; 41(3) 外连接外连接v外连接与普通连接的区别外连接与普通连接的区别n普通连接操作只输出满足连接条件的元组普通连接操作只输出满足连接条件的元组n外连接操作以指定表为连接主体，将主体表中不满足外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出连接条件的元组一并输出v外连接外连接左外连接左外连接(LEFT JOIN)右外连接右外连接(RIGHT JOIN)完整外连接完整外连接(FULL JOI

33、N)42例例4.56 查询每个学生及其选修课程的情况。查询每个学生及其选修课程的情况。 SELECT S.SNO,SNAME,SEX,AGE,SDEPT,CNO,GRAGE FROM S LEFT JOIN SC ON(S.SNO=SC.SNO);v例子：例子： - a表 id name b表 id job parent_id 1 张3 1 23 1 2 李四 2 34 2 3 王武 3 34 4 a.id同parent_id 存在关系 v左连接 select a.*,b.* from a left join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四

34、 2 34 2 3 王武 null 43v右连接 select a.*,b.* from a right join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 44v 完全连接 select a.*,b.* from a full join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null4546(4) 复合条件连接复合条件连接WHERE子句中可以有多个连接条件，称为复合条件连接。子句中可以有多个连接条件，称为复合条件连接

35、。例例4.57 查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有学生的学号和姓名。分以上的所有学生的学号和姓名。 SELECT S.SNO,SNAME FROM S,SC WHERE S.SNO=SC.SNO AND SC.CNO=2 AND SC.GRADE90; 该查询的一种优化（高效）的执行过程是先从该查询的一种优化（高效）的执行过程是先从SC中挑选出中挑选出CNO=2并并且且GRADE90的元组形成一个中间关系，再和的元组形成一个中间关系，再和S中满足连接条件的元组进中满足连接条件的元组进行连接得到最终的结果关系。行连接得到最终的结果关系。 2、嵌套查询、嵌套查询 在在S

36、QL语言中，一个语言中，一个SELECTFROMWHERE语句称为语句称为一个查询块。将一个查询块嵌套在另一个查询块的一个查询块。将一个查询块嵌套在另一个查询块的WHERE子子句或句或HAVING短语的条件中的查询称为短语的条件中的查询称为嵌套查询（嵌套查询（nested query）。 带有带有IN谓词的子查询谓词的子查询 例例4.59 查询与查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。先分步来完成此查询，然后再构造嵌套查询。先分步来完成此查询，然后再构造嵌套查询。 确定确定“刘晨刘晨”所在系名所在系名 SELECT SDEPT FROM S WHERE SNAME=刘晨刘

37、晨;结果为：结果为：CS 查找所有在查找所有在CS系学习的学生。系学习的学生。 SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT=CS; 48将第一步查询嵌人到第二步查询的条件中，嵌套查询如下：将第一步查询嵌人到第二步查询的条件中，嵌套查询如下： SELECT SNO,SNAME,SDEPT FROM S WHERE SDEPT IN (SELECT SDEPT FROM S WHERE SNAME=刘晨刘晨 ); 本例中，子查询的查询条件不依赖于父查询，称为本例中，子查询的查询条件不依赖于父查询，称为不相关子不相关子查询查询。一种求解方法是由里向外处理，即先

38、执行子查询，子查询的一种求解方法是由里向外处理，即先执行子查询，子查询的结果用于建立其父查询的查找条件。结果用于建立其父查询的查找条件。 49本查询涉及学号、姓名和课程名本查询涉及学号、姓名和课程名3个属性。学号和姓名存放个属性。学号和姓名存放在在S表中，课程名存放在表中，课程名存放在C表中，但表中，但S与与C两个表之间没有直接两个表之间没有直接联系，必须通过联系，必须通过SC表建立它们二者之间的联系。所以本查询表建立它们二者之间的联系。所以本查询实际上涉及实际上涉及3个关系。个关系。 SELECT SNO,SNAME /* 外层在外层在S关系中取出关系中取出SNO和和SNAME */ FRO

39、M SWHERE SNO IN (SELECT SNO /* 在在SC关系中找出选修了关系中找出选修了3号课程的学生学号号课程的学生学号 */ FROM SC WHERE CNO IN (SELECT CNO /* 在在C关系中找出关系中找出“信息系统信息系统”的课的课 FROM C 程号，结果为程号，结果为3号号 */ WHERE CNAME=信息系统信息系统 ) ); 例例4.604.60 查询选修了课程名为查询选修了课程名为“信息系统信息系统”的学生学号和姓的学生学号和姓名。名。50本查询同样可以用连接查询实现：本查询同样可以用连接查询实现： SELECT S.SNO,SNAME FRO

40、M S,SC,C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CNAME=信息系统信息系统; 说明说明: 有些嵌套查询可以用连接运算替代，有些是不能替代的有些嵌套查询可以用连接运算替代，有些是不能替代的。如果子查询的查询条件依赖于父查询，这类子查询称为如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询相关子查询。51v当能确切知道内层查询返回单值时，可用比较运算符（当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或或）。）。v与与ANY或或ALL谓词配合使用。谓词配合使用。（2 2） 带有比较运算符的子查询带有比较运算符的子查询52

41、v例如在例例如在例4.59中，由于一个学生只可能在一中，由于一个学生只可能在一个系学习，则在因此可以个系学习，则在因此可以用用 = 代替代替IN ：SELECT SNO， SNAME ， SDEPT FROM S WHERE SDEPT = （SELECT SDEPT FROM S WHERE SNAME= 刘晨刘晨 ）；）；53 注意：注意：子查询一定要跟在比较符之后！子查询一定要跟在比较符之后！ 错误的例子错误的例子： SELECT SNO， SNAME ， SDEPT FROM S WHERE ( SELECT SDEPT FROM S WHERE SNAME= 刘晨刘晨 ) = SDE

42、PT ；54例例4.61 找出每个学生超过他选修课程平均成绩的找出每个学生超过他选修课程平均成绩的课程号。课程号。 SELECT SNO,CNO FROM SC X WHERE GRADE=( SELECT AVG(GRADE) FROM SC Y WHERE Y.SNO=X.SNO); 55谓词语义谓词语义vANY：任意一个值：任意一个值vALL：所有值：所有值（3 3）带有）带有ANYANY或或ALLALL谓词的子查询谓词的子查询需要配合使用比较运算符：需要配合使用比较运算符： ANY ANY大于子查询结果中的某个值大于子查询结果中的某个值 ALL ALL大于子查询结果中的所有值大于子查询

43、结果中的所有值 ANY ANY小于子查询结果中的某个值小于子查询结果中的某个值 ALL= ANY= ANY大于等于子查询结果中的某个值大于等于子查询结果中的某个值 = ALL= ALL大于等于子查询结果中的所有值大于等于子查询结果中的所有值= ANY= ANY小于等于子查询结果中的某个值小于等于子查询结果中的某个值 = ALL= ALL小于等于子查询结果中的所有值小于等于子查询结果中的所有值= ANY= ANY等于子查询结果中的某个值等于子查询结果中的某个值 =ALL =ALL 等于子查询结果中的所有值（没有实际意义）等于子查询结果中的所有值（没有实际意义）!=!=（或（或）ANYANY 不等

44、于子查询结果中的某个值不等于子查询结果中的某个值!=!=（或（或）ALLALL 不等于子查询结果中的任何一个值不等于子查询结果中的任何一个值57例例4.62 查询其他系中比计算机科学系某一学生年龄小的学查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。生姓名和年龄。 SELECT SNAME,AGE FROM S WHERE AGE ANY(SELECT AGE FROM S WHERE SDEPT=CS) AND SdeptCS; 注意这是父查询注意这是父查询块中的条件块中的条件 RDBMS RDBMS执行此查询时，首先处理子查询，找出执行此查询时，首先处理子查询，找出CSCS系中所

45、有学生的系中所有学生的年龄，构成一个集合（年龄，构成一个集合（2020，1919）。然后处理父查询，找所有不是）。然后处理父查询，找所有不是CSCS系且年龄小于系且年龄小于2020或或l9l9的学生。的学生。 58ANY和和ALL谓词有时可以用聚集函数实现谓词有时可以用聚集函数实现ANY与与ALL与聚集函数的对应关系与聚集函数的对应关系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX 用聚集函数实现子查询通常比直接用用聚集函数实现子查询通常比直接用ANY或或ALL查查询效率要高，因为前者通常能够减少比较次数。询效率要高，因为前者通常能够

46、减少比较次数。59【例例4.624.62】可以用聚集函数实现。可以用聚集函数实现。 SELECT SNAMESELECT SNAME，AGEAGE FROM S FROM S WHERE AGE WHERE AGE (SELECT (SELECT MAX(AGE)MAX(AGE) FROM S FROM S WHERE SDEPT= WHERE SDEPT= CS )CS ) AND SDEPT AND SDEPT CS CS ; ;60例例4.63 4.63 查询其他系中比计算机科学系查询其他系中比计算机科学系所有所有学生年龄学生年龄都都小小的学生姓名及年龄。的学生姓名及年龄。 用用ALL谓

47、词谓词SELECT SNAME，AGEFROM SWHERE AGE ALL (SELECT AGE FROM S WHERE SDEPT= CS ) AND SDEPT CS ;查询结果为空表。查询结果为空表。61 用聚集函数用聚集函数 SELECT SNAME，AGE FROM S WHERE AGE (SELECT MIN(AGE) FROM S WHERE SDEPT = CS ) AND SDEPT CS ;62(4) (4) 带有带有EXISTSEXISTS谓词的子查询谓词的子查询EXISTS谓词谓词NOT EXISTS谓词谓词不同形式的查询间的替换不同形式的查询间的替换相关子查询

48、的效率相关子查询的效率用用EXISTS/NOT EXISTS实现全称量词实现全称量词用用EXISTS/NOT EXISTS实现逻辑蕴函实现逻辑蕴函63EXISTSEXISTS谓词谓词存在量词存在量词 带有带有EXISTSEXISTS谓词的子查询不返回任何数据，只产生逻谓词的子查询不返回任何数据，只产生逻辑真值辑真值“truetrue”或逻辑假值或逻辑假值“falsefalse”。 若内层查询结果非空，则返回若内层查询结果非空，则返回真值真值 若内层查询结果为空，则返回若内层查询结果为空，则返回假值假值由由EXISTSEXISTS引出的子查询，其目标列表达式通常都用引出的子查询，其目标列表达式通

49、常都用* * ，因为带，因为带EXISTSEXISTS的子查询只返回真值或假值，给出列的子查询只返回真值或假值，给出列名无实际意义。名无实际意义。NOT EXISTSNOT EXISTS谓词谓词64例例4.644.64 查询所有选修了查询所有选修了1 1号课程的学生姓名。号课程的学生姓名。 分析：分析： 本查询涉及本查询涉及S S和和SCSC关系。关系。 在在S S中依次取每个元组的中依次取每个元组的SNOSNO值，用此值去检查值，用此值去检查SCSC关系。关系。 若若SCSC中存在这样的元组，其中存在这样的元组，其SNOSNO值等于此值等于此S. SNOS. SNO值，并值，并且其且其CNO

50、= CNO= 1 1，则取此，则取此S.SNAMES.SNAME送入结果关系。送入结果关系。65用嵌套查询用嵌套查询 SELECT SNAMESELECT SNAME FROM FROM S S WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM SC / FROM SC /* *相关子查询相关子查询* */ / WHERE SNO= WHERE SNO=S.SNOS.SNO AND AND CNO= 1)CNO= 1)；66n用连接运算用连接运算SELECT SNAMESELECT SNAMEFROM S, SCFROM S, SCWHERE

51、 S.SNO =SC.SNO AND SC.CNO= 1;WHERE S.SNO =SC.SNO AND SC.CNO= 1;67例例4.65 4.65 查询没有选修查询没有选修1 1号课程的学生姓名。号课程的学生姓名。 SELECT SNAMESELECT SNAME FROM FROM S S WHERE NOT EXISTS WHERE NOT EXISTS (SELECT (SELECT * * FROM SC FROM SC WHERE SNO = WHERE SNO = S.S.SNO AND CNO=1)SNO AND CNO=1)；此例用连接运算难于实现此例用连接运算难于实现!

52、 ! 68n一些一些带带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词的子查询不能被其他形式的子查询谓词的子查询不能被其他形式的子查询等价替换等价替换n所有所有带带ININ谓词、比较运算符、谓词、比较运算符、ANYANY和和ALLALL谓词的子查询都能用带谓词的子查询都能用带EXISTSEXISTS谓词的子查询等价替换。谓词的子查询等价替换。 例例4.654.65 （例（例4.594.59另解）查询与另解）查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。 SELECT SNO,SNAME,SDEPT SELECT SNO,SNAME,SDEPT FROM

53、 S S1 FROM S S1 WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM S S2 FROM S S2 WHERE S2.SDEPT=S1.SDEPT AND S2.SNAME= WHERE S2.SDEPT=S1.SDEPT AND S2.SNAME=刘晨刘晨); ); 69SQL中没有全称量词（中没有全称量词（FOR ALL）。可将题目的意思转换成等价的形）。可将题目的意思转换成等价的形式：查询这样的学生，没有一门课程是他不选修的。式：查询这样的学生，没有一门课程是他不选修的。其其SQL语句为：语句为： SELECT SNAME

54、FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXlSTS (SELECT * FROM SC WHERE SNO=S.SNO AND CNO=C.CNO); 从而用从而用EXIST/NOT EXIST来实现带全称量词的查询。来实现带全称量词的查询。 例例4.664.66 查询选修了全部课程的学生姓名。查询选修了全部课程的学生姓名。70本查询可以用逻辑蕴涵来表达：查询学号为本查询可以用逻辑蕴涵来表达：查询学号为x的学生，对所有的课程的学生，对所有的课程y，只要，只要200915122学生选修了课程学生选修了课程y，则，则x也选修了也选修了

55、y。形式化表示：形式化表示：用用p p表示谓词表示谓词 “学生学生200915122选修了课程选修了课程y y”用用q q表示谓词表示谓词 “学生学生x x选修了课程选修了课程y y”则上述查询为则上述查询为: (: ( y) p y) p q q 。等价变换：等价变换： ( y)p q ( y ( (p q ) ( y ( ( p q) y(p q)将语义表达为将语义表达为：不存在这样的课程：不存在这样的课程y，学生，学生200915122选修选修了了y，而学生，而学生x没有选。没有选。 例例4.674.67 查询至少选修了学生查询至少选修了学生200915122200915122选修的全部

56、课程的学生号码。选修的全部课程的学生号码。71SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.SNO=200915122 AND NOT EXISTS ( SELECT * FROM SC SCZ WHERE SCZ.SNO=SCX.SNO AND SCZ.CNO=SCY.CNO ); 72v标准标准SQLSQL直接支持的集合操作种类直接支持的集合操作种类n并操作并操作(UNION)(UNION)v一般商用数据库支持的集合操作种类一般商用数据库支持的集合操作种类n并操作并操作(UN

57、ION)(UNION)n交操作交操作(INTERSECT)(INTERSECT)n差操作差操作(MINUS)(MINUS)4.3.6 集合操作集合操作73vSELECTSELECT语句的查询结果是元组的集合，所以多个语句的查询结果是元组的集合，所以多个SELECTSELECT语句的结果可进行集合操作。语句的结果可进行集合操作。 v形式形式 UNION n参加参加UNION操作的各结果表的列数必须相同；对应项的操作的各结果表的列数必须相同；对应项的数据类型也必须相同数据类型也必须相同74v例例4.68 查询计算机科学系的学生及年龄不大于查询计算机科学系的学生及年龄不大于19岁的学生岁的学生。 S

58、ELECT SELECT * * FROM S FROM S WHERE SDEPT= WHERE SDEPT=CSCS UNIONUNION SELECT SELECT * * FROM S FROM S WHERE AGE=19; WHERE AGE=19; v本查询实际上是求计算机科学系的所有学生与年龄不大于本查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集。使岁的学生的并集。使UNION将多个查询结果合并起来时，系将多个查询结果合并起来时，系统会自动去掉重复元组。如果要保留重复元组则需要换为统会自动去掉重复元组。如果要保留重复元组则需要换为UNION ALL操作符。操

59、作符。 SELECT SNO SELECT SNO FROM SC FROM SC WHERE CNO=WHERE CNO=l lINTERSECTINTERSECTSELECT SNOSELECT SNOFROM SCFROM SCWHERE CNO=WHERE CNO=2 2； 本例也可以表示为本例也可以表示为: : SELECT SNO SELECT SNO FROM SC FROM SC WHERE CNO= WHERE CNO=1 1 AND SNO IN AND SNO IN ( SELECT SNO FROM SC ( SELECT SNO FROM SC WHERE CNO=

60、WHERE CNO=2 2); ); 例例4.71 4.71 查询既选修了查询既选修了1 1号课程又选修了号课程又选修了2 2号课程的全体学生。号课程的全体学生。就是查询选修就是查询选修1 1号课程的学生集合与选修号课程的学生集合与选修2 2号课程的学生集合号课程的学生集合的交集。的交集。76SELECTSELECT ALL|DISTINCT ALL|DISTINCT 别名别名 ， 别名别名 FROMFROM 别名别名 ， 别名别名 WHEREWHERE GROUP BYGROUP BY 1， . . HAVING HAVING ORDER BYORDER BY ASC|DESC 2 ASC|