使用SQL语句创建视图.ppt

第 7 章实 现 视 图,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,视图简介,提问：如果经常需要从多个表中获取特定列的数据，并需要将这些数据组织在一起使用，有没有什么好办法让我能够进行这项工作。,场景：你建立了一个数据库，其中存储了大量的公司客户的联系人信息。公司的华东团队希望能够方便的查询上海方面的联系人信息，以便他们关注上海的市场情况。你查看了存储有关联系人的表，发现表中有一列为“城市”，并且不允许该列为“NULL”，由于这个信息将持续的提供给华东团队。这时你会作出什么样的决定呢？你决定根据该表建立视图，同时通过“城市”列让视图只显示表中的一部分信息。,视图简介,视图是个虚表，是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。,使用视图的优点和作用主要有：（1）视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。（2）视图大大地简化了用户对数据的操作。（3）视图可以让不同的用户以不同的方式看到不同或者相同的数据集。（4）在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。而使用视图可以重新组织数据，从而使外模式保持不变，原有的应用程序仍可以通过视图来重载数据。（5）视图提供了一个简单而有效的安全机制。,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,用 SQL SERVER 管理平台创建视图；用 Transact-SQL 语句中的 CREATE VIEW 命令创建视图；用 SQL SERVER 管理平台的视图模板来创建视图。,创建视图时应该注意以下情况：你必须是sysadmin、db_owner、db_ddladmin角色的成员，或拥有创建视图权限，以及对要在其中创建视图的架构的 ALTER SCHEMA 权限。只能在当前数据库中创建视图，在视图中最多只能引用1024列，视图中记录的数目限制只由其基表中的记录数决定。如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。不能在规则、默认、触发器的定义中引用视图。,SQL Server 2005提供了如下几种创建视图的方法：,当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，每个数据库对象在语句的上下文中有效，而且数据修改语句不能违反数据完整性规则。视图的名称必须遵循标识符的规则，且对每个用户必须是惟一的。此外，该名称不得与该用户拥有的任何表的名称相同。,利用 SQL SERVER 管理平台创建视图：,在SQL SERVER管理平台中，展开指定的服务器，打开要创建视图的数据库文件夹，展开指定的【数据库】，右击该数据库中【视图】图标，从弹出的快捷菜单中依次选择【新建视图】选项，如图所示。,打开【添加表】对话框。如图所示。,选择将要定义的视图所需的表、视图、函数后，通过单击字段左边的复选框选择需要的字段，如图所示。,单击工具栏中的【保存】按钮，或者单击鼠标右键，从快捷菜单中选择保存选项保存视图，输入视图名，即可完成视图的创建。,利用Transact-SQL语句中的CREATE VIEW命令创建视图：,其语法形式如下：,CREATE VIEW schema_name.view_name(column,n)WITH ENCRYPTION SCHEMABINDING VIEW_METADATA AS select_statement;WITH CHECK OPTION,注意：应制定统一的命名约定以将视图与表进行区分。例如：可将字母V或VIEW作为前缀添加到你所创建的每个视图的名称中。,举例：选择表s和sc中的部分字段和记录来创建一个视图，并且限制表s中的记录只能是计算机系的记录集合，视图定义为view_s。程序清单如下：CREATE VIEW view_sAS SELECT s.name,s.age,s.sex,o,sc.score FROM s,scWHERE s.sno=sc.sno AND s.dept=计算机;举例：创建一个视图，使之包含复杂的查询。程序清单如下：CREATE VIEW v_ExampleView WITH SCHEMABINDINGAS SELECT sno,SUM(score)AS Sumscore,COUNT(*)AS CountCol FROM scGROUP BY sno;,利用模板创建视图：,在SQL SERVER 管理平台中，选择VIEW菜单中的【模板资源管理器】选项，如下图所示。,在出现的【模板资源管理器】选项中选择【创建视图】选项，如上图所示。,按照提示输入视图名称，select语句后，执行此语句，即可创建视图。,所有权链对视图的影响：,访问视图,从属,从属,用户:John,1,视图所有者:Mary,2,表所有者:Tim,4,视图所有者:Mary,3,所有权链接允许你通过设置对一个对象（如：视图）的权限而管理对多个对象（多个表）的访问。在允许跳过权限检查的情形下，所有权链接还带来了性能上的优势。为了避免中断所有权链接，应确保所有视图和底层表和函数具有相同的所有者。,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,修改视图：,在SQL SERVER管理平台中，右击要修改的视图，从弹出的快捷菜单中选择【设计视图】选项，出现【视图修改】对话框。该对话框与创建视图时的对话框相同，可以按照创建视图的方法修改视图。,使用 Transact-SQL 语句修改视图：,使用可视化数据库工具修改视图：,首先必须拥有使用视图的权限，然后才能使用 ALTER VIEW 语句，该语句的语法形式如下：,ALTER VIEW schema_name.view_name(column,n)WITH ENCRYPTION SCHEMABINDING VIEW_METADATA AS select_statement;WITH CHECK OPTION,举例：修改了视图 V_employees，在该视图中增加了新的字段employees.salary，并且定义一个新的字段名称 e_salary。,程序清单如下：ALTER VIEW dbo.V_employees（number,name,age,e_salary）ASSELECT number,name,age,salaryFROM employees；,重命名视图：,使用可视化数据库工具重命名视图：,在SQL SERVER管理平台中，选择要修改名称的视图，并右单击该视图，从弹出的快捷菜单中选择【重命名】选项；或者在视图上再次单击，也可以修改视图的名称。接着该视图的名称变成可输入状态，可以直接输入新的视图名称。,使用系统存储过程 sp_rename 重命名视图：,该过程的语法形式为：sp_rename old_name,new_name,举例：把视图 v_all 重命名为 v_part。程序清单如下：sp_rename v_all,v_part,查看视图信息：,使用可视化数据库工具查看视图：,在SQL SERVER管理平台中，右击某个视图的名称，从弹出的快捷菜单中选择【打开视图】选项，在SQL SERVER管理平台中就会显示该视图的输出数据，如图所示。,使用系统存储过程 查看视图：,可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义，使用sp_depends显示该视图所依赖的对象。（使用SQL Server 查询分析器可以方便地显示视图属性信息，如左边图所示。右边图显示了使用sp_helptext存储过程显示视图的创建语句。）,删除视图：,对于不再使用的视图，可以使用 SQL SERVER 管理平台或者Transact-SQL语句中的DROP VIEW命令删除它。,使用Transact-SQL语句DROP VIEW删除视图，其语法形式如下：,DROP VIEW schema_name.view_name,n,可以使用该命令同时删除多个视图，只需在要删除的各视图名称之间用逗号隔开即可。举例：同时删除视图 v_student 和 v_teacher。程序清单如下：DROP view v_student,v_teacher,注意：删除视图与重建和修改视图是不一样的。,视图加密：,要保护定义视图的逻辑，可在CREATE VIEW或ALTER VIEW语句中指定WITH EMCRYPTION选项。该定义文本加密存储于sys.syscomments系统表中，因此无法读取。,提示：创建加密视图之前，应在某个安全位置存储 CREATE VIEW 或 ALTER VIEW 语句的副本；否则，若以后需要该视图的定义，则无法访问到该定义。,举例：-修改HumanResource.vEmployee视图，并启用加密，-通过 HumanResource.vEmployee 和 Person.Contact 表的内连接，-获取员工相关信息。,ALTER VIEW HumanResource.vEmployee WITH ENCRYPTION AS SELECT e.EmployeeID,c.Title.c.FirstName,c.MiddleName,c.LsstName,c.Suffix,e.Title AS JobTitle,c.Phone,c.EmailAddress FROM HumanResource.vEmployee e INNER JOIN Person.Contact c ON c.ContactID=e.ContactID,程序清单如下：,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,使用视图修改数据时，需要注意以下几点：,修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段。例如包含计算值或者合计函数的字段。如果在创建视图时指定了 WITH CHECK OPTION 选项，那么使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。执行 UPDATE、DELETE 命令时，所删除与更新的数据必须包含在视图的结果集中。如果视图引用多个表时，无法用 DELETE 命令删除数据，若使用UPDATE 命令则应与 INSERT 操作一样，被更新的列必须属于同一个表。,举例：创建一个基于表 employees 的新视图 v_employees。程序清单如下：CREATE VIEW v_employees（number,name,age,sex,salary）ASSELECT number,name,age,sex,salary FROM employees执行以下语句可向表 employees 中添加一条新的数据记录：INSERT INTO v_employeesValues（001,李力,22,m,2000）;,举例：首先创建一个包含限制条件的视图v_employee2，限制条件为工资2000，然后插入了一条不满足限制条件的记录（002,王则，30，f,1000），再用SELECT语句检索视图和表。程序清单如下：CREATE VIEW v_employee2ASSELECT*FROM employeeWHERE 工资2000GOINSERT INTO v_employee2VALUES（002,王则,30,f,1000）GOSELECT*FROM employeeGOSELECT*FROM v_employee2GO,举例 在上一个例子的基础上创建视图v_employee3，并添加WITH CHECK OPTION选项。程序清单如下：CREATE VIEW v_employee3ASSELECT*FROM employeeWHERE 工资2000WITH CHECK OPTIONGOINSERT INTO v_employee3VALUES（002,王则,30,f,1000）GOSELECT*FROM employeeGOSELECT*FROM v_employee3GO,运行该程序将显示如下出错信息：Server:Msg 550,Level 16,State 1,Line 1The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.The statement has been terminated.,使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE 语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。举例：创建了一个基于表 employees 的视图 v_employees，然后通过该视图修改表 employees 中的记录。程序清单如下：CREATE VIEW v_employees AS SELECT*FROM employees UPDATE v_employees SET name=张然 WHERE name=张三；举例：利用视图v_employees删除表employees中姓名为张然的记录。程序清单如下：DELETE FROM v_employees WHERE name=张然；,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,视图的性能注意事项：,视图将导致性能开销，因为视图是动态生成的嵌套视图存在性能上的风险评估非加密嵌套视图的定义使用“SQL Server Profiler”评估性能索引视图和分区视图能够提高性能,索引视图：,场景：你为销售部门创建一个数据库，用于存储售出的产品信息。销售部经理需要你将售出的产品中有打折信息的产品信息单独提供给销售部，这些信息每周处理一次。你查看产品表，发现其中一列信息用来显示打折，因此你为销售部门单独建立视图以供他们查询。一段时间后，销售部门反映查询速度慢，发现其中存有大量数据，你为其建立索引，将其转换为索引视图。经测试，查询性能得到显著提高。,唯一聚集索引的视图具体化视图。对基本表中数据进行修改时，存储于索引视图的数据也相应发生变化。缩短了检索时间，提高了查询性能；在查询解决方案中，若某一查询的FROM字句未直接指定视图，允许使用查询优化器使用视图索引。使用场合查询性能收益大于维护开销 底层数据更新不频繁 查询执行大量处理多行或由很多用户频繁执行的联接和聚合操作,创建索引视图存在多种相关要求和限制，主要包括：对视图创建的第一个索引必须是唯一的聚集索引；该视图必须是使用SCHEMABINDING 选项定义；视图可以引用基表，但不能引用其他视图；,USE AdventureWorksGOCREATE UNIQUE CLUSTERED INDEXIX_vStateProvinceCountryRegion ONPerson.vStateProvinceCountryRegion(StateProvinceID ASC,CountryRegionCode ASC),举例：-在Person.vStateProvinceCountryRegion 上创建唯一聚集索引，-指定StateProvinceID和CountryRegionCode为索引列，按升序排列。程序清单如下：,创建索引视图存在多种相关要求和限制，主要包括（续）：视图引用的基表必须与该视图位于同一个数据库中，并且与该视图有着相同的所有者；视图中的表达式所引用的函数必须是确定性的。,视图简介 创建视图 修改、查看、删除和重命名视图 通过视图修改数据使用视图优化性能实验,在与人力资源部门的协商后，高级数据库开发人员确定了一些将改善数据库使用并简化未来开发的视图。高级数据库开发人员要求你执行以下任务：创建一个新视图。因为你以后需要对该视图创建索引，所以创建视图时需要使用SCHEMABINDING。在测试了该视图之后，为视图创建唯一聚集索引，将其转变为索引视图。,实验：,