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

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

熟练正确的使用SQL完成对数据库的查询、插入、删除、更新操作。用SQL语言正确完成复杂查询，掌握SQL语言强大的查询功能。

教学内容

牢固掌握SQL，达到举一反三的掌握SQL的功能。同时通过实践,体会面向过程的语言和SQL的区别和优点。体会关系数据库系统为数据库应用系统的开发提供良好环境，减轻用户负担，提高用户生产率的原因。

教学目标§3关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6空值的处理3.7视图3.8小结§3.1SQL概述SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，充分体现了关系数据库语言的特点和优点。其主要特点包括：3.1.2SQL特点1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供多种使用方法5.语言简洁，易学易用§3.1SQL概述

另外，在关系模型中，实体和实体间的联系均用关系表示，这种数据结构的单一性带来了数据操作符的统一性，查找、插入、删除、更新等每一种操作都只需一种操作符。1.综合统一SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。§3.1SQL概述2.高度非过程化

用SQL语言进行数据库操作时，用户只需提出“做什么”，而不必指明“怎么做”。因此，用户无需了解数据存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。§3.1SQL概述3.面向集合的操作方式

而SQL语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。§3.1SQL概述4.以同一种语法结构提供多种使用方法

作为嵌入式语言，SQL语句能够嵌入到高级语言(例如C，C#，JAVA等)程序中，供程序员设计程序时使用。

作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；§3.1SQL概述5.语言简洁，易学易用SQL语言功能极强，由于设计巧妙，语言十分简洁，完成核心功能只用了9个动词，如表所示：表3.2SQL的动词§3.1SQL概述3.1.3SQL的基本概念SQL对关系数据库模式的支持

基本表是本身独立存在的表；一个或多个基本表对应一个存储文件，一个基本表可以带若干索引，索引也存放在存储文件中。

存储文件的逻辑结构组成了关系数据库的内模式；存储文件的物理结构是任意的，对用户是透明的。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图在概念上与基本表等同，用户可以在视图上再定义视图。§3.2学生-课程数据库

定义一个学生-课程模式S-T，学生-课程数据库中包含3张表：学生表：Student（Sno，Sname，Ssex，Sage，Sdept）课程表：Course（Cno，Cname，Cpno，Ccredit）学生选课表：SC（Sno，Cno，Grade）§3.3数据定义SQL语言支持数据库三级模式结构内模式中的基本对象：外模式中的基本对象：模式中的基本对象：表视图索引§3.3数据定义3.3.1模式的定义与删除1.定义模式CREATESCHEMA<模式名>AUTHORIZATION<用户名>SQL语言使用CREATESCHEMA语句创建模式，其一般格式为：

要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的CREATESCHEMA的权限。§3.3数据定义例3.1为用户WANG定义一个学生-课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG例3.2CREATESCHEMAAUTHORIZATIONWANG§3.3数据定义2.删除模式SQL中删除模式的语句：DROPSCHEMA<模式名>

<CASCADE|RESTRICT>

例3.4DROPSCHEMAZHANGCASCADE；§3.3数据定义3.3.2基本表的定义、删除与修改1.定义基本表SQL语言使用CREATETABLE语句创建基本表，其一般格式为：CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件§3.3数据定义

例题：建立一个职工表emp，它由职工号eno、姓名ename、性别sex、年龄age、部门dept五个属性组成，其中职工号不能为空且取值惟一。CREATETABLEemp (enoCHAR(5)NOTNULLUNIQUE，

enameCHAR(8)，

sexCHAR(1)，

ageINT，

deptCHAR(16))；

执行上面的CREATETABLE语句后，就在数据库中建立了一个新的空的职工表emp，并将有关职工表的定义及有关约束条件存放在数据字典中。§3.3数据定义常用完整性约束主码约束：PRIMARYKEY

唯一性约束：UNIQUE

非空值约束：NOTNULL

参照完整性约束PRIMARYKEY与

UNIQUE的区别？§3.3数据定义[例3.7]建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。

CREATETABLESC(SnoCHAR(5)，

CnoCHAR(3)，

Gradeint，

Primarykey(Sno,Cno))；表级完整性约束条件的例子：§3.3数据定义2.数据类型

在SQL中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及长度。注意：不同的RDBMS中支持数据类型不完全相同。

一个属性选用哪种数据类型要根据实际情况来决定，一般要从两个方面来考虑：一是取值范围，二是要做哪些运算。P83表3.4数据类型§3.3数据定义3.模式与表

模式与表的对应关系是1：n，定义表时如何处理它所属的模式存在三种方法：方法一、在表名中明显地给出模式名；方法二、在创建模式语句中同时创建表；

方法三、设置所属的模式，在创建表时表名不必给出模式名；§3.3数据定义4.修改基本表SQL语言用ALTERTABLE语句修改基本表，其一般格式为ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和数据类型§3.3数据定义[例3.8]向Student表增加“入学时间”列，其数据类型为日期型。

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

ALTERTABLEStudentALTERCOLUMNSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据[例]删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；§3.3数据定义5.删除基本表

当某个基本表不再需要时，可以使用SQL语句DROPTABLE进行删除，其一般格式为：DROPTABLE<表名>[RESTRICT|CASCADE]；

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

DROPTABLE

StudentCASCADE;

基本表一旦被删除，表中的数据和在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此，执行删除基本表操作一定要格外小心。§3.3数据定义3.3.3索引的建立与删除

一般说来，建立与删除索引由数据库管理员DBA或表的属主(即建立表的人)负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。建立索引是加快表的查询速度的有效手段。§3.3数据定义1.建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引§3.3数据定义在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作[例3.13]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；§3.3数据定义2.修改索引

在SQL语言中，修改索引使用ALTERINDEX语句，其一般格式为：ALTERINDEX<旧索引名>RENAMETO<新索引名>；[例3.14]将SC表的SCno索引名改为SCSno。

ALTERINDEXSCnoRENAMETOSCSno；

§3.3数据定义3.删除索引

建立索引是为了减少查询操作的时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。在SQL语言中，删除索引使用DROPINDEX语句，其一般格式为：

DROPINDEX<索引名>[例3.15]删除Student表上的Stusname索引。

DROPINDEXStudent；删除索引时，系统会同时从数据字典中删去有关该索引的描述。§3.3数据定义3.3.4数据字典

数据字典最重要的作用是作为分析阶段的工具。在结构化分析中，数据字典的作用是给数据流图上每个成分加以定义和说明，数据字典中严密一致的定义有助于改进分析员和用户的通信。

在查询优化和查询处理时，数据字典中的信息是重要的依据。§3.4数据查询语句格式：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

§3.4数据查询SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序§3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5基于派生表的查询3.4.6SELECT语句的一般格式§3.4数据查询3.4.1单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、使用集函数五、对查询结果分组§3.4数据查询一、选择表中的若干列1、查询指定列2、查询全部列3、查询经过计算的值§3.4数据查询1、查询指定列[例3.16]查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；

§3.4数据查询2、查询全部列[例3.18]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；//方法1SELECT*FROMStudent；//方法2§3.4数据查询3、查询经过计算的值SELECT子句的<目标列表达式>为下列表达式：算术表达式字符串常量函数列别名其他§3.4数据查询[例3.20]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

SELECTSname，'YearofBirth:'，2000-SageLOWER(Sdept)FROMStudent；

输出结果：

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

李勇YearofBirth:1976cs

刘晨YearofBirth:1977is

王名YearofBirth:1978ma

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

SnoCnoGrade---------------------9500119295001285950013889500229095002380[例3.21]查询选修了课程的学生学号。SELECTSnoFROMSC;

结果：Sno-------95001950019500195002950021.消除取值重复的行§3.4数据查询SELECTDISTINCTSnoFROMSC;

结果：

Sno-------9500195002SELECTSnoFROMSC;

等价于(默认ALL)SELECTALLSnoFROMSC;§3.4数据查询注意DISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;

§3.4数据查询2.查询满足条件的元组重点内容(1)比较大小(2)确定范围(3)确定集合(4)字符串匹配(5)涉及空值的查询(6)多重条件查询§3.4数据查询(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例3.23]查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；§3.4数据查询使用谓词BETWEEN…AND…NOTBETWEEN…AND…[例3.25]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

(2)确定范围§3.4数据查询使用谓词IN<值表>,NOTIN<值表>

<值表>：用逗号分隔的一组取值[例3.27]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(3)确定集合DatabaseSystemSummary§3.4数据查询[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词用!=或<>运算符取代NOTLIKE谓词(4)字符串匹配§3.4数据查询%(百分号)代表任意长度（长度可以为0）的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。§3.4数据查询1)匹配模板为固定字符串[例]查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE'95001'；等价于：SELECT*FROMStudentWHERESno='95001'；§3.4数据查询2)匹配模板为含通配符的字符串[例3.30]查询所有姓刘学生的姓名、学号和性别。

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

SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；§3.4数据查询3)使用换码字符将通配符转义为普通字符

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

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

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；§3.4数据查询

使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例3.36]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL；(5)涉及空值的查询§3.4数据查询用逻辑运算符AND和OR来联结多个查询条件

AND的优先级高于OR

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

[NOT]IN[NOT]BETWEEN…AND…(6)多重条件查询[例3.38]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；§3.4数据查询

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

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；查询结果SnoGrade--------------9501095024950079295003829501082950097595014619500265§3.4数据查询四、使用聚集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）§3.4数据查询求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短语：在计算时要取消指

定列中的重复值 ALL短语：不取消重复值 ALL为缺省值§3.4数据查询[例3.41]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

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

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

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

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

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

结果

CnoCOUNT(Sno) 122 234 344 433 548§3.4数据查询GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数GROUPBY子句的注意事项：§3.4数据查询[例]查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数

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

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

Having短语的注意事项：§3.4数据查询3.4.2连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>§3.4数据查询连接字段：连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。§3.4数据查询SQL中连接查询的主要类型：一、等值连接(含自然连接)与非等值连接查询二、自身连接查询三、外连接查询四、多表连接五、广义笛卡尔积§3.4数据查询一、等值连接(含自然连接)与非等值查询连接运算符为=的连接操作。[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。[例3.49]查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；§3.4数据查询假设Student表、SC表分别有下列数据：SnoSnameSsexSageSdept95001

李勇

男20CS95002

刘晨

女19IS95003

王敏

女18MA95004

张立

男19ISSC表：

SnoCnoGrade9500119295001285950019500295002323889080Student表：§3.4数据查询结果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

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

这张表有什么问题？§3.4数据查询自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。[例3.50]对[例3.49]用自然连接完成。

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

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

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

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；查询结果

173556

cnocpno

§3.4数据查询三、外连接查询外连接与普通连接的区别：普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出§3.4数据查询[例3.53]查询每个学生及其选修课程的情况包括没有选修课程的学生。

SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudent

LEFTOUTERJOINSC ON(Student.Sno=SC.Sno)；结果：

Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595002刘晨女19IS38095003王敏女18MANULLNULL95004张立男19ISNULLNULL§3.4数据查询四、多表连接两个以上的表进行连接，称为多表连接。

[例3.54]查询学生的学号、姓名、选修的课程及成绩。

SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno§3.4数据查询复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接

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

SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno='2'AND/*其他限定条件*/SC.Grade>90；/*其他限定条件*/§3.4数据查询五、广义笛卡尔积不带连接谓词的连接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC§3.4数据查询3.4.3嵌套查询嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或

HAVING短语的条件中的查询称为嵌套查询。SELECTSname 外层查询/父查询

FROMStudentWHERESnoIN

（SELECTSno内层查询/子查询

FROMSCWHERECno='2'）；§3.4数据查询子查询的限制

不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询§3.4数据查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY或ALL谓词的子查询四、带有EXISTS谓词的子查询嵌套查询的分类：§3.4数据查询一、带有IN谓词的子查询[例3.55]查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成①确定“刘晨”所在系名

SELECTSdeptFROMStudentWHERESname='刘晨'； 结果为：

Sdept IS§3.4数据查询②查找所有在IS系学习的学生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；结果为：SnoSnameSdept95001刘晨IS95004张立IS§3.4数据查询将第一步查询嵌入到第二步查询的条件中。

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。§3.4数据查询[例3.56]查询选修了课程名为“信息系统”的学生学号和姓名。

SELECTSno，Sname③最后在Student关系中

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

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

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

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

WHERECname=‘信息系统’));§3.4数据查询结果：

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

