MySQL数据库管理与应用任务式教程（微课版）单元6 创建与管理视图

单元6创建与管理视图《MySQL数据库管理与应用任务式教程（慕课版）》学习内容任务1创建学生成绩管理数据库中的视图

2任务2管理学生成绩管理数据库中的视图数据库系统的组成任务3通过视图更新数据1.视图的概念视图(View)是一种基本的数据库对象，它是由基于一个或多个数据表(或视图)的一个查询生成的虚拟表。视图中保存着该查询的定义。同真实表一样，视图也由列和行构成，但与真实表不同，视图本身并不存储数据，数据存储在视图引用的数据表(通常称为基表)中，视图的行和列的数据来自基表，并且是在使用视图时执行查询语句动态生成的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。我们可以这样理解，数据库中只存放了视图的定义，视图是一个或多个表(或视图)查询的结果。使用视图查询数据时，数据库会从基表中取出对应的数据，并以二维表的形式呈现出来。视图就像是基表的窗口，它反映了一个或多个基表的局部数据。6.1.1认识视图任务1创建学生成绩管理数据库中的视图2.视图常见的应用视图在数据库中起着非常重要的作用，常用于以下几个方面。显示来自基表的部分行数据。如显示学生表xs中所有男同学的信息。显示来自基表的部分列数据。如显示学生表xs中所有同学的学号、姓名、性别信息。显示来自基表的统计数据。如统计学生表xs中各专业的学生人数。显示来自多个基表的复杂查询数据。如显示学生表xs中同一个专业的学生成绩信息，包含学号、姓名、专业名、课程名和成绩列。简化数据交换操作。如可以将学生成绩信息集中到一个视图中，当需要交换数据时只需将该视图中的数据导出。任务1创建学生成绩管理数据库中的视图3.视图的优点和缺点(1)视图的优点定制用户数据。视图可以使用户将注意力集中在其关心的数据上，而不必了解基表的结构，从而屏蔽了数据的复杂性。通过定义视图，用户眼中的数据库结构变得更简单、清晰。简化数据操作。在使用查询时，很多时候要使用聚合函数，同时还要显示列的信息，可能还需要关联其他表，语句会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。共享所需数据。使用视图，不必每个用户都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。视图提供了一个简单而有效的安全机制，能够对数据提供安全保护。在设计数据库应用系统时，可以对不同的用户需求定义不同的视图，使用户看到的只是他该看到的数据，从而达到保护基础数据的目的。一般来说，在使用敏感数据的企业里，视图几乎是唯一可以用来面向普通用户的数据库对象。数据的完整性。在用户通过视图访问或者更新数据时，数据库管理系统的相关部分会自动地检查数据，确保它满足预先设定的完整性约束。任务1创建学生成绩管理数据库中的视图(2)视图的缺点性能不稳定。对视图进行查询必须先将其转换为对底层基表的查询。若视图的定义是一个基于多表的复杂的查询语句，可能需要花费很长的时间来处理查询操作。数据更新受限。由于视图是一个虚拟表，而不是一个实际存在的表，对视图的更新操作实际上是对基表的更新操作。基表的完整性约束必将影响视图，这样视图中的数据更新将受到限制。数值格式不同。在不同的数据库系统中，由于系统使用的数据类型和系统显示数值的方式有所差别，因此通过视图检索获得的结果中可能有一些数值格式不同的数据。任务1创建学生成绩管理数据库中的视图在MySQL中，可以用CREATEVIEW语句来创建视图，其语法格式如下。CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]任务1创建学生成绩管理数据库中的视图6.1.2用命令方式创建视图1.创建基于一个基表的视图默认情况下，创建的视图的列和基表的列是一样的，可以通过指定视图中列的名称来创建视图。【例题6.1】在成绩管理数据库cjgl中，创建学生视图，查看各学生的基本信息。打开MySQLWorkbench，然后打开成绩管理数据库单击工具栏上的“CreateanewSQL

