数据库技术 第三章 关系数据库标准语言SQL 中国科学技术大学网络学院 阚卫华

第三章 关系数据库标准语言SQL 3.1 SQL概述 3.2 数据定义 3.3 查询 3.4 数据更新 3.5 视图 3.6 数据控制 3.8 小结 2018年11月17日星期六

3.4.1 插入数据 3.4 数 据 更 新 3.4.1 插入数据 3.4.2 修改数据 3.4.3 删除数据 2018年11月17日星期六

3.4.1 插入数据 两种插入数据方式 插入单个元组 插入子查询结果 2018年11月17日星期六

一、 插入单个元组 语句格式: INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)] VALUES (<常量1> [，<常量2>] … ) 功能: 将新元组插入指定表中。其中，新记录属性列1的值为常量1，属性列2的值为常量2， … 。 INTO 子句中没有出现的属性列，新记录在这些列上将取空值。 注意：在表定义时说明了NOT NULL的属性列不能取空值，否则会出错。 　　　 如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。 2018年11月17日星期六

插入单个元组（续） 　例1(P.117) 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 INSERT INTO Student VALUES ('95020'，'陈冬'，'男'，'IS'，18)； 2018年11月17日星期六

插入单个元组（续） 例2(P.117) 插入一条选课记录( '95020'，'1 ')。 INSERT INTO SC(Sno，Cno) VALUES (' 95020 '，' 1 ')； 新插入的记录在Grade列上取空值。 2018年11月17日星期六

插入单个元组（续） INTO子句 指定要插入数据的表名及属性列； 属性列的顺序可与表定义中的顺序不一致； 没有指定属性列时：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致。 指定部分属性列：插入的元组在其余属性列上取空值。 VALUES子句 提供的值必须与INTO子句匹配：包括值的个数与值的类型。 2018年11月17日星期六

二、 插入子查询结果 语句格式 INSERT INTO <表名> [(<属性列1> [，<属性列2>… )] 子查询； 功能 将子查询结果插入指定表中。 　　 注:子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件(如3.3.3所述)，也可以嵌套在INSERT语句中，用以生成要插入的批量数据。 2018年11月17日星期六

插入子查询结果（续） 例3(P.118)对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表(首先在数据库中建立一个新表,其中一列存放系名,另一列存放相应的学生平均年龄): CREATE TABLE Deptage (Sdept CHAR(15) /* 系名 */ Avgage SMALLINT)； /* 学生平均年龄 */ 2018年11月17日星期六

插入子查询结果（续） 第二步：插入数据(对Student表按系分组求平均年龄,再把系名和平均年龄存入新表中) INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept； 2018年11月17日星期六

插入子查询结果（续） INTO子句(与插入单条元组类似) 指定要插入数据的表名及属性列； 属性列的顺序可与表定义中的顺序不一致； 没有指定属性列：表示要插入的是一条完整的元组； 指定部分属性列：插入的元组在其余属性列上取空值。 子查询 SELECT子句目标列必须与INTO子句匹配： 值的个数； 值的类型。 2018年11月17日星期六

插入子查询结果（续） DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则： 实体完整性； 参照完整性； 用户定义的完整性。 对于有NOT NULL约束的属性列是否提供了非空值； 对于有UNIQUE约束的属性列是否提供了非重复值； 对于有值域约束的属性列所提供的属性值是否在值域范围内。 2018年11月17日星期六

3.4.2 修改数据 语句格式： UPDATE <表名> 3.4.2 修改数据 语句格式： UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>]； 功能：修改指定表中满足WHERE子句条件的元组。其中， SET子句给出<表达式>的值，用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。 2018年11月17日星期六

修改数据（续） 三种修改方式： 修改某一个元组的值； 修改多个元组的值； 带子查询的修改语句。 2018年11月17日星期六

一、 修改某一个元组的值 例4(P.118)将学生95001的年龄改为22岁。 UPDATE Student SET Sage=22 WHERE Sno=' 95001 '； 2018年11月17日星期六

二、 修改多个元组的值 例5(P.119) 将所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1； 2018年11月17日星期六

修改多个元组的值(续) 补例 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1 补例 将信息系所有学生的年龄增加1岁。 UPDATE Student SET Sage= Sage+1 WHERE Sdept=' IS '； 2018年11月17日星期六

三、 带子查询的修改语句 例6(P.119) 将计算机科学系全体学生的成绩置零。 UPDATE SC SET Grade=0 WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)； 注:子查询也可以嵌套在UPDATE语句中,用以构造修改条件. 2018年11月17日星期六

修改数据 小结（续） SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组 修改数据 小结（续） 　SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组 2018年11月17日星期六

修改数据 小结（续） DBMS在执行修改语句时会检查修改操作 是否破坏表上已定义的完整性规则: 实体完整性; 主码不允许修改; 用户定义的完整性. NOT NULL约束; UNIQUE约束; 值域约束. 2018年11月17日星期六

3.4.3 删除数据 删除语句的一般格式: DELETE FROM <表名> [WHERE <条件>]； 功能:删除指定表中满足WHERE子句条件的元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典中（ DELETE语句删除的是表中的数据，而不是关于表的定义）。 WHERE子句： 指定要删除的元组； 缺省时表示要修改表中的所有元组。 2018年11月17日星期六

删除数据（续） 三种删除方式： 删除某一个元组的值； 删除多个元组的值； 带子查询的删除语句。 2018年11月17日星期六

一、删除某一个元组的值 例7 (P119) 删除学号为95019的学生记录。 DELETE FROM Student WHERE Sno='95019'； 2018年11月17日星期六

二、 删除多个元组的值 例8 (P120)删除所有的学生选课记录。 DELETE FROM SC； 补例 删除2号课程的所有选课记录。 WHERE Cno='2'； 注:这条DELETE语句将使SC成为空表,它删除了SC的所有元组. 2018年11月17日星期六

三、 带子查询的删除语句 例9 (P.120) 删除计算机科学系所有学生的选课记录。 DELETE FROM SC WHERE 'CS' = (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)； 注:子查询同样也可以嵌套在DELETE语句中,用以构造执行删除操作的条件. 2018年11月17日星期六

删除数据(续) DBMS在执行插入语句时会检查所插元组 是否破坏表上已定义的完整性规则: 参照完整性 不允许删除; 级联删除. 2018年11月17日星期六

四、更新数据与数据一致性 DBMS在执行插入、删除、更新语句时，必须保证数据库一致性； 必须有事务的概念和原子性； 完整性检查和保证。 　必须有事务的概念和原子性； 　完整性检查和保证。 2018年11月17日星期六

四、更新数据与数据一致性（续） 增、删、改操作只能对一个表进行操作。这样会带来一个问题。例如，以前的例子中，95019学生被删除后，有关其选课信息也应同时删除，而这只能通过两条语句进行。 　 第1条语句删除95019学生： 　　DELETE FROM Student WHERE Sno=‘95019’ ；　 2018年11月17日星期六

四、更新数据与数据一致性（续） 第2条语句删除95019学生的选课记录： DELETE FROM SC WHERE Sno= ' 95019 ' ； 　　　在执行了第1条DELETE语句之后，数据库中的数据已处于不一致状态，因为这时实际上已没有学号为95019的学生了，但SC表中仍然记录着关于95019学生的选课信息，即数据的参照完整性受到破坏。只有执行了第2条DELETE语句之后，数据才重新处于一致状态。 2018年11月17日星期六

四、更新数据与数据一致性（续） 　　但如果执行了第1条DELETE语句之后，机器突然出现故障，无法再继续执行第2条UPDATE语句，则数据库中的数据将永远处于不一致状态。因此，必须保证这两条语句要么都做，要么都不做。为解决这一问题，数据库系统通常都引入了事务的概念。 　　　在2.3节“关系的完整性”中已讲到,实体完整性和参照完整性是关系模型必须满足的 　完整性约束条件,应该由关系系统自动支持。 2018年11月17日星期六

