SQL系统环境.doc

第8章 SQL系统环境本章主要讨论数据库的设计步骤以及每个步骤用到的方法。数据库的设计主要有四个步骤：需求分析、概念设计、逻辑设计和物理设计。本章的主要知识点包括：学习要点1、嵌入式SQL学习要点2、有关事物的嵌入式SQL学习要点3、SQL环境学习要点4、数据库的完整性学习要点5、SQL中的安全和用户权限学习要点1、嵌入式SQL1、什么是嵌入式SQL当一个程序既要访问数据库，又要处理数据时，把SQL语言嵌入程序设计语言即宿主语言中，将SQL语言访问数据库的功能和宿主语言的数据处理功能相结合，是目前解决该问题的最有效途径。这样使用的SQL称为嵌入式SQL。2、如何实现嵌入式SQL对于不同的DBMS和宿主语言，实现嵌入式SQL的方法基本相同，只是在一些特殊的地方略有差异。实现嵌入式SQL语言主要是实现说明部分的嵌入和执行部分的嵌入。以SQL嵌入C语言为例说明实现嵌入式SQL的方法。说明部分C语言和SQL语言之间是通过共享变量来进行数据的传送。共享变量是由宿主语言程序定义、用SQL的DECLARE语句说明，以后可在SQL语句中引用的宿主语言变量。共享变量需用以“EXEC SQL ”为前缀的说明语句说明，在说明语句的结尾加“；”。在SQL语句中引用共享变量时，每个共享变量前要加“：”。在共享变量中，有一个系统定义的特殊变量，称为SQLSTATE。在每执行一个SQL语句时，都要返回一个SQLSTATE代码，来表示这个SQL语句是否执行成功等情况。当SQLSTATE为全零时，表示SQL语句执行成功；当SQLSTATE为“02000”时，表示SQL语句已执行，但未找到元组。所有SQL语句中用到的共享变量，除系统定义的（例如SQLSTATE）以外，都必须在宿主程序中进行说明。说明语句的开头行为：EXEC SQL BEGIN DECLARE SECTION；说明语句的结束行为：EXEC SQL END DECLARE SECTION；在一个说明语句中定义几个共享变量，它们都是按照C语言的数据类型和格式进行定义的。EXEC SQL BEGIN DECLARE SECTION；char sno6,sname20；int sage；char SQLSTATE6；EXEC SQL END DECLARE SECTION；执行部分插入语句的格式为：EXEC SQL INSERTINTO Student (StudentNo，StudentName)VALUES (:sno, :sname)；修改语句的格式为：EXEC SQL UPDATE Student SET (StudentName=：sname，StudentAge=：sage)WHERE StudentNo=：sno；删除语句的格式为：EXEC SQL DELETE FROM StudentCourseWHERE StudentNo=(SELECT StudentNo FROM StudentWHERE StudentName=：sname）； 查询语句：当查询的结果只有一个元组时，EXEC SQL SELECT StudentName，StudentDept INTO ：sname，：sdept FROM StudentWHERE StudentNo=：sno；当查询的结果有多个元组时，需采用涉及到游标的查询方法。 3、使用游标的SQL编程利用游标进行查询需要四种语句：说明游标语句、打开游标语句、推进语句和关闭游标语句。分析学生成绩的分布情况。首先从StudentCourse中取出每个元组的Grade；其次设置游标scCursor复盖这些元组；然后打开游标，使之处于初始位置；随后，推进游标，取出一个单分量元组放入共享变量grade；接着，确定该成绩属于哪一段，并在相应的统计上加1；不断推进游标，重复上述统计过程，直到元组全部取完，再关闭游标；最后打印统计结果。举例：假设分析学生成绩的分布情况，把统计结果划分为11段：09，1019，20299099，100。首先从StudentCourse中取出每个元组的Grade；其次设置游标scCursor复盖这些元组；然后打开游标，使之处于初始位置；随后，推进游标，取出一个单分量元组放入共享变量grade；接着，确定该成绩属于哪一段，并在相应的统计上加1；不断推进游标，重复上述统计过程，直到元组全部取完，再关闭游标；最后打印统计结果。程序如下：#define NO-TUPLE !(strcmp(SQLSTATE,02000)void gradeSection() int i,section,count11；EXEC SQL BEGIN DECLARE SECTION；int grade；char SQLSTATE6；EXEC SQL END DECLARE SECTION；EXEC SQL DECLARE scCursor CURSOR FORSELECT Grade FROM SC；EXEC SQL OPEN scCursor；for(i=0;i11;i+) counti=0；while(1)EXEC SQL FETCH FROM scCursor INTO:grade；if (NO-TUPLE) break；section=grade/10；countsection+；EXEC SQL CLOSE scCursor；for(i=0;i11;i+)coutSection=i+1 number=countin； ；学习要点2、有关事物的嵌入式SQL在集中选课的时间里，学生可以在能连接到数据库上的成百上千台计算机上同时对一个数据库进行操作。在同一时间里，数据库中完全有可能存在相互影响的两个或者更多的操作。如果对这些操作的顺序没有限制，那么它们之间的相互影响就有可能产生错误。1、事务假定某门课的选课余额为1，这时，同时有两名学生进行了上面的查询。结果两名学生都选上了这门课，并分别把选课余额由1修改为0。把一个选课的名额送给两个学生显然是不妥当的。解决这个问题的方式就是当一个函数正在执行的时候，限制另一个函数，不能让它也执行。这种方式执行的两个函数是串行的。SQL中的机制就是使两个函数的执行实现串行化。EXEC SQL BEGIN DECLARE SECTION;int cNo;char cName20; int nLeft;EXEC SQL END DECLARE SECTION;void ChooseCourse()cout cNo；EXEC SQL SELECT numLeft INTO :nLeft FROM CourseWHERE courseNo=:cNo;if(nLeft0)EXEC SQL UPDATE CourseSET numLeft=numLeft-1WHERE courseNo=:cNo;cout “你已经选定了这门课。”; EXEC SQL COMMIT; else cout acct1 acct2 amount;EXEC SQL SELECT b INTO :b1FROM AccountsWHERE acctNo=:acct1;if(b1=account)EXEC SQL UPDATE AccountsSET b=b-:amount20）WHERE acctNo=:acct1;EXEC SQL UPDATE AccountsSET b=b+:amountWHERE acctNo=:acct2; EXEC SQL COMMIT; else cout ” 没有足够结余“; EXEC SQL ROLLBACK;我们假定在程序执行完20）行的时候发生了故障。我们只知道这时数据库的状态：在第一个帐户中，已经扣除了要转的这笔金额，而这笔金额并没有加到第二个帐户中去。用户在这个过程中损失掉了这笔钱。数据库操作的某些组合需要以原子的方式完成，也就是说，它们或者都做，或者都不做。一个通常的解决方式是让对数据库的所有改变在本地的工作区完成，并且只有当所有的工作完成以后才把这种改变提交给数据库。两种结束事务的方法当所有操作都正确合理地执行完毕之后，使用SQL语句COMMIT来提交做过的操作。如果事务异常中止，则使用SQL语句ROLLBACK。学习要点3、SQL环境1、数据库元素的层次结构 通常可以把运行在某个设备上的数据库管理系统看作是SQL的环境。我们讨论过的数据库元素，比如基本表、视图，都是在SQL环境中定义的。这些元素组成了层次结构。（图见81.ppt）模式（Schema）：是指数据库模式，而不是关系模式，是表、视图、域等数据库元素的聚集。模式是整个体系结构的基本单位。一个模式，包括学生、课程和学生选课等三个关系以及计算机系学生视图，其说明简要描述如下：CREATE SCHEMA StudentSchemaCREATETABLE StudentCREATE TABLE CourseCREATE TABLE StudentCourseCREATE VIEW ComputerDeptStud我们要把学生模式作为当前模式，可如下设置：SET SCHEMA StudentSchema；于是，随后定义的表、视图等模式元素均属于学生模式。目录（Catalog）：是模式的聚集，即每个目录都有一个或多个模式。在一个目录中模式的名字必须是唯一的。每个目录都包含一个称为INFORMATION-SCHEMA（信息模式）的特殊模式，该模式包含该目录中所有模式的信息。在目录中建立模式类似于在模式中建立表或视图之类的模式元素。可以设想用如下语句建立学生目录：CREATE CATALOG StudentCatalog后面跟着属于学生目录的模式及模式的说明。可以用SET CATALOG（设置目录）语句设置当前目录。我们要把学生目录作为当前目录，可如下设置：SET CATALOG StudentCatalog;于是，随后定义的模式将属于学生目录。 群集（Cluster）：是目录的聚集。每个用户都有相关的群集，也就是该用户可以访问的所有目录的集合。群集是数据库可以操作的最大范围，因此，对于特定的用户，群集就是“数据库”。2、客户程序和服务程序系统SQL的客户程序和服务程序与连接、会话和模块这三个概念紧密地联系在一起。连接是指把客户程序和服务程序连接起来。可用如下的SQL连接语句在客户程序和服务程序之间打开连接：CONNECT TO AS 一般把服务器名作为服务程序名。会话：当连接激活时，所执行的SQL操作将在客户程序和服务程序之间形成请求响应式的交往，我们称这种操作序列为会话（Session）。会话与建立它的连接处于同一状态。 模块：SQL2标准建议SQL实现至少为用户提供如下三种类型的应用程序（称为模块，Module）之一：通用SQL接口嵌入式SQL实际模块3、Client/Server体系结构Client/Server体系结构如图所示。（如图82.ppt所示）客户机和服务器的执行过程如图所示。（如图83.ppt所示）学习要点4、数据库的完整性数据库运行时，应防止输入或输出不符合语义的错误数据，而始终保持其中数据的正确性。这就是通常所说的数据库的完整性（Integrity）。1、说明键码约束数据库中最重要的约束就是说明某个属性或属性集构成关系的键码。这意味着对于关系的两个元组，不允许在说明为键码的属性上一致，或者不允许在共同构成键码的属性集的所有属性上一致。键码约束在SQL的建表语句中说明。假设学生都不重名，则可用姓名作为学生关系的键码，而把学号作为主键码。建表语句如下：CREATE TABLE Student(StudentNo INT PRIMARY KEY,StudentName CHAR(30) UNIQUE,StudentAge INT,StudentGender CHAR(1); 在属性上建立索引，除了用CREATE INDEX语句之外，还可加上关键字UNIQUE，从而在为属性建立索引的同时将其说明为键码。比如，用如下语句既为学生姓名建立索引，又为之说明了唯一值约束：CREATE UNIQUE INDEXStudentNameIndex ON Student(StudentName); 2、说明外键码约束 可以把一个关系的属性或属性集说明为外键码（Foreign Key）。一是指引用另外一个关系的某个属性或属性集，而所引用的属性或属性集必须是所在关系的主键码。二是指说明为外键码的属性值必须是所引用的关系的某个相应属性值。 对于学生选课关系，可用如下语句说明：CREATE TABLE StudentCourse(StudentNo INT,CourseNo INT,Grade SMALLINTPRIMARY KEY(StudentNo,CourseNo),FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo),FOREIGN KEY (CourseNo) REFERENCES Course(CourseNo),); 3、说明检验约束检验（CHECK）约束是除了键码约束和外键码约束之外的第三种重要的约束。检验约束既可用于属性，也可用于元组。基于属性的检验约束可先说明域（DOMAIN）的约束：CREATE DOMAIN GenderDomain CHAR(1)CHECK(VALUE IN (F,M);属性gender可用如下形式来说明：gender GenderDomain，基于元组的检验约束 基于元组的检验约束就是对单个关系中的元组给予某种限制。当用CREATE TABLE语句建表时，要想说明对关系元组的检验约束，可在属性表和键码、外键码的说明之后加上关键字CHECK和约束条件。4、更新约束可以在任何时候更新约束，即增加、更改或撤消约束。对约束命名在约束之前加上关键字CONSTRAINT（约束）和该约束名。把表明属性name为主键码的约束命名为NameIsKey，其说明如下：name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY 更新约束更改约束：用ALTER语句。ALTER TABLE即用于基于属性的检验，也用于基于元组的检验。ALTER TABLE语句有几个选项，使之既可用于撤消约束，也可用于增加约束。撤消约束：用关键字DROP随后加上要撤消的约束名。比如，要把表明name为关系Actor的主键码这一约束撤消，可用如下语句：ALTER TABLE Actor DROP CONSTRAINT NameIsKey； 增加约束：用关键字ADD随后加上要增加的约束名。比如，要增加一个约束以表明name为关系Actor的主键码，可用如下语句：ALTER TABLE Actor ADD CONSTRAINT NameIsKeyPRIMARY KEY(name); 5、触发与触发程序触发（Trigger）有时也称为事件条件动作规则（EventConditionAction Rule）或ECA规则。触发程序的选项与结构（1）规定的动作与触发事件之间的关系1）AFTER 2）BEFORE3）INSTEAD OF常用的触发事件的类型1）UPDATE2）INSERT3）DELETE用元组变量引用元组1）触发事件为UPDATE2）触发事件为INSERT3）触发事件为DELETE触发程序分类触发程序可分为元组级触发程序和语句级触发程序两类。举例：假设学生的英语考试分为初试和复试。初试后的成绩存入学生选课关系SC。若复试成绩比初试成绩好，则按复试成绩修改原有成绩，否则不应修改。CREATE TRIGGER GTriggerAFTER UPDATE OF G ON SCREFERENCINGOLD AS OldTuple,NEW AS NewTupleWHEN(OldTuple.GNewTuple.GAND NewTuple.Cno=012)UPDATE SCSET G=OldTuple.GWHERE Sno=NewTuple.Sno AND Cno=NewTuple.CnoFOR EACH ROW假设在安排课程时发现某门课选课人数过少而暂停开设，且把选课的学生并入同类的另一门课，于是要在学生选课关系SC中修改课称号。若修改课程号而使某门课的选课人员超过150人，则不应修改。CREATE TRIGGER CTriggerINSTEAD OF UPDATE OF CNo ON SCREFERENCINGOLD_TABLE AS OldSetNEW_TABLE AS NewSetWHEN (150=ALL( SELECT COUNT(SNo)FROM( SC EXCEPT OldSet)UNION NewSet) GROUP BY CNo )DELETE FROM SCWHERE (SNo,CNo,G) IN OldSet;INSERT I