tabforexecutingqueries”按钮，创建用于执行查询的新SQL选项卡，打开成绩管理数据库在代码编辑区输入并执行如下语句。USEcjgl;CREATEORREPLACEVIEW学生ASSELECT*FROMxs;可使用SELECT语句查看该视图，执行如下语句，结果如图6-1所示。SELECT*FROM学生;任务1创建学生成绩管理数据库中的视图【例题6.2】创建男生视图，查看全体男生的学号、姓名、专业名。通过MySQL命令行客户端执行如下语句。USEcjgl;CREATEVIEW男生ASSELECT学号,姓名,专业名FROMxsWHERE性别='男';【例题6.3】创建学生平均成绩视图cj_avg，其中包括学号和平均成绩。执行如下语句。mysql>USEcjgl;mysql>CREATEVIEW平均成绩(学号,平均成绩)ASSELECT学号,AVG(成绩)FROMcjGROUPBY学号;任务1创建学生成绩管理数据库中的视图2.创建基于多个基表的视图可以创建基于两个以上基表的视图，在使用这种视图时，用户不需要了解基表的完整结构，更接触不到基表中的数据，从而保护了数据的安全。【例题6.4】在cjgl数据库中，创建名为xscj的学生成绩视图，视图中包括学号、课程名和成绩列。执行如下语句。mysql>USEcjgl;mysql>CREATEVIEWxscjASSELECTcj.学号,kc.课程名,cj.成绩FROMcj,kcWHEREkc.课程号=cj.课程号;任务1创建学生成绩管理数据库中的视图3.创建视图时需要注意的事项创建视图时应该注意以下几点。只能在当前数据库中创建视图。视图的创建者必须具有数据库拥有者授予的创建视图的权限，同时还必须具有对定义视图时引用的表的相应权限。视图的命名必须符合MySQL中标识符的定义规则。每个用户定义的视图名称必须是唯一的，而且不能与用户的某个表同名。如果视图中的某一列是函数、数学表达式、常量或者视图中来自多个表的列名相同，则必须为列定义名称。如果视图引用的基表或者视图被删除，则该视图不能再使用。不能在视图上创建索引，不能在规则、默认值、触发器的定义中引用视图。任务1创建学生成绩管理数据库中的视图打开MySQLWorkbench，在SCHEMAS栏中展开当前默认的cjgl数据库，右击Views，在弹出式菜单中选择CreateView...，打开编辑视图的对话框，如图6-3所示。在图6-4所示的编辑区定义视图后单击Apply按钮，可以预览当前操作的SQL语句，如图6-5所示。6.1.3用MySQLWorkbench创建视图任务1创建学生成绩管理数据库中的视图任务1创建学生成绩管理数据库中的视图单击Apply按钮，在弹出的对话框中单击Finish按钮，即可完成视图的创建。在SCHEMAS栏中，右击Views，在弹出式菜单中选择RefreshAll，可以看到学生视图。1.查看视图的定义可以用DESCRIBE语句来查看视图的结构，其语法格式如下。DESCRIBE视图名;DESCRIBE一般情况下可以简写成DESC。【例题6.8】查看学生视图的结构。执行如下语句。mysql>USEcjgl;mysql>DESCRIBE学生;可以使用SHOWTABLESTATUS语句查看视图的基本信息，其语法格式如下。SHOWTABLESTATUSLIKE'视图名';【例题6.9】查看学生视图的基本信息。执行如下语句。mysql>SHOWTABLESTATUSLIKE'学生'\G6.2.1用命令方式管理视图任务2管理学生成绩管理数据库中的视图可以使用SHOWCREATEVIEW语句查看视图的定义，其语法格式如下。SHOWCREATEVIEW视图名;【例题6.10】查看学生视图的定义。执行如下语句。mysql>SHOWCREATEVIEW学生;任务2管理学生成绩管理数据库中的视图2.修改视图如果要修改视图的名称，可以先将原视图删除，然后用CREATEVIEW语句重新创建视图，将其名称改为新的视图名。如果视图依赖的数据表发生变化，可以通过ALTERVIEW语句修改视图来保持视图与数据表一致。其语法格式如下。ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]任务2管理学生成绩管理数据库中的视图【例题6.11】修改【例题6.4】的视图，使视图中增加开课学期列。执行如下语句。mysql>ALTERVIEWxscjASSELECTcj.学号,kc.课程名,cj.成绩,kc.开课学期FROMcj,kcWHEREkc.课程号=cj.课程号;3.删除视图当不再需要视图或要清除视图的定义和与之关联的访问权限定义时，可以删除视图。当视图被删除之后，该视图基表中存储的数据并不会受到影响，但是任何创建在该视图之上的其他数据库对象的查询将会发生错误。使用DROPVIEW语句可以删除视图，其语法格式如下。DROPVIEW[IFEXISTS]view_name[,view_name]...[RESTRICT|CASCADE]可以使用该语句同时删除多个视图，各视图名称之间需用英文逗号隔开。【例题6.12】删除学生视图。执行如下语句。mysql>DROPVIEW学生;任务2管理学生成绩管理数据库中的视图1.查看视图打开MySQLWorkbench，在SCHEMAS栏中展开当前默认的cjgl数据库，然后展开视图对象，右击想查看的学生视图，在图6-6所示的弹出式菜单中选择SelectRows–Limit200，即可查看视图内容，如图6-7所示。任务2管理学生成绩管理数据库中的视图6.2.2用MySQLWorkbench管理视图3.删除视图打开MySQLWorkbench，在SCHEMAS栏中展开当前默认的cjgl数据库，然后展开视图对象，右击需要删除的视图，在图6-6所示的弹出式菜单中选择DropView...，在弹出的图6-8所示的对话框中选择DropNow，即可直接删除视图。若选择ReviewSQL，则可以显示删除操作对应的SQL语句，单击Execute按钮就可以执行删除视图操作。2.修改视图在MySQLWorkbench中，修改视图的操作和创建视图的操作相同。任务2管理学生成绩管理数据库中的视图在MySQL中，通过视图不仅可以查询数据，而且可以更新数据。在对视图的数据进行操作时，系统根据视图的定义操作与视图关联的基表。因此，与视图对应的基表的数据也会发生变化。更新视图是指通过视图向基表中插入数据、修改数据和删除数据。由于视图不是物理存储的数据，因此对视图中的数据进行的插入、修改、删除操作实质上是作用在基表上的。要通过视图更新基表数据，必须保证视图是可更新视图。如果视图符合以下的任何一点，它就是不可更新的。（1）定义视图的SELECT语句的列名列表中有聚合函数或通过计算得到的列或DISTINCT关键字。（2）定义视图的SELECT语句中有GROUPBY、HAVING子句。（3）定义视图的SELECT语句中有UNION或UNIONALL运算符。（4）FROM子句中有不可更新视图或包含多个表。（5）WHERE子句中的子查询，引用FROM子句中的表。（6）ALGORITHM参数为TEMPTABLE（使用临时