数据库系统原理-3

1/283数据库系统原理ThePrincipleofDatabaseSystem

第三章关系数据库标准语言SQL黄穗副教授主讲暨南大学信息学院计算机系2/283【教学目标】

了解SQL语言产生以前的问题。了解SQL的特点。掌握标准SQL语言的格式与功能。学会使用SQL。

【教学重点】SQL的查询、控制、更新语句使用方法。视图的定义与应用

【教学难点】如何根据实际需要灵活选择适当的SQL语句并完成相应功能。3/283第一节、SQL概述【教学目标】了解SQL产生与演变的历史了解SQL的特点学习掌握SQL的基本概念4/283SQL概述：

SQL以前—各种模式采用不同的语言（外模式-高级语言/专门语言、模式-C语言、内模式-汇编语言）。系统兼容性差、难移植。

SQL的版本-86版、89版、92版、99版、XMLSQL、SparkSQL。不断增加新语句，向基于内容查询方面发展。

SQL实现的差异—T-SQL（MSSQLServer/Sybase)、PL/SQL(Oracle)、ISQL(InterBase)。主要体现在扩展语句和函数功能方面。只有通过原生(Original)接口才能充分支持，ODBC/JDBC接口支持不足。5/283SQL的特点三大功能的统一—指DDL、DML、DCL都由SQL完成。解决系统互操作、移植、更新问题，充分体现两个独立性。1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供两种使用方法5.语言简洁，易学易用6/2835.语言简捷，易学易用7/283第二节、SQL的数据定义【教学目标】了解数据定义的内容与格式掌握基本表的创建与修改语句理解索引概念并学会建立索引8/283

数据定义的内容9/283

定义语句的格式CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件10/283[例1]建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；11/283常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与

UNIQUE的区别？[例2]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));12/283三、删除基本表

DROPTABLE<表名>;

基本表删除。数据、表上的索引都删除，表上的视图往往仍然保留，但无法引用。删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述。[例5]删除Student表

DROPTABLE

Student;13/283二、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和数据类型14/283例题[例2]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

15/283删除属性列

直接/间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列:(新)例：ALTERTABLEStudentDropScome；16/283[例3]将年龄的数据类型改为半字长整数。

ALTERTABLEStudentMODIFYSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据[例4]删除学生姓名必须取唯一值的约束。

ALTERTABLEStudentDROPUNIQUE(Sname)；17/283

建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成

（早期dBase、FoxPro等除外）使用索引

DBMS自动选择是否使用索引以及使用哪些索引18/283

索引概念—索引与基表的区别。建立索引的目的(排序与定位)。索引的代价。索引的维护。

建立索引—语句格式（注意索引名与索引目标对应）。UNIQUE、CLUSTER、DESC参数的意义与使用。

删除索引—减少系统维护时间。索引实例—建立、删除索引的例子。19/283一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引20/283[例6]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束21/283聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放

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

DROPINDEXStusname；23/283第三节、SQL的查询语句【教学目标】了解查询语句的内容与格式掌握单表与多表查询掌握嵌套集函数的使用24/283语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GroupBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；25/283SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件

GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序26/283示例数据库学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)27/283单表查询

查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组

28/283查询指定列