95002刘晨本例用连接查询实现：

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系统’；§3.4数据查询

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

例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例3.55]可以用=代替IN：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='刘晨'；§3.4数据查询

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

错误的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘刘晨’)=Sdept；§3.4数据查询三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值（类似逻辑算符

）ALL：所有值（类似逻辑算符

）§3.4数据查询[例]查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄。

SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';/*注意这是父查询块中的条件*/§3.4数据查询四、带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函§3.4数据查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS)3.4.4集合查询§3.4数据查询1．并操作形式

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

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；§3.4数据查询2．交操作

标准SQL中没有提供集合交操作，但可用其他方法间接实现。[例3.66]查询计算机科学系的学生与年龄不大于19岁的学生的交集。SELECT*FROMStudentWHERESdept=‘cs’INTERSECTSELECT*FROMStudentWHERESage<=19;§3.4数据查询3．差操作

标准SQL中没有提供集合差操作，但可用其他方法间接实现。[例3.68]查询计算机科学系的学生与年龄不大于19岁的学生的差集。SELECT*FROMStudentWHERESdept=‘cs’EXCEPTSELECT*FROMStudentWHERESage<=19;§3.4数据查询3.3.5基于派生表的查询

子查询出现在FROM子句中，子查询生成临时的派生表，作为主查询的查询对象，派生表必须指定一个别名。

