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

第三章关系数据库标准语言SQL§3.1SQL概述§3.2学生－课程数据库§3.3数据定义§3.4查询§3.5数据更新§3.6视图

SQL（StructuredQueryLanguage）语言来源于20世纪70年代IBM的一个被称为SEQUEL(StructuredEnglishQueryLanguage)的研究项目。20世纪80年代，SQL由ANSI进行了标准化，它包括了定义和操作数据的指令。由于它具有功能丰富、使用方式灵活、语言简洁易学等突出特点，在计算机界深受广大用户欢迎，许多数据库生产厂家都相继推出各自支持SQL标准。1998年4月，ISO提出了具有完整性特征的SQL，并将其定为国际标准，推荐它为标准关系数据库语言。1990年，我国也颁布了《信息处理系统数据库语言SQL》，将其定为中国国家标准。§3.1SQL概述§3.1SQL概述SQL的特点1.综合统一SQL集查询、DDL、DML、DCL(数据控制语言)为一体。用SQL语言可实现DB生命周期的全部活动。2.高度非过程化SQL语言是非过程化语言（第四代语言）。3.面向集合的操作方式4.以同一种语法结构提供两种使用方法(自含式语言，嵌入式语言)联机交互使用方式；嵌入某种高级程序设计语言。5.语言简捷，易学易用SQL支持关系数据库三级模式基本表：本身独立存在的表。一个关系就对应一个基本表。（模式）存储文件：一个（或多个）基本表对应一个存储文件，一个表可带若干索引。索引也存放在存储文件中。存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理结构是任意的，对用户是透明的。（内模式）视图：从一个或几个基本表导出的表。是一个虚表，数据库中仅存视图定义，不存放视图对应的数据。并且用户可以在视图上再定义视图。（外模式）用户外模式模式内模式Storefile1Storefile2Basetable1Basetable2Basetable3Basetable4View1View2SQLSQL对关系数据库模式的支持SQL命令的种类SQL语句可以在命令窗口中执行，也可以作为查询或视图(的内容)被使用，还可以在程序文件被执行SQL命令动词——————————————————————SQL功能命令动词——————————————————————数据查询SELECT数据定义CREATE、DROP、ALTER数据操作INSERT、UPDATE、DELETE数据控制GRANT、REVOKE——————————————————————§3.2学生－课程数据库CSCSMAIS20191819男女女男李勇刘晨王敏张立200215121200215122200215123200215125所在系Sdept年龄Sage性别Ssex姓名Sname学号SnoStudent424342451676数据库数学信息系统操作系统数据结构数据处理

PASCAL语言1234567学分Ccredit先行课Cpno课程名Cname课程号CnoCourse928588908012323200215121200215121200215121200215122200215122成绩Grade课程号Cno学号SnoSC§3.3数据定义DROPINDEXCREATEINDEX索引DROPVIEWCREATEVIEW视图ALTERTABLEDROPTABLECREATETABLE表DROPSCHEMACREATESCHEMA模式修改删除创建操作方式操作对象表3.2SQL的数据定义语句§3.2.1模式的定义与删除（1）定义模式CREATESCHEMA<模式名>AUTHORIZATION<用户名>如果没有指定<模式名>，那么<模式名>隐含为<用户名>要创建模式，调用该命令的应用必须拥有DBA权限，或者获得了DBA授予的创建模式的权限[例1]

定义一个学生－课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户WANG定义了一个模式S-T[例2]

CREATESCHEMAAUTHORIZATIONWANG;该语句没有指定<模式名>，所以<模式名>隐含为用户名WANG说明：定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，如基本表、视图、索引等

CREATESCHEMA<模式名>AUTHORIZATION<用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>][例3]

CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT,COL2INT,COL3CHAR(20),COL4NUMERIC(10,3),COL5DECIMAL(5,2));该语句为用户ZHANG创建了一个模式TEST，并且在其中定义了一个表TAB1(2)删除模式

DROPSCHEMA<模式名><CASCADE|RESTRICT>其中CASCADE和RESTRICT，两者必选其一CASCADE（级联），表示在删除模式的同时把该模式中所有的数据库对象全部一起删除RESTRICT（限制），表示如果该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行，只有当该模式中没有任何下属的对象时才能执行DROPSCHEMA语句例4：

DROPSCHEMATESTCASCADE;该语句删除了模式TEST。同时，该模式中已经定义的表TAB1也被删除了§3.3.2基本表的定义、删除与修改一、定义基本表CREATETABLE<表名>（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件[例5]

