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

《数据库系统概论：第4章 高级SQL》由会员分享，可在线阅读，更多相关《数据库系统概论：第4章 高级SQL（71页珍藏版）》请在金锄头文库上搜索。

1、第第4章章 高级高级SQL吉林大学计算机科学与技术学院吉林大学计算机科学与技术学院数据库系统概论数据库系统概论第四章第四章 高级高级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例子例子:date2005-7-27

2、ntime:一天中的时间：小时、分钟、秒一天中的时间：小时、分钟、秒n例子例子:time09:00:30time09:00:30.75ntimestamp:时间戳：日期加上时间时间戳：日期加上时间n例子例子:timestamp2005-7-2709:00:30.75ninterval:时间期间时间期间n例子例子:interval1dayndate/time/timestamp值相减得到期间值值相减得到期间值n期间值能够和期间值能够和date/time/timestamp值相加值相加SQL 中内建数据类型中内建数据类型 n能从能从date/time/timestamp中抽取个体字段的值中抽取个体

3、字段的值n例子例子:extract(yearfromr.starttime)n能将符合正确格式的字符串类型转化为能将符合正确格式的字符串类型转化为ndate/time/timestamp类型类型n例子例子:castasdaten例子例子:castastime 4.1.2 用户定义类型用户定义类型ncreatetype结构建立结构建立用户定义类型用户定义类型createtypeDollarsasnumeric(12,2)finalcreatetypePounds asnumeric(12,2)finaln总共总共12位数字的十进制数，其中两位小数。（在这个环境下位数字的十进制数，其中两位小数。（

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

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

6、umeric(12,2)SQL还提供了还提供了droptype和和altertype子句来删除或修改以前子句来删除或修改以前创建过的类型。创建过的类型。关于域约束关于域约束n域约束（域约束（domainconstraints）是完整性约束最基本是完整性约束最基本的形式。的形式。数据库插入和查询等将进行该约束测试数据库插入和查询等将进行该约束测试，以，以确保比较有意义。确保比较有意义。n新的域可以用现有数据类型来建立新的域可以用现有数据类型来建立n例子例子:createdomainDollarsnumeric(12,2)createdomainPoundsnumeric(12,2)n我们不能将我

7、们不能将 Dollars 值赋予值赋予 Pounds类型，二者也无类型，二者也无法进行比较法进行比较类型和域的区别n类型和域之间有两个重大的区别：类型和域之间有两个重大的区别：n1在域上可以指定约束在域上可以指定约束，例如，例如notnull，也，也可以可以为域类型变量定义默认值为域类型变量定义默认值，然而用户定，然而用户定义类型上不能指定约束或默认值。用户定义义类型上不能指定约束或默认值。用户定义类型不仅被设计来指定属性类型，还用于在类型不仅被设计来指定属性类型，还用于在不能施加约束的地方对不能施加约束的地方对SQL进行过程扩展。进行过程扩展。 n2.域并不是强类型的。因此域并不是强类型的。

8、因此一个域类型的值一个域类型的值可以被赋给另一个域类型可以被赋给另一个域类型，只要它们的类型，只要它们的类型是相容的。是相容的。 4.1.3 大对象（大对象（Large-Object）类型）类型n大对象大对象 (photos,videos,CAD文档文档,等等等等)以下面类型存以下面类型存储储 nblob:binarylargeobject二进制二进制数据的大对象数数据的大对象数 。n对象是未翻译的（如何翻译留给数据库系统以外的应对象是未翻译的（如何翻译留给数据库系统以外的应用）用）nclob:characterlargeobject字符数据的大对象字符数据的大对象n 对象是大量字符数据集合对

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

10、器定位器”，然后用这个定位器来处理宿主，然后用这个定位器来处理宿主语言的对象。例如，语言的对象。例如，JDBC应用程序接口应用程序接口 允许取出一个定允许取出一个定位器而不是整个大对象；然后可以用这个定位器来一点一位器而不是整个大对象；然后可以用这个定位器来一点一点地取出这个大对象，而不是一次取出全部，这很像用一点地取出这个大对象，而不是一次取出全部，这很像用一个个read函数调用从一个操作系统文件中读取数据。函数调用从一个操作系统文件中读取数据。4.1.4 模式、目录与环境模式、目录与环境n现代数据库系统提供一个现代数据库系统提供一个三层结构的关系三层结构的关系命名机制命名机制。n最顶层由目

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