四、更新数据与数据一致性（续） 　　　当向参照表中插入元组，如往SC中插入选课记录(`95020`,`1`)，系统将自动地检查被参照表(Student和Course) 中是否存在相应的元组( 95020号学生的记录，1号课程的记录) ，如果都存在则操作成功，否则操作失败。 　　　当删除主表中的元组(如学生95019 )，可以有以下的策略：　 2018年11月17日星期六

四、更新数据与数据一致性（续） (1) 系统自动地删除参照表(如SC)中相应的元组(学生95019的选课记录)。 　(2)　系统检查参照表中是否存在相应的元组，如果存在，则操作失败。 注：各种数据库产品提供了不同的实现策略。 2018年11月17日星期六

3.5 视 图 视图的特点： 视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。 3.5 视 图 视图的特点： 　　　视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。 　　　数据库中只存放视图的定义，而不存放视图对应的数据,这些数据仍存放在原来的基本表中(不会出现数据冗余)。 2018年11月17日星期六

3.5 视 图（续） 　　基表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就象一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。 　　　视图一经定义，就可以和基本表一样被查询、被删除，也可以在一个视图之上再定义新的视图，但对视图的更新(增加、删除、修改)操作，则有一定的限制。 2018年11月17日星期六

基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图 2018年11月17日星期六

3.5.1定义视图 语句格式 一、 建立视图 CREATE VIEW <视图名> [(<列名> [，<列名>]…)] AS <子查询> [WITH CHECK OPTION]； 2018年11月17日星期六

建立视图（续） 其中子查询可以是任意复杂的SELECT语句，但不允许含有ORDER BY 子句和DISTINCT短语。 　　WITH CHECK OPTION 表示对视图进行UPDATE ，INSERTT和DELETE操作时，要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。 2018年11月17日星期六

建立视图（续） 组成视图的属性列名：全部省略或全部指定。 省略：则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。 明确指定视图的所有列名： (1)某个目标列不是单纯的属性名，而是集函数或列表达式； (2) 目标列为 *； 2018年11月17日星期六

建立视图（续） (3) 多表连接时选出了几个同名列作为视图的字段； (4) 需要在视图中为某个列启用新的更合适的名字。 2018年11月17日星期六

建立视图（续） DBMS执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中 　的SELECT语句。只是在对视图查询时，才按视图的定义从基本表中将数据查出。 2018年11月17日星期六

行列子集视图 例1(P.122) 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS'； 从单个基本表导出； 只是去掉了基本表的某些行和某些列； 保留了码。 注：本例中，省略了视图IS_Student的列名，隐含了由子查询中SELECT子句中的三个列名组成。 2018年11月17日星期六

建立视图（续） WITH CHECK OPTION 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）。 　　　透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）。 2018年11月17日星期六

WITH CHECK OPTION的视图 例2(P.122) 建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS' WITH CHECK OPTION； 注:由于在定义IS_Student视图时加上了WITH CHECK OPTION子句，以后对该视图进行插入、修改和删除操作时，DBMS会自动加上Sdept=`IS`的条件。 2018年11月17日星期六

对IS_Student视图的更新操作 修改操作：DBMS自动加上Sdept= ‘IS’的条件； 如果不是，则拒绝该插入操作； 如果没有提供Sdept属性值，则自动定义Sdept为'IS'。 2018年11月17日星期六

对IS_Student视图的更新操作 　　　若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，我们称这类视图为行列子集视图。 IS_Student视图就是一个行列子集视图。 　　　视图不仅可以建立在单个基本表上，也可以建立在单个基本表上。 2018年11月17日星期六

基于多个基表的视图 例3(p.123)建立信息系选修了1号课程的学生视图。CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= 'IS' AND Student.Sno=SC.Sno AND SC.Cno= '1'； 说明 2018年11月17日星期六

基于多个基表的视图（续） 　　　说明：由于视图IS_S1的属性列中包含了Student表与SC表的同列名Sno，所以必须在视图名后面明确说明视图的各个属性列　名。 　　　视图不仅可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。 2018年11月17日星期六

基于视图的视图 例4(P.123) 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade>=90； 注：这里的视图IS_S2就是建立在IS_S1之上的。 2018年11月17日星期六

基于视图的视图（续） 　　　定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在，我们称它们为虚拟列。带虚拟列的视图也称为带表达式的视图。见下例。 2018年11月17日星期六

带表达式的视图 例5(P.123) 定义一个反映学生出生年份的视图。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS　SELECT Sno，Sname，1996-Sage 　　　 FROM Student； 2018年11月17日星期六

带表达式的视图（续） BT_S视图是一个带表达式的视图，视图中的出生年份值是通过计算得到的。 　　　　设置一些派生属性列, 也称为虚拟列－Sbirth 带表达式的视图必须明确定义组成视图的各个属性列名。 　　　BT_S视图是一个带表达式的视图，视图中的出生年份值是通过计算得到的。 　　　　还可以用带集函数和GROUP BY子句的查询来定义视图，这种视图称为分组视图。见下例： 2018年11月17日星期六

建立分组视图 例7(P.124) 将学生的学号及他的平均成绩定义为一个视图。 假设SC表中“成绩”列Grade为数字型 CREAT VIEW S_G(Sno，Gavg) 　　　　　AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； 注:由于AS子句中SELECT语句的目标列平均成绩是通过作用集函数得到的,所以CREATE VIEW中必须明确定义组成S_G视图的各个属性列名, S_G是一个分组视图. 2018年11月17日星期六

建立视图（续） 一类不易扩充的视图 以 SELECT * 方式创建的视图可扩充性差，应尽可能避免。 2018年11月17日星期六

建立视图（续） 例7(P.124)将Student表中所有女生记录定义为一个视图。 CREATE VIEW F_Student1(stdnum，name，sex，age，dept) 　　AS SELECT * 　　 FROM Student 　　 WHERE Ssex='女'； 　　　这里视图F_Student1是由子查询“SELECT *”建立的。如果以后修改基表Student的结构后，则Student表与F_Student1视图的映象关系被破坏，导致该视图不能正确工作。 2018年11月17日星期六

建立视图（续） 为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。 CREATE VIEWF_Student2 (stdnum，name，sex，age，dept) 　　AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex='女'； 　　为基表Student增加属性列不会破坏Student表与F_Student2视图的映象关系。 2018年11月17日星期六

常见的视图形式（小结） ＊行列子集视图 WITH CHECK OPTION的视图 基于多个基表的视图 基于视图的视图 带表达式的视图 分组视图 2018年11月17日星期六

二、 删除视图 DROP VIEW <视图名>； 该语句删除从数据字典中指定的视图定义； 由该视图导出的其他视图定义仍在数据字典中，(但已不能使用，必须显式删除)。 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW删除。 2018年11月17日星期六