建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，且为主码，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；系统执行上面的语句后，在数据库中建立一个新的空的“学生”表Student，并将有关“学生”表的定义及有关约束条件存放在数据字典中常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束:FOREIGNKEY问题：PRIMARYKEY与UNIQUE的区别？[例6]

建立一个“课程”表Course。CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEYCpnoREFERENCESCourse(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/

);说明：参照表和被参照表可以是同一个表[例7]

建立学生选课表SC。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT),PRIMARYKEY(Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY（Sno）REFERENCESStudent(Sno)/*表级完整性约束条件，Sno是外码，被参照表是Student*/

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

);二、数据类型数据类型含义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.SS每一个基本表都属于某一个模式，一个模式包含多个基本表，定义模式的方法有三种：方法一：在表名中明显地给出模式名Createtable“S-T”.Student(……);Createtable“S-T”.Course(……);Createtable“S-T”.SC(……);方法二：在创建模式语句中同时创建表方法三：设置所属的模式，这样在创建表时表名中不必给出模式名三、模式与表当用户创建基本表时若没有指定模式，系统根据搜索路径来确定该对象所属的模式。搜索路径包含一组模式列表，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出模式SHOW

search_path搜索路径的当前默认值是：$user,PUBLIC其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式四、修改基本表

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROPCOLUMN<列名>][MODIFY<列名><数据类型>][DROP<完整性约束名>]；其中：ADD：增加新列和新列的完整性约束条件DROP：删除指定列MODIFY：修改原有列的数据类型DROP<完整性约束名>：删除原有的完整性约束[例8]

向Student表增加“入学时间”列，其数据类型为日期型ALTERTABLEStudentADDScomeDATE;注：不论基本表中原来是否已有数据，新增加的列一律为空值[例9]

将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYCOLUMNSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据[例10]

删除学生姓名必须取唯一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；五、删除基本表

DROPTABLE<表名>[RESTRICT|CASCADE];

基本表删除，表中的数据、表上的索引都删除表上的视图往往仍然保留，但无法引用删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述RESTRICT：表的删除是有限制条件CASCADE：表的删除没有限制条件缺省情况是RESTRICT§3.3.3建立与删除索引建立索引是加快查询速度的有效手段建立索引DBA或表的属主（即建立表的人）根据需要建立有些DBMS自动建立以下列上的索引

PRIMARYKEYUNIQUE维护索引

DBMS自动完成使用索引

DBMS自动选择是否使用索引以及使用哪些索引索引一经建立，就由系统使用和维护它，不需用户干预一、建立索引语句格式CREATE

[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引指索引项的顺序与表中记录的物理顺序一致的索引组织[例14]

为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束聚簇索引建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致一、建立索引例：在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放CREATECLUSTERINDEXStusnameONStudent(Sname)；说明：在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作建立索引原则记录有一定规模某列在where子句中频繁使用先装数据，后建索引二、删除索引语句格式

DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述[例15]删除Student表的Stusname索引DROPINDEXStusname；§3.4数据查询语句格式：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；其中：SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件

GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序

§3.4.1单表查询查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列二、选择表中的若干元组三、对查询结果排序四、聚集函数五、对查询结果分组一、选择表中的若干列1.查询指定列[例1]

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

[例2]

查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；2.查询全部列[例3]

查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；3.查询经过计算的值

SELECT子句的<目标列表达式>:表中的属性列表达式算术表达式、字符串常量、函数、列别名等[例4]

查全体学生的姓名及其出生年份SELECTSname，2006-SageFROMStudent；

输出结果：Sname2006-Sage----------------------李勇1976刘晨1977王名1978张立1978算术表达式[例5]

查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,’YearofBirth:’,2008-Sage,LOWER(Sdept)FROMStudent；函数字符串常量输出结果：Sname'YearofBirth:'2008-SageLOWER(Sdept)----------------------------------------------------

李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is[例5.1]使用列别名改变查询结果的列标题SELECTSnameNAME，'YearofBirth:’

BIRTH，2008-Sage

BIRTHDAY，LOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs刘晨YearofBirth:1977is王名YearofBirth:1978ma张立YearofBirth:1977is二、选择表中的若干元组消除取值重复的行查询满足条件的元组1.消除取值重复的行在SELECT子句中使用DISTINCT短语假设SC表中有下列数据

SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL与DISTINCT的使用[例6]

查询选修了课程的学生学号。(1)SELECTSnoFROMSC; 或(默认ALL)SELECTALLSnoFROMSC;

结果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;

结果：Sno-------9500195002注:

DISTINCT短语的作用范围是所有目标列例：查询选修课程号，及成绩错误：SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确：SELECTDISTINCTCno，GradeFROMSC;

2.查询满足条件的元组WHERE子句常用的查询条件(1)比较大小在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=(或<>)，!>，!<逻辑运算符NOT+比较运算符[例8]

查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；(2)确定范围使用谓词：BETWEEN…AND…NOTBETWEEN…AND…[例10]

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

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

(3)确定集合使用谓词IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('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'<换码字符>'短语对通配符进行转义。1)匹配模板为固定字符串[例14]

查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE'95001'；等价于： SELECT*FROMStudentWHERESno=‘95001’；2)匹配模板为含通配符的字符串[例15]

查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；[例17]

查询名字中第2个字为"阳"字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE‘__阳%’；[例18]查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；3)使用换码字符将通配符转义为普通字符

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