12、下）。每个用户都有一个默认的目录和模式每个用户都有一个默认的目录和模式，这个组合，这个组合对用户来说是唯一的。当一个用户连接到数据库对用户来说是唯一的。当一个用户连接到数据库系统时，将为该连接设置好默认的目录和模式。系统时，将为该连接设置好默认的目录和模式。这意味着当用户登录进一个操作系统时，把当前这意味着当用户登录进一个操作系统时，把当前目录设置为用户的目录设置为用户的home(主主)目录。目录。4.1.4 模式、目录与环境模式、目录与环境n为了唯一标识一个关系，必须使用一个名字为了唯一标识一个关系，必须使用一个名字，该名字包含三部分，仍如：该名字包含三部分，仍如：ncatalog5.ban

13、k_schema.accountn当连接到默认目录时，可以忽略目录成分。这样当连接到默认目录时，可以忽略目录成分。这样如果如果catalog5是默认目录，我们可以用是默认目录，我们可以用bank_schema.account来唯一标识上述关系。进一步来唯一标识上述关系。进一步地，若是连接到默认模式，我们也可以忽略模式的地，若是连接到默认模式，我们也可以忽略模式的名字。这样如果默认目录是名字。这样如果默认目录是catalog5，默认模式是，默认模式是bank_schema，我们可以只用，我们可以只用account。4.1.4 模式、目录与环境模式、目录与环境n当有多个目录和模式可用时，不同应用和

14、不同用户当有多个目录和模式可用时，不同应用和不同用户可以独立工作而不必担心命名冲突。可以独立工作而不必担心命名冲突。n 所有通常的所有通常的SQL语句，包括语句，包括DDL和和DML语句，都语句，都在一个模式的环境中运行。在一个模式的环境中运行。n我们可以用我们可以用createschema和和dropschema语句来语句来创建和删除模式。创建和删除模式。n创建和删除目录依据实现的不同而不同，这不是创建和删除目录依据实现的不同而不同，这不是SQL标准中的一部分。标准中的一部分。 42 完整性约束完整性约束n完整性约束完整性约束通过确保被授权的对数据库的修改不会导通过确保被授权的对数据库的修改

15、不会导致数据一致性的丢失，来防止数据库免予意外破坏。致数据一致性的丢失，来防止数据库免予意外破坏。n关系的完整性是关系型数据模型结构中的另一组成部关系的完整性是关系型数据模型结构中的另一组成部分，它分为分，它分为实体完整性实体完整性、参照完整性参照完整性和和用户定义完整用户定义完整性性三类。三类。n实体完整性实体完整性是通过是通过主码主码（PRIMARYKEY）的定义来）的定义来实现的。一旦某个属性或属性组被定义为主码，该主实现的。一旦某个属性或属性组被定义为主码，该主码的每个属性就码的每个属性就不能为空值不能为空值，并且在表中不能出现主，并且在表中不能出现主码值码值完全相同的两个记录完全相同

