第3章 关系数据库标准语言SQL

数据库原理TheTheoryofDatabaseSystem第三章关系数据库标准语言SQLSQL概述数据定义功能本讲主要内容1974年由Boyce和Chamberlin提出，1975年～1979年IBM公司在SystemR原型系统上实现。3.1.1SQL的发展关系数据库的标准语言，是数据库领域中一个主流语言3.1SQL概述SQL/86：第一个SQL标准。由美国国家标准局（AmericanNationalStandardInstitute，简称ANSI）公布，1987年国际标准化组织（InternationalOrganizationforStandardization，简称ISO）通过。SQL标准SQL/89SQL/92（简称SQL2）SQL/99（简称SQL3）SQL/4正在进行之中功能动词数据库查询SELECT数据定义CREATE，DROPALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE(4)语言简洁，易学易用3.1.2SQL的特点(3)高度非过程化(1)数据描述、操纵、控制等功能一体化(2)两种使用方式，统一的语法结构3.1SQL概述外模式模式内模式3.1.3SQL体系结构基本表4存储文件2

SQL视图1视图2基本表1存储文件1基本表2基本表3用户基本表（BaseTable）。简称基表，是数据库中实际存在的关系。视图是从一个或几个基表导出的表，它本身不实际存储在数据库中，只存放对视图的定义信息（没有对应的数据）。因此，视图是一个虚表或虚关系，而基表是一种实关系存储文件。每个基表对应一个存储文件，一个基表还可以带一个或几个索引，存储文件和索引一起构成了关系数据库的内模式。3.2SQL的定义功能3.2.1基本表的定义CREATETABLE<表名>（<列名><数据类型>[<列级完整性约束条件>]

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

[，<表级完整性约束条件>]

）；[例1]建立一个“学生”表S，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLES(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；[例2]建立一个“课程”表C，它由课程号Cno、课程名Cname、学分Credit、先修课程Pro四个属性组成。其中课程号不能为空，值是唯一的。CREATETABLEC(CnoCHAR(4)NOTNULLUNIQUE,CnameCHAR(15),CreditINT,ProCHAR(4));定义基本表（续）数据类型1)定长和变长字符串CHAR(n)VARCHAR(n)2)定长和变长二进制串BIT(n)BITVARING(n)3)整型数INTSMALLINT4)浮点数FLOATDOUBLEPRECISION5)日期型DATE6)时间型TIME7)时标TIMESTAMP定义基本表（续）常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束PRIMARYKEY与

UNIQUE的区别？为学生表和课程表添加主码：CREATETABLES(SnoCHAR(5)PrimaryKey，

SnameCHAR(20)，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；CREATETABLEC(CnoCHAR(4)PrimaryKey,CnameCHAR(15),CreditINT,ProCHAR(4));[例3]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),GradeINT,Primarykey(Sno,Cno));主关键字的定义1）在列出关系模式的属性时,在属性及其类型后加上保留字PRIMARYKEY；2）在列出关系模式的所有属性后，再附加一个声明：

PRIMARYKEY（<属性1>[，<属性2>，…]）说明：如果关键字由多个属性构成，则必须使用第二种方法。外部关键字的定义1）如果外部关键字只有一个属性，可以在它的属性名和类型后面直接用“REFERENCES”说明它参照了某个表的某些属性，其格式为：

REFERENCES<表名>(<属性>)2）在CREATETABLE语句的属性列表后面增加一个或几个外部关键字说明，其格式为：

