D-3关系数据库标准语言SQL

1第三讲关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制2【学习目标】

本讲的内容是关系数据库的标准语言SQL(StructuredQueryLanguage)--结构化查询语言。本讲的学习目标主要是熟练掌握SQL的语法，能够在实践中熟练地运用SQL实现各种查询请求。【学习方法】

要求大家将课程中讲到的例子在MicrosoftSQLServer中进行上机练习，看一看SQL与Transaction-SQL(简称T-SQL)到底有什么不同。3第三讲关系数据库标准语言SQL3．1SQL概述

SQL语言集数据查询(dataquery)、数据操纵(datamanipulation)、数据定义(datadefinition)和数据控制(datacontrol)功能于一体，充分体现了关系数据语言的特点和优点：1．综合统一2．高度非过程化3．面向集合的操作方式（元组集合）4．以同一种语法结构提供两种使用方式（自含式，嵌入式）5．语言简洁，易学易用4SQL的基本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL支持关系数据库三级模式结构3.2数据定义

SQL的数据定义功能:模式定义、表定义、视图和索引的定义

3.2.1基本表的定义、删除与修改一、定义基本表CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。8定义基本表常用完整性约束主码约束：PRIMARYKEY唯一性约束：UNIQUE非空值约束：NOTNULL参照完整性约束:

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

CREATETABLEStudent (SnoCHAR(9)PRIMARYKEY，/*列级完整性约束条件*/SnameCHAR(20)UNIQUE，/*Sname取唯一值*/SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20))；

主码课程表Course[例2]建立一个“课程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，

CnameCHAR(40)，

CpnoCHAR(4)，

CcreditSMALLINT，

FOREIGNKEY(Cpno)REFERENCESCourse(Cno));先修课Cpno是外码被参照表是Course被参照列是Cno学生选课表SC[例3]建立一个“学生选课”表SC CREATETABLESC (SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

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

/*表级完整性约束条件，Sno是外码，被参照表是Student*/ FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/ );二、数据类型SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度选用哪种数据类型取值范围要做哪些运算二、数据类型数据类型含义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二、修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][ALTERCOLUMN<列名><数据类型>]；15二．修改基本表(续)例4向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDScomeDATE注意：修改原有的列定义有可能会破坏已有数据。三、删除基本表

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

DROPTABLEStudent注意：基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除。3.2.2索引的建立与删除建立索引的目的：加快查询速度谁可以建立索引DBA或表的属主（即建立表的人）DBMS一般会自动建立以下列上的索引

PRIMARYKEYUNIQUE谁维护索引

DBMS自动完成

使用索引

DBMS自动选择是否使用索引以及使用哪些索引索引RDBMS中索引一般采用B+树、HASH索引来实现B+树索引具有动态平衡的优点HASH索引具有查找速度快的特点采用B+树，还是HASH索引则由具体的RDBMS来决定索引是关系数据库的内部实现技术，属于内模式的范畴CREATEINDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引一、建立索引语句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 建立索引（续）[例6]CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引在最经常查询的列上建立聚簇索引以提高查询效率一个基本表上最多只能建立一个聚簇索引建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大。经常更新的列不宜建立聚簇索引

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

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

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

SC表按学号升序和课程号降序建唯一索引二、删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例8]删除Student表的Stusname索引

DROPINDEXStusname；243．3查询以后均以下列三个表为操作对象：Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno,Cname,Cpno,Credit)SC(Sno,Cno,Grade)数据查询格式

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

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

261．一般查询例9查询所有选修过课的学生的学号。

SELECTSno

FROMSC等价于

SELECTAllSno

FROMSC要去掉重复行，应写：

SELECTDISTINCTSno

FROMSC272．条件查询例10查信息系(IS)、数学系(MA)和计算机科学系(CS)的学生的姓名和性别。

SELECTSname，Ssex

FROMStudent WHERESdeptIN('IS'，'MA'，'CS')例11查询学号为95001的学生的详细情况。

