第四章,数据库——高级SQL.ppt

2020年2月2日4时50分 第四章 高级SQL 2020年2月2日4时50分 第四章 高级SQL SQL数据类型和模式 schema 完整性约束授权嵌入式SQL动态SQL函数和过程结构 递归查询 高级SQL特性 2020年2月2日4时50分 SQL中内建数据类型 date 日期 年 4位 月 日例子 date 2005 7 27 time 一天中的时间 小时 分钟 秒例子 time 09 00 30 time 09 00 30 75 timestamp 时间戳 日期加上时间例子 timestamp 2005 7 2709 00 30 75 interval 时间期间例子 interval 1 daydate time timestamp值相减得到期间值期间值能够和date time timestamp值相加 2020年2月2日4时50分 SQL中内建数据类型 Cont 能从date time timestamp中抽取个体字段的值例子 extract yearfromr starttime 能将符合正确格式的字符串类型转化为date time timestamp类型例子 castasdate例子 castastime 2020年2月2日4时50分 用户定义类型 createtype结构建立用户定义类型createtypeDollarsasnumeric 12 2 finalcreatedomain结构 SQL 92 结构建立用户定义域类型createdomainperson namechar 20 notnull类型和域相似 域可以有约束 如notnull 域不是强制类型 不能在兼容类型间强制转换 不同的兼容类型间可以进行比较 如customer namechar 20 和branch namechar 15 2020年2月2日4时50分 域约束 域约束 domainconstraints 是完整性约束最基本的形式 数据库插入和查询等将进行该约束测试 以确保比较有意义 新的域可以用现有数据类型来建立例子 createdomainDollarsnumeric 12 2 createdomainPoundsnumeric 12 2 我们不能将Dollars值赋予Pounds类型 二者也无法进行比较但我们可以进行如下形式的类型转换 castr AasPounds 应该乘以美元 英镑汇率 2020年2月2日4时50分 大对象 Large Object 类型 大对象 photos videos CAD文档 等等 以下面类型存储 blob binarylargeobject 对象是未翻译的 如何翻译留给数据库系统以外的应用 clob characterlargeobject 对象是大量字符数据集合当查询返回大对象时 实际返回一个指针而不是大对象本身 2020年2月2日4时50分 完整性约束 完整性约束通过确保被授权的对数据库的修改不会导致数据一致性的丢失 来防止数据库免予意外破坏 企业过往帐户至少有 10 000 00以上余额银行雇员薪水至少每小时 4 00客户必须有非空 non null 电话号码完整性约束可以是与数据库有关的任意谓词 但任意谓词的检测代价可能很高 2020年2月2日4时50分 单个关系上的约束 notnullprimarykeyuniquecheck P 这里P是谓词 2020年2月2日4时50分 NotNull约束 声明branch的branch name为notnullbranch namechar 15 notnull声明域Dollars为notnullcreatedomainDollarsnumeric 12 2 notnull 2020年2月2日4时50分 Unique约束 unique A1 A2 Am unique限定指出属性A1 A2 Am形成一个候选码候选码属性允许为null 这和主码不同 注意对空值的比较运算结果 2020年2月2日4时50分 check子句 check P 这里P是谓词 例子 声明branch name为branch的主码 并且确保assets的值为非负createtablebranch branch namechar 15 branch citychar 30 assetsinteger primarykey branch name check assets 0 2020年2月2日4时50分 check子句 Cont SQL 92中 check子句可用于域限制 使用check子句确保hourly wage域的值只允许大于某给定值createdomainhourly wagenumeric 5 2 constraintvalue testcheck value 4 00 使该域有约束 用于确保hourly wage大于4 00子句constraintvalue test可选 可用于系统指出一个更新违反了那个约束 2020年2月2日4时50分 参照完整性 ReferentialIntegrity 确保一个关系上给定属性集上的取值也在另一关系某一属性集上的取值中存在 例子 如果 Perryridge 是account关系中一个元组中支行的名字 则对Perryridge支行 在branch关系中存在一个相应元组主码 候选码 外码能够在SQL的createtable声明中使用 primarykey子句列表组成主码的属性uniquekey子句列表组成候选码的属性foreignkey子句列表组成外码的属性和被该外码参照的关系 按照默认 外码参照的是参照表中的主码属性 2020年2月2日4时50分 SQL中的实体 主码 完整性 例子 createtablecustomer customer namechar 20 customer streetchar 30 customer citychar 30 primarykey customer name createtablebranch branch namechar 15 branch citychar 30 assetsnumeric 12 2 primarykey branch name 2020年2月2日4时50分 SQL中的参照完整性 例子 Cont createtableaccount account numberchar 10 branch namechar 15 balanceinteger primarykey 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 2020年2月2日4时50分 SQL中的参照完整性 SQL中支持references子句 该子句可以显式指明被参照关系的一组属性 这一组属性必须定义为被参照关系的候选码 可以使用如下简写形式作为属性定义的一部分 并指明该属性为外码 branch namechar 15 referencesbranch 2020年2月2日4时50分 SQL中的参照完整性 Cont 当违反参照完整性约束时 通常操作是拒绝执行导致完整性被破坏的操作 即进行更新操作的事物被回滚 但 foreignkey子句中可指明 被参照关系上删除或更新违反了约束 则系统必须修改参照关系中的元组来恢复完整性约束 而不是拒绝该修改操作 例子 createtableaccount foreignkey branch name referencesbranchondeletecascadeonupdatecascade 所谓 级联 删除和更新 2020年2月2日4时50分 SQL中的参照完整性 Cont 当违反参照完整性约束时 Cont SQL还允许foreignkey子句指明cascade以外其它动作 如 setnull或setdefault代替cascade增加完整性约束altertabletable nameaddconstraint首先对该操作检测完整性完整性检测的时间问题立即执行和延迟执行 2020年2月2日4时50分 断言 Assertions 断言是一个谓词 表达一个我们希望数据库总能满足的条件 SQL中断言的形式 createassertioncheck建立断言时 系统要检测其有效性 如果有效 以后每次对可能违反该断言的数据库的修改将被检测这种检测可能带来巨大的工作量 所以要小心使用 断言forallX P X 的实现是通过下面方式 SQL不提供forallX P X 结构 实现 notexistsXsuchthatnotP X 2020年2月2日4时50分 断言 例子 每笔贷款中至少有一个借款人拥有余额在 1000 00以上的存款帐户createassertionbalance constraintcheck notexists select fromloanwherenotexists select fromborrower depositor accountwhereloan loan number borrower loan numberandborrower customer name depositor customer nameanddepositor account number account account numberandaccount balance 1000 2020年2月2日4时50分 断言 例子 Cont 每个支行的贷款总额必须小于帐户余额总数createassertionsum constraintcheck notexists select frombranchwhere selectsum amount fromloanwhereloan branch name branch branch name selectsum amount fromaccountwhereloan branch name branch branch name 2020年2月2日4时50分 授权 Authorization 我们可能会为一个用户在数据库某些部分指定几种形式的授权 Read 允许读取 但不能进行数据修改Insert 允许插入新数据 但不能修改现有数据Update 允许修改 但不能删除数据Delete 允许删除数据数据库模式修改授权的形式 Index 允许建立和删除索引Resources 允许建立新的关系Alteration 允许增加或删除关系中的属性Drop 允许删除关系 2020年2月2日4时50分 SQL中的授权 grant声明用来授予权限grantonto是 一个用户的IDpublic 允许授权到所有有效用户一个角色对视图的授权并不意味对下层逻辑关系的任何授权SQL允许通过 授予权限 权限来指定权限被授予者的权限授予权 授予其它用户自己已被授予的权限 权限的授予者必须已经持有特定权限项 或是DBA 2020年2月2日4时50分 SQL中的权限 privileges select 允许读取关系 或者使用视图查询例子 grantselectonbranchtoU1 U2 U3insert 插入元组update 使用SQL更新声明来更新delete 删除元组allprivileges 所有允许的权限 关系的建立者自动被授予该关系上所有权限 其它 2020年2月2日4时50分 SQL中授权的收回 revoke revoke声明用来收回授权revokeonto例子 revokeselectonbranchfromU1 U2 U3可以是all 用来收回被收回者持有的所有权限如果包括public 除了那些显式授权的用户 其它所有用户将失去权限 如果某权限被两个不同授予者两次授予同一用户 收回操作后该用户可能保留了这些权限所有依赖于被收回权限的权限也一起被收回了 2020年2月2日4时50分 嵌入式SQL SQL标准定义了在C Java 和Cobol等中嵌入SQL被嵌入SQL查询的语言称为宿主语言 宿主语言中使用的SQL结构称为嵌入式SQLEXECSQL声明用来识别嵌入式SQL请求 EXECSQLEND EXEC注意 确切语法依赖于宿主语言 如Java SQL 2020年2月2日4时50分 查询例子 确定SQL查询 并声明代表它的游标 cursor EXECSQLdeclareccursorforselectdepositor customer name customer cityfromdepositor customer accountwheredepositor customer name customer customer nameanddepositoraccount number account account numberandaccount balance amountEND EXEC 假设有宿主变量amount 我们需要找出所有在银行有余额大于amount的帐户的客户的名字和居住城市 2020年2月2日4时50分 嵌入式SQL Cont open语句用来使查询被求值EXECSQLopencEND EXECfetch语句用来把一个元组的值放到宿主变量中EXECSQLfetchcinto cn ccEND EXEC重复使用fetch得到查询结果中连续的元组SQL通讯区 SQLCA 中的有一个叫做SQLSTATE的变量 它被置为 02000 时 表示再无可用数据了 即后面不再有待处理元组了close语句用来使数据库删除保存查询结果的零时关系EXECSQLclosecEND EXEC注意 上面细节随宿主语言而变化 如Java中 定义了Javaiterators来遍历结果元组集 2020年2月2日4时50分 通过游标更新 通过声明更新游标 可以更新游标取得的元组 declareccursorforselect fromaccountwherebranch name Perryridge forupdate下面更新游标c在当前位置的元组 每次fetch都执行下面代码 updateaccountsetbalance balance 100wherecurrentofc 2020年2月2日4时50分 动态SQL 允许程序在运行时构造和递交SQL查询例子 C程序中使用动态SQLchar sqlprog updateaccountsetbalance balance 1 05whereaccount number EXECSQLpreparedynprogfrom sqlprog characcount 10 A 101 EXECSQLexecutedynprogusing account 这个动态SQL程序包含一个 它持有了一个在SQL程序执行时刻提供的值 2020年2月2日4时50分 ODBCandJDBC ODBC和JDBC都是API application programinterface 被应用程序用来与数据库服务器交互应用用它们来 与数据库服务器取得连接发送SQL命令到数据库服务器一个一个从查询结果中取得元组到程序变量中ODBC OpenDatabaseConnectivity C C C 和VisualBasic中使用JDBC JavaDatabaseConnectivity Java中使用 2020年2月2日4时50分 ODBC ODBC标准是应用程序与数据库服务器通讯的标准是API 打开与数据库的连接发送查询和更新返回结果GUI 电子表格等应用可以使用ODBC 2020年2月2日4时50分 ODBC Cont 支持ODBC的数据库系统提供一个与客户端程序相连接的驱动库当客户端发送ODBC请求时 库中的代码和服务器通讯 执行请求并取回结果ODBC程序首先分配一个SQL环境 然后是一个数据库连接句柄 handle 使用SQLConnect 打开数据库连接 SQLConnect的参数为 连接句柄需要连接的数据库用户标识密码同时必须指定参数类型 SQL NTS表示之前的参数是以null结束的字符串 2020年2月2日4时50分 ODBC代码 intODBCexample RETCODEerror HENVenv environment HDBCconn databaseconnection SQLAllocEnv 2020年2月2日4时50分 ODBCCode Cont 程序使用SQLExecDirect发送SQL命令到数据库使用SQLFetch 取回结果元组SQLBindCol 绑定C语言变量到查询结果的属性上当一个元组被取回 其属性值自动存储在相应的C变量中好的编程要求检查每个函数调用的结果是否出错 这里为了简洁起见都省略了 2020年2月2日4时50分 ODBC代码 Cont 程序主体 charbranchname 80 floatbalance intlenOut1 lenOut2 HSTMTstmt SQLAllocStmt conn 2020年2月2日4时50分 更多的ODBC特性 预备语句 PreparedStatement SQL语句准备prepared 在数据库中编译可以使用占位符如 insertintoaccountvalues 按照占位符实际值重复执行元数据特性 Metadatafeatures 找出数据库中所有关系找出查询结果或数据库中关系的列的名字和类型 2020年2月2日4时50分 ODBC符合性等级 符合性等级确定具体ODBC实现中与标准所定义的功能的子集的符合程度CoreLevel1要求支持元数据查询Level2要求能发送和提取参数值数组 更详细的目录信息SQL的调用级接口CallLevelInterface CLI 标准类似ODBC接口 但有细节上的差别ADOAPI和ADO netAPI是ODBC以外的可选方法 它们是为VisualBasic和C 语言设计的 2020年2月2日4时50分 JDBC JDBC是支持SQL的与数据库通讯的JavaAPIJDBC支持各式各样的特性 用来查询 更新数据和取回查询结果JDBC也支持元数据检索 如查询关于数据库中关系 属性的名字和类型与数据库通讯 打开一个连接建立一个 statement 对象使用statement对象执行查询 发送查询和取回结果处理异常 Exception 的机制 2020年2月2日4时50分 JDBC代码 publicstaticvoidJDBCexample Stringdbid Stringuserid Stringpasswd try Class forName oracle jdbc driver OracleDriver Connectionconn DriverManager getConnection jdbc oracle thin aura bell 2000 bankdb userid passwd Statementstmt conn createStatement DoActualWork stmt close conn close catch SQLExceptionsqle System out println SQLException sqle 2020年2月2日4时50分 JDBC代码 Cont 更新数据库try stmt executeUpdate insertintoaccountvalues A 9732 Perryridge 1200 catch SQLExceptionsqle System out println Couldnotinserttuple sqle 执行查询 取回和打印结果ResultSetrset stmt executeQuery selectbranch name avg balance fromaccountgroupbybranch name while rset next System out println rset getString branch name rset getFloat 2020年2月2日4时50分 过程扩展和存储过程 SQL提供模块 module 语言允许使用if then else repeat for while case等语句定义SQL过程存储过程能在数据库中储存过程然后通过call语句执行它们从而允许外部应用在数据库上进行操作 但不知道内部细节 2020年2月2日4时50分 函数 Function 和过程 Procedure SQL 1999支持函数和过程函数 过程可以使用SQL或外部编程语言来定义函数对特殊数据类型如图像和几何特别有用一些数据库系统支持table valued函数 它能返回一个关系作为结果SQL 1999也支持规则结构 如循环 if then else 赋值很多数据库对SQL有自己专有的 不同于SQL 1999的过程扩展 2020年2月2日4时50分 SQL函数 例子 给定客户名字 返回该客户拥有的帐户的数目createfunctionaccount count customer namevarchar 20 returnsintegerbegindeclarea countinteger selectcount intoa countfromdepositorwheredepositor customer name customer namereturna count end找到每个有不止一个帐户的客户的名字和街道selectcustomer name customer street customer cityfromcustomerwhereaccount count customer name 1 2020年2月2日4时50分 Table函数 例子 SQL 2003增加了返回关系的函数例子 返回所有给定客户的帐户createfunctionaccounts of customer namechar 20 returnstable account numberchar 10 branch namechar 15 balancenumeric 12 2 returntable selectaccount number branch name balancefromaccountAwhereexists select fromdepositorDwhereD customer name accounts of customer nameandD account number A account number 使用select fromtable accounts of Smith 2020年2月2日4时50分 SQL过程 account count函数可以使用下面过程代替 createprocedureaccount count proc innamevarchar 20 outa countinteger beginselectcount intoa countfromdepositorwheredepositor customer name account count proc nameend使用call语句 可以从SQL过程或嵌入式 动态SQL调用过程 如 declarea countinteger callaccount count proc Smith a count SQL 1999允许同名函数 过程 称为名称重载overloading 前题是参数数量或类型不同 2020年2月2日4时50分 过程结构 复合语句 begin end 可以包含多个SQL语句可以在复合语句中声明本地变量While和repeat语句 declarenintegerdefault0 whilen 10dosetn n 1endwhilerepeatsetn n 1untiln 0endrepeat 2020年2月2日4时50分 过程结构 Cont For循环允许在整个查询结果上进行反复例子 找到Perryridge支行余额总和declarenintegerdefault0 forrasselectbalancefromaccountwherebranch name Perryridge dosetn n r balanceendfor 2020年2月2日4时50分 过程结构 Cont 条件语句 if then else 例子 找出三个区间帐户余额总和 balance 1000and 5000 ifr balance 1000thensetl l r balanceelseifr balance 5000thensetm m r balanceelseseth h r balanceendif