单元创建视图和索引PPT课件.ppt

单元6创建视图和索引 引言 视图 View 是一种基本数据库对象 为用户提供了一个可以检索数据表中数据的方式 使用视图可以简化查询操作 提高数据安全性等 索引是另一个重要的数据库对象 通过索引可以快速访问表中的记录 大大提高数据库的查询性能 学习任务 任务1创建视图任务2利用视图简化查询操作任务3通过视图更新数据任务4管理和维护视图任务5创建索引任务6管理和维护索引 学习目标 理解视图概念和优点 能灵活使用图形工具及CREATEVIEW语句创建视图 能使用视图对表数据操作 查询 添加 修改 删除等 能使用视图简化查询操作 理解索引的优缺点和分类 能灵活使用图形工具及CREATEINDEX语句创建索引 任务1 任务1创建视图 任务提出 单元5中介绍的数据操作实现了对表数据的查询和更新 除了直接对表数据进行查询和更新外 还可以使用视图实现数据查询和更新 使用视图可以大大简化数据查询操作 尤其是对于实现复杂查询 视图非常有用 任务分析 在创建视图之前须先了解视图的有关概念 然后创建视图 再通过视图进行查询 更新操作 相关知识与技能 1 视图概念通过将定义好的查询作为一个视图对象存储在数据库中 视图是由表派生的 派生表被称为视图的基本表 简称基表 视图创建好后 可以和表一样 可以对它进行查询和更新 也可以在视图的基础上继续创建视图 视图也称为虚表 相关知识与技能 2 使用图形工具创建视图 1 选择 新建视图 命令 2 选择视图的来源 3 选择视图包含的列 即输出的列 4 设置连接和查询条件 5 预览视图返回的结果集 6 保存视图 注意 在同一数据库中 视图名不能和表名相同 相关知识与技能 3 CREATEVIEW语句CREATEVIEW视图名 视图列名1 视图列名n WITHENCRYPTION ASSELECT语句 WITHCHECKOPTION 相关知识与技能 4 视图作用 1 为用户集中提取数据 2 隐蔽数据库的复杂性 3 简化数据库用户权限管理 4 方便数据的交换 任务实施 1 使用图形工具创建视图 例1 创建视图BoyInfo 该视图中包含班级编号为 200901001 的班中所有男生的详细信息 练习1 创建视图CourseInfo 该视图中包含所有课程名称中含有 数据库 的课程的课程编号和课程名称 例2 创建视图CourseDetail 该视图中包含Course表和Score表中的所有列 但去除重复列 完成的视图设计器窗口如图6 5所示 例3 创建视图Details 该视图中包含Class Student Course表和Score表中的所有列 但去除重复列 任务实施 例4 创建视图EachClassNumber 该视图中包含各班级的学生人数 练习2 创建视图EachCourseScore 该视图中包含各门课程的课程名称 平时成绩的平均分和期末成绩的平均分信息 例5 创建视图FailInfo 该视图中包含期末成绩有不及格的学生的学号 完成的视图设计器窗口如图6 10所示 任务实施 2 使用CREATEVIEW语句创建视图 例6 创建视图ComputerInfo 该视图中包含计算机应用技术专业学生的学号和姓名 CREATEVIEWComputerInfoASSELECTSno SnameFROMClassJOINStudentONClass ClassNo Student ClassNoWHERESpecialty 计算机应用技术 任务实施 例7 创建视图StudentAge 该视图中包含所有学生的学号 姓名和年龄 CREATEVIEWASSELECTSno Sname YEAR GETDATE YEAR Birth 年龄FROMStudent 任务实施 练习3 创建视图GirlInfo 该视图中包含所有女生的详细信息 Student表中的所有列 迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件 任务总结 创建视图其实是将数据查询作为一个视图对象保存起来 视图可看做是一个能把焦点定在用户感兴趣的数据上的监视器 视图中没有存储数据 在数据库中 表是唯一存储数据的数据库对象 建立视图有很多作用 具体在后面的任务中介绍 拓展练习 1 创建视图LiveDetail 该视图中包含Dorm表和Live表中的所有列 但去除重复列 2 创建视图CheckDetail 该视图中包含Dorm表 Live表和CheckHealth表中的所有列 但去除重复列 3 创建视图DormDetail 该视图中包含Dorm表 Live表 CheckHealth表 Class和Student表中的所有列 但去除重复列 任务2 任务2利用视图简化查询操作 任务提出 视图最大的优点是简化查询操作 那到底如何简化查询操作呢 任务分析 视图创建好后 可以和表一样 可以对它进行查询 在大多数的情况下 用户查询的数据可能存储在多个表中 查询起来比较繁琐 此时 可以将多个表中的数据集中在一个视图中 然后通过对视图的查询查看多个表中的数据 从而大大简化数据的查询操作 任务实施 例1 查询所有年龄小于20岁的学生的学号 姓名和年龄 SELECT FROMStudentAgeWHERE年龄 20 例2 查询所有学生的学号 姓名 班级名称 选修的课程编号及平时成绩 SELECTSno Sname ClassName Cno UscoreFROMDetails 任务实施 练习1 查询班级编号为 200901001 班学生的基本信息及其选课信息 结果包含学号 姓名 性别 课程编号 课程名称 建议对视图Details进行查询 练习2 查询班级编号为 200901001 的班级人数 建议对视图EachClassNumber进行查询 任务总结 多表连接查询是关系数据库中最重要的查询 而多表连接查询往往比较复杂 使用视图可以大大简化查询 拓展练习 1 查询所有学生的详细住宿信息 结果包含学号Sno 宿舍编号DormNo 楼栋Build 房间号RoomNo 入住日期InDate 2 查询住在 龙川北苑04南 楼栋 即字段Build的值为 龙川北苑04南 的学生的学号Sno和宿舍编号DormNo 3 查询所有宿舍在2010年10月份的卫生检查情况 结果包含楼栋Build 宿舍编号DormNo 房间号RoomNo 检查时间CheckDate 检查人员CheckMan 检查成绩Score 存在问题Problem 4 查询 龙川北苑04南 楼栋各宿舍的卫生检查平均成绩 结果包含宿舍编号 平均成绩 拓展练习 5 查询 龙川北苑04南 楼栋的宿舍在2010年10月份的卫生检查情况 结果包含宿舍编号DormNo 房间号RoomNo 检查时间CheckDate 检查人员CheckMan 检查成绩Score 存在问题Problem 6 查询 龙川北苑04南 楼栋的宿舍在2010年10月份的卫生检查成绩不及格的宿舍个数 7 查询所有学生的基本信息及其住宿信息 结果包含学号Sno 姓名Sname 性别Sex 宿舍编号DormNo 楼栋Build 房间号RoomNo 入住日期InDate 8 查询姓名为 王康俊 学生的住宿信息 结果包含宿舍编号DormNo 房间号RoomNo 入住日期InDate 拓展练习 9 查询所有学生的详细信息及其住宿信息 结果包含学号Sno 姓名Sname 性别Sex 班级编号ClassNo 班级名称ClassName 宿舍编号DormNo 楼栋Build 房间号RoomNo 入住日期InDate 10 查询 计算机应用技术 专业所有学生的入住信息 结果包含学号Sno 姓名Sname 性别Sex 班级编号ClassNo 班级名称ClassName 宿舍编号DormNo 楼栋Build 房间号RoomNo 入住日期InDate 查询结果按照班级编号升序排列 同班的按照学号升序排列 任务3 任务3通过视图更新数据 任务提出 视图创建好后 可以和表一样 可以对它进行查询和更新 更新操作包括插入 INSERT 修改 UPDATE 和删除 DELETE 操作 任务分析 虽说可以对视图进行更新 但由于视图是不实际存储数据的虚表 因此对视图的更新 最终要转换为对表的更新 如果不能转换为对表的更新 则该视图更新操作不能执行 任务实施 例1 将 单元6 任务1 例1 创建的视图BoyInfo中倪骏同学的出生年月修改为1991年8月16日 UPDATEBoyInfoSETBirth 1991 8 16 WHERESname 倪骏 转换为对基本表Student表的修改 UPDATEStudentSETBirth 1991 8 16 WHERESname 倪骏 任务实施 例2 往 单元6 任务1 例2 创建的视图CourseDetail中添加一条记录 注意 通过视图更新数据时 需要注意以下几点 1 通过视图更新数据时 必须要能转换为对基表的更新 即必须符合更新基表的条件 2 修改视图中的数据时 不能同时修改两个或者多个基表 若要对基于两个或多个基表的视图中的数据进行修改 每次修改都必须只能影响一个基表 3 不能修改那些通过计算得到的字段 例如包含计算值或者集函数的字段 任务实施 例3 往GirlInfo表中添加一条男生记录 WITHCHECKOPTION子句 通过视图增 删 改数据时 系统将检查视图定义中的条件 若不满足此条件 则拒绝执行该操作 任务总结 视图创建好后 可以和表一样 可以对它进行查询和更新 更新操作包括插入 INSERT 修改 UPDATE 和删除 DELETE 操作 但须注意的是对视图的更新是受限的 因为视图是不实际存储数据的虚表 因此对视图的更新 最终要转换为对表的更新 任务4 任务4管理和维护视图 任务提出 视图的管理和维护包括查看视图的定义 查看视图与其他数据库对象的依赖关系 修改视图 重命名视图 删除视图等 任务分析 视图的管理和维护可使用SSMS的图形工具或使用SQL语句实现 任务实施 1 导出视图数据 例1 将 任务1 例4创建 视图EachClassNumber 该视图中包含各班级的学生人数 中的数据导出到D 汇总 xls工作薄中 练习1 将 任务1 练习2创建 视图EachCourseScore 该视图中包含各门课程的课程名称 平时成绩的平均分和期末成绩的平均分信息 中的数据导出到D 汇总 xls工作薄中 任务实施 2 查看视图的定义信息 1 使用SSMS的图形工具查看 2 使用sp helptext查看 EXECUTE sp helptext视图名 练习2 分别使用SQLServerManagementStudio的图形工具和sp helptext查看视图CourseDetail和视图Details的定义信息 任务实施 3 查看视图与其他对象的依赖关系 1 使用SSMS的图形工具查看 2 使用sp depends查看 EXECUTE sp depends视图名 练习3 分别使用SQLServerManagementStudio的图形工具和sp depends查看视图CourseDetail和视图Details与其他对象的依赖关系 任务实施 4 修改视图的定义 1 使用SSMS的图形工具修改视图的定义 2 使用ALTERVIEW语句修改视图定义ALTERVIEW视图名 WITHENCRYPTION ASSELECT语句 WITHCHECKOPTION 练习4 使用ALTERVIEW语句修改视图BoyInfo 任务1 例1创建 对视图的定义进行加密 并使用sp helptext查看该视图的定义信息 任务实施 5 重命名视图 1 使用SSMS的图形工具重命名视图 2 使用sp rename重命名视图 EXECUTE sp rename原视图名 新视图名 练习5 重命名 任务1 例5创建 视图FailInfo 修改后的视图名为xushanchu 任务实施 6 删除视图 1 使用SSMS的图形工具删除视图 2 使用DROPVIEW语句删除视图DROPVIEW视图名1 视图名n 练习6 删除上面练习4重命名后的视图xushanchu 任务总结 通过对视图加密来保护视图的安全性 视图的维护可使用SSMS的图形工具或使用SQL语句实现 任务5 任务5创建索引 任务提出 用户对数据库的操作最频繁的是数据查询 一般情况下 数据库在进行查询操作时需要对整个表进行数据搜索 当表中的数据很多时 按顺序搜索数据就需要很长的时间 这就造成了服务器的资源浪费 为了提高检索数据的能力 数据库引入了索引机制 任务分析 若要在一本书中查找所需信息 应首先查找书的目录 找到该信息所在的页码 然后再查阅该页码的信息 无须阅读整本书 在数据库中查找数据也一样 为了加快查询速度 创建索引 通过搜索索引找到特定的值 然后跟随指针到达包含该值的行 从而提高数据检索速度 本任务先理解SQLServer2005的数据访问方法 然后理解创建索引的优缺点和索引分类 再来根据实际需求创建和维护索引 相关知识与技能 1 数据的访问 1 表扫描法当SQLServer2005执行表扫描时 它从表的第一行开始进行逐行查找 直到找到符合查询条件的行 2 索引法先通过搜索索引树来查找所需行的存储位置 然后通过查找的结果提取所需的行 相关知识与技能 2 创建索引的优缺点 1 创建索引的好处1 加快数据查询 2 加快表的连接 排序和分组工作 2 创建索引的不足1 创建索引要花费时间和占用存储空间 2 创建索引会减慢数据修改速度 相关知识与技能 3 索引分类 1 聚集索引和非聚集索引聚集索引会对表进行物理排序 聚集索引中 表中各行的物理顺序与索引表的顺序相同 一张表只能包含一个聚集索引 与新华字典中的按拼音检索类似非聚集索引不会对表进行物理排序 与新华字典中的按笔画检索类似 2 唯一索引和非唯一索引唯一索引不允许两行具有相同的索引值 包括不能有两个空值NULL 相关知识与技能 4 使用图形工具创建索引5 CREATEINDEX语句CREATE UNIQUE CLUSTERED NONCLUSTERED INDEX索引名ON表名 列名1 列名2 任务实施 1 使用图形工具创建索引 练习1 查看School数据库表中的现有索引 观察索引的索引名称 索引类型等 练习2 在Student表的Sname列上创建非聚集索引 索引名称为 IX Student Sname 任务实施 2 使用CREATEINDEX语句创建索引 例1 在Class表的ClassName列上创建唯一索引 索引名称为 IX Class ClassName CREATEUNIQUEINDEXIX Class ClassNameONClass ClassName 练习3 在Course表的Cname列上创建非聚集索引 索引名称为 IX Course Cname 练习4 在Course表的Credit列上创建非聚集索引 索引名称为 IX Course Credit 任务总结 创建索引可以加快数据的检索速度 一张表上只能创建一个聚集索引 但可以创建多个非聚集索引 SQLServer自动会在主键列上建立聚集唯一索引 在唯一约束列上建立唯一索引 拓展练习 根据实际需求在Dorm表 Live表 CheckHe