数据库技术讲义 第3章 关系数据库标准语言SQL-3.ppt

1、3.4 数据更新,SQL中数据的更新操作包括插入数据、修改数据和删除数据三条语句，分别通过：INSERT、UPDATE和DELETE语句来实现。,3.4.1 插入数据,一、插入单个元组 语法格式: INSERT INTO (,) VALUES (, )； 其功能是将新元组插入到指定表中，其中新记录列1的值为常量1，列2的值为常量2，依次类推。INTO子句中没有出现的属性列，新纪录在这些列上将取空值。在表定义时为NOT NULL的属性列不能取空。如果INTO子句中没有指定任何列，则新插入的记录必须在每列上都有值，值的顺序和表中列顺序一致。,3.4.1 插入数据,例：插入一个新的学生记录 INSE

2、RT INTO Student VALUES(95020, 陈冬, 男,IS,18); 例：插入一条选课记录 INSERT INTO SC(Sno,Cno) VALUES(95020, 1);,3.4.1 插入数据,二、插入子查询结果 语法格式为： INSERT INTO (,) 子查询 插入语句中列名的列表应该和子查询的目标列表达式一致。,3.4.1 插入数据,例：对每一个系求平均年龄并插入数据库。 INSERT INTO Deptage(Sdept,Avgage) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;,3.4.2 修改数据,

3、修改语句的一般语法格式为： UPDATE SET =| , =| WHERE 如果省略WHERE子句则表示要修改表中的所有元组。,3.4.2 修改数据,一、修改某个元组的值 例：将学生95001的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno=95001; 二、修改多个元组的值 例：将所有学生的年龄加1 UPDATE Student SET Sage=Sage+1;,3.4.2 修改数据,三、带子查询的修改语句 例：将计算机科学系全体学生的成绩置0 UPDATE SC SET Sage=0 WHERE CS= (SELECT Sdept FROM St

4、udent WHERE Student.Sno=SC.Sno);,3.4.3 删除数据,删除语句的语法格式为： DELETE FROM WHERE ; DELETE语句从表中删除满足WHERE条件的元组，如果省略WHERE则删除表中所有元组。,3.4.3 删除数据,一、删除某个元组 例：删除学号为95019的学生 DELETE FROM Student WHERE Sno=95019; 二、删除多个元组 例:删除所有的学生选课记录 DELETE FROM SC;,3.4.3 删除数据,三、带子查询的删除语句 例：删除计算机系所有学生的选课记录 DELETEFROM SC WHERE CS=(S

5、ELECT Sdept FROM Student WHERE Student.Sno=SC.Sno); 四、更新操作与数据库的一致性,3.5 视图,视图是从一个或几个基本表导出的表，是一个虚报。数据库中只存储视图的定义，而不存放数据。视图一经定义，就可以和基本表一样被查询、删除，我们也可以在一个视图上再定义视图，但对视图的更新操作有一定的限制。,3.5.1 定义视图,一、建立视图 用CREATE VIEW语句创建视图，语法格式为： CREATE VIEW (,) AS WITH CHECK OPTION; 子查询是任意复杂的SELECT语句，但通常不允许含有ORDER BY和DISTINCT子

6、句。 WITH CHECK OPTION表示对视图进行更新操作是满足视图中定义的谓词条件。,3.5.1 定义视图,组成视图的属性列或者全部省略或者全部指定。如果省略则表示视图列由子查询中的目标列表达式组成，但以下三种情况要指定列名： 某个目标列不是单纯的属性名，而是集函数和列表达式 多表连接时选出了几个同名列作为视图字段 需要再视图中为某个列启用新的更合适的名字 若一个视图是从基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，我们称这类视图为行列子集视图。,3.5.1 定义视图,二、删除视图 语法格式为： DROP VIEW ; 视图定义后可以象基本表一样进行查询，但对视图的查询