删除视图(续） 例8 (P.124) 删除视图IS_S1 DROP VIEW IS_S1； 　注：　执行该语句后， IS_S1视图的定义将从数据字典中删除。由IS_S1视图导出IS_S2视图的定义虽然仍在数据字典中，但是该视图已无法使用了，因此，应该同时删除。 2018年11月17日星期六

3.5.2 查询视图 从用户角度：查询视图与查询基本表相同。 DBMS实现视图查询的方法： 3.5.2 查询视图 　从用户角度：查询视图与查询基本表相同。 　DBMS实现视图查询的方法： 实体化视图（View Materialization） 有效性检查：检查所查询的视图是否存在； 执行视图定义，将视图临时实体化，生成临时表； 查询视图转换为查询临时表； 查询完毕删除被实体化的视图(临时表)。 2018年11月17日星期六

查询视图（续） 视图消解法（View Resolution） 进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义； 把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询； 执行修正后的查询。 2018年11月17日星期六

查询视图（续） 例1(P.125) 在信息系学生的视图中找出年龄小于20岁的学生。 SELECT Sno，Sage FROM IS_Student WHERE Sage<20； IS_Student视图的定义 (视图定义例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage 　　 FROM Student WHERE Sdept= 'IS ' ； 2018年11月17日星期六

查询视图（续） 视图实体化法 视图消解法 本例转换后的查询语句为： SELECT Sno，Sage FROM Student WHERE Sdept= 'IS' AND Sage<20； 2018年11月17日星期六

查询视图（续） 例2(P.125)查询信息系选修了1号课程的学生. SELECT Sno，Sname FROM IS_Student，SC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1'； 注:本查询涉及虚表CS_Student和基本表SC,通过这两个表的连接来完成用户请求. 2018年11月17日星期六

查询视图（续） 视图消解法的局限 　　　在一般情况下，视图查询的转换是直接了当的。但在有些情况下，视图消解法不能生成正确查询。采用视图消解法的DBMS会限制这类查询(如下页例3)。 2018年11月17日星期六

查询视图（续） 例3(P.125)在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩: SELECT * FROM S_G WHERE Gavg>=90； S_G视图定义为： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； 2018年11月17日星期六

查询转换 正确： 错误： SELECT Sno，AVG(Grade) FROM SC WHERE AVG(Grade)>=90 GROUP BY Sno； 正确： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90； 2018年11月17日星期六

3.5.3 更新视图 用户角度：更新视图与更新基本表相同 DBMS实现视图更新的方法 3.5.3 更新视图 用户角度：更新视图与更新基本表相同 DBMS实现视图更新的方法 视图实体化法（View Materialization） 视图消解法（View Resolution） 　　　更新视图是指通过视图来插入(INSERT)、删除(DELETE)和修改(UPDATE)数据.由于视图是实际存储数据的虚表,因此,对视图的更新,最终要转换为对基本表的更新。 2018年11月17日星期六

更新视图（续） 　　　为防止用户通过视图对不属于视图范围内的基本表数据进行更新（增加、删除、修改）时，可在定义视图时加上指定的WITH CHECK OPTION子句。这样，在视图上增加、删除、修改时， DBMS会进行检查视图定义中的条件，若不满足条件，则拒绝执行该操作。 2018年11月17日星期六

更新视图（续） 例1（P.126） 将信息系学生视图IS_Student中学号95002 的学生姓名改为“刘辰”。 UPDATE IS_Student SET Sname= '刘辰' WHERE Sno= '95002'； 转换后的语句为： UPDATE Student SET Sname= '刘辰' WHERE Sno= '95002' AND Sdept= 'IS'； 2018年11月17日星期六

更新视图（续） 例2 （P.126~127） 向信息系学生视图IS_S中插入一个新的学生记录：95029，赵新，年龄为20岁. INSERT INTO IS_Student VALUES('95029'，'赵新'，20)； 转换为对基本表的更新： INTO Student(Sno，Sname，Sage，Sdept) VALUES('95029'，'赵新'，20，'IS' )； 注: 这里系统自动将系名'IS'放入VALUES子句中. 2018年11月17日星期六

更新视图（续） 例3 （P. 127）删除计算机系学生视图CS_S中学号为95029的记录. DELETE FROM IS_Student WHERE Sno= '95029'； 转换为对基本表的更新： FROM Student WHERE Sno= '95029' AND Sdept= 'IS'； 2018年11月17日星期六

更新视图的限制 　　　在关系数据库中，有一些视图是不可更新的，因为对这些视图的更新，并不能唯一地、有意义地转换成对相应基本表的更新(对两类方法均如此)。 2018年11月17日星期六

更新视图的限制（续） 例：视图S_G为不可更新视图。 CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； 2018年11月17日星期六

更新视图（续） 对于如下更新语句： UPDATE S_G SET Gavg=90 WHERE Sno= '95001'； 　　　无论实体化法还是消解法都无法将其转换成对基本表SC的更新。因为系统无法修改各科成绩，以使平均成绩为90。 2018年11月17日星期六

视图的可更新性 理论上可更新 理论上不可更新 允许更新 不允许更新 不允许更新 理论上可更新 理论上不可更新 允许更新 不允许更新 不允许更新 注：一般地，行列子集视图是可更新的。除此之外，还有些视图理论上是可更新的，但它们确切特征还是尚待研究的课题。还有些视图从理论上是不可更新的。 　　目前，各个关系数据库系统一般都只允许对行列子集视图进行更新，而且各个系统对视图的更新还有待进一步的规定，由于各系统实现方法上的差异，这些规定也不尽相同。 2018年11月17日星期六

实际系统对视图更新的限制 允许对行列子集视图进行更新； 对其他类型视图的更新不同系统有不同限制。 DB2对视图更新的限制： (1) 若视图是由两个以上基本表导出的，则此视图不允许更新。 (2) 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。 2018年11月17日星期六

更新视图（续） (3) 若视图的字段来自集函数，则此视图不允许更新。 (4) 若视图定义中含有GROUP BY子句，则此视图不允许更新。 　　(3) 若视图的字段来自集函数，则此视图不允许更新。 (4) 若视图定义中含有GROUP BY子句，则此视图不允许更新。 (5) 若视图定义中含有DISTINCT短语，则此视图不允许更新。 2018年11月17日星期六

更新视图（续） (6) 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。 例如：将视图SC中成绩在平均成绩之上的元组定义成一个视图GOOD_SC(修课成绩在平均成绩之上的元组)： 2018年11月17日星期六

更新视图（续） AS SELECT Sno，Cno，Grade FROM SC WHERE Grade > 例：视图GOOD_SC(修课成绩在平均成绩之上的元组) ：CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC)； 　　　该例中，导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以，视图GOOD_SC是不允许更新的。 2018年11月17日星期六

更新视图（续） (7) 一个不允许更新的视图上定义的视图也不允许更新。 　　　应该指出的是，不可更新的视图与不允许更新的视图是两个不同的概念。前者指理论上已证明它是不可更新的视图；后者指实际系统中不支持其更新，但它本身有可能是可更新的视图。 2018年11月17日星期六

3.5.4 视图的作用 1. 视图能够简化用户的操作 　　　视图机制使用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作。当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作。例如， 2018年11月17日星期六

视图能够简化用户的操作（续） 　　那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐蔽起来了。换言之，用户所做的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。 2018年11月17日星期六

2. 视图使用户能以多种角度看待同一数据 　　　视图机制能使不同用户以不同的方式看待同一数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常重要的。 2018年11月17日星期六

3.视图对重构数据库提供了一定程度的逻辑独立性 　　　 （回顾）物理独立性与逻辑独立性的概念：数据的物理独立性是指用户和用户程序不依赖于数据库的物理结构；数据的逻辑独立性是指当数据库重构造时（例如增加新的关系或对原有关系增加新的字段等），用户和用户程序不会受到影响（层次数据库和网状数据库一般能较好地支持数据的物理独立性，而对于逻辑独立性则不能完全地支持）。 　　　视图在一定程度上保证了数据的逻辑独立性。 2018年11月17日星期六

3.视图对重构数据库提供了一定程度的逻辑独立性 　　　由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。 　　例：数据库逻辑结构发生改变 学生关系Student(Sno，Sname，Ssex，Sage，Sdept) “垂直”地分成两个基本表： SX(Sno，Sname，Sage) SY(Sno，Ssex，Sdept) 2018年11月17日星期六

3.视图对重构数据库提供了一定程度的逻辑独立性(续) 通过建立一个视图Student： CREATE VIEW Student(Sno，Sname，Ssex，Sage，Sdept) AS SELECT SX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.Sdept FROM SX，SY WHERE SX.Sno=SY.Sno； 　　使用户的外模式保持不变，从而对原Student表的 查询程序不必修改。 注：这样尽管数据库的逻辑结构改变了，但应用程序不必修改。因为新建立的视图定义了用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。 2018年11月17日星期六

4. 视图能够对机密数据提供安全保护 对不同用户定义不同视图，使每个用户只能看到他有权看到的数据； 　　　对不同用户定义不同视图，使每个用户只能看到他有权看到的数据； 　　　通过WITH CHECK OPTION对关键数据定义操作时间限制。 2018年11月17日星期六

建立视图（续） CREATE VIEW IS_SC 　　补例　建立1号课程的选课视图，并要求透过该视图进行的更新操作只涉及1号课程，同时对该视图的任何操作只能在工作时间进行。 CREATE VIEW IS_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Cno= '1' AND TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 AND TO_CHAR(SYSDATE,'D') BETWEEN 2 AND 6 WITH CHECK OPTION; 2018年11月17日星期六

3.6 数据控制 　　概述 3.6.1 授权 3.6.2 收回权限 小结 2018年11月17日星期六

概述 　　　由DBMS提供统一的数据控制功能是数据库系统的特点之一。SQL中数据控制功能包括事务管理功能，即数据库的恢复、并发控制；数据库的安全性控制和完整性控制。　　　SQL语言定义完整性约束条件的功能主要体现在CREATE TABLE语句和ALTER TABLE中，可以在这些语句中定义码、取值唯一的列、不允许空值的列、外码（参照完整性）以及其它一些约束条件。 2018年11月17日星期六

概述(续) SQL语言的数据控制功能 SQL语言也提供了并发控制及恢复的功能，支持事务、提交、回滚等概念； 2018年11月17日星期六

1. 完整性 数据库的完整性是指数据库中数据的正确性与相容性。 SQL语言定义完整性约束条件 CREATE TABLE语句 　　数据库的完整性是指数据库中数据的正确性与相容性。 SQL语言定义完整性约束条件 CREATE TABLE语句 ALTER TABLE语句 码 取值唯一的列 参照完整性 其他约束条件 2018年11月17日星期六

2. 并发控制 并发控制: 当多个用户并发地对数据库进行操作时，对他们加以控制、协调，以保证并发操作正确执行，保持数据库的一致性。 SQL语言并发控制能力：提供事务、事务开始、事务结束、提交等概念。 2018年11月17日星期六

3. 恢复 恢复: 当发生各种类型的故障导致数据库处于不一致状态时，将数据库恢复到一致状态的功能。 SQL语言恢复功能： 　　　恢复: 当发生各种类型的故障导致数据库处于不一致状态时，将数据库恢复到一致状态的功能。 SQL语言恢复功能： 提供事务回滚、重做（UNDO、REDO）等概念。 2018年11月17日星期六

4. 安全性 存取控制是保证数据安全性的主要措施：控制用户只能存取他有权存取的数据；规定不同用户对于不同数据对象所允许执行的操作。 　　　安全性：保护数据库，防止不合法的使用所造成的数据泄露和破坏。 　　　存取控制是保证数据安全性的主要措施：控制用户只能存取他有权存取的数据；规定不同用户对于不同数据对象所允许执行的操作。 2018年11月17日星期六

DBMS实现数据安全性保护的过程 （1）用户或DBA把授权决定告知系统；这是由SQL的GRANT和REVOKE语句来完成的。 　　（2）DBMS把授权的结果存入数据字典。　（3）当用户提出操作请求时，DBMS根据授权情况进行检查，以决定是否执行操作请求。 2018年11月17日星期六

3.6.1 授权 GRANT语句的一般格式： GRANT <权限>[,<权限>]... [ON <对象类型> <对象名>] TO <用户>[,<用户>]... [WITH GRANT OPTION]； 语义：将对指定操作对象的指定操作权限授予指定的用户。 2018年11月17日星期六

(1) 操作权限 对象 对象类型 操 作 权 限 属性列 TABLE SELECT,INSERT,UPDATE， DELETE,ALL PRIVIEGES 视图 基本表 DELETE， ALTER, INDEX, ALL PRIVIEGES 数据库 DATABASE CREATETAB 表3.4 不同对象类型允许的操作权限 2018年11月17日星期六

(1) 操作权限(续) 对属性列和视图的操作权限有: 查询(SELECT)、 插入(INSERT) 、修改 (UPDATE) 、删除 (DELETE) 以及这四种权限的总和(ALL PRIVILEGES) 。 　　　对基本表的操作权限有: 　　　查询(SELECT)、 插入(INSERT) 、修改 (UPDATE) 、删除 (DELETE) 、修改表(ALTER)和建立索引(INDEX)以及这六种权限的总和(ALL PRIVILEGES) 。 2018年11月17日星期六

(2) 用户的权限 　　　对数据库可以有建立表（CREATETAB）的权限，该权限属于DBA，可由DBA授予普通用户，普通用户拥有此权后可以建立基本表，基本表的(或视图的)属主拥有对该表或视图的一切操作权限。 　　　接受权限的用户可以是一个或多个具体用户，也可以是 PUBLIC（即：全体用户）。 2018年11月17日星期六

(3) WITH GRANT OPTION子句 　　　如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其它的用户。如果没有指定WITH GRANT OPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。 2018年11月17日星期六

例题 例1(P.131) 把查询Student表权限授给用户U1 GRANT SELECT ON TABLE Student TO U1; 2018年11月17日星期六

例题（续） 例2 (P.131) 把对Student表和Course表的全部操作权限授予用户U2和U3。 GRANT ALL PRIVILIGES 　 ON TABLE Student, Course TO U2, U3； 注：该例说明了GRANT语句可以一次向多个用户授权；还可以一次传播多个同类对象的权限。 2018年11月17日星期六

例题（续） GRANT SELECT ON TABLE SC TO PUBLIC； 例3 (P.131) 把对表SC的查询权限授予所有用户. 2018年11月17日星期六

例题（续） 例4 (P.131) 把查询Student表和修改学生学号的权限授给用户U4。 ON TABLE Student TO U4； GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4； 注：这里实际上要授予U4的是，对基本表Student的 SELECT权限和对属性列Sno 的UPDATE权限。授予关于属性列的权限时，必须明确指出相应属性列名。 2018年11月17日星期六

例题（续） 例5 (P.131) 把对表SC的INSERT权限授予U5用户，并允许(他再将)此权限授予其他用户. GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION； 说明 2018年11月17日星期六

传播权限 执行例5后，U5不仅拥有了对表SC的INSERT权限，还可以传播此权限给其他用户(如U6)： GRANT INSERT ON TABLE SC TO U6 WITH GRANT OPTION； 同样，U6还可以将此权限授予U7： GRANT INSERT ON TABLE SC TO U7； 但U7不能再传播此权限[因为U6未给U7传播的权限]。 2018年11月17日星期六

例题（续） 例6 (P.132) DBA把在数据库S_C中建立表的权限授予用户U8。 GRANT CREATETAB ON DATABASE S_C TO U8； 2018年11月17日星期六

例题（续）小结 　　　由上面的例子可以看到，GRANT语句可以一次向一个用户授权（例1），这是最简单的一种授权操作；也可以一次向多个用户授权（例2、例3等）；还可以一次传播多个同类对象的权限（例2）；甚至一次可以完成对基本表、视图和属性列这些不同对象的权限（例4）；但授予关于DATEBASE的权限必须与授予关于TABLE的权限分开，因为对象类型不同。 2018年11月17日星期六

3.6 数据控制 概述 3.6.1 授权 3.6.2 收回权限 小结 2018年11月17日星期六

3.6.2 收回权限 授予的权限可以由DBA或其他授权者用REVOKE语句收回。 REVOKE语句的一般格式为： 3.6.2 收回权限 　　授予的权限可以由DBA或其他授权者用REVOKE语句收回。 REVOKE语句的一般格式为： REVOKE <权限>[,<权限>]... [ON <对象类型> <对象名>] FROM <用户>[,<用户>]…； 功能：从指定用户那里收回对指定对象的指定权限。 2018年11月17日星期六

例题 REVOKE UPDATE(Sno) ON TABLE Student FROM U4； 例7（P.132） 把用户U4修改学生学号的权限收回。 REVOKE UPDATE(Sno) ON TABLE Student FROM U4； 2018年11月17日星期六

例题（续） REVOKE SELECT ON TABLE SC FROM PUBLIC； 例8(P.132) 收回所有用户对表SC的查询权限. REVOKE SELECT ON TABLE SC FROM PUBLIC； 2018年11月17日星期六

例题（续） REVOKE INSERT ON TABLE SC FROM U5； 例9(P.132)把用户U5对SC表的INSERT权限收回. REVOKE INSERT ON TABLE SC FROM U5； 注: 在例5中,U5将对SC表的INSERT权限授予了U6,而U6又将其权限授予了U7.执行例9的REVOKE语句后,DBMS在收回U5对SC表的INSERT权限的同时,还会自动收回U6和U7对SC表的INSERT权限,即收回权限的操作会级联下去的.但如果U6或U7还从其他用户处获得对SC表的INSERT权限,则他们仍具有此权限,系统只收回直接或间接从U5处获得的权限. 2018年11月17日星期六

权限的级联回收(小结) * 系统将收回直接或间接从U5处获得的对SC表的INSERT权限: →U5→ U6→ U7 收回U5、U6、U7获得的对SC表的INSERT 权限: ←U5← U6← U7 2018年11月17日星期六

小结:SQL灵活的授权机制 DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。 　用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户； 被授权的用户如果有“继续授权”的许可，他还可以把获得的权限再授予其他用户。 所有授予出去的权力在必要时又都可以用REVOKE语句收回。 2018年11月17日星期六

这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充。 交互式 嵌入式 为什么要引入嵌入式SQL SQL语言是非过程性语言 事务处理应用需要高级语言 　　　　　这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充。 2018年11月17日星期六

嵌 入 式 SQL（续） 　　　以上讨论的SQL语言是作为独立语言在终端交互方式下使用的。这是面向集合的描述性语言，是非过程性的。即大多数语句都是独立执行，与上下文无关的。而许多事务处理应用都是过程性的，需要根据不同的条件来执行不同的任务，因此，单纯用SQL语言是很难实现这类应用的。 　　　为了解决这一问题， SQL语言提供了另一种使用方式：将SQL语言嵌入到某种 2018年11月17日星期六

嵌 入 式 SQL（续） 　　高级语言中使用，利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足。这种方式下使用的SQL语言称为嵌入式SQL（Embedded SQL），而嵌入SQL的高级语言称为主语言或宿主语言。 　　　前面已经讲到，SQL的特点之一是，在两种不同的使用方式下， SQL语言的语法结构基本上是一致的（当然，细节上会有许多差别，在程序设计的环境下，SQL语句要做某些必要的扩充）。 2018年11月17日星期六

3.7.1嵌入式SQL的一般形式 　　　为了区分SQL语句与主语言语句，所有SQL语句都必须加 前缀：EXEC SQL。 SQL语句的 结束标志则随主语言的不同而不同。 以C为主语言的嵌入式SQL语句的一般形式 EXEC SQL <SQL语句>； 例如：一条交互形式的SQL语句：DROP TABLE Student； 嵌入到C程序中，应写作： 　　　EXEC SQL DROP TABLE Student； 2018年11月17日星期六

DBMS处理宿主型数据库语言SQL 的方法 　　　对宿主型数据库语言SQL，DBMS可采用两种方法处理，一种是预编译，另一种是修改和扩充主语言，使之能处理SQL语句。目前采用较多的是预编译的方法。 2018年11月17日星期六

预编译 1．由DBMS的预处理程序对源程序进行扫描，识别出SQL语句； 2．把它们转换成主语言调用语句，以使主语言编译程序能识别它； 3．最后由主语言的编译程序将整个源程序编译成目标码。 2018年11月17日星期六

嵌入SQL语句的一般形式（续） 嵌入式SQL语句，根据其作用的不同，可分为：可执行语句和说明性语句两类。 说明性语句 数据定义 数据操纵 　 说明性语句 数据定义 嵌入SQL语句 可执行语句 数据控制 数据操纵 在宿主程序中，任何允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句； 任何允许出现说明性高级语言语句的地方，都可以写说明性SQL语句。 2018年11月17日星期六

3.7.2 嵌入式SQL语句与主语言之间的通信 将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句。 SQL语句： 描述性的面向集合的语句； 负责操纵数据库。 高级语言语句： 过程性的面向记录的语句； 负责控制程序流程。 2018年11月17日星期六

工作单元之间的通信方式 一、 SQL通信区 二、 主变量 三、 游标 向主语言传递SQL语句的执行状态信息； 主语言能够据此控制程序流程。 解决集合性操作语言与过程性操作语言的不匹配。 2018年11月17日星期六

一、 SQL通信区 　　　SQLCA (SQL Communication Area) ：SQLCA是一个数据结构，在应用程序中用EXEC SQL INCLUDE SQLCA 加以定义。 　　　SQLCA的用途（参见教材P.134~135）： SQL语句执行后，DBMS反馈给应用程序信息： 描述系统当前工作状态； 描述运行环境； 这些信息将送到SQL通信区SQLCA中； 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句。 2018年11月17日星期六

SQLCA的内容 与所执行的SQL语句有关； 与该SQL语句的执行情况有关。 例：在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息： 违反数据保护规则，操作拒绝； 没有满足条件的行，一行也没有删除； 成功删除，并有删除的行数； 无条件删除警告信息； 由于各种原因，执行出错。 2018年11月17日星期六

SQLCA的使用方法 定义SQLCA(P.134) 使用SQLCA 用EXEC SQL INCLUDE SQLCA加以定义 SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE； 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错； 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。 2018年11月17日星期六

二、 主变量 什么是主变量(Host Variable) 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据； 在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）。 2018年11月17日星期六

主变量（续） 主变量的类型 输入主变量： 由应用程序对其赋值，SQL语句引用； 输出主变量： 　　　 一个主变量有可能既是输入主变量又是输出主变量。利用输入主变量，可以指定向数据库中插入的数据，可以将数据库中的数据修改为指定值，可以指定执行的操作，可以指定WHERE子句或HAVING子句中的条件；利用输出主变量，可以获取SQL语句的结果数据 ，可以获取SQL语句的执行状态。 2018年11月17日星期六

主变量(续) ＊主变量的用途 (小结) ☆输入主变量: 将数据库中的数据修改为指定值； 指定执行的操作； 主变量(续) ＊主变量的用途 (小结) 　　☆输入主变量: 指定向数据库中插入的数据； 将数据库中的数据修改为指定值； 指定执行的操作； 指定WHERE子句或HAVING子句中的条件。 ☆输出主变量: 获取SQL语句的结果数据； 获取SQL语句的执行状态。 2018年11月17日星期六

主变量（续）指示变量 一个主变量可以附带一个任选的指示变量（Indicator Variable）。 什么是指示变量？ 指示变量是一个整型变量，用来“指示”所指主变量的值或条件。 指示变量的用途： 指示变量可以“指示”输入主变量是否为空值； 指示变量可以“指示”输出主变量是否为空值，值是否被截断。 2018年11月17日星期六

主变量（续）指示变量 在SQL语句中使用主变量和指示变量的方法 1) 说明主变量和指示变量 BEGIN DECLARE SECTION ......... END DECLARE SECTION (所有主变量和指示变量必须在 BEGIN ........ . END之间进行说明.) 2018年11月17日星期六

