高级SQL.ppt

database system concepts,第四章 高级sql chapter 4 advanced sql,2,数据库系统概念-高级sql,提纲,类型约束 完整性约束 嵌入式sql 动态sql,3,数据库系统概念-高级sql,数据类型与模式,数据类型 date：日期（年、月、日） time：时间（小时、分、秒）,4,数据库系统概念-高级sql,类型定义,类型定义 格式 create type 类型名 数据类型 示例 create type person-name char（20） 类似c语言中： typedef address_list char name10; char telephone20; char location20 char email20; address_list tom;,5,数据库系统概念-高级sql,类型定义,create table a （a1 char（10）， a2 person-name ）,6,数据库系统概念-高级sql,域定义,域定义 格式 create domain 域名 数据类型 示例 create domain person-name char（20） 类似c语言中： typedef address_list char name10; char telephone20; char location20 char email20; address_list tom;,7,数据库系统概念-高级sql,大数据对象,clob（字符数据的大对象数据类型）/blob（二进制数据的大对象数据类型）,8,数据库系统概念-高级sql,完整性控制,完整性 数据的正确性和相容性 完整性约束保证授权用户对数据库进行修改时不会破坏数据的一致性 完整性检查 dbms必须提供一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据是正确的，避免非法的不合语义的错误数据的输入和输出，即所谓的“垃圾进垃圾出”(garbage in garbage out)所造成的无效操作和错误结果,9,数据库系统概念-高级sql,完整性控制,完整性子系统 数据库的非法更新 数据本身是错误的 数据原来是正确的，由于操作或程序错误，导致输入数据错误 由于系统故障，导致数据错误 事务的并发执行产生不正确结果 人为故意破坏 完整性子系统功能 监督事务执行，检查是否违反完整性规则 如有违反，采取相应措施（拒绝、报告、改正）,10,数据库系统概念-高级sql,完整性控制,完整性规则 完整性规则集 由dba或程序员事先提供的有关数据约束的一组规则 规则的构成 如何表达完整性约束条件？ 如何检查完整性约束条件？ 违反条件时候的如何处理？,11,数据库系统概念-高级sql,完整性控制,在属性值上的约束 非空约束 要求某属性取值不能为空值 sname char(8) not null 基于属性的检查子句 check(age15) 域约束子句 用create domain定义域时，可以出现 check create domain age smallint check(value = 15) and value = 25),12,数据库系统概念-高级sql,完整性控制,主码约束 主码值不允许空，也不允许出现重复 意义：关系对应到现实世界中的实体集，元组对应到实体，实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不容许的 主码定义形式 主码子句：primary key(sno) 主码短语：sno char(4) primary key,13,数据库系统概念-高级sql,完整性控制,全表扫描缺点：十分耗时。为避免对基本表进行全表扫描，dbms一般都在主码上自动建立一个索引,用全表扫描方法检查主码唯一性,14,数据库系统概念-高级sql,完整性控制,如果新插入记录的主码值是25 通过主码索引，从b+树的根结点开始查找 读取3个结点： 根结点（51） 中间结点（12 30） 叶结点（15 20 25） 该主码值已经存在，不能插入这条记录,通过b+树索引查找基本表中是否已经存在新的主码值，可以提高效率,15,数据库系统概念-高级sql,完整性控制,外码约束（参照完整性） 关系r中的一个属性组，它不是r的码，但它与另一个关系s的码相对应，则称这个属性组为r的外部码，如s关系中的dno属性 如果关系r2的外部码fk与关系r1的主码pk相对应，则r2中的每一个元组的fk值或者等于r1 中某个元组的pk 值，或者为空值 意义：如果关系r2的某个元组t2参照了关系r1的某个元组t1，则t1必须存在 例如关系s在dno上的取值有两种可能 空值，表示该学生尚未分到任何系中；若非空值，则必须是d关系中某个元组的dno值，表示该学生不可能分到一个不存在的系中,16,数据库系统概念-高级sql,完整性控制,定义形式 在sc表中，定义 foreign key (sno) references s(sno) sno在sc中是外码，在s中是主码，sno在sc中取值或为空或为s中某个sno值 作为主码的关系称为基本（参照）关系，作为外码的关系称为依赖关系,17,数据库系统概念-高级sql,完整性控制,删除基本关系元组 restrict方式 只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时，才可以删除该元组，否则系统拒绝此删除操作 cascade方式 将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除 set null方式 删除基本关系中元组时，将依赖关系中与基本关系中被删主码值相对应的外码值置为空值 如foreign key (sno) references s(sno) on delete restrict |cascade | set null ,18,数据库系统概念-高级sql,完整性控制,修改基本关系主码 restrict方式 只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时，才可以修改该元组主码，否则系统拒绝此次修改 cascade方式 将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值 set null方式： 修改基本关系中元组主码时，将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值 如foreign key (sno) references s(sno) on update cascade | set null ,19,数据库系统概念-高级sql,完整性控制,全局约束 全局约束涉及多个属性间的或多个关系间的联系 create table sc (sno char(4), cno char(4), score smallint, primary key (sno, cno), check(sno in (select sno from s), check(cno in (select cno from c),如果s中删除元组，不会触发check子句，只有对sc表的更新和插入才会触发,20,数据库系统概念-高级sql,完整性控制,对约束的命名、撤消和添加 命名 constraint 约束名 示例 sno char(4) constraint s_pk priamry key age samllint constraint age_val check(age = 15 and age = 25),21,数据库系统概念-高级sql,完整性控制,关系上约束的撤消与添加 撤消用alter drop 添加用alter add 示例 alter table s drop constraint s_pk alter table sc add constraint sc_check check(sno in select sno from s),22,数据库系统概念-高级sql,完整性控制,域约束的创建、撤消与添加 create domain age_domain smallint constraint dc_age check(value = 15) alter domain age_domain add constraint dc_age check(value = 15) alter domain age_domain drop constrain dc_age,23,数据库系统概念-高级sql,断言,断言 定义 create assertion check 断言是谓词，表达数据库总应该满足的条件 一旦定义了断言，dbms验证其有效性，并且对每个可能违反该断言的更新操作都进行检查 这种检查会带来巨大的系统负载，因此应该谨慎使用断言 对断言“所有x, p(x)”，是通过检查“not exists x， p(x)”来实现的,24,数据库系统概念-高级sql,断言,示例：不允许男同学选修001老师课程,create assertion asse1 check (not exists (select * from sc where cno in (select cno from tc where tno = 001） and sno in (select sno from s where sex = m),25,数据库系统概念-高级sql,断言,示例：每门课最多50名男同学选修 create assertion asse2 check (50 = all (select count(sc.sno) from s, sc where s.sno= sc.sno and sex= m group by cno),26,数据库系统概念-高级sql,断言,撤消：drop assertion 断言名 drop assertion asse1,27,数据库系统概念-高级sql,授权,权限的转授和回收 允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来 权限图 结点是用户，根结点是dba，有向边uiuj，表示用户ui把某权限授给用户uj 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径,28,数据库系统概念-高级sql,授权,29,数据库系统概念-高级sql,授权,授权命令 grant 表级权限 on 表名 | 视图名 to 用户 ，用户 | public with grant option 表级权限包括：select, update, insert, delete, index, alter, drop, resource以及它们的总和all，其中对select , update可指定列名 with grant option表示获得权限的用户可以把权限再授予其它用户,30,数据库系统概念-高级sql,授权,示例 grant select , insert on s to liming with grant option grant all on s to public grant update(sno), select on table s to u4 grant all priviliges to public,31,数据库系统概念-高级sql,授权,把对表sc的insert权限授予u5用户，并允许他再将此权限授予其他用户 grant insert on table sc to u5 with grant option,32,数据库系统概念-高级sql,授权,角色：为了指明一类人应有的授权，提出了角色概念。在数据库中建立角色集，并授予每个角色一定的权限，然后将角色分配给用户。 create role teller,33,数据库系统概念-高级sql,授权,回收权限 revoke 表级权限 on 表名 | 视图名 from 用户 ，用户 | public 收回权限时，若该用户已将权限授予其它用户，则也一并收回。授权路径的起点一定是dba 示例 revoke insert on s from liming,34,数据库系统概念-高级sql,授权,支持多库的数据库系统中授权对象可以是数据库 grant 数据库级权限 to 用户 ，用户 | public 数据库级权限包括： connect：允许用户在database语句中指定数据库 resource：connect权限+建表、删除表及索引权利 dba：resource权限 + 授予或撤消其他用户的connect、resource、dba权限 不允许dba撤消自己的dba权限,思考： 如何使dba撤消自己的dba权限？,35,数据库系统概念-高级sql,触发器,触发器 触发器是一条语句，当对数据库做修改时，它自动被系统执行 触发器的定义 指明什么条件下触发器被执行 指明触发器执行的动作是什么 触发器的事件-条件-动作模型(event-condition-action model) 触发器的作用 示警 满足特定条件时自动执行某项任务 触发器事件 insert、 delete、update,36,数据库系统概念-高级sql,触发器,37,数据库系统概念-高级sql,触发器,emp(eno, ename, sal, job) 职工工资增幅不得超过10% create trigger raise_limit after update of sal on emp referencing new row as nrow old row as orow for each row when (nrow.sal 1.1 * orow.sal) begin signal sqlstate 7500 (“salary increase 10%) end,38,数据库系统概念-高级sql,触发器,当帐户透支时，将帐户余额设为0，并建一笔贷款，其金额为透支额 create trigger overdraft-trigger after update on account referencing new row as nrow for each row when nrow.balance 0 begin 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 = 0 where account.account-number = nrow.account-number end,39,数据库系统概念-高级sql,oracle中的实例,create or replace trigger si.trig_medi_account_update before update of dwjze,fsrq,grjze,jfjs,sbjgbh,xzbz on si.medi_account referencing old as old new as new for each row begin if nvl(:new.fsrq,sysdate)nvl(:old.fsrq,sysdate + 1) then raise_application_error(-20100,试图修改医疗帐户信息！(try update fsrq ); end if; if nvl(:new.xzbz,*)nvl(:old.xzbz,#) then raise_application_error(-20100,试图修改医疗帐户信息！(try update xzbz ); end if; if nvl(:new.jfjs,0)nvl(:old.jfjs,1) then raise_application_error(-20100,试图修改医疗帐户信息！(try update jfjs ); end if; end;,40,数据库系统概念-高级sql,嵌入式sql,为什么使用嵌入式sql？ 嵌入式sql执行过程 需要解决的几个问题,41,数据库系统概念-高级sql,为什么使用嵌入式sql？,有些操作对于交互式sql是不可能的任务 sql的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用sql无法完成。一方面，sql在逐渐增强自己的表达能力（参见习题3.5），另一方面，太多的扩展会导致优化能力及执行效率的降低 非声明性动作 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现,42,数据库系统概念-高级sql,嵌入式sql执行过程,43,数据库系统概念-高级sql,需要解决的几个问题,区分sql语句与宿主语言语句 嵌入的sql语句以exec sql开始，以分号(;) 或end_exec结束 exec sql delete from s where sno = s1；,44,数据库系统概念-高级sql,需要解决的几个问题,连接数据库 在执行sql语句之前，必须首先连接数据库 exec sql connect to server user user-name end-exec,45,数据库系统概念-高级sql,需要解决的几个问题,嵌入sql语句与宿主语言之间的数据传递 宿主变量 宿主语言变量，既可以用在宿主语句中，也可用在sql语句中，用来在两者之间传递数据 宿主变量的声明 声明为通常的宿主变量，并将其放在下列标识语句之间 exec sql begin declare section exec sql end declare section,46,数据库系统概念-高级sql,需要解决的几个问题,exec sql begin declare section int prof_no; char prof_name30; int salary; exec sql end declare section,47,数据库系统概念-高级sql,需要解决的几个问题,注：宿主变量出现于sql语句中时，前面加（:）以区别列名 宿主变量可出现的地方：sql的数据操纵语句中可出现常数的任何地方，select，fetch等语句的into子句中 示例： exec sql select sname , age into :sname , :age from s where sno = :sno;,48,数据库系统概念-高级sql,需要解决的几个问题,指示变量 宿主变量，用来指示返回给宿主变量的值是否为null值，以及返回给宿主变量的字符串是否发生了截断 指示变量应用场合 如果一个宿主变量所对应的数据库字段允许空值，或字符串类型的宿主变量的长度可能小于所对应的数据库字段的长度，则需要一个指示变量来指明数据库访问的返回状态 指示变量的返回值 = 0：取到主变量的值不空，没有发生截断 = 1：取到主变量的值为空值 0：取到主变量的值发生了截断，指示变量的值是截断前的字符串的实际长度,49,数据库系统概念-高级sql,需要解决的几个问题,指示变量的用法 声明与宿主变量的声明方式一样，在数据操纵语句中，在宿主变量和指示变量之间加( : )或关键字indicator exec sql begin declare section char sno, prof_name30; int age; short name_id; short sal_id; exec sql end declare section exec sql select sanem , age into :sname : name_id , :age: age_id from s where sno = :sno ;,50,数据库系统概念-高级sql,需要解决的几个问题,sql与主语言之间操作方式的协调 执行方式的差别 sql：一次一集合 宿主语言：一次一记录 游标 在查询结果的记录集合中移动的指针 若一个sql语句返回单个元组，则不用游标 若一个sql语句返回多个元组，则使用游标,51,数据库系统概念-高级sql,需要解决的几个问题,不需要游标的数据操作 结果是一个元组的select语句 exec sql select sname , age into :sname : name_id, :age : age_id from s where sno = :sno ;,52,数据库系统概念-高级sql,需要解决的几个问题,insert语句 exec sql insert into s values (:sno, :sname , :age , :dno , : sex) ; delete语句 exec sql delete from s where sno :sno ; update语句 exec sql update s set age = :age where sno = : sno ;,53,数据库系统概念-高级sql,需要解决的几个问题,需要游标的数据操作 当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组 活动集：select语句返回的元组的集合 当前行：活动集中当前处理的那一行。游标即是指向当前行的指针,54,数据库系统概念-高级sql,需要解决的几个问题,游标分类 滚动游标 游标的位置可以来回移动，可在活动集中取任意元组 非滚动游标 只能在活动集中顺序地取下一个元组 更新游标 数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁,55,数据库系统概念-高级sql,需要解决的几个问题,定义与使用游标的语句 declare 定义一个游标，使之对应一个select语句 declare 游标名 scroll cursor for select语句for update of列表名 for update任选项，表示该游标可用于对当前行的修改与删除,56,数据库系统概念-高级sql,需要解决的几个问题,open 打开一个游标，执行游标对应的查询，结果集合为该游标的活动集 open 游标名 fetch(java除外) 在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中 fetch next | prior | first | last | current | relative n | absolute m 游标名 into 宿主变量表,57,数据库系统概念-高级sql,需要解决的几个问题,close 关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句 close 游标名 free 删除游标，以后不能再对该游标执行open语句 free 游标名,58,数据库系统概念-高级sql,需要解决的几个问题,sql语句执行信息反馈 良好的应用程序必须提供对错误的处理,应用程序需要知道sql语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息 sql通讯域sqlca是一结构,每一嵌入sql语句的执行情况在其执行完成后写入sqlca结构中的各变量中, 根据sqlca中的内容可以获得每一嵌入sql语句执行后的信息,应用程序就可以做相应的处理 为了说明 (userca),必须在应用程序中包括: exec sql include sqlca;,59,数据库系统概念-高级sql,动态sql,动态sql允许应用程序在运行时构造、提交sql查询 实例参见课本p90,60,数据库系统概念-高级sql,数据库访问标准化接口：odbc,事实标准 占统治地位的产品，没有通过任何的公认程序而被作为标准广泛接受 被动标准 把一些已经被厂商实现的、甚至已经变成事实标准的特性标准化 预见性标准 引导市场的正式标准，先定义一些特性，然后厂商实现这些特性,61,数据库系统概念-高级sql,数据库访问标准化接口：odbc,odbc（开放数据库互联标准） 适用于客户-服务器体系结构，定义客户程序用以连接到数据库系统和发出sql命令的api 客户可以用同一odbc api来连接到任何支持odbc的数据库系统；odbc允许用户同时连接到多个数据源并在这些数据源之间进行切换 每个数据库系统必须提供一个驱动程序，受客户端的odbc驱动程序管理器控制，负责与服务器连接和通讯以及进行所有必要的数据和查询格式转换 odbc api定义一个cli(调用层接口)、一个sql语法定义以及关于允许的cli调用序列的规则,62,数据库系统概念-高级sql,数据库访问标准化接口：odbc,oracle服务器,db2服务器,其它数据库服务器,63,数据库系统概念-高级sql,数据库访问标准化接口：odbc,odbc实例参见课本p91,64,数据库系统概念-高级sql,jdbc,jdbc基础 jdbc驱动程序 jdbc编程,65,数据库系统概念-高级sql,jdbc-基础,jdbc(java database connectivity)是一个独立于特定数据库管理系统的、通用的sql数据库存取和操作的公共接口（一组api），定义了用来访问数据库的标准java类库，使用这个类库可以以一种标准的方法、方便地访问数据库资源（在java.sql类包中）。 jdbc为访问不同的数据库提供了一种统一的途径，象odbc(open database connectivity)一样，jdbc对开发者屏蔽了一些细节问题。 jdbc的目标是使应用程序开发人员使用jdbc可以连接任何提供了jdbc驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。,66,数据库系统概念-高级sql,jdbc-基础,与odbc相类似，jdbc接口（api）也包括两个层次： 面向应用的api：java api，抽象接口，供应用程序开发人员使用（连接数据库，执行sql语句，获得结果）。 面向数据库的api：java driver api，供开发商开发数据库驱动程序用。,与odbc相比，jdbc没有了定制的“数据源”的概念，而是直接在应用程序中加载驱动程序并连接特定的数据库。,67,数据库系统概念-高级sql,jdbc主要概念,driver manager（java.sql.drivermanager） 装载驱动程序，管理应用程序与驱动程序之间的连接。 driver（由驱动程序开发商提供） 将应用程序的api请求转换为特定的数据库请求。 connection（java.sql.connection） 将应用程序连接到特定的数据库 statement（java.sql.statement） 在一个给定的连接中，用于执行一个静态的数据库sql语句。 resultset（java.sql.resultset） sql语句中心完后，返回的数据结果集（包括行、列）。 metadata（java.sql.databasemetadata; java.sql. resultsetmetadata） 关于查询结果集、数据库和驱动程序的元数据信息。,68,数据库系统概念-高级sql,jdbc基本工作步骤,import the necessary classes load the jdbc driver identify the database source allocate a “connection” object (create) allocate a “statement” object (create) execute a query using the “statement” object retrieve data from the returned “resultset” object close the “resultset” object close the “statement” object close the “connection” object,69,数据库系统概念-高级sql,jdbc基本工作步骤,70,数据库系统概念-高级sql,jdbc基本工作步骤,1. load the jdbc driver class: class.forname(“drivername”); 2. open a database connection: drivermanager.getconnection (“jdbc:xxx:datasource”); 3. issue sql statements: stmt = con.createstatement(); stmt.executequery (“select * from mytable”); 4. process result set: while (rs.next() name = rs.getstring(“name”); amount = rs.getint(“amt”); ,71,数据库系统概念-高级sql,jdbc-与powerbuilder对比关系,72,数据库系统概念-高级sql,jdbc-驱动程序,jdbc支持四种类型的驱动程序： jdbc-odbc bridge, plus odbc driver (type 1) simplest jdbc methods - translate jdbc methods to odbc methods - odbc to native methods - native methods api native-api, partly java driver (type 2) jdbc methods - map jdbc methods to native methods (calls to vendor library) - native methods api (vendor library) jdbc-net, pure java driver (type 3) jdbc methods - translate to native api methods through tcp/ip network - native api methods native-protocol, pure java driver (type 4) java methods - native methods in java,73,数据库系统概念-高级sql,jdbc-驱动程序,jdbc-odbc bridge, plus odbc driver (type 1) 由 sun的java2 jdk提供(sun.jdbc.odbc.jdbcodbcdriver) 通过odbc驱动程序来获得对数据库的jdbc访问 必须先安装odbc驱动程序和配置odbc数据源。 仅当特定的数据库系统没有相应的jdbc驱动程序时使用。,74,数据库系统概念-高级sql,jdbc-驱动程序,native-api, partly java driver (type 2) native-api driver 将jdbc命令转换为特定数据库系统的本地库方法。 与type1相类似，必须先安装特定数据库的库方法（二进制代码，非java）。,75,数据库系统概念-高级sql,jdbc-驱动程序,jdbc-net, pure java driver (type 3) 将jdbc命令转换为与数据库系统无关的网络协议，并发送给一个中间件服务器。 中间件服务器再将数据库系统无关的网络协议转换为特定数据库系统的协议，并发送给数据库系统。 从数据库系统获得的结果先发送给中间件服务器，并进而返回给应用程序。,76,数据库系统概念-高级sql,jdbc-驱动程序,native-protocol, pure java driver (type 4) 纯java的驱动程序，直接与特定的数据库系统通信。 直接将jdbc命令转换为数据库系统的本地协议。 优点：没有中间的转换或者是中间件。 通常用于提高数据库访问的性能。,77,数据库系统概念-高级sql,jdbc-编程,任何一个jdbc应用程序，都需要以下四个步骤： 加载jdbc驱动程序 建立与数据库的连接 进行数据库操作 关闭相关连接,78,数据库系统概念-高级sql,编程：加载jdbc驱动程序,在应用程序中，有三种方法可以加载驱动程序： 利用system类的静态方法setproperty() system.setproperty(“jdbc.drivers”, “sun.jdbc.odbc.jdbcodbcdriver”); 利用class类的静态方法forname() class.forname(“sun.jdbc.odbc.jdbcodbcdriver”); class.forname(“oracle.jdbc.driver.oracledriver”); 直接创建一个驱动程序对象 new sun.jdbc.odbc.jdbcodbcdriver();,79,数据库系统概念-高级sql,编程：建立与数据库的连接,利用drivermanager类的静态方法getconnection()来获得与特定数据库的连接实例（connection实例）。 connection conn = driverm