数据库原理与应用 教学课件 作者 林 小 玲第3章 关系数据库标准语言

上海大学自动化系林小玲第3章关系数据库标准语言3

3.1.SQL概述3.2.学生-课程数据库3.3.数据定义3.4.数据查询3.5.数据更新第3章关系数据库标准语言SQL本章内容3.6.视图4

3.1SQL概述SQL（StructuredQueryLanguage）:结构化查询语言，是关系数据库的标准语言。SQL是一个通用的、功能极强的关系数据库语言。5

SQL概述(续)3.1.2SQL的特点3.1.3SQL的基本概念3.1.1SQL的产生和发展

6

3.1.1SQL的产生和发展标准发布日期SQL/861986年SQL/89(FIPS127-1)1989年SQL/921992年SQL991999年SQL20032003年7

3.1.2SQL的特点SQL语言是类似于英语的自然语言，简洁易用；SQL语言是一种非过程语言；SQL语言是一种面向集合的语言；SQL语言既是自含式语言，又是嵌入式语言；SQL语言具有数据查询、数据定义、数据操纵和数据控制四种功能。8

3.1.3SQL的基本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL支持关系数据库三级模式结构:9

SQL的基本概念（续）基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图10

3.2学生-课程数据库学生-课程模式S-T:

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

11

Student表学号Sno姓名Sname性别

Ssex年龄

Sage所在系

Sdept200915121200915122200915123200915125李勇刘晨王敏张立男女女男20191819CSCSMAIS12

Course表课程号Cno课程名Cname先行课Cpno学分Ccredit1234567数据库数学信息系统操作系统数据结构数据处理C语言51676424342413

SC表学号Sno课程号

Cno成绩

Grade

200915121200915121200915121200915122200915122

12323

928588908014

3.3.2索引的建立与修改3.3.1基本表的定义、删除与修改

3.3数据定义15

3.3.1基本表的定义、删除与修改二、数据类型一、定义基本表

三、修改基本表

四、删除基本表

16

一、定义基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>]

[，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

17

定义学生表Student[例5]建立“学生”表Student，学号是主码，姓名取值唯一。

CREATETABLEStudent (SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/

SnameCHAR(20)UNIQUE，/*Sname取唯一值*/

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20))；主码18

定义课程表Course

[例6]建立一个“课程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，

CnameCHAR(40)，

CpnoCHAR(4)，

CcreditSMALLINT，

FOREIGNKEY(Cpno)REFERENCESCourse(Cno));先修课Cpno是外码被参照表是Course被参照列是Cno19

定义学生选课表SC[例7]建立一个“学生选课”表SC CREATETABLESC(SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

/*主码由两个属性构成，必须作为表级完整性进行定义*/ FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*表级完整性约束条件，Sno是外码，被参照表是Student*/ FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/ );20

二、数据类型SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度选用哪种数据类型由二个因素决定

取值范围要做哪些运算21

数据类型(续)数据类型含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT长整数（也可以写作INTEGER）SMALLINT短整数NUMERIC(p，d)定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字REAL取决于机器精度的浮点数DoublePrecision取决于机器精度的双精度浮点数FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，格式为HH:MM:SS22

三、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN

<列名><数据类型>]；

23

修改基本表（续）[例8]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADD

S_entrance

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

[例9]将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTERTABLEStudentALTERCOLUMNSageINT；[例10]增加课程名称必须取唯一值的约束条件。

ALTERTABLECourseADD

UNIQUE(Cname);

24

四、删除基本表

DROPTABLE<表名>［RESTRICT|CASCADE］；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除25

删除基本表(续)

[例11]删除Student表

DROPTABLEStudentCASCADE;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除26

删除基本表（续）［例12］若表上建有视图，选择RESTRICT时表不能删除

CREATEVIEWIS_Student

AS SELECTSno，Sname，Sage FROMStudent WHERESdept=‘IS’；/*创建了一个视图*/

DROPTABLEStudentRESTRICT;/*执行删除操作出错*/

--ERROR:cannotdroptableStudentbecauseotherobjectsdependonit

27

删除基本表（续）[例12]如果选择CASCADE时可以删除表，视图也自动被删除DROPTABLEStudentCASCADE; --NOTICE:dropcascadestoviewIS_Student

SELECT*FROMIS_Student;--ERROR:relation"IS_Student"doesnotexist

28