SELECTCno，CcreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE‘\’;[例20]

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

SELECT*FROMCourseWHERECnameLIKE‘DB\_%i__’ESCAPE‘\’;(5)涉及空值的查询使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]

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

查所有有成绩的学生学号和课程号。

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

AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词[NOT]IN[NOT]BETWEEN…AND…[例23]

查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改写[例12][例12]

查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(‘IS’，’MA’，’CS’)可改写为：

SELECTSname，SsexFROMStudentWHERESdept=‘IS‘ORSdept=‘MA’ORSdept=‘CS‘；改写[例10][例10]

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

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改写为：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；三、对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示[例24]

查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；[例25]

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT*FROMStudentORDERBYSdept(ASC),SageDESC;

结果：SnoGrade--------------9501095024950079295003829501082950097595014619500255四、使用集函数5类主要集函数计数COUNT（[DISTINCT|ALL]*）统计元组个数COUNT（[DISTINCT|ALL]<列名>）统计一列中值的个数计算总和（列必须是数值型）SUM（[DISTINCT|ALL]<列名>）统计一列值的总和 计算平均值（列必须是数值型）AVG（[DISTINCT|ALL]<列名>）统计一列值的平均值求最大值MAX（[DISTINCT|ALL]<列名>）求一列中最大值求最小值MIN（[DISTINCT|ALL]<列名>）求一列中最小值其中：DISTINCT短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值除COUNT（＊）外，其它函数均跳过空值而只处理非空值四、使用集函数[例26]

查询学生总人数。SELECTCOUNT(*)FROMStudent；

[例27]查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数[例28]计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]

查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno='1'；五、对查询结果分组使用GROUPBY子句分组 细化聚集函数的作用对象若未对查询结果分组，聚集函数将作用于整个查询结果若对查询结果分组后，聚集函数将分别作用于每个组

[例30]

求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

结果CnoCOUNT(Sno) 122 234 344 433 548GROUPBY子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数如果分组后仍要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件只有满足HAVING短语指定条件的组才输出

五、对查询结果分组

使用HAVING短语筛选最终输出结果[例31]

查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；[例32]

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

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

使用HAVING短语筛选最终输出结果HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。WHERE子句中的谓词在形成分组前起作用HAVING短语作用于组，从中选择满足条件的元组。HAVING子句中的谓词在形成分组后才起作用§3.4.2连接查询

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

比较运算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的广义笛卡尔积等值连接(含自然连接)非等值连接查询自身连接查询外连接查询复合条件连接查询SQL中连接查询的主要类型一、广义笛卡尔积不带连接谓词的连接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC二、等值与非等值连接查询等值连接、自然连接非等值连接等值连接连接运算符为“=”的连接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。

[例32]

查询每个学生及其选修课程的情况。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；/*将Student与SC中同一学生的元组连接起来*/Student表SC表Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380等值连接后查询结果为：SnoSnameSsexSageSdept95001李勇男20CS95001李勇男20CS95001李勇男20CS95002刘晨女19IS95002刘晨女19IS95003王敏女18MA95004张立男19ISSnoCnoGrade9500119295001285950013889500229095002380自然连接是等值连接的一种特殊情况，把目标列中重复的属性列去掉[例33]

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

SELECT

Student.Sno，Sname，Ssex，Sage,Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095002刘晨女19IS380非等值连接查询连接运算符不是=

