第四章 视图和游标

数据库运用技术——视图和游标主讲：邵燕林GeographicalInformationSystem§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标第五大节教学目标：1.了解视图的概念和在数据库系统中发挥的作用；2.掌握创建视图、修改视图和删除视图的方法；※3.掌握查询视图和通过视图更新数据的方法，了解哪些视图不可进行数据更新；4.了解游标的概念和在数据库系统中发挥的作用；5.掌握声明游标的语法，理解其中一些常用关键字的作用、含义；※6.掌握打开和读取游标的语法，熟悉各种类型游标的读取方法；※7.掌握关闭、删除游标的方法。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标§6.1视图视图的概念：视图是从一个或多个表（或视图）导出的虚拟表。视图是实现关系数据库系统“三级模式”中外模式的重要技术，是实现关系数据库系统“逻辑独立性”的重要机制。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标试比较通过视图和直接通过SQL查询两种方法的区别，归纳视图的作用与优点。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标使用视图的作用与优点：※为用户集中数据，简化用户的数据查询和处理；※屏蔽数据库的复杂性；※简化用户权限的管理；※便于数据共享；※可以重新组织数据，以便输出到其他应用程序中。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标一、创建视图从前面的学习可以知道，任何数据库对象都有两种基本的创建方法：通过企业管理器创建（或向导）；通过T-SQL语句创建。TIP：这两种方法的本质是一样的，企业管理器只不过是数据库生产商为了方便用户对数据库的管理而实现的一个数据库应用程序而已，其对数据库的常规操作，一般都是在后台转化为T-SQL语句来实现的。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标1.通过企业管理器创建打开企业管理器，展开XSBOOK数据库，选中其子节点【视图】，右键弹出菜单，选择【新建视图】，弹出视图创建窗口。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标可在此修改编辑自动生成的代码选择需要输出的列，将自动生成代码§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标点击保存视图§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标2.T-SQL创建视图基本语法：CREATEVIEWview_name[WITHview_attribute[,…n]ASselect_statement[WITHCHECKOPTION]说明：1)WITHview_attribute：指出视图的属性。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标view_attribute::={ENCRYPTION|SCHEMABINDING|VIEW_METADATA}ENCRYPTION：说明在系统表syscomments中存储CREATEVIEW语句时进行加密；SCHEMABINDING：说明将视图与所依赖的表或视图结构相关联；VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，返回有关视图的元数据信息；§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标2)CREATEVIEW语句不能：◆包含COMPUTE或COMPUTEBY子句。◆包含ORDERBY子句，除非在SELECT语句的选择列表中也有一个TOP子句。◆包含INTO关键字。◆引用临时表或表变量。3)WITHCHECKOPTION：指出在视图上进行数据更新时要符合select_statement所限定的记录范围。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标3.视图创建示例基于XSBOOK数据库中的XS、BOOK、JY表创建包含学生借阅信息的视图。创建代码：CREATEVIEW学生借阅信息ASSELECT姓名,专业名,书名,借书时间FROMXS,JY,BOOKWHEREXS.借书证号=JY.借书证号ANDJY.ISBN=BOOK.ISBNGO§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标4.课堂实训基于XSBOOK数据库中的XS表创建包含学生基本信息（借书证号、姓名、性别、年龄）的视图，要求性别用“男/女”表示。创建代码：CREATEVIEWview_ageASSELECT借书证号,姓名,性别=CASE性别WHEN0THEN'男'WHEN1THEN'女'END,datepart(yyyy,getdate())-datepart(yyyy,出生时间)as年龄FROMXSGO§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标二、查询视图视图定义后，就可以如同查询基本表那样对视图进行查询。具体查询语法在前面一章中已经介绍，这里就不在赘述。视图查询机理：执行视图查询时，系统首先进行有效性检查，检查视图及其关联的表是否存在；通过检查后，从系统取出视图的定义，把定义中的子查询和用户查询结合起来，转换成等价的对基本表的查询，然后再执行转换以后的查询。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标三、通过视图更新数据由于视图是不实际存储数据的虚拟表，因此对视图的更新最终要转换为对基本表的更新。要通过视图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是下情形之一：1.可更新的分区视图；2.同时满足：§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标①创建视图的SELECT语句中无聚合函数，且没TOP、GROUP、UNION子句及DISTINCT关键字；②创建视图的SELECT语句中不包含从基本表列通过计算机所得的列；③创建视图的SELECT语句的FROM子句中至少要包含一个基本表。3.通过INSTEADOF触发器创建的可更新视图。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标视图更新实例：1）先基于XSBOOK数据库XS表创建一个包含’计算机’专业学生信息的视图；2）在其中插入一行记录（有默认值的字段可以先不输入数值），体会视图创建语句中WITHCHECKOPTION关键字的作用；3）对刚插入的记录进行更新和删除；§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标此处如果不是插入’计算机’，则不满足withcheckoption，插入数据将被拒绝§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标四、修改视图可以通过企业管理器或T-SQL语句修改1.通过企业管理器修改启动企业管理器，选中要修改得视图，右键弹出菜单，选择【设计】视图，进入视图设计界面，前面新建视图中已讲解，这里不再赘述。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标函数七、游标应用实例数据库运用技术数据库运用技术第四章视图和游标2.使用ALTERVIEW语句修改视图语法格式：ALTERVIEW[view_name[(column_name[,…n])][WITHview_attribute[,…n]]ASselect_satement[WITHCHECKOPTION]【应用实例】修改前面创建好的’学生借阅信息’视图，使其只包含计算机专业学生的借阅信息。§4.1视图一、创建视图二、查询视图三、通过视图更新数据四、修改视图五、删除视图六、视图应用§4.2游标一、声明游标二、打开游标三、读取游标四、关闭游标五、删除游标六、常用游标