FOREIGNKEY(<属性>)REFERENCES<表名>(<属性>)19为学生选课表建立外码：CREATETABLESC(SnoCHAR(5)REFERENCESS(Sno),CnoCHAR(3)REFERENCESC(Cno),GradeINT,PRIMARYKEY(Sno,Cno));20CREATETABLESC(SNOCHAR(8),CNOCHAR(4),GRADESMALLINT,PRIMARYKEY(SNO,CNO),

FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(CNO)REFERENCESC(CNO));3.2.2基本表的修改和删除1、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<列名>|<完整性约束名>][ALTER<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的列或完整性约束条件ALTER子句：用于修改列名和数据类型[例4]向学生表增加“入学时间”列，其数据类型为日期型。

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

语句格式（续）删除属性列例：删除学生表中“入学时间”属性列。ALTERTABLESDropScome；注意：若一个属性被说明为NOTNULL，则不允许修改或删除。

[例5]将年龄的数据类型改为半字长整数。

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

ALTERTABLESDROPUNIQUE(Sname)；2、删除基本表

DROPTABLE<表名>;

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

DROPTABLES;关于缺省值(不适用于ACCESS)可以在定义属性时增加保留字DEFAULT和一个合适的值。例如：性别CHAR（1）DEFAULT‘男’；年龄SMALLINTDEFAULT1；修改默认值

ALTERTABLESADDCONSTRAINTddDEFAULT‘男’

FORsex;在SQLSERVER2005中CREATETABLE

S(SNO

CHAR(8)PRIMARYKEY,SNAMECHAR(8),SEXCHAR(2)DEFAULT'男'

CHECK(SEX='男'OR

SEX='女'),AGETINYINTDEFAULT20,DEPTCHAR(30)DEFAULT'计算机学院');3.2.3索引的建立与删除索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（B树）中，使数据库可以快速有效地查找与键值关联的行。在ACCESS中有重复索引与非重复索引SqlServer索引的分类聚集索引聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有一个聚集索引。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。SqlServer索引的分类非聚集索引非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。从非聚集索引中的索引行指向数据行的指针称为行定位器。SqlServer索引的分类聚集索引和非聚集索引都可以是唯一的。索引也可以不是唯一的，即多行可以共享同一键值。在创建PRIMARYKEY约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。在创建UNIQUE约束时，默认情况下将创建唯一非聚集索引，以便强制UNIQUE约束。如果不存在该表的聚集索引，则可以指定唯一聚集索引。一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚集索引[例8]为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONS(Sno)；CREATEUNIQUEINDEXCoucnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；建立索引（续）唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。建立索引（续）聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致例：CREATECLUSTERINDEXStusnameONS(Sname)；在S表的Sname（姓名）列上建立一个聚簇索引，而且S表中的记录将按照Sname值的升序存放。建立索引（续）在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围

很少对基表进行增删操作很少对其中的变长列进行修改操作

二、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例9]删除S表的Stusname索引。

DROPINDEXStusname；使用索引的技巧小表不需要索引数据列中有较多不相同数据时可使用索引查询要返回的数据很少时可用索引需要经常更新数据时不宜用索引只有主索引或聚集索引才会引起数据表中数据的排序数据操纵功能——数据查询本讲主要内容3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4集合查询3.3.5嵌套查询3.3.6小结3.3.1概述语句格式SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

