数据库原理第三讲

1、数据库原理及应用教案数据库原理及应用教案计算机科学学院计算机科学学院第3章 数据库的标准语言SQLn3.1 SQL概述n3.2 数据定义n3.3 数据查询n3.4 数据更新n3.5 SQL的完整性控制n3.6 SQL中的触发器n3.7 嵌入式SQL第3章 数据库的标准语言SQLnSQL（Structrued Query Language）早已确立自己作为关系数据库标准语言的地位，已被众多商用 DBMS 产品所采用， 使得它已成为关系数据库领域中一个主流语言。 它不仅包含了数据查询功能，还包括插入、删除、更新和数据定义功能。作为一个 SQL数据库是表的汇集，它用一个或多个 SQL模式定义。作为

2、SQL的用户可以是应用程序，也可以是终端用户。31 SQL概述3.1.1 SQL语句的特征n尽管人们习惯性地称SQL是一个 “查询语言 ”，但实际上，它的功能远非查询信息这么简单。主要包括:n数据查询（Query）n数据操纵（Manipulation）n数据定义（Definition）n数据控制(Control)1SQL的特点n（1）综合统一n（2）高度非过程化n（3）面向集合的操作方式n（4）二种使用方式n（5）语言简洁、易学易用1SQL的特点nSQL 语言功能极强，完成核心功能只用了9个动词，包括如下四类:n数据查询:SELECTn数据定义:CREATE、DROP、ALTERn数据操纵:I

3、NSERT、UODATE、DELETEn数据控制:GRANT、REVORK。nSQL语言支持关系数据库的三级模式结构，其中：n视图对应外模式n基本表对应模式n存储文件对应内模式n具体结构如图3-1所示。2SQL支持三级模式结构 SQLView（视图）（视图）1View（视图）（视图）2Base Table 1（基本表）（基本表）Base Table2（基本表）（基本表）Base Table 3（基本表）（基本表）Base Table4（基本表）（基本表）Sored File 1（存储文件）（存储文件）Sored File 2（存储文件（存储文件）Sored File 3（存储文件）（存储文件）

4、内模式内模式图图3-1 关系数据库的三级模式结构关系数据库的三级模式结构模式模式外模式外模式 3.1.2 SQL的基本组成 nSQL由以下几个部分组成:n(1)数据定义语言(DDL)：提供定义关系模式和视图、删除关系和视图、修改关系模式的命令。n(2)交互式数据操纵语言(DML)：提供查询、插入、删除和修改的命令。n(3)事务控制(transaction control)：SQL提供定义事务开始和结束的命令。SQL组成部分n(4)嵌入式SQL和动态SQL（embedded SQL and dynamic SQL）用于嵌入到某种通用的高级语言中混合编程。其中 SQL 负责操纵数据库，高级语言负责

5、控制程序流程。n(5)完整性(integrity)：SQL DDL 包括定义数据库中的数据必须满足的完整性约束条件的命令，对于破坏完整性约束条件的更新将被禁止。n(6)权限管理(authorization)：SQL DDL中包括说明对关系和视图的访问权限。 32 数据定义n基本表和视图都是表。基本表是实际存储在数据库中的表。视图是虚表，它是从基本表或其它视图中导出的表。数据库中只存放视图的定义而不存放视图的数据。这些数据仍存放在导出视图的基本表中。用户可用SQL 语句对基本表和视图进行查询等操作。一个表可以带若干索引，索引也存储在存储文件中。每个存储文件就是外部存储器上一个物理文件，存储文件的