例3.57查询每个学生超过自己选修课程平均成绩的课程。 SelectSno，Cno FromSC,(SelectSno,Avg(Grade)FromSCGroupbySno)AsAvg_sc(avg_sno,avg_grade)WhereSC.Sno=Avg_sc.avg_snoand SC.Grade>=Avg.sc.avg_grade§3.4数据查询3.3.6SELECT语句的一般格式SELECT[ALL|DISTINCT]<目标列表达式>[别名][，<目标列表达式>[别名]]…FROM<表名或视图名>[别名][，<表名或视图名>[别名]]…[WHERE<条件表达式>][GROUPBY<列名1>[，<列名1’>]...[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC][，<列名2>[ASC|DESC]]…]；§3.5数据更新3.5.1插入数据3.5.2修改数据3.5.3删除数据§3.5数据更新3.5.1插入数据两种插入数据方式：一、插入单个元组二、插入子查询结果§3.5数据更新一、插入单个元组语句格式INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)功能将新元组插入指定表中。§3.5数据更新子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值

VALUES子句提供的值必须与INTO子句匹配值的个数值的类型§3.5数据更新[例3.69]将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）

插入到Student表中。

INSERTINTOStudentVALUES(‘95020’,‘陈冬’,‘男’,‘IS’,

18)；§3.5数据更新二、插入子查询结果语句格式：

INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]

子查询；功能：将子查询结果插入指定表中§3.5数据更新INTO子句(与插入单条元组类似)指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值子查询SELECT子句目标列必须与INTO子句匹配值的个数值的类型§3.5数据更新[例3.72]对每一个系，求学生的平均年龄，并把

结果存入数据库。第一步：建表

CREATETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)；/*学生平均年龄*/

§3.5数据更新第二步：插入数据

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；§3.5数据更新3.5.2修改数据语句格式

UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能