主变量（续）指示变量 2) 使用主变量 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现； 2018年11月17日星期六

主变量（续）指示变量 3) 使用指示变量 指示变量前也必须加冒号标志； 必须紧跟在所指主变量之后。 2018年11月17日星期六

主变量（续）指示变量 　　　在SQL语句之外(主语言语句中)使用主变量和指示变量的方法 可以直接引用，不必加冒号。 2018年11月17日星期六

三、 游标（cursor） 为什么要使用游标 SQL语言与主语言具有不同的数据处理方式。 　　　为什么要使用游标 SQL语言与主语言具有不同的数据处理方式。 　SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录。 2018年11月17日星期六

游标（续） 主语言是面向记录的，一组主变量一次只能存放一条记录。 所以仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求； 　　　主语言是面向记录的，一组主变量一次只能存放一条记录。 所以仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求； 为此，嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式。 2018年11月17日星期六

游标（续）什么是游标 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。 每个游标区都有一个名字。 2018年11月17日星期六

嵌入式SQL语句与主语言之间的通信(续) 小结 在嵌入式SQL中，SQL语句与主语言语句分工非常明确。 SQL语句：直接与数据库打交道。 主语言语句： 1. 控制程序流程； 2. 对SQL语句的执行结果做进一步 加工处理。 2018年11月17日星期六

