学习数据库技术全第3章语言

华东理工大学信息学院

数据库技术IntroductiontoDatabaseTechnology第3章关系据库标准语言--SQL

掌握了解SQL语言的概念和定义SQL语言发展的过程SQL语言的基本概念SQL语言的特点体会面向过程的语言和SQL语言的区别和优点体会关系数据库系统为数据库应用系统的开发提供良好的环境，减轻用户负担，提高用户生产率的原因需要举一反三的：1熟练而正确地使用SQL语言完成对数据库的各种各样的查询。掌握插入操作。掌握删除操作。掌握更新操作。掌握创建视图的方法难点:用SQL语言正确完成对数据库复杂查询。在学习的过程中一定要多练习，并在SQLServer上进行实际运行，检查查询的结果是否正确。只有通过大量练习，才能真正达到举一反三的熟练程度。第3章关系数据库标准语言--SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.1SQL概述SQL的特点：1综合统一2高度非过程化3面向集合的操作方式4以同一种语法结构提供两种使用方式5语言简洁，易学易用SQL对关系数据库模式的支持一个关系就对应于一个基本表数据库的内模式只存放视图的定义而不存放视图对应的数据从一个或几个基本表导出的表。它本身不独立存储在数据库中

学生-课程数据库本章用学生-课程数据库作为一个例子来讲解SQL的数据定义、数据操纵、数据查询和数据控制语句的具体应用。该数据库有三个表：学生表：Student(Sno,Sname,Ssex,Sage,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)学生选课表：SC(Sno,Cno,Grade)注：加下划线的为该关系的主码。3.2数据定义3.2.1基本表的定义、删除与修改3.2.2索引的建立与删除3.2.1基本表定义、删除与修改1、定义基本表2、修改基本表3、删除基本表

[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(7)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageSMALLINT，

SdeptCHAR(15))；1、定义基本表命令表名列名类型名例题1、定义基本表常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束FOREIGNKEY…REFERENCESPRIMARYKEY与

UNIQUE的区别？[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),Gradeint,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Sno)REFERENCESStudent(Sno),

FOREIGNKEY(Cno)REFENERCESCourse(Cno));2、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件ALTERCOLUMN：修改原有的列定义[例3]向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。[例4]将年龄的数据类型改为半字长整数。ALTERTABLEStudentALTERCOLUMNSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据[例5]删除学生姓名必须取唯一值的约束。ALTERTABLECourseADDUNIQUE(Cname)；ALTERTABLEStudentDROPUNIQUE(Sname)；

[例6]增加课程名称必须取唯一值的约束。

间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名删除属性列。3、删除基本表DROPTABLE<表名>[RESTRICT|CASCADE];若选择RESTRICT：则该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用（如CHECK，FOREIGNKEY等约束），不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。3、删除基本表若选择CASCADE：则该表的删除是没有限制条件的。在删除基本表的同时，相关的依赖对象，如约束（如CHECK，FOREIGNKEY等约束）、视图、触发器、存储过程或函数等。都将一起被删除。缺省情况：RESTRICT例题[例7]删除Student表

DROPTABLE

StudentCASCADE;注：基本表的定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的索引、视图、触发器等有关对象一般也都将被一起删除。注：不同的DBMS的实现细节和处理策略会有差别。3.2.2索引的建立与删除建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引1、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引1、建立索引

[例8]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；命令名索引名索引1、建立索引唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束1、建立索引聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，那么Student表中的记录将按照Sname值的升序存放

1、建立索引在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率。聚簇索引的适用范围很少对基表进行增删操作进行聚簇索引的很少进行更新操作2、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例7]删除Student表的Stusname索引。

DROPINDEXStusname；SQL的数据定义语句总结3.3数据查询3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序3.3.1

单表查询