示例数据库学生-课程数据库学生表：S(Sno，Sname，Ssex，Sage，Sdept)课程表：C(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4集合查询3.3.5嵌套查询3.3.6小结3.3.2单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列（投影）二、选择表中的若干元组（选择）三、对查询结果排序四、使用集函数五、对查询结果分组查询指定列[例1]查询全体学生的学号与姓名。SELECTSno，SnameFROMS；

[例2]查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMS；查询全部列[例3]查询全体学生的详细记录。

SELECTSno,Sname,Ssex,Sage,Sdept

FROMS；

或

SELECT*FROMS；

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

输出结果：

SnameExpr1001----------------------

李勇1976

刘晨1977

王名1978

张立19783.查询经过计算的值使用列别名改变查询结果的列标题：SELECTSname,2000-SageasBirthYearFROMS；

输出结果：

SnameBirthYear----------------------

李勇1976

刘晨1977

王名1978

张立1978二、选择表中的若干元组消除取值重复的行查询满足条件的元组说明SQL查询语句的结果也是一个关系。关系代数中基于关系是一个集合这样的数学概念，因此，重复的元组不会在关系中出现。但在实践当中，要删除查询结果中的重复元组是相当费时的！所以在商用的数据库产品中，允许在关系和SQL表达式的结果中出现重复元组。1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

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

或

SELECTALLSnoFROMSC;(默认ALL)结果：Sno-------9500195001950019500295002

(2)SELECTDISTINCTSnoFROMSC;

结果：

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

SELECTDISTINCTCno，GradeFROMSC;

2.查询满足条件的元组带有where子句的查询常用的查询条件查询条件 谓词比较 =,<>,>,>=,<,<=算术运算 +-*/确定范围 BetweenAnd, NotBetweenAnd确定集合 IN,NOTIN字符匹配 Like,NotLike空值 ISNULL,ISNOTNULL多重条件 AND,OR(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例6]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，SageFROMSWHERESage<20；或

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

SELECTSname，Sdept，SageFROMSWHERESageBETWEEN20AND23；

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

(3)确定集合使用谓词：IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例9]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMSWHERESdeptIN('IS'，'MA'，'CS');(3)确定集合[例10]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMS 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‘<转义字符>’

短语对通配符进行转义。如：like'__\%'escape'\'

在access中查找通配符用[]1)匹配模板为固定字符串[例11]查询学号为95001的学生的详细情况。

SELECT*FROMSWHERESnoLIKE'95001'；等价于：

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

SELECTSname，Sno，SsexFROMSWHERESnameLIKE‘刘%’；匹配模板为含通配符的字符串（续）[例13]查询姓"欧阳"且全名为三个汉字的学生的姓名。

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

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

SELECTSname，Sno，SsexFROMSWHERESnameNOTLIKE'刘%'；3)使用转义字符将通配符转义为普通字符

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

SELECTCno，CcreditFROMCWHERECnameLIKE'DB\_Design'

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

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

使用谓词ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

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

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

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

AND的优先级高于OR

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

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

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

SELECTSname，Sdept，SageFROMSWHERESageBETWEEN20AND23；可改写为：SELECTSname，Sdept，SageFROMSWHERESage>=20ANDSage<=23；三、对查询结果排序

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

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

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

SELECTCOUNT(*)FROMS；

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

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

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

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

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

结果：

CnoCOUNT(Sno) 122 234 344 433 548对查询结果分组（续）GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数使用HAVING短语筛选最终输出结果[例30]查询选修了3门以上课程的学生学号。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

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

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4集合查询3.3.5嵌套查询3.3.6小结3.3.3连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符：=、>、<、>=、<=、!=连接查询（续）SQL中连接查询的主要类型 广义笛卡尔积 等值连接(含自然连接)

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

SELECTS.*,SC.*FROMS,SC；二、等值与非等值连接查询等值连接、自然连接、非等值连接[例32]查询学生及其选修课程的情况等价于：

SELECTS.*，SC.*FROMS，SCWHERES.Sno=SC.Sno；SELECTS.*，SC.*FROMSINNERJOINSCONS.Sno=SC.Sno;等值连接连接运算符为=的连接操作

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

S表SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISSC表SnoCnoGrade9500119295001285950013889500229095002380等值连接结果表S.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002刘晨女19 IS 9500229095002刘晨女19 IS 95002380

自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。[例33]查询学生及其选修课程的情况（用自然连接完成）。

SELECTS.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMS，SCWHERES.Sno=SC.Sno；非等值连接查询连接运算符不是=

的连接操作

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：>、<、>=、<=、!=三、自身连接一个表与其自己进行连接，称为表的自身连接；需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。自身连接（续）[例34]查询每一门课的间接先修课（即先修课的先修课）

SELECTFIRST.Cno，SECOND.CpnoFROMCasFIRST，CasSECONDWHEREFIRST.Cpno=SECOND.Cno；