SELECT*

FROMStudent

WHERESnoLIKE’95001’等价于：

SELECT*

FROMStudent

WHERESno='95001'28SQL提供的集函数：COUNT([DISTINCT|ALL]*) 统计元组个数COUNT([DISTINCT|ALL]＜列名＞) 统计一列中值的个数SUM([DISTINCT|ALL]＜列名＞) 计算一列值的总和(此列必须是数值型)AVG([DISTINCT|ALL＜列名＞) 计算一列值的平均值(此列必须是数值型)MAX([DISTINCT|ALL]＜列名＞)求一列中最大值MIN([DISTINCT|ALL]＜列名＞)求一列中最小值29例12查询各个课程号与相应的选课人数。

SELECTCno，COUNT(Sno)

FROMSCGROUPBYCnoHAVING短语作用于组，从中选择满足条件的组。例13查询选修了3门以上课程的学生的学号。

SELECTSnoFROMSC

GROUPBYSno HAVINGCOUNT(*)＞330３．连接查询例14查询每个学生及其选修课程的情况。

SELECTStudent.*，SC.*

FROMStudent，SCWHEREStudent.Sno＝SC.Sno（列Sno重复）例15自然连接Student和SC表。

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

FROMStudent，SC

WHEREStudent.Sno＝SC.Sno（列Sno不重复）31４．带有ANY或ALL谓词的子查询＞ANY 大于子查询结果中的某个值＜ANY 小于子查询结果中的某个值＞＝ANY 大于等于子查询结果中的某个值＜＝ANY 小于等于子查询结果中的某个值＝ANY 等于子查询结果中的某个值!＝ANY或＜＞ANY 不等于子查询结果中的某个值＞ALL 大于子查询结果中的所有值＜ALL 小于子查询结果中的所有值＞＝ALL 大于等于子查询结果中的所有值＜＝ALL 小于等于子查询结果中的所有值＝ALL 等于子查询结果中的所有值(通常没有实际意义)!=ALL或＜＞ALL不等于子查询结果中的任何一个值3216335．集合查询例17查询选修了课程1或者选修了课程2的学生。(并操作)SELECTSno

FROMSC

WHERECno='1'

UNION

SELECTSno

FROMSC

WHERECno='2’例18查询计算机科学系中年龄不大于19岁的学生。

SELECT*

FROMStudentWHERESdept=‘CS’ANDSage<=19

（交操作的等价操作）343．4数据更新1.插入单个元组语句格式INSERTINTO<表名>[(<属性列1>[，<属性列2>…)]VALUES(<常量1>[，<常量2>]…)功能将新元组插入指定表中。2.插入子查询结果语句格式

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

子查询；功能将子查询结果插入指定表中37插入数据例19对每一个系，求学生的平均年龄，并把结果存入数据库。CREATETABLEDeptage(SdepCHAR(15),AvgageSMALLINT)INSERT INTODeptage(Sdep,Avgage) SELECTSdept,AVG(Sage) FROMStudent GROUPBYSdept3修改数据语句格式

UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能修改指定表中满足WHERE子句条件的元组39

例20将计算机科学系全体学生的成绩置零。

UPDATESCSETGrade=0 WHERE'CS‘= (SELECTSdeptFROMStudent WHEREStudent.Sno=SC.Sno)修改数据40注意：基本表的修改操作与数据库的一致性由DBMS保证。例21将95007号学生的学号修改为96089。

UPDATEStudent

SETSno='96089' WHERESno='95007‘实际执行了两条语句，第二条为

UPDATESC

SETSno='96089’

WHERESno='95007'4删除数据DELETEFROM<表名>[WHERE<条件>]；功能删除指定表中满足WHERE子句条件的元组WHERE子句指定要删除的元组缺省表示要修改表中的所有元组42例22删除计算机科学系所有学生的选课记录。

DELETEFROMSCWHERE'CS'= (SELECTSdept FROMStudent WHEREStudent.Sno=SC.Sno)

删除数据433．5视图3．5．1定义视图1．建立视图

CREATEVIEW＜视图名＞[(＜列名＞[，＜列名＞]…)] AS＜子查询＞

[WITHCHECKOPTION] “WITHCHECKOPTION”表示对视图进行UPDATE，INSERT 和DELETE操作时要保证更新、插入或删除的行满足视图定义时的谓词条件（即子查询中的条件表达式）。44三种情况下必须明确指定组成视图的所有列名：其中某个目标列不是单纯的属性名，而是集函数或列表达式。多表连接时选出了几个同名列作为视图的字段。需要在视图中为某个列启用新的更合适的名字。例23建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生。

CREATEVIEWIS_StudentAS SELECTSno，Sname，Sage FROMStudent WHERESdept='IS'

WITHCHECKOPTION45例24将学生的学号及其平均成绩定义为一个视图。

CREATEVIEWS_G(Sno，Gavg)

AS

SELECTSno，AVG(Grade)

FROMSC

GROUPBYSno2．删除视图

DROPVIEW＜视图名＞例25删除视图IS_S1DROPVIEWIS_S1463．5．2查询视图例26在信息系学生的视图中找出年龄小于20岁的学生。

SELECTSno，SageFROMIS_StudentWHERESage＜20DBMS转换后的查询语句为：

SELECTSno，SageFROMStudentWHERESdept＝'IS'ANDSage<2047例27查询信息系选修了1号课程的学生。（基本表和虚表的连接）

SELECTSno，Sname

FROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1’483．5．3更新视图例28将信息系学生视图IS_Student中学号为95002的学生姓名改为“刘辰”。

CREATEVIEWIS_Student AS

SELECTSno，Sname，Sage

FROMStudent WHERESdept='IS’

WITHCHECKOPTIONUPDATEIS_Student

SETSname=‘刘辰’

WHERESno='95002'49DBMS转换后的更新语句为

UPDATEStudent

SETSname='刘辰'WHERESno='95002'ANDSdept='IS';在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。50并不是所有的视图都可以更新：

如果想把视图S_G中学号为95001的学生的平均成绩改成90分，SQL语句如下：

UPDATES_G

SETGavg=90

WHERESno='95001‘

这个对视图的更新是无法转换成对基本表SC的更新的，因为系统无法修改各科成绩，以使平均成绩成为90。所以S_G视图是不可更新的。513．5．4视图的用途1．视图能够简化用户的操作2．视图使用户能以多种角度看待同一数据3．视图对重构数据库提供了一定程度的逻辑独立性4．视图能够对机密数据提供安全保护523．5．5使用视图的限制

●在一个基本表上建立的视图，只有包含基本表的主键才可以更新；

●一个视图最多只能有250个列；

●不能在视图上建立触发器和索引；

●对视图的一个更新语句只能影响一个基本表，所以由多表连接定义的视图不允许更新。

●定义视图语句不能使用UNION操作符。

●视图定义中用到GROUPBY子句或包含集合函数、计算列的数据不能修改。

●注意：不同的系统对视图的更新有不同的限制，使用时要参照具体的DBMS的说明。533．6数据控制

数据控制也称为数据保护，包括数据的安全性控制、完整性控制、并发控制和恢复。541．授权GRANT＜权限＞[，＜权限＞]…[ON＜对象类型＞＜对象名＞]TO＜用户＞[，＜用户＞]…[WITHGRANTOPTION]//传播权限55例29把对Student表和Course表的全部权限授予用户U2和U3。

GRANTALLPRIVILEGESONTABLEStudent，CourseTOU2，U3例30把对表SC的查询权限授予所有用户。

GRANTSELECTONTABLESCTOPUBLIC56

2．收回权限REVOKE＜权限＞[，＜权限＞]…[ON＜对象类型＞＜对象名＞] FROM＜用户＞[，＜用户＞]…例31把用户U4修改学生学号的权限收回。

REVOKEUPDATE(Sno)ONTABLEStudentFROMU4习题1.假定三个关系组成用户子模式。A(A#ANAMEWQTYCITY)B(B#BNAMEPMCE)AB(A#B#QTY)各个属性的含义如下：A#(商店代号)ANAME(商店名)、WQTY(店员人数)CITY(所在城市)、B#(商品号)BNAME(商品名称)、PRICE(价格)QTY(商品数量)。试用SQL语言写出下列查询。

(1)找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。

(2)找出供应书包的商店名。

(3)找出至少供应商店代号为256的商店所供应的全部商品的商店名和所在城市。2.设有图书登记表TS，具有属性：BNO(图书编号)，BC(图书类别)，BNA(书名)，AU(著者)，PUB(出版社)。按下列要求用SQL语言进行设计：

(1)按图书馆编号BNO建立TS表的索引ITS。

(2)查询按出版社统计其出版图书总数。

(3)删除索引ITS。3.设有学生表S(SNO，SN)(SNO为学生号，SN为姓名)学生选修课程表SC(SNO，CNO，CN，G)(CNO为课程号，CN为课程名，G为成绩)，试用SQL语言完成以下各题：

(1)建立一个视图v—SSC(SNO，SN，CNO，CN，G)，并按CNO升序排序；

(2)从视图v-SSC上查询平均成绩在90分以上的SN、CN和G。“第三讲关系数据库标准语言SQL”阅读提示示例数据库——BankingEnterpriseBranch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)Account(branch-name,account-number,balance)Depositor(customer-name,account-number)Loan(branch-name,loan-number,amount)Borrower(customer-name,loan-number)Select子句(SelectClause)选择子句对应于关系代数中的投影运算，用于列出出现在结果关系中的属性名。“找出关系loan中所有分支机构的名称”

selectbranch-name fromloan与上面查询对应的关系代数语义：在选择子句中出现的“*”(asterisk)表示全部属性(allattributes) select* fromloanSQL允许在关系中出现重复(duplicate)元组出于性能原因容许保留重复元组SQL运算基于多重集(multi-sets)，而非集合(sets)一些运算对重复元组敏感(e.g.,count,average,etc.)在select后插入distinct来删除重复元组“找出关系loan中所有不同的分支机构的名称”

selectdistinctbranch-name fromloan用all来说明不删除重复的元组

selectallbranch-name fromloan在选择子句中可以包括算术表达式在元组的属性和常量上进行+,-,*,/运算

selectbranch-name,loan-number,amount*100 fromloan结果关系模式与loan相同，但属性amount的值乘以100Where子句(WhereClause)对应于关系代数中的选择谓词(selectionpredicate)谓词由涉及from子句中关系的属性组成“找出所有在Perryridge分支机构贷款且贷款额超过$1200的贷款号”

selectloan-number fromloan wherebranch-name=“Perryridge”andamount>1200SQL应用逻辑连接词and,or,和not在比较运算的操作数中允许使用算术表达式between比较运算简化在where子句中出现的比较运算，如：avalue

somevalue并且someothervalue“找出贷款额在$90,000~$100,000(即：

$90,000并且

$100,000)之间的贷款号”

selectloan-number fromloan whereamountbetween90000and100000等价如下查询：

selectloan-number fromloan whereamount>=90000andamount<=100000From子句(FromClause)对应于关系代数中的笛卡儿乘积(Cartesianproduct)运算From子句列出在查询求值中需要扫描的关系“计算笛卡儿乘积borrowerloan” select* fromborrower,loan“找出在Perryridge银行中有贷款的客户姓名和贷款号”

selectdistinctcustomer-name,borrower.loan-number fromborrower,loan whereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”更名运算(RenameOperation)需要更名运算的理由在结果关系中可能出现具有相同名称的属性结果关系的属性中可能没有名字希望在结果关系中更改属性的名称一个关系可能以不同的名称参与查询关系和属性通过as子句更名

old-nameasnew-name“找出在Perryridge银行中有贷款的客户姓名和贷款号，将贷款号loan-number更名为loan-id” selectdistinctcustomer-name,borrower,loan-numberasloan-id fromborrower,loan whereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”元组变量(TupleVariables)元组变量在from子句中用as定义一个元组变量必须与一个特定的关系相关联，大部分用于在同一个关系中比较两个元组“找出在银行中有贷款的客户姓名和贷款号”

selectdistinctcustomer-name,T.loan-number fromborrowerasT,loanasS whereT.loan-number=S.loan-number“找出资产至少比位于Brooklyn的某一家分支机构多的分支机构”

selectdistinctT,branch-name frombranchasT,branchasS whereT.assets>S.assetsandS.branch-city=“Brooklyn”字符串操作(StringOperations)字符串匹配(String-matching)是在字符串上的比较运算。用两个特殊的字符来描述模式百分号(%)：%匹配任意子串(substring)下划线(_)：_匹配任意字符(character)“找出在街道地址中包含子串‘Main’的所有顾客名”

selectcustomer-name fromcustomer wherecustomer-streetlike“%Main%”转义符(Escapecharacter)\(backslash)匹配“Main%”：like“Main\%”匹配“ab\cd”：like“ab\\cd”任意只有一个字符的字符串?任意至少有一个字符的字符串?排序显示的元组“按字母序列出有贷款的客户名”

selectdistinctcustomer-name fromborrower,loan whereborrower.loan-number=loan.loan-number orderbycustomer-name用desc说明降序，asc说明升序(default) select* fromloan orderbyamountdesc,loan-numberascorderby请求完成排序(sort)对大数量的元组排序代价高仅在需要时才进行排序重复元组(Duplicates)在关系中保留重复的元组是有用的SQL形式化地定义在结果关系中有什么元组和多少个副本元组出现SQL查询中的重复元组的语义可以用多重集(multi-set)的关系代数来定义

重复元组(Cont.)假定关系r(A,B)和s(C)是如下的多重集：r={(1,a),(2,a)},s={(2),(3),(3)}

SQLduplicate语义上面查询等价于多重集的关系代数表达式：集合运算(SetOperations)集合运算union,intersect,和except在关系上进行操作对应于关系代数的,,

和–运算自动删除重复元组多重集版本的unionall,intersectall和exceptall保留重复的元组假设一个元组在r中出现m次，在s中出现n次在runionalls中出现m+n次在rintersectalls中出现min(m,n)次在rexceptalls中出现max(0,m-n)次“找出在银行有贷款、帐户、或两者都有的客户”(selectcustomer-namefromdepositor)union(selectcustomer-namefromborrower)“找出在银行同时有帐户和贷款的客户”(selectcustomer-namefromdepositor)intersect(selectcustomer-namefromborrower)“找出在银行有帐户而无贷款的客户”(selectcustomer-namefromdepositor)except(selectcustomer-namefromborrower)集合运算(cont.)聚集函数(AggregateFunctions)聚集函数以多重集(multiset)为输入，返回单个值

avg：平均值(averagevalue)min：最小值(minimumvalue)max：最大值(maximumvalue)sum：总和(sumofvalues)count：计数(numberofvalues)聚集函数(Cont.)“找出在Perryridge分支机构的帐户余额的平均值”selectavg(balance)fromaccountwherebranch-name=“Perryridge”“计算在customer关系中元组的个数”selectcount(*)fromcustomer“计算在银行中有存款的人数”selectcount(distinctcustomer-name)fromdepositor聚集函数—GroupBy列表“找出在每个分支机构的存款人数”selectbranch-name,count(distinctcustomer-name)fromdepositor,accountwheredepositor.account-number=account.account-numbergroupbybranch-name注意：在select子句中，聚集函数外的属性必须出现在groupby列表中。聚集函数—Having子句“找出平均余额大于$1,200的分支机构和它们的平均余额”selectbranch-name,avg(balance)fromaccountgroupbybranch-namehavingavg(balance)>1200注意：having子句中的谓词在形成分组后才求值空值(NullValues)元组的属性有空值，由null表示。意义：任何涉及null的算术表达式，其结果是null所有涉及null的比较运算返回false更准确地：任何与null的比较运算返回unknown(SQL-92语义)(trueorunknown)=true(falseorunknown)=unknown(unknownorunknown)=unknown,(trueandunknown)=unknown(falseandunknown)=false(unknownandunknown)=unknown如果谓词求值为unknown，则where子句对其处理为false如果谓词P求值为unknown，则“Pisunknown”为true“找出在关系loan中amount为空值的贷款号”selectloan-numberfromloanwhereamountisnull“计算总的贷款值”selectsum(amount)fromloan以上查询忽略空值。如果没有非空值的amount，则结果是null聚集函数除count(*)外，都忽略null值。嵌套子查询(NestedSubqueries)SQL提供了一套嵌套子查询的机制，子查询是一个嵌套在另一个查询中的Select-from-where表达式。子查询的一般用途是用于完成如下测试：集合成员资格(setmembership)集合的比较(setcomparison)集合的基数(setcardinality)集合成员资格(SetMembership)集合成员资格—Example(1)“找出在银行中同时有帐户和贷款的客户”selectdistinctcustomer-namefromborrowerwherecustomer-namein(selectcustomer-name fromdepositor)“找出在银行中有贷款而无帐户的客户”selectdistinctcustomer-namefromborrowerwherecustomer-namenotin(selectcustomer-namefromdepositor)“找出在Perryridge分支机构既有帐户又有贷款的客户”selectdistinctcustomer-namefromborrower,loanwhereborrower.loan-number=loan.loan-numberand branch-name=“Perryridge”and (branch-name,customer-name)in (selectbranch-name,customer-name fromdepositor,account wheredepositor.account-number=account.account-number)集合成员资格—Example(2)Some子句(SomeClause)集合比较(SetComparison)—Example“找出总资产至少比位于Brooklyn的某一家分支机构多的分支机构名称”selectdistinctT.branch-namefrombranchasT,branchasSwhereT.assets>S.assetsand S.branch-city=“Brooklyn”selectbranch-namefrombranchwhereassets>some(selectassets frombranch wherebranch-city=“Brooklyn”）All子句(AllClause)集合比较(SetComparison)—Example“找出总资产比位于Brooklyn的任意一家分支机构都多的分支机构名称”selectbranch-namefrombranchwhereassets>all(selectassets frombranch wherebranch-city=“Brooklyn”）测试是否为空关系Exists结构在作为参数的子查询非空时返回true

“找出在Brooklyn所有分支机构都有帐户的客户”selectdistinctS.customer-namefromdepositorasSwherenotexists((selectbranch-name frombranch wherebranch-city=“Brooklyn”) except (selectR.branch-name fromdepositorasT,accountasR whereT.account-number=R.account-numberandS.customer-name=T.customer-name))测试是否存在重复元组

unique结构测试是否在作为参数的子查询中存在任何重复元组“找出在Perryridge分支机构中只有一个帐户的客户”selectT.customer-namefromdepositorasTwhereunique( selectR.customer-name fromaccount,depositorasR whereT.customer-name=R.customer-nameand R.account-number=account.account-numberand account.branch-name=“Perryridge”派生关系(DerivedRelations)“找出平均帐户结算大于$1200的分支机构和平均结算”selectbranch-name,avg-balancefrom(selectbranch-name,avg(balance) fromaccount groupbybranch-name) asresult(branch-name,avg-balance)whereavg-balance>1200视图(Views)用视图来对特定的用户隐藏特定的数据createviewvas<queryexpression>

其中：<queryexpression>是任意的SQL查询表达式视图名为v视图—Example包含各分支机构名称以及在分支机构有帐户或贷款的客户名字createviewall-customeras(selectbranch-name,customer-namefromdepositor,accountwheredepositor.account-number=account.account-number)union(selectbranch-name,customer-namefromborrower,loanwhereborrower.loan-number=loan.loan-number)“找出在Perryridge分支机构的全部客户”selectcustomer-namefromall-customerwherebranch-name=“Perryridge”修改数据库—删除(Deletion)“删除在Perryridge分支机构的所有帐户”deletefromaccountwherebranch-name=“Perryridge”“删除位于Needham的没一个分支机构的所有帐户”deletefromaccountwherebranch-namein(selectbranch-name frombranch wherebranch-city=“Needham”)deletefromdepositorwhereaccount-numberin(selectaccount-number frombranch,account wherebranch-city=“Needham” andbranch.branch-name=account.branch-name)修改数据库—删除(Deletion)(Cont..)“删除余额低于银行平均余额的帐户”deletefromaccountwherebalance<(selectavg(balance) fromaccount)问题：当删除account中的元组时，平均余额随之发生变化SQL的解决方法：首先计算平均余额，找出需要删除的元组然后，删除上一步找出的元组(不重新计算avg函数和测试要删除的元组)修改数据库—插入(Insertion)在account关系中增加一个元组insertintoaccountvalues(“Perryridge”,“A-9732”,1200)

或者insertintoaccount(branch-name,balance,account-number)values(“Perryridge”,1200,“A-9732”)在account关系中增加一个元组，该元组的balance设置为nullinsertintoaccountvalues(“Perryridge”,“A-777”,null)给所有在Perryridge分支机构有贷款的客户奖励$200，以他们的贷款帐号作为其新的存款帐号insertintoaccountselectbranch-name,loan-number,200fromloanwherebranch-name=“Perryridge”insertintodepositorselectcustomer-name,loan-numberfromloan,borrowerwherebranch-name=“Perryridge”andloan.account-number=borrower.account-number修改数据库—插入(cont.)修改数据库—更新(Update)给所有超过$10,000存款增加6%的利息，其它存款增加5%的利息。updateaccountsetbalance=balance*1.06wherebalance>10000updateaccountsetbalance=balance*1.05wherebalance10000运算的顺序非常重要！关系的连接(JoinedRelations)关系的连接以两个关系作为输入，返回另一个关系作为连接的结果连接运算的典型应用是在from子句中作为子查询表达式连接条件(Joincondition)：定义在参与连接的两个关系中匹配的元组定义出现在连接结果关系中的属性连接类型(Jointype)定义如何处理在连接运算中不匹配的元组关系的连接—DatasetsforExamples关系的连接—Examplesloaninnerjoinborroweronloan.loan-number=borrower.loan-numberloanleftouterjoinborroweronloan.loan-number=borrower.loan-number关系的连接—Examplesloannaturalinnerjoinborrowerloannaturalrightouterjoinborrower关系的连接—Examples

loanfullouterjoinborrowerusing(loan-number)“找出在银行有贷款或者有存款的客户(但不能两者都有)selectcustomer-namefrom(depositornaturalfullouterjoinborrower)whereaccount-numberisnullorloan-numberisnull数据定义语言

(DDL)不仅允许定义一组关系，而且能够说明各关系每个关系的模式与每个关系和属性相关的说明：每个关系的模式各属性的值域完整性约束每个关系要维护的索引集合每个关系的安全和授权信息每个关系在磁盘上的物理存储结构SQL中的域类型char(n)：定长字符串，用户说明长度nvarchar(n)：变长字符串，用户说明长度nint：整数(与计算机相关的整数的有限子集)smallint：小整数(与计算机相关的整数域类型的子集)numeric(p,d)：定点数，用户说明精度，有p位数字，小数点右边有n位数字real,doubleprecision：浮点数和双精度浮点数，精度与机器相关float(n)：浮点数，用户指定至少为n位数字的精度date：日期，包括年(4位)、月和日time：时间，包括时、分和秒创建表的结构SQL关系通过createtable命令来创建

r

是关系名

Example:createtablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger)创建关系表中的完整性约束非空值：notnull

谓词：check(P),其中P是一个谓词Example:说明branch-name为关系branch的主码，并且使得assets的值非负createtablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger,primarykey(branch-name),check(assets>=0))在SQL-92中，说明为primarykey的属性自动为非空嵌入式(Embedded)SQL和动态(Dynamic)SQL嵌入式SQL即在多种程序设计语言中使用SQLSQL所嵌入的语言称为宿主语言(hostlanguage)动态SQL允许程序在运行时构造和提交SQL查询DBMS的完整性控制机制（续）2.检查功能立即执行的约束(Immediateconstraints)

语句执行完后立即检查是否违背完整性约束延迟执行的约束(Deferredconstrainsts)完整性检查延迟到整个事务执行结束后进行DBMS的完整性控制机制（续）例：银行数据库中“借贷总金额应平衡”的约束就应该是延迟执行的约束从账号A转一笔钱到账号B为一个事务，从账号A转出去钱后账就不平了，必须等转入账号B后账才能重新平衡，这时才能进行完整性检查。实体完整性检查和违约处理插入或对主码列进行更新操作时，RDBMS按照实体完整性规则自动进行检查。包括：1.检查主码值是否唯一，如果不唯一则拒绝插入或修改2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改参照完整性检查和违约处理可能破坏参照完整性的情况及违约处理被参照表（例如Student）参照表（例如SC）违约处理可能破坏参照完整性

插入元组拒绝可能破坏参照完整性

修改外码值拒绝删除元组

可能破坏参照完整性拒绝/级连删除/设置为空值修改主码值

可能破坏参照完整性拒绝/级连修改/设置为空值违约处理参照完整性违约处理1.拒绝(NOACTION)执行默认策略2.级联(CASCADE)操作3.设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值［例4］显式说明参照完整性的违约处理示例

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表中相应的元组*/)；

属性上的约束条件的定义CREATETABLE时定义列值非空（NOTNULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）属性上的约束条件的定义(续)1.不允许取空值［例5］在定义SC表时，说明Sno、Cno、Grade属性不允许取空值。

CREATETABLESC

（SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINTNOTNULL，

PRIMARYKEY(Sno，Cno)，

/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/）；属性上的约束条件的定义(续)2.列值唯一［例6］建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATETABLEDEPT(DeptnoNUMERIC(2)，

DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，

PRIMARYKEY(Deptno))；属性上的约束条件的定义(续)3.用CHECK短语指定列值应该满足的条件［例7］Student表的Ssex只允许取“男”或“女”。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(8)NOTNULL，

SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’))，

/*性别属性Ssex只允许取'男'或'女'*/SageSMALLINT，

SdeptCHAR(20));元组上的约束条件的定义(续)［例9］当学生的性别是男时，其名字不能以Ms.打头。

CREATETABLEStudent(SnoCHAR(9)，

SnameCHAR(8)NOTNULL，

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20)，

PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定义元组中Sname和Ssex两个属性值间的约束条件*/)性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立当性别是男性时，要通过检查则名字一定不能以Ms.打头完整性约束命名子句CONSTRAINT约束CONSTRAINT<完整性约束条件名>［PRIMARYKEY短语

|FOREIGNKEY短语

|CHECK短语］［例10］建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

CREATETABLEStudent(SnoNUMERIC(6)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，

SnameCHAR(20)

CONSTRAINTC2NOTNULL，

SageNUMERIC(3)

CONSTRAINTC3CHECK(Sage<30)，

SsexCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男'，'女'))，

CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。［例13］修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40可以先删除原来的约束条件，再增加新的约束条件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，

ALTERTABLEStudentDROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；域中的完整性限制SQL支持域的概念，并可以用CREATEDOMAIN语句建立一个域以及该域应该满足的完整性约束条件。