查询仅涉及一个表，是一种最简单的查询操作1、选择表中的若干列2、选择表中的若干元组3、对查询结果排序4、使用集函数5、对查询结果分组1、选择表中若干列(1)查询指定列(2)查询全部列(3)查询经过计算的值1.查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；2.查询全部列[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；3.查询经过计算的值SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名其它3.查询经过计算的值[例4]查全体学生的姓名及其出生年份。SELECTSname，2013-SageFROMStudent；

输出结果：

Sname2013-Sage----------------------

李勇1993

刘晨1994

王名1995

张立19943.查询经过计算的值[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，‘YearofBirth:’，2013-Sage，ISLOWER(Sdept)FROMStudent；输出结果：

Sname'YearofBirth:'2013-SageISLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1988cs

刘晨YearofBirth:1989cs

王名YearofBirth:1990ma

张立YearofBirth:1989is[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’

BIRTH，

2013-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1993cs

刘晨YearofBirth:1994cs

王名YearofBirth:1995ma

张立YearofBirth:1994is2、选择表中的若干元组(1)消除取值重复的行(2)查询满足条件的元组(1)消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------------20111211922011121285201112238820111222902011122380ALL与DISTINCT[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;

或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno------------20111212011121201112120111222011122

(2)SELECTDISTINCTSnoFROMSC;

结果：

Sno---------------20111212011122例题注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法

SELECTDISTINCTCno，GradeFROMSC;

2.查询满足条件的元组WHERE子句常用的查询条件(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例6]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

(2)确定范围使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例7]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；[例8]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；(3)确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例9]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');[例10]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');(4)字符串匹配

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。例题1)匹配模板为固定字符串

[例11]查询学号为2011121的学生的详细情况。

SELECT*FROMStudentWHERESno

LIKE'2011121'；等价于：

SELECT*FROMStudentWHERESno='2011121'；等价2)匹配模板为含通配符的字符串[例12]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；[例13]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；2)匹配模板为含通配符的字符串[例14]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__阳%'；[例15]查询所有不姓刘的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；3)使用换码字符将通配符转义为普通字符[例16]查询DB_Design课程的课程号和学分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'[例17]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；(5)涉及空值的查询

使用谓词ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

代替[例19]查所有有成绩的学生学号和课程号。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；[例18]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL；(6)多重条件查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词

[NOT]IN[NOT]BETWEEN…AND…[例20]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改写[例9][例9]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；改写[例8][例8]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；3、对查询结果排序

使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示[例21]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；查询结果

SnoGrade--------------95010950249500792950038295010829500975950146195002553、对查询结果排序[例22]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；4、使用集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）求最大值MAX（[DISTINCT|ALL]<列名>）求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值4、使用集函数[例23]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

[例24]查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数4、使用集函数[例25]计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；

[例26]查询选修1号课程的学生最高分数。

SELECTMAX(Grade)FROMSCWHERCno='1'；4、使用聚集函数[例27]查询学号为“200215012”选修课程的总学分数。

SELECTSUM(Ccredit)FROMSC，CourseWHERESno=‘200215012’ANDSC.Cno=Course.Cno；

注：在聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。

5、对查询结果分组使用GROUPBY子句分组细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

5、对查询结果分组[例28]求各个课程号及相应的选课人数。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

结果

CnoCOUNT(Sno)

122

234 344 433 548[例29]查询选修了3门以上课程的学生学号。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>=3；

只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

例题[例30]查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;

3.3.2连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕连接操作的过程：

表1

表2

第一个元组第二个元组满足连接条件的元组下一个元组连接条件拼接成结果表中元组排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止95001张明1995002李樟199500119095001290950013909500229095002190索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组95001张明1995002李樟1995001190950021909500139095002290950012901、内连接[例31]查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；（1）等值连接连接运算符为=的连接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。Student表

Sno

SnameSsexSageSdept2011121

李勇

男20CS2011122

刘晨

女19CS2011123

王敏

女18MA2011125

张立

男19IS

SnoCnoGrade20111211922011121285201112138820111222902011122380结果Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

2011121李勇男20CS20111211922011121李勇男20CS20111212852011121李勇男20CS20111213882011122刘晨女19IS20111222902011122刘晨女19IS2011122380