FIRST表（C表）CnoCnameCpnoCcredit1数据库542数学

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

27PASCAL语言64SECOND表（C表）CnoCnameCpnoCcredit1数据库542数学

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

27PASCAL语言64查询结果

173556

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

[例35]查询每个学生及其选修课程的情况包括没有选修课程的学生----用外连接操作

SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMS,SCWHERES.Sno=SC.Sno(*)；或者

SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno，GradeFROMSLEFT(OUTER)JOIN

SC

ONS.Sno=SC.Sno；结果：

S.SnoSnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095002刘晨女19IS38095003王敏女18MA95004张立男19IS外连接（续）左外连接LEFT(OUTER)JOIN<表名>ON<条件>;右外连接RIGHT(OUTER)JOIN<表名>ON<条件>;全外连接FULL(OUTER)JOIN<表名>ON<条件>;五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例36]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名SELECTS.Sno,S.SnameFROMS,SCWHERES.Sno=SC.SnoAND/*连接谓词*/SC.Cno='2'AND/*其他限定条件*/SC.Grade>90；

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

SELECTS.Sno，Sname，Cname，GradeFROMS，SC，CWHERES.Sno=SC.SnoandSC.Cno=C.Cno；

结果：

S.SnoSnameCnameGrade 95001李勇数据库9295001李勇数学8595001李勇信息系统8895002刘晨数学9095002刘晨信息系统80TOP谓词例：查询成绩前五名的学生姓名，课程名及成绩。SELECTTOP5SNAME,CNAME,GRADEFROMS,SC,CWHERES.SNO=SC.SNOANDSC.CNO=C.CNOORDERBYGRADEDESC;3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4集合查询3.3.5嵌套查询3.3.6小结3.3.4集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)1．并操作形式

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