嵌入式SQL语句与主语言之间的通信(续) SQL语句的执行状态由DBMS送至SQLCA中； 主语言程序从SQLCA中取出状态信息，据此决定下一步的操作； 如果SQL语句从数据库中成功地检索出数据，则通过主变量传给主语言做进一步处理。 SQL语言和主语言的不同数据处理方式通过游标来协调，这实际上反映了嵌入式SQL的工作原理。 2018年11月17日星期六

嵌入式SQL语句与主语言之间的通信(续) 例：带有嵌入式SQL的一小段C程序 ............ EXEC SQL INCLUDE SQLCA; /* (1) 定义SQL通信区 */ EXEC SQL BEGIN DECLARE SECTION； /* (2) 主变量说明开始*/ CHAR Sno(5) ； CHAR Cno(3) ； INT Grade； EXEC SQL END DECLARE SECTION； 　　　　　　　　　　　　　/* 主变量说明结束*/ 2018年11月17日星期六

嵌入式SQL语句与主语言之间的通信(续) main( ) { EXEC SQL DECLARE C1 CURSOR FOR /* (3) 游标操作（定义游标）*/ SELECT FROM SC; /* 从表中查询 Sno,Cno,Grade */ EXEC SQL OPEN C1; /* (4) 游标操作（打开游标）*/ 2018年11月17日星期六

嵌入式SQL语句与主语言之间的通信(续) for(;;) { EXEC SQL FETCH C1 INTO :Sno, :Cno, :Grade; /* (5) 游标操作（推进游标指针并将当前数据放入主变量）*/ if (sqlca.sqlcode < > SUCCESS) /* (6) 利用SQLCA中的状态信息决定何时退出循环 */ break; printf(“Sno: %s, Cno: %s, Grade : %s ", :Sno, :Cno, :Grade); /* 打印查询结果 */ } EXEC SQL CLOSE C1; /* (7) 游标操作（关闭游标）*/ 2018年11月17日星期六

3.7.3 不用游标的SQL语句 不用游标的SQL语句的种类 说明性语句 数据定义语句 数据控制语句 查询结果为单记录的SELECT语句 非CURRENT形式的UPDATE语句 非CURRENT形式的DELETE语句 INSERT语句 2018年11月17日星期六

一、说明性语句 说明性语句是专为在嵌入式SQL中说明主变量、SQLCA等而设置的。 1. EXEC SQL BEGIN DECLARE SECTION; 2. EXEC SQL END DECLARE SECTION; 这两条语句必须配对出现，相当于一个括号，两条语句中间是主变量的说明。 说明SQLCA 3. EXEC SQL INCLUDE SQLCA 2018年11月17日星期六

二、数据定义语句 例1（P.138） 建立一个“学生”表Student。 EXEC SQL CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15)); 2018年11月17日星期六

数据定义语句（续） 数据定义语句中不允许使用主变量 例：下列语句是错误的 EXEC SQL DROP TABLE :table_name; 2018年11月17日星期六

三、数据控制语句 例2 （P.138） 把查询Student表权限授给用户U1. EXEC SQL GRANT SELECT ON TABLE Student TO U1; 2018年11月17日星期六

四、查询结果为单记录的SELECT语句 语句格式 EXEC SQL SELECT [ ALL | DISTINCT ] <目标列表达式>[,<目标列表达式>]... INTO <主变量>[<指示变量>] [,<主变量>[<指示变量>]]... FROM <表名或视图名>[,<表名或视图名>] ... [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]]; 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 　　　上述语句对交互式SELECT语句的扩充就是多了一个INTO子句。把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。其它子句的含义不变。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 使用该语句时的注意事项： 1. 使用主变量（下列3种情形均可使用主变量） INTO子句中； WHERE子句的条件表达式中； HAVING短语的条件表达式中。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 2. 使用指示变量 指示变量只能用于INTO子句中； 如果INTO子句中主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，但不向该主变量执行赋值操作，即主变量值仍保持执行SQL语句之前的值。所以当指示变量为负值时，不管主变量为何值，均应认为主变量值为NULL 。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 3. 查询结果为空集 如果数据库中没有满足条件的记录，即查询结果为空，则DBMS将SQLCODE的值置为100. 4. 查询结果为多条记录 　　　如果查询结果实际上并不是单条记录,而是多条记录,则程序出错，DBMS会在SQLCA中返回错误信息。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 例3 (P.139)根据学生号码查询学生信息。 假设已将要查询的学生的学号赋给了主变量givenSno EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept INTO : Hsno, : Hname, : Hsex, : Hage, : Hdept FROM Student WHERE Sno = : givenSno; 注:Hsno, Hname, Hsex, Hage, Hdept和givenSno均是主变量，并均已在前面的程序中说明过了。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 例4 (P.139) 查询某个学生选修某门课程的成绩。 假设已将要查询的学生的学号赋给了主变量givenSno，将课程号赋给了主变量givenCno。 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） EXEC SQL SELECT Sno, Cno, Grade INTO :Hsno, :Hcno, :Hgrade:Gradeid FROM SC WHERE Sno=:givenSno AND Cno=:givenCno; 注:由于学生选修一门课后有可能没有参加考试,也就是说其成绩为空值,所以在执行INTO子句中加了指示变量Gradeid,用于指示主变量Hgrade是否为空值.执行此语句后,如果Gradeid小于0,则不论Hgrade为何值,均认为该学生成绩为空值. 2018年11月17日星期六