(2)自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。[例32]对[例31]用自然连接完成。

SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；(3)非等值连接查询连接运算符不是=的连接操作

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

(4)自身连接一个表与其自己进行连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀[例33]查询每一门课的间接先修课（即先修课的先修课）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；FIRST表（COURSE表）SECOND表（COURSE表）

CnoCpno173556查询结果分析：1、Course中，课号→直接先行课号→间接先行课，所以需要自身连接。2、为Course表取两个别名，一个是FIRST，另一个是SECOND。2外连接（OuterJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出左外连接和左右连接

[例36]查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；3交叉连接不带连接谓词的连接很少使用例：

SELECTStudent.*,SC.*FROMStudent，SC

交叉连接是不带连接谓词的连接。它返回被连接的两个表的广义笛卡尔积。4复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例37]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTStudent.Sno,Student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno='2'AND/*其他限定条件*/SC.Grade>90；

/*其他限定条件*/5多表连接[例33]查询每个学生的学号、姓名、选修的课程名及成绩。

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；

结果：

Student.SnoSnameCnameGrade 2011121李勇数据库922011121李勇数学852011121李勇信息系统882011122刘晨数学902011122刘晨信息系统80例34查询每个学生的学号、姓名、选修的课程名及成绩查询结果：3.3.3嵌套查询

SELECTSname外层查询/父查询

FROMStudentWHERESnoIN

（SELECTSno内层查询/子查询

FROMSCWHERECno='2'）；将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块子查询不能使用ORDERBY子句结构化引出子查询的谓词1、带有IN谓词的子查询2、带有比较运算符的子查询3、带有ANY或ALL谓词的子查询4、带有EXISTS谓词的子查询1、带有IN谓词的子查询[例34]查询与“刘晨”在同一个系学习的学生。

此查询要求可以分步来完成①确定“刘晨”所在系名

SELECTSdeptFROMStudentWHERESname='刘晨'； 结果为：

Sdept CS②查找所有在CS系学习的学生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='CS'；结果为：SnoSnameSdept2011121李勇CS2011122刘晨CS

1、带有IN谓词的子查询

构造嵌套查询将第一步查询嵌入到第二步查询的条件中

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘

刘晨’)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。

用自身连接完成本查询要求

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='刘晨'；父查询和子查询中的表均可以定义别名

SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘

刘晨’)；1、带有IN谓词的子查询[例35]查询选修了课程名为“信息系统”的学生学号和姓名

SELECTSno，Sname③最后在Student关系中

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC关系中找出选

FROMSC修了3号课程的学生学号

WHERECnoIN(SELECTCno①首先在Course关系中找出“信

FROMCourse息系统”的课程号，结果为3号

WHERECname=‘信息系统’));1、带有IN谓词的子查询结果：

Sno Sname--------------2011121李勇

2011122刘晨1、带有IN谓词的子查询用连接查询

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系统’；1、带有IN谓词的子查询2、带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例39]可以用=代替IN

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='刘晨'；1、带有IN谓词的子查询

子查询一定要跟在比较符之后

错误的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘

刘晨’

)=Sdept；1、带有IN谓词的子查询[例36]找出每个学生超过他选修课程平均成绩的课程号。SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)1、带有IN谓词的子查询x是表SC的别名，又称为元组变量，可以用来表示SC的一个元组。内层查询是一个学生所有选修课程平均成绩的，至于哪个学生的平均成绩要看参数x.Sno的值，而该值是与父查询相关的，因此这类查询称为相关子查询。1、带有IN谓词的子查询3、带有ANY或ALL谓词的子查询需要配合使用比较运算符>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或<>）ALL 不等于子查询结果中的任何一个值[例37]查询其他系中比计算机科学系某一个学生年龄小的学生姓名和年龄

SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='CS')

ANDSdept<>'CS';/*注意这是父查询块中的条件*/3、带有ANY或ALL谓词的子查询结果

Sname Sage

王敏18

