数据库基础教程.ppt

第四章SQL 提纲 SQL概述SQL数据定义功能SQL数据查询功能SQL数据修改功能SQL数据控制功能嵌入式SQL SQL概述 历史SQL StruceuredQueryLanguang1974年 由Boyce和Chamber提出1975 1979年 在SystemR上实现 由IBM的SanJose研究室研制 称为Sequel SQL概述 用户1 用户2 用户4 视图V1 视图V1 用户3 基本表B1 基本表B2 基本表B4 基本表B3 存储文件S1 存储文件S2 存储文件S4 存储文件S3 SQL数据库体系结构 SQL用户 VIEW BaseTable Storedfile SQL概述 标准化有关组织ANSI AmericanNaturalStandardInstitute ISO InternationalOrganizationforStandardization 有关标准SQL 86 数据库语言SQL SQL 89 具有完整性增强的数据库语言SQL 增加了对完整性约束的支持SQL 92 数据库语言SQL 是SQL 89的超集 增加了许多新特性 如新的数据类型 更丰富的数据操作 更强的完整性 安全性支持等 SQL 3正在讨论中的新的标准 将增加对面向对象模型的支持 SQL概述 特点一体化集DDL DML DCL于一体单一的结构 关系 带来了数据操作符的统一面向集合的操作方式一次一集合高度非过程化用户只需提出 做什么 无须告诉 怎么做 不必了解存取路径两种使用方式 统一的语法结构SQL既是自含式语言 用户使用 又是嵌入式语言 程序员使用 语言简洁 易学易用 SQL概述 示例关系 DEPT D DNAME DEAN S S SNAME SEX AGE D COURSE C CN PC CREDIT SC S C SCORE PROF P PNAME AGE D SAL PC P C SQL数据定义功能 域定义基本表的定义索引的定义数据库的建立与撤消SQL数据定义特点 域定义 域类型 SQL 92 char n 固定长度的字符串varchar n 可变长字符串int 整数smallint 小整数类型numeric p d 定点数 小数点左边p位 右边q位real 浮点数doubleprecision 双精度浮点数date 日期 年 月 日 time 时间 小时 分 秒 interval 两个date或time类型数据之间的差 域定义 域定义格式createdomain域名数据类型示例createdomainperson namechar 20 类似C语言中 typedefADDRESS LIST charname 10 chartelephone 20 charlocation 20 charemail 20 ADDRESS LISTtom 基本表的定义 基本表的定义 CREATE 格式createtable表名 列名数据类型 default缺省值 notnull 列名数据类型 default缺省值 notnull primarykey 列名 列名 foreignkey 列名 列名 references表名 列名 列名 check 条件 基本表的定义 示例CREATETABLES S CHAR 4 SNAMECHAR 8 NOTNULL AGESMALLINT SEXCHAR 1 PRIMARYKEY S CHECK SEX 0ORSEX 1 基本表的定义 CREATETABLEC C CHAR 4 CNAMECHAR 10 NOTNULL TEACHERCHAR 8 PRIMARYKEY C 基本表的定义 CREATETABLESC S CHAR 4 C CHAR 4 GRADESAMLLINT PRIMARYKEY S C FOREIGNKEY S REFERENCESS S FOREIGNKEY C REFERENCESC S CHECK GRADEISNULL ORGRADEBETWEEN0AND100 基本表的定义 示例createdomainperson namechar 20 createtablePROF PNOchar 10 person namePNAMEnotnull SALint AGEint DNOchar 10 primarykey PNO foreignkey DNO referencesDEPT DNO check SAL 0 基本表的定义 修改基本表定义 ALTER 格式 altertable表名 add子句 增加新列 drop子句 删除列 modify子句 修改列定义示例altertablePROFaddLOCATIONchar 30 基本表的定义 撤消基本表定义 drop 格式droptable表名示例droptableDEPTDANGER撤消基本表后 基本表的定义 表中数据 索引 以及由此表导出的视图的定义都被删除 索引的定义 索引的定义格式create unique distinct cluster index索引名on表名 列名 asc desc 列名asc desc unique distinct 唯一性索引 不允许表中不同的行在索引列上取相同值 若已有相同值存在 则系统给出相关信息 不建此索引 系统并拒绝违背唯一性的插入 更新cluster 聚集索引 表中元组按索引项的值排序并物理地聚集在一起 一个基本表上只能建一个聚集索引asc desc 索引表中索引值的排序次序 缺省为asc 索引的定义 示例 createclusterindexs indexonS S 索引的删除格式dropindex索引名 索引的定义 索引的有关说明可以动态地定义索引 即可以随时建立和删除索引不允许用户在数据操作中引用索引 索引如何使用完全由系统决定 这支持了数据的物理独立性应该在使用频率高的 经常用于连接的列上建索引一个表上可建多个索引 索引可以提高查询效率 但索引过多耗费空间 且降低了插入 删除 更新的效率 数据库的建立与撤消 有的数据库系统支持多库建立一个新数据库createdatabase数据库名撤消一个数据库dropdatabase数据库名指定当前数据库database数据库名指定当前数据库closedatabase数据库名 SQL数据定义特点 SQL中 任何时候都可以执行一个数据定义语句 随时修改数据库结构 而在非关系型的数据库系统中 必须在数据库的装入和使用前全部完成数据库的定义 若要修改已投入运行的数据库 则需停下一切数据库活动 把数据库卸出 修改数据库定义并重新编译 再按修改过的数据库结构重新装入数据数据库定义不断增长 不必一开始就定义完整 数据库定义随时修改 不必一开始就完全合理 可进行增加索引 撤消索引的实验 检验其对效率的影响 SQL数据查询功能 SQL数据查询基本结构select子句重复元组的处理from子句where子句更名运算字符串操作元组显示顺序集合操作 SQL数据查询功能 分组和聚集函数空值嵌套子查询派生关系视图关系的连接 SQL数据查询基本结构 基本结构selectA1 A2 Anfromr1 r2 rmwhereP A1 A2 An p r1 r2 rm SQL数据查询基本结构 示例给出所有老师的姓名selectPNAMEfromPROF select子句 目标列形式可以为列名 算术表达式 聚集函数 表示 所有的属性 给出所有老师的信息select fromPROF select子句 带 的算术表达式给出所有老师的姓名及税后工资额selectPNAME SAL 0 95fromPROF 重复元组的处理 语法约束缺省为保留重复元组 也可用关键字all显式指明 若要去掉重复元组 可用关键字distinct或unique指明示例找出所有选修课程的学生selectdistinctSNOfromSC from子句 说明from子句列出查询的对象表当目标列取自多个表时 在不混淆的情况下可以不用显式指明来自哪个关系示例例 找出工资低于500的职工的姓名 工资 系别selectPNAME SAL DNAMEfromPROF DEPTwhereSAL 500andPROF DNO DEPT DNO from子句 例 列出教授 哲学 课程的老师的教工号及姓名selectPROF PNO PNAMEfromPROF PC COURSEwherePROF PNO PC PNOandPC CNO COURSE CNOandCOURSE CNAME 哲学 where子句 语法成分比较运算符 逻辑运算符and or notbetween条件判断表达式的值是否在某范围内 where子句 示例列出工资在500 800之间的老师姓名selectPNAMEfromPROFwhereSALbetween500and800 更名运算 格式old nameasnew name为关系和属性重新命名 可出现在select和from子句中注 as可选 更名运算 示例属性更名例 给出所有老师的姓名 所纳税额及税后工资额selectPNAME SAL 0 05astaxi SAL 0 95asincomingfromPROF 更名运算 关系更名找出工资比所在系主任工资高的老师姓名及工资selectP1 PNAME P1 SALfromPROFasP1 PROFasP2 DEPTwhereP1 DNO DEPT DNOandDEPT DEAN P2 PNOandP1 SAL P2 SAL 字符串操作 命令格式格式列名 not like 字符串 找出满足给定匹配条件的字符串 字符串操作 匹配规则 匹配零个或多个字符 匹配任意单个字符Escape定义转义字符 以去掉特殊字符的特定含义 使其被作为普通字符看待如escape 定义 作为转义字符 则可用 去匹配 用 去匹配 思考 用什么去匹配 字符串操作 示例列出姓名以 张 打头的教师的所有信息select fromPROFwherePNAMElike 张 字符串操作 列出名称中含有4个字符以上 且倒数第3个字符是d 倒数第2个字符是 的系的所有信息select fromPROFwherePNAMElike d 元组显示顺序 命令orderby列名 asc desc 示例按系名升序列出老师姓名 所在系名 同一系中老师按姓名降序排列selectDNAME PNAMEfromPROF DEPTwherePROF DNO DEPT DNOorderbyDNAMEasc PNAMEdesc 集合操作 命令集合并 union集合交 intersect集合差 except提示集合操作自动去除重复元组 如果要保留重复元组的话 必须用all关键词指明 集合操作 示例求选修了001或 且 002号课程的学生号 selectSNOfromSCwhereCNO 001 union intersect selectSNOfromSCwhereCNO 002 集合操作 求选修了001和002号而没有选003号课程的学生号 selectSNOfromSCwhereCNO 001orCNO 002 except selectSNOfromSCwhereCNO 003 分组和聚集函数 分组命令groupby列名 having条件表达式 groupby将表中的元组按指定列上值相等的原则分组 然后在每一分组上使用聚集函数 得到单一值having则对分组进行选择 只将聚集函数作用到满足条件的分组上 分组和聚集函数 列出每个学生的平均成绩 列出每门课程的平均成绩 groupbyS groupbyC 92 85 90 92 92 90 分组和聚集函数 聚集函数平均值 avg最小值 min最大值 max总和 sum记数 count count 与count 列名 的差别 分组和聚集函数 示例列出各系的老师的最高 最低 平均工资selectDNO max SAL min SAL avg SAL fromPROFgroupbyDNO 分组和聚集函数 火眼金睛 求选修了课程的学生人数selectcount SNO fromSC selectPNAME max SAL fromPROF selectDNO avg SAL fromPROFgroupbyDNOwhereAGE 60 分组和聚集函数 列出及格的学生的平均成绩selectSNO avg SCORE fromSCgroupbySNOhavingmin SCORE 60selectSNO avg SCORE fromSCwhereSCORE 60groupbySNO 哪个正确 分组和聚集函数 列出每一年龄组中男学生 超过50人 的人数selectAGE count S fromSwhereSEX M groupbyAGEhavingcount 50 空值 空值测试is not null测试指定列的值是否为空值示例找出年龄值为空的老师姓名selectPNAMEfromPROFwhereAGEisnull不可写为whereAGE null 空值 注意事项除is not null之外 空值不满足任何查找条件如果null参与算术运算 则该算术表达式的值为null如果null参与比较运算 则结果可视为false 在SQL 92中可看成unknown如果null参与聚集运算 则除count 之外其它聚集函数都忽略null 空值 例 selectsum G fromSC例 selectcount fromSC 360 6 嵌套子查询 集合成员资格集合之间的比较集合基数的测试测试集合是否为空测试集合是否存在重复元组 集合成员资格 in子查询表达式 not in 子查询 判断表达式的值是否在子查询的结果中示例列出张军和王红同学的所有信息select fromSwhereSNAMEin 张军 王红 集合成员资格 选修了001号课程的学生的学号及姓名selectSNO SNAMEfromSwhereSNOin selectSNOfromSCwhereCNO 001 集合成员资格 列出选修了001号和002号课程的学生的学号selectSNOfromSCwhereSC CNO 001andSNOin selectSNOfromSCwhereCNO 002 集合之间的比较 some all子查询表达式比较运算符 some 子查询 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式比较运算符 all 子查询 表达式的值与子查询结果中的所有的值相比都满足比较运算符 集合之间的比较 示例找出平均成绩最高的学生号selectSNOfromSCgroupbySNOhavingavg SCORE all selectavg SCORE fromSCgroupbySNO 集合基数的测试 测试集合是否为空 not exists 子查询 判断子查询的结果集合中是否有任何元组存在 in后的子查询与外层查询无关 每个子查询执行一次 而exists后的子查询与外层查询有关 需要执行多次 称之为相关子查询 集合基数的测试 列出选修了01号课程的学生的学号及姓名selectSNO SNAMEfromSwhereexists select fromSCwhereCNO 01andSNO S SNO 集合基数的测试 列出选修了001号和002号课程的学生的学号selectSNOfromSCSC1whereSC1 CNO 001andexists selectSNOfromSCSC2whereSC2 CNO 002andSC2 SNO SC1 SNO 列出选修了全部课程的学生姓名selectSNAMEfromSwherenotexists selectC fromCwherenotexists select fromSCwhereSC C C C andSC S S S 任意课程 所求学生选之 不存在任何一门课程 所求学生没有选之 集合基数的测试 列出至少选修了001号学生选修的所有课程的学生名selectSNAMEfromSwherenotexists selectCNOfromCOURSEwhereexists select fromSCwhereSC CNO COURSE CNOandSC SNO 001 andnotexists select fromSCwhereSC CNO COURSE CNOandSC SNO S SNO 任意课程 001号学生选之 所求学生选之 不存在任何一门课程 001号学生选之 所求学生没有选之 集合基数的测试 测试集合是否存在重复元组unique 子查询 如果子查询结果中没有重复元组 则返回true 集合基数的测试 示例找出所有只教授一门课程的老师姓名selectPNAMEfromPROFwhereunique selectPNOfromPCwherePC PNO PROF PNO 集合基数的测试 找出至少选修了两门课程的学生姓名selectSNAMEfromSwherenotunique selectSNOfromSCwhereSC SNO S SNO toTRUEornottoTRUE thatisthequestionunique a b null a b null 派生关系 命令 子查询 as关系名 列名 列名 SQL 92中 允许在from子句中使用子查询表达式 这时可将该子查询的结果命名为一个临时关系加以引用 派生关系Vs视图 派生关系 示例找出平均成绩及格的学生先求出每个学生的平均成绩 再从中找出及格的学生selectSNAME avg SCORE fromS SCwhereSC SNO S SNOgroupbySC SNO 派生关系 selectSNAME AVG SCOREfrom selectSNAME avg SCORE fromS SCwhereSC SNO S SNOgroupbySC SNO asresult SNAME AVG SCORE whereAVG SCORE 60 视图 定义视图createviewview name 列名 列名 as 查询表达式 withcheckoption 视图的属性名缺省为子查询结果中的属性名 也可以显式指明withcheckoption指明当对视图进行insert update时 要检查是否满足视图定义中的条件撤消视图dropviewview name 视图 示例createviewCOMPUTER PROFas selectPNO PNAME SALfromPROF DEPTwherePROF PNO DEPT PNOandDEPT DNAME 计算机系 视图 createviewDEPTSAL DNO LOW HIGH AVERAGE TOTAL as selectDNO min SAL max SAL avg SAL sum SAL fromPROFgroupbyDNO 视图 给出计算机系工资超过800的老师姓名selectPNAMEfromCOMPUTER PROFwhereSAL 800给出计算机系老师的最低 最高 平均工资以及工资总额selectLOW HIGH AVERAGE TOTALfromDEPTSAL DEPTwhereDEPTSAL DNO DEPT DNOandDEPT DNAME 计算机系 关系的连接 基本分类连接成分包括两个输入关系 连接条件 连接类型连接条件决定两个关系中哪些元组相互匹配 以及连接结果中出现哪些属性连接类型决定如何处理与连接条件不匹配的元组 关系的连接 关系的连接 自然连接出现在结果关系中的两个连接关系的元组在公共属性上取值相等 且公共属性只出现一次on出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件P 且公共属性出现两次using A1 A2 An A1 A2 An 是两个连接关系的公共属性的子集 元组在 A1 A2 An 上取值相等 且 A1 A2 An 只出现一次 关系的连接 内连接舍弃不匹配的元组左外连接内连接 左边关系中失配的元组 缺少的右边关系属性值用null表示 右外连接内连接 右边关系中失配的元组 缺少的左边关系属性值用null表示 全外连接内连接 左边关系中失配的元组 缺少的右边关系属性值用null表示 右边关系中失配的元组 缺少的左边关系属性值用null表示 关系的连接 crossjoin两个关系的笛卡儿积unionjoin左边关系中失配的元组 右边关系中失配的元组对于外连接 连接条件是必须的对于内连接 连接条件是可选的 没有连接条件等价于两个关系的笛卡儿积 关系的连接 列出老师的教工号 姓名 工资 所教课程号selectPNO PNAME SAL CNOfrom PROFnatureleftouterjoinPC 关系的连接 RinnerjoinSonR C S C R S 关系的连接 RleftouterjoinSonR C S C RnaturerightouterjoinS 关系的连接 RfullouterjoinSonR C S C SQL的数据修改功能 插入删除修改视图更新 插入操作 命令insertinto表名 列名 列名 values 值 值 插入一条指定好值的元组insertinto表名 列名 列名 子查询 插入子查询结果中的若干条元组 插入操作 示例insertintoPROFvalues P123 王明 35 D08 498 insertintoPROF PNO PNAME DNO values P123 王明 D08 思考 SAL取何值 如何防止插入带有空值的元组 插入操作 将平均成绩大于90的学生加入到EXCELLENT中insertintoEXCELLENT SNO GRADE selectSNO avg SCORE fromSCgroupby SNO havingavg SCORE 90 插入操作 不支持修改在子查询中出现的表 insertintoPROFselect fromPROF 若支持 则完成查询后 再执行修改操作 删除操作 命令deletefrom表名 where条件表达式 从表中删除符合条件的元组 如果没有where语句 则删除所有元组 删除操作 示例清除所有选课记录deletefromSC删除王明老师所有的任课记录deletefromPCwherePNOin selectPNOfromPROFwherePNAME 王明 删除操作 删除低于平均工资的老师记录deletefromPROFwhereSAL selectavg SAL fromPROF 思考 是先找到所有符合条件的元组 一并删除 还是找到一个删除一个 更新操作 命令update表名set列名 表达式 子查询列名 表达式 子查询 where条件表达式 指定对哪些列进行更新 以及更新后的值是什么 更新操作 示例老师工资上调5 updatePROFsetSAL SAL 1 05 更新操作 将D01系系主任的工资改为该系的平均工资updatePROFsetSAL selectavg SAL fromPROFwhereDNO D01 wherePNO selectDEANfromDEPTwhereDNO D01 更新操作 当C1课程的成绩小于该课程的平均成绩时 将该成绩提高5 updateSCsetGRADE GRADE 1 05whereC C1andGRADE selectavg GRADE fromSCwhereC C1 更新操作 工资超过2000的缴纳10 所得税 其余的缴纳5 所得税 updatePROFsetSAL SAL 0 9whereSAL 2000 updatePROFsetSAL SAL 0 95whereSAL 2000 执行顺序是 还是 视图更新 示例createviewP SALas selectPNO PNAME SALfromPROF insertintoP SALvalues P08 张立 750 insertintoPROFvalues P08 张立 null null 750 转换为 视图更新 createviewSN AGEas selectSNAME AGEfromS insertintoSN AGEvalues 张立 23 insertintoSvalues null 张立 23 null 转换为 缺主码信息 视图 createviewS G S AVERAGE as selectS avg GRADE fromSCgroupbyS updateS GsetAVERAGE 85whereS S1如何更新SC 视图更新 示例createviewS C Gas selectSNAME CNAME GRADEfromS C SCwhereS S SC S andC C SC C insertintoS C Gvalues 张立 物理 97 往SC中插入对应姓名为张立的学号和对应课程名为物理的课程号 以及成绩为97的元组如果有多个张立怎么办 视图更新 视图更新约束select子句中的目标列不能包含聚集函数select子句中不能使用unique或distinct关键字不能包括groupby子句不能包括经算术表达式计算出来的列对于行列子集视图可以更新 视图是从单个基本表使用选择 投影操作导出的 并且包含了基本表的主码 SQL数据控制功能 安全性控制完整性控制 安全性控制 安全性控制定义安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏 其基本措施是存取控制安全性措施物理级人际级操作系统级网络级数据库系统级 安全性控制 权限的转授和回收允许用户把已获得的权限转授给其他用户 也可以把已授给其他用户的权限再回收上来权限图结点是用户 根结点是DBA 有向边Ui Uj 表示用户Ui把某权限授给用户Uj一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径 安全性控制 安全性控制 授权命令grant表级权限on 表名 视图名 to 用户 用户 public withgrantoption 表级权限包括 select update insert delete index alter drop resource以及它们的总和all 其中对select update可指定列名withgrantoption表示获得权限的用户可以把权限再授予其它用户 安全性控制 示例grantselect insertonStoLimingwithgrantoptiongrantallonStopublic 安全性控制 回收权限revoke表级权限on 表名 视图名 from 用户 用户 public 收回权限时 若该用户已将权限授予其它用户 则也一并收回 授权路径的起点一定是DBA示例revokeinsertonSfromLiming 安全性控制 思考一 grant可以基于列授权 当revoke这样的权限时 结果是什么 思考二 执行完下列语句的结果是什么 revokeallonSfrompublicgrantselectonStopublic 安全性控制 支持多库的数据库系统中授权对象可以是数据库grant数据库级权限to 用户 用户 public 数据库级权限包括 connect 允许用户在database语句中指定数据库resource connect权限 建表 删除表及索引权利dba resource权限 授予或撤消其他用户的connect resource dba权限不允许dba撤消自己的dba权限 思考 如何使dba撤消自己的dba权限 完整性控制 完整性数据的正确性和相容性完整性检查DBMS必须提供一种机制来检查数据库中的数据是否满足规定的条件 以保证数据库中数据是正确的 避免非法的不合语义的错误数据的输入和输出 即所谓的 垃圾进垃圾出 GarbageInGarbageOut 所造成的无效操作和错误结果 完整性控制 完整性子系统数据库的非法更新数据本身是错误的数据原来是正确的 由于操作或程序错误 导致输入数据错误由于系统故障 导致数据错误事务的并发执行产生不正确结果人为故意破坏完整性子系统功能监督事务执行 检查是否违反完整性规则如有违反现象 采取相应措施 拒绝 报告 改正 完整性控制 完整性规则完整性规则集由DBA或程序员事先提供的有关数据约束的一组规则规则的构成约束条件 要检查什么样的错误 性别为男女 触发条件 什么时候使用规则进行检查 插入元组 ELSE子句 若检查出错误 该怎样进行处理 显示错误 规则的分类域完整性规则 属性取值范围域联系规则 在一个或多个关系中 属性间的联系 影响和约束关系完整性规则 更新操作对数据库中值的影响和限制 完整性控制 主码约束主码值不允许空 也不允许出现重复主码定义形式主码子句 PRIMARYKEY S 主码短语 S CHAR 4 PRIMARYKEY 完整性控制 外码约束定义形式在SC表中 定义FOREIGNKEY S REFERENCESS S S 在SC中是外码 在S中是主码 S 在SC中取值或为空或为S中某个S 值作为主码的关系称为基本 参照 关系 作为外码的关系称为依赖关系 完整性控制 删除基本关系元组RESTRICT方式只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时 才可以删除该元组 否则系统拒绝此删除操作CASCADE方式将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除SETNULL方式删除基本关系中元组时 将依赖关系中与基本关系中被删主码值相对应的外码值置为空值如FOREIGNKEY S REFERENCESS S ONDELETE CASCADE SETNULL 完整性控制 修改基本关系主码RESTRICT方式只有当依赖关系中没有一个外码值与要修改的基本关系的主码值相对应时 才可以修改该元组主码 否则系统拒绝此次修改CASCADE方式将依赖关系中所有与基本关系中要修改的主码值所对应的外码值一起修改为新值SETNULL方式 修改基本关系中元组主码时 将依赖关系中与基本关系中被修改主码值相对应的外码值置为空值如FOREIGNKEY S REFERENCESS S ONUPDATE CASCADE SETNULL 完整性控制 在属性值上的约束非空约束要求某属性取值不能为空值SNAMECHAR 8 NOTNULL基于属性的检查子句CHECK AGE 15 域约束子句用CREATEDOMAIN定义域时 可以出现CHECKCREATEDOMAINAGESMALLINTCHECK VALUE 15 ANDVALUE 25 完整性控制 全局约束全局约束涉及多个属性间的或多个关系间的联系CREATETABLESC S CHAR 4 C CHAR 4 GRADESMALLINT PRIMARYKEY S C CHECK S IN SELECTS FROMS CHECK C IN SELECTC FROMC 如果S中删除元组 不会触发CHECK子句 只有对SC表的更新才会触发 完整性控制 对约束的命名 撤消和添加命名CONSTRAINT约束名示例S CHAR 4 CONSTRAINTS PKPRIAMRYKEYAGESAMLLINTCONSTRAINTAGE VALCHECK AGE 15ANDAGE 25 完整性控制 关系上约束的撤消与添加撤消用alter drop 添加用alter add 示例altertableSdropconstraintS PKaltertableSCaddconstraintSC CHECKcheck S inselectS fromS 完整性控制 域约束的创建 撤消与添加createdomainAGE DOMAINsmallintconstraintDC AGEcheck value 15 alterdomainAGE DOMAINaddconstraintDC AGEcheck value 15 alterdomainAGE DOMAINdropconstrainDC AGE 完整性控制 断言定义CREATEASSERTIONCHECK断言是谓词 表达数据库总应该满足的条件一旦定义了断言 系统验证其有效性 并且对每个可能违反该断言的更新操作都进行检查这种检查会带来巨大的系统负载 因此应该谨慎使用断言对断言 所有X P X 是通过检查 notexistsX P X 来实现的 完整性控制 示例 不允许男同学选修张老师课程createassertionASSE2check notexists select fromSCwhereC in selectC fromCwhereTEACHER 张 andS in selectS fromSwhereSEX M 完整性控制 示例 每门课最多50名男同学选修createassertionASSE1check 50 all selectcount SC S fromS SCwhereS S SC S andSEX M groupbyC 撤消 dropassertion断言名dropassertionASSE1 SQL中完整性约束 触发器触发器是一条语句 当对数据库做修改时 它自动被系统执行触发器的定义指明什么条件下触发器被执行指明触发器执行的动作是什么触发器的作用示警满足特定条件时自动执行某项任务触发器事件Insert delete update SQL中完整性约束 SQL中完整性约束 EMP ENO ENAME SAL JOB 职工工资增幅不得超过10 createtriggerRAISE LIMITafterupdateofSALonEMPreferencingnewrowasnrowoldrowasorowforeachrowwhen nrow SAL 1 1 orow SAL beginatomicsignalSQLSTATE 7500 Salaryincrease10 end SQL中完整性约束 当帐户透支时 将帐户余额设为0 并建一笔贷款 其金额为透支额createtriggeroverdraft triggerafterupdateonaccountreferencingnewrowasnrowforeachrowwhennrow balance 0beginatomicinsertintoborrower selectcustomer name account numberfromdepositorwherenrow account number depositor account number insertintoloanvalues nrow account number nrow branch name nrow balance updateaccountsetbalance 0whereaccount account number nrow account numberend 嵌入式SQL 为什么使用嵌入式SQL 嵌入式SQL执行过程需要解决的几个问题 为什么使用嵌入式SQL 有些操作对于交互式SQL是不可能的任务SQL的表达能力相比高级语言有一定的限制 有些数据访问要求单纯使用SQL无法完成 一方面 SQL在逐渐增强自己的表达能力 参见习题4 10 另一方面 太多的扩展会导致优化能力及执行效率的降低非声明性动作实际的应用系统是非常复杂的 数据库访问只是其中一个部件 有些动作如与用户交互 图形化显示数据等只能用高级语言实现 嵌入式SQL执行过程 需要解决的几个问题 区分SQL语句与C语言语句嵌入的SQL语句以EXECSQL开始 以分号 或END EXEX结束EXECSQLdeletefromPROFwhereDNO 10 需要解决的几个问题 嵌入SQL语句与C语言之间的数据传递宿主变量C变量 既可以用在C语句中 也可用在SQL语句中 用来在两者之间传递数据宿主变量的声明声明为通常的C变量 并将其放在下列标识语句之间EXECSQLBEGINDECLARESECTIONEXECSQLENDDECLARESECTION 需要解决的几个问题 EXECSQLBEGINDECLARESECTIONintprof no charprof name 30 intsalary EXECSQLENDDECLARESECTION 需要解决的几个问题 注 宿主变量出现于SQL语句中时 前面加 以区别列名宿主变量可出现的地方 SQL的数据操纵语句中可出现常数的任何地方 select fetch等语句的into字句中示例 EXECSQLselectPNAME SALinto prof name salaryfromPROFwherePNO prof no 需要解决的几个问题 指示变量C变量 用来指示返回给宿主变量的值是否为null值 以及返回给宿主变量的字符串是否发生了截断指示变量应用场合如果一个宿主变量所对应的数据库字段允许空值 或字符串类型的宿主变量的长度可能小于所对应的数据库字段的长度 则需要一个指示变量来指明数据库访问的返回状态指示变量的返回值 0 取到主变量的值不空 没有发生截断 1 取到主变量的值为空值 0 取到主变量的值发生了截断 指示变量的值是截断前的字符串的实际长度 需要解决的几个问题 指示变量的用法声明与宿主变量的声明方式一样 在数据操纵语句中 在宿主变量和指示变量之间加 或关键字indicatorEXECSQLBEGINDECLARESECTIONintprof no charprof name 30 intsalary shortname id shortsal id EXECSQLENDDECLARESECTIONEXECSQLselectPNAME SALinto prof n