6、逻辑结构组成了关系数据库的内模式。nSQL的数据定义包括对表、视图、索引的创建和删除。 3.2.1创建表 (CREATE TABLE)n语句格式:nCREATE TABLE (列级完整性约束条件， 列级完整性约束条件，)；3.2.1创建表 (CREATE TABLE)n列级完整性约束条件有:nNULL（空）nUNIQUE（取值唯一），如NOT NULL UNIQUE 表示取值唯一，不能取空值。举例 【例3.1】建立一个供应商、零件数据库。其中“供应商”表S(Sno，Sname，Status，City)分别表示:供应商代码、供应商名、供应商状态、供应商所在城市。“零件”表P(Pno，Pname，

7、Color，Weight，City)，表示零件号、零件名、颜色、重量及产地。其中，数据库要满足如下要求:n(1)供应商代码不能为空，且值是唯一的，供应商的名也是唯一的。n(2)零件号不能为空，且值是唯一的。零件名不能为空n(3)一个供应商可以供应多个零件，而一个零件可以由多个供应商供应。举例n分析：根据题意供应商和零件分别要建立一个关系模式。供应商和零件之间是一个多对多的联系，在关系数据库中，多对多联系必须生成一个关系模式，而该模式的码则是由该联系两端实体的码加上联系的属性构成的，若该联系名为SP，那麽关系模式为SP(Sno，Pno，Qty)，其中Qty表示零件的数量。n根据上述分析，用SQL

