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

《数据库技术讲义 第3章 关系数据库标准语言SQL-3.ppt》由会员分享，可在线阅读，更多相关《数据库技术讲义 第3章 关系数据库标准语言SQL-3.ppt（50页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

3、UPDATE vSET=|,=|WHEREv如果省略WHERE子句则表示要修改表中的所有元组。3.4.2 修改数据v一、修改某个元组的值v例：将学生95001的年龄改为22岁vUPDATE StudentvSET Sage=22vWHERE Sno=95001;v二、修改多个元组的值v例：将所有学生的年龄加1vUPDATE StudentvSET Sage=Sage+1;3.4.2 修改数据v三、带子查询的修改语句v例：将计算机科学系全体学生的成绩置0vUPDATE SCvSET Sage=0vWHERE CS=v(SELECT Sdeptv FROM Studentv WHERE Stude

4、nt.Sno=SC.Sno);3.4.3 删除数据v删除语句的语法格式为：vDELETEvFROM vWHERE;vDELETE语句从表中删除满足WHERE条件的元组，如果省略WHERE则删除表中所有元组。3.4.3 删除数据v一、删除某个元组v例：删除学号为95019的学生vDELETEvFROM StudentvWHERE Sno=95019;v二、删除多个元组v例:删除所有的学生选课记录vDELETE FROM SC;3.4.3 删除数据v三、带子查询的删除语句v例：删除计算机系所有学生的选课记录vDELETEFROM SCvWHERE CS=(SELECT Sdeptv FROM St

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

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

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

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

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

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

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

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

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

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

15、L 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*EXEC SQL OPEN C1;.(4)游标操作(打开游标)for(

16、;)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语句不用游标的SOL语句有：说明性语句数据定义语句数据控制语句查询结果为单记录的SELECT语句非CU

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

18、QL 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,Sdept CHAR(15)；EXEC SQL DROP TABLE Student;数据定义语句中不允许使用主变量。例如下列语句是错误的:EXEC S

19、QL 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 SQL SELECT ALL|DISTINCT INTO,FROM ，WHERE GROUP BYHAVING ORDER BYASC|DESC3.7.3 不用游标的SQL

20、语句v 该语句对交互式SELECT语句的扩充就是多了一个INT0子句。把从数据库中找到的符合条件的记录，放到INTO子句指出的主变量中去。其他子句的含义不变。使用该语句需要注意以下几点:v (1)INTO子句、WHERE子句的条件表达式、HAVING短语的条件表达式中均可以使用主变量。v (2)查询返回的记录中，可能某些列为空值NULL。如果INTO子句中主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将相应主变量后面的指示变量置为负值，而不再向该主变量赋值，即主变量值仍为执行SQL语句之前的值。所以当指示变量值为负值时，不管主变量为何值，均应认为主变量值为NULL。指示变

21、量只能用于INTO子句中。v (3)如果数据库中没有满足条件的记录，则DBMS将SQLCODE的值置为100。v (4)如果查询结果实际上并不是单条记录，而是多条记录，则程序出错，DBMS会在SQLCA中返回错误信息。3.7.3 不用游标的SQL语句五、非CURRENT形式的UPDATE语句 在UPDATE语句中，SET子句和WHERE子句中均可以使用主变量，其中SET子句中还可以使用指示变量。六、非CURRENT形式的DELETE语句 DELETE语句的WHERE子句中可以使用主变量指定删除条件。七、INSERT语句 INSERT语句的VALUES子句中可以使用主变量和指示变星。3.7.4

22、使用游标的SQL语句v必须使用游标的 SQL 语句有：v查询结果为多条记录的SELECT语句vCURRENT形式的UPDATE语句vCURRENT形式的DELTE语句3.7.4 使用游标的SQL语句一、查询结果为多条记录的SELECT语句 一般情况下，SELECT语句查询结果都是多条记录，而高级语言一次只能处理一条记录，因此需要用游标机制，将多条记录一次一条送至宿主程序处理，从而把对集合的操作转换为对单个记录的处理。使用游标的步骤为：(1)说明游标。用DECLARE语句为一条SELECT语句定义游标。DECLARE语句的一般形式为：EXEC SQL DECLARECURSOR FOR；其中SE

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

24、 使用游标的SQL语句其中主变量必须与SELECT语句中的目标列表达式具有一一对应关系。FETCH语句通常用在一个循环结构中，通过循环执行FETCH语句逐条取出结果集中的行进行处理。为进一步方便用户处理数据，现在许多关系数据库管理系统对FETCH语句做了扩充，允许向任意方向以任意步长移动游标指针，而不仅仅是把游标指针向前推进一行了。(4)关闭游标。用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。CLOSE语句的一般形式为；EXEC SQLCLOSE；游标被关闭后，就不再和原来的查询结果集相联系。但被关闭的游标可以再次被打开，与新的查询结果相联系。3.7.4 使用游标的SQL语句 二

25、、CURRENT形式的UPDATE语句和DELETE语句 UPDATE语句和DELETE语句都是集合操作，如果只想修改或删除其中某个记录，则需要用带游标的SELECT语句查出所有满足条件的记录，从中进一步找出要修改或删除的记录，然后用CURRENT形式的 UPDATE 和DELETE 语句修改或删除之。具体步骤是：(1)用DECLARE语句说明游标。如果是为CURRENT形式的UPDATE语句作准各，则SELECT语句中要用 FOR UPDATE OF 用来指明检索出的数据在指定列是可修改的。如果是为 CURRENT 形式的DELETE语句作准备，则不必使用上述子句。3.7.4 使用游标的SQ

26、L语句(2)用OPEN语句打开游标，把所有满足查询条件的记录从指定表取到缓冲区中。(3)用FETCH语句推进游标指针，并把当前记录从缓冲区中取出来送至主变量。(4)检查该记录是否是要修改或删除的记录。如果是，则用UPDATE语句或DELETE语句修改或删除该记录。这时UPDATE语句和DELETE语句中要用子句 WHERE CURRENT OF来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录。第(3)和(4)步通常用在一个循环结构中，通过循环执行FETCH语句，逐条取出结果集中的行进行判断和处理。(5)处理完毕用CLOSE语句关闭游标，释放结果集占用的缓冲区和其他资源。3.7.5动

27、态SQL简介如果在预编译时下列信息不能确定，就必须使用动态SQL技术，如：(1)SQL语句正文(2)主变量个数(3)主变量的数据类型(4)SQL 语句中引用的数据库对象(例如列、索引、基本表、视图等)动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式：3.7.5动态SQL简介1语句可变允许用户在程序运行时临时输入完整的SQL语句。2条件可变 对于非查询语句，条件子句有一定的可变性。例如删除学生选课记录，既可以是因某门课临时取消，需要删除有关该课程的所有选课记录，也可以是因为某个学生退学，需要删除该学生的所有选课记录。对于查询语句，SELECT子句是确定的，即语句的输出是确定的，其他子句(如WHERE子句、HAVING短语)有一定的可变性。例如查询学生人数，可以是查询某个系的学生总数，查询某个性别的学生人数，查询某个年龄段的学生人数，查询某个系某个年龄段的学生人数等，这时SELECT子句的目标列表达式是确定的(COUNT(*)，但WHERE子句的条件是不确定的。3数据库对象、查询条件均可变 对于查询语句，SELECT子句中的列名、FROM子句中的表名或视图名、WHERE子句和HAVING短语中的条件等均可由用户临时构造，即语句的输入和输出可能都是不确定的。例如前面查询学生选课关系SC的例子。对于非查询语句，涉及的数据库对象及条件也是可变的。