7、都将由DBMS转化为等价的对基本表的查询，这一转换过程称为视图消解。,3.5.2 查询视图,视图定义后可以象基本表一样进行查询，但对视图的查询都将由DBMS转化为等价的对基本表的查询，这一转换过程称为视图消解。,3.5.3 更新视图,对视图的更新最终都要转换为对基本表的更新。为了防止用户更新视图时更新不属于视图的数据，可以在定义视图时加上WITH CHECK OPTION子句，这样在更新视图时会自动检查视图定义中的条件。,3.5.3 更新视图,一般对应行列子集视图可以更新，而其它视图有些理论上可更新，但确切特征还尚待研究；还有些视图从理论上就不可更新。目前各个关系数据库系统一般都只允许对行列子

8、集视图进行更新，不同数据库系统规定也不相同。,3.5.4 视图的作用,使用视图的优点： (1)简化了用户的操作 (2)视图机制使不同的用户能以不同的方式看待同一数据，当许多不同种类的用户使用同一个集成数据库时，这种灵活性非常重要 (3)视图对于数据库的重构提供了一定程度的逻辑独立性 (4)能够将对数据库的访问限制在一定范围内，有利于数据保密,3.6 数据控制,是指控制用户对数据的存取权力 功能要求： 把授权的决定告诉系统，由GRANT和REVOKE语句完成 把授权的结果存入数据字典 当用户提出操作请求时，根据授权情况进行检查，以决定是执行操作还是拒绝,3.6.1 授权,语句格式：GRANT 权

9、限、权限 ON TO , WITH GRANT OPTION; 功能是将对指定操作对象的指定操作权限授予指定的用户。如果指定了WITH GRANT OPTION子句，则获得权限的用户还可以把该权限再授予其它用户,3.6.2 收回权限,语法格式为： REVOKE 权限、权限 ON FROM ,3.7 嵌入式SQL,SQL语言提供了一种使用方式，即将sQL语言嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足。这种方式下使用的SQL语言称为嵌入式SQL(Embedded SQL)，而嵌入SQL的高级语言称为主语言或宿主语言。,3.7.1 嵌入式SQL的一般形式