查询结果为单记录的SELECT语句（续） 2018年11月17日星期六

五、非CURRENT形式的UPDATE语句 使用主变量的有： SET子句； WHERE子句。 使用指示变量的有： SET子句 　　　非CURRENT形式的UPDATE语句可以操作多条元组。 2018年11月17日星期六

非CURRENT形式的UPDATE语句（续） 假设增加的分数已赋给主变量Raise. EXEC SQL UPDATE SC SET Grade=Grade+:Raise WHERE Cno='1'; 2018年11月17日星期六

非CURRENT形式的UPDATE语句（续） 假设该学生的学号已赋给主变量givensno， 修改后的成绩已赋给主变量newgrade。 EXEC SQL UPDATE SC SET Grade = : newgrade WHERE Sno = : givensno; 2018年11月17日星期六

非CURRENT形式的UPDATE语句（续） 例7 (P. 140) 将计算机系全体学生年龄置NULL值 Sageid = -1; EXEC SQL UPDATE Student SET Sage=:Raise:Sageid WHERE Sdept='CS'; 注:将指示变量Sageid赋一个负值后，无论主变量Raise 为何值，DBMS都会将CS系所有记录的年龄属性置空值。 它等价于： 2018年11月17日星期六

非CURRENT形式的UPDATE语句（续） *将指示变量Sageid赋一个负值后，无论主变量Raise为何值，DBMS都会将CS系所有记录的年龄属性置空值。它等价于： EXEC SQL UPDATE Student SET Sage=NULL WHERE Sdept='CS'; 2018年11月17日星期六

