视图的规划与操作

SQLServer 第9章视图的规划与操作 视图是用户查看数据库内数据的一种方式 它相当于一个虚拟表 用户通过它来浏览表中感兴趣的部分或全部数据 使用视图可以将用户注意力聚焦在特定的数据上 并达到数据安全保护的目的 还能简化数据查询和处理操作 第9章视图的规划与操作 9 1视图的作用与规划9 2视图操作9 3视图应用综合实例分析 9 1视图的作用与规划 视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制 视图对应于三级模式中的外模式 用户模式 它是从一个或几个基本表导出的表 由CREATEVIEW命令创建 视图又称为虚拟表 因为数据库中存放着视图的定义及其关联的基本表名等信息 而不存放视图对应的数据 视图一经定义 就可以和基本表一样被查询 被删除 但对视图的更新 增加 删除 修改 操作则有一定的限制 9 1 1视图的作用 数据库使用视图机制主要有以下优点 1 视图能够简化用户的操作视图机制使用户可以将注意力集中在所关心的数据上 2 视图使用户能以多种角度看待同一数据视图机制能使不同岗位 不同职责 不同需求的用户按照自己的方式看待同一数据 3 视图为数据库重构提供了一定程度的逻辑独立性 4 视图能够对机密数据提供安全保护对不同的用户定义不同的视图 使机密数据不出现在不应看到这些数据的用户视图上 这样视图机制就自动提供了对机密数据的安全保护功能 9 1 2视图的规划 在设计好数据库的全局逻辑结构后 还应该根据局部应用的需求 结合DBMS的特点 设计局部应用的数据库局部逻辑结构 即设计更符合局部用户需要的用户视图 定义数据库全局逻辑结构主要从系统的时间效率 空间效率 易维护等角度出发 定义用户局部视图时可以注重考虑用户的习惯与方便 9 1 2视图的规划 定义用户局部视图时可以主要考虑以下几个方面 1 使用更符合用户习惯的别名在设计数据库总体结构时 同一关系和属性具有唯一的名字 但是 在局部应用中 对同一关系或属性 有自己更加习惯的名字 我们可以用视图机制在设计用户视图时重新定义某些属性名 使其与用户习惯一致 以方便使用 2 可以对不同级别的用户定义不同的视图 以保证系统的安全性 3 简化用户对系统的使用如果某些局部应用中经常要使用某些很复杂的查询 为了方便用户 可以将这些复杂查询定义为视图 用户每次只对定义好的视图进行查询 大大简化了用户的使用 9 2视图操作 9 2 1创建视图SQLSERVER提供了使用SSMS和SQL命令两种方法来创建视图 在创建或使用视图时应该注意到以下情况 只能在当前数据库中创建视图 在视图中最多只能引用1024列 如果视图引用的表被删除 则当使用该视图时将返回一条错误信息 如果创建具有相同的表的结构新表来替代已删除的表视图则可以使用 否则必须重新创建视图 如果视图中某一列是函数 数学表达式 常量或来自多个表的列名相同 则必须为列定义名字 定义视图的查询语句不能包含COMPUTE或COMPUTEBY子句 不能包含ORDERBY子句 除非在SELECT语句的选择列表中也有一个TOP子句 不能包含INTO关键字 不能引用临时表或表变量 不能在视图上创建全文索引 规则 默认值和after触发器 不能在规则 缺省 触发器的定义中引用视图 不能创建临时视图 也不能在临时表上建立视图 1 使用SQLSERVER企业管理器来创建视图 在SQLSERVER中使用SSMS来创建视图 步骤如下 启动SSMS 登录到指定的服务器 打开要创建视图的数据库文件夹 选中 视图 图标 此时在右面的窗格中显示当前数据库的所有视图 右击图标 在弹出菜单中选择 新建视图 选项 打开 新建视图 对话框 在 新建视图 对话框中共有四个区 表区 列区SQLscript区 数据结果区 当然刚打开时是空白 2 Transact SQL命令创建视图 使用Transact SQL命令CREATEVIEW创建视图 语法格式 CREATEVIEW view name column n WITH n ASselect statement WITHCHECKOPTION ENCRYPTION SCHEMABINDING VIEW METADATA 2 Transact SQL命令创建视图 参数说明 1 view name 是视图的名称 视图名称必须符合标识符规则 可以选择是否指定视图所有者名称 2 Column 是视图中的列名 只有在下列情况下 才必须命名CREATEVIEW中的列 当列是从算术表达式 函数或常量派生的 两个或更多的列可能会具有相同的名称 通常是因为联接 视图中的某列被赋予了不同于派生来源列的名称 以便符合用户习惯 还可以在SELECT语句中指派列名 如果未指定column 则视图列与SELECT语句中的列具有相同的名称 2 Transact SQL命令创建视图 3 AS 是视图要执行的操作 4 select statement 是定义视图的SELECT语句 该语句可以使用多个表或其它视图 若要从创建视图的SELECT子句所引用的对象中选择 必须具有适当的权限 视图不必是具体某个表的行和列的简单子集 可以用具有任意复杂性的SELECT子句 使用多个表或其它视图来创建视图 在索引视图定义中 SELECT语句必须是单个表的语句或带有可选聚合的多表JOIN 在select statement中可以使用函数 select statement可使用多个由UNION或UNIONALL分隔的SELECT语句 2 Transact SQL命令创建视图 5 WITHCHECKOPTION 强制视图上执行的所有数据修改语句都必须符合由select statement设置的准则 通过视图修改行时 WITHCHECKOPTION可确保提交修改后 仍可通过视图看到修改的数据 6 WITHENCRYPTION 表示SQLServer加密包含CREATEVIEW语句文本的系统表列 使用WITHENCRYPTION可防止将视图作为SQLServer复制的一部分发布 7 SCHEMABINDING 将视图绑定到架构上 8 VIEW METADATA 指定为引用视图的查询请求浏览模式的元数据时 SQLServer将向DBLIB ODBC和OLEDBAPI返回有关视图的元数据信息 而不是返回基表或表 3 视图创建实例 例9 1 使用简单的CREATEVIEW下例创建具有简单SELECT语句的视图 当需要频繁地查询列的某种组合时 简单视图非常有用 USE教学管理IFEXISTS SELECTTABLE NAMEFROMINFORMATION SCHEMA VIEWSWHERETABLE NAME courses VIEW DROPVIEWcourses VIEWGOCREATEVIEWcourses VIEWASSELECTcno cname cbname cedi cpubFROMcourseGO 例9 2 使用WITHENCRYPTION下例使用WITHENCRYPTION选项和内置函数 使用函数时 必须为派生列指定列名 USE教学管理CREATEVIEWstu avg sno sname AVG SCORE WITHENCRYPTIONASSELECTS sno sname AVG grade FROMstudentS enrollmentEWHERES sno E snoGROUPBYS sno snameGOselectc id c textfromsyscommentsc sysobjectsowherec id o idando name stu avg 例9 3 使用WITHCHECKOPTION下例显示名为ISonly的视图 该视图使得只能对信息学院的学生做数据修改 USE教学管理CREATEVIEWISonlyASSELECTsno sssn sname ssex smtel scity smajor sdepa sgpaFROMstudentWHEREsdepa 信息学院 WITHCHECKOPTIONGOinsertintoisonlyvalues s060501 11111111 张 男 1111 宁波 会计学 会计学院 162 例9 4 如果如本章第一节概述所述 若某一全国连锁的销售企业将销售数据表按照省份进行水平分割 那我们可以使用以下视图重新装载表的数据 Createviewsales tableAsSelect fromsales beijingunionSelect fromsales tianjinunionSelect fromsales shanghai 9 2 2特殊类型视图简介 索引视图 建立唯一聚簇索引的视图为索引视图 分区视图 分区视图是通过对具有相同结构的成员表使用UNIONALL所定义的视图 信息架构视图 Microsoft提供的用于SQLServer元数据的内部视图 这些视图符合SQL 92标准中的INFORMATION SCHEMA定义 1 索引视图 由于视图返回的结果集与具有行列结构的表有着相同的表格形式 并且我们可以在SQL语句中像引用表那样引用视图 所以我们常把视图称为虚表 标准视图的数据的物理存放依然是在数据库的基本表中 只是在执行引用了视图的查询时 SQLServer才把相关的基本表中的数据合并成视图的逻辑结构 所以 这类视图也称做存储查询 问题 由于是在执行了引用了视图的查询时 SQLServer才把相关的基本表中的数据合并成视图的逻辑结构 那么当查询所引用的视图包含大量的数据行或涉及到对大量数据行进行合计运算或连接操作 毋庸置疑 动态地创建视图结果集将给系统带来沉重的负担 尤其是经常引用这种大容量视图 如何解决 索引视图 建立唯一聚簇索引的视图称做索引视图在视图上创建索引可存储创建索引时存在的数据 优点 查询优化器开始在查询中使用视图索引 而不是直接在FROM子句中命名视图 这样一来 可从索引视图检索数据而无需重新编码 由此带来的高效率也使现有查询获益 缺点 它降低了对视图基表数据的修改操作的速度 且维护索引视图比维护基础表的索引更为复杂 适合情况 非常频繁地检索视图数据 或很少修改基表数据时 在视图上创建聚集索引之前 该视图必须满足下列要求 1 当执行CREATEVIEW语句时 ANSI NULLS和QUOTED IDENTIFIER选项必须设置为ON 2 为执行所有CREATETABLE语句以创建视图引用的表 ANSI NULLS选项必须设置为ON 3 该视图所引用的对象仅包括基础表而不包括其它的视图 4 视图引用的所有基表必须与视图位于同一个数据库中 并且所有者也与视图相同 5 必须使用SCHEMABINDING选项创建视图 SCHEMABINDING将视图绑定到基础基表的架构 6 如果视图引用了用户自定义函数 那么在创建这些用户自定义函数时也必须使用SCHEMABINDING选项 7 视图必须以owner objectname的形式来使用所引用的表或用户自定义函数 8 视图中的表达式所引用的所有函数必须是确定性的 9 视图中的SELECT语句不能包含下列T SQL语法元素 选择列表不能使用 或table name 语法指定列 必须显式给出列名 不能在多个视图列中指定用作简单表达式的表的列名 派生表 行集函数 UNION运算符 子查询 外联接或自联接 TOP子句 ORDERBY子句 DISTINCT关键字 COUNT 允许COUNT BIG AVG MAX MIN STDEV STDEVP VAR或VARP聚合函数 注意 通常而言 可以在视图上创建多个索引 但是应该记住 在视图上所创建的第一个索引必须是聚簇索引 然后才可以创建其它的非聚簇索引 如果准备为视图创建索引 在执行CREATEINDEX命令以前 您必须确保以下条件 CREATEINDEX命令的执行者必须是视图的所有者 在执行创建索引命令期间 ANSI NULLS ANSI PADDING ANSI WARNINGS ARITHABORT CONCAT NULL YIELDS NULL QUOTED IDENTIFIERS诸选项应被设置成ON状态 NUMERIC ROUNDABORT选项被设置为OFF状态 视图不能包括text ntext image类型的数据列 如果视图定义中的SELECT语句指定了一个GROUPBY子句 则唯一聚集索引的键只能引用在GROUPBY子句中指定的列 2 分区视图 分区视图在一个或多个服务器间水平连接一组成员表中的分区数据 使数据看起来就象来自一个表 MicrosoftSQLServer区分本地分区视图和分布式分区视图 在本地分区视图中 所有的参与表和视图驻留在同一个SQLServer实例上 在分布式分区视图中 至少有一个参与表驻留在不同的 远程 服务器上 此外 SQLServer还区分可更新的分区视图和作为基础表只读复本的视图 在实现分区视图之前 必须先水平分割表 原始表被分成若干个较小的成员表 每个成员表包含与原始表相同数量的列 并且每一列具有与原始表中的相应列同样的特性 如数据类型 大小 排序规则 成员表设计好后 每个表基于键值的范围存储原始表的一块水平区域 键值范围基于分区列中的数据值 例如 正在将一个顾客信息Customer表分区成三个表 这些表的CHECK约束为 OnServer1 CREATETABLECustomer 33 CustomerIDINTPRIMARYKEYCHECK CustomerIDBETWEEN1AND32999 Additionalcolumndefinitions OnServer2 CREATETABLECustomer 66 CustomerIDINTPRIMARYKEYCHECK CustomerIDBETWEEN33000AND65999 Additionalcolumndefinitions OnServer3 CREATETABLECustomer 99 CustomerIDINTPRIMARYKEYCHECK CustomerIDBETWEEN66000AND99999 Additionalcolumndefinitions 生成分布式分区视图的方式 在每一个含有在其它成员服务器上执行分布式查询所需连接信息的成员服务器上添加链接服务器定义 这将使得分布式分区视图能够访问其它服务器上的数据 建以下分布式分区视图 CREATEVIEWCustomersASSELECT FROMCompanyDatabase TableOwner Customers 33UNIONALLSELECT FROMServer2 CompanyDatabase TableOwner Customers 66UNIONALLSELECT FROMServer3 CompanyDatabase TableOwner Customers 99 3 信息架构视图 信息架构视图基于SQL 92标准中针对架构视图的定义 这些视图独立于系统表 提供了关于SQLServer元数据的内部视图 信息架构视图的最大优点是 即使我们对系统表进行了重要的修改 应用程序也可以正常地使用这些视图进行访问 因此对于应用程序来说 只要是符合SQL 92标准的数据库系统 使用信息架构视图总是可以正常工作的 表9 2常用的信息架构视图 部分 在访问信息架构视图时 必须同时说明视图所属模式 即采用以下语法格式 INFORMATION SCHEMA view name例如 我们要得到某个表有多少列 可以使用以下语句 SELECTCOUNT FROMINFORMATION SCHEMA COLUMNSWHERETABLE NAME mytable 9 2 3视图的修改 重命名和删除 1 修改视图修改一个先前创建的视图的定义 使用ALTERVIEW语句 ALTERVIEW语句不影响相关的存储过程或触发器 也不更改权限 语法格式 ALTERVIEW view name column n WITH n ASselect statement WITHCHECKOPTION ENCRYPTION SCHEMABINDING VIEW METADATA 参数说明 如果原来的视图定义是用WITHENCRYPTION或CHECKOPTION创建的 那么只有在ALTERVIEW中也包含这些选项时 这些选项才有效 如果使用ALTERVIEW更改当前正在使用的视图 SQLServer将在该视图上放一个排它架构锁 当锁已授予 并且该视图没有活动用户时 SQLServer将从过程缓存中删除该视图的所有复本 引用该视图的现有计划将继续保留在缓存中 但当唤醒调用时将重新编译 ALTERVIEW可应用于索引视图 然而 ALTERVIEW将无条件地除去视图上的所有索引 例9 5 更改视图 下例创建称为All teachers的视图 该视图包含全部的教师 并将该视图的查询权授予所有用户 但是由于该视图中包含了教师的编号 身份证号等个人信息 需使用ALTERVIEW替换该视图 不包括编号 身份证号等个人信息 以保护教师个人隐私 CREATEaVIEWFROMtheteacertablethatcontainsallteachers CREATEVIEWAll teacher tno tssn tname tmtel tcity tdepa trank ASSELECTtno tssn tname tmtel tcity tdepa trankFROM教学管理 teacherGO GrantSELECTpermissionsontheVIEWtopublic GRANTSELECTONAll teacherTOpublicGO TheVIEWneedstobechangedtoexcludethetno tssn tcityattributeofallteachersALTERVIEWAll teacher tname tmtel tdepa trank ASSELECTtname tmtel tdepa trankFROM教学管理 teacherGO 2 视图重命名 使用系统存储过程sp rename对已创建的视图进行重命名 语法格式 sp rename objname object name newname new name objtype object type 例9 6 将例9 5中称为All teacher的视图重命名 语法格式 execsp rename All teacher All teacher view 3 删除视图 从当前数据库中删除一个或多个视图 可执行DROPVIEW语句 语法格式 DROPVIEW View name n 例9 7 下例删除stu avg视图 USE教学管理IFEXISTS SELECTTABLE NAMEFROMINFORMATION SCHEMA VIEWSWHERETABLE NAME stu avg DROPVIEWstu avgGO 9 2 4查询视图 视图定义后 用户就可以象对基本表一样对视图进行查询了 例9 8 如果要查询信息学院每个学生的情况 只要从视图ISonly查询即可 Select FromISonly 例9 9 创建信息学院每个学生的成绩视图 包括学生的学号 姓名 所选课程号 课程名 成绩 并进行查询 USE教学管理IFEXISTS SELECTTABLE NAMEFROMINFORMATION SCHEMA VIEWSWHERETABLE NAME ISstu score DROPVIEWISstu scoreGOCREATEVIEWISstu score sno sname cno cname grade ASSELECTS sno sname C cno cname gradeFROMstudentS enrollmentE offeringO courseCWHERES sno E snoANDE ono O onoANDO cno C cnoANDsdepa 信息学院 GO 9 2 5更新视图 更新视图是指通过视图来插入 INSERT 删除 DELETE 和修改 UPDATE 数据 由于视图是不实际存储数据的虚表 因此对视图的更新 最终要转换为对基本表的更新 MicrosoftSQLServer以两种方法增强可更新视图的类别 INSTEADOF触发器 分区视图 例9 10 在例9 3中 ISonly是一可更新视图 但由于视图使用了withCHECKOPTION选项 只允许更新信息学院学生数据 下面例子说明 如果没有withCHECKOPTION选项 则不能保护非视图数据库被插入 修改和删除 首先 创建 会计学院 学生视图 不带withCHECKOPTION选项 再用INSERT语句通过ACConly视图插入一工商管理学院的学生 然后输入前面不能通过ISonly视图插入的学生元组 通过视图对数据进行更新与删除时需要注意到以下几个问题 1 不带withCHECKOPTION选项的视图 能够插入非视图数据 因为数据最终存储在视图所引用的基本表 但插入后 不在视图数据集 故无法通过视图查询该数据 2 执行UPDATEDELETE时 所删除与更新的数据 必须包含在视图结果集中 否则失败 例子中通过ACConly视图对 S060601 学生数据的修改和删除操作均失败 3 如果视图引用多个表时 无法用DELETE命令删除数据 若使用UPDATE则应与INSERT操作一样 被更新的列必须属于同一个表 9 3视图应用综合实例分析 例9 12 一般学生信息视图 视图1 由于学生的一些个人私密信息如 身份证号 出生日期 家庭地址 家庭电话等信息是不能随便透露的 为保证学生信息安全 于是为一般用户创建一般学生信息视图如下 USE教学管理IFEXISTS SELECTTABLE NAMEFROMINFORMATION SCHEMA VIEWSWHERETABLE NAME G Stu VIEW DROPVIEWG Stu VIEWGOCREATEVIEWG Stu VIEW sno sname ssex scity sdepa smajor ASSELECTsno sname ssex scity sdepa smajorFROMstudentGO 例9 13 教师基本信息视图 视图2 USE教学管理IFEXISTS SELECTTABLE NAMEFROMINFORMATION SCHEMA