数据库系统概论：第4章 高级SQL

1、第四章第四章 高级高级SQLnSQL 数据类型和模式n完整性约束 n授权n嵌入式 SQLn动态 SQLn函数和过程结构n递归查询n高级 SQL 特性4.1 SQL的数据类型与模式的数据类型与模式n在第3章中，我们介绍了SQL支持的一些内建的数据类型，如整数类型、实数类型和字符类型nSQL还支持一些其他的内建的数据类型 n在SQL中创建基本的用户定义类型 4.1 .1 SQL 中内建数据类型中内建数据类型ndate: 日期：日期： 年年(4位位) 、月、日、月、日n例子例子: date 2005-7-27ntime: 一天中的时间：小时、分钟、秒一天中的时间：小时、分钟、秒n例子例子: time

2、 09:00:30 time 09:00:30.75ntimestamp: 时间戳：日期加上时间时间戳：日期加上时间n例子例子: timestamp 2005-7-27 09:00:30.75ninterval: 时间期间时间期间n例子例子: interval 1 dayndate/time/timestamp 值相减得到期间值值相减得到期间值n期间值能够和期间值能够和date/time/timestamp值相加值相加SQL 中内建数据类型中内建数据类型 n能从能从date/time/timestamp中抽取个体字段的值中抽取个体字段的值n例子例子: extract (year from r.

3、starttime) n能将符合正确格式的字符串类型转化为能将符合正确格式的字符串类型转化为ndate / time / timestamp类型类型n例子例子: cast as daten例子例子: cast as time 4.1.2 用户定义类型用户定义类型ncreate type 结构建立结构建立用户定义类型用户定义类型create type Dollars as numeric (12,2) final create type Pounds as numeric (12,2) finaln总共总共12位数字的十进制数，其中两位小数。（在这个环境下位数字的十进制数，其中两位小数。（在这个