(SELECT*FROMSWHERESdept='CS‘)UNION(SELECT*FROMSWHERESage<=19)；方法二：

SELECT*FROMSWHERESdept='CS'ORSage<=19；[例39]查询选修了课程1或者选修了课程2的学生。方法一：

(SELECTSnoFROMSCWHERECno='1')UNION(SELECTSnoFROMSCWHERECno='2')；方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[例40]设数据库中有一教师表Teacher(Tno,Tname,...)。查询学校中所有师生的姓名。

(SELECTSnameFROMS)UNION(SELECTTnameFROMTeacher);2．交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。有些DBMS支持交运算：形式

<查询块>INTERSECT<查询块>[例41]查询既选修了课程1又选修了课程2的学生学号。

(SELECTSC.SNOFROMSCWHERESC.CNO='1')

INTERSECT(SELECTSC.SNOFROMSCWHERESC.CNO='2');3．差操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。有些DBMS支持交运算：形式

<查询块>EXCEPT<查询块>3．差操作[例42]查询只选修课程1而没有选修课程2的学生学号。

(SELECTSC.SNOFROMSCWHERESC.CNO='1')

EXCEPT(SELECTSC.SNOFROMSCWHERESC.CNO='2');3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4集合查询3.3.5嵌套查询3.3.6小结3.3.5嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词

嵌套查询(续)嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询(续)例：查询选修了2号课程的学生姓名。SELECTSname FROMSWHERESnoINSELECTSnoFROMSCWHERECno='2'内层查询/子查询外层查询/父查询（）;[例41]查询既选修了课程1又选修了课程2的学生学号。(解法二)

SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询一、带有IN谓词的子查询[例44]查询与“刘晨”在同一个系学习的学生。

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

SELECTSdeptFROMSWHERESname='刘晨'； 结果为：SdeptIS带有IN谓词的子查询（续）②查找所有在IS系学习的学生。

SELECTSno，Sname，SdeptFROMSWHERESdept='IS'；结果为：SnoSnameSdept95001刘晨IS95004张立IS

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

SELECTSno，Sname，SdeptFROMSWHERESdeptIN(SELECTSdeptFROMSWHERESname=‘

刘晨’)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。带有IN谓词的子查询（续）

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

SELECTS1.Sno，S1.Sname，S1.SdeptFROMSasS1，SasS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='刘晨'；带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名

SELECTSno，Sname，SdeptFROMSasS1WHERES1.SdeptIN(SELECTSdeptFROMSasS2WHERES2.Sname=‘

刘晨’)；带有IN谓词的子查询（续）[例45]查询选修了课程名为“信息系统”的学生学号和姓名(SELECTCno

FROMC

WHERECname=‘信息系统’));(SELECTSnoFROMSC

WHERECnoINSELECTSno，SnameFROMSWHERESnoIN带有IN谓词的子查询（续）用连接查询

SELECTSno，SnameFROMS，SC，CWHERES.Sno=SC.SnoANDSC.Cno=C.CnoANDC.Cname=‘信息系统’；二、带有比较运算符的子查询

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

：

SELECTSno，Sname，SdeptFROMSWHERESdept=(SELECTSdeptFROMSWHERESname='刘晨')；带有比较运算符的子查询（续）

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

错误的例子：

SELECTSno，Sname，SdeptFROMSWHERE(SELECTSdeptFROMSWHERESname=‘

刘晨’

)

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

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

SELECTSname，SageFROMSWHERESage<ANY(SELECTSageFROMSWHERESdept='IS')

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

Sname Sage

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

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

19或18的学生带有ANY或ALL谓词的子查询（续）用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数带有ANY或ALL谓词的子查询（续）[例46']：用集函数实现[例46]SELECTSname，SageFROMSWHERESage<(SELECTMAX(Sage)FROMSWHERESdept='IS')ANDSdept<>'IS’;带有ANY或ALL谓词的子查询（续）[例47]查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

SELECTSname，SageFROMSWHERESage<ALL(SELECTSageFROMSWHERESdept='IS')ANDSdept<>'IS’;带有ANY或ALL谓词的子查询（续）

方法二：用集函数

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

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

SELECTSnameFROMSWHEREEXISTS/*相关子查询*/(SELECT*FROMSCWHERESno=S.SnoANDCno=‘1’)；

思路分析：本查询涉及S和SC关系。在S中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此S.Sno值，并且其Cno=‘1’，则取此S.Sname送入结果关系。也可以用连接运算实现SELECTSnameFROMS,SCWHERES.Sno=SC.SnoANDSC.Cno='1';[例49]查询没有选修1号课程的学生姓名。

SELECTSnameFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=‘1’)；此例用连接运算难于实现

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

（Forall）思考：查询选修了全部课程的学生姓名。思路：构建某个学生所选修的课程集合，记为A；构建全部的课程集合，记为B；某个学生选修了全部课程，可以表示为：

¬(B-A)165(SELECTCNO FROMC)

EXCEPT (SELECTDISTINCTCNO FROMSC WHERESC.SNO=XXX)没有被某个学生选修的课程号166SELECT SNAMEFROMSWHERENOTEXISTS ((SELECTCNO FROMC)

EXCEPT

(SELECTCNO FROMSC WHERESC.SNO=S.SNO))某个学生所选的课程所有的课程除法表示方法1167SELECTSNAMEFROMSWHERENOTEXISTS(SELECTCNOFROMCWHERENOTEXISTS (SELECTDISTINCTCNO FROMSCWHERESC.CNO=C.CNO

andSC.SNO=S.SNO))除法表示方法2用NOTEXISTS表示EXCEPT[例50]查询选修了全部课程的学生姓名。

SELECTSnameFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=C.Cno);[例51]查询至少选修了学生95002选修的全部课程的学生学号。

SELECTDISTINCTSnoFROMSCasSCXWHERENOTEXISTS(SELECT*FROMSCasSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCasSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；[52]求被所有的学生都选修了的课程名SELECT