[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；29/283

查询经过计算的值SELECT子句的<目标列表达式>为表达式算术表达式字符串常量函数列别名等30/283[例4]查全体学生的姓名及其出生年份。SELECTSname，2000-SageFROMStudent；

输出结果：

Sname2000-Sage----------------------

李勇1976

刘晨1977

王名1978

张立197831/283[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，'YearofBirth:'，2000-Sage，

ISLOWER(Sdept)FROMStudent；

32/283输出结果：

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

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

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

BIRTH，

2000-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；输出结果：

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

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is34/283二、选择表中的若干元组消除取值重复的行查询满足条件的元组35/2831.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------950011929500128595001388950022909500238036/283ALL与

DISTINCT

[例6]查询选修了课程的学生学号。(1)SELECTSnoFROMSC;

或(默认ALL)SELECTALLSnoFROMSC;

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

SELECTDISTINCTCno，GradeFROMSC;

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

SELECTSname，Sage

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

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

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

41/283[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

42/283(3)确定集合使用谓词IN<值表>,NOTIN<值表>

<值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');43/283(3)确定集合[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');44/283(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词45/283通配符%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串46/283ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。47/2831)匹配模板为固定字符串[例14]查询学号为95001的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：

SELECT*FROMStudentWHERESno='95001'；48/2832)匹配模板为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

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

SELECTSnameFROMStudentWHERESnameLIKE‘欧阳__’；[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__阳%'；50/283匹配模板为含通配符的字符串（续）[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；51/2833)使用换码字符将通配符转义为普通字符

[例19]查询DB_Design课程的课程号和学分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'52/283使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

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

使用谓词ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

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

SELECTSno，CnoFROMSCWHEREGradeISNULL；54/283[例22]查所有有成绩的学生学号和课程号。

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

AND的优先级高于OR

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

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

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

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；59/283三、对查询结果排序

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

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

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

SELECT*FROMStudentORDERBYSdept，SageDESC；63/283四、使用集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）64/283求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值65/283[例26]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

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

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数66/283[例28]计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

SELECTMAX(Grade)FROMSCWHERCno='1'；67/283五、对查询结果分组使用GROUPBY子句分组 细化集函数的作用对象未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组68/283使用GROUPBY子句分组[例30]求各个课程号及相应的选课人数。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

结果

CnoCOUNT(Sno)

122

234 344 433 54869/283GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数

70/283使用HAVING短语筛选最终输出结果[例31]查询选修了3门以上课程的学生学号。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

71/283[例32]查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数

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

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

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

比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>74/283连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的75/283连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕76/283排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续77/283排序合并法找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止78/283索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组79/283SQL中连接查询的主要类型

广义笛卡尔积 等值连接(含自然连接)

非等值连接查询 自身连接查询 外连接查询 复合条件连接查询80/283一、广义笛卡尔积不带连接谓词的连接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC81/283二、等值与非等值连接查询等值连接、自然连接、非等值连接[例32]查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；82/283等值连接连接运算符为=的连接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。83/283等值连接假设Student表、SC表分别有下列数据：

Student表SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19IS84/283等值连接SC表SnoCnoGrade950011929500128595001950029500232388908085/283等值连接结果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002刘晨女19 IS 9500229095002刘晨女19 IS 95002380

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

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

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

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

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；90/283FIRST表（Course表）

CnoCnameCpnoCcredit1数据库542数学

23信息系统144操作系统635数据结构746数据处理

27PASCAL语言6491/283SECOND表（Course表）

CnoCnameCpnoCcredit1数据库542数学

23信息系统144操作系统635数据结构746数据处理

27PASCAL语言6492/283查询结果

173556

cnocpno93/283四、外连接（OuterJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出94/283

[例33]查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；95/283结果：

Student.Sno SnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095002刘晨女19IS38095003王敏女18MA95004张立男19IS96/283在表名后面加外连接操作符(*)或(+)指定非主体表非主体表有一“万能”的虚行，该行全部由空值组成虚行可以和主体表中所有不满足连接条件的元组进行连接由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值97/283

左外连接

外连接符出现在连接条件的左边 右外连接

外连接符出现在连接条件的右边

98/283五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例35]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno='2'AND/*其他限定条件*/SC.Grade>90；

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

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

结果：

Student.SnoSnameCnameGrade 95001李勇数据库9295001李勇数学8595001李勇信息系统8895002刘晨数学9095002刘晨信息系统80100/283嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词

101/283嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

102/283

SELECTSname 外层查询/父查询

FROMStudentWHERESnoIN

（SELECTSno内层查询/子查询

FROMSCWHERECno='2'）；103/283子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代104/283嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询105/283嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。106/283相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。107/283引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询108/283一、带有IN谓词的子查询[例37]查询与“刘晨”在同一个系学习的学生。

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

SELECTSdeptFROMStudentWHERESname='刘晨'； 结果为：

Sdept IS109/283②查找所有在IS系学习的学生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；结果为：SnoSnameSdept95001刘晨IS95004张立IS110/283

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

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘

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

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

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

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

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

刘晨’)；113/283[例38]查询选修了课程名为“信息系统”的学生学号和姓名

SELECTSno，Sname③最后在Student关系中

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

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

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

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

WHERECname=‘信息系统’));114/283结果：

Sno Sname---------95001李勇

95002刘晨115/283用连接查询

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系统’；116/283二、带有比较运算符的子查询

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

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='刘晨'；118/283

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

错误的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘

刘晨’

)=Sdept；119/283三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值ALL：所有值120/283需要配合使用比较运算符>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或<>）ALL 不等于子查询结果中的任何一个值121/283[例39]查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

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

ANDSdept<>'IS';/*注意这是父查询块中的条件*/122/283结果

Sname Sage

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

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

19或18的学生123/283ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系

=

<>或!=

<<=>>=ANY

IN

--

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

NOTIN

<MIN<=MIN>MAX>=MAX124/283用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数125/283[例39']：用集函数实现[例39]

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;126/283[例40]查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS’;查询结果为空表。127/283

方法二：用集函数

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

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2.NOTEXISTS谓词130/283

思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果关系。131/283[例41]查询所有选修了1号课程的学生姓名。用嵌套查询

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoAND Cno='1')；求解过程132/283用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND SC.Cno='1';133/283[例42]查询没有选修1号课程的学生姓名。

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

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

135/283例：[例37]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS

SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname='刘晨'；136/2835.用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(x)P≡

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

SELECTSnameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；138/283

6.用EXISTS/NOTEXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：

pq≡p∨q139/283

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

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

则上述查询为:(y)pq140/283等价变换：

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

y(p∧q)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。141/283用NOTEXISTS谓词表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；142/283集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS)143/2831．并操作形式

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

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；145/283方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；146/283[例46]查询选修了课程1或者选修了课程2的学生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；147/283方法二：

SE