10、,区分SQL语句与C语言语句 嵌入的SQL语句以EXEC SQL开始，以分号(;) 或END_EXEX结束 EXEC SQL delete from Student where Sno = 95001；,3.7.2 嵌入式SQL语句与主语句之间的通信,数据库工作单元与源程序工作单元之间的通信主要包括： (1)向主语言传递SQL语句的执行状态信息，使主语言能够据此信息控制程序流程，主要用SQL通信区(SQL Communication Area,简称SQLCA)实现； (2)主语言向SQL语句提供参数，主要用主变量(Host Variable实现： (3)将SQL语句查询数据库的结果交主语言进一

11、步处理，主要用主变量和游标(Cursor)实现。,3.7.2 嵌入式SQL语句与主语句之间的通信,一、SQL通信区 SQLCA是一个数据结构，在应用程序中用EXEC SQL INCLUDE SQLCA加以定义。SQLCA中有一个存放每次执行SQL语句后返回代码的变量 SQLCODE。应用程序每执行完一条SQl语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则在SQLCODE中存放错误代码。,3.7.2 嵌入式SQL语句与主语句之间的通信,例如，在执行删除语句DELETE后，不同的执行情况

12、SQLCA中有下列不同的信息： 成功删除，并有删除的行数(SQLCODESUCCESS) 无条件删除警告信息 违反数据保护规则，拒绝操作 没有满足条件的行，一行也没有删除 由于各种原因，执行出错,3.7.2 嵌入式SQL语句与主语句之间的通信,二、主变量 主变量根据其作用的不同，分为输入主变星和输出主变量。输入主变量由应用程序对其赋值，SQL语句引用；输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序。一个主变量有可能既是输入主变量又是输出主变量。利用输入主变量，可以指定向数据库中插入的数据，可以将数据库中的数据修改为指定值，可以指定执行的操作，可以指定WHERE子句或HAVING子

13、句中的条件。利用输出主变量，可以得到SQL语句的结果数据和状态。,3.7.2 嵌入式SQL语句与主语句之间的通信,二、主变量 一个主变量可以附带一个任选的指示变量(Indicator Variable)。指示变量是一个整型变量，用来“指示”所指主变量的值或条件。指示变量可以指示输入主变量是否为空值，可以检测输出主变量是否空值，值是否被截断。,3.7.2 嵌入式SQL语句与主语句之间的通信,所有主变量和指示变量必须在SQL语句BEGIN DECLARE SECTION与END DECLARE SECTION之间进行说明。说明之后，主变量可以在SQL语句中任何一个能够使用表达式的地方出现，为了与数

14、据库对象名(表名、视图名、列名等)区别SQL语句中的主变量名前要加冒号(：)作为标志。同样，SQL语句中的指示变量前也必须加冒号，并且要紧跟在所指主变量之后。而在SQL语句之外，主变量和指示变量均可以直接引用，不必加冒号。,3.7.2 嵌入式SQL语句与主语句之间的通信,三、游标 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求，为此嵌入式SQL引入了游标的概念，用游标来协调这两种不同的处理方式。游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。,3.7.2 嵌入式SQL语句与主

15、语句之间的通信,四、程序实例 EXEX SQL INCLUDE SQLCA;.(1)定义SQL通信区 EXEC SQL BEGIN DECLARE SECTION;.(2)主变量说明开始 CHAR Sno(5); CHAR Cno(3); INT Grade; EXEC SQL END DECLARE SECTION;.主变量说明结束,3.7.2 嵌入式SQL语句与主语句之间的通信,main() EXEC SQL DECLARE C1 CURSOR FOR .(3)游标操作(定义游标) SELECT Sno，Cno，Grade FR0M SC; *从表中查询Sno，Cno，Grade* EXE

16、C SQL OPEN C1;.(4)游标操作(打开游标) for(;) EXEC SQL FETCH C1 INTO:SM,:ClD,:Grade; .(5)游标操作(推进游标指针并 将当前数据放入主变量) if(sqlca.sqlcodeSUCCESS) .(6)利用SQLCA中的状态信 息决定何时退出循环,3.7.2 嵌入式SQL语句与主语句之间的通信,break; printf(Sno:%s,Cno:%s,Grade:%d, :Sn o,:Cno,:Grade); *打印查询结果* EXEC SQL CLOSE C1; .(7)游标操作（关闭游标） ,3. 7.3不用游标的SQL语句,不

17、用游标的SOL语句有： 说明性语句 数据定义语句 数据控制语句 查询结果为单记录的SELECT语句 非CURRENT形式的UPDATE语句 非CURRENT形式的DELETE语句 INSERT语句,3. 7.3 不用游标的SQL语句,所有的说明性语句及数据定义与控制语句都不需要使用游标。它们是嵌入式sQL中最简单的一类语句，不需要返回结果数踞，也不需要使用主变量。在主语言中嵌入说明姓语句及数据定义与控制语句，只要给语句加上前额ExEC SQL和语句结束符即可。 INSERT语句也不需要使用游标，但通常需要使用主变量。 SELECT语句、UPDATE语句、DELETE语句则要复杂些。,3.7.3

18、 不用游标的SQL语句,一、说明性语句 说明性语句是专为在嵌入式SQL中说明主变量等 而设置的，主要有两条语句： EXEC SQL BEGIN DECLARE SECTION； 和 EXEC SQL END DECLARE SECTION; 两条语句必须配对出现，相当于一个括号，两条 语句中间是主变量的说明。,3.7.3 不用游标的SQL语句,二、数据定义语句 例1 建立一个“学生”表sm6gt。 ExEC SQL CREATE TABLE SNdgt (Sno CHAR(5) NOT NULL UIQUE， Sname CHAR(20)， Ssex CHAR(1)， Sage INT, Sd

19、ept CHAR(15)； EXEC SQL DROP TABLE Student; 数据定义语句中不允许使用主变量。例如下列语句 是错误的: EXEC SQL DROP TABLE: table_name;,3.7.3不用游标的SQL语句,三、数据控制语句 例2 把查询Student表权限授给用户U1。 EXEC SQL GRANT SELECT ON TABLE Student TO U1;,3.7.3 不用游标的SQL语句,四、查询结果为单记录的SELECT语句 在嵌入式SQL中，查询结果为单记录的SELECT语句需要 用INTO子句指定查询结果的存放地点。该语句的一般格式为： EXEC

20、 SQL SELECT ALL|DISTINCT INTO, FROM ， WHERE GROUP BYHAVING ORDER BYASC|DESC,3.7.3 不用游标的SQL语句,该语句对交互式SELECT语句的扩充就是多了一个INT0子句。把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。其他子句的含义不变。使用该语句需要注意以下几点: (1)INTO子句、WHERE子句的条件表达式、HAVING短语的条件表达式中均可以使用主变量。 (2)查询返回的记录中，可能某些列为空值NULL。如果INTO子句中主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动

21、将相应主变量后面的指示变量置为负值，而不再向该主变量赋值，即主变量值仍为执行SQL语句之前的值。所以当指示变量值为负值时，不管主变量为何值，均应认为主变量值为NULL。指示变量只能用于INTO子句中。 (3)如果数据库中没有满足条件的记录，则DBMS将SQLCODE的值置为100。 (4)如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，DBMS会在SQLCA中返回错误信息。,3.7.3 不用游标的SQL语句,五、非CURRENT形式的UPDATE语句 在UPDATE语句中，SET子句和WHERE子句中均可以使用主变量，其中SET子句中还可以使用指示变量。 六、非CURRENT形式的

22、DELETE语句 DELETE语句的WHERE子句中可以使用主变量指定删除条件。 七、INSERT语句 INSERT语句的VALUES子句中可以使用主变量和指示变星。,3.7.4 使用游标的SQL语句,必须使用游标的 SQL 语句有： 查询结果为多条记录的SELECT语句 CURRENT形式的UPDATE语句 CURRENT形式的DELTE语句,3.7.4 使用游标的SQL语句,一、查询结果为多条记录的SELECT语句 一般情况下，SELECT语句查询结果都是多条记录，而高级语言一次只能处 理一条记录，因此需要用游标机制，将多条记录一次一条送至宿主程序处理， 从而把对集合的操作转换为对单个记录

23、的处理。 使用游标的步骤为： (1)说明游标。用DECLARE语句为一条SELECT语句定义游标。 DECLARE语句的一般形式为： EXEC SQL DECLARECURSOR FOR ； 其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。 定义游标仅仅是一条说明性语句，这时DBMS并不执行SELECT语句。,3.7.4 使用游标的SQL语句,(2)打开游标。用OPEN语句将定义的游标打开。0PEN语句的一般形式为： EXECSQL 0PEN； 打开游标实际上是执行相应的SELECT语句，把查询结果取到缓冲 区中。这时游标处于活动状态，指针指向查询结果集中第1条记录。 (

24、3)推进游标指针并取当前记录。用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。FETCH语句的一般形式为： EXEC SQL FETCH INT0,；,3.7.4 使用游标的SQL语句,其中主变量必须与SELECT语句中的目标列表达式具有一一对应关系。 FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句 逐条取出结果集中的行进行处理。 为进一步方便用户处理数据，现在许多关系数据库管理系统对 FETCH语句做了扩充，允许向任意方向以任意步长移动游标指针，而 不仅仅是把游标指针向前推进一行了。 (4)关闭游标。用CLOSE语句关

25、闭游标，释放结果集占用的缓冲区及 其他资源。CLOSE语句的一般形式为； EXEC SQLCLOSE； 游标被关闭后，就不再和原来的查询结果集相联系。但被关闭的游标可以 再次被打开，与新的查询结果相联系。,3.7.4 使用游标的SQL语句,二、CURRENT形式的UPDATE语句和DELETE语句 UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中某个记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的 UPDATE 和DELETE 语句修改或删除之。具体步骤是： (1)用DECLARE语句说明游标。如果是为CURRENT形式的UPDATE语句作准各，则SELECT语句中要用 FOR UPDATE OF 用来指明检索出的数据在指定列是可修改的。如果是为 CURRENT 形式的DELETE语句作准备，则不必使用上述子句。,3.7.4 使用游标的SQL语句,(2)用OPEN语句打开游标，把所有满足查询条件的记录从指定表取到 缓冲区中。 (3)用FETCH语句推进游标指针，并把当前记录从缓冲区