张立19执行过程1.DBMS执行此查询时，首先处理子查询，找出

CS系中所有学生的年龄，构成一个集合(20，19)2.处理父查询，找所有不是CS系且年龄小于

20或19的学生3、带有ANY或ALL谓词的子查询[例37']：用集函数实现[例37]SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、带有ANY或ALL谓词的子查询ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系

=

<>或!=

<<=>>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL--

NOTIN

<MIN<=MIN>MAX>=MAX用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数3、带有ANY或ALL谓词的子查询[例38]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、带有ANY或ALL谓词的子查询结果

Sname Sage

王敏18执行过程1.DBMS执行此查询时，首先处理子查询，找出

CS系中所有学生的年龄，构成一个集合(20，19)2.处理父查询，找所有不是CS系且年龄小于

20，并且小于

19的学生3、带有ANY或ALL谓词的子查询

方法二：用集函数

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、带有ANY或ALL谓词的子查询4、带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴涵1)EXISTS谓词存在量词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2)NOTEXISTS谓词4、带有EXISTS谓词的子查询[例39]查询所有选修了1号课程的学生姓名。用嵌套查询

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoAND Cno=‘1’)4、带有EXISTS谓词的子查询

查询过程：

(1)取外层查询Student表中的第一个元组，根据它与内层查询相关的属性值（Sno）处理内层查询。

(2)若WHERE子句返回值为真，则取此元组放入结果表中。再取Student表中的下一个元组。直至Student表全部检查完毕。

(3)再对结果表投影取Sname4、带有EXISTS谓词的子查询STUDENTSCEXISTTRUEFALSEFALSEFALSE结果用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1';4、带有EXISTS谓词的子查询[例38]查询没有选修1号课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno ANDCno='1')；此例用连接运算难于实现

3)不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

4、带有EXISTS谓词的子查询4)用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(x)P≡

(x(P))[例40]查询选修了全部课程的学生姓名。

SELECTSname,Sno

FROMStudent某个学生

WHERENOT

EXISTS

不存在

(

SELECT*

FROMCourse一门课

WHERE

NOTEXISTS

不选修

(SELECT*

FROMSC

WHERE

Sno=Student.SnoANDCno=Course.Cno));4、带有EXISTS谓词的子查询StudentCourseSCStudentCourseSCNOTEXISTSNOTEXISTSFALSEFALSEFALSETRUE5)用EXISTS/NOTEXISTS实现逻辑蕴涵(难点)SQL语言中没有蕴涵(Implication)逻辑运算可以利用谓词演算将逻辑蕴涵谓词等价转换为：

pq≡p∨q4、带有EXISTS谓词的子查询

[例41]查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示： 用p表示谓词“学生95002选修了课程y”

用q表示谓词“学生x选修了课程y”

则上述查询为:(y)pq4、带有EXISTS谓词的子查询等价变换：

(y)pq≡(y((pq))≡(y((p∨q)≡

y(p∧q)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。4、带有EXISTS谓词的子查询

SELECTDISTINCTSnoFROMSCSCX

WHERENOTEXISTS不存在一门课程

(SELECT*FROMSCSCYWHERESCY.Sno=‘95002’

95002号选了

ANDNOTEXISTS(SELECT*另一个学生

FROMSCSCZ

没有选

WHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno));4、带有EXISTS谓词的子查询SCXSCYSCZSCXSCYSCZNOTEXISTSNOTEXISTSFALSEFALSETRUE结果3.3.4集合查询集合操作种类1.并操作(UNION)2.交操作(INTERSECT)3.差操作(EXCEPT)注：1参加集合操作的各查询结果的列数必须相同；2对应项的数据类型也必须相同。1、并操作形式

<查询块R> UNION <查询块S>查询结果为属于R或属于S的元组。参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同[例42]查询计算机科学系的学生及年龄不大于19岁的学生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；1、并操作方法二：SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；[例43]查询选修了课程1或者选修了课程2的学生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；1、并操作方法二：SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[补充示例