《数据库系统课件关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《数据库系统课件关系数据库标准语言SQL.ppt（118页珍藏版）》请在金锄头文库上搜索。
1、第3章关系数据库标准语言SQLn31SQL概述n32数据定义n33查询n34数据更新n35视图n36数据控制n37嵌入式SQL（EmbededSQL）31SQL概述1974Boyce和Chaberlin提出SQL19751979IBM的SanJoseResearchLaboratorySystemR上实现1986.10ANSI第 一 个 SQL标 准 ：SQL-861987ISO通过该标准1989ANSISQL-891992ANSISQL-92目前ANSISQL3SQL组成SQL语言可以划分成若干部分：n数据查询（dataquery）n数据操纵（datamanipulation）n数据定义（d
2、atadefinition）n数据控制（datacontrol）SQL特点n1综合统一n在非关系模型（层次和网状）中，数据库语言被划分成明显不同的几 个 部 分 ， 包 括 模 式 定 义 语 言 （ schema data definitionlanguage，简称DDL，用于定义模式）、外模式数据定义语言（subschemadatadefinitionlanguage，用于定义外模式）、与 数 据 存 储 有 关 的 描 述 语 言 （ data storage descriptionlanguage，简称DSDL，用于定义内模式）、数据操纵语言（datamanipulationlangu
3、age，简称DML，用于定义数据存取和处置）。n这些语言不仅在语法上可能有极大的差别，而且应用环境也各不相同。另外，当用户数据库投入运行后，如果需要修改模式，必须停止现有数据库的运行，转储数据，修改模式后并编译后再重装数据库，因此很麻烦。n在关系模型中，SQL如上所述已经将各部分的定义完全统一在一种语言中，而且在系统提供的统一环境中就可以灵活地使用这一语言对任何模式进行操作和定义。SQL特点（续）n2高度非过程化nSQL语言是高度非过程化的，利于提高数据的独立性。只须提出“做什么”，不必指明“怎么做”（过程控制），因此，无须了解存取路径及其选择（系统会根据用户提出的要求自动确定存取路径，并选择
4、并有效地方式进行存取）。n3面向集合的操作方式n非关系数据模型是面向记录的。而SQL语言则面向集合，即元组的集合。从前面的论述过程中也可以看出，关系代数或关系演算的对象和结果都是元组的集合。SQL特点（续）n4以同一种语法结构提供两种使用方法n一般地，数据库语言可以分为自含式（自主式）和嵌入式两种。在SQL语言中，这两种方式的语法结构非常相似，只有很细微的差别。n自含式语言可以独立执行，主要应用于联机交互的环境，它是可以直接输入的SQL命令。嵌入式语言需要嵌入到高级语言中，供程序应用，方便程序进行结构化设计和过程控制台。n5语言简洁，易学易用n核心功能只用了9个动词：CREATE、DROP、A
5、LTER用于数据定义SELECT用于查询INSERT、UPDATE、DELETE用于数据操纵GRANT、REVOKE用于数据控制32数据定义基本对象主要包括表、视图和索引。表tablecreate、drop、alter视图viewcreate、drop索引indexcreate、drop321定义、删除和修改基表（basetable，又称基本表）322建立与删除索引数据定义n321定义、删除和修改基表n1、定义基表n2、修改基表n3、删除基表n322建立与删除索引n1、建立索引n2、删除索引1、定义基表格式：CREATETABLE(列级约束,列级约束,);v注：在上面的格式规定中可以看出，一外
6、表名后的括号中可以跟若干个列的定义，列与列之间用逗号分隔，（方括号表示可选项，尖括号表示必选项，圆括号为实际应用的括号）。另外，SQL语言通常以分号结尾（在少数SQL语言的实际使用环境中，不以分号结尾）。示例定义基表示例n例：CREATETABLEStudentn(SnoCHAR(5)NOTNULLUNIQUE,nSnameCHAR(20),nSsexCHAR(1),nSageINT,nSdeptCHAR(15);v定义及约束条件存于数据字典中。DB2的数据类型SMALLINT半字长的二进制整数INTEGER或INT全字长的二进制整数DECIMAL（p,q）或DEC（p,q）压缩十进制数，共p
7、位，其中小数点后有q位。0qp15，q=0可省FLOAT双定长浮点数CHARTER(n)或CHAR(n)长度为n的定长字符串VARCHAR(n)最长长度为n的变长字符串GRAPHIC(n)长度为n的定长图形字符串VARGRAPHIC(n)最长长度为n为变长图形字符串DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间2、修改基表修改基表包括下列几种方式：修改方式使用的关键字增加新列ADD增加新的完整性约束条件修改原有的列定义MODIFY删除已有的列定义DROP其它（如改变约束的使、能状态） enable、disable格式：ALTERTA
8、BLEADD完整性约束DROPMODIFY;示例修改基表示例n例：在学生表（Student）中增加新列Scome，表示入学时间。nALTERTABLEStudentADDScomeDATE;v注：新增列为空值（允许空值），而不能为非空（列约束为NOTNULL），否则可能破坏已有的数据。在有些实际的数据库系统中，允许在数据库表中为空（没有数据）的情形下，新增非空列。n例：改变列Sage的数据类型定义。nALTERTABLEStudentMODIFYSageSMALLINT;v注：改变列的数据类型定义，一般只允许扩展原有的数据类型长度；而对于减小数据类型长度或改变数据类型（如INT改为DATE）则
9、会有限制。一般地，只有在表中列值为空时允许这类修改。示例（续）n例：删除列Sno的约束。nALTERTABLEStudentDROPUNIQUE(Sno);v如果要删除属性（列），一般需要几个步骤：nl将保留部分及其内容复制到一个新表（可以使用createasselect这样的名句式完成）。nl删除原表。nl将复制的表重新命名。3、删除基表格式：DROPTABLE;n例：删除表Student。nDROPTABLEStudent;v基表定义一旦被删除，表中的数据及在此表上建立的索引都将自动被删除，但与其相关的视图仍然保留，却已无法引用。1、建立索引索引的作用在于加快表的检索（查询）速度。系统在存
10、取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。索引对于用户而言只是作为优化SQL语句的参考（如，如何充分利用现有的索引）。至于系统以何种方式建立索引，要引用哪些索引，如何引用都无须关心。n格式：CREATEUNIQUECLUSTERINDEXnON ( , );v其中次序有两种，即升序（ASC，缺省值）和DESC（降序）。UNIQUE表示索引值唯一，在此索引的约束下，元组的索引项不能有相同的值。示例建立索引示例n例：为表Student中的列Sno建立唯一索引。nCREATEUNIQUEINDEXStusnoONStudent(Sno);n例：为表Course中的列Cno建立
11、唯一索引。nCREATEUNIQUEINDEXCoucnoONCourse(Cno);n例：为表SC中的列Sno和Cno建立唯一索引，且Sno升序，Cno降序。nCREATE UNIQUE INDEX SCno ON SC(SnoASC,CnoDESC);CLUSTER说明nCLUSTER表示聚簇索引，是指索引项的顺序与表中记录的物理顺序一致的索引组织（如若索引为升序，则表中数据的存放按索引项的升序依次存放）。为了提高查询的效率，一表只能有一个聚簇索引项。在更新索引列数据时，由于可能会导致表中元组的物理顺序变更，所以系统维护的代价比较大。如果索引列需要经常更新的话，则不宜在其上建立聚簇索引。n
12、对于聚簇索引，一般地，在物理空间上也会更节省，因为可以减少索引列的一个索引表（如果是多级索引的话，一个索引列可能会有多个索引表）。2、删除索引一旦建立索引，数据库系统负责使用、维护，用户不需要干预。使用索引能够减少查询的操作时间，但在数据增、删、改时，系统会花费时间、空间一维护相关索引表，又会增加开销，所以索引并不是建得越多越好。有时需要对索引进行调整，如删除索引。n格式：DROPINDEX;n例：删除索引Stusname。nDROPINDEXStusname;v删除指定的索引后，系统会删除索引内容，并将索引的定义（描述）也从数据字典中删除。33查询格式：SELECT ALL|DISTINCT
13、 ,FROM,WHEREGROUPBYHAVINGORDERBYASC|DESC;v注：“|”表示选择项，其中排在前面的ALL和ASC为缺省选择项。目标列表达式可以是列名，也可以是算术表达式、字符串常量、函数等。n331单表查询n332连接查询（多表检索）n333嵌套查询n334集合查询单表查询n1投影n选择表中的若干列n2选择n选择表中的若干元组n3排序n对查询结果排序n4使用集合函数n又称集函数，或聚集函数，或组函数n5分组n对查询结果分组1投影n1）查询指定列n例：SELECTSno,SnameFROMStudent;v指定列表达式时，先后顺序可以与表中的顺序不同，即可以改变列的显示顺序
14、。n例：SELECTSname,Sno,SdeptFROMStudent;n2）查询全部列（全表查询）n例：SELECT*FROMStudent;v其中“*”表示全部列，也可以写上所有属性的列名来代替。n3）查询经过计算的值n见例查询计算列示例n例：SELECTSname,2001-SageFROMStudent;nSage原来表示年龄，如果当前年份为2001年，表达式2001-Sage表示出生年份。n例：SELECTSname,YearofBirth:,2001-Sage,ISLOWER(Sdept)nFROMStudent;n这例中使用了字符串常量和函数，字符串常量用于改变列的显示标题，函
15、数则作用于每一检索到的元组。其显示结果如下：SnameYearofBirth:2001-SageISLOWER(Sdept)-例：SELECTSnameName,YearofBirth:BIRTH,2001-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent;其显示结果如下：NAMEBIRTHBIRTHDAYDEPARTMENT-2选择n1）消除取值重复的行n2）查询满足条件的元组n1、关于比较运算符的使用n2、关于确定范围的使用n3、关于确定集合的使用n4、关于匹配的使用n5、关于空值的判断n6、关于逻辑运算符的使用1）消除取值重复的行n例：SE
16、LECTSnoFROMSC;n这个查询结果中可能含有重复的行（元组），因为如果一个学生选修了多门课的话，该学生在SC表中将出现多次。n这一查询相当于：SELECTALLSnoFROMSC;n为了消除查询结果中的重复的行，可以使用关键字DISTINCT。n例 ： SELECT DISTINCT Sno FROMSC;2）查询满足条件的元组n查询条件用条件表达式表示，使用了下面的运算符：比较运算符=，=，=，!=，!，!，NOT+比较符确定范围BETWEEN AND ，NOT BETWEEN AND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值判断ISNULL，ISNOTNULL逻辑
17、运算符AND，OR，NOT1、关于比较运算符的使用n例：SELECTSnameFROMStudentnWHERESdept=CS;n例：SELECTSname,SageFROMStudentnWHERESage=20;n例：SELECTDISTINCTSnoFROMSCnWHEREGrade60;2、关于确定范围的使用n例：SELECTSname,Sdept,SagenFROMStudentnWHERE Sage BETWEEN 20 AND23;n例：SELECTSname,Sdept,SagenFROMStudentnWHERE Sage NOT BETWEEN 20AND23;3、关于确
18、定集合的使用n例：SELECTSname,SsexFROMStudentnWHERESdeptIN(IS,MA,CS);n例：SELECTSname,SsexFROMStudentnWHERESdeptNOTIN(IS,MA,CS);4、关于匹配的使用n匹配格式：NOTLIKEESCAPEv换码字符有时也称为转义符。匹配串中可以使用通配符，包括百分号（“%”）和下划线（“_”）。百分号表示任意长度（可为0）的字符串，下划线表示任意单个字符。n例：SELECT*FROMStudentWHERESnoLIKE95001;v此例要求找出与学号95001相同的学生的信息。此处没有使用通配符。在匹配串中
19、不含通配符时，通常可用“=”代替“LIKE”，如nSELECT*FROMStudentWHERESno=95001;v而“!=”或“”等不等号可以代替“NOTLIKE”。关于匹配的使用（续）n例：找出95级学生的信息。SELECT*FROMStudentWHERESnoLIKE95%;v其中学号的前两位表示该学生的所在级号。n例：找出所有姓“刘”的同学信息。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE刘%;n例：找出姓“欧阳”的单名学生的学号。SELECTSnameFROMStudentWHERESnameLIKE欧阳_;v由于一个汉字相当于两个字
20、符，所以使用两个下划线表示一个汉字的单名。n例：找出名字第二个字为“阳”的同学姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE_阳%;关于匹配的使用（续）n例：找出不姓“刘”的学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMStudentWHERESnameNOTLIKE刘%;n例：找出课程名为“DB_Design”的课号和学分。SELECTCno,CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE;v由于课程名为的下划线与SQL语言中使用的通配符相同，为了避免产生岐义，所以使用转义符
21、（换码字符）“”将下划线转为不具有特殊含义的字符（即使_失去了作为通配符时的含义，只表示一下划线字符）。n例：找出课程名以“DB_”开头，并且倒数第三个字符为“i”的课程信息。SELECT * FROM Course WHERE Cname LIKE DB_%i_ESCAPE;v此例中出现了多个下划线，只有第一个下划线前带有转义符“”，其它两个都没有。所以除了第一个下划线保持下划线的本意外，另外两个下划线字符表示两结尾的字符。5、关于空值的判断n例：找出选修课成绩尚未登记的学生学号及其选修课程号。nSELECT Sno,Cno FROM SC WHEREGradeISNULL;n例：找出已经登
22、记了选修课程成绩的学生学号及其选修课程号。nSELECT Sno,Cno FROM SC WHEREGradeISNOTNULL;6、关于逻辑运算符的使用n例：找出计算机系（CS）年龄在20岁以下（不含20）的学生姓名。nSELECTSnameFROMStudentnWHERESdept=CSANDSage20;v关键字IN相当于多个OR的缩写形式。如vSdeptIN(IS,MA,CS);vvSdept=ISORSdept=MAORSdept=CS;3排序v如果不指定排序的方式，查询结果一般按照元组在表中的存储顺序依次列示。为了调整显示的顺序以便更好地观察结果，可以对查询结果进行排序。n例：按
23、照成绩从高到底（降序）对课程1的选修学生和成绩进行列示。nSELECTSno,GradeFROMSCWHERECno=1nORDERBYGradeDESC;v注意：如果元组中有空值存在，则在升序时，空值排在结果的最后；在降序时，空值排在结果的最前。排序（续）v在排序时可以指定多个列，系统将按照指定列的顺序来依次列示结果。n例：显示所有学生的信息，按系排序（不指定升序或降序，通常使用缺省值，一般升序为SQL语言中的缺省值），同系中按年龄从大到小排列（降序）。nSELECT*FROMStudentORDERBYSdept,SageDESC;v有时也可以使用列的序号来指定排序的列，这在列为常量、函数
24、等较长列名时尤其有效。n例：SELECTSno,SnameFROMStudentORDERBY1;v这里的“1”即指列Sno。4使用集函数nSQL语言中使用的集合函数主要有：COUNT(DISTINCT|ALL*)统计元组个数COUNT(DISTINCT|ALL)统计一列中值的个数SUM(DISTINCT|ALL)计算一列值的总和（列须是数值型）AVG(DISTINCT|ALL)计算一列值的平均值（列须是数值型）MAX(DISTINCT|ALL)计算一列值的最大值MIN(DISTINCT|ALL)计算一列值的最小值使用集函数（续）n例：求总的学生数。nSELECTCOUNT(*)FROMStu
25、dent;n例：求选修了课程的学生数。nSELECTCOUNT(DISTINCTSno)FROMSC;n例：求课程1的平均成绩。nSELECTAVG(Grade)FROMSCWHERECno=1;n例：求课程1的最高分数。nSELECTMAX(Grade)FROMSCWHERECno=1;5分组v将查询结果表的各行按一列或多列取值相等的原则进行分组。目的是细化集合函数的作用对象。如果不分组，集合函数的作用对象就是整个结果；如果分组，集合函数的作用对象就是每一组。n例：找出每门的选修人数。nSELECTCno,COUNT(Sno)FROMSCGROUPBYCno;n例：查询选修课程超过3门的信息
26、系学生学号。nSELECT Sno FROM SC WHERE Sdept=IS GROUP BYSnonHAVINGCOUNT(*)3;v其中HAVING子句用于对分组结果进行筛选。连接查询n1等值与非等值连接查询n2自身连接n3外连接（OuterJoin）n4复合条件连接1等值与非等值连接查询n等值连接和非等值连接的连接条件（用来连接两个表的条件，又称连接谓词）的一般格式是：n.v当比较运算符使用“=”时，称为等值连接；使用其它运算符则称为非等值连接。n另外还有一处比较特殊的形式：n.BETWEEN.AND.v连接条件中的列名称为连接字段，其数据类型必须是可比的，但不必是相同的。例如，可以
27、都是字符型，或者都是日期型；也可以是一个是整型，另一个是实数型。由于整型和实数型都是数值型，是可比的。等值与非等值连接查询（续）v从概念上讲，DBMS执行连接操作的过程是，首先在表1中找到第一个元组，然后从头开始顺序扫描或按索引扫描表2，查找满足连接的元组，每找到一个元组，就将表1中的第一个元组与该元组拼接起来，形成结果表中的一个元组。表2全部扫描完毕后，再到表1中中找第二个元组，然后再从头开始顺序扫描或按索引扫描表2，查找满足连接条件的元组，每找到一个元组，就将表1中的第二个元组与该元组拼接起来，形成结果表中的一个元组。一直重复上述操作，直到表1全部元组都处理完毕为止。n例：查询每个学生及其
28、选修课程的情况。nSELECTStudent.*,SC.*FROMStudent,SCnWHEREStudent.Sno=SC.Sno;等值与非等值连接查询（续）n连接运算中有两种特殊情况，一种称为卡氏积连接，另一种称为自然连接。n卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中元组的交叉乘积，也即其中一表中的每一个元组都要与另一表中的每一元组作拼接，因此结果表往往很大。n如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。n此外，还有一种称为半联接的情况。半联接是自然联接的一种特殊情况，是自然联接后只保留了其中一个表的属性
29、，而忽略了另一个表的除重复属性外的其它属性。其与自然联接的关系是：RS=R(RS)n即R和S的半联接就是R和S的自然联接结果在R上的投影。2自身连接v连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。n例：查询每门课的间接先修课（即先修课的先修课）。nSELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDnWHEREFIRST.Cpno=SECOND.Cno;v很明显，属性列Cno和Cpno来自同一个域，是可比的。3外连接v在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。但有时候也希望
30、一些不满足连接条件的元组也作为输出，比如，要查询学生的选课情况，看看每个学生都选了哪几门课，这时就要把Student和SC两个表进行连接操作，但没有选课（或者说选课数为0）的学生不会出现在SC表中，因此在结果中就不会输出。这对查询所有学生的要求来说，就不太理想了，最好选了课学生和没选课的学生都列示出来，作为结果，这时候就需要使用外连接了。v外连接根据连接符（一般为*，有时为+）出现在连接运算符的左边还是右边，分别称为左外连接（leftouterjoin）和右外连接（rightouterjoin）。有些数据库系统就直接使用“leftouterjoin”和“rightouterjoin”作为外连接
31、谓词，而不使用连接符。外连接（续）v外连接就好象是为连接符指定的表增加了一个“万能”的行，这个行全部由空值组成，它可以和另一个表中所有不能与指定表其他行连接的元组进行连接。n例：查询每个学生的信息及其选修课情况nSELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradenFROMStudent,SCnWHEREStudent.Sno=SC.Sno(*);v注：在多表检索中，由于几个表可能有相同的属性列，这些列名也有可能同名，为了区别，在指定具有同名的属性列时，要加上该列的前缀，即所属的表名（如上例中的Student.Sno），而对于不同名的属性列，则可以
32、不加前缀。v如果不使用连接符，连接条件就可能是“RIGHTOUTERJOINStudent.SnoANDSC.Sno”，或是“RIGHTOUTERJOINONStudent.Sno=SC.Sno”。4复合条件连接v当WHERE子句中出现多个条件的连接操作时，称为复合条件连接。连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，这通常称为多表连接。n例：查询选修2号课程且成绩在90分以上的所有学生。nSELECTStudent.Sno,SnamenFROMStudent,SCnWHERE Student.Sno=SC.Sno AND SC.Cno=2 ANDSC.Gr
33、ade90;n例：查询每个学生选修的课程名及其成绩。nSELECTStudent.Sno,Sname,Course.Cname,SC.GradenFROMStudent,SC,CoursenWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;嵌套查询n说明n1带有IN谓词的子查询n2带有比较运算符的子查询n3带有ANY或ALL的子查询n4带有EXISTS谓词的子查询说明v在SQL语言中，一个SELECT-FROM-WHERE为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。n例：查询选修了课程2
34、的学生姓名。nSELECTSnameFROMStudentnWHERESnoINn(SELECTSnoFROMSCWHERECno=2);v上例中，把括号外的SELECT-FROM-WHERE查询块称为主查询（或称父查询），把括号内的SELECT-FROM-WHERE查询块称为子查询（或称内层查询），子查询不能使用ORDERBY子句。v嵌套查询的求解方法是由里向外处理。即每个子查询在其上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。v嵌套查询使得可以用一系列简单查询构成复杂的查询，从而明显地增强了SQL的查询能力。以层层嵌套的方式构造程序正是SQL中“结构化”的含义所在。1带有
35、IN谓词的子查询n例：查询与“刘晨”在同一个系的学生。nSELECTSno,Sname,SdeptFROMStudentnWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨);n此例也可以用表的自身连接查询完成：nSELECTS1.Sno,S1.Sname,S1.SdeptnFROMStudentS1,StudentS2nWHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨;v有时候，为了区别主查询和子查询中出现的相同的表名，可以为各查询中的表名定义不同的别名以示区别。如：nSELECTSno,Sname,SdeptFROMSt
36、udentS1nWHERE S1.Sdept IN (SELECT Sdept FROM Student S2WHERES2.Sname=刘晨);带有IN谓词的子查询（续）n例：查询选修了课程名为“信息系统”的学生学号和姓名。nSELECTSno,SnameFROMStudentnWHERESnoINn(SELECTSnoFROMSCnWHERECnoINn(SELECTCnoFROMCourseWHERECname=信息系统);v查询涉及多个关系时，用嵌套查询逐步求解，层次清晰，易于理解，具备结构化程序设计的优点。当然有些嵌套查询是可以是连接运算替代（有些是不可替代的）。到底采用哪种方法，用
37、户可以根据自己的习惯以及执行效率确定。v在上面的例子中，各子查询由里向外执行，而且都只执行一次，其结果用于父查询，子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。不相关子查询是最简单的一类子查询。2带有比较运算符的子查询n当确切知道内层查询返回的是单值时，可以用比较运算符。n例：查询选修了课程名为“信息系统”的学生学呈和姓名。nSELECTSno,SnameFROMStudentnWHERESnoINn(SELECTSnoFROMSCnWHERECno=n(SELECTCnoFROMCourseWHERECname=信息系统);v由于确定课程名为“信息系统”的课号只可能有一个，所以
38、 在 Cno和 子 查 询 “(SELECT Cno FROM CourseWHERECname=信息系统)”间使用了“=”（注：子查询一定要跟在比较符之后）。3带有ANY或ALL的子查询子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义为：ANY大于子查询中某个值=ANY大于等于子查询中某个值=ANY小于等于子查询中某个值=ANY等于子查询中某个值!=ANY或ANY不等于子查询中某个值ALL大于子查询中的所有值=ALL大于等于子查询中的所有值=ALL小于等于子查询中的所有值=ALL等子查询中的所有值!=ALL或ALL不等于子查询中的所有值带有ANY或
39、ALL的子查询（续）n例：查询其它系中比IS系作一学生年龄小的学生名单。nSELECTSname,SageFROMStudentnWHERESageANYn(SELECTSageFROMStudentWHERESdept=IS)nANDSdeptISnORDERBYSageDESC;n该例也可以用集合函数来实现。如：nSELECTSname,SageFROMStudentnWHERESagen(SELECTMAX(Sage)FROMStudentWHERESdept=IS)nANDSdeptISnORDERBYSageDESC;带有ANY或ALL的子查询（续）n例：查询其它系中比IS系所有学生
40、年龄都小的学生名单。nSELECTSname,SageFROMStudentnWHERESageALLn(SELECTSageFROMStudentWHERESdept=IS)nANDSdeptISnORDERBYSageDESC;n此例也可以用集函数实现。如nSELECTSname,SageFROMStudentnWHERESagen(SELECTMIN(Sage)FROMStudentWHERESdept=IS)nANDSdeptISnORDERBYSageDESC;带有ANY或ALL的子查询（续）v事实上，用集函数实现子查询通常比直接用ANY或者ALL查询效率更高。ANY、ALL与集函数
41、的对应关系如下所示：=或!=ANYINMAXMIN=MINALLNOTINMINMAX=MAX4带有EXISTS谓词的子查询vEXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何实际数据，只产生逻辑真值“true”或逻辑假值“false”。n例：查询所有选修了1号课程和学生姓名。nSELECTSnameFROMStudentnWHEREEXISTSn(SELECT*FROMSCWHERESno=Student.SnoANDCno=1);v由于EXISTS只返回逻辑值，所以在EXISTS后的子查询一般使用“*”来代替列名，因为即使指定的列名也无意义。v在这例中，子查询的查询条件依赖于
42、外层父查询的某个属性值（Student.Sno），这类查询被称为相关子查询（correlatedsubquery）。带有EXISTS谓词的子查询（续）n前面提到，求解不相关子查询时，一次将子查询求解出来，然后求解父查询。而在求解相关子查询中，不是这样。而是先取外层查询中的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后检查下一个元组，重复这一过程，直至外层查询的元组全部检索过。n可以用EXISTS等价替换带IN、比较运算符、ANY和ALL的子查询，但不能用其他形式子查询等价替换EXISTS子查询。n由于带EXISTS谓词的相关子查询只
43、关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于不相关子查询，甚至有时是最高效的方法。nSQL语言中没有全称量词（Forall），因此必须利用谓词演算将一个带有全称量词的谓词转换为等价的存在量词的谓词：n(x)p(x(p)n见例量词转换示例n例：查询选修了全部课程的学生姓名。nSELECTSnameFROMStudentnWHERENOTEXISTSn(SELECT*FROMCoursenWHERENOTEXISTSn(SELECT*FROMSCnWHERESno=Student.SnoANDCno=Course.Cno);带有EXISTS谓词的子查询（续）vSQL语言中也没
44、有蕴含（Implication）逻辑运算。因此必须利用谓词演算将一个逻辑蕴含的谓词转换为等价的带有存在量词的谓词：npqpqn例：查询至少选修了学生95002选修的全部课程的学生学号。v本题的查询要求可以做如下解释，查询这样的学生：凡是95002选修的课，他都选取修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为95002的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。该查询可以形式化地表示如下：v用p表示谓词“学生95002选修了课程y”v用q表示谓词“学生x选修了课程y”v则上述查询可表示为(y)(pq)n该查询可以转换为如下等价形式：n(y)(pq)y(pq)
45、y(pq)ny(pq)带有EXISTS谓词的子查询（续）vy(pq)v它所表达的语义为：不存在这样的课程y，学生95002选修了，而学生x没有选。用SQL语言表示如下：SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=95002ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCX.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno);集合查询v每一个SELECT语句都能获得一个或一组元组。若要把多个SELECT语句的结果合并为一个结果，可用集合操作来完成。集合操作主要包 括
46、并 操 作 （ UNION） 、 交 操 作 （ INTERSECT） 和 差 操 作（MINUS）。n例：查询计算机科学系的学生及年龄不大于19岁的学生。nSELECT*FROMStudentWHERESdept=CSnUNIONnSELECT*FROMStudentWHERESage=19;n例：查询选修了课程1或者选修了课程2的学生。nSELECTSnoFROMSCWHERECno=1nUNIONnSELECTSnoFROMSCWHERECno=2集合查询（续）v标准SQL中没有直接提供集合交操作和集合差操作（在ORACLE中，则提供了单独的交操作和差操作，使用方法与UNION一样），但
47、可以用其他方法来实现。具体实现方法依查询不同而不同。n例：查询计算机科学系的学生与年龄不大于19岁的学生交集。v换种角度，就是查询计算机科学系中年龄不大于19岁的学生。nSELECT*FROMStudentnWHERESdept=CSANDSage19;n例：查询选修课程1的学生集合与选修课程2的学生集合的差集。n即查询选修了课程1但没有选修课程2的学生。nSELECTSnoFROMSCnWHERECno=1ANDSnoNOTINn(SELECTSnoFROMSCWHERECno=2);34数据更新n1、插入数据n2、修改数据n3、删除数据1、插入数据n1插入单个元组n格式：INSERTnIN
48、TO(,)nVALUES(,);n2插入子查询结果n格式：INSERTnINTO(,)n;n例：对每一个系，求学生的平均年龄，并把结果存入数据库。nINSERTnINTODeptage(Sdept,Avgage)nSELECT Sdept,AVG(Sage) FROM Student GROUP BYSdept;2、修改数据n格式：UPDATEnSET=,=nWHERE;v根据条件的指定可以修改一个元组的值，也可以修改多个元组的值，此外也可以使用带子查询的修改语句。n例：将计算机科学系的学生的成绩置零。nUPDATESCnSETGrade=0nWHERECS=n(SELECTSdeptFROM
49、StudentWHEREStudent.Sno=SC.Sno);vUPDATE语句一次只能操作一个表。这会带来数据库的一致性起问题。例如，学号为95007的学生因病休学一年，复学后需要将其学号改为96089，由于Student表和SC表都有关于95007的信息，因此两个表都需要修改，而这种修改只能通过两条UPDATE语句进行。v在执行了一句修改后，数据库中的数据处于不一致状态，数据的参照完整性受到破坏。只有两条修改语句都执行了，数据库才会再次片于一致状态，为了保证这两条语句要么都做，要么都不做，数据库系统通常都引入了事务（transaction）的概念，这在以后再讲。3、删除数据n删除语句的一
50、般格式为：nDELETEnFROMnWHERE;vDELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，则表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。v与修改语句一样，根据指定条件的不同，可以分别完成一个元组、多个元组的删除，也可以完成带子查询的删除，而且也可能造成数据库的一致性问题。35视图n351定义视图n352查询视图n353更新视图n354视图的用途351定义视图1建立视图n格式：CREATEVIEW(,)nASnWITHCHECKOPTION;n子查询可以是任意复杂的SELEC
51、T语句，但通常不允许含有ORDERBY子句和DISTINCT短语。nWITH子句表示对视图进行UPDATE、INSERT和DELETE操作时保证更新、插入和删除的行满足视图定义的谓词条件（即子查询的条件表达式）。v视图的列名可以省略，但以下情况需要明确组成视图的所有列名（或全部省略，或全部指定）：n其中某个目标列不是单纯的属性名，而是集函数或列表达式。n多表连接时选出了几个同名列作为视图的字段。n需要在视图中为某个列启用新的更合适的名字。vDBMS执行CREATEVIEW语句的结果只是把对视图定义存入数据字典，并不执行其中的SELECT语句，只在对视图查询时，才按视图的定义从基表中将数据查询出
52、来。n视图可以分为行列子集视图、带表达式的视图和分组视图几种。n行列子集视图从单个基表导出，只去掉了基表的某些行和某些列，但保留了基表的码的查询。n带表达式的视图是带虚拟列的视图。n分组视图则是带有集函数和GROUPBY子句的查询。n视图的主要作用是减少冗余和简化查询。基表只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。n在已有字段上的视图定义当然也不含这些派生数据。n视图中的数据不实际存储。根据基表中的列派生出的属性列不实际存在，故称属性列。续1续2n如果子查询中使用了“SELECT*”子句选择了一表的全部，如：nCREATEVIEWF_Student(Stdnum,nam
53、e,sex,age,dept)nASSELECT*FROMStudentWHERESsex=女;n如果Student表增加一新的列，该视图与Student表的列映象关系就受破坏。因为在视图的定义中没有关于新列的定义，子查询列表与视图表无法对应，视图的应用就会产生错误，解决的方法有两种：n把“*”改为明确的列名，保持子查询列表与视图定义列表的数量、类型的一致。n重建视图n在ORACLE中，采用如下的方法：n将“*”自动转换成该表的全部列。基表增加的新列不含在已有视图中。n如果视图查询包含下列结构：连接、集合运算符、集函数、GROUPBY子句、CONNECTBY子句或STARTWITH子句和DIS
54、TINCT算符，则在视图上不能执行插入、修改和删除操作。删除视图n2删除视图n格式：DROPVIEW;n视图所基的表如果被删除，视图将失效，基于该视图的其它视图也失效。但视图的定义在未执行DROPVIEW语句前始终存在于数据字典。352查询视图nDBMS执行对视图的查询时，首先进行有效性检查：所涉及的表、视图等是否在数据库中存在。如果存在，则从数据字典中取出视图定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基表的查询，然后再执行这个经过修正的查询。n对视图的查询转换为基表的查询的过程，称为消解（viewresolution）。但是并不是所有的消解都有意义的。n见例示例n例：有视图S
55、_G的子查询如下：nSELECTSno,AVG(Grade)FROMSCGROUPBYSno;n现有一个对S_G的查询如下：nSELECT*FROMS_GWHEREGavg=90;n在多数情况下，关系数据库会将其转换成：nSELECTSno,AVG(Grade)FROMSCnWHEREAVG(Grade)=90nGROUPBYSno;n实际上，这一消解的结果是错误的，因为在WHERE子句是不能使用集函数的。合理的查询形式应使用HAVING短语：nSELECTSno,AVG(Grade)FROMSCnGROUPBYSnonHAVINGAVG(Grade)=90;v注意：对非行列子集的查询的转换可
56、能导致不正确的SQL语句，应尽量避免条件中出现这类特殊属性。353更新视图n更新的操作主要是插入（INSERT）、修改（UPDATE）和删除（DELETE）。对视图可以进行更新操作。n实际上，对视图的更新，最终要转换成为对基表的更新。n可以用带“WITHCHECKOPTION”子句来限定用户对基表操作的权限。带该子句的视图在进行更新操作时，系统会把视图的子查询中的条件作为更新条件加以处理，若不满足条件，则拒绝执行该更新操作。示例可以看出，有时候转换后的条件是多余的（由于Sno为码，已经可以唯一的确定一个元组，条件“Sdept=IS”事实上并不起作用）。有视图IS_Student对视图的更新操作
57、结合定义子查询和操作条件后转换成对基表的更新操作CREATE VIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=ISWITHCHECKOPTION;UPDATEIS_StudentSETSname=刘晨WHERESno=95002;UPDATEStudentSETSname=刘晨WHERESno=95002ANDSdept=IS;INSERTINTOIS_StudentVALUES(95002,赵 青,20);INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES(95002,赵青,20,IS
58、);DELETEFROMIS_StudentWHERESno=95002;DELETEFROMStudentWHERESno=95002ANDSdept=IS;更新条件n一般可以更新的条件：n所有行列子集视图可以修改、删除元组。n若基表中所有不允许为空的列都在视图中，则可插入（如此才可能对所有不允许为空的列进行赋值）。n视图可以分为理论上可更新和理论上不可更新的。理论上可更新的确切特征尚待研究，而理论上不可更新的规定各关系数据库差异也很大，参见DB2规定。n不可更新的视图即是理论上不可更新的视图，即无法转换成对基表的更新。所以一般基表和视图间无法一一映射的话，都会被限制更新。n不允许更新的视图
59、则是有可能可更新的，只不过系统不支持，无法保证数据的完整。因为如果映射条件非常复杂，支持的代价过大，系统可以考虑不支持。DB2更新条件规定n若视图是由两个以上基表导出，不许更新。n若视图的字段来自字段表达式或常数，不许INSERT和UPDATE，但可以DELETE。n若视图字段来自集函数，不许更新。n视图定义中含GROUPBY子句，不许更新。n若视图定义中含DISTINCT短语，不许更新。n有嵌套查询，且内层查询的FROM子句中涉及的表也是导出该视图的基表，不许更新。n例：成绩在平均成绩之上的元组GOOD_SC:CREATEVIEWGOOD_SCASSELECTSno,Cno,GradeFRO
60、MSCWHEREGrade(SELECTAVG(Grade)FROMSC);n一个不允许更新的视图上定义的视图也不允许更新。354视图的用途n视图的用途大致有这么几种：n1视图能够简化用户的操作，一是简化对象的内容，二是屏蔽查询的复杂度，如视图可以将多个表连接起来，用户在查询是就可以省略复杂的连接条件。n2视图使用户能以多种角度看待同一数据，增加了数据的灵活性。如可以用视图将基表的数据按照不同的方式进行各类统计（求平均值，计数等），用户可以看到很多不同的数据，如平均值、计数等，但这些数据都来源于同一数据。n3视图对重构数据库提供了一定程度的逻辑独立性。数据独立性一般分为物理独立性和逻辑独立性，
61、物理独立性就是用户和应用不依赖于数据库的物理结构，而逻辑独立性就是数据库重构时，如增加新的关系，原有关系增加新的字段等，用户和应用不受影响。见例。n4视图能够对机密数据提供安全保护。n把基表的数据通过视图分成若干不同安全级别的部分，然后按照安全级别分别授权给不同的用户，就可以在一定程序上保护数据的安全性。通过细致的划分，就可以保证不同级别的用户只能看到属于他所在级别的数据。示例n例：关系数据库的重构（INF2NF或2NF3NF时会导致重构的需求），即将一个表分成多个表。如：nStudent(Sno,Sname,Ssex,Sage,Sdept,Sdeptloc)将转换成两个关系：nStudent
62、s(Sno,Sname,Ssex,Sage,Sdept)和nDepartments(Sdept,Sdeptloc)n而应用中已经有下面的查询：nSELECTSno,SdeptlocFROMStudent;n则可以采用如下的解决方案，即增加一个视图：nCREATEVIEWStudent(Sno,Sname,Ssex,Sage,Sdept,Sdeptloc)nASnSELECTSX.Sno,Sx.Sname,SX.Ssex,SX.Sage,SY.Sdept,SY.SdeptlocnFROMStudentsSX,DepartmentsSYnWHERESX.Sdept=SY.Sdept;36数据控制n
63、数据控制也称数据保护，分为安全性控制、完整性控制、并发控制和恢复。n安全性控制主要是防止数据泄密和破坏，其主要措施是进行存取控制。n完整性控制是为了保证数据的正确性和相容性。1授权2收回权限1授权格式：GRANT,ON,TO,WITHGRANTOPTION；不同对象类型允许的操作权限如下表所示：对象对象类型操作权限属性列TABLESELECT、 INSERT、 UPDATE、 DELETE、 ALLPRIVILEGES视图TABLESELECT、 INSERT、 UPDATE、 DELETE、 ALLPRIVILEGES基表TABLESELECT、INSERT、UPDATE、DELETE、AL
64、TER、INDEX、ALLPRIVILEGES数据库DATABASECREATETAB其中ALLPRIVILEGES表示所有权限。在用户指定时，可以使用PUBLIC，表示全体用户。短语“WITHGRANTOPTION”表示允许转授权限。另外，在权限指定时可以指定列表，列表用括号表示。示例授权示例n例：GRANTSELECTONTABLEStudentTOU1;nGRANTALLPRIVILEGESONTABLEStudent,CourseTOU2,U3;nGRANTSELECTONTABLESCTOPUBLIC;nGRANTUPDATE(Sno),SELECTONTABLEStudentTOU
65、4;nGRANT INSERT ON TABLE SC TO U5 WITH GRANTOPTION;n则U5可以执行下列授权：nGRANT INSERT ON TABLE SC TO U6 WITH GRANTOPTION;nU6获U5的授权后，又可以将所获权限转换给U7：nGRANTINSERTONTABLESCTOU7;nGRANTCREATABONDATABASES_CTOU8;2收回权限n格式：REVOKE,nON,nFROM,;n可以将权限授予他人，当然也可以收回已经授出的权限了。参见示例。收权示例n例 ： REVOKE UPDATE(Sno) ON TABLEStudentFRO
66、MU4;nREVOKESELECTONTABLESCFROMPUBLIC:nREVOKEINSERTONTABLESCFROMU5;n此时，DBMS会同时自动收回U6和U7对表SC的INSERT权限。即收回权限的操作会导致级联效应。n系统只回收直接或间接从U5处得到的权限。若有其它用户授权U6、U7对SC进行INSERT，而非U5，则U6、U7仍具有此INSERT权限。37嵌入式SQLn由于应用都是过程化的，而SQL语言却是非过程化的，因此应用不能通过简单的SQL语言来完成。为了完成应用，一般会把SQL语言嵌入到某种高级语言中，如PL/1，COBOL，FORTRAN，C等。这些嵌入有SQL语言
67、的高级语言相对地称为主语言或宿主语言。而在这些高级语言中使用的SQL语言则称为嵌入式SQL语言。n371嵌入式SQL的一般形式n372嵌入式SQL语句与主语句之间的通信n373不用游标的SQL语句n374使用游标的SQL语句n375动态SQL简介371嵌入式SQL的一般形式n对宿主型SQL语言，DBMS可采用两种方法处理，一种是预编译，另一种是修改和扩充主语言使之能够处理SQL语句。预编译就是DBMS的预处理程序对源程序进行扫描，识别出其中的SQL语句，并将其转换成主语言调用语句，使主语言编译程序能够识别并最终编译成目标码。n为了区分SQL语句和主语言语句，一般都在SQL语句前加前缀：nEXE
68、CSQLn例如，在C或PL/1的嵌入式语句的一般形式为：nEXECSQL;n而在COBOL中，其形式如下：nEXECSQLEND-EXECn例：DROPTABLEStudent;n转换成C的嵌入式形式，则为：nEXECSQLDROPTABLEStudent;372嵌入式SQL语句与主语句之间的通信n在嵌入式形式下，SQL语句负责操纵数据库，高级语言语句负责控制程序流程。这时程序中会含有两种不同计算模型的语句，一种是描述性的面向集合的SQL语句，一种是过程性、面向记录的高级语言语句，它们之间必须要有一种通信机制，这种机制要保证：1.向主语言传递SQL语句的执行状态信息，如正确与否、处理量等，使主
69、语言能够据此控制程序的流程。2.主语言向SQL语句提供参数，如通过GUI接收的用户输入等。3.将SQL语句查询数据库的结果交主语言进一步处理，如表等。n其中执行状态信息通过SQL通信区（SQLCommunicationArea，简称SQLCA）来完成通信；输入数据则通过主变量（hostvariable，又称宿主变量）向SQL语句提供参数，而输出数据则通过主变量和游标（cursor，也有称光标的）来完成通信。SQL通信区nSQL通信区是一数据结构，用“EXECSQLINCLUDESQLCA”来定义。在该结构中有若干变量，用于存放不同的状态信息，其中SQLCODE用 于 说 明 SQL语 句 的
70、执 行 的 成 功 与 否 ， 如 成 功（SUCCESS），返回0；否则返回失败，一般以负数表示例外或错误代码。例如，DELETE可能有以下返回：n违反数据保护规则，操作拒绝。（可能返回accessdenied的错误代码。）n没有满足条件的元组，一行也没有删除。（返回成功，但其它变量，如ROWSAFFECTED为0。）n成功删除，并删除的行数。（SQLCA.SQLCODE=SUCCESS且ROWSAFFECTED为删除行的数。）n无条件删除警告信息。（如是否全部删除？）n由于各种原因，执行出错。（如语句错。）宿主变量n在SQL语句中使用的主语言程序的变量。根据其使用的场合可以分为输入主变量（
71、主语言语句赋值，SQL语句引用）、输出主变量（SQL语句赋值，主语言语句引用）和两者兼具的主变量。n输入主变量主要应用于插入、修改和条件设置等情形。而输出主变量主要提供SQL语句的结果数据和状态。n每一个主变量可带一任选的指示变量（indicatorvariable）。指示变量为一整形变量，用来“指示”所指主变量的值或条件。n输入主变量可以利用指示变量赋空值，输出主变量可以利用指示变量检测出是否为空值，或者值是否被截断。n所有的主变量和指示变量都应在“BEGINDECLARESECTION”和“ENDDECLARESECTION”之间说明。在SQL语句中，主变量前加冒号（:）作为标识，以示区别
72、，指示变量前也要加冒号，且紧跟主变量。在SQL语句以外，则不必加冒号。游标n使用主变量并不能满足把面向集合的数据转换成面向记录的数据。引入游标的作用就是要把面向集合的数据在主语言中加以引用。n游标其实是数据缓冲区，存放SQL语句的执行结果。在程序中，可以通过主变量逐一获取记录。小结n由此可以看到，在嵌入式SQL中，SQL语句与主语言语句分工非常明确。SQL语句用来直接与数据库打交道，主语言语句用来控制程序流程以及对SQL语句的执行结果做进一步加工处理。n嵌入式SQL的工作原理是这样的：SQL语句用主变量从主语言中接收执行参数，操纵数据库；SQL语句的执行状态由DBMS送到SQLCA中；主语言程
73、序从SQLCA中取出状态信息，据此决定下一步操作；如果SQL语句从数据库中成功地检索出数据，则通过主变量传给主语言做进一步处理。SQL语言和主语言的不同数据处理方式通过游标来协调。n参见一段带有嵌入式SQL的C程序。嵌入式SQL示例1.2.EXECSQLINCLUDESQLCA;/定义SQL通信区3.EXECSQLBEGINDECLARESECTION;/说明主变量和指示变量4.CHARtitle_id(7);5.CHARtitle(81);6.INTroyaty;7.EXECSQLENDDECLARESECTION;8.9.main()10.EXECSQLDECLAREC1CURSORFOR
74、11.SELECTtit_id,tit,royFROMtitles;/定义游标12./*从titles表中查询tit_id，tit，roy*/13.EXECSQLOPENC1;/打开游标14.for(;)15.EXECSQLFETCHC1INTO:title_id,:title,:royalty;/当前行数据放入主变量，且游标指针向前进16.if(sqlca.sqlcodeSUCCESS)break;/由状态信息决定退出循环17.printf(“TitleID:%s,Royalty:%d”,title_id,royalty);18.printf(“Title:%s”,title);/打印结果1
75、9./endfor20.EXECSQLCLOSEC1;/关闭游标21.373不用游标的SQL语句说明性语句。如INCLUDE数据定义语句。如CREATETABLE数据控制语句。如GRANTv上述三种语句都不需要返回结果，不需要主变量，只需要加前缀EXECSQL就可以执行了。查 询 结 果 为 单 记 录 的 SELECT语 句 。 如 SELECTSUM(salary)FROMEMP;非CURRENT形式的UPDATE语句。如UPDATEEMPSETsalary=salary+100;非 CURRENT形 式 的 DELETE语 句 。 如 DELETE FROMEMP;vINSERT语句。I
76、NSERT语句中通常会使用主变量。说明性语句nEXECSQLINCLUDESQLCA;nEXECSQLBEGINDECLARESECTION;nEXECSQLENDDECLARESECTION;n其中后两句是成对出现的。数据定义语句n数据定义语句中不允许使用主变量。如：nEXECSQLDROPTABLE:table_name;n是错误的。数据控制语句nEXECSQLGRANTSELECTONTABLEStudentTOU1;查询结果为单记录的SELECT语句n这种SELECT语句一般使用INTO子句。其格式如下：nEXEC SQL SELECT ALL|DISTINCT ,nINTO,nFRO
77、M,nWHEREnGROUPBYHAVINGnORDERBYASC|DESC;注意1.INTO、WHERE子句、HAVING短语中均可使用主变量，但些主变量必须事先加以说明，并且引用时前面要加上冒号。2.返回记录中某些列可能为空值。如主变量后有指示变量，则其值为负值，但不禹该主变量作赋值操作，即其值保持SQL语句执行之前的原值。所以当发现指示变量值为负值时，不管主变量为何值，均应当认为主变量值为NULL。指示变量只能用于INTO子句中，并且也必须事先加以说明，引用时前面要加上冒号。3.如果数据库中没有满足条件的记录，即查询结果为空，则DBMS将SQLCODE的值置为100。4.如果查询结果实际
78、上并不是单条记录，而是多条记录，则程序出错，DBMS会在SQLCA中返回错误信息。n在有些关系数据库中，第4条不认为出错，而把多条记录中的第一条作为返回值赋给主变量。但为了确保程序的正确，最好避免出现这种情形，一般可以使用下面的方式：n由条件表达式限定返回值只有一行。（例如通过确定码值来唯一确定一元组。）n不使用GROUPBY子句的聚集函数应用。非CURRENT形式的UPDATE语句n例：nSageid=-1;nEXECSQLUPDATEStudentnSETSage=:Raise:SageidnWHERESdept=CS;n它等价于下面的语句：nEXECSQLUPDATEStudentnSE
79、TSage=NULLnWHERESdept=CS;非CURRENT形式的DELETE语句n例：学生退学，要求删除其选修的课程。nEXECSQLDELETEFROMSCnWHERESno=(SELECTSnoFROMStudentnWHERESname=:stdname);n它相当于：nEXECSQLDELETEFROMSCnWHERE:stdname=(SELECTSnamenFROMStudentnWHEREStudent.Sno=SC.Sno);n一般认为，前一条SQL语句更直接，更高效。INSERT语句n例：插入一带NULL值的元组。ngradeid=-1;nEXECSQLINSERTI
80、NTOnSC(Sno,Cno,Grade)nVALUES(:stdno,:couno,:gr:gradeid);n其中Grade字段中插入的是空值。374使用游标的SQL语句n查询结果为多条记录的SELECT语句。nCURRENT形式的UPDATE语句nCURRENT形式的DELETE语句。游标使用步骤n使用游标的步骤可以分为四个，即说明游标；打开游标；移动游标，取当前记录；关闭游标。n其中说明游标的格式：nEXECSQLDECLARECURSORFOR;n对于该语句，DBMS并不执行。n打开游标的格式为：nEXECSQLOPEN;n打开游标后，将执行游标定义时指定的SELECT语句，同时游标
81、指针指向第一条记录。n移动游标，取当前记录的格式为：nEXECSQLFETCHnINTO,n这一语句中，主变量应与游标定义时指定的SELECT语句中的列表达式一一对应，通常这一语句会在循环结构中使用。有些嵌入式语句中，还有其它格式的语句来移动游标指针，控制游标指针的移动方向和位移。n关闭游标的格式为：nEXECSQLCLOSE;n关闭游标语句执行后，将释放游标所占用的缓冲区。这时，游标可以被再次打开。示例1n例：查询某个系全体学生的信息。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中。nnEXECSQLINCLUDESQLCA;nEXECSQLBEGINDECLARESE
82、CTION;nn/*说明主变量deptname，HSno，HSname，HSsex，HSage等*/nnEXECSQLENDDECLARESECTION;nngets(deptname);/*为主变量deptname赋值*/nnEXECSQLDECLARESXCURSORFORnSELECTSno,Sname,Ssex,SagenFROMStudentnWHERESdept=:deptname;/*说明游标*/示例1续nEXECSQLOPENSX;/*打开游标*/nWHILE(1)/*用循环结构逐条处理结果集中的记录*/nnEXECSQLFETCHSXINTO:HSno,:HSname,:HS
83、sex,:HSage;n/*游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/nif(sqlca.sqlcodeSUCCESS)break;n/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/n/*由主语言语句进行进一步处理*/nnnEXECSQLCLOSESX;/*关闭游标*/n示例2n例：查询某些系全体学生的信息。nnEXECSQLINCLUDESQLCA;nEXECSQLBEGINDECLARESECTION;nn/*说明主变量deptname，HSno，HSname，HSsex，HSage等*/nnEXECSQLENDDECLARESECTION;nnEXEC
84、SQLDECLARESXCURSORFORnSELECTSno,Sname,Ssex,SagenFROMStudentnWHERESdept=:deptname;/*说明游标*/示例2续nWHILE(gets(deptname)!=NULL)/*接收主变量deptname的值*/nn/*下面开始处理deptname指定系的学生信息，每次循环中deptname可具有不同的值*/nEXECSQLOPENSX;/*打开游标*/nWHILE(1)/*用循环结构逐条处理结果集中的记录*/nEXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;n/*游标指针向前推进一
85、行，然后从结果集中取当前行，送相应主变量*/nif(sqlca.sqlcodeSUCCESS)break;n/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/n/*由主语言语句进行进一步处理*/nn/*内循环结束*/nEXECSQLCLOSESX;/*关闭游标*/n/*外循环结束*/n使用CURRENT形式的UPDATE、DELETE语句n非CURRENT形式的UPDATE、DELETE语句都属于集合操作，一次修改或删除所有满足条件的记录。而用CURRENT形式的UPDATE、DELETE语句，则只针对单个记录，它需要用带游标的SELECT语句查出所有满足条件的记录，再从中找出要
86、修改或删除的记录进行处理，其步骤如下：n1）用DECLARE语句说明游标，如果是为CURRENT形式的UPDATE语句作准备，则SELECT语句中要加nFORUPDATEOFn子句指明将来检索出的数据在指定列是可修改的。n2）用OPEN语句打开游标，把所有满足查询条件的记录从指定表取到缓冲区。n3）用FETCH语句推进游标指针，并把当前记录从缓冲区中取出来送到主变量。续n4）检索该记录是否是要修改或删除的记录。如果是，则用UPDATE语句或DELETE语句修改或删除该记录。这时UPDATE语句和DELETE语句中要用nWHERECURRENTOFn子句，表示悠扬或删除的是该游标中最近一次取出的
87、记录，即游标指针指向的记录。n第3和4步通常用在一个循环结构中，通过循环执行FETCH语句，逐条取出结果集中的行进行判断和处理。n5）处理完毕用CLOSE语句关闭游标，释放结果集占用的缓冲区和其它资源。示例1n例：查询某个系的全体学生信息（要查询的系名由主变量deptname指定），然后根据用户的要求修改其中某些记录的年龄字段值。nnEXECSQLINCLUDESQLCA;nEXECSQLBEGINDECLARESECTION;nn/*说明主变量deptname，HSno，HSname，HSsex，HSage，NEWAge等*/nnEXECSQLENDDECLARESECTION;nngets
88、(deptname);/*为主变量deptname赋值*/nnEXECSQLDECLARESXCURSORFORnSELECTSno,Sname,Ssex,SagenFROMStudentnWHERESdept=:deptnamenFORUPDATEOFSage;/*说明游标，指明可对Sage列进行修改操作*/示例1续nnEXECSQLOPENSX;/*打开游标*/nWHILE(1)/*用循环结构逐条处理结果集中的记录*/nnEXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;n/*游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/nif(s
89、qlca.sqlcodeSUCCESS)break;n/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/nnprintf(“%s,%s,%s,%d”,HSno,HSname,HSsex,HSage); /*显示该记录*/nnprintf(“UPDATEAGE?”);/*问用户是否要修改Sage字段*/nscanf(“%c”,&yn);示例1续nif(yn=yoryn=Y)/*用户输入“y”或“Y”，表示要修改*/nnprintf(“INPUTNEWAGE:”);nscanf(“%d”,&NEWAge);/*输入新的年龄值*/nnEXECSQLUPDATEStudentnSETSa
90、ge=:NEWAgenWHERECURRENTOFSX;/*修改当前记录的年龄字段*/nnnnEXECSQLCLOSESX;/*关闭游标*/n示例2n例：查询某个系全体学生的信息（要查询的系名由主变量deptname指定），然后根据用户的要求修改删除其中某些记录。nnEXECSQLINCLUDESQLCA;nEXECSQLBEGINDECLARESECTION;nn/*说明主变量deptname，HSno，HSname，HSsex，HSage等*/nnEXECSQLENDDECLARESECTION;nngets(deptname);/*为主变量deptname赋值*/nnEXECSQLDEC
91、LARESXCURSORFORnSELECTSno,Sname,Ssex,SagenFROMStudentnWHERESdept=:deptname;/*说明游标*/示例2续nEXECSQLOPENSX;/*打开游标*/nWHILE(1)/*用循环结构逐条处理结果集中的记录*/nEXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;n/*游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/nif(sqlca.sqlcodeSUCCESS)break;n/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/nnprintf(“%s,%s,
92、%s,%d”,HSno,HSname,HSsex,HSage);/*显示记录*/nnprintf(“DELETE?”);/*询问用户是否要进行删除*/示例2续nscanf(“%c”,&yn);nnif(yn=yoryn=Y)nEXECSQLDELETEFROMStudentnWHERECURRENTOFSX;/*删除当前记录*/nnn/*内循环结束*/nEXECSQLCLOSESX;/*关闭游标*/n/*外循环结束*/nv当游标定义中的SELECT语句带有UNION或ORDERBY子句时，或该SELECT语句相当于一个不可更新的视图时，不能使用CURRENT形式的UPDATE和DELETE语句
93、。375动态SQL简介n上述例子中，WHERE子句、HAVING短语可以输入某些变量值。特点是，主变量的个数、数据类型在预编译时都是确定的。只有主变量的值是在运行中动态输入的。这类SQL语句称为静态SQL语句。n动态SQL语句的技术主要应用在：nSQL语句正文n主变量个数n主变量的数据类型nSQL语句中引用的数据库对象（如列、索引、基表、视图等）n主程序运行过程中临时“组装”SQL语句：n语句可变，即允许用户在程序运行时，临时输入完整的SQL语句，如EXECUTEIMMEDIATE语句可完成该功能。n条件可变。n数据库对象、查询条件均可变，如PREPARE-EXECUTE-DESCRIBE语句可完成该功能。