删除基本表（续）序号标准及主流数据库的处理方式依赖基本表的对象SQL99KingbaseESORACLE9iMSSQLSERVER2000RCRCC1.索引无规定√√√√√2.视图×√×√√保留√保留√保留3.DEFAULT，PRIMARYKEY，CHECK（只含该表的列）NOTNULL等约束√√√√√√√4.ForeignKey×√×√×√×5.TRIGGER×√×√√√√6.函数或存储过程×√√保留√保留√保留√保留√保留DROPTABLE时，SQL99与3个RDBMS的处理策略比较R表示RESTRICT,C表示CASCADE

'×'表示不能删除基本表，'√'表示能删除基本表，‘保留’表示删除基本表后，还保留依赖对象

29

3.3.2索引的建立与删除建立索引的目的：加快查询速度谁可以建立索引DBA或表的属主（即建立表的人,DB_Owner）DBMS一般会自动建立以下列上的索引

PRIMARYKEYUNIQUE谁维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引30

索引的建立与删除（续）RDBMS中索引一般采用B+树、HASH索引来实现B+树索引具有动态平衡的优点HASH索引具有查找速度快的特点采用B+树，还是HASH索引则由具体的RDBMS来决定索引是关系数据库的内部实现技术，属于内模式的范畴

CREATEINDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引31

索引的建立与删除（续）二、删除索引一、定义索引

32

一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…) [例13]CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引在最经常查询的列上建立聚簇索引以提高查询效率一个基本表上最多只能建立一个聚簇索引经常更新的列不宜建立聚簇索引

33

建立索引（续）

[例14]为学生-课程数据库中的Student，Course，SC三个表建立索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

Student表按学号(Sno)升序建唯一索引

Course表按课程号(Cno)升序建唯一索引

SC表按学号(Sno)升序和课程号(Cno)降序建唯一索引34

二、删除索引DROPINDEX<索引名>；

删除索引时，系统会从数据字典中删去有关该索引的描述。[例15]删除Student表的Stusname索引

DROPINDEXStusname35

3.4数据查询语句格式

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM

<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

36

3.4数据查询3.4.2连接查询3.4.3嵌套查询

3.4.1单表查询3.4.4集合查询

3.4.5Select语句的一般形式

37

3.4.1单表查询二、选择表中的若干元组三、ORDERBY子句

一、选择表中的若干列四、聚集函数

五、GROUPBY子句

38

一、选择表中的若干列2.查询全部列3.查询经过计算的值

1.查询指定列39

1.查询指定列

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

SELECTSno，Sname

FROMStudent；

[例2]查询全体学生的姓名、学号、所在系。

SELECTSname，Sno，Sdept

FROMStudent；

40

2.查询全部列选出所有属性列：在SELECT关键字后面列出所有列名将<目标列表达式>指定为*