修改指定表中满足WHERE子句条件的元组§3.5数据更新SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组§3.5数据更新三种修改方式一、修改某一个元组的值二、修改多个元组的值三、带子查询的修改语句§3.5数据更新一、修改某一个元组的值[例3.73]将学生95001的年龄改为22岁。

UPDATEStudentSETSage=22WHERESno='95001'；§3.5数据更新二、修改多个元组的值[例3.74]将所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1；§3.5数据更新[例3.75]将计算机科学系全体学生的成绩置零。

UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；三、带子查询的修改语句§3.5数据更新3.4.3删除数据

DELETEFROM<表名>[WHERE<条件>]；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组§3.5数据更新三种删除方式：

1.删除某一个元组的值

2.删除多个元组的值

3.带子查询的删除语句§3.5数据更新1.删除某一个元组的值[例]删除学号为95019的学生记录。

DELETEFROMStudentWHERESno='95019'；§3.5数据更新2.删除多个元组的值[例]删除2号课程的所有选课记录。

DELETEFROMSC；

WHERECno='2'；§3.5数据更新3.带子查询的删除语句[例3.78]删除计算机科学系所有学生的选课记录。

DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；§3.6空值的处理插入数据时未赋值InsertintoSC(Sno,Cno,Grade)values(‘98012’,’1’,NULL)明确定义空值 UpdateStudent SetSdept=NULL WhereSno=‘98012’外连接1.空值的产生§3.6空值的处理ISNULL或者ISNOTNULL2.空值的判断[例3.81]从Student表中找出漏填数据的学生信息。

Select*FromStudentWhereSnameISNULLORSsexISNULLORSageISNULLORSdeptISNULL§3.6空值的处理用户自定义完整性指定的非空约束NOTNULLUNIQUE约束

相应属性不能为空实体完整性约束主码不能为空3.空值的约束§3.6空值的处理NULL与其他值的算术结果为NULLNULL与其他值的比较结果为NULLNULL的逻辑运算：4.空值的运算NULL&true=⊥NULL|true=trueNULL&false=falseNULL|false=⊥NULL&NULL=⊥NULL|NULL=⊥NOTNULL=⊥§3.6空值的处理[例3.83]选出选修1号课程的不及格以及缺考的学生。SelectSnoFromSCWhereCno=1and(Grade<60ORGradeisnull);§3.7视图虚表，是从一个或几个基本表（或视图）导出的表。只存放视图的定义，不会出现数据冗余。基表中的数据发生变化，从视图中查询出的数据也随之改变。视图的特点：§3.7视图

查询删除更新定义基于该视图的新视图基于视图的操作：§3.7视图3.7.1定义视图3.7.2查询视图3.7.3更新视图3.7.4视图的作用§3.7视图3.7.1定义视图一、建立视图二、删除视图§3.7视图一、建立视图语句格式

CREATEVIEW<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。§3.7视图常见的视图形式★

行列子集视图★

WITHCHECKOPTION的视图★

基于多个基表的视图★

基于视图的视图★

带表达式的视图★

分组视图§3.7视图行列子集视图定义：

[例3.84]建立信息系学生的视图。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；从单个基本表导出只是去掉了基本表的某些行和某些列保留了主码§3.7视图WITHCHECKOPTION视图透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）。§3.7视图[例3.85]建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；§3.7视图对IS_Student视图的更新操作修改操作：DBMS自动加上Sdept='IS'的条件删除操作：DBMS自动加上Sdept='IS'的条件插入操作：DBMS自动检查Sdept属性值是否为'IS'如果不是，则拒绝该插入操作。如果没有提供Sdept属性值，则自动定义Sdept为‘IS’。§3.7视图基于多个基表的视图[例3.86]建立信息系选修了1号课程的学生视图。

CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；§3.7视图基于视图的视图

[例3.87]建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；§3.7视图带表达式的视图[例3.88]定义一个反映学生出生年份的视图。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent★设置一些派生属性列,也称为虚拟列--Sbirth。★带表达式的视图必须明确定义组成视图的各个属性列名。§3.7视图

分组视图[例3.89]将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型。

CREA