六、非CURRENT形式的DELETE语句 使用主变量 DELETE语句的WHERE子句中可以使用主变量指定删除条件。 　　　非CURRENT形式的DELETE语句可以操作多条元组。 2018年11月17日星期六

非CURRENT形式的DELETE语句（续） 例8 （P.140）某个学生退学了，现要将有关他的所有选课记录删除掉。 假设该学生的姓名已赋给主变量stdname. EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname); 2018年11月17日星期六

七、INSERT语句 非CURRENT形式的INSERT语句 非CURRENT形式的INSERT语句一次只能输入一条元组。 使用主变量 INSERT语句的VALUES子句可以使用主变量和使用指示变量。 使用指示变量 VALUES子句 　　　非CURRENT形式的INSERT语句一次只能输入一条元组。 2018年11月17日星期六

INSERT语句（续） 由于该学生刚选修课程，尚未考试，因此成绩列为空。所以本例中用指示变量指示相应的主变量为空值。 例9(P.141) 某个学生新选修了某门课程，将有关记录插入SC表。假设学生的学号已赋给主变量stdno，课程号已赋给主变量couno。 gradeid = -1; EXEC SQL INSERT INTO SC(Sno, Cno, Grade) VALUES(:stdno, :couno, :gr:gradeid); 由于该学生刚选修课程，尚未考试，因此成绩列为空。所以本例中用指示变量指示相应的主变量为空值。 2018年11月17日星期六

3.7.4 使用游标的SQL语句 必须使用游标的SQL语句 查询结果为多条记录的SELECT语句 CURRENT形式的UPDATE语句 CURRENT形式的DELETE语句 2018年11月17日星期六

一、查询结果为多条记录的SELECT语句 使用游标的步骤： 1. 说明游标； 2. 打开游标； 3. 移动游标指针，然后取当前记录； 4. 关闭游标。 2018年11月17日星期六

1. 说明游标 EXEC SQL DECLARE <游标名> CURSOR 语句格式 FOR <SELECT语句>; 功能：用DECLARE语句为一条SELECT语句定义游标。其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。　　定义游标仅仅是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。 2018年11月17日星期六

2. 打开游标 使用OPEN语句将定义的游标打开。 语句格式 功能 EXEC SQL OPEN <游标名>; 功能 打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中； 这时游标处于活动状态，指针指向查询结果集中第一条记录（之前）。 返回P.169 2018年11月17日星期六

3. 移动游标指针，然后取当前记录 使用FETCH语句把游标指针向前推进一条记录。 语句格式 EXEC SQL FETCH [[NEXT|PRIOR| FIRST|LAST] FROM] <游标名> INTO <主变量>[<指示变量>] [,<主变量>[<指示变量>]]...; 返回P.169 2018年11月17日星期六

移动游标指针，然后取当前记录(续) 功能 指定方向推动游标指针，然后将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。 NEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。 NEXT：向前推进一条记录 PRIOR：向回退一条记录 FIRST：推向第一条记录 LAST：推向最后一条记录 缺省值为NEXT 返回P.169 2018年11月17日星期六

移动游标指针，然后取当前记录（续） 说明 (1) 主变量必须与SELECT语句中的目标列表达式具有一一对应关系； (2) FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理； (3) 为进一步方便用户处理数据，现在一些关系数据库管理系统对FETCH语句做了扩充，允许用户向任意方向以任意步长移动游标指针。 返回P.169 2018年11月17日星期六

4. 关闭游标 使用CLOSE语句 语句格式 功能 说明 EXEC SQL CLOSE <游标名>； 关闭游标，释放结果集占用的缓冲区及其他资源。 说明 游标被关闭后，就不再和原来的查询结果集相联系； 被关闭的游标可以再次被打开，与新的查询结果相联系。 返回P.169 2018年11月17日星期六

例题 例1(P.142) 查询某个系全体学生的信息（学号、姓名、性别和年龄）。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中. ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; 2018年11月17日星期六

例题（续） ...... /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 为主变量deptname赋值 */ 2018年11月17日星期六

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; /* 说明游标SX,将 SX与 查询结果集相联系 */ EXEC SQL OPEN SX /* 打开游标 */ 2018年11月17日星期六

例题（续） WHILE(1) /* 用循环结构逐条处理结果集中的记录 */ { EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结 果集中取当前行，送相应主变量*/ 2018年11月17日星期六

例题（续） }; if (sqlca.sqlcode < > SUCCESS) break; 　 /* 若所有查询结果均已处理完或出现 SQL语句错误，则退出循环 */ ...... /* 由主语言语句进行进一步处理 */ ...... }; 2018年11月17日星期六

例题（续） EXEC SQL CLOSE SX; /* 关闭游标SX */ ...... 　　被关闭的游标SX实际上可以再次被打开，与新的查询结果相联系。例如，可以在例1中再加上一层外循环，每次对deptname赋新的值,这样SX就每次和不同的系的学生集合相联系。如下例2所示。 2018年11月17日星期六

例题（续） 例2 (P.143)查询某些系全体学生的信息。 ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ 2018年11月17日星期六

例题（续） EXEC SQL END DECLARE SECTION; ...... EXEC SQL DECLARE SX CURSOR FOR /* 说明游标SX,将 SX与查询结果集相联系 */ SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; 2018年11月17日星期六

例题（续） WHILE (gets(deptname)!=NULL) /* 接收主变量deptname 的值 */ EXEC SQL OPEN SX /* 每次打开游标SX就和不同系 的学生集合相联系 */ 2018年11月17日星期六

例题（续） WHILE (1) { /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/ 2018年11月17日星期六

例题（续） if (sqlca.sqlcode < > SUCCESS) break; /* 若所有查询结果均已处理完或出现 ...... /* 由主语言语句进行进一步处理 */ }; /* 内循环结束 */ 2018年11月17日星期六

例题（续） EXEC SQL CLOSE SX; /* 关闭游标 */ }; /* 外循环结束 */ ...... }; /* 外循环结束 */ ...... 2018年11月17日星期六

二、CURRENT形式的UPDATE语句和DELETE语句 面向集合的操作； 一次修改或删除所有满足条件的记录。 2018年11月17日星期六

二、CURRENT形式的UPDATE语句和DELETE语句 如果只想修改或删除其中某个记录 用带游标的SELECT语句查出所有满足条件的记录； 从中进一步找出要修改或删除的记录； 用CURRENT形式的UPDATE语句和DELETE语句修改或删除之。具体步骤： 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） 步骤 (1) 用DECLARE语句说明游标 说明游标； (2) 用OPEN语句打开游标 打开游标，把所有满足查询条件的记录从指定表取到缓冲区中。 (3) 用FETCH语句推进游标指针 推进游标指针，并把当前记录从缓冲区中取出来送至主变量； 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） 　(5) 重复第(3)和(4)步，[第(3)和(4)步通常在一个循环结构中,通过循环执行FETCH语句,] 逐条取出结果集中的行进行判断和处理。 (6) 处理完毕用CLOSE语句关闭游标 关闭游标，释放结果集占用的缓冲区和其他资源。 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） 说明游标1) 、2) 1)为UPDATE语句说明游标 使用带FOR UPDATE OF <列名>短语的DECLARE语句 语句格式 EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句> FOR UPDATE OF <列名>； FOR UPDATE OF <列名>短语用于指明检索出的数据在指定列上是可修改的，以便DBMS进行并发控制。 返回P190 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） ＊注:如果是为CURRENT形式的DELETE语句作准备,则不必使用上述子句。 　<DELETE语句> WHERE CURRENT OF <游标名> P190 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） 　　　　修改或删除当前记录 经检查缓冲区中记录是要修改或要删除的记录，则用UPDATE语句或DELETE语句修改或删除该记录。这时UPDATE语句或DELETE语句要用子句WHERE CURRENT OF <游标名>来表示要修改或要删除的是最近一次取出的记录。 语句格式 <UPDATE语句> WHERE CURRENT OF <游标名> <DELETE语句> WHERE CURRENT OF <游标名> P191 2018年11月17日星期六