[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，Sdept

FROMStudent；或SELECT*FROMStudent；

41

3.查询经过计算的值SELECT子句的<目标列表达式>可以为：算术表达式字符串常量函数列别名

42

[例4]查全体学生的姓名及其出生年份。

SELECTSname，2010-Sage/*假定当年的年份为2010年*/FROMStudent；

输出结果：

Sname2010-Sage-------------------

李勇 1990

刘晨 1991

王敏 1992

张立 1991查询经过计算的值（续）43

查询经过计算的值（续）[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名

SELECTSname，‘YearofBirth:'，2010-Sage，ISLOWER(Sdept)FROMStudent；

输出结果：

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

李勇YearofBirth:1990cs

刘晨YearofBirth:1991is

王敏YearofBirth:1992ma

张立YearofBirth:1991is44

查询经过计算的值（续）使用列别名改变查询结果的列标题:

SELECTSname

NAME，'YearofBirth:’BIRTH，

2010-SageBIRTHDAY，LOWER(Sdept)DEPARTMENT FROMStudent；输出结果：

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

李勇YearofBirth:1990cs

刘晨YearofBirth:1991is

王敏YearofBirth:1992ma

张立YearofBirth:1991is45

二、选择表中的若干元组2.查询满足条件的元组1.取消取值重复的行

46

1.消除取值重复的行 如果没有指定DISTINCT关键词，则缺省为ALL

[例6]查询选修了课程的学生学号。

SELECTSnoFROMSC；

等价于：

SELECTALLSnoFROMSC；

执行上面的SELECT语句后，结果为：

Sno

-------------------- 200915121 200915121 200915121 200915122 20091512247

消除取值重复的行（续）指定DISTINCT关键词，去掉表中重复的行

SELECTDISTINCT

Sno

FROMSC；

执行结果：

Sno

------------- 200915121 20091512248

2.查询满足条件的元组查询条件谓词比较=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件（逻辑运算）AND，OR，NOT常用的查询条件表49

（1）比较大小[例7]查询计算机科学系全体学生的名单。

SELECTSname

FROMStudentWHERESdept=‘CS’；[例8]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，SageFROMStudentWHERESage<20；[例9]查询考试成绩有不及格的学生的学号。SELECTDISTINCT

Sno

FROMSCWHEREGrade<60；

50

（2）确定范围谓词: BETWEEN…AND… NOTBETWEEN…AND…

[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

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

SELECTSname，Sdept，Sage FROMStudent WHERESageNOTBETWEEN20AND23；51

（3）确定集合谓词：IN<值表>,NOTIN<值表>

[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname，Ssex

FROMStudent WHERESdeptIN('IS'，'MA'，'CS');[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

SELECTSname，Ssex

FROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');52

（4）字符匹配谓词：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]匹配串为固定字符串[例14]查询学号为200215121的学生的详细情况。

SELECT*FROMStudentWHERESno

LIKE‘200215121'；等价于：

SELECT*FROMStudentWHERESno='200215121'；53

字符匹配（续）

2)匹配串为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，Ssex

FROMStudentWHERESname

LIKE‘刘%’；

[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSname

FROMStudentWHERESname

LIKE'欧阳__'；54

字符匹配（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，Sno

FROMStudentWHERESname

LIKE‘__阳%’；

[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，Ssex

FROMStudentWHERESname

NOTLIKE'刘%'；

55

字符匹配（续）3)使用换码字符将通配符转义为普通字符

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

SELECTCno，Ccredit

FROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\‘；

[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\‘；

ESCAPE'＼'表示“＼”为换码字符56

（5）涉及空值的查询谓词：ISNULL

或ISNOTNULL

“IS”不能用“=”代替

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

SELECTSno，Cno

FROMSCWHEREGradeISNULL[例22]查所有有成绩的学生学号和课程号。

SELECTSno，Cno

FROMSCWHEREGradeISNOTNULL；57

（6）多重条件查询逻辑运算符：AND和OR来联结多个查询条件

AND的优先级高于OR

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

[NOT]IN[NOT]BETWEEN…AND…58

多重条件查询（续）[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSname

FROMStudentWHERESdept='CS'ANDSage<20；59

多重条件查询（续）改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，Ssex

FROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，Ssex

FROMStudentWHERESdept='IS'OR

Sdept='MA'OR

Sdept='CS'；

60

三、ORDERBY子句ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示61

ORDERBY子句（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；[例25]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；62

四、聚集函数聚集函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）63

聚集函数（续）

[例26]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

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

SELECTCOUNT(DISTINCT

Sno)FROMSC；

[例28]计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；64

聚集函数（续）

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

SELECTMAX(Grade)FROMSCWHERCno='1'；

［例30］查询学生200915012选修课程的总学分数。(本例不是单表) SELECTSUM(Ccredit)FROMSC，CourseWHERSno='200915012'AND

SC.Cno=Course.Cno;65

五、GROUPBY子句GROUPBY子句分组：目的：细化聚集函数的作用对象

未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组66

GROUPBY子句（续）[例31]求各个课程号及相应的选课人数。

SELECTCno，COUNT(Sno)FROMSC

GROUPBYCno；

查询结果：

Cno

COUNT(Sno) 122 234 344 433 54867

GROUPBY子句（续）[例32]查询选修了3门以上课程的学生学号。

SELECTSno

FROMSCGROUPBYSno

HAVINGCOUNT(*)>3；

该例中*是指什么？

68

GROUPBY子句（续）HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。

69

3.4.2连接查询连接查询：同时涉及多个表的查询

连接条件或连接谓词：用来连接两个表的条件 一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

连接字段：连接谓词中的列名称连接条件中的各连接字段类型必须是可比的，但名字不必是相同的70

连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2

，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再从表1中得到第二个元组，然后再从头开始扫描表2

，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕。71

排序合并法(SORT-MERGE)常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续72

排序合并法(续)找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止。73

索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

74

连接查询（续）二、自身连接三、外连接

一、等值与非等值连接查询

四、复合条件连接

75

一、等值与非等值连接查询等值连接：连接运算符为=[例33]查询每个学生及其选修课程的情况

SELECTStudent.*，SC.* FROMStudent，SC WHEREStudent.Sno=SC.Sno；76

等值与非等值连接查询（续）Student.Sno

Sname

Ssex

Sage

Sdept

SC.Sno

Cno

Grade

200915121

李勇男20

CS

200915121

1

92

200915121

李勇男20

CS

200915121

2

85

200915121

李勇男20

CS

200915121

3

88

200915122

刘晨女19

CS

200915122

2

90

200915122

刘晨女19

CS

200915122

3

80

查询结果：77

等值与非等值连接查询（续）自然连接：

[例34]对[例33]用自然连接完成。

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；78

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

SELECTFIRST.Cno，SECOND.Cpno

FROMCourseFIRST，CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno；79

自身连接（续）Cno

Cname

Cpno

Ccredit

1

数据库

5

4

2

数学

2

3

信息系统

1

4

4

操作系统

6

3

5

数据结构

7

4

6

数据处理

2

7

PASCAL语言

6

4

SECOND表（Course表）80

自身连接（续）查询结果：Cno

Pcno

1

7

3

5

5

6

81

三、外连接外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出[例36]改写[例33]SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；

82

外连接（续）执行结果：Student.Sno

Sname

Ssex

Sage

Sdept

Cno

Grade

200915121

李勇男20

CS

1

92

200915121

李勇男20

CS

2

85

200915121

李勇男20

CS

3

88

200915122

刘晨女19

CS

2

90

200915122

刘晨女19

CS

3

80

200915123

王敏女18

MA

NULL

NULL

200915125

张立男19

IS

NULL

NULL

83

外连接（续）左外连接列出左边关系（如本例Student）中所有的元组右外连接列出右边关系中所有的元组84

四、复合条件连接复合条件连接：WHERE子句中含多个连接条件

[例37]查询选修2号课程且成绩在90分以上的所有学生

SELECTStudent.Sno,Sname

FROMStudent,SC WHEREStudent.Sno=SC.Sno

AND

/*连接谓词*/

SC.Cno=‘2’AND

SC.Grade>90；

/*其他限定条件*/85

复合条件连接（续）[例38]查询每个学生的学号、姓名、选修的课程名及成绩

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表连接*/WHEREStudent.Sno=SC.Sno

andSC.Cno=Course.Cno；

86

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

87

嵌套查询(续)

SELECTSname/*外层查询/父查询*/FROMStudentWHERESnoIN

（SELECTSno/*内层查询/子查询*/FROMSCWHERECno='2'）；

88

嵌套查询(续)子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代89

嵌套查询求解方法不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。90

嵌套查询求解方法（续）相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止91

嵌套查询（续）二、带有比较运算符的子查询三、带有ANY(SOME)或ALL谓词的子查询

一、带有IN谓词的子查询四、带有EXISTS谓词的子查询

92

一、带有IN谓词的子查询[例39]查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成：①确定“刘晨”所在系名

SELECTSdept

FROMStudentWHERESname='刘晨'； 结果为：CS93

带有IN谓词的子查询（续）②查找所有在IS系学习的学生。

SELECTSno，Sname，Sdept

FROMStudentWHERESdept='CS'；结果为：Sno

Sname

Sdept

200915121

李勇CS

200915122刘晨CS94

带有IN谓词的子查询（续）将第一步查询嵌入到第二步查询的条件中

SELECTSno，Sname，Sdept

FROMStudent WHERESdept

IN(SELECTSdept

FROMStudentWHERESname=‘刘晨’)；此查询为不相关子查询。95

带有IN谓词的子查询（续）

用自身连接完成[例39]查询要求

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2

WHERES1.Sdept=S2.SdeptAND

S2.Sname='刘晨'；

96

带有IN谓词的子查询（续）[例40]查询选修了课程名为“信息系统”的学生学号和姓名

SELECTSno，Sname

③最后在Student关系中

FROMStudent取出Sno和Sname

WHERESnoIN(SELECTSno

②然后在SC关系中找出选

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

WHERECnoIN(SELECTCno

①首先在Course关系中找出

FROMCourse“信息系统”的课程号，为3号

WHERECname=‘信息系统’));97

带有IN谓词的子查询（续）用连接查询实现[例40]SELECTSno，Sname

FROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoAND

SC.Cno=Course.CnoAND

Course.Cname=‘信息系统’；98

二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用

99

带有比较运算符的子查询（续）例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例39]可以用=代替IN

：

SELECTSno，Sname，Sdept

FROMStudentWHERESdept

=

(SELECTSdept

FROMStudentWHERESname=‘刘晨’)；100

带有比较运算符的子查询（续）子查询一定要跟在比较符之后

错误的例子：

SELECTSno，Sname，Sdept

FROMStudentWHERE(SELECTSdept

FROMStudentWHERESname=‘刘晨’)=Sdept；101

带有比较运算符的子查询（续）［例41］找出每个学生超过他选修课程平均成绩的课程号。

SELECTSno，Cno

FROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCyWHEREy.Sno=x.Sno);相关子查询102

带有比较运算符的子查询（续）可能的执行过程：1.从外层查询中取出SC的一个元组x，将元组x的Sno值（200915121）传送给内层查询。

SELECTAVG(Grade)FROMSCyWHEREy.Sno='200915121';

2.执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：

SELECTSno，Cno

FROMSCxWHEREGrade>=88；103

带有比较运算符的子查询（续）3.执行这个查询，得到（200215121，1）（200215121，3）4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为:

（200215121，1）（200215121，3）（200215122，2）104

三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值ALL：所有值

105

带有ANY或ALL谓词的子查询（续）需要配合使用比较运算符：>ANY 大于子查询结果中的某个值>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或<>）ALL 不等于子查询结果中的任何一个值106

[例42]查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

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

ANDSdept<>‘CS';/*父查询块中的条件*/带有ANY或ALL谓词的子查询（续）107

执行过程：

1.RDBMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19)2.处理父查询，找所有不是CS系且年龄小于20或19的学生Sname

Sage

王敏18

张立19带有ANY或ALL谓词的子查询（续）结果：108

用聚集函数实现[例42]

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)

FROMStudentWHERESdept=‘CS')ANDSdept<>'CS’;带有ANY或ALL谓词的子查询（续）109

[例43]查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS'ANDSdept<>‘CS’;）带有ANY或ALL谓词的子查询（续）110

方法二：用聚集函数

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)

FROMStudentWHERESdept='CS')ANDSdept<>'CS’;带有ANY或ALL谓词的子查询（续）111

ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系表

