第三章 关系数据库标准语言SQL(3)

.,第三章关系数据库标准语言SQL(3),.,3.5视图,视图是从一个或几个基本表(或视图)导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。也可以在一个视图之上再定义新的视图对视图的更新(增加、删除、修改)操作则有一定的限制。,.,351定义视图,一、建立视图,子查询可以是任意复杂的SELECT语句，子查询不允许含有ORDERBY子句和DISTINCT短语。,.,WITHCHECKOPTION表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。如果省略了视图的各个属性列名，则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。下列三种情况下必须明确指定组成视图的所有列名：(1)某个目标列不是单纯的属性名，而是集函数或列表达式(2)多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字,.,.,.,若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，我们称这类视图为行列子集视图。视图可以建立在多个基本表,.,建立在一个或多个已定义好的视图上，或建立在基本表与视图上,.,但由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在也称它们为虚拟列。带虚拟列的视图也称为带表达式的视图。,.,可以用带有集函数和GROUPBY子句的查询来定义视图，称为分组视图。,.,修改基本表之后删除由该基本表导出的视图，然后重建(同名)视图。,.,二、删除视图,.,352查询视图,DBMS从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(ViewResolution)。,.,.,涉及虚表CSStudent和基本表SC:,.,.,将上面查询语句与子查询结合后:,WHERE子句中是不能用集函数作为条件表达式的,此修正后的查询将会出现语法错误。正确的查询语句应该是：,.,353更新视图,对视图的更新，最终要转换为对基本表的更新。为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITHCHECKOPTION子句。,.,.,.,.,在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。例:视图SG是由“学号”和“平均成绩”两个属性列组成的，其中平均成绩一项是由Student表中对元组分组后计算平均值得来的。如果我们想把视图SG中学号为95001的学生的平均成绩改成90分，SQL语句如下：UPDATESGSETGavg=90WHERESno=95001；,.,目前各个关系数据库系统一般都只允许对行列子集视图进行更新各个系统对视图的更新还有更进一步的规定例如DB2规定：(1)若视图是由两个以上基本表导出的，则此视图不允许更新。(2)若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作(3)若视图的字段来自集函数，则此视图不允许更新。(4)若视图定义中含有GROUPBY子句，则此视图不允许更新,.,(5)若视图定义中含有DISTINCT短语，则此视图不允许更新。(6)若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，此视图不允许更新。例如将SC中成绩在平均成绩之上的元组定义成一个视图GOOD_SC：CREATEVIEWGOOD_SCASSELECTSno，Cno，GradeFROMSCWHEREGrade(SELECTAVG(Grade)FROMSC)；,.,(7)一个不允许更新的视图上定义的视图也不允许更新。,.,354视图的作用,合理使用视图能够带来许多好处:1视图能够简化用户的操作2视图使用尸能以多种角度看待同一数据3视图对重构数据库提供了一定程度的逻辑独立性重构数据库最常见的是将一个基本表“垂直”地分成多个基本表。例如：将学生关系Student(Sno，Sname，Ssex，Sage，Sdept)分为SX(Sno，Sname，Sage)和SY(Sno，Ssex，Sdept)两个关系。,.,原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：CREATEVIEWStudent(Sno，Sname，Ssex，Sage，Sdept)ASSELECTSXSno，SXSname，SYSsex，SXSage，SYSdeptFROMSX，SYWHERESXSno=SYSno：4视图能够对机密数据提供安全保护,.,3.6数据控制,SQL中数据控制功能包括:事务管理功能和数据保护功能，即数据库的恢复、并发控制：数据库的安全性和完整性控制。DBMS必须具有以下功能：(1)把授权的决定告知系统，这是由SQL的GRANT和REVOKE语句来完成的。(2)把授权的结果存入数据字典。(3)当用户提出操作请求时，根据授权情况进行检查,.,361授权,SQL语言用GRANT语句向用户授予操作权限，GRANT语句的一般格,.,.,对属性列和视图的操作权限有：查询(SEIECT)、插入(mSERT)、修改(UPDATE)、删除(DELETE)以及这四种权限的总和(ALLPRIVILEGES)。对基本表的操作权限有：查询(SELECT)、插入(mSERT)、修改(UPDATE)、删除(DELETE)、修改表(ALTER)和建立索引(1NDEX)以及这六种权限的总和(ALLPRIVILEGES)。对数据库可以有建立表(CREATETAB)的权限:该权限属于DBA，可由DBA授予普通用户，普通用户拥有此权限后可以建立基本表，基本表的属主(Owner)拥有对该表的一切操作权限。接受权限的用户可以是一个或多个具体用户，也可以是PUBLIC，即全体用户。,.,如果指定了WITHGRANTOPTION子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。如果没有指定WITHGRANTOPTION子句，则获得某种权限的用户只能使用该权限，但不能传播该权限。,.,.,.,.,362收回权限,授予的权限可以由DBA或其他授权者用REVOKE语句收回，REVOKE语句的一般格式为：,.,.,3.7嵌入式SQL,将SQL语言嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补SQL语言实现复杂应用方面的不足。这种方式下使用的SQL语言称为嵌入式SQL(EmbeddedSQL)，而嵌入SQL的高级语言称为主语言或宿主语言。371嵌入式SQL的一般形式对宿主型数据库语言SQL，采用两种方法处理:一种是预编译一种是修改和扩充主语言使之能处理SQL语句。,.,SQL语句都必须加前缀EXECSQLSQL语句的结束标志则随主语言的不同而不同例：在PL1和C中以分号(；)结束：EXECSQL；在COBOL中以END-EXEC结束：EXECSQLEND-EXEC嵌入到C程序中写作：EXECSOLDROPTABLEStudent；在宿主程序中SQL语句位置：（1）任何允许出现可执行的高级语言语句的地方；（2）任何允许出现说明性高级语言语句的地方，都可以写说明性SQL语句。,.,372嵌入式SQL语句与主语言之间的通信,数据库工作单元与源程序工作单元之间的通信主要包括：(1)SQL通信区：向主语言传递SQL语句的执行状态信息，使主语言能够据此信息控制程序流程，主要用SQL通信区（SQLCommunicationArea，简称SQLCA)实现；(2)主变量(HostVariable)：主语言向SQL语句提供参数，主要用主变量(HostVariable)实现；(3)游标(Cursor)：将SQL语句查询数据库的结果交主语言进一步处理，主要用主变量和游标(Cursor)实现。,.,一、SQL通信区,SQL通信区（SQLCA）内容：SQL语句执行后，系统要反馈给应用程序若干信息（系统当前工作状态和运行环境的各种数据）SQLCA是一个数据结构，用EXECSQLINCLUDESQLCA加以定义SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE。应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理。SQLCODE（常量SUCCESS，则表示SQL语句成功）,.,例如，在执行删除语句DELETE后，不同的执行情况SQLCA中有下列不同的信息：成功删除，并有删除的行数(SQLCODE=SUCCESS)无条件删除警告信息违反数据保护规则，拒绝操作没有满足条件的行，一行也没有删除由于各种原因，执行出错,.,二、主变量,嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据，把SQL语句中使用的主语言程序变量简称为主变量。分输入主变量和输出主变量输入主变量由应用程序对其赋值，SQL语句引用；输出主变量由SQL语句对其赋值或设置状态信息，返回给应用程序。一个主变量有可能既是输入主变量又是输出主变量。,.,指示变量是一个整型变量，用来“指示”所指主变量的值或条件。指示变量可以指示输入主变量是否为空值，可以检测输出主变量是否空值，值是否被截断。所有主变量和指示变量必须在SQL语句BEGINDECLARESECTIONENDDECLARESECTION之间进行说明。,.,SQL语句中的主变量名前要加冒号(：)作为标志。同样，SQL语句中的指示变量前也必须加冒号，并且要紧跟在所指主变量之后。而在SQL语句之外，主变量和指示变量均可以直接引用，不必加冒号。,.,三、游标,游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果；每个游标区都有一个名字；用户可以通过游标逐一获取记录，并赋给主变量，交由主语言进一步处理。,.,四、程序实例,.,.,.,3.7.3不用游标的SQL语句,不用游标的SQL语句有：说明性语句数据定义语句数据控制语句查询结果为单记录的SELECT语句非CURRENT形式的UPDATE语句非CURRENT形式的DELETE语句INSERT语句,.,一、说明性语句,说明性语句是专为在嵌入式SQL中说明主变量等而设置的，主要有两条:EXECSQLBEGINDECLARESECTIONEXECSQLENDDECLARESECTION,.,二、数据定义语句,例1建立一个“学生”表Student。(1)EXECSQLCREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)，SsexCHAR(1)，SageINT，SdeptCHAR(15)；(2)EXECSQLDROPTABLEStudent;,.,三、数据控制语句,例2把查询Smdent表权限授给用户U1。EXECSQLGRANTSELECTONTABLEStudentTOUl,.,四、查询结果为单记录的SELECT语句,.,.,.,五、非CURRENT形式的UPDATE语句,.,.,.,六、非CURRENT形式的DELETE语句,.,.,七、INSERT语句,.,374使用游标的SQL语句,必须使用游标的SQL语句有：查询结果为多条记录的SELECT语句CURRENT形式的UPDATE语句CURRENT形式的DELETE语句,.,一、查询结果为多条记录的SELECT语句,使用游标的步骤为：(1)说明游标。用DECLARE语句为一条SELECT语句定义游标。DECLARE语句的一般形式为：EXECSQLDECLARECURSORFOR；,.,(2)打开游标。用OPEN语句将定义的游标打开。OPEN语句的一般形式为：EXECSQLOPEN；,.,(3)推进游标指针并取当前记录。用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。FETCH语句的一般形式为：,.,(4)关闭游标。用CLOSE语句关闭游标，释放结果集占用的缓冲区及其他资源。CLOSE语句的一般形式为：EXECSQLCLOSE；例1查询某个系全体学生的信息。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中。,.,.,.,.,.,.,.,二、CURRENT形式的UPDATE语句和DELETE语句,(1)用DECLARE语句说明游标。如果是为CURRENT形式的UPDATE语句作准备，则SELECT语句中要用FORUPDATEOF用来指明检索出的数据在指定列是可修改的。如果是为CURRENT形式的DELETE语句作准备，则不必使用上述子句。(2)用OPEN语句打开游标，把所有满足查询条件的记录从指定表取到缓冲区中。,.,(3)用FETCH语句推进游标指针，把当前记录从缓冲区中取出来送至主变量。(4)检查该记录是否是要修改或删除的记录。如果是，则用UPDATE语句或DELETE语句修改或删除该记录。这时UPDATE语句和DELETE语句中要用于句WHERECURRENTOF来表示修改或删除的是最近一次取出的记录，即游标指针指向的记录(5)处理完毕用CLOSE语句关闭游标，释放结果集占用的缓冲区和其他资源。,.,.,.,.,.,.,.,.,.,375动态SQL简介,静态SQL语句：语句中主变量的个数与数据类型在预编译时都是确定的，只有主变量的值是程序运行过程中动态输入的，称这类嵌入式SQL语句为静态SQL语句。,如果在预编译时下列信息不能确定，就必须使用动态SQL技术(1)SQL语句正文(2)主变量个数(3)主变量的数据类型：(4)SQL语句中引用的数据库对象(例如列、索引、基本表、视图等),.,动态SQL方法允许在程序运行过程中临时“组装”SQL语句，主要有三种形式：1语句可变允许用户在程序运行时临时输入完整的SQL语句。2条件可变对于非查询语句，条件子句有一定的可变性。3。数据库对象、查询条件均可变,.,3.8ADO.NET,.,1、名空间引用usingSystem;usingSystem.Windows.Forms;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.OleDb;,.,2、定义对象privateSqlConnectionmySqlConn;privateSqlCommandmySqlCommand1;privateSqlDataAdapter