16、的两个记录。实体完整性实体完整性n n主码可以在主码可以在CREATETABLE语句中使用语句中使用PRIMARYKEY定义。有两种定义主码的方法：定义。有两种定义主码的方法：n一种是在属性后增加关键字一种是在属性后增加关键字PRIMARYKEY n另一种是在属性表中加入额外的定义主码的子句另一种是在属性表中加入额外的定义主码的子句 PRIMARYKEY（主码属性名表）。（主码属性名表）。SQL中的实体（主码）完整性中的实体（主码）完整性（1）属性后增加关键字定义）属性后增加关键字定义CREATETABLEStudentInfo(StudentIDchar(8)PRIMARYKEY,Stude

17、ntNamevarchar(10),StudentSexbit);（2）加入额外的定义主码的子句）加入额外的定义主码的子句CREATETABLEStudentInfo(StudentIDchar(8)，StudentNamevarchar(10),StudentSexbit,PRIMARYKEY(StudentID);如果表的主码只含有单个属性，上面的两种方法都可以使用。如果表的主码只含有单个属性，上面的两种方法都可以使用。如果主码由如果主码由多个属性组成，只能使用第二种方法。多个属性组成，只能使用第二种方法。n除了主码，除了主码，SQL提供了类似提供了类似候候选码选码的说明方法，使用关键字的

18、说明方法，使用关键字UNIQUE定定义（义（ 定义为定义为UNIQUE的属性可以定义为的属性可以定义为空值空值，但只能有一个记录该属性的值为，但只能有一个记录该属性的值为NULL），说明该属性（或属性组）的值），说明该属性（或属性组）的值不能重复。不能重复。一个表中只能有一个主码，但可以有多一个表中只能有一个主码，但可以有多个个“UNIQUE”定义。定义。42 完整性约束完整性约束n企业过往帐户至少有企业过往帐户至少有$10,000.00以上余额以上余额n银行雇员薪水至少每小时银行雇员薪水至少每小时$4.00n客户必须有非空客户必须有非空(non-null)电话号码电话号码n 一个账户的余额不

19、能为空。一个账户的余额不能为空。n任何两个账户不能有相同的账号任何两个账户不能有相同的账号n完整性约束可以是与数据库有关的任意谓词，完整性约束可以是与数据库有关的任意谓词，但任意谓词的但任意谓词的检测代价可能很高检测代价可能很高n许多数据库允许用户指定那些只需极小开销就可检测许多数据库允许用户指定那些只需极小开销就可检测的完整性约束的完整性约束。 4.2.1 单个关系上的约束单个关系上的约束 ncreatetable命令也可以包括完整性约束语命令也可以包括完整性约束语句句。除了。除了“主码主码”约束以外，还有其他许约束以外，还有其他许多可以包括在多可以包括在createtable命令里的约束。

20、命令里的约束。允许的完整性约束包括：允许的完整性约束包括：nnotnullnprimarykeynuniquencheck(P ), 这里P 是谓词4.2.2 Not Null 约束约束 nnull值是所有域的成员，值是所有域的成员， 是是SQL中每个属中每个属性的默认合法值性的默认合法值。然而对于一些属性来说，。然而对于一些属性来说，空值可能是不合适的。空值可能是不合适的。 n例如我们例如我们不会希望一个账户的余额为空不会希望一个账户的余额为空。n在这些情况下，我们希望禁止空值。在这些情况下，我们希望禁止空值。4.2.2 Not Null 约束约束 n 我们可以通过我们可以通过限定属性限定属

21、性balance的域来排的域来排除空值除空值naccount_numberchar(10)notnullnbalancenumeric(12,2)notnullnnotnull的限定禁止在该属性上插人一个空值的限定禁止在该属性上插人一个空值。任何可能导致向一个声明为任何可能导致向一个声明为notnull的属性插入的属性插入一个空值的数据库修改都会产生错误诊断信息。一个空值的数据库修改都会产生错误诊断信息。4.2.2 Not Null 约束约束 n许许多多情情况况下下我我们们会会希希望望避避免免空空值值，尤尤其其是是SQL禁禁止在关系模式的主码中出现空值。止在关系模式的主码中出现空值。n 因此主

22、码不必显式地声明为因此主码不必显式地声明为notnull.nnotnull的限定同样可以被用在的限定同样可以被用在用户定义域用户定义域的声明的声明中；由此该域类型的属性不能为空中；由此该域类型的属性不能为空 n例如，如果我们希望例如，如果我们希望Dollars域不能有空值，我们域不能有空值，我们可以作如下声明：可以作如下声明：ncreatedomainDollarsnumeric(12,2)notnull4.2.3 Unique 约束约束nunique(A1,A2,Am)nunique限定指出属性限定指出属性A1,A2,Am形成一个候选码形成一个候选码n候选码属性允许为候选码属性允许为null

23、 （这和主码不同）除非（这和主码不同）除非它们已被显示地声明为非空它们已被显示地声明为非空n注意对空值的比较运算结果注意对空值的比较运算结果4.2.4 check 子句子句nSQL中的中的check子句可以用于子句可以用于关系声明和域声明关系声明和域声明。当用于关系声明时，当用于关系声明时，check子句指定一个谓词子句指定一个谓词P，关系中的所有元组都必须满足该谓词。关系中的所有元组都必须满足该谓词。 ncheck子句的子句的通常应用是保证属性值满足指定的通常应用是保证属性值满足指定的条件条件，由此创建一个强大的类型系统。，由此创建一个强大的类型系统。n例如，创建关系例如，创建关系branc

24、h的的createtable命令中的命令中的一个子句一个子句check（assets=0）将保证资产值是）将保证资产值是非负的。非负的。 check (P ), 这里 P 是谓词。例子例子:声明声明branch_name为为branch的主码，并的主码，并且确保且确保assets的值为非负的值为非负createtablebranch (branch_name char(15),branch_city char(30),assets integer,primarykey(branch_name), check(assets = 0)createtabIestudent(namechar(15)n

25、otnull,student_idchar(10),degree_levelchar(15)，primarykey(student_id)，check(degree_levelin(Bachelors，Masters，Doctorate)例如：用例如：用check子句可以保证小时工资域的值必须大于某子句可以保证小时工资域的值必须大于某一指定值（如最低工资）：一指定值（如最低工资）：createdomainHourlyWagenumber(5,2)constraintwage_value_testcheck(value=6.00) 域域HourlyWage有一个约束有一个约束，以保证小时工资数大

26、于或等于，以保证小时工资数大于或等于6.00。子句子句constraintwage_value_test是可选的是可选的，它用来，它用来将该约束命名为将该约束命名为wage_value_test。系统用这个名字指出一。系统用这个名字指出一个更新违反了哪个约束。个更新违反了哪个约束。作为另一个例子，使用作为另一个例子，使用in子句可以限定一个域只包含指定的一子句可以限定一个域只包含指定的一组值：组值：createdomainAccountTypechar(10)constraintaccount_type_testcheck(valuein(Checking,Saving)check条件中允许出

27、现包含其他关系的子查询，条件中允许出现包含其他关系的子查询，check条件可条件可能变得更复杂（也更难检测）能变得更复杂（也更难检测）.例如，这个约束可以指定作用在关系例如，这个约束可以指定作用在关系deposit中：中：check(branch_namein(selectbranch_namefrombranch)这个这个check条件检测在条件检测在deposit关系中每一个关系中每一个branch_name元组的确是在关系元组的确是在关系branch中的分行名称。中的分行名称。因此这个条件必须不仅仅是因此这个条件必须不仅仅是deposit中插入或修改中插入或修改一个元组的时一个元组的时候

28、检测，而且在关系候检测，而且在关系branch改变改变的时候也要检测（如在关系的时候也要检测（如在关系branch中，当一个元组中，当一个元组被删除或修改被删除或修改的情况下）的情况下）复杂的复杂的check条件在我们希望确保数据完整性的时候是很有用的，条件在我们希望确保数据完整性的时候是很有用的，但要但要谨慎地使用谨慎地使用，因为检测它们的，因为检测它们的开销会很大开销会很大。 SQL SQL标准允许将一个子句标准允许将一个子句initially deterredinitially deterred加入到约加入到约束说明中；这样该完整性约束不是在事务的中间步骤上检查，束说明中；这样该完整性约

29、束不是在事务的中间步骤上检查，而是在而是在事务结束的时候检查事务结束的时候检查。 一个约束可以被指定为可延迟的一个约束可以被指定为可延迟的, ,意味着默认状态下它会立意味着默认状态下它会立即被检查，但是当需要的时候可以延迟。对于声明为可延迟的即被检查，但是当需要的时候可以延迟。对于声明为可延迟的约束，执行一个语句约束，执行一个语句set constraints constraint-list set constraints constraint-list deferreddeferred作为事务的一部分，这会作为事务的一部分，这会使指定约束的检查被延迟到使指定约束的检查被延迟到事务结束时执行。

30、事务结束时执行。 注意，注意， 默认的行为是立即检查约束，而且许多数据库实现默认的行为是立即检查约束，而且许多数据库实现不支持延迟约束检查。不支持延迟约束检查。4.2.5 参照完整性（参照完整性（Referential Integrity）n确保一个关系上给定属性集上的取值也在另一关系确保一个关系上给定属性集上的取值也在另一关系某一属性集上的取值中存在。某一属性集上的取值中存在。n n例例:account表有一个声明：表有一个声明：nforeignkey（branch_name）referencesbranch，这个外码声明指，这个外码声明指 明对于账户中的每个明对于账户中的每个元组，元组中指

31、定的分行名字必须在元组，元组中指定的分行名字必须在branch关关系中存在系中存在.没有这个约束，可能会为一个账户指定没有这个约束，可能会为一个账户指定一个不存在的分行名字。一个不存在的分行名字。4.2.5 参照完整性（参照完整性（Referential Integrity）n一般地说，令关系一般地说，令关系r1(R1)和和r2(R2)的主码分别为的主码分别为K1和和K2。如果要求对。如果要求对r2中任意元组中任意元组t2，均存在，均存在r1中元中元组组t1使得使得t1K1=t2a，我们称，我们称R2的子集的子集a为参照关为参照关系系r1中中K1的外码。这种要求称为的外码。这种要求称为参照完整

32、性约束参照完整性约束或或子集依赖子集依赖。 n参照完整性，又被称为参照完整性，又被称为关联完整性关联完整性，意思是说，如，意思是说，如果一个表包含一个外键列，那么这个列中的每一个果一个表包含一个外键列，那么这个列中的每一个值值(除了空，如果允许为空除了空，如果允许为空)都在关联表或被参照表都在关联表或被参照表的主键里。的主键里。 4.2.5 参照完整性（参照完整性（Referential Integrity）n参照完整性属于表间规则。对于永久关系的相关表，参照完整性属于表间规则。对于永久关系的相关表，在在更新、插入或删除记录时，如果只改其一不改其更新、插入或删除记录时，如果只改其一不改其二，就

33、会影响数据的完整性二，就会影响数据的完整性n例如修改父表中关键字值后，子表关键字值未做相例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录；对于子表插入删除，致使这些记录称为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录；等等。的记录，父表中没有相应关键字值的记录；等等。对于这些设计表间数据的完整性，统称为参照完整对于这些设计表间数据的完整性，统称为参照完整性。性。n限制策略限制策略n外键约束对子表的含义外键约束对子表的含义: : n如果在父表中找不到候选键如果在父表中找

34、不到候选键, ,则不允许在子表上进则不允许在子表上进行行insert/update insert/update n级联策略级联策略 n外键约束对父表外键约束对父表( (被参照表被参照表) )的含义的含义: : 在父表上进行在父表上进行update/delete update/delete 以更新或删除在子表以更新或删除在子表中有一条或多条对应匹配行的候选键时中有一条或多条对应匹配行的候选键时, ,父表的行父表的行为取决于：在定义子表的外键时指定的为取决于：在定义子表的外键时指定的 on on update/on delete update/on delete 子句子句 4.2.5 参照完整性（

35、参照完整性（Referential Integrity）n 主码、候选码、外码能够在主码、候选码、外码能够在SQL的的createtable声声明明中使用中使用:nprimarykey子句列表组成主码的属性子句列表组成主码的属性nuniquekey子句列表组成候选码的属性子句列表组成候选码的属性nforeignkey子句列表组成外码的属性和被该外子句列表组成外码的属性和被该外码参照的关系。码参照的关系。按照默认，按照默认， 外码参照的是参照表外码参照的是参照表中的主码属性。中的主码属性。SQL中的实体（主码）完整性中的实体（主码）完整性createtablecustomer(customer_

36、namechar(20),customer_streetchar(30),customer_city char(30),primarykey (customer_name )createtablebranch(branch_name char(15),branch_citychar(30),assetsnumeric(12,2),primarykey (branch_name )SQL中的参照完整性中的参照完整性 createtable account(account_numberchar(10),branch_name char(15),balance integer,primarykey

37、(account_number), foreignkey (branch_name) referencesbranch )createtabledepositor(customer_namechar(20),account_numberchar(10),primarykey (customer_name, account_number),foreignkey (account_number )referencesaccount,foreignkey (customer_name ) referencescustomer )4.2.6 断言（断言（Assertions）n断言是一个谓词，表达一个

38、我们希望数据库总能满断言是一个谓词，表达一个我们希望数据库总能满足的条件。足的条件。n域约束和参照完整性约束是断言的特殊形式域约束和参照完整性约束是断言的特殊形式nnSQL中断言的形式：中断言的形式：createassertioncheck4.2.6 断言（断言（Assertions）n建立断言时，系统要检测其有效性。如果有效，以建立断言时，系统要检测其有效性。如果有效，以后每次对可能违反该断言的数据库的修改将被检测后每次对可能违反该断言的数据库的修改将被检测n这种检测可能带来巨大的工作量，所以要小心使用。这种检测可能带来巨大的工作量，所以要小心使用。n断言断言 forallX,P(X)的实现

39、是通过下面方式（的实现是通过下面方式（SQL不提供不提供forallX,P(X)结构结构）实现：）实现：notexistsXsuchthatnotP(X)4.2.6 断言（断言（Assertions）n前面用大量篇幅介绍了这几种断言，是因为它们容前面用大量篇幅介绍了这几种断言，是因为它们容易检测并且适用于很多数据库应用。但是，还有许易检测并且适用于很多数据库应用。但是，还有许多约束不能用这几种特殊形式表达。多约束不能用这几种特殊形式表达。n如有两个这样的例子：如有两个这样的例子：n每个支行的贷款金额总和必须少于该支行每个支行的贷款金额总和必须少于该支行账户余额总和。账户余额总和。n每笔贷款的客

40、户中至少有一人的账户余额每笔贷款的客户中至少有一人的账户余额不小于不小于1000.00美元。美元。断言断言 例子例子n每笔贷款中至少有一个借款人拥有余额在每笔贷款中至少有一个借款人拥有余额在$1000.00以上的以上的存款帐户存款帐户createassertionbalance_constraint check(notexists(select*fromloan wherenotexists(select*fromborrower, depositor, account whereloan.loan_number = borrower.loan_number andborrower.custo

41、mer_name = depositor.customer_name and depositor.account_number = account.account_number andaccount.balance = 1000)n每个支行的贷款总额必须小于帐户余额总数每个支行的贷款总额必须小于帐户余额总数createassertion sum_constraint check(notexists(select*frombranch where(selectsum(amount )fromloan whereloan.branch_name=branch.branch_name ) = (se

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

43、一般性断言的支持，或只提供易于检测的特殊形式的断言。于检测的特殊形式的断言。 4.3 授权（授权（Authorization）我们可能会为一个用户在数据库某些部分指定几种形式的授我们可能会为一个用户在数据库某些部分指定几种形式的授权：权：nRead允许读取允许读取,但不能进行数据修改但不能进行数据修改nInsert允许插入新数据允许插入新数据,但不能修改现有数据但不能修改现有数据nUpdate允许修改允许修改,但不能删除数据但不能删除数据nDelete允许删除数据允许删除数据数据库模式修改授权的形式：数据库模式修改授权的形式：nIndex允许建立和删除索引允许建立和删除索引nResources

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

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

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

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

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

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

50、询n例子例子:grantselectonbranch toU1, U2, U3ninsert:插入元组插入元组nupdate:使用使用SQL更新声明来更新更新声明来更新ndelete:删除元组删除元组nallprivileges:所有允许的权限。关系的建立者所有允许的权限。关系的建立者自动被授予该关系上所有权限。自动被授予该关系上所有权限。n其它其它SQL 中授权的收回（中授权的收回（revoke）nrevoke声明用来收回授权nrevoke on from n例子:revokeselectonbranch fromU1, U2, U3n 可以是all，用来收回被收回者持有的所有权限n如果 包

51、括public，除了那些显式授权的用户，其它所有用户将失去权限。n如果某权限被两个不同授予者两次授予同一用户，收回操作后该用户可能保留了这些权限n所有依赖于被收回权限的权限也一起被收回了。nrevokeselectonbranchfromJohn,Marynrevokeupdate(amount)onloanfromJohn,Mary4.4 嵌入式嵌入式 SQLnSQL标准定义了在标准定义了在 C、Java、和、和 Cobol等中嵌等中嵌入入 SQLn被嵌入被嵌入SQL查询的语言称为查询的语言称为宿主语言宿主语言,宿主语宿主语言中使用的言中使用的SQL结构称为嵌入式结构称为嵌入式SQLnEXE

52、CSQL声明用来识别嵌入式声明用来识别嵌入式SQL请求：请求：EXECSQLEND_EXEC注意注意:确切语法依赖于宿主语言确切语法依赖于宿主语言如如Java：#SQL; 查询例子查询例子n确定SQL查询，并声明代表它的游标（cursor ）EXECSQL declareccursorforselectdepositor.customer_name, customer_city fromdepositor, customer, account wheredepositor.customer_name = customer.customer_name and depositor account_

53、number = account.account_numberandaccount.balance :amountEND_EXEC变量以用于程序和数据库系统间的通信。在嵌入的SQL语句中可以使用宿主语言的变量，不过前面要加上冒号(：)以区别于SQL变量。n假设有宿主变量假设有宿主变量amount,我们需要找出所有在银行有我们需要找出所有在银行有余额大于余额大于amount 的帐户的客户的名字和居住城市的帐户的客户的名字和居住城市nopen语句用来使查询被求值语句用来使查询被求值EXECSQLopenc END_EXECnfetch语句用来把一个元组的值放到宿主变量中语句用来把一个元组的值放到宿

54、主变量中EXECSQLfetchc into:cn, :ccEND_EXEC重复使用重复使用 fetch得到查询结果中连续的元组得到查询结果中连续的元组nSQL通讯区通讯区(SQLCA)中的有一个叫做中的有一个叫做 SQLSTATE的的变量，它被置为变量，它被置为02000时，表示再无可用数据了，时，表示再无可用数据了，即后面不再有待处理元组了即后面不再有待处理元组了nclose语句用来使数据库删除保存查询结果的临时关语句用来使数据库删除保存查询结果的临时关系系EXECSQLclosecEND_EXEC注意注意:上面细节随宿主语言而变化，如上面细节随宿主语言而变化，如 Java中，定义中，定义

55、了了Javaiterators来遍历结果元组集来遍历结果元组集通过游标更新通过游标更新n通过声明更新游标，可以更新游标取得的元组：declarec cursorforselect* fromaccount where branch_name = Perryridge forupdaten下面更新游标 c 在当前位置的元组（每次fetch都执行下面代码）updateaccount set balance = balance + 100 wherecurrentofc4.5 动态动态 SQLn在许多情况下，应用要执行的具体的SQL 语句在书写应用的时候就已经知道了。不过，在某些情况下，SQL 语句

56、是在运行时或者由外部的数据提供的。在这种情况下，我们不能直接在C 代码嵌入SQL 语句，但是有个机制可以允许你调用放在一个字串变量里的任何SQL 语句。4.5 动态动态 SQLnSQL的动态SQL组件允许程序在运行时构造、提交SQL查询。与此相反，嵌入式SQL语句必须在编译时全部确定，并交由嵌入式SQL预处理器编译。n使用动态SQL，程序能够在运行时（也许基于用户输入）以字符串的形式生成SQL查询，立即执行该查询或使其为后续使用作好准备。准备动态SQL语句是对其进行编译，使用预备语句就是使用已编译过的版本。4.5 动态动态 SQLn允许程序在运行时构造和递交SQL查询n例子：C程序中使用动态S

57、QL char* sqlprog = “updateaccount set balance = balance * 1.05 whereaccount_number = ？”EXEC SQL prepare dynprogfrom:sqlprog;char account 10 = “A-101”;EXEC SQL executedynprogusing:account;n这个动态SQL程序包含一个“? ”, 它持有了一个在SQL程序执行时刻提供的值ODBC and JDBCnODBC和和JDBC都是都是API(application-programinterface)，被应用程序用来与数据库

58、服务器交，被应用程序用来与数据库服务器交互互n应用用它们来：应用用它们来：n与数据库服务器取得连接与数据库服务器取得连接n发送发送SQL命令到数据库服务器命令到数据库服务器n一个一个从查询结果中取得元组到程序变量中一个一个从查询结果中取得元组到程序变量中nODBC(OpenDatabaseConnectivity)： C,C+,C#,和和 VisualBasic中使用中使用nJDBC(JavaDatabaseConnectivity)： Java中中使用使用ODBCnODBC标准n是应用程序与数据库服务器通讯的标准n是API：n打开与数据库的连接 n发送查询和更新n返回结果nGUI、电子表格等

59、应用可以使用ODBCODBC n支持支持ODBC的数据库系统提供一个与客户端程序相连接的的数据库系统提供一个与客户端程序相连接的驱动库。当客户端发送驱动库。当客户端发送ODBC请求时，库中的代码和服请求时，库中的代码和服务器通讯，执行请求并取回结果务器通讯，执行请求并取回结果nODBC程序首先分配一个程序首先分配一个SQL环境环境,然后是一个数据库连然后是一个数据库连接句柄（接句柄（handle）n使用使用 SQLConnect()打开数据库连接打开数据库连接.SQLConnect的参的参数为数为:n连接句柄连接句柄n需要连接的数据库需要连接的数据库n用户标识用户标识n密码密码 n同时必须指定

60、参数类型同时必须指定参数类型:nSQL_NTS表示之前的参数是以表示之前的参数是以null结束的字符串结束的字符串ODBC 代码代码nint ODBCexample() RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, aura.bell-, SQL_NTS, avi, SQL_NTS, avipasswd, SQL_NTS); . Do actual wo

61、rk SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); ODBC Coden程序使用SQLExecDirect发送SQL命令到数据库n使用SQLFetch()取回结果元组nSQLBindCol() 绑定C 语言变量到查询结果的属性上n当一个元组被取回，其属性值自动存储在相应的C 变量中n好的编程要求检查每个函数调用的结果是否出错，这里为了简洁起见都省略了ODBC 代码代码 n程序主体： charbranchname80;floatbalance;intlenOut1,lenOut2;HSTMTstmt;SQLAllocStm

62、t(conn,&stmt);char*sqlquery=selectbranch_name,sum(balance)fromaccountgroupbybranch_name;error=SQLExecDirect(stmt,sqlquery,SQL_NTS);if(error=SQL_SUCCESS)SQLBindCol(stmt,1,SQL_C_CHAR,branchname,80,&lenOut1);SQLBindCol(stmt,2,SQL_C_FLOAT,&balance,0,&lenOut2);while(SQLFetch(stmt)=SQL_SUCCESS)printf(%s%g

63、n,branchname,balance);SQLFreeStmt(stmt,SQL_DROP);更多的更多的 ODBC 特性特性n预备语句（预备语句（PreparedStatement）nSQL 语句准备prepared: 在数据库中编译n可以使用占位符n如： insert into account values(?,?,?)n按照占位符实际值重复执行n元数据特性（元数据特性（Metadatafeatures）n找出数据库中所有关系n找出查询结果或数据库中关系的列的名字和类型ODBC 符合性等级符合性等级n符合性等级确定具体ODBC实现中与标准所定义的功能的子集的符合程度nCorenLeve

64、l 1 要求支持元数据查询nLevel 2 要求能发送和提取参数值数组，更详细的目录信息nSQL 的调用级接口Call Level Interface (CLI) 标准类似ODBC 接口, 但有细节上的差别nADO API和ADO.net API 是ODBC以外的可选方法，它们是为Visual Basic和C#语言设计的JDBCnJDBC 是支持SQL的与数据库通讯的Java APInJDBC 支持各式各样的特性，用来查询、更新数据和取回查询结果nJDBC 也支持元数据检索, 如查询关于数据库中关系、属性的名字和类型n与数据库通讯:n打开一个连接n建立一个“statement” 对象n使用st

65、atement对象执行查询，发送查询和取回结果n处理异常（Exception）的机制publicstaticvoidJDBCexample(Stringdbid,Stringuserid,Stringpasswd)tryClass.forName(oracle.jdbc.driver.OracleDriver);Connectionconn=DriverManager.getConnection(jdbc:oracle:thin:aura.bell-:2000:bankdb,userid,passwd);Statementstmt=conn.createStatement();DoActual

66、Work.stmt.close();conn.close();catch(SQLExceptionsqle)System.out.println(SQLException:+sqle);n更新数据库trystmt.executeUpdate(insertintoaccountvalues(A-9732,Perryridge,1200);catch(SQLExceptionsqle)System.out.println(Couldnotinserttuple.+sqle);n执行查询、取回和打印结果ResultSetrset=stmt.executeQuery(selectbranch_name,avg(balance)fromaccountgroupbybranch_name);while(rset.next()System.out.println(rset.getString(branch_name)+rset.getFloat