8、建立一个供应商、零件数据库如下:建立供应商、零件数据库 CREATE TABLE S(Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(30) UNIQUE， Status CHAR(8) ， City CHAR(20) PRIMARY KEY（Sno）; CREATE TABLE P(Pno CHAR(6)， Pname CHAR(30) NOT NULL， Color CHAR(8)， Weight NUMERIC (6，2)， City CHAR(20) PRIMARY KEY(Pno);建立供应商、零件数据库 CREATE TABLE SP(Sno CHA

9、R(5)， Pno CHAR(6)， Status CHAR(8) ， Qty NUMERIC（9）， PRIMARY KEY（Sno，Pno） FOREIGN KEY（Sno） REFERENCES S（Sno）， FOREIGN KEY（Pno） REFERENCES P（Pno）；举例n从上述定义可以看出，“Sno CHAR(5) NOT NULL UNIQUE”语句定义了Sno的列级完整约束条件，取值唯一，不能取空值，需要说明如下:n(1)PRIMARY KEY（Sno）已经定义了Sno为主码，所以，“ Sno CHAR(5) NOT NULL UNIQUE”语句中的“NOT NULL

10、 UNIQUE”可以省略。 n(2)“ FOREIGN KEY（Sno）REFERENCES S（Sno)”定义了在SP关系中Sno为外码，其取值必须来自S关系中的Sno域。同理在SP关系中Pno也定义为外码。 3.2.2 修改表和删除表n1修改表（ALTER TABLE）n语句格式:nALTER TABLE ADD完整性约束条件 DROPMODIFY ；举例n例如，向“供应商”表S增加Zap“邮政编码”可用如下语句:nALTER TABLE S ADD Zap CHAR(6)；n注意，不论基本表中原来是否已有数据，新增加的列一律为空。n又如，将Status字段改为整型可用如下信息:nALTE

11、R TABLE S MODIFY Status INT；2删除表(DROP TABLE)n语句格式:DROP TABLE n例如，执行DROP TABLE Student；此后关系 Student不再是数据库模式的一部分，关系中的元组也无法访问。 3.2.3定义和删除索引n在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。数据库中的索引是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 3.2.3定义和删除索引n索引的作用如下:n通过创建唯一索引，可以保证数据记录的唯一性。n可以大大加快数据检索速度。n可以加速表与表之间的连接，这一点

12、在实现数据的参照完整性方面有特别的意义。n在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。n使用索引可在检索数据的过程中使用优化隐藏器，提高系统性能。1聚集索引和非聚集索引n聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。 n非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。 2建立索引n语句格式:nCREATE UNIQUECLUSTER INDEX ON （ ，）

13、； 2建立索引n说明:n(1)次序:可选ASC（升序）或DSC（降序），默认值为ASC。n(2)UNIQUE:表明此索引的每一个索引值只对应唯一的数据记录。n(3)CLUSTER:表明要建立的索引是聚簇索引，意为索引项的顺序是与表中记录的物理顺序 一致的索引组织。举例 【例3.2】 假设供应销售数据库中有供应商S、零件P、工程项目J、供销情况SPJ关系，希望建立四个索引。其中:供应商S中Sno按升序建立索引；零件P中 Pno按升序建立索引；工程项目J中 Jno 按升序建立索引；对供销情况SPJ中的Sno按升序，Pno按降序，Jno按升序建立索引。举例 解:根据题意建立的索引如下 CREATE

14、UNIQUE INDEX S-SNO ON S(Sno)； CREATE UNIQUE INDEX P-PNO ON P(Pno)； CREATE UNIQUE INDEX J-JNO ON J(Jno)； CREATE UNIQUE INDEX SPJ-NO ON SPJ(Sno ASC，Pno DESC，JNO ASC)；3删除索引n语句格式:DROP INDEX n例如，执行 DROP INDEX StudentIndex；此后索引StudentIndex不再是数据库模式的一部分。3.2.4 定义、删除、更新视图n视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上

15、的。n视图包括几个被定义的数据列和多个数据行，但从其本质上讲，这些数据列和数据行来源于其所引用的表。n视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。3.2.4 定义、删除、更新视图 n视图的优点和作用 n可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。n使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。n视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。视图

16、的优点和作用n视图大大地简化了用户对数据的操作。n视图可以让不同的用户以不同的方式看到不同或者相同的数据集。n在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响，视图则避免了这样的问题。 n视图提供了一个简单而有效的安全机制。 1视图的创建n 语句格式:nCREATE VIEW 视图名 (列表名) AS SELECT 查询子句 WITH CHECK OPTION；1视图的创建n视图的创建中，必须遵循如下规定:n(1)子查询可以是任意复杂的 SELECT 语句，但通常不允许含有order by 子句和DISTINCT短语。n(2

17、)WITH CHECK OPTION表示对UPDATE，INSERT，DELETE操作时保证更新、插入、或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。n(3)组成视图的属性列名或者全部省略或者全部指定。如果省略属性列名，则隐含该视图由SELECT子查询目标列的主属性组成。举例n【例3.3】建立“计算机系”（CS表示计算机系）学生的视图，并要求进行修改、插入操作时保证该视图只有计算机系的学生。nCREATE VIEW CS-STUDENT AS SELECT Sno，Sname，Sage，Sex FROM Students WHERE SD=CS WITH CHECK OPTIO

18、N;n由于在CS_STUDENT视图中使用了“WITH CHECK OPTION” 子句，因此，对该视图进行修改、插入操作时DBMS会自动加上SD=CS的条件，保证该视图只有计算机系的学生。举例2视图的撤消n语句格式: nDROP VIEW 视图名n例如，DROP VIEW CS-STUDENT将删除视图CS-STUDENT。 33 数据查询nSQL的数据操纵功能包括：nSELECT(查询)nINSERT(插入)nDELETE(删除)nUPDATE(修改) 3.3.1 Select基本结构nSQL语言提供了SELECT语句进行数据库的查询。n语句格式: nSELECT ALL|DISTINCT

19、 ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC典型的SQL查询具有如下:n所对应关系代数表达式为: pwhererrrfromAAAselectmn,2121mpAAArrrn21,21需要说明的是:nSQL查询中的子句顺序:SELECT、FROM、WHERE、GROUP BY、HAVING和ORDER BY。但是SELECT、FROM是必须的，而且，HAVING子句只能与GROUP BY搭配起来使用。nSELECT子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是:列名、表达式、集函数，DISTINCT选项可以保证查询

20、的结果集中不存在重复元组。 nFROM 子句对应的是关系代数中的笛卡儿积，它列出的是表达式求值过程中需扫描的关系即在 FROM子句中出现多个基本表或视图时，系统首先执行笛卡尔积操作。nWHERE子句对应的是关系代数中的选择谓词。WHERE子句的条件表达式中可以使用的运算符如表3-1所示:需要说明的是: 3.3.1 Select基本结构n说明1 在SELECT查询中，没有带全程量词，但可以将带全程量词的谓词转换成等价的带有存在量词的谓词，其形式如下:n说明2 在SELECT查询中，没有逻辑蕴含，但可以利用谓词演算将一个逻辑蕴含的谓词等价地转换，其形式如下:)()(PxPxqppq 3.3.2 简

21、单查询n SQL最简单的查询是找出关系中满足特定条件的元组，这些查询与关系代数中的选择操作类似。简单查询只需要使用三个保留字SELECT、FROM和WHERE。举例n【例3.4】查询学生-课程数据库中计算机系学生的学号、姓名及年龄。nSELECT Sno，Sname，Sage FROM Students WHERE SD=CS；举例【例3.5】查询数学系全体学生的详细信息。n SELECT * FROM Students WHERE SD=MS；【例3.6】查询学生的出生年份。n SELECT Sno，2004-Sage FROM Students； 3.3.3 连接查询n若查询涉及两个以上的

22、表，则称为连接查询。n【例3.7】检索选修了课程号为“C1”的学生号和学生姓名可用连接查询和嵌套查询实现，实现方法如下:nSELECT Sno，Sname FROM Students，SC WHERE Students.Sno=SC.Sno AND SC.Cno= C1举例 【例3.8】检索选修课程名为“MS”的学生号和学生姓名可用连接查询和嵌套查询实现，实现方法如下:nSELECT Sno，Sname FROM Students，SC，C WHERE Students.Sno=SC.Sno AND SC.Cno=C.Cno AND C.Cname=MS举例 【例3.9】检索至少选修了课程号为

23、“C1”和“C3”的学生号，实现方法如下:n SELECT Sno FROM SC SCX， SC SCY WHERE SCX.Sno=SCY.Sno AND SCX.Cno=C1 AND SCY.Cno=C3 3.3.4子查询与聚集函数n 1子查询n子查询也称嵌套查询。嵌套查询是指一个 SELECT-FROM-WHERE查询块可以嵌入另一个查询块之中。在SQL中允许多重嵌套。举例 【例3.10】例3.8可以采用嵌套查询来实现。nSELECT Sno，Sname FROM Students WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELEC

24、T Cno FROM C WHERE Cname=MS)2聚集函数n聚集函数是一个值的集合为输入，返回单个值的函数。SQL 提供了5个预定义集函数：平均值AVG、最小值MIN、最大值MAX、求和SUM及计数COUNT，如表 3-2所示。2聚集函数2聚集函数n使用ANY和ALL谓词必须同时使用比较运算符，其含义及等价的转换关系如表 3-3所示。用集函数实现子查询通常要比直接用ALL或ANY查询效率高。举例 【例3.11】查询课程C1的最高分和最低分以及高低分之间的差距nSELECT MAX(G),MIN(G),MAX(G)-MIN(G) FROM SC WHERE Cno=C1举例 【例3.12

25、】查询其它系比计算机系CS所有学生年龄都要小的学生姓名及年龄。n方法1:（用ALL谓词）nSELECT Sname，Sage FROM Students WHERE Sage ALL (SELECT Sage FROM Students WHERE SD=CS) AND SDCS举例n方法2:（用MIN集函数）从等价的转换关系表3-3中可见，“ALL”可用“MIN”代换。nSELECT Sname，Sage FROM Students WHERE Sage (SELECT MIN (Sage) FROM Students WHERE SD=CS AND SDCS 举例【例3.13】查询其它系比

26、计算机系某一学生年龄小的学生姓名及年龄。n方法1:（用ANY谓词）nSELECT Sname，Sage FROM Students WHERE Sage ANY (SELECT Sage FROM Students WHERE SD=CS) AND SDCS举例n方法2:“ANY”可用“MAX”代换。n SELECT Sname，Sage FROM Students WHERE Sage (SELECT MAX (Sage) FROM Students WHERE SD=CS) AND SDCS 3.3.5分组查询n1. GROUP BY子句n在WHERE子句后面加上 GROUP BY子句可以

27、对元组重新组织，并进行分组。保留字GROUP BY后面跟一个分组属性列表。SELECT子句中使用的聚集操作符仅用在每个分组上。举例【例3.14】学生数据库中的SC关系，查询每个学生的平均成绩。nSELECT Sno，AVG（Grade） FROM SC GROUP BY Sno 2. HAVING子句n假如元组在分组前按照某种方式加上限制，使得不需要的分组为空，可以在GROUP BY子句后面跟一个HAVING子句即可。2. HAVING子句n当元组含有空值时，应注意:n第一，空值在任何聚集操作中被忽视。它对求和、求平均值和计数都没有影响。它也不能是某列的最大值或最小值。例如，COUNT(*)是

28、某个关系中所有元组数目之和，但COUNT(A)却是A属性非空的元组个数之和。n第二，NULL值又可以在分组属性中看作是一个一般的值。例如，SELECT A，AVG(B) FROM R中，当A的属性值为空时，就会统计A=NULL的所有元组中B的均值。举例【例3.15】供应商数据库中的S、P、J、SPJ关系，查询某工程至少用了3家供应商（包含3家）供应的零件的平均数量，并按工程号的降序排列。nSELECT JNO，AVG（QTY） FROM SPJ GROUP BY JNO HAVING COUNT（DISTINCT（SNO）2 ORDER BY JNO DESC；举例表3-4 按工程号JNO=J

29、1分组SnoPnoJnoQtyS1S2S2S2S3S4S5P1P3P3P5P1P6P3J1 J1J1 J1J1 J1 J1200400200100200300200n假如按工程号JNO=J1来分组，结果如表3-4 所示。从表中可以看出如果不加DISTINCT ,统计的数为 7 ， 而加了DISTINCT，统计的数是5。 3.3.6 更名运算nSQL提供可为关系和属性重新命名的机制，这是通过使用具有如下形式的as子句来实现的:n As Old-name as new-namenAs子句即可出现在select子句，也可出现在from子句中。举例【例3.16】查询计算机学生的Sname和Sage，但

30、Sname的用姓名表示，Sage用年龄表示。其语句如下:nSELECT Sname as姓名，Sage as 年龄 FROM Students WHERE Sage (SELECT MAX (Sage) FROM Students WHERE SD=CS) AND SDCS举例【例3.17】查询计算机选修了C1课程的学生姓名Sname和成绩Grade。其语句如下:nSELECT Sname，Grade FROM Students as x，SC as y WHERE x.sno=y.sno and o=C1举例【例3.18】查询平均成绩至少比“1004”平均成绩高的学生学号Sno和平均成绩Gr

31、ade。其语句如下:nSELECT Sno，avg(Grade) FROM SC as x Group by sno Having avg(Grade)(SELECT avg(Grade) FROM SC as y WHERE y.sno=1004) 3.3.7字符串操作n字符串匹配使用两个特殊的字符来描述模式：“”匹配任意字符串；“_”匹配任意一个字符。模式是大小写敏感的。n例如:“Marry%”匹配任何以“Marry”开头的字符串；“idge%” 匹配任何包含“idge”的字符串，如 “Marryidge”、“Rock Ridge”、“Mianus Bridge”和“Ridgeway”。n

32、“_” 匹配只含两个字符的字符串；“_” 匹配至少包含两个字符的字符串。举例【例3.19】学生关系模式为（Sno，Sname，Sex，SD，SAge，SAdd），其中: Sno为学号，Sname为姓名，Sex为性别，SD为所在系，SAge为年龄，SAdd为家庭住址。请查询:n查询家庭住址包含“科技路”的学生姓名。n检索名字为“晓军”的学生姓名、年龄和所在系。举例解：n(1)家庭住址包含“科技路”的学生姓名的SQL语句如下:nSELECT Sname n FROM Studentsn WHERE Add like 科技路n(2)名字为“晓军”的学生姓名、年龄和所在系的SQL语句如下:nSELEC

33、T Sname，Age，SDn FROM Studentsn WHERE Sname LIKE _晓军使用escape关键词来n为了使模式中包含特殊模式字符(即%和_)，在 SQL 中允许使用 escape关键词来定义转义符。转义符紧靠着特殊字符，并放在它的前面，表示该特殊字符被当成普通字符。例如在like比较中使用escape关键词来定义转义符，例如使用反斜杠“”作为转义符。使用escape关键词来nLike abcdescape ，匹配所有以abcd开头的字符串。nLike abcdescape ，匹配所有以abcd开头的字符串。 3.3.8集合操作n在关系代数中可以用集合的并、交和差来组

34、合关系。SQL也提供了对应的操作，但是查询的结果必须具有相同的属性和类型列表。保留字UNION、INTERSECT和EXCEPT分别对应。保留字用于两个查询时，应该分别用括号括起来。举例n【例3.20】假定学生和教师关系模式如下所示，查询即是女研究生，又是教师且工资大于等于1500元的名字和地址。nStudents(Name，Sno，SEX，SD，Type，Address)nTeachers(Name，Eno，SEX，Salary，Address) n解:本题第一条SELECT语句查询和第二条SELECT语句查询的结果集模式都为(Name，Address)，故可以对它们取交集。n(SELECT

35、 Name，Addressn FROM Studentsn WHERE SEX女 AND Type研究生)n INTERSECTn(SELECT Name，Addressn FROM Teachersn WHERE Salary 1500)举例举例n同理，我们也可以对两个相同结果集的关系取差集n【例3.21】查询不是教师的学生。n(SELECT Name，Address FROM Students)n EXCEPTn(SELECT Name，Address FROM Teachers)3.3.9视图的查询和删除n1视图查询n【例3.22】建立 “计算机系 ”（ CS 表示计算机系）学生的视图如

36、下所示，并要求进行修改、插入操作时保证该视图只有计算机系的学生。3.3.9视图的查询和删除解：nCREATE VIEW CS-STUDENT n AS SELECT Sno，Sname，Sage，Sexn FROM Studentn WHERE SD=CSn WITH CHECK OPTION;举例n例:查询计算机年龄小于20岁的学号及年龄的:nSELECT Sno，Sage FORM CS-STUDENT WHERE SD=CS AND Sage20; n系统执行该语句时，通常现将其转换成等价的对基本表的查询然后执行查询语句，即当查询视图表时，系统先从数据字典中取出该视图的定义，然后将定义中

37、的查询语句和对该视图的查询语句结合起来，形成一个修正的查询语句。对上例修正之后的查询语句为:nSELECT Sno，Sage FORM Student WHERE SD=CS AND Sage= n branch-total-avg.value举例34 数据更新3.4.1 插入、删除和修改语句n1插入语句n语句的基本格式:nINSERT INTO 基本表名(字段名，字段名) VALUES(常量，常量); 查询语句nINSERT INTO 基本表名(列表名) SELECT 查询语句举例n【例3.25】将学号为“3002”、课程号为“C4”、成绩为98的元组插入SC关系中。其语句如下:nInser

38、t into SC Values(3002， C4，98)举例【例3.26】创建一个新的视图v_employees，要求该视图基于表employees创建。ncreate view v_employees(number，name， n age，sex，salary)n asn select number，name，age，sex，salary n from employeesn where name=张三nInsert into v_employeesn Values(001，李力，22，m，2000)2删除语句n语句格式：nDELETE FROM 基本表名WHERE 条件表达式【例3.27】

39、删除表employees中姓名为张然的记录。nDELETE from employeesn where name=张然3修改语句n语句格式：nUPDATE 基本表名 SET 列名=值表达式(，列名=值表达式) WHERE 条件表达式【例3.28】将教师的工资增加5。nupdate teachersn set Salary = Salary*1.05举例【例3.29】将教师的工资小于1000的增加5工资。nupdate teachersn set Salary = Salary*1.05n where Salary =15 AND (SEX=M AND Sage n =25)ORn (SEX=F

40、 AND Sage =16 AND Sage=20)检测。n基于断言的语法格式n格式:CREATE ASSERTION n CHECK() 举例【例3.36】教学数据库的模式Students、SC、C中有一个约束，不允许男同学选修“张勇”老师的课。nCREATE ASSERTION ASSE-SC1 CHECKn (NOT EXISTSn (SELECT * FROM SC WHERE Cno INn (SELECT Cno FROM C WHERE n TEACHER=张勇)n AND Sno INn (SELECT Sno FROM Students WHERE n SEX=M);举例【例

41、3.37】教学数据库的模式Students、SC、C中有一个约束，每门课最多50名男同学选修。n解:可写成如下的断言形式:n CREATE ASSERTION ASSE-SC2 CHECKn (50=ALL(SELECT COUNT(SC.Sno)n FROM Students，SC n WHERE Students.Sno=SC.Sno AND n SEX=Mn GROUP BY Cno);3.6 SQL中的触发器n触发器（Trigger）就是一类由来事件驱动的特殊过程，一旦由某个用户定义，任何用户对该触发器指定的数据进行增、删或改操作时，系统将自动激活相应的触发器，在核心层进行集中的完整性

42、控制。3.6.1 触发器的组成和类型n 触发器的定义包括两个方面:n（1）指明触发器的触发事件；n（2）指明触发器执行的动作。3.6.1 触发器的组成和类型n触发事件包括表中行的插入、删除和修改，即执行INSERT、DELETE、UPDATE语句。在修改操作（UPDATE）中，还可以指定，特定的属性或属性组的修改为触发条件。事件的触发还有两个相关的时间:nBefore触发器是在事件发生之前触发nAfter触发器是在事件发生之后触发3.6.1 触发器的组成和类型n触发动作实际上是一系列SQL语句，可以有两种方式:n(1)对被事件影响的每一行（FOR EACH ROW）每一元组执行触发过程，称为行

43、级触发器。n(2)对整个事件只执行一次触发过程（FOR EACH STATEMENT），称为语句级触发器。该方式是触发器的默认方式。 3.2.2 创建触发器n1创建触发器语句的格式n CREATE TRIGGER BEFORE|AFTER DELETE| INSERT| UPDATEOF列名清单 ON 表名 REFERENCING临时视图名 WHEN触发条件| 触发动作 FOR EACHROW|STATEMENT 说明n(1)BEFORE:指示DBMS在执行触发语句之前激发触发器。n(2)AFTER:指示 DBMS 在执行触发语句之后激发触发器。n(3)DELETE:指明是DELETE触发器，

44、每当一个DELETE 语句从表中删除一行时激发触发器。 n(4)INSERT:指明是INSERT触发器，每当一个INSERT语句向表中插入一行时激发触发器。n (5) UPDATE:指明是 UPDATE 触发器，每当UPDATE语句修改由OF子句指定的列值时，激 发 触 发 器 。 如 果 忽 略 O F 子 句 ， 每 当UDPATE语句修改表的任何列值时，DBMS都将激发触发器。说明说明n(6) REFERENCING 临时视图名: 指定临时视图的别名。在触发器运行过程中，系统会生成两个临时视图，分别存放被更新值（旧值）和更新后的值（新值）。对于行级触发器，默认临时视图名分别是OLD和NE

45、W；对于语句级触发器，默认临时视图名分别是 OLD-TABLE和 NEW-TABLE 。一旦触发器运行结束，临时视图就不在。说明n(7)WHEN 触发条件: 指定触发器的触发条件。当满足触发条件时，DBMS才激发触发器。触发条件中必须包含临时视图名，不包含查询。 【例3.38】为employee表创建一触发器，当修改某位职工的职称时，应同时修改salary表中的基本工资（Basepay），具体标准为:当职称修改为工程师时，增加150元；当修改为高工时，增加300元:举例nCREATE TRIGGERtri-updatetitle-employeen AFTER UPDATE OF titlen

46、 ON employeen REFERENCINGn NEW AS newtuplen WHEN(newtuple.title=工程师)n UPDA TE salaryn SET basepay=basepay+150n WHERE eno=newtuple.enon WHEN(newtuple.title=高工)n UPDATE salaryn SET basepay=basepay+300n WHERE eno=newtuple.enon FOR EACH ROW举例 37 嵌入式SQLn用某种传统的编程语言编写程序，但程序中的某些函数或某些语句是SQL语句。这种方式下使用的SQL语言称为

47、嵌入式SQL（EmbeddedSQL），其中传统的编程语言称为宿主语言（或主语言）。 nSQL是非过程的、面向集合的数据操纵语言，它大部分语句的使用都是独立的，与上下文无关。但在事务处理中，常需要有流程控制，如果单使用SQL 语言，很难实现这类应用。另一方面，普通的编程语言在涉及数据库操作时，不能高效地进行数据的存取。所以，嵌入式 SQL的使用，结合了编程语言的过程性和 SQL语言的数据操纵能力，可提高数据库应用程序的效率。 37 嵌入式SQL 3.7.1 SQL与宿主语言接口n嵌入式SQL的实现DBMS有两种处理方法。n一种是扩充主语言及修改主语言编译程序，使之能处理SQL语句；n另一种是采

48、用预处理方式，即由DBMS的预处理程序对源程序进行扫描，识别出SQL语句，把它们转换成主语言的函数调用语句，以使主语言编译程序能识别它们，最后由主语言的编译程序将整个源程序编译成目标码。 使用嵌入式SQL的原则:n1区分主语言语句与SQL语句n为了区分主语言语句与SQL语句，需要在所有的SQL语句前加前缀 EXEC SQL，而SQL的结束标志随主语言的不同而不同。n例如，PL/1和C语言的引用格式为:n EXEC SQL ；n又如，COBOL语言的引用格式为:n EXEC SQL END-EXEC使用嵌入式SQL的原则:n2主语言工作单元与数据库工作单元通信n嵌入的SQL语句通过引用主语言的程

49、序变量实现相互间的通信。主变量是由主语言程序定义，并且必须在SQL的BEGIN DECLARE SECTION和END DECLARE SECTION说明语句之间加以说明。说明之后嵌入式SQL 语句即可引用主变量，引用时必须在主变量之前加冒号“:”作前缀标志，以区别数据库字段变量。 2主语言工作单元与数据库工作单元通信n在 主 变 量 中 ， 有 一 个 特 殊 的 主 变 量SQLSTATE，用于存放每条SQL语句执行后的执行状况代码，应用程序在执行完一条SQL语句后可以测试它，以了解SQL语句执行状况并作响应处理。 2主语言工作单元与数据库工作单元通信nSQLSTATE是由5个字符组成的字

50、符数组，是SQLCA(SQL Communication Area，SQL通信区)数据结构的一成员分量。当 SQL语句执行成功时，DBMS给SQLSTATE中赋全零 “00000”值，表示无错;否则赋非零，分别表示SQL语句执行时发生的各种错误，如等于“02000” 表示未找到元组。SQLSTATE也必须在两个SQL说明语句之间说明，嵌入式SQL语句才能引用。举例n例如:在C语言中可用如下形式说明主变量:nEXEC SQL BEGIN DECLARE SECTION; n /*说明主变量/*nchar Msno4，Mcno3，givensno5;nint Mgrade;nchar SQLSTA

51、TE6;nEXEC SQL END DECLARE SECTION; n上面5行组成一个说明节，说明了5个共享变量，其中， SQLSTATE是一个特殊的共享变量，起着解释SQL语句执行状况的作用。因此，当执行一条SQL语句后，可以根据SQLSTATE的值转向不同的分支，以控制程序的流向。举例举例 【例3.39】根据共享变量givensno值查询学生关系students中学生的姓名、年龄和性别。nEXEC SQL SELECT sname，age，sexnINTO :Msno，:Mcno，:givensnonFROM studentsnWHERE sno=:Msno;举例 【例3.40】某学生选

52、修了一门课程信息，将其插入学生选课表SC中， 假设学号、 课程号、成绩分别赋给主变量 HSno、Hcno和Hgrade。 EXEC SQL INSERT INTO SC（Sno，Cno，Grade） VALUES（:Hsno，:Hcno，:Hgrade）； nVALUES子句中通常使用主变量传递输入数据。举例 【例3.40】将学号等于“001”考生的考试成绩增加若干分。 假设增加的分数已赋给主变量 Hgrade。 EXEC SQL UPDATE SC SET grade=grade+:Hegrade WHERE Sno=001； 使用嵌入式SQL的原则:n3游标nSQL语言是面向集合的，一条S

53、QL语句可产生或处理多条记录。而主语言是面向记录的，一组主变量一次只能放一条记录，所以，引入游标，通过移动游标指针来决定对获取哪一条记录。与游标相关的SQL语句有四条:3游标n定义游标。定义游标的格式如下:nEXEC SQL DECLARE CURSOR FOR END_EXECn这是一条说明性语句，定义中的SELECT语句并不立即执行。3游标n打开游标。打开游标的格式如下:nEXEC SQL OPEN END_EXECn该语句执行游标定义中的SELECT语句，同时游标处于活动状况。游标是一个指针，此时指向查询结果的第一行之前。3游标n 推进游标。推进游标的格式如下:n EXEC SQL FETCH FROM INTO END_EXECn该语句使用时，游标推进一行，并把游标指向的行（称为当前行