数据库5版讲稿第四章-高级SQL.ppt

DATABASE SYSTEM CONCEPTS,第4章 高级SQL,2023/11/14,2,数据库系统概念-SQL,4.2 完整性约束,完整性数据的正确性和相容性完整性检查DBMS必须提供一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据是正确的，避免非法的不合语义的错误数据的输入和输出，即所谓的“垃圾进垃圾出”(Garbage In Garbage Out)所造成的无效操作和错误结果,2023/11/14,3,数据库系统概念-SQL,完整性约束,完整性子系统数据库的非法更新数据本身是错误的数据原来是正确的，操作或程序错误导致输入数据错误由于系统故障，导致数据错误事务的并发执行产生不正确结果人为故意破坏完整性子系统功能监督事务执行，检查是否违反完整性规则如有违反，采取相应措施（拒绝、报告、改正）,2023/11/14,4,数据库系统概念-SQL,完整性控制,完整性规则完整性规则集由DBA或程序员事先提供的有关数据约束的一组规则规则的构成约束条件：要检查什么样的错误（性别为男女）触发条件：什么时候使用规则进行检查（插入元组）ELSE子句：若检查出错误，该怎样进行处理（显示错误）规则的分类域完整性规则：属性取值范围域联系规则：在一个或多个关系中,属性间的联系影响约束关系完整性规则：更新操作对数据库中值的影响和限制,2023/11/14,5,数据库系统概念-SQL,完整性控制,在属性值上的约束非空约束：要求某属性取值不能为空值SNAME CHAR(8)NOT NULL基于属性的检查子句CHECK(AGE15)域约束子句用CREATE DOMAIN定义域时，可以出现 CHECKCREATE DOMAIN AGE SMALLINT CHECK(VALUE=15)AND VALUE=25),2023/11/14,6,数据库系统概念-SQL,完整性控制,主码约束主码值不允许空，也不允许出现重复意义：关系对应到现实世界中的实体集，元组对应到实体，实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不容许的主码定义形式主码子句：PRIMARY KEY(Sno)主码短语：Sno CHAR(4)PRIMARY KEY,2023/11/14,7,数据库系统概念-SQL,完整性控制,外码约束（参照完整性）关系R中的一个属性组，它不是R的码，但它与另一个关系S的码相对应，则称这个属性组为R的外部码，如 S关系中的Dno属性如果关系R2的外部码Fk与关系R1的主码Pk相对应，则R2中的每一个元组的Fk值或者等于R1 中某个元组的Pk 值，或者为空值意义：如果关系R2的某个元组t2参照了关系R1的某个元组t1，则t1必须存在例如关系S在Dno上的取值有两种可能 空值，表示该学生尚未分到任何系中；若非空值，则必须是DEPT关系中某个元组的Dno值，表示该学生不可能分到一个不存在的系中,2023/11/14,8,数据库系统概念-SQL,完整性控制,定义形式在SC表中，定义 FOREIGN KEY(Sno)REFERENCES S(Sno)Sno在SC中是外码，在S中是主码，Sno在SC中取值或为空或为S中某个Sno值作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系,2023/11/14,9,数据库系统概念-SQL,完整性控制,删除基本关系元组RESTRICT方式只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作CASCADE方式将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除SET NULL方式删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值如FOREIGN KEY(Sno)REFERENCES S(Sno)ON DELETE CASCADE|SET NULL,2023/11/14,10,数据库系统概念-SQL,完整性控制,修改基本关系主码RESTRICT方式只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改CASCADE方式将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值SET NULL方式：修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值如FOREIGN KEY(Sno)REFERENCES S(Sno)ON UPDATE CASCADE|SET NULL,2023/11/14,11,数据库系统概念-SQL,完整性控制,全局约束全局约束涉及多个属性间的或多个关系间的联系CREATE TABLE SC(Sno CHAR(4),Cno CHAR(4),GRADE SMALLINT,PRIMARY KEY(Sno,Cno),CHECK(Sno IN(SELECT Sno FROM S),CHECK(Cno IN(SELECT Cno FROM C),S中删除元组，不会触发CHECK子句，只有对SC表的更新才会触发,2023/11/14,12,数据库系统概念-SQL,完整性控制,对约束的命名、撤消和添加命名CONSTRAINT 约束名 示例Sno CHAR(4)CONSTRAINT S_PK PRIAMRY KEYAGE SAMLLINT CONSTRAINT AGE_VAL CHECK(AGE=15 AND AGE=25),2023/11/14,13,数据库系统概念-SQL,完整性控制,关系上约束的撤消与添加撤消用alter drop添加用alter add示例alter table S drop constraint S_PKalter table SC add constraint SC_CHECK check(Sno in select Sno from S),2023/11/14,14,数据库系统概念-SQL,完整性控制,域约束的创建、撤消与添加create domain AGE_DOMAIN smallintconstraint DC_AGE check(value=15)alter domain AGE_DOMAINadd constraint DC_AGE check(value=15)alter domain AGE_DOMAINdrop constrain DC_AGE,2023/11/14,15,数据库系统概念-SQL,完整性控制,关系上约束的撤消与添加撤消用alter drop添加用alter add示例alter table S drop constraint S_PKalter table SC add constraint SC_CHECK check(Sno in select Sno from S),2023/11/14,16,数据库系统概念-SQL,完整性控制,域约束的创建、撤消与添加create domain AGE_DOMAIN smallintconstraint DC_AGE check(value=15)alter domain AGE_DOMAINadd constraint DC_AGE check(value=15)alter domain AGE_DOMAINdrop constrain DC_AGE,2023/11/14,17,数据库系统概念-SQL,完整性控制,断言定义：CREATE ASSERTION CHECK 断言是谓词，表达数据库总应该满足的条件一旦定义了断言，系统验证其有效性，并且对每个可能违反该断言的更新操作都进行检查这种检查会带来巨大的系统负载，因此应该谨慎使用断言对断言“所有X,P(X)”，是通过检查“not exists X，P(X)”来实现的,2023/11/14,18,数据库系统概念-SQL,完整性控制,示例：不允许男同学选修张老师课程create assertion ASSE2 check(not exists(select*from SC where Cno in(select Cno from C where TEACHER=张)and Sno in(select Sno from S where SEX=M),2023/11/14,19,数据库系统概念-SQL,完整性控制,示例：每门课最多50名男同学选修create assertion ASSE1 check(50=all(select count(SC.Sno)from S,SC where S.Sno=SC.Sno and SEX=M group by Cno)撤消：drop assertion 断言名drop assertion ASSE1,2023/11/14,20,数据库系统概念-SQL,完整性控制,触发器：触发器是一条语句，当对数据库做修改时，它自动被系统执行触发器的定义指明什么条件下触发器被执行指明触发器执行的动作是什么触发器的作用示警满足特定条件时自动执行某项任务触发器事件Insert、delete、update,2023/11/14,21,数据库系统概念-SQL,完整性控制,2023/11/14,22,数据库系统概念-SQL,完整性控制,EMP(ENO,ENAME,SAL,JOB)职工工资增幅不得超过10%create trigger RAISE_LIMIT after update of SAL on EMPreferencing new row as nrow old row as orowfor each rowwhen(nrow.SAL 1.1*orow.SAL)begin atomicsignal SQLSTATE(7500,Salary increase 10%)end,2023/11/14,23,数据库系统概念-SQL,完整性控制,当帐户透支时，将帐户余额设为0，并建一笔贷款，其金额为透支额create trigger overdraft-trigger after update on accountreferencing new row as nrow for each rowwhen nrow.balance 0begin atomic insert into borrower(select customer-name,account-number from depositor where nrow.account-number=depositor.account-number);insert into loan values(nrow.account-number,nrow.branch-name,nrow.balance);update account set balance=0where account.account-number=nrow.account-numberend,2023/11/14,24,数据库系统概念-SQL,4.3 授权,安全性控制定义安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏。其基本措施是存取控制安全性措施物理级人际级操作系统级网络级数据库系统级,2023/11/14,25,数据库系统概念-SQL,数据控制功能,权限的转授和回收允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来权限图结点是用户，根结点是DBA，有向边UiUj，表示用户Ui把某权限授给用户Uj一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径,2023/11/14,26,数据库系统概念-SQL,数据控制功能,2023/11/14,27,数据库系统概念-SQL,数据控制功能,授权命令grant 表级权限 on 表名|视图名 to 用户，用户|public with grant option表级权限包括：select,update,insert,delete,index,alter,drop,resource以及它们的总和all，其中对select,update可指定列名with grant option表示获得权限的用户可以把权限再授予其它用户,2023/11/14,28,数据库系统概念-SQL,数据控制功能,示例grant select,insert on S to Liming with grant optiongrant all on S to public,2023/11/14,29,数据库系统概念-SQL,数据控制功能,回收权限revoke 表级权限 on 表名|视图名 from 用户，用户|public收回权限时，若该用户已将权限授予其它用户，则也一并收回。授权路径的起点一定是DBA示例revoke insert on S from Liming,2023/11/14,30,数据库系统概念-SQL,数据控制功能,思考一：grant可以基于列授权，当revoke这样的权限时，结果是什么？思考二：执行完下列语句的结果是什么？revoke all on S from publicgrant select on S to public,2023/11/14,31,数据库系统概念-SQL,数据控制功能,支持多库的数据库系统中授权对象可以是数据库grant 数据库级权限 to 用户，用户|public数据库级权限包括：connect：允许用户在database语句中指定数据库resource：connect权限+建表、删除表及索引权利dba：resource权限+授予或撤消其他用户的connect、resource、dba权限不允许dba撤消自己的dba权限,2023/11/14,32,数据库系统概念-SQL,提纲,SQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能嵌入式SQL,2023/11/14,33,数据库系统概念-SQL,4.4 嵌入式SQL,为什么使用嵌入式SQL？嵌入式SQL执行过程需要解决的几个问题,2023/11/14,34,数据库系统概念-SQL,为什么使用嵌入式SQL？,有些操作对于交互式SQL是不可能的任务SQL的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用SQL无法完成。一方面，SQL在逐渐增强自己的表达能力（参见习题4.10），另一方面，太多的扩展会导致优化能力及执行效率的降低非声明性动作实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现,2023/11/14,35,数据库系统概念-SQL,嵌入式SQL执行过程,2023/11/14,36,数据库系统概念-SQL,需要解决的几个问题,区分SQL语句与C语言语句嵌入的SQL语句以EXEC SQL开始，以分号(;)或END_EXEC结束EXEC SQL delete from PROF where DNO=10；,2023/11/14,37,数据库系统概念-SQL,需要解决的几个问题,嵌入SQL语句与C语言之间的数据传递宿主变量C变量，既可以用在C语句中，也可用在SQL语句中，用来在两者之间传递数据宿主变量的声明声明为通常的C变量，并将其放在下列标识语句之间EXEC SQL BEGIN DECLARE SECTIONEXEC SQL END DECLARE SECTION,2023/11/14,38,数据库系统概念-SQL,需要解决的几个问题,EXEC SQL BEGIN DECLARE SECTIONint prof_no;char prof_name30;int salary;EXEC SQL END DECLARE SECTION,2023/11/14,39,数据库系统概念-SQL,需要解决的几个问题,注：宿主变量出现于SQL语句中时，前面加（:）以区别列名 宿主变量可出现的地方：SQL的数据操纵语句中可出现常数的任何地方，select，fetch等语句的into子句中示例：EXEC SQL select PNAME,SAL into:prof_name,:salary from PROF where PNO=:prof_no;,2023/11/14,40,数据库系统概念-SQL,需要解决的几个问题,SQL与主语言之间操作方式的协调执行方式的差别SQL：一次一集合C语言：一次一记录游标在查询结果的记录集合中移动的指针若一个SQL语句返回单个元组，则不用游标若一个SQL语句返回多个元组，则使用游标,2023/11/14,41,数据库系统概念-SQL,需要解决的几个问题,不需要游标的数据操作结果是一个元组的select语句EXEC SQL select PNAME,SALinto:prof_name,:salary from PROFwhere PNO=:prof_no;,2023/11/14,42,数据库系统概念-SQL,需要解决的几个问题,insert语句 EXEC SQL insert into PROF values(:prof_no,:prof_name,:salary,:dept_no,:salary);delete语句 EXEC SQL delete from PROF where PNO:prof_no;update语句 EXEC SQL update PROF set SAL=:salary where PNO=:prof_no;,2023/11/14,43,数据库系统概念-SQL,需要解决的几个问题,需要游标的数据操作当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组活动集：select语句返回的元组的集合当前行：活动集中当前处理的那一行。游标即是指向当前行的指针,2023/11/14,44,数据库系统概念-SQL,需要解决的几个问题,定义与使用游标的语句declare定义一个游标，使之对应一个select语句 declare 游标名 cursor forselect语句for update of列表名for update：该游标可用于对当前行的修改删除open打开游标，执行对应的查询，结果集为该游标的活动集open 游标名,2023/11/14,45,数据库系统概念-SQL,需要解决的几个问题,fetch在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中fetch 游标名 into 宿主变量表close关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句close 游标名,2023/11/14,46,数据库系统概念-SQL,需要解决的几个问题,推进游标指针EXEC SQL FETCH INTO，.FETCH语句通常用在一个循环结构中，逐条取出结果集中的记录进行处理。许多关系数据库管理系统对FETCH语句做了扩充，允许向任意方向以任意步长移动游标指针。关闭游标EXEC SQL CLOSE释放缓冲区及其他资源。,2023/11/14,47,数据库系统概念-SQL,需要解决的几个问题,说明游标EXEC SQL DECLARE CURSOR FOR 其中SELECT语句可以是简单查询，也可以是复杂的连接查询和嵌套查询。打开游标EXEC SQL OPEN执行相应的SELECT语句，把查询记录取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中第一条记录。,2023/11/14,48,数据库系统概念-SQL,需要解决的几个问题,SQL语句执行信息反馈良好的应用程序必须提供对错误的处理,应用程序需要知道SQL语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息SQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行情况在其执行完成后写入SQLCA结构中的各变量中,根据SQLCA中的内容可以获得每一嵌入SQL语句执行后的信息,应用程序就可以做相应的处理在PB中，一个SQL语句执行结束后，SQLCA.SQLCODE=0，100，-1分别表示执行成功，未查找到符合条件的元组，执行出错,2023/11/14,49,数据库系统概念-SQL,PB中的游标,Declare cur_a cursor for Select a,b,c from;Open cur_a;If sqlca.sqlcode0 then returnDo while true Fetch cur_a into:va,:vb,:vc;If sqlca.sqlcode=100 then exit If sqlca.sqlcode=-1 then return LoopClose cur_a;If sqlca.sqlcode0 then return,2023/11/14,50,数据库系统概念-SQL,嵌入式 SQL 编程,嵌入式SQL语句与主语言之间的通信过程1数据库工作单元与源程序工作单元之间通信主要包括：向主语言传递SQL语句的执行状态信息，使主语言能够据此控制程序流程。主语言向SQL语句提供参数。将SQL语句查询数据库的结果交主语言进一步处理。,2023/11/14,51,数据库系统概念-SQL,嵌入式 SQL 编程,2嵌入式SQL与主语言之间通信的实现SQL语句通过SQL通信区SQLCA(SQL communiction area)向主语言传递SQL执行状态信息。主语言通过自己的主变量(host vaiable)向SQL语句输入数据参数。两者数据传送通过主变量和游标(crusor)实现。,2023/11/14,52,数据库系统概念-SQL,嵌入式 SQL 编程,3SQL通信区SQL语句执行后，系统将当前工作状态送到SQL通信区SQLCA中。应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句。主语言程序中用EXEC SQL INCLUDE SQLCA定义通信区。SQLCA中有一个返回代码的变量SQLCODE。执行完一条SQL语句后可以测试一下SQLCODE的值，如果是SUCCESS，则表示SQL语句成功，否则表示错误。,2023/11/14,53,数据库系统概念-SQL,嵌入式 SQL 编程,4主变量在SQL语句中使用的主语言程序的变量称为主变量。输入主变量由主语言程序对其赋值，SQL语句引用。输出主变量由SQL语句对其赋值，返回给主语言程序。一个主变量附带一个任选的指示变量,整型变量，指示主变量的值或条件。输入主变量可以利用指示变量赋空值，输出主变量可以利用指示变量检测出是否空值，值是否被截断。,2023/11/14,54,数据库系统概念-SQL,嵌入式 SQL 编程举例,例 查询某个系全体学生的信息。系名在主程序中指定。EXEC SQL INCLUDE SQLCA EXEC SQL BEGIN DECLARE SECTION CHAR deptname(20)CHAR HSno(6)CHAR HSnmae(8)CHAR HSsex(2),2023/11/14,55,数据库系统概念-SQL,嵌入式 SQL 编程举例,CHAR HSdept(20)INT HSageEXEC SQL END DECLARE SECTION;main()WHILE(gets(deptname!=NULL),2023/11/14,56,数据库系统概念-SQL,嵌入式 SQL 编程举例,EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Sdept=:deptname EXEC SQL OPEN SX WHILE(1)EXEC SQL FETCH SX INTO,2023/11/14,57,数据库系统概念-SQL,嵌入式 SQL 编程举例,:HSno,:HSname,:HSsex,:Hsage,:HSdept if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsage,HSdept);,2023/11/14,58,数据库系统概念-SQL,嵌入式 SQL 编程举例,EXEC SQL CLOSE SX;End;例 修改学生的年龄。.main()Char yn(1);Int Newage;,2023/11/14,59,数据库系统概念-SQL,嵌入式 SQL 编程举例,gets(deptname);EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student WHERE Sdept=:deptname FOR UPDATE OF Sage;EXEC SQL OPEN SX;WHILE(1),2023/11/14,60,数据库系统概念-SQL,嵌入式 SQL 编程举例,EXEC SQL FETCH SX INTO:HSno,:HSname,:HSsex,:Hsage,:HSdept if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsage,HSdept);printf(“修改该学生的年龄？”);,2023/11/14,61,数据库系统概念-SQL,嵌入式 SQL 编程举例,scanf(“%c”,2023/11/14,62,数据库系统概念-SQL,嵌入式 SQL 编程举例,;EXEC SQL CLOSE SX;End;UPDATE语句分为CURRENT形式和非CURRENT形式，非CURRENT形式是集合操作，一次修改所有满足条件的记录。CURRENT形式只修改其中某个记录。,2023/11/14,63,数据库系统概念-SQL,嵌入式 SQL 编程举例,例 删除某些学生的记录。.EXEC SQL DECLARE SX CURSOR FOR SELECT Sno,Sname,Ssex,Sage FROM Student WHERE SDept=:deptname;EXEC SQL OPEN SX WHILE(1),2023/11/14,64,数据库系统概念-SQL,嵌入式 SQL 编程举例,EXEC SQL FETCH SX INTO:HSno,:HSname,:HSsex,:HSage，:HSdept;if(sqlca.sqlcodeSUCCESS)break;printf(%s,%s,%s,%d,%s,HSno,HSname,HSsex,Hsage,HSdept)printf(删除该记录?);,2023/11/14,65,数据库系统概念-SQL,嵌入式 SQL 编程举例,scanf(%c,.,