的连接操作[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>比较运算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>三、自身连接一个表与其自己进行连接，称为表的自身连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀[例34]

查询每一门课的间接先修课（即先修课的先修课）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；

FIRST表（Course表）SECOND表（Course表）自身连接结果为：cnocpnocnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64cnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言6417354567四、外连接（OuterJoin）外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出外连接分类：左外连接LEFTOUTJOIN:外连接符出现在连接条件的左边右外连接RIGHTOUTJOIN:外连接符出现在连接条件的右边全外连接FULLOUTJOIN[例35]

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

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);/*也可以作用USING来去掉结果中的重复值：FROMStudentLEFTOUTJOINSCUSING(Sno);*/Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002刘晨女19IS29095002刘晨女19IS38095003王敏女18MANULLNULL95004男张立19ISNULLNULL五、复合条件连接WHERE子句中含多个连接条件时，称为复合条件连接[例37]查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.Sno

AND

/*连接谓词*/

SC.Cno=‘2’AND

SC.Grade>90；

/*逻辑运算符其他限定条件*/多表连接[例38]

查询每个学生的学号、姓名、选修的课程名及成绩。

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

结果： Student.SnoSnameCnameGrade95001李勇数据库9295001李勇数学8595001李勇信息系统8895002刘晨数学9095002刘晨信息系统80§3.4.3嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

SELECTSname

外层查询/父查询

FROMStudentWHERESno

IN

（SELECTSno

内层查询/子查询

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

查询与“刘晨”在同一个系学习的学生。(此查询要求可以分步来完成，然后再构造嵌套查询)①确定“刘晨”所在系名

SELECTSdeptFROMStudentWHERESname='刘晨'； 结果为： Sdept IS②查找所有在IS系学习的学生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；结果为：SnoSnameSdept95001刘晨IS95004张立IS构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘刘晨’)；判断此查询是相关还是不相关子查询？是不相关子查询并且DBMS求解该查询时也是分步去做的此查询也可以用自身连接完成SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='刘晨'；父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘刘晨’)；接上[例38]查询选修了课程名为“信息系统”的学生学号和姓名。

SELECTSno，Sname③最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno①首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=‘信息系统’));课程名课程号学生号学生姓名结果：SnoSname---------95001李勇95002刘晨courseSCStudent用连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系统’;接上二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用例：[例37]可以用=代替IN：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname='刘晨‘)；二、带有比较运算符的子查询

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

错误的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘刘晨’)=Sdept；三、带有ANY或ALL谓词的子查询谓词语义ANY：任意一个值ALL：所有值需要配合使用比较运算符>ANY 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值<ANY 小于子查询结果中的某个值<ALL 小于子查询结果中的所有值>=ANY 大于等于子查询结果中的某个值>=ALL 大于等于子查询结果中的所有值<=ANY 小于等于子查询结果中的某个值<=ALL 小于等于子查询结果中的所有值=ANY 等于子查询结果中的某个值=ALL 等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY 不等于子查询结果中的某个值!=（或<>）ALL 不等于子查询结果中的任何一个值[例39]

查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

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

ANDSdept<>'IS';

/*注意这是父查询块中的条件*/DBMS执行过程：1.首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18)2.接着处理父查询，找所有不是IS系且年龄小于19或18的学生ANY和ALL谓词有时可以用聚集函数实现ANY与ALL与集函数的对应关系用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数

=<>或!=

<<=>>=ANYIN--

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

<MIN<=MIN>MAX>=MAX[例39‘]：用集函数实现[例39]：查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;[例40]

查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词

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

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

[例41]

查询所有选修了1号课程的学生姓名。思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送入结果关系。方法一：用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoANDCno='1')；方法二：用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND SC.Cno='1';

[例42]查询没有选修1号课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；此例用连接运算难于实现。3.不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。四、带有EXISTS谓词的子查询例：[例37]查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

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

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

pq≡

p∨q

四、带有EXISTS谓词的子查询[例43]

查询选修了全部课程的学生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno))；不存在没有选修课程的学生不存在没有学生选修的课程任意课程,所求的学生都选了不存在任何一门课程,所求的学生没选[例44]

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

用P表示谓词“学生95002选修了课程y” 用q表示谓词“学生x选修了课程y” 则上述查询为:(y)pq等价变换： (y)pq≡(y((pq))≡(y((p∨q)≡

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

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；任意课程,95002学生选了,所求的学生都选了不存在任何一门课程,99002学生选了,所求的学生没选§3.4.4集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)1．并操作形式 <查询块> UNION <查询块>注：参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同[例45]

查询计算机科学系的学生或年龄不大于19岁的学生。方法一：

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

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；注：使用UNION将多个查询结果合并起来，系统会自动去掉重复元组[例46]

查询选修了课程1或者选修了课程2的学生。方法一：SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[例47]

设数据库中有一教师表Teacher(Tno,name,...)。查询学校中所有师生的姓名。

SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;2．交操作[例48]

查询计算机科学系的学