CURRENT形式的UPDATE语句和DELETE语句（续） 　　注意：当游标定义中的SELECT语句带有UNION或ORDER BY子句时，或者该SELECT语句相当于定义了一个不可更新的视图时，不能使用CURRENT形式的UPDATE语句和DELETE语句。 2018年11月17日星期六

例题 例3 (P.144)查询某个系全体的学生信息，根据用户的要求修改其中某些人的年龄字段。 思路 查询某个系全体学生的信息(要查询的系名由主变量deptname指定）； 然后根据用户的要求修改其中某些记录的年龄字段。 2018年11月17日星期六

例题（续） ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; 　...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; ...... /* 说明主变量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ 2018年11月17日星期六

例题（续） ...... EXEC SQL END DECLARE SECTION; 　gets(deptname); /* 为主变量deptname赋值 */ 2018年11月17日星期六

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname 　 FOR UPDATE OF Sage; /* 说明游标，为 CURRENT UPDATE作准备 */ EXEC SQL OPEN SX /* 打开游标 */ 2018年11月17日星期六

例题（续） WHILE(1) {/* 用循环结构逐条处理结果集中的记录 */ :HSsex, :HSage; EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集 中取当前行，送相应主变量*/ 2018年11月17日星期六

例题（续） if (sqlca.sqlcode < > SUCCESS) break; /* 若所有查询结果均已处理完或 　 /* 若所有查询结果均已处理完或 出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage); /* 显示该记录 */ printf("UPDATE AGE ? "); /* 问用户是否要修改 */ scanf("%c",&yn); 2018年11月17日星期六

例题（续） if (yn = = 'y' or yn = = 'Y') /* 需要修改 */ { printf("INPUT NEW AGE: "); scanf("%d",&NEWAge); /* 输入新的年龄值*/ EXEC SQL UPDATE Student SET Sage=:NEWAge WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */ }; 2018年11月17日星期六

例题（续） ...... }; EXEC SQL CLOSE SX; /* 关闭游标 */ 2018年11月17日星期六

例题（续） 例4(P.145)查询某个系全体学生的信息(要查询的系名由主变量deptname指定），然后根据用户的要求删除其中某些(人的)记录。 ...... EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ ....... 2018年11月17日星期六

例题（续） EXEC SQL END DECLARE SECTION; ...... gets(deptname); /* 为主变量deptname赋值 */ 2018年11月17日星期六

例题（续） EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname/* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */ 2018年11月17日星期六

例题（续） WHILE(1){/* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结 果集中取当前行，送相应主变量*/ 2018年11月17日星期六

例题（续） if (sqlca.sqlcode < > SUCCESS) break; /* 若所有查询结果均已处理完或 　 /* 若所有查询结果均已处理完或 出现SQL语句错误，则退出循环 */ printf("%s, %s, %s, %d", Sno, Sname, Ssex, Sage); /* 显示该记录 */ 2018年11月17日星期六

例题（续） WHERE CURRENT OF SX; /* 删除当前记录 */ printf("DELETE ? "); /* 问用户是否要删除 */ scanf("%c",&yn); if (yn= ='y' or yn= ='Y') /* 需要删除 */ EXEC SQL DELETE FROM Student WHERE CURRENT OF SX; /* 删除当前记录 */ 2018年11月17日星期六

例题（续） ...... }; EXEC SQL CLOSE SX； /* 关闭游标 */ 2018年11月17日星期六

3.7.5 动态SQL简介 静态嵌入式SQL 动态嵌入式SQL 2018年11月17日星期六

一、静态SQL的特点 在3.7.3和3.7.4中，我们所讨论的嵌入式SQL语句为编程提供了一定的灵活性，使用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。 这些SQL语句的共同特点是：语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是在程序运行过程中动态输入的（称这类嵌入式SQL语句为静态SQL语句）。 2018年11月17日星期六

静态SQL的不足 　　　静态SQL语句提供的编程灵活性在许多情况下仍显得不足，难以编写更为通用的程序。例如： 2018年11月17日星期六

需求 例，对SC表，可能的情况是： 任课教师想查询选修某门课程的所有学生的学号及其成绩； 班主任想查询某个学生选修的所有课程的课程号及相应成绩； 学生想查询某个学生选修某门课程的成绩。 即：查询条件是不确定的，要查询的属性列也是不确定的。这时就无法用一条静态SQL语句来实现之。如果在预编译时有关信息不能确定，则必须使用动态SQL技术，如： 2018年11月17日星期六

二、动态SQL 1.应用范围 2.什么是动态嵌入式SQL 在预编译时下列信息不能确定时： 主变量个数 主变量的数据类型 SQL语句中引用的数据库对象（列、索引、基本表、视图等） 2.什么是动态嵌入式SQL 动态SQL方法允许在程序运行过程中临时“组装”SQL语句。 2018年11月17日星期六

动态SQL（续） 3. 动态SQL的形式（三种） 语句可变 条件可变 允许用户在程序运行时临时构造完整的SQL语句； WHERE子句中的条件 HAVING短语中的条件 （1）对于非查询语句，条件子句有一定的可变性[例如,删除学生选课记录,既可以是因某门课临时取消,需要删除有关该课程的所有选课记录,也可以是因为某个学生退学,需要删除该学生的所有选课记录]。 （2）对于查询语句，SELECT子句是确定的，即 2018年11月17日星期六

动态SQL（续） 语句的输出是确定的，其它子句(如WHERE子句、HAVING短语)有一定的可变性[例如查询学生人数，可以是查询某个系的学生总数，查询某种性别的学生人数，查询某个年龄段的学生人数，查询某个系某个年龄段的学生人数等，这时SELECT子句的目标列表达式是确定的（COUNT(*)）,但WHERE子句中的条件是不确定的]。 2018年11月17日星期六

动态SQL（续） 数据库对象、查询条件均可变 SELECT子句中的列名 FROM子句中的表名或视图名 WHERE子句中的条件 HAVING短语中的条件 　对于查询语句， SELECT子句中的列名、 FROM子句中的表名或视图名和HAVING短语中的条件等均可由用户临时构造，即语句的输入和输出可能都是不确定的。例如前面查询学生选课关系SC的例子。对于非查询语句，涉及的数据库对象、查询条件均是可变的。 2018年11月17日星期六

动态SQL（续） ＊4. 常用动态SQL语句 使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身。 EXECUTE IMMEDIATE PREPARE EXECUTE DESCRIBE 　　　使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身。 2018年11月17日星期六

3.8 小 结 SQL的特点 ⒈ 综合统一 2. 高度非过程化 3. 面向集合的操作方式 4. 同一种语法结构提供两种使用方式 3.8 小 结 SQL的特点 ⒈ 综合统一 2. 高度非过程化 3. 面向集合的操作方式 4. 同一种语法结构提供两种使用方式 5. 语言简捷，易学易用 2018年11月17日星期六

小结（续） 交互式SQL 数据定义 查询 数据更新 数据控制 2018年11月17日星期六

小结（续） 表3.1 SQL语言的动词 SQL 功能 动 词 数据查询 SELECT 数据定义 CREATE ,DROP ,ALTER 数据操纵 数据控制 CREATE ,DROP ,ALTER INSERT, UPDATE ,DELETE GRANT,REVOKE 2018年11月17日星期六

小结（续） 嵌入式SQL 与主语言的通信方式 1. SQL通信区 2. 主变量 3. 游标 向主语言传递SQL语句的执行状态信息 解决集合性操作语言与过程性操作语言的不匹配 2018年11月17日星期六

小 结（续） 嵌入式SQL(续) 静态SQL 不用游标 动态SQL 不需要返回结果数据的SQL语句 只返回一条结果的SQL语句 使用游标 说明游标 打开游标 推进游标并取当前记录 关闭游标 动态SQL 2018年11月17日星期六