=

<>或!=

<

<=

>

>=

ANY

IN

--

<MAX

<=MAX

>MIN

>=MIN

ALL

--

NOTIN

<MIN

<=MIN

>MAX

>=MAX

带有ANY或ALL谓词的子查询（续）112

四、带有EXISTS谓词的子查询1.EXISTS谓词存在量词

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

带有EXISTS谓词的子查询(续)[例44]查询所有选修了1号课程的学生姓名。

思路分析：本查询涉及Student和SC关系在Student中依次取每个元组的Sno值，用此值去检查SC关系若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno='1'，则取此Student.Sname送入结果关系114

带有EXISTS谓词的子查询(续)用嵌套查询

SELECTSname

FROMStudent

WHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；

115

带有EXISTS谓词的子查询(续)用连接运算

SELECTSname

FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno='1';

116

带有EXISTS谓词的子查询(续)[例45]查询没有选修1号课程的学生姓名。

SELECTSname

FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；117

带有EXISTS谓词的子查询(续)不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

(x)P≡(x(P))

左表示：全部的x都满足条件P；右表示：不存在这样x，它不满足条件P

118

带有EXISTS谓词的子查询(续)[例46]查询选修了全部课程的学生姓名。

SELECTSname

FROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno

ANDCno=Course.Cno））119

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

pq≡

p∨q

120

带有EXISTS谓词的子查询(续)

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

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

则上述查询为:(y)pq121

带有EXISTS谓词的子查询(续)等价变换：

(y)p

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

y(p∧q)

变换后语义：不存在这样的课程y，学生200215122选修了y，而学生x没有选。122

带有EXISTS谓词的子查询(续)

用NOTEXISTS谓词表示：

SELECTDISTINCTSno

FROMSCSCX

WHERENOTEXISTS(SELECT*FROMSCSCY

WHERESCY.Sno=‘200215122’ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；123

3.4.4集合查询集合操作的种类并操作UNION交操作INTERSECT(T-SQL不支持)

差操作EXCEPT(T-SQL不支持)参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同124

集合查询（续）[例48]查询计算机科学系的学生及所有系中年龄不大于19岁的学生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNIONALL：将多个查询结果合并起来时，保留重复元组125

集合查询（续）方法二：

SELECTDISTIN