4、环境下final 关关键字并不是真的有意义，但键字并不是真的有意义，但SQL:1999要求支持这个关键字，要求支持这个关键字， 原因我原因我们不会在这里仔细讨论；一些系统实现允许忽略们不会在这里仔细讨论；一些系统实现允许忽略final关键字关键字.） n create table accountn (account_number char(10),n branch_name char(15),n balance Dollars）n为不同货币声明不同的类型能帮助发现为不同货币声明不同的类型能帮助发现运算汇率运算汇率不同的错误。不同的错误。 4.1.2 用户定义类型用户定义类型n create d

5、omain 结构（结构（ SQL-92 ）结构建立）结构建立用户定义域类型用户定义域类型create domain person_name char(20) not nulln类型和域相似，类型和域相似，域可以有约束，如域可以有约束，如not null； 域不是强制类型域不是强制类型(不能在兼容类型间强制转换不能在兼容类型间强制转换)（不同的兼容类型间可以进行比较：如（不同的兼容类型间可以进行比较：如customer_name char(20) 和和 branch_name char(15)） 一种类型的数值可以被转换（一种类型的数值可以被转换（cast）成另一个值域，）成另一个值域， cas

6、t (account.balance to numeric(12,2) SQL还提供了还提供了drop type和和alter type子句来删除或修改以前创子句来删除或修改以前创建过的类型。建过的类型。 关于域约束关于域约束n域约束（域约束（domain constraints）是完整性约束最基本的是完整性约束最基本的形式。形式。 数据库插入和查询等将进行该约束测试数据库插入和查询等将进行该约束测试，以，以确保比较有意义。确保比较有意义。n新的域可以用现有数据类型来建立新的域可以用现有数据类型来建立n例子例子:create domain Dollars numeric(12, 2) crea

7、te domain Pounds numeric(12,2)n我们不能将我们不能将 Dollars 值赋予值赋予 Pounds类型，二者也无法类型，二者也无法进行比较进行比较 类型和域的区别n类型和域之间有两个重大的区别：类型和域之间有两个重大的区别：n 1在域上可以指定约束在域上可以指定约束，例如，例如not null，也，也可以可以为域类型变量定义默认值为域类型变量定义默认值，然而用户定，然而用户定义类型上不能指定约束或默认值。用户定义义类型上不能指定约束或默认值。用户定义类型不仅被设计来指定属性类型，还用于在类型不仅被设计来指定属性类型，还用于在不能施加约束的地方对不能施加约束的地方对S

8、QL进行过程扩展。进行过程扩展。 n2. 域并不是强类型的。因此域并不是强类型的。因此一个域类型的值一个域类型的值可以被赋给另一个域类型可以被赋给另一个域类型，只要它们的类型，只要它们的类型是相容的。是相容的。 4.1.3 大对象（大对象（LARGE-OBJECT）类型）类型n大对象大对象 (photos, videos, CAD 文档文档,等等等等) 以下面类型存储以下面类型存储 nblob: binary large object 二进制二进制数据的大对象数数据的大对象数 。n对象是未翻译的（如何翻译留给数据库系统以外的应对象是未翻译的（如何翻译留给数据库系统以外的应用）用）nclob:

9、character large object 字符数据的大对象字符数据的大对象n 对象是大量字符数据集合对象是大量字符数据集合n当查询返回大对象时，实际返回一个指针而不是大对当查询返回大对象时，实际返回一个指针而不是大对象本身象本身4.1.3 大对象（大对象（LARGE-OBJECT）类型）类型n 执行一个执行一个SQL查询通常把结果中的一条或多条记录放查询通常把结果中的一条或多条记录放人内存。人内存。大对象通常用于外部应用大对象通常用于外部应用，对于非常大的对象来，对于非常大的对象来说说( 几个几个MB甚至甚至GB)，把整个大对象放人内存中是非常低，把整个大对象放人内存中是非常低效和不现实的

10、。效和不现实的。 一个应用通常用一个一个应用通常用一个SQL查询来检索一查询来检索一个大对象的个大对象的 “定位器定位器”，然后用这个定位器来处理宿主，然后用这个定位器来处理宿主语言的对象。例如，语言的对象。例如，JDBC应用程序接口应用程序接口 允许取出一个定允许取出一个定位器而不是整个大对象；然后可以用这个定位器来一点一位器而不是整个大对象；然后可以用这个定位器来一点一点地取出这个大对象，而不是一次取出全部，这很像用一点地取出这个大对象，而不是一次取出全部，这很像用一个个read函数调用从一个操作系统文件中读取数据。函数调用从一个操作系统文件中读取数据。 4.1.4 模式、目录与环境模式、

11、目录与环境n 现代数据库系统提供一个现代数据库系统提供一个三层结构的关系三层结构的关系命名机制命名机制。n最顶层由目录最顶层由目录(catalogs)构成，每个目录都构成，每个目录都可以包含一个模式可以包含一个模式(schema)。SQL对象（如对象（如关系和视图）都包含在模式关系和视图）都包含在模式(schema)中。中。（一些数据库实现用术语（一些数据库实现用术语“数据库数据库”代替代替术语术语“目录目录”）4.1.4 模式、目录与环境模式、目录与环境n要在数据库上做任何操作，用户（或程序）都必要在数据库上做任何操作，用户（或程序）都必须先连接到数据库。为了验证用户的身份，这个须先连接到数

12、据库。为了验证用户的身份，这个用户必须提供用户名以及一个密码（通常情况下用户必须提供用户名以及一个密码（通常情况下）。）。每个用户都有一个默认的目录和模式每个用户都有一个默认的目录和模式，这个，这个组合对用户来说是唯一的。当一个用户连接到数组合对用户来说是唯一的。当一个用户连接到数据库系统时，将为该连接设置好默认的目录和模据库系统时，将为该连接设置好默认的目录和模式。这意味着当用户登录进一个操作系统时，把式。这意味着当用户登录进一个操作系统时，把当前目录设置为用户的当前目录设置为用户的home(主主)目录。目录。4.1.4 模式、目录与环境模式、目录与环境n为了唯一标识一个关系，必须使用一个名

13、字为了唯一标识一个关系，必须使用一个名字，该名字包含三部分，仍如：，该名字包含三部分，仍如：n catalog5.bank_schema.accountn 当连接到默认目录时，可以忽略目录成分。这样当连接到默认目录时，可以忽略目录成分。这样如果如果catalog5是默认目录，我们可以用是默认目录，我们可以用bank _schema.account来唯一标识上述关系。进一步地来唯一标识上述关系。进一步地，若是连接到默认模式，我们也可以忽略模式的名，若是连接到默认模式，我们也可以忽略模式的名字。这样如果默认目录是字。这样如果默认目录是catalog5，默认模式是，默认模式是bank_schema，

14、我们可以只用，我们可以只用account。4.1.4 模式、目录与环境模式、目录与环境n当有多个目录和模式可用时，不同应用和不同用户当有多个目录和模式可用时，不同应用和不同用户可以独立工作而不必担心命名冲突。可以独立工作而不必担心命名冲突。n 所有通常的所有通常的SQL 语句，包括语句，包括DDL和和DML语句，都语句，都在一个模式的环境中运行。在一个模式的环境中运行。n我们可以用我们可以用create schema和和drop schema语句来创语句来创建和删除模式。建和删除模式。n创建和删除目录依据实现的不同而不同，这不是创建和删除目录依据实现的不同而不同，这不是SQL标准中的一部分。标

15、准中的一部分。 42 完整性约束完整性约束n完整性约束完整性约束通过确保被授权的对数据库的修改不会导通过确保被授权的对数据库的修改不会导致数据一致性的丢失，来防止数据库免予意外破坏。致数据一致性的丢失，来防止数据库免予意外破坏。n关系的完整性是关系型数据模型结构中的另一组成部关系的完整性是关系型数据模型结构中的另一组成部分，它分为分，它分为实体完整性实体完整性、参照完整性参照完整性和和用户定义完整用户定义完整性性三类。三类。 n实体完整性实体完整性是通过是通过主码主码（PRIMARY KEY）的定义来）的定义来实现的。一旦某个属性或属性组被定义为主码，该主实现的。一旦某个属性或属性组被定义为主

16、码，该主码的每个属性就码的每个属性就不能为空值不能为空值，并且在表中不能出现主，并且在表中不能出现主码值码值完全相同的两个记录完全相同的两个记录。实体完整性实体完整性n n主码可以在主码可以在CREATE TABLE语句中使用语句中使用PRIMARY KEY定义。有两种定义主码的方法：定义。有两种定义主码的方法：n一种是在属性后增加关键字一种是在属性后增加关键字PRIMARY KEY n另一种是在属性表中加入额外的定义主码的子句另一种是在属性表中加入额外的定义主码的子句 PRIMARY KEY（主码属性名表）。（主码属性名表）。SQL中的实体（主码）完整性中的实体（主码）完整性（1）属性后增加

17、关键字定义）属性后增加关键字定义CREATE TABLE StudentInfo(StudentIDchar(8)PRIMARY KEY,StudentNamevarchar(10),StudentSexbit);（2）加入额外的定义主码的子句）加入额外的定义主码的子句CREATE TABLE StudentInfo(StudentIDchar(8)，StudentNamevarchar(10),StudentSexbit,PRIMARY KEY(StudentID); 如果表的主码只含有单个属性，上面的两种方法都可以使用。如果表的主码只含有单个属性，上面的两种方法都可以使用。如果主码由多个如

18、果主码由多个属性组成，只能使用第二种方法。属性组成，只能使用第二种方法。n 除了主码，除了主码，SQL提供了类似提供了类似候选码候选码的说明方法，使用关键字的说明方法，使用关键字UNIQUE定义定义（ 定义为定义为UNIQUE的属性可以定义为的属性可以定义为空空值值，但只能有一个记录该属性的值为，但只能有一个记录该属性的值为NULL），说明该属性（或属性组）的），说明该属性（或属性组）的值不能重复。值不能重复。一个表中只能有一个主码，但可以有多一个表中只能有一个主码，但可以有多个个“UNIQUE”定义。定义。42 完整性约束完整性约束n企业过往帐户至少有企业过往帐户至少有$10,000.00以

19、上余额以上余额n银行雇员薪水至少每小时银行雇员薪水至少每小时$4.00 n客户必须有非空客户必须有非空(non-null)电话号码电话号码n 一个账户的余额不能为空。一个账户的余额不能为空。n任何两个账户不能有相同的账号任何两个账户不能有相同的账号 n完整性约束可以是与数据库有关的任意谓词，完整性约束可以是与数据库有关的任意谓词，但任意谓词的但任意谓词的检测代价可能很高检测代价可能很高n许多数据库允许用户指定那些只需极小开销就可检测许多数据库允许用户指定那些只需极小开销就可检测的完整性约束的完整性约束。 4.2.1 单个关系上的约束单个关系上的约束 ncreate table命令也可以包括完整

20、性约束语命令也可以包括完整性约束语句句。除了。除了“主码主码”约束以外，还有其他许约束以外，还有其他许多可以包括在多可以包括在create table命令里的约束。命令里的约束。允许的完整性约束包括：允许的完整性约束包括：nnot nullnprimary keynuniquencheck (P ), 这里 P 是谓词4.2.2 NOT NULL 约束约束 nnull值是所有域的成员，值是所有域的成员， 是是SQL中每个属性中每个属性的默认合法值的默认合法值。然而对于一些属性来说，。然而对于一些属性来说，空值可能是不合适的。空值可能是不合适的。 n例如我们例如我们不会希望一个账户的余额为空不会

21、希望一个账户的余额为空。n在这些情况下，我们希望禁止空值。在这些情况下，我们希望禁止空值。4.2.2 NOT NULL 约束约束 n 我们可以通过我们可以通过限定属性限定属性balance的域来排除的域来排除空值空值 n account_number char(10) not nulln balance numeric(12,2) not nulln not null的限定禁止在该属性上插人一个空值的限定禁止在该属性上插人一个空值。任何可能导致向一个声明为任何可能导致向一个声明为not null 的属性插入的属性插入一个空值的数据库修改都会产生错误诊断信息。一个空值的数据库修改都会产生错误诊断

22、信息。 4.2.2 NOT NULL 约束约束 n许多情况下我们会希望避免空值，尤其是许多情况下我们会希望避免空值，尤其是SQL禁禁止在关系模式的主码中出现空值。止在关系模式的主码中出现空值。n 因此主码不必显式地声明为因此主码不必显式地声明为not null.nnot null的限定同样可以被用在的限定同样可以被用在用户定义域用户定义域的声明的声明中；由此该域类型的属性不能为空中；由此该域类型的属性不能为空 n例如，如果我们希望例如，如果我们希望Dollars域不能有空值，我们域不能有空值，我们可以作如下声明：可以作如下声明：ncreate domain Dollars numeric(12

23、,2) not null4.2.3 UNIQUE 约束约束nunique ( A1, A2, , Am)nunique 限定指出属性限定指出属性 A1, A2, Am形成一个候选码形成一个候选码n候选码属性允许为候选码属性允许为null （这和主码不同）除非（这和主码不同）除非它们已被显示地声明为非空它们已被显示地声明为非空n注意对空值的比较运算结果注意对空值的比较运算结果4.2.4 CHECK 子句子句nSQL中的中的check子句可以用于子句可以用于关系声明和域声明关系声明和域声明。当用于关系声明时，当用于关系声明时，check子句指定一个谓词子句指定一个谓词P，关系中的所有元组都必须满足

24、该谓词。关系中的所有元组都必须满足该谓词。 ncheck子句的子句的通常应用是保证属性值满足指定的条通常应用是保证属性值满足指定的条件件，由此创建一个强大的类型系统。，由此创建一个强大的类型系统。n例如，创建关系例如，创建关系branch的的create table命令中的一命令中的一个子句个子句check（assets=0）将保证资产值是非负）将保证资产值是非负的。的。 check (P ), 这里 P 是谓词。例子例子: 声明声明 branch_name 为为 branch 的主码，并的主码，并且确保且确保assets 的值为非负的值为非负create table branch (bran

25、ch_name char(15), branch_city char(30), assets integer, primary key (branch_name), check (assets = 0)create tabIe student (name char(15) not null, student_id char(10), degree _level char(15) ， primary key (student_id)， check(degree _level in (Bachelors，Masters，Doctorate) 例如：用例如：用check子句可以保证小时工资域的值必须

26、大于某子句可以保证小时工资域的值必须大于某一指定值（如最低工资）：一指定值（如最低工资）： create domain HourlyWage number(5,2) constraint wage_value_test check(value=6.00) 域域HourlyWage有一个约束有一个约束，以保证小时工资数大于或等于，以保证小时工资数大于或等于6.00。子句子句constraint wage_value_test是可选的是可选的，它用来，它用来将该约束命名为将该约束命名为wage_value_test。系统用这个名字指出一。系统用这个名字指出一个更新违反了哪个约束。个更新违反了哪个约

27、束。作为另一个例子，使用作为另一个例子，使用in子句可以限定一个域只包含指定的一子句可以限定一个域只包含指定的一组值：组值： create domain AccountType char(10) constraint account_type_test check (value in(Checking,Saving)check条件中允许出现包含其他关系的子查询，条件中允许出现包含其他关系的子查询，check条件可条件可能变得更复杂（也更难检测）能变得更复杂（也更难检测）.例如，这个约束可以指定作用在关系例如，这个约束可以指定作用在关系deposit中：中： check(branch_name

28、in (select branch_name from branch) 这个这个check条件检测在条件检测在deposit关系中每一个关系中每一个branch_name元组的确是在关系元组的确是在关系branch中的分行名称。中的分行名称。因此这个条件必须不仅仅是因此这个条件必须不仅仅是deposit中插入或修改中插入或修改一个元组的时一个元组的时候检测，而且在关系候检测，而且在关系branch改变改变的时候也要检测（如在关系的时候也要检测（如在关系branch中，当一个元组中，当一个元组被删除或修改被删除或修改的情况下）的情况下）复杂的复杂的check条件在我们希望确保数据完整性的时候是很

29、有用条件在我们希望确保数据完整性的时候是很有用的，但要的，但要谨慎地使用谨慎地使用，因为检测它们的，因为检测它们的开销会很大开销会很大。 SQL SQL标准允许将一个子句标准允许将一个子句initially deterredinitially deterred加入到加入到约束说明中；这样该完整性约束不是在事务的中间步骤上检查，约束说明中；这样该完整性约束不是在事务的中间步骤上检查，而是在而是在事务结束的时候检查事务结束的时候检查。 一个约束可以被指定为可延迟的一个约束可以被指定为可延迟的, ,意味着默认状态下它会立意味着默认状态下它会立即被检查，但是当需要的时候可以延迟。对于声明为可延迟的即被

30、检查，但是当需要的时候可以延迟。对于声明为可延迟的约束，执行一个语句约束，执行一个语句set constraints constraint-list set constraints constraint-list deferreddeferred作为事务的一部分，这会作为事务的一部分，这会使指定约束的检查被延迟到使指定约束的检查被延迟到事务结束时执行。事务结束时执行。 注意，注意， 默认的行为是立即检查约束，而且许多数据库实现默认的行为是立即检查约束，而且许多数据库实现不支持延迟约束检查。不支持延迟约束检查。4.2.5 参照完整性（参照完整性（REFERENTIAL INTEGRITY）n确保

31、一个关系上给定属性集上的取值也在另一关系确保一个关系上给定属性集上的取值也在另一关系某一属性集上的取值中存在。某一属性集上的取值中存在。n n例例: account表有一个声明：表有一个声明：nforeign key （branch _name）references branch，这个外码声明指，这个外码声明指 明对于账户中的每个元组，明对于账户中的每个元组，元组中指定的分行名字必须在元组中指定的分行名字必须在branch关系中存在关系中存在.没有这个约束，可能会为一个账户指定一个不没有这个约束，可能会为一个账户指定一个不存在的分行名字。存在的分行名字。4.2.5 参照完整性（参照完整性（RE

32、FERENTIAL INTEGRITY）n一般地说，令关系一般地说，令关系r1(R1)和和r2(R2)的主码分别为的主码分别为K1和和K2。如果要求对。如果要求对r2中任意元组中任意元组t2，均存在，均存在r1中元组中元组t1使得使得t1K1=t2a，我们称，我们称R2的子集的子集a为参照关系为参照关系r1中中K1的外码。这种要求称为的外码。这种要求称为参照完整性约束参照完整性约束或或子集子集依赖依赖。 n参照完整性，又被称为参照完整性，又被称为关联完整性关联完整性，意思是说，如，意思是说，如果一个表包含一个外键列，那么这个列中的每一个果一个表包含一个外键列，那么这个列中的每一个值值(除了空，

33、如果允许为空除了空，如果允许为空)都在关联表或被参照表都在关联表或被参照表的主键里。的主键里。 4.2.5 参照完整性（参照完整性（REFERENTIAL INTEGRITY）n参照完整性属于表间规则。对于永久关系的相关表参照完整性属于表间规则。对于永久关系的相关表，在，在更新、插入或删除记录时，如果只改其一不改更新、插入或删除记录时，如果只改其一不改其二，就会影响数据的完整性其二，就会影响数据的完整性n例如修改父表中关键字值后，子表关键字值未做相例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记

34、录称为孤立记录；对于子表插入删除，致使这些记录称为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录；等等。的记录，父表中没有相应关键字值的记录；等等。对于这些设计表间数据的完整性，统称为参照完整对于这些设计表间数据的完整性，统称为参照完整性。性。 n限制策略限制策略n外键约束对子表的含义外键约束对子表的含义: : n如果在父表中找不到候选键如果在父表中找不到候选键, ,则不允许在子表上进则不允许在子表上进行行insert/update insert/update n级联策略级联策略 n外键约束对父表外键约束对父表( (被参照表被参照表) )的含义的含义: : 在父表上进行在父表上进行

35、update/delete update/delete 以更新或删除在子以更新或删除在子表中有一条或多条对应匹配行的候选键时表中有一条或多条对应匹配行的候选键时, ,父表的父表的行为取决于：在定义子表的外键时指定的行为取决于：在定义子表的外键时指定的 on on update/on delete update/on delete 子句子句 4.2.5 参照完整性（参照完整性（REFERENTIAL INTEGRITY）n 主码、候选码、外码能够在主码、候选码、外码能够在SQL的的create table声明声明中使用中使用:nprimary key 子句列表组成主码的属性子句列表组成主码的属性

36、nunique key 子句列表组成候选码的属性子句列表组成候选码的属性nforeign key 子句列表组成外码的属性和被该外码子句列表组成外码的属性和被该外码参照的关系。参照的关系。按照默认，按照默认， 外码参照的是参照表外码参照的是参照表中的主码属性。中的主码属性。SQL中的实体（主码）完整性中的实体（主码）完整性create table customer(customer_name char(20),customer_street char(30),customer_citychar(30),primary key (customer_name )create table branch

37、(branch_namechar(15),branch_citychar(30),assetsnumeric(12,2),primary key (branch_name )SQL中的参照完整性中的参照完整性 create table account(account_numberchar(10),branch_name char(15),balanceinteger,primary key (account_number), foreign key (branch_name) references branch )create table depositor(customer_namechar

38、(20),account_numberchar(10),primary key (customer_name, account_number),foreign key (account_number ) references account,foreign key (customer_name ) references customer )4.2.6 断言（断言（ASSERTIONS）n断言是一个谓词，表达一个我们希望数据库总能满断言是一个谓词，表达一个我们希望数据库总能满足的条件。足的条件。n域约束和参照完整性约束是断言的特殊形式域约束和参照完整性约束是断言的特殊形式n nSQL 中断言的形

39、式：中断言的形式：create assertion check 4.2.6 断言（断言（ASSERTIONS）n建立断言时，系统要检测其有效性。如果有效，以建立断言时，系统要检测其有效性。如果有效，以后每次对可能违反该断言的数据库的修改将被检测后每次对可能违反该断言的数据库的修改将被检测n这种检测可能带来巨大的工作量，所以要小心使用。这种检测可能带来巨大的工作量，所以要小心使用。n断言断言 for all X, P(X) 的实现是通过下面方式（的实现是通过下面方式（SQL不提供不提供for all X, P(X) 结构结构）实现：）实现： not exists X such that not

40、P(X)4.2.6 断言（断言（ASSERTIONS）n前面用大量篇幅介绍了这几种断言，是因为它们容前面用大量篇幅介绍了这几种断言，是因为它们容易检测并且适用于很多数据库应用。但是，还有许易检测并且适用于很多数据库应用。但是，还有许多约束不能用这几种特殊形式表达。多约束不能用这几种特殊形式表达。n如有两个这样的例子：如有两个这样的例子：n 每个支行的贷款金额总和必须少于该支行账户每个支行的贷款金额总和必须少于该支行账户余额总和。余额总和。n 每笔贷款的客户中至少有一人的账户余额不小每笔贷款的客户中至少有一人的账户余额不小于于1000. 00美元。美元。断言断言 例子例子n每笔贷款中至少有一个借

41、款人拥有余额在每笔贷款中至少有一个借款人拥有余额在$1000.00以上的存以上的存款帐户款帐户 create assertion balance_constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan.loan_number = borrower.loan_number and borrower.customer_name = depositor.customer_name and depositor.ac

42、count_number = account.account_number and account.balance = 1000)n每个支行的贷款总额必须小于帐户余额总数每个支行的贷款总额必须小于帐户余额总数 create assertion sum_constraint check (not exists (select * from branch where (select sum(amount ) from loan where loan.branch_name=branch.branch_name ) = (select sum (amount ) from account where

43、 loan.branch_name= branch.branch_name )n 当创建断言时，系统要检测其有效性。如当创建断言时，系统要检测其有效性。如果断言有效，则以后只有不破坏断言的数据库果断言有效，则以后只有不破坏断言的数据库修改才被允许。如果断言较复杂，则检测会带修改才被允许。如果断言较复杂，则检测会带来相当大的开销。因此，使用来相当大的开销。因此，使用断言应该特别小断言应该特别小心。心。n由于检测和维护断言的开销较高，一些系统开由于检测和维护断言的开销较高，一些系统开发者省去了对一般性断言的支持，或只提供易发者省去了对一般性断言的支持，或只提供易于检测的特殊形式的断言。于检测的特殊

44、形式的断言。 4.3 授权（授权（Authorization）我们可能会为一个用户在数据库某些部分指定几种形式的我们可能会为一个用户在数据库某些部分指定几种形式的授权：授权：nRead 允许读取允许读取, 但不能进行数据修改但不能进行数据修改nInsert 允许插入新数据允许插入新数据,但不能修改现有数据但不能修改现有数据nUpdate 允许修改允许修改, 但不能删除数据但不能删除数据nDelete 允许删除数据允许删除数据数据库模式修改授权的形式：数据库模式修改授权的形式：nIndex 允许建立和删除索引允许建立和删除索引nResources 允许建立新的关系允许建立新的关系nAlterat

45、ion 允许增加或删除关系中的属性允许增加或删除关系中的属性nDrop 允许删除关系允许删除关系n每种类型的授权都称为一个权限。我们可能在每种类型的授权都称为一个权限。我们可能在数据库某些指定的部分（如一个关系或一个视数据库某些指定的部分（如一个关系或一个视图）授权给用户所有这些权限，或完全不授权图）授权给用户所有这些权限，或完全不授权，或这些权限的组合。，或这些权限的组合。 nSQL标准包括标准包括delete、insert、select和和update权权限。限。select权限授予用户读取数据的权限。除权限授予用户读取数据的权限。除了这些形式的访问数据的权限以外，还支持其了这些形式的访问

46、数据的权限以外，还支持其他几种权限，如创建、删除或修改关系的权限他几种权限，如创建、删除或修改关系的权限，还有执行程序的权限。，还有执行程序的权限。 n “所有权限所有权限” 可以用作所有允许的权限的简可以用作所有允许的权限的简写形式。写形式。一个创建了新关系的用户将自动被授一个创建了新关系的用户将自动被授予该关系上的所有权限。予该关系上的所有权限。n一个拥有某些形式的权限的用户可以把该权限一个拥有某些形式的权限的用户可以把该权限传递给（授予）其他用户，或撤销（收回）以传递给（授予）其他用户，或撤销（收回）以前授予的权限。前授予的权限。nSQL数据定义语言包括授予和收回权限的命令数据定义语言包

47、括授予和收回权限的命令 ngrant 声明用来授予权限声明用来授予权限grant on to n 是是:n一个用户的一个用户的IDnpublic, 允许授权到所有有效用户（角色允许授权到所有有效用户（角色 ）n对视图的授权并不意味对下层逻辑关系的任何授权对视图的授权并不意味对下层逻辑关系的任何授权nSQL允许通过允许通过“授予权限授予权限”权限来指定权限被授予者的权限来指定权限被授予者的权限授予权（权限授予权（授予其它用户自己已被授予的权限授予其它用户自己已被授予的权限）n权限的授予者必须已经持有特定权限项（或是权限的授予者必须已经持有特定权限项（或是DBA）ngrant语句授予数据库用户语句

48、授予数据库用户John和和Mary关系关系account上的上的select权限：权限：n grant select on account to John, Mary ngrant 声明用来授予权限声明用来授予权限update授权既可以在关系的所有属性上进行，授权既可以在关系的所有属性上进行，又可以只在某些属性上进行。又可以只在某些属性上进行。 如果如果grant语句中包括语句中包括update授杈，将被授予授杈，将被授予update权限的属性列表可以出现在紧跟关键字权限的属性列表可以出现在紧跟关键字update的括号中。如果省略属性列表，则是在的括号中。如果省略属性列表，则是在关系的所有属性

49、上授予关系的所有属性上授予update权限权限。n下面的下面的grant语句授予用户语句授予用户John和和Mary关系关系loan的的amount属性上的属性上的update权限：权限：n grant update (amount) on loan to John, Maryn insert权限也可以说明属性列表；对关系作插权限也可以说明属性列表；对关系作插入时只允许申明这些属性，而系统将其余各属入时只允许申明这些属性，而系统将其余各属性或者赋默认值（如果该属性有默认值定义）性或者赋默认值（如果该属性有默认值定义），或者置为空。，或者置为空。n 用户用户public指系统所有当前用户和将来的

50、用指系统所有当前用户和将来的用户。因此，授予户。因此，授予public的权限隐含着授予所有的权限隐含着授予所有当前用户和将来的用户。当前用户和将来的用户。n 默认情况下，授予了一个权限的用户角色是默认情况下，授予了一个权限的用户角色是不允许把该权限授予其他用户角色的不允许把该权限授予其他用户角色的. SQL允许通过允许通过授予权限授予权限来指定权限的接受者可以进来指定权限的接受者可以进一步把该权限授予其他用户。一步把该权限授予其他用户。 在在8.7节详细讨节详细讨论这个特性。论这个特性。SQL中的权限（中的权限（ privileges）nselect: 允许读取关系，或者使用视图查询允许读取关

51、系，或者使用视图查询n例子例子: grant select on branch to U1, U2, U3ninsert: 插入元组插入元组nupdate: 使用使用SQL更新声明来更新更新声明来更新ndelete: 删除元组删除元组nall privileges: 所有允许的权限。关系的建立者自所有允许的权限。关系的建立者自动被授予该关系上所有权限。动被授予该关系上所有权限。n其它其它SQL 中授权的收回（中授权的收回（revoke）nrevoke 声明用来收回授权nrevoke on from n例子:revoke select on branch from U1, U2, U3n 可以是

52、 all ，用来收回被收回者持有的所有权限n如果 包括 public，除了那些显式授权的用户，其它所有用户将失去权限。n如果某权限被两个不同授予者两次授予同一用户，收回操作后该用户可能保留了这些权限n所有依赖于被收回权限的权限也一起被收回了。nrevoke select on branch from John, Maryn revoke update (amount) on loan from John, Mary4.4 嵌入式嵌入式 SQLnSQL标准定义了在标准定义了在 C、Java、和、和 Cobol等中嵌入等中嵌入 SQLn被嵌入被嵌入SQL 查询的语言称为查询的语言称为宿主语言宿主语

53、言, 宿主语宿主语言中使用的言中使用的SQL结构称为嵌入式结构称为嵌入式SQLnEXEC SQL 声明用来识别嵌入式声明用来识别嵌入式SQL请求：请求：EXEC SQL END_EXEC注意注意: 确切语法依赖于宿主语言确切语法依赖于宿主语言 如如Java：# SQL ; 查询例子查询例子n确定SQL查询，并声明代表它的游标（cursor ） EXEC SQL declare c cursor for select depositor.customer_name, customer_city from depositor, customer, account where depositor.c

54、ustomer_name = customer.customer_name and depositor account_number = account.account_numberand account.balance :amount END_EXEC变量以用于程序和数据库系统间的通信。在嵌入的SQL语句中可以使用宿主语言的变量，不过前面要加上冒号(：)以区别于SQL变量。n假设有宿主变量假设有宿主变量amount, 我们需要找出所有在银行有我们需要找出所有在银行有余额大于余额大于amount 的帐户的客户的名字和居住城市的帐户的客户的名字和居住城市nopen 语句用来使查询被求值语句用来使

55、查询被求值EXEC SQL open c END_EXECnfetch 语句用来把一个元组的值放到宿主变量中语句用来把一个元组的值放到宿主变量中EXEC SQL fetch c into :cn, :cc END_EXEC重复使用重复使用 fetch 得到查询结果中连续的元组得到查询结果中连续的元组nSQL通讯区通讯区 (SQLCA)中的有一个叫做中的有一个叫做 SQLSTATE 的的变量，它被置为变量，它被置为 02000 时，表示再无可用数据了，时，表示再无可用数据了，即后面不再有待处理元组了即后面不再有待处理元组了nclose 语句用来使数据库删除保存查询结果的临时关系语句用来使数据库删

56、除保存查询结果的临时关系EXEC SQL close c END_EXEC注意注意: 上面细节随宿主语言而变化，如上面细节随宿主语言而变化，如 Java 中，定义中，定义了了Java iterators 来遍历结果元组集来遍历结果元组集通过游标更新通过游标更新n通过声明更新游标，可以更新游标取得的元组： declare c cursor for select * from account where branch_name = Perryridge for updaten下面更新游标 c 在当前位置的元组（每次fetch都执行下面代码） update account set balance =

57、 balance + 100 where current of c4.5 动态动态 SQLn 在许多情况下，应用要执行的具体的 SQL 语句在书写应用的时候就已经知道了。 不过，在某些情况下，SQL 语句是在运行时或者由外部的数据提供的。 在这种情况下，我们不能直接在 C 代码嵌入 SQL 语句，但是有个机制可以允许你调用放在一个字串变量里的任何 SQL 语句。 4.5 动态动态 SQLn SQL的动态SQL组件允许程序在运行时构造、提交SQL查询。与此相反，嵌入式SQL语句必须在编译时全部确定，并交由嵌入式SQL预处理器编译。n使用动态SQL，程序能够在运行时（也许基于用户输入）以字符串的形

58、式生成SQL查询，立即执行该查询或使其为后续使用作好准备。准备动态SQL语句是对其进行编译，使用预备语句就是使用已编译过的版本。 4.5 动态动态 SQLn允许程序在运行时构造和递交SQL查询n例子：C程序中使用动态 SQL char * sqlprog = “update account set balance = balance * 1.05 where account_number = ？”EXEC SQL prepare dynprog from :sqlprog;char account 10 = “A-101”;EXEC SQL execute dynprog using :acc

59、ount;n这个动态 SQL程序包含 一个“ ? ”, 它持有了一个在SQL 程序执行时刻提供的值ODBC AND JDBCnODBC和和JDBC都是都是API (application-program interface) ，被应用程序用来与数据库服务器交互，被应用程序用来与数据库服务器交互n应用用它们来：应用用它们来：n与数据库服务器取得连接与数据库服务器取得连接n发送发送 SQL 命令到数据库服务器命令到数据库服务器n一个一个从查询结果中取得元组到程序变量中一个一个从查询结果中取得元组到程序变量中nODBC (Open Database Connectivity) ： C, C+, C#

60、, 和和 Visual Basic中使用中使用nJDBC (Java Database Connectivity) ： Java中使用中使用ODBCnODBC标准 n是应用程序与数据库服务器通讯的标准n是API： n打开与数据库的连接 n发送查询和更新n返回结果nGUI、电子表格等应用可以使用 ODBCODBC n支持支持ODBC的数据库系统提供一个与客户端程序相连接的的数据库系统提供一个与客户端程序相连接的驱动库。当客户端发送驱动库。当客户端发送ODBC 请求时，库中的代码和服请求时，库中的代码和服务器通讯，执行请求并取回结果务器通讯，执行请求并取回结果nODBC程序首先分配一个程序首先分配一个SQL环境环境, 然后是一个数据库连然后