第12章安全性.ppt

创建和管理数据库对象 学习目标重点索引 视图 存储过程 触发器的创建事务和锁的概念 3 汉语字典中的汉字按页存放 一般都有汉语拼音目录 索引 偏旁部首目录等 1创建和管理索引 可根据拼音或偏旁部首 快速查找某个字词 目录 第1章数据库技术基础 1 第2章SQLServer2005概述 22 2 4 2服务器管理 44 第3章数据库的基本操作 55第4章表的基本操作 78第5章数据的基本操作 99 5 1 3数据的删除 109 1创建和管理索引 数据库应用技术 38 43 2 4 2服务器管理 44 5 1 3数据的删除 109 DELETE 110 5 IndexesUseKeyValuestoLocateData 根据索引键查找定位数据行 IndexPages 索引页 什么是索引 1创建和管理索引 6 什么是索引 SQLServer中的数据也是按页 4KB 存放索引 是SQLServer编排数据的内部方法 它为SQLServer提供一种方法来编排查询数据 索引页 数据库中存储索引的数据页 索引页类似于汉语字 词 典中按拼音或笔画排序的目录页 索引的作用 通过使用索引 可以大大提高数据库的检索速度 改善数据库性能 1创建和管理索引 非聚集索引 读者 聚集索引 读者 1创建和管理索引 110 110 8 索引类型 唯一索引 唯一索引不允许两行具有相同的索引值主键索引 为表定义一个主键将自动创建主键索引 主键索引是唯一索引的特殊类型 主键索引要求主键中的每个值是唯一的 并且不能为空聚集索引 Clustered 表中各行的物理顺序与键值的逻辑 索引 顺序相同 每个表只能有一个非聚集索引 Non clustered 非聚集索引指定表的逻辑顺序 数据存储在一个位置 索引存储在另一个位置 索引中包含指向数据存储位置的指针 可以有多个 小于249个 1创建和管理索引 9 如何创建索引 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameONtable name column name UNIQUE表示唯一索引 可选CLUSTERED NONCLUSTERED表示聚集索引还是非聚集索引 可选 使用T SQL语句创建索引的语法 唯一索引 聚集索引或非聚集索引 1创建和管理索引 10 USEstuDBGOIFEXISTS SELECTnameFROMsysindexesWHEREname IX writtenExam DROPINDEXstuMarks IX writtenExam 笔试列创建非聚集索引 填充因子为30 CREATENONCLUSTEREDINDEXIX writtenExamONstuMarks writtenExam GO 指定按索引IX writtenExam查询 SELECT FROMstuMarks INDEX IX writtenExam WHEREwrittenExamBETWEEN60AND90 如何创建索引 在stuMarks表的writtenExam列创建索引 检测索引是否存在 索引存放在系统表sysindexes中 1创建和管理索引 11 索引的优缺点 优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间 因为它们需要对索引进行更新 1创建和管理索引 12 创建索引的指导原则 请按照下列标准选择建立索引的列 该列用于频繁搜索该列用于对数据进行排序 1创建和管理索引 请不要使用下面的列创建索引 列中仅包含几个不同的值 表中仅包含几行 为小型表创建索引可能不太划算 因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长 CREATEVIEWView RASSELECT姓名 图书编号 借书时间FROM读者表 借阅表WHERE性别 女 AND读者表 借书证号 借阅表 借书证号 View R 2创建和管理视图 王维莉 李菊 112266 445501 2003 7 16 2003 6 24 112267 2004 2 6 读者 借阅 查询女性读者的姓名 所借图书的编号和借书时间 14 什么是视图 视图是一张虚拟表 它表示一张表的部分数据或多张表的综合数据 其结构和数据是建立在对表的查询基础上 2创建和管理视图 视图中并不存放数据 而是存放在视图所引用的原始表 基表 中 同一张原始表 根据不同用户的不同需求 可以创建不同的视图 15 视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度 2创建和管理视图 16 2创建和管理视图 表 Student 视图1 v Student1 视图2 v Student2 视图3 v Student3 17 演示使用设计器创建视图 2创建和管理视图 SELECTStudent StuName Course CouName StuCou StateFROMStudentJOINStuCouONStudent StuNo StuCou StuNoJOINCourseONStuCou CouNo Course CouNo 18 使用T SQL语句创建视图的语法 CREATEVIEWview nameAS 2创建和管理视图 CREATEVIEWv Student1ASSELECT FROMStudentWHEREClassNo 20000001 CREATEVIEWv Student2ASSELECT FROMStudentWHEREClassNo 20000002 EXECP R EXECP R EXECP R CREATEPROCEDUREP RASSELECT姓名 图书编号 借书时间FROM读者表 借阅表WHERE性别 女 AND读者表 借书证号 借阅表 借书证号 3创建和管理存储过程 查询女性读者的姓名和她们所借图书的编号及借书时间 20 存储过程 procedure 类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数 也可以返回结果 intsum inta intb ints s a b returns 存储过程相当于C语言中的函数 3创建和管理存储过程 什么是存储过程 21 存储过程 单个SELECT语句 SELECT语句块 SELECT语句与逻辑控制语句 可以包含 存储过程可以包含数据操纵语句 变量 逻辑控制语句等 3创建和管理存储过程 什么是存储过程 22 执行速度更快允许模块化程序设计提高系统安全性减少网络流通量 3创建和管理存储过程 存储过程的优点 23 系统存储过程由系统定义 存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以 sp 开头或 xp 开头 3创建和管理存储过程 存储过程的分类 用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数 24 3创建和管理存储过程 常用的系统存储过程 25 EXECsp databasesEXECsp renamedb Northwind Northwind1 USEstuDBGOEXECsp tablesEXECsp columnsstuInfoEXECsp helpstuInfoEXECsp helpconstraintstuInfoEXECsp helpindexstuMarksEXECsp helptext view stuInfo stuMarks EXECsp stored procedures 修改数据库的名称 单用户访问 列出当前系统中的数据库 当前数据库中查询的对象的列表 返回某个表列的信息 查看表stuInfo的信息 查看表stuInfo的约束 查看表stuMarks的索引 查看视图的语句文本 查看当前数据库中的存储过程 3创建和管理存储过程 常用的系统存储过程 26 定义存储过程的语法CREATEPROC EDURE 存储过程名 参数1数据类型 默认值OUTPUT 参数n数据类型 默认值OUTPUT ASSQL语句GO和C语言的函数一样 参数可选参数分为输入参数 输出参数输入参数允许有默认值 3创建和管理存储过程 如何创建存储过程 27 问题 请创建存储过程 查看本次考试平均分以及未通过考试的学员名单 3创建和管理存储过程 创建不带参数的存储过程 28 CREATEPROCEDUREproc stuASDECLARE writtenAvgfloat labAvgfloatSELECT writtenAvg AVG writtenExam labAvg AVG labExam FROMstuMarksprint 笔试平均分 convert varchar 5 writtenAvg print 机试平均分 convert varchar 5 labAvg IF writtenAvg 70AND labAvg 70 print 本班考试成绩 优秀 ELSEprint 本班考试成绩 较差 print print 参加本次考试没有通过的学员 SELECTstuName stuInfo stuNo writtenExam labExamFROMstuInfoINNERJOINstuMarksONstuInfo stuNo stuMarks stuNoWHEREwrittenExam 60ORlabExam 60GO proc stu为存储过程的名称 笔试平均分和机试平均分变量 显示考试成绩的等级 显示未通过的学员 3创建和管理存储过程 创建不带参数的存储过程 29 EXECUTE 执行 语句用来调用存储过程调用的语法EXEC过程名 参数 EXECproc stu 3创建和管理存储过程 调用存储过程 30 存储过程的参数分两种 输入参数输出参数 intsum inta intb ints s a b returns c sum 5 8 传入参数值 输入参数 用于向存储过程传入值 类似C语言的按值传递 输出参数 用于在调用存储过程后 返回结果 类似C语言的按引用传递 返回结果 3创建和管理存储过程 创建带参数的存储过程 31 问题 修改上例 由于每次考试的难易程度不一样 每次笔试和机试的及格线可能随时变化 不再是60分 这导致考试的评判结果也相应变化 分析 在述存储过程添加2个输入参数 writtenPass笔试及格线 labPass机试及格线 3创建和管理存储过程 带输入参数的存储过程 32 CREATEPROCEDUREproc stu writtenPassint labPassintASprint print 参加本次考试没有通过的学员 SELECTstuName stuInfo stuNo writtenExam labExamFROMstuInfoINNERJOINstuMarksONstuInfo stuNo stuMarks stuNoWHEREwrittenExam writtenPassORlabExam labPassGO 输入参数 笔试及格线 输入参数 机试及格线 查询没有通过考试的学员 3创建和管理存储过程 带输入参数的存储过程 33 EXECproc stu60 55 调用带参数的存储过程假定本次考试机试偏难 机试的及格线定为55分 笔试及格线定为60分 或这样调用 EXECproc stu labPass 55 writtenPass 60 机试及格线降分后 李斯文 59分 成为 漏网之鱼 了 3创建和管理存储过程 带输入参数的存储过程 34 带参数的存储过程确实比较方便 调用者可根据试卷的难易度 随时修改每次考试的及格线 问题 如果试卷的难易程度合适 则调用者还是必须如此调用 EXECproc stu60 60 比较麻烦这样调用就比较合理 EXECproc stu55EXECproc stu 笔试及格线55分 机试及格线默认为60分 笔试和机试及格线都默认为标准的60分 3创建和管理存储过程 输入参数的默认值 35 CREATEPROCEDUREproc stu writtenPassint 60 labPassint 60ASprint print 参加本次考试没有通过的学员 SELECTstuName stuInfo stuNo writtenExam labExamFROMstuInfoINNERJOINstuMarksONstuInfo stuNo stuMarks stuNoWHEREwrittenExam writtenPassORlabExam labPassGO 笔试及格线 默认为60分 机试及格线 默认为60分 查询没有通过考试的学员 3创建和管理存储过程 输入参数的默认值 36 EXECproc stu 都采用默认值EXECproc stu64 机试采用默认值EXECproc stu60 55 都不采用默认值 调用带参数默认值的存储过程 错误的调用方式 希望笔试采用默认值 机试及格线55分EXECproc stu 55 正确的调用方式 EXECproc stu labPass 55 3创建和管理存储过程 输入参数的默认值 37 如果希望调用存储过程后 返回一个或多个值 这时就需要使用输出 OUTPUT 参数了 问题 修改上例 返回未通过考试的学员人数 3创建和管理存储过程 带输出参数的存储过程 38 CREATEPROCEDUREproc stu notpassSumintOUTPUT writtenPassint 60 labPassint 60AS SELECTstuName stuInfo stuNo writtenExam labExamFROMstuInfoINNERJOINstuMarksONstuInfo stuNo stuMarks stuNoWHEREwrittenExam writtenPassORlabExam labPassSELECT notpassSum COUNT stuNo FROMstuMarksWHEREwrittenExam writtenPassORlabExam labPassGO 输出 返回 参数 表示没有通过的人数 推荐将默认参数放后 统计并返回没有通过考试的学员人数 3创建和管理存储过程 带输出参数的存储过程 39 调用存储过程 DECLARE sumintEXECproc stu sumOUTPUT 64print IF sum 3print 未通过人数 convert varchar 5 sum 人 超过60 及格分数线还应下调 ELSEprint 未通过人数 convert varchar 5 sum 人 已控制在60 以下 及格分数线适中 GO 调用带输出参数的存储过程 调用时必须带OUTPUT关键字 返回结果将存放在变量 sum中 后续语句引用返回结果 3创建和管理存储过程 带输出参数的存储过程 CREATETRIGGERT RON读者表FORINSERTASPRINT ThankYou ThankYou INSERTINTO读者表 借书证号 姓名 VALUES 144 王雪 4创建和管理触发器 触发器 41 赵二 插入 删除 触发器触发 赵二退休 赵二 员工表 退休员工表 4创建和管理触发器 触发器 101 何为触发器 对某一个表的一定的操作 触发某种条件 从而执行的一段程序 触发器是一个特殊的存储过程 学生 触发器 4创建和管理触发器 UPDATE 101 109 101 deleted 学号姓名 inserted 学号姓名 更新操作所影响的原有行 更新操作后的新行 101 109 袁敏 109 袁敏 课程注册 43 触发器是在对表进行插入 更新或删除操作时自动执行的存储过程触发器通常用于强制业务规则触发器是一种高级约束 可以定义比用CHECK约束更为复杂的约束可执行复杂的SQL语句 if while case 可引用其它表中的列 4创建和管理触发器 什么是触发器 44 DELETE触发器INSERT触发器UPDATE触发器 4创建和管理触发器 触发器的类型 45 触发器触发时 系统自动在内存中创建deleted表或inserted表只读 不允许修改 触发器执行完成后 自动删除inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足 则向用户报告错误消息 并回滚插入操作 4创建和管理触发器 inserted和deleted表 46 inserted表和deleted表存放的信息 4创建和管理触发器 inserted和deleted表 47 创建触发器的语法 CREATETRIGGERtrigger nameONtable name WITHENCRYPTION FOR DELETE INSERT UPDATE AST SQL语句GO WITHENCRYPTION表示加密触发器定义的SQL文本DELETE INSERT UPDATE指定触发器的类型 4创建和管理触发器 如何创建触发器 48 插入记录行 触发insert触发器 向inserted表中插入新行的副本 触发器检查inserted表中插入的新行数据 确定是否需要回滚或执行其他操作 INSERT触发器的工作原理 4创建和管理触发器 INSERT触发器 49 问题 解决上述的银行取款问题 当向交易信息表 transInfo 中插入一条交易信息时 我们应自动更新对应帐户的余额 分析 在交易信息表上创建INSERT触发器从inserted临时表中获取插入的数据行根据交易类型 transType 字段的值是存入 支取 增加 减少对应帐户的余额 4创建和管理触发器 INSERT触发器示例 50 关键代码 CREATETRIGGERtrig transInfoONtransInfoFORINSERTASDECLARE typechar 4 outMoneyMONEYDECLARE myCardIDchar 10 balanceMONEYSELECT type transType outMoney transMoney myCardID cardIDFROMinsertedIF type 支取 UPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardIDELSEUPDATEbankSETcurrentMoney currentMoney outMoneyWHEREcardID myCardID GO 从inserted表中获取交易类型 教员金额等 根据交易类型 减少或增加对应卡号的余额 4创建和管理触发器 INSERT触发器示例 51 4创建和管理触发器 INSERT触发器示例 52 删除记录行 触发delete触发器向deleted表中插入被删除的副本 触发器检查deleted表中被删除的数据 决定是否需要回滚或执行其他操作 DELETE触发器的工作原理 4创建和管理触发器 DELETE触发器 53 问题 当删除交易信息表时 要求自动备份被删除的数据到表backupTable中 分析 在交易信息表上创建DELETE触发器被删除的数据可以从deleted表中获取 4创建和管理触发器 DELETE触发器示例 54 关键代码 CREATETRIGGERtrig delete transInfoONtransInfoFORDELETEASprint 开始备份数据 请稍后 IFNOTEXISTS SELECT FROMsysobjectsWHEREname backupTable SELECT INTObackupTableFROMdeletedELSEINSERTINTObackupTableSELECT FROMdeletedprint 备份数据成功 备份表中的数据为 SELECT FROMbackupTableGO 从deleted表中获取被删除的交易记录 4创建和管理触发器 DELETE触发器示例 55 4创建和管理触发器 DELETE触发器示例 56 删除记录行 向deleted表中插入被删除的副本 检查deleted和inserted表中的数据 确定是否需要回滚或执行其他操作 UPDATE触发器的工作原理 向inserted表中插入被添加的副本 插入记录行 4创建和管理触发器 UPDATE触发器 57 问题 跟踪用户的交易 交易金额超过20000元 则取消交易 并给出错误提示 分析 在bank表上创建UPDATE触发器修改前的数据可以从deleted表中获取修改后的数据可以从inserted表中获取 4创建和管理触发器 UPDATE触发器 58 关键代码 CREATETRIGGERtrig update bankONbankFORUPDATEASDECLARE beforeMoneyMONEY afterMoneyMONEYSELECT beforeMoney currentMoneyFROMdeletedSELECT afterMoney currentMoneyFROMinsertedIFABS afterMoney beforeMoney 20000BEGINprint 交易金额 convert varchar 8 ABS afterMoney beforeMoney RAISERROR 每笔交易不能超过2万元 交易失败 16 1 ROLLBACKTRANSACTIONENDGO 从deleted表中获取交易前的余额 从inserted表中获取交易后的余额 交易金额是否 2万 回滚事务 撤销交易 4创建和管理触发器 UPDATE触发器 59 4创建和管理触发器 UPDATE触发器 60 UPDATE触发器除了跟踪数据的变化 修改 外 还可以检查是否修改了某列的数据使用UPDATE 列 函数检测是否修改了某列 问题 交易日期一般由系统自动产生 默认为当前日期 为了安全起见 一般禁止修改 以防舞弊 分析 UPDATE 列名 函数可以检测是否修改了某列 4创建和管理触发器 列级UPDATE触发器 61 关键代码 CREATETRIGGERtrig update transInfoONtransInfoFORUPDATEASIFUPDATE transDate BEGINprint 交易失败 RAISERROR 安全警告 交易日期不能修改 由系统自动产生 16 1 ROLLBACKTRANSACTIONENDGO 检查是否修改了交易日期列transDate 回滚事务 撤销交易 4创建和管理触发器 列级UPDATE触发器 62 4创建和管理触发器 列级UPDATE触发器 63 为什么需要事务 银行转帐 例如 银行转帐问题 假定资金从帐户A转到帐户B 至少需要两步 帐户A的资金减少然后帐户B的资金相应增加 帐户A 帐户B 5事务 64 假定张三的帐户直接转帐1000元到李四的帐户 为什么需要事务 CREATETABLEbank customerNameCHAR 10 顾客姓名currentMoneyMONEY 当前余额 GOALTERTABLEbankADDCONSTRAINTCK currentMoneyCHECK currentMoney 1 GOINSERTINTObank customerName currentMoney VALUES 张三 1000 INSERTINTObank customerName currentMoney VALUES 李四 1 创建帐户表 存放用户的帐户信息 添加约束 根据银行规定 帐户余额不能少于1元 否则视为销户 张三开户 开户金额为1000元 李四开户 开户金额1元 5事务 65 目前两个帐户的余额总和为 1000 1 1001元 5事务 为什么需要事务 转帐测试 张三转账1000元给李四 我们可能会这样这样编写语句 张三的帐户少1000元 李四的帐户多1000元UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 张三 UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 李四 GO 再次查看转帐后的结果 SELECT FROMbankGO 66 模拟实现转帐 从张三的帐户转帐1000元到李四的帐户 请问 执行转帐语句后 张三 李四的帐户余额为多少 张三的帐户没有减少但李四的帐户却多了1000元1000 1001 2001元总额多出了1000元 5事务 为什么需要事务 67 张三的帐户减少1000元 李四的帐户增加1000元UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 张三 UPDATEbankSETcurrentMoney currentMoney 1000WHEREcustomerName 李四 GO 错误原因分析 UPDATE语句违反约束 余额 1元 执行失败 所以张三还是1000元 继续往下执行 执行成功 所以李四变为1001元 如何解决呢 使用事务 5事务 为什么需要事务 68 事务 TRANSACTION 是作为单个逻辑工作单元执行的一系列操作这些操作作为一个整体一起向系统提交 要么都执行 要么都不执行事务是一个不可分割的工作逻辑单元 转帐过程就是一个事务 它需要两条UPDATE语句来完成 这两条语句是一个整体 如果其中任一条出现错误 则整个转帐业务也应取消 两个帐户中的余额应恢复到原来的数据 从而确保转帐前和转帐后的余额不变 即都是1001元 5事务 什么是事务 69 事务必须具备以下四个属性 简称ACID属性 原子性 Atomicity 事务是一个完整的操作 事务的各步操作是不可分的 原子的 要么都执行 要么都不执行一致性 Consistency 当事务完成时 数据必须处于一致状态隔离性 Isolation 对数据进行修改的所有并发事务是彼此隔离的 这表明事务必须是独立的 它不应以任何方式依赖于或影响其他事务永久性 Durability 事务完成后 它对数据库的修改被永久保持 事务日志能够保持事务的永久性 5事务 事务的特性 70 T SQL使用下列语句来管理事务 开始事务 BEGINTRANSACTION提交事务 COMMITTRANSACTION回滚 撤销 事务 ROLLBACKTRANSACTION一旦事务提交或回滚 则事务结束 判断某条语句执行是否出错 使用全局变量 ERROR ERROR只能判断当前一条T