第5章 关系数据库标准语言—SQL.ppt

数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院第5章 关系数据库标准语言SQL Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院关系数据库标准语言SQL5.1SQL概述及特点概述及特点5.2数据定义语句数据定义语句 5.3数据查询语句数据查询语句5.4数据更新语句数据更新语句5.5嵌入式嵌入式SQL5.6数据控制机制和语句数据控制机制和语句数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院5.1 SQL5.1 SQL概述及特点概述及特点1.SQL的主要功能的主要功能(1)(1)数据定义功能数据定义功能 定义关系数据库的模式、外模式和内模式，以实现对基本定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。表、视图以及索引文件的定义、修改和删除等操作。(2)(2)数据操纵功能数据操纵功能 包括数据查询和数据更新两种数据操作语句：数据查询指包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。指数据的插入、删除、修改等数据维护操作。(3)(3)数据控制功能数据控制功能 通过对数据库用户的授权和收权命令来实现有关数据的存通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。取控制，以保证数据库的安全性。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院2.SQL的特点 (1)SQL(1)SQL具有自含式和嵌入式两种形式。具有自含式和嵌入式两种形式。(2)SQL(2)SQL具有语言简洁、易学易用的特点。具有语言简洁、易学易用的特点。(3)SQL(3)SQL支持三级模式结构。支持三级模式结构。全体基本表构成了数据库的模式。全体基本表构成了数据库的模式。视图和部分基本表构成了数据库的外模式。视图和部分基本表构成了数据库的外模式。数据库的存储文件和它们的索引文件构成了关系数据库的数据库的存储文件和它们的索引文件构成了关系数据库的内模式。内模式。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院nSQL功能极强，完成核心功能只用了9个动词。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模式外模式模模式式内模式内模式SQL支持关系数据库三级模式结构数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院5.2 数据定义语句5.2.1 5.2.1 基本表的定义和维护基本表的定义和维护1.1.定义基本表定义基本表定义基本表语句的一般格式为：定义基本表语句的一般格式为：CREATE TABLE CREATE TABLE 库名库名 表名表名(列名数据类型列名数据类型 列级完整性约束条件列级完整性约束条件 ，列名数据类型列名数据类型 列级完整性约束条件列级完整性约束条件 ，nn ，表级完整性约束条件，表级完整性约束条件 ，n)n)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院类型表示类型表示类型说明类型说明数值型数值型数据数据SMALLINT半字长二进制整数。半字长二进制整数。15bits数据数据INTEGER或或INT全字长（四字长）整数。全字长（四字长）整数。31bits数据数据DECIMAL(p，q)十进制数，共十进制数，共p位，其中小数点后位，其中小数点后q位。位。0qp，q=0时可省略不写时可省略不写FLOAT双字长浮点数双字长浮点数字符型字符型数据数据CHARTER(n)或或CHAR(n)长度为长度为n的定长字符串的定长字符串VARCHAR(n)最大长度为最大长度为n的变长字符串的变长字符串特殊数据类型特殊数据类型GRAPHIC(n)长度为长度为n的定长图形字符串的定长图形字符串VARGRAPHIC(n)最大长度为最大长度为n的变长图形字符串的变长图形字符串日期时日期时间型间型DATE日期型，格式为日期型，格式为YYYY-MM-DDTIME时间型，格式为时间型，格式为HH.MM.SSTIMESTAMP日期加时间日期加时间(1)SQL(1)SQL支持的数据类型支持的数据类型数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(2)列级完整性的约束条件针对属性值设置的限制条件。针对属性值设置的限制条件。1)NOT NULL1)NOT NULL或或NULLNULL约束。约束。NOT NULLNOT NULL约束不允许字段值为空，而约束不允许字段值为空，而NULLNULL约束允许字段值为空。约束允许字段值为空。2)UNIQUE2)UNIQUE约束。惟一性约束，即不允许列中出现重复的属性值。约束。惟一性约束，即不允许列中出现重复的属性值。3)DEFAULT3)DEFAULT约束。默认值约束。约束。默认值约束。DEFAULT约束名约束名默认值默认值FOR列名列名4)CHECK4)CHECK约束。检查约束。约束。检查约束。CONSTRAINT约束名约束名CHECK(约束条件表达式约束条件表达式)数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(3)表级完整性约束条件表级完整性约束条件 涉及到关系中多个列的限制条件。涉及到关系中多个列的限制条件。1)UNIQUE1)UNIQUE约束。惟一性约束。约束。惟一性约束。2)PRIMARY KEY2)PRIMARY KEY约束。定义主码，保证惟一性和非空性。约束。定义主码，保证惟一性和非空性。CONTRAINT CONTRAINT约束名约束名PRIMARY KEY CLUSTERED(PRIMARY KEY CLUSTERED(列组列组)3)FOREIGN KEY3)FOREIGN KEY约束。用于定义参照完整性。约束。用于定义参照完整性。CONTRAINT CONTRAINT约束名约束名FOREIGN KEY(FOREIGN KEY(外码外码)REFERENCES REFERENCES被参照表名被参照表名(与外码对应的主码名与外码对应的主码名)数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院 CREATE TABLE CREATE TABLE 学生学生(学号学号 CHAR(5)NOT NULL UNIQUE CHAR(5)NOT NULL UNIQUE，姓名姓名 CHAR(8)NOT NULL CHAR(8)NOT NULL，年龄年龄 SMALLINT SMALLINT，性别性别 CHAR(2)CHAR(2)，所在系所在系 CHAR(20)CHAR(20)，DEFAULT C1 20 FOR DEFAULT C1 20 FOR 年龄，年龄，CONSTRAINT C2 CHECK(CONSTRAINT C2 CHECK(性别性别 IN(IN(男男，女女)；建立基本表：建立基本表：学生学生(学号，姓名，年龄，性别，所在系学号，姓名，年龄，性别，所在系)；课程课程(课程号，课程名，先行课课程号，课程名，先行课)；选课选课(学号，课程号，成绩学号，课程号，成绩).数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院CREATETABLE课程课程(课程号课程号CHAR(5)PRIMARYKEY，课程名课程名CHAR(20)，先行课先行课CHAR(5)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院CREATETABLE选课选课(学号学号CHAR(5)，课程号课程号CHAR(5)，成绩成绩SMALLINT，CONSTRAINTC3CHECK(成绩成绩BETWEEN0AND100)，CONSTRAINTC4PRIMARYKEY(学号，课程号学号，课程号)，CONSTRAINTC5FOREIGNKEY(学号学号)REFERENCES学生学生(学号学号)，CONSTRAINTC6FOREIGNKEY(课程号课程号)REFERENCES课程课程(课程号课程号)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性定义实体完整性定义n关系模型的实体完整性CREATETABLE中用PRIMARYKEY定义n单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件n对多个属性构成的码只有一种说明方法定义为表级约束条件数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性定义实体完整性定义(续续)例将Student表中的Sno属性定义为码(1)在列级定义主码在列级定义主码CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性定义实体完整性定义(续续)(2)在表级定义主码在表级定义主码CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性定义实体完整性定义(续续)例将SC表中的Sno，Cno属性组定义为码CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY(Sno，Cno)/*只能在表级定义主码*/);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性检查和违约处理实体完整性检查和违约处理n插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：1.检查主码值是否唯一，如果不唯一则拒绝插入或修改2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性检查和违约处理实体完整性检查和违约处理(续续)n检查记录中主码值是否唯一的一种方法是进行全表扫描数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院实体完整性检查和违约处理实体完整性检查和违约处理(续续)n索引数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院参照完整性定义参照完整性定义n关系模型的参照完整性定义在CREATETABLE中用FOREIGNKEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院参照完整性定义参照完整性定义(续续)例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码例定义SC中的参照完整性CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY(Sno，Cno)，/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*在表级定义参照完整性*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*在表级定义参照完整性*/);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院参照完整性检查和违约处理参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理被参照表（例如Student）参照表（例如SC）违约处理可能破坏参照完整性 插入元组拒绝可能破坏参照完整性 修改外码值拒绝删除元组 可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值 可能破坏参照完整性拒绝/级连修改/设置为空值数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院违约处理违约处理n参照完整性违约处理1.拒绝(NOACTION)执行默认策略2.级联(CASCADE)操作3.设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院违约处理违约处理(续续)例显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno），FOREIGNKEY(Sno)REFERENCESStudent(Sno)ONDELETECASCADE/*级联删除SC表中相应的元组*/ONUPDATECASCADE，/*级联更新SC表中相应的元组*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETENOACTION/*当删除course表中的元组造成了与SC表不一致时拒绝删除*/ONUPDATECASCADE/*当更新course表中的cno时，级联更新SC表中相应的元组*/)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院用户定义的完整性用户定义的完整性n用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求nRDBMS提供，而不必由应用程序承担数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院属性上的约束条件的定义属性上的约束条件的定义nCREATETABLE时定义列值非空（NOTNULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院属性上的约束条件的定义属性上的约束条件的定义(续续)n1.不允许取空值例在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。CREATETABLESC（SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINTNOTNULL，PRIMARYKEY(Sno，Cno)，/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/）；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院属性上的约束条件的定义属性上的约束条件的定义(续续)n2.列值唯一例建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码CREATETABLEDEPT(DeptnoNUMERIC(2)，DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，PRIMARYKEY(Deptno)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院属性上的约束条件的定义属性上的约束条件的定义(续续)n3.用CHECK短语指定列值应该满足的条件例7Student表的Ssex只允许取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(8)NOTNULL，SsexCHAR(2)CHECK(SsexIN(男，女)，/*性别属性Ssex只允许取男或女*/SageSMALLINT，SdeptCHAR(20);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理属性上的约束条件检查和违约处理n插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足n如果不满足则操作被拒绝执行数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院元组上的约束条件的定义元组上的约束条件的定义n在CREATETABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制n同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院元组上的约束条件的定义元组上的约束条件的定义(续续)例9当学生的性别是男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，CHECK(Ssex=女女 OR Sname NOT LIKE Ms.%)/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/)；性别是女性的元组都能通过该项检查，因为Ssex=女成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理元组上的约束条件检查和违约处理n插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足n如果不满足则操作被拒绝执行数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院完整性约束命名子句完整性约束命名子句nCONSTRAINT约束CONSTRAINTPRIMARYKEY短语|FOREIGNKEY短语|CHECK短语数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院完整性约束命名子句完整性约束命名子句(续续)例建立学生登记表Student，要求学号在9000099999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。CREATETABLEStudent(SnoNUMERIC(6)CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，SnameCHAR(20)CONSTRAINTC2NOTNULL，SageNUMERIC(3)CONSTRAINTC3CHECK(Sage30)，SsexCHAR(2)CONSTRAINTC4CHECK(SsexIN(男，女)，CONSTRAINTStudentKeyPRIMARYKEY(Sno)；在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院2.2.修改基本表和删除基本表修改基本表和删除基本表 ALTER TABLE表名表名 ADD(新列名新列名数据类型数据类型完整性约束完整性约束，n)DROP完整性约束名完整性约束名 MODIFY(列名列名数据类型数据类型，n)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院n向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。n增加课程名称必须取唯一值的约束条件。ALTERTABLECourseADDUNIQUE(Cname);数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院例修改表Student中的约束条件，要求学号改为在900000999999之间，年龄由小于30改为小于40可以先删除原来的约束条件，再增加新的约束条件ALTERTABLEStudentDROPCONSTRAINTC1;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，ALTERTABLEStudentDROPCONSTRAINTC3;ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage（大于）、（大于）、（小于）、（小于）、=（大于等于）、（大于等于）、!=!=（不等于）、（不等于）、（小于大于）、（小于大于）、!（不（不大于）和大于）和!、=、9090；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(2)自身连接自身连接例如，课程表中的先行课是在上学期应开设的，先行课的例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。程表进行自身连接。课程号课程号课程名课程名先行课先行课C1计算机引论计算机引论C2PASCAL语言语言C1C3数据结构数据结构C2C4数据库数据库C3C5软件工程软件工程C4课程的先行关系链为：课程的先行关系链为：C5C4C3C2C1，课程的间接关系链为：课程的间接关系链为：C5C3C1。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院课程号课程号课程名课程名先行课先行课课程号课程号课程名课程名先行课先行课C1计算机引论计算机引论C1计计 算算 机机 引引论论C2Pascal语言语言C1C2Pascal语言语言C1C3数据结构数据结构C2C3数据结构数据结构C2C4数据库数据库C3C4数据库数据库C3C5软件工程软件工程C4C5软件工程软件工程C4A.课程号课程号A.课程名课程名B.先行课先行课C2Pascal语言语言C3数据结构数据结构C1C4数据库数据库C2C5软件工程软件工程C3AB结果【例】查询每一门课的间接先行课。SELECTA.课程号，A.课程名，B.先行课FROM课程A，课程BWHEREA.先行课=B.课程号数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院关系代数:n外连接如果把舍弃的元组也保存在结果关系中，而在其他属性上填空值(Null)，这种连接就叫做外连接（OUTERJOIN）。n左外连接如果只把左边关系R中要舍弃的元组保留就叫做左外连接(LEFTOUTERJOIN或LEFTJOIN)n右外连接如果只把右边关系S中要舍弃的元组保留就叫做右外连接(RIGHTOUTERJOIN或RIGHTJOIN)。数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院loan-numberamountL-170L-230L-260300040001700customer-nameloan-numberJonesSmithHayesL-170L-230L-155branch-nameDowntownRedwoodPerryridge关系loan关系borrower例：数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院内连接内连接loan Borrowerloan borrower 左外连接左外连接loan-numberamountL-170L-23030004000customer-nameJonesSmithbranch-nameDowntownRedwoodloan-numberamountL-170L-230L-260300040001700customer-nameJonesSmithnullbranch-nameDowntownRedwoodPerryridge数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院右外连接右外连接 loanborrowerloan-numberamountL-170L-230L-15530004000nullcustomer-nameJonesSmithHayesloan-numberamountL-170L-230L-260L-155300040001700nullcustomer-nameJonesSmithnullHayesloan borrower 全外连接全外连接branch-nameDowntownRedwoodnullbranch-nameDowntownRedwoodPerryridgenull数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(3)外部连接外部连接左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为外部连接符号为“*=”，右外部连接符号为，右外部连接符号为“=*”。外部连接中不匹。外部连接中不匹配的分量用配的分量用NULL表示。表示。职工职工号号姓名姓名性别性别年龄年龄所在部所在部门门部门部门号号部门名部门名称称电话电话1010李勇李勇男男201111生产科生产科5661011刘晨刘晨女女1912计划科计划科5781012王敏王敏女女221213一车间一车间4671014张立张立男男211314科研所科研所职工表职工表 部门表部门表 数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院连接的结果集连接的结果集 职工号职工号姓名姓名性别性别年龄年龄所在部门所在部门部门名称部门名称电话电话1010李勇李勇男男2011生产科生产科5661012王敏王敏女女2212计划科计划科5781014张立张立男男2113一车间一车间467职工号职工号姓名姓名性别性别年龄年龄所在部门所在部门部门名称部门名称电话电话1010李勇李勇男男2011生产科生产科5661011刘晨刘晨女女191012王敏王敏女女2212计划科计划科5781014张立张立男男2113一车间一车间467内连接的结果集内连接的结果集 左外部连接的结果集左外部连接的结果集数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院内连接：内连接：SELECT SELECT 职工职工.*.*，部门名称，电话，部门名称，电话 FROM FROM 职工，部门职工，部门 WHERE WHERE 职工职工.所在部门所在部门=部门部门.部门号；部门号；左外部连接：左外部连接：SELECT SELECT 职工职工.*.*，部门名称，电话，部门名称，电话 FROM FROM 职工，部门职工，部门 WHERE WHERE 职工职工.所在部门所在部门*=*=部门部门.部门号；部门号；右外部连接：右外部连接：SELECT SELECT 职工职工.*.*，部门名称，电话，部门名称，电话 FROM FROM 职工，部门职工，部门 WHERE WHERE 职工职工.所在部门所在部门=*=*部门部门.部门号；部门号；用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院3.3.嵌套查询嵌套查询嵌套查询嵌套查询(1)使用使用IN操作符的嵌套查询操作符的嵌套查询【例例5-20】求选修了高等数学的学生学号和姓名。求选修了高等数学的学生学号和姓名。SELECT SELECT 学号，姓名学号，姓名 FROM FROM 学生学生 WHERE WHERE 学号学号 IN(SELECT IN(SELECT 学号学号 FROM FROM 选课选课 WHERE WHERE 课程号课程号 IN(SELECT IN(SELECT 课程号课程号 FROM FROM 课程课程 WHERE WHERE 课程名课程名=高等数学高等数学)；该题也可以使用下面的连接查询表达。该题也可以使用下面的连接查询表达。SELECT SELECT 学生学生.学号，姓名学号，姓名 FROM FROM 学生，课程，选课学生，课程，选课 WHERE WHERE 学生学生.学号学号=课程课程.学号学号 AND AND 课程课程.课程号课程号=选课选课.课程号课程号 AND AND 课程课程.课程名课程名=高等数学高等数学；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(2)使用比较符的嵌套查询使用比较符的嵌套查询【例例】求求C1课程的成绩高于张三的学生学号和成绩。课程的成绩高于张三的学生学号和成绩。SELECT SELECT 学号，成绩学号，成绩 FROM FROM 选课选课 WHERE WHERE 课程号课程号=C1 AND=C1 AND 成绩成绩 (SELEC SELEC 成绩成绩 FROM FROM 选课选课 WHERE WHERE 课程号课程号=C1AND=C1AND 学号学号=(SELECT (SELECT 学号学号 FROM FROM 学生学生 WHERE WHERE 姓名姓名=张三张三)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(3)(3)使用使用ANYANY或或ALLALL操作符的嵌套查询操作符的嵌套查询格式为：字段比较符格式为：字段比较符ANY|ALLANY|ALL子查询子查询操作符操作符语意语意ANY大于子查询结果中的某个值，即表示大于查询结果中最小值大于子查询结果中的某个值，即表示大于查询结果中最小值ALL大于子查询结果中的所有值，即表示大于查询结果中最大值大于子查询结果中的所有值，即表示大于查询结果中最大值ANY小于子查询结果中的某个值，即表示小于查询结果中最大值小于子查询结果中的某个值，即表示小于查询结果中最大值=ANY大于等于子查询结果中的某个值，即表示大于等于结果集中最小值大于等于子查询结果中的某个值，即表示大于等于结果集中最小值=ALL大于等于子查询结果中的所有值，即表示大于等于结果集中最大值大于等于子查询结果中的所有值，即表示大于等于结果集中最大值=ANY小于等于子查询结果中的某个值，即表示小于等于结果集中最大值小于等于子查询结果中的某个值，即表示小于等于结果集中最大值=ALL小于等于子查询结果中的所有值，即表示小于等于结果集中最小值小于等于子查询结果中的所有值，即表示小于等于结果集中最小值=ANY等于子查询结果中的某个值，即相当于等于子查询结果中的某个值，即相当于IN=ALL等于子查询结果中的所有值等于子查询结果中的所有值(通常没有实际意义通常没有实际意义)!=(或或)ANY不等于子查询结果中的某个值，不等于子查询结果中的某个值，!=(或或)ALL不等于子查询结果中的任何一个值，即相当于不等于子查询结果中的任何一个值，即相当于NOT IN数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院【例】【例】求其他系中比计算机系某一学生年龄小的学生。求其他系中比计算机系某一学生年龄小的学生。SELECT*SELECT*FROM FROM 学生学生 WHERE WHERE 年龄年龄 ANY(SELECT ANY(SELECT 年龄年龄 FROM FROM 学生学生 WHERE WHERE 所在系所在系=计算机系计算机系)AND)AND 所在系所在系计算机系计算机系；【例】【例】求其他系中比计算机系学生年龄都小的学生。求其他系中比计算机系学生年龄都小的学生。SELECT*SELECT*FROM FROM 学生学生 WHERE WHERE 年龄年龄 ALL(SELECT ALL(SELECT 年龄年龄 FROM FROM 学生学生 WHERE WHERE 所在系所在系=计算机系计算机系)AND)AND 所在系所在系 计算机系计算机系；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院(4)(4)使用使用EXISTSEXISTS操作符的嵌套查询操作符的嵌套查询【例】求选修了【例】求选修了C2C2课程的学生姓名。课程的学生姓名。SELECT SELECT 姓名姓名 FROM FROM 学生学生 WHERE EXISTS(SELECT*FROM WHERE EXISTS(SELECT*FROM 选课选课 WHERE WHERE 学生学生.学号学号=学号学号 AND AND 课程号课程号=C2)=C2)；【例】求没有选修【例】求没有选修C2C2课程的学生姓名。课程的学生姓名。SELECT SELECT 姓名姓名 FROM FROM 学生学生 WHERE NOT EXISTS(SELECT*FROM WHERE NOT EXISTS(SELECT*FROM 选课选课 WHERE WHERE 学生学生.学号学号=学号学号 AND AND 课程号课程号=C2)=C2)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院【例】【例】查询选修了全部课程的学生的姓名。查询选修了全部课程的学生的姓名。SELECT SELECT 姓名姓名 FROM FROM 学生学生 WHERE NOT EXISTS WHERE NOT EXISTS(SELECT*FROM(SELECT*FROM 课程课程 WHERE NOT EXISTS WHERE NOT EXISTS(SELECT*(SELECT*FROM FROM 选课选课 WHERE WHERE 学生学生.学号学号=学号学号 AND AND 课程课程.课程号课程号=课程号课程号)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院【例例】求求至至少少选选修修了了学学号号为为“S2”“S2”的的学学生生所所选选修修的的全全部部课课程程的的学学生学号和姓名。生学号和姓名。SELECT SELECT 学号，姓名学号，姓名 FROM FROM 学生学生 WHERE NOT EXISTS(SELECT*FROM WHERE NOT EXISTS(SELECT*FROM 选课选课 选课选课1 1 WHERE WHERE 选课选课1.1.学号学号=S2 AND NOT EXISTS=S2 AND NOT EXISTS (SELECT*FROM (SELECT*FROM 选课选课 选课选课2 2 WHERE WHERE 学生学生.学号学号=选课选课2.2.学号学号 AND AND 选课选课2.2.课程号课程号=选课选课1.1.课程号课程号)；数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院n不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换n用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x)P(x(P)数据库技术与应用数据库技术与应用北京邮电大学北京邮电大学计算机学院计算机学院4.组合查询【例】求选修了【例】求选修了C1C1课程或选修了课程或选修了C2C2课程的学生学号。课程的学生学号。SELECT SELECT 学号学号 FROM FROM 选课选课 WHERE WHERE 课程号课程号=C1=C1 UNION UNION SELECT SELECT 学号学号 FROM FROM 选课选课 WHERE WHERE 课程号课程号=C2=C2【例】求选修【例】求选修C1C1课程，并且也选修课程