SQL视图的创建与使用

项目7创建与使用视图7.1视图概述7.2创建视图7.3使用视图的优点和缺点7.4创建视图示例7.5视图信息的查看7.6视图的修改、删除与重命名7.7视图数据的查询、插入、修改与删除7.8视图的安全性22学习目标：1、理解视图的概念；2、掌握视图的创建、查看、修改、删除与重命名；3、掌握视图数据的查询、插入、修改与删除；4、掌握视图安全性的常用设置。项目7创建与使用视图37.1视图概念视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。视图被定义后便存储在数据库中，通过视图看到的数据只是存放在基表中的数据。当对通过视图看到的数据进行修改时，相应的基表的数据也会发生变化，同时，若基表的数据发生变化，这种变化也会自动地反映到视图中。视图可以是一个数据表的一部分，也可以是多个基表的联合；视图也可以由一个或多个其他视图产生。一旦视图定义后,就可以用select语句象对真实表一样查询。本项目首页47.2创建视图7.2.1使用Transact-SQL语句创建视图1.创建视图的SQL语句的语法形式CREATEVIEW[owner.]view_name[(column_name[,...n])][WITHENCRYPTION]ASselect_statementFROMtable_nameWHEREsearch_condition[WITHCHECKOPTION]其中：view_name：为新创建的视图指定的名字，视图名称必须符合标识符规则。column_name：在视图中包含的列名,也可以在SELECT语句中指定列名。5table_name：视图基表的名字。select_statement：选择哪些列进入视图的SELECT语句。WHEREsearch_condition：基表数据进入视图所应满足的条件WITHCHECKOPTION：迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。WITHENCRYPTION：对视图的定义进行加密。2.用SQL语句创建视图的步骤在创建视图时，应首先测试SELECT语句以确保能返回正确的结果。创建视图的步骤如下：①编写用于创建视图的SELECT语句。②对SELECT语句进行测试。③检查测试结果是否正确，是否和预期的一样。④创建视图。63.在创建视图的时候，应该考虑以下因素①在CREATEVIEW语句中，不能包括ORDERBY、COMPUTE或者COMPUTEBY子句，也不能出现INTO关键字。②创建视图所参考基表的列数最多为1024列。③创建视图不能参考临时表。④在一个批处理语句中，CREATEVIEW语句不能和其他Transact-SQL语句混合使用。⑤尽量避免使用外连接创建视图。77.2.2使用SQLServerManagementStudio创建视图假设要从student表中建立一个性别为“男”、包含student_id、student_name、class_id、sex四列信息的视图。使用SQLServerManagementStudio创建视图的具体步骤如下：1）首先进入SQLServerManagementStudio。2）按顺序展开“数据库”、要创建视图所属的数据库、再展开“视图”子节点。3）右边“摘要”窗口显示的是数据库中已经存在的视图，右击窗口的空白处，在弹出的快捷菜单上选择“新建视图”项，系统弹出如图9-1的“添加表”窗口，这个窗口用于为新创建的视图提供基础数据。该窗口有三个选项卡，表、视图及函数，这意味着可以以表、视图及表值函数为基础数据创建新的视图。84）点击“添加”，选择表student，再点击“关闭”。系统呈现如图9-2的视图建立窗口。图9-1创建视图的窗口9图9-2视图建立窗口10在SQL查询条件窗格中输入查询条件语句：SELECTstudent_id,student_name,class_id,sexFROMstudentWHEREsex=1执行该语句，预览产生的结果。5）确认结果正确后，点击工具栏上的保存按钮，保存当前创建的视图，输入视图的名称，点击“确定”按钮，一个视图就创建完成了。本项目首页117.3使用视图的优点和缺点1.视图的优点查询的简单性：将复杂的查询定义为视图，保留用户所关心的数据内容，简化用户浏览和操作。安全保护：视图可以设置不同用户对数据的访问权限。屏蔽数据的复杂性：简化用户对数据库的操作，还可以使用视图重新组织数据。2.视图的缺点性能降低修改的限制本项目首页127.4创建视图示例7.4.1创建水平视图7.4.2创建投影视图7.4.3创建联合视图7.4.4创建包含集合函数的视图7.4.5创建视图的视图本项目首页7.4.1创建水平视图视图的常见用法是限制用户只能够存取表中的某些数据行，用这种方法产生的视图称为水平视图，即表中行的子集。【例9-1】在数据库JWGL的表student上创建一个视图student_view1，视图的数据包括班级号为‘g99402’或‘g99403’所有学生的资料。USEjwglGOCREATEVIEWstudent_view1ASSELECT*FROMstudentWHERE（class_id=’g99402’ORclass_id=’g99403’）本节首页147.4.2创建投影视图如果限制用户只能存取表中的部分列的数据，那么，使用这种方法创建的视图就称为投影视图，即表中列的子集。【例9-2】创建一个名为“studdent_view2”的视图，它从数据库JWGL的student表中查询出性别为“男”的所有学生的学号、姓名、性别、班级、家庭住址、入学时间、出生年月资料。USEjwglGOCREATEVIEWstudent_view2ASSELECTstudent_idAS学号,student_nameAS姓名,sexAS性别,class_idAS班级,home_addrAS家庭住址,entrance_dateAS入学时间,birthAS出生年月FROMstudentWHEREsex=1WITHCHECKOPTION本节首页157.4.3创建联合视图使用视图的一个原因是简化多表查询,可以生成从多个表中提取数据的联合视图(joinedView)把查询结果表示为一个单独的“可见表”。【例9-3】创建一个名为“student_view3”的视图，它是由表course、book及class_course创建的一个显示“g99402”班所开课程的课程名、所用教材的教材名、出版社及作者的视图。USEjwglGOCREATEVIEWstudent_view3WITHENCRYPTION/*加密视图*/ASSELECTcourse.course_nameAS课程名,book.book_nameAS书名,book.publish_companyAS出版社,book.authorAS作者FROMcourse,book,class_courseWHERE(course.book_id=book.book_idANDclass_course.course_id=course.course_id)AND(class_course.class_id='g99402')本节首页167.4.4创建包含集合函数的视图在视图定义中可以包含GROUPBY和集合函数，从而将这些汇总数据放到一个“可见”的表中，允许用户对它们做进一步的查询。要注意，出现在SELECT子句中的列名，要么包含在集合函数中，要么包含在GROUPBY子句中。【例9-4】使用集合函数SUM和GROUPBY子句以student_course表为基表，创建一个名为“student_sum_view4”、能显示所有学生学号和总成绩的视图。USEjwglGOCREATEVIEWstudent_sum_view4(学号,总成绩)ASSELECTstudent_id,sum(grade)FROMstudent_courseGROUPBYstudent_id注意：与水平视图和投影视图不同，本例产生的视图中的行与基本表中的行不是一一对应的，它是一些行数据的汇总，因此，不能通过视图来修改数据。本节首页177.4.5创建视图的视图前面创建的视图都是在表的基础上创建的，在视图的基础上还可以创建视图。【例9-5】从视图student_view1创建一个名为“student_view5”，能查询出班级名为“g99402”的所有学生资料的视图。USEjwglGOCREATEVIEWstudent_view5ASSELECT*FROMstudent_view1WHEREclass_id='g99402'GO本节首页187.5查看视图信息使用系统存储过程查看视图信息sp_help数据库对象名称sp_helptext视图（触发器、存储过程）sp_depends数据库对象名称1.使用系统存储过程①查看视图的名称、拥有者及创建日期等信息sp_helpstudent_view1②查看视图的定义脚本sp_helptextstudent_view1③查看数据的来源sp_dependsstudent_view12.使用SQLServerManagementStudio查看视图(P164)本项目首页197.6视图的修改、删除与重命名7.6.1视图的修改7.6.2视图的删除7.6.3视图的重命名本项目首页207.6.1视图的修改1．使用SQLServerManagementStudio修改视图(P165)2．使用Transact-SQL语句修改视图ALTERVIEWview_name[（column[,...n]）][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]在以上语句的语法中：view_name：被修改的视图的名字。column_name：在视图中包含的列名。WITHCHECKOPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。21table_name：视图基表的名字。WITHENCRYPTION：对包含创建视图的SQL脚本进行加密。【例9-6】修改视图student_view1的定义，使其从student表中查询出性别为“女”的所有学生的资料。USEjwglGOALTERVIEWstudent_view1ASSELECT*FROMstudentWHEREsex=0本节首页227.6.2视图的删除删除一个视图，就是删除视图的定义及其赋予的全部权限,而原先通过视图获得的数据并没有被删除。删除视图参考表（即基表）的时候，视图不会被删除，视图必须被单独删除。删除视图有两种方法，一是使用SQLServerManagementStudio删除，二是用DROPVIEW语句删除。1.用DROPVIEW语句删除视图DROPVIEW语句的语法形式如下：DROPVIEWview_namel,view_name2,…使用DROPVIEW语句可以一次删除多个视图。下面的例子是将student_view5视图删除。DROPVIEWstudent_view5232.使用SQLServerManagementStudio删除视图①进入SQLServerManagementStudio，展开相应的服务器组和相应的服务器节点。②展开“数据库”节点，然后展开视图所属的数据库，再展开“视图”子节点。③在右侧的视图资料显示窗口上，右击视图student_view1，在弹出的快捷菜单中，单击“删除”菜单命令，再单击“全部除去”按钮即可将选定的视图删除。本节首页247.6.3视图的重命名视图创建后，有时需要对视图重命名，对视图重命名有两种方法：一是用SQLServerManagementStudio，二是用系统存储过程sp_rename。1.使用系统存储过程重命名视图使用系统存储过程sp_rena