MySQL开发与实践 第7章 视图.pdf

lmrm二二一第7章视图本章要求:了解使用CREATE VI EW语句创建视图 了解创建视图的注意事项 掌握SHOW TABL E STATUS语句查看视图 掌握CREATE OR REPL ACE VI EW语句修改视图 掌握使用AL TER语句修改视图 掌握更新视图和使用DROP VI EW语句删除视图4HFL 二二主要内容第7章视图1.视图概述2.创建视图3.视图操作4.综合实例一一使用视图查询学生信息表7.1视图概述7.1.1视图的概念7.1.2视图的作用7.1.1视图的概念视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。同真实的 表一样，视图包含一系列带有名称的列和行数据。但是，数据库中只存放了视图的定义，而并没有 存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表 中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些 数据，例如：员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。7.1.2视图的作用 对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数.据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时.的限制也很少。下面将视图的作用归纳为如下几点：.i.简单性看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经 常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。2.安全性 视图的安全性可以防止未授权用户查看特定的行或列，权限用户只能看到表中特定行的方法如下：(1)在表中增加一个标志用户名的列；(2)建立视图，是用户只能看到标有自己用户名的行；(3)把视图授权给其他用户。3.逻辑数据独立性视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的 O有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以 下几个方面使程序与数据独立：(1)如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表 的变化，从而应用程序可以不动。(2)如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的 变化，从而使数据库表不动。(3)如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变 化，从而应用程序可以不动。(4)如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化,从而数据库可以不动。7.2创建视图7.2.1查看创建视图的权限7.2.2创建视图7.2.3创建视图的注意事项27.2.1查看创建视图的权限创建视图需要具有CREATE VI EW的权限。同时应该具有查询涉及的列的SEL ECT权限。可以使用SEL ECT语句来查询这些权限信息，查询语法如下：SELECT Selete_priv,Create_view_priv FROM mysqLuser WHERE user=用户名；Selete_priv属性表示用户空否具有SEL ECT权限，Y表示拥有SEL ECT权限，N表示没有；Create_view_priv属性表示用户是否具有CREATE VI EW权限；mysql.user表示M ySQL数据库下面 的user；“用户名;参数表示要查询是否拥有DROP权限的用户，该参数需要用单引号引起来。!Select_priv；Create_uiew_priv；3 rows in set YYY【例7-1】下面查询M ySQL中root用户是否具有创建视图的权限，代码如下:SELECT Selete_priv,Create_view_priv FROM mysql.user WHERE user=root;执行结果如由7T所示。n/sql SELECT Select_priv,Create_uiew_priv FROM mt/sql.user WHERE user=*root*；图7-1查看用户是否具有创建视图的权限结果中uSelect_privn和“Createviewpriv”属性的值都为Y,这表示root用户具有SEL ECT 和CREATE VI EW权限。7.2.2创建视图M ySQL中，创建视图是通过CREATE VI EW语句实现的。其语法如下:CREATE ALGORITHM：UNDEFINED|MERGE|TEMPTABLE VIEW视图名（属性清单）AS SELECT语句WITH CASCADED|LOCAL CHECK OPTION;AL G ORI THM是可选参数，表示视图选择的算法；“视图名”参数表示要创建的视图名称；“属性清单”是可选参数，指定视图中各个属性的名词，默认情况下与SEL ECT语句中查询的属 性相同；SEL ECT语句参数是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记 录导入视图中；WI TH CHECK OPTI ON是可选参数，表示更新视图时要保证在该视图的权限范围之内。【例7-2】在tb_book数据表中创建viewl视图，视图命名为book_viewl,并设置视图属性分别为a_sort a_talk a_books,代码如下：CREATE VIEWbook_view 1(a_sort,a_talk,a_books)AS SELECT sort,talk,books FROM tb_book;执行结巢如图7-2所示。nysql CREATE UIEU-book_uiei/l AS SELECT sort,talk,books-FROM tbjtiook；Query OK,0 rovis affected 图7-2 创建视图bookviewl如果要在tb_book表和tb_user表上创建名为book_viewl的视图，执行代码如下:CREATE ALGORITHM=MERGE VIEWbook_view 1(a_sort,a_talk,a_books,a_name)AS SELECT sort,talk,books,tb_user.nameFROM tb_book,tb_name WHERE tb_book.id=tb_name.idWITH LOCAL CHECK OPTION;建议读者自己上机实践一下，这样会加深记忆。7.2.3创建视图的注意事项创建视图时需要注意以下几点：.(1)运行创建视图的语句需要用户具有创建视图(create view)的权限，若加了 or replace时，还需要用户具有删除视图(drop view)的权限；.(2)select语句不能包含from子句中的子查询；(3)select语句不能引用系统或用户变量；.(4)select语句不能引用预处理语句参数；(5)在存储子程序内，定义不能引用子程序参数或局部变量；(6)在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句；(7)在定义中不能引用temporary表，不能创建temporary视图；.(8)在视图定义中命名的表必须已存在；(9)不能将触发程序与视图关联在一起；.(10)在视图定义中允许使用。rder by,但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。一二一7.3视图操作7.3.1查看视图7.3.2 修改视图7.3.3 更新视图7.3.4 删除视图7.3.1查看视图查看视图是指查看数据库中已存在的视图。查看视图必须要有SHOW VI EW的权限。查看视图的 方法主要包括DESCRI BE语句、SHOW TABL E STATUS语句、SHOW CREATE VI EW语句等。本节将主要介 绍这几种查看视图的方法。1.DESCRI BE语句DESCRI BE可以缩写成DESC,DESC语句的格式如下：DESCRIBE视图名；下面使用DESC语句查询book_viewl视图中的结构，结果如图7-3所示。mysql DESC book_uiewl；I Field I Type!Null!Key!Defaulti a_sort i uarchari a_talk uarchar a_books!uarchaiNO NO NONULL NULL NULL3 vows in set 图7-3 使用DESC语句查询book_viewl视图中的结构结果中显示了字段的名称(Field)、数据类型(Type)、是否为空(N ull)、是否为主外键(K ey)、默认值(Default)和额外信息(Extra)。说明：如果只需了解视图中的各个字段的简单信息，可以使用DESCRI BE语句。DESCRI BE语句查看视图 的方式与查看普通表的方式是相同的，结果显示的方式也相同。通常情况下，都是使用DESC代替 DESCRI BEo/I 2.SHOW TABL E STATUS语句.在M YSQL中，可以使用SHOW TABL E STATUS语句查看视图的信息。其语法格式如下：SHOW TABLE STATUS LIKE，视图名，；“L I K E”表示后面匹配的是字符串；“视图名”参数指要查看的视图名称，需要用单引号定义。【例7-3】下面使用SHOW TABL E STATUS语句查看视图book_viewl中的信息，代码如下:SHOW TABLE STATUS LIKE book_viewl,;执行结果如图7-4所示。mysql SHOW TABLE STATUS LIKE fbook_uiewl,G；X WX XMX X X-X X X X X XM WXMX M J.pQVI X X X X X X X X X X Name：book_uiewlEngine：NULLUersion：NULLRov;_fornat：NULLRows：NULLAvg_row_length：NULLData_length：NULLMax_data_length：NULLIndex_length：NULLData_free：NULLAuto_increnent：NULLCreate_tine：NULLUpdate_time：NULLCheck_tine：NULLCollation：NULLChecksum：NULLCreate_options：NULL Connent：UIEW1 row in set 图7-4 使用SHOW TABL E STATUS语句查看视图book_viewl中的信息从执行结果可以看出，存储引擎、数据长度等信息都显示为N UL L,则说明视图为虚拟表，与普通数据表是有区别的。下面使用SHOW TABL E STATUS语句来查看tb_book表的信息，执行结果如图7-5所示。二二二iysql SHOU TABLE STATUS LIKE tb_bookG；Name：tb_book Engine：MyI SAM Uersion：10 Row_fornat：Dynamic Rows：14 Avg_row_length：204 Data_length：2964 lax_data_length：281474976710655 Index_length：2048 Data_free:96 Auto_increment：35 Create_tine：2011-06-0?14:00:25 Update_tine：2011-06-13 10:26：36 Check_time:NULLCollation：utf8_general_ci Checksum：NULL Create_options：Comment:row in set 图7-5 使用SHOW TABL E STATUS语句来查看tb_book表的信息从上面的结果中可以看出，数据表的信息都已经显示出来了，这就是视图和普通数据表的区别。3.SHOW CREATE VI EW语句在M YSQL中，SHOW CREATE VI EW语句可以查看视图的详细定义。其语法格式如下：SHOW CREATE VIEW 视图名【例7-4】下面使用SHOW CREATE VI EW语句查看视图book.viewl的信息，代码如下:SHOW CREATE VIEW book_viewl;代码执行结果如图7-6所示。nysql SHOW CREATE UIEW book_uiewlG；*X*XX 1.POW XMXXXXXXMXMmXXX X XXXNXXUiew：book_uiewlCreate Uiew：CREATE ALGORITHM=UNDEFINED DEFINER=root0localhost SQL SECURIT DEFINER UIEW book_uiewl AS select tb_book.sort AS a_sort,tb_Jt)ook.talk AS a_talk,tb_book.books AS a_books From tb_book 1 row in set 图7-6 使用SHOW CREATE VI EW语句查看视图book_viewl的信息 通过SHOW CREATE VI EW语句，可以查看视图的所有信息7.3.2修改视图修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修 改视图来保持视图和基本表之间一致。M ySQL中通过CREATE OR REPL ACE VI EW语句和AL TER语句来修 改视图。下面介绍这两种修改视图的方法。1.CREATE OR REPL ACE VI EW在M YSQL中，CREATE OR REPL ACE VI EW语句可以用来修改视图。该语句的使用非常灵活。在视图 已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。CREATE OR REPL ACE VI EW语 句的语法如下：CREATE OR REPLACE ALGORITHMUNDEFINED|MERGE|TEMPTABLEVIEW视图（属性清单）AS SELECT 语句WITH CASCADED|LOCAL CHECK OPTION;【例7-5】下面使用CREATE OR REPL ACE VI EW语句将视图book_viewl的字段修改为a sort和a_book,执行结果如图7-7所示。二;CREATE OR REPLACE ALGORITHM=TEMPTABLEUIEW book_uiewl AS SELECT sort,books FROM tb_book；!uery OK,0 rows affected 图7-7 使用CREATE OR REPL ACE VI EW语句修改视图使用DESC语句查询book_viewl视图，结果如图7-8所示。nysql DESC book_uiewl；Field-!Type-!Null!Key-!Default Extraa_sorti uarchar!NO1 1：NULL!a_booki uarchai!NO1 1；NULL：-f-2 rows in set 图7-8 使用DESC语句查询book_viewl从上面的结果中可以看出，修改后的book_viewl中只有两个字段。2.AL TERAL TER VI EW语句改变了视图的定义，包括被索引视图，但不影响所依赖的存储过程或触发器。该语句与CREATE VI EW语句有着同样的限制，如果删除并重建了一个视图，就必须重新为它分配权限alter view语句的语法如下：alter view algorithm=merge|temptable|undefined view view_name(column_list)as select_statementwith cascaded|local check option algorithm：该参数已经在创建视图中作了介绍，这里不再赘述。view name：视图的名称。select statement：SQL语句用于限定视图。注意：在创建视图时，在使用了WI TH CHECK OPTI ON,WI TH EN CRYPTI ON,WI TH SCHEM ABI N G或VI EW_M ETADATA选项时，如果想保留这些选项提供的功能，必须在AL TER VI EW语句中将它们包括 进去。【例7-6 下面将book viewl视图进行修改，将原有的a_sort和a_book两个属性更改为a sort 1个属 性。在更改前，先来查看一下book_viewl视图此时包含的房性，结巢如图7-9所示。mysql DESC book_uiewl；!Field：Type 1!Null!Key!Default!Extra1 1i a_sort i uarchar i!a_book!varchar!：NO：!NO：!NULL i!NULL!112 rows in set 图7-9 查看bookviewl视图的属性队结果中可以看出，此时的book_viewl视图中包含两个属性，下面对视图进行修改，结果如图7-10所示。nysql ALTER UIEU book_uiev/1-AS SELECT sort-FROM tbjjook-WITH CHECK OPTION；Quei*y OK,0 rows affected 图7-10修改视图属性结果显示修改成功，下面再来查看一下修改后的视图属性，结果如图7-11所示。Hysql DESC book_uiewl；Field iType1 1Nulli Key1 1Default-i Extraa_sort!varchar1 1NO1 11 1NULL1 1-_ 1-1 rov;in set select*from book_uiewl；!a_book2627282930252414202122233134应 应型础目例础酢目础例础础 典基项范基范典实实项基,基i JAUA典型陵块!ASP.NET24堂课：Ctt项目整合；Word范例宝典 PHP24堂i窄;J备陶薪学手册;糠算自学手册:PHP项目整合：PHP24堂课:UB范例宝英：PHP网络编程自学手册：PHP编程全典14 rows in set 图7T2 查看bookviewl视图中的数据下面更新视图中的第27条记录，a_sort的值为“模块类，a_book的值为“PHP典型模块”，更新语句结果如图7T3所示。nysql UPDATE book_uiewl SET a_sort=，模块类la_book=，PHP典型模块，WHERE id=27；Query OK,1 row affected Rows matched：1 Changed：1 Warnings：0图7-13更新视图中的数据|二一结果显示更新成功,下面再来查看一下book_viewl视图中的数据是否有变化，结果如图7T4所示。SELECT*FROM book_uiewl;id i a_sort；a_book；26!27!28：29：30：25!24：14!20：21!22：23I 31：34型块目例砒例型例例目砒例砒砒 壹项范基范典实实项基壹基类 用类 用!JAUA典型模块!PHP典型模块!C#项目整合；Wo范例宝典：PHP24堂课!J匿勰薪学手册 二瓣爨全自学手册:PHP项目整合:PHP24堂课:UB范航宝典；PHP网络编程自学手册!PHP编程宝典4 rows in set 图7T4查看更新后视图中的数据 SELECT id,sort,books FROM tb_book；i id sort!books6789054401231422223221222233型块目例砒目砒例砒砒 意项范基范典实实项基实基基应 应我 用:JAUA典型模块：PHP典型榭夬：C4项目整合!Mord范例宝典：PHP24堂课类 用；JAUA建例完全自学手册；UB典型摸块:PHP葩例完全自学手册:C范丽宝典：PHP项目整合:PHP24堂课：UB范例j宝典；PHP网络编程自学手册：PHP编程金典4 rows in set 图7T 5查看tb_book表中的数据 从上面的结果可以看出，对视图的更新其实就是对基本表的更新。2.更新视图的限制并不是所有的视图都可以更新，以下几种情况是不能更新视图的：(1)视图中包含COUN T。、SUM()、M AX()和M I N()等函数。例如：CREATE VIEW book_view 1(a_sort,a_book)AS SELECT sort,books,COUNT(name)FROM tb_book;(2)视图中包含UN I ON、UN I ON AL L、DI STI N CT、G ROUP BY和HAVI G等关键字。例如:CREATE VIEW book_view 1(a_sort,a_book)AS SELECT sort,books,FROM tb_book GROUP BY id;(3)常量视图。例如：CREATE VIEW book_viewlAS SELECT Aric as a_book;(4)视图中的SEL ECT中包含子查询。例如:CREATE VIEW book_view 1(a_sort)AS SELECT(SELECT name FROM tb_book);(5)由不可更新的视图导出电视图。例如：CREATE VIEW book_viewlAS SELECT*FROM book_view2;|二一(6)创建视图时，AL G ORI THM为TEM PTABL E类型。例如：CREATE ALGORITHM=TEMPTABLEVIEW book_viewlAS SELECT*FROM tb.book;(7)视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。例如，表中包含的name字 段没有默认值，但是视图中不包括该字段。那么这个视图是不能更新的。因为，在更新视图时，这 个没有默认值的记录将没有值插入，也没有N UL L值插入。数据库系统是不会允许这样的情况出现的,其会阻止这个视图更新。上面的几种情况其实就是一种情况，规则就是，视图的数据和基本表的数据不一样了。注意：视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟 表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据 的限制，可能会造成数据更新失败。7.3.4删除视图删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据 o M ySQL中，使用DROP VI EW语句来删除视图。但是，用户必须拥有DROP权限。本节将介绍删除视图的 方法。.DROP VI EW语句的语法如下：DROP VIEW IF EXISTS视图名RESTRICT|CASCADE I F EXI STS参数指判断视图是否存在，如果存在则执行，不存在则不执行；“视图名列表”参数表示要删除的视图的名称和列表，各个视图名称之间用逗号隔开。该语句从数据字典中删除指定的视图定义；如果该视图导出了其他视图，则使用CASCADE级联删除，或者先显式删除导出的视图，再删除该视图；删除基表时，由该基表导出的所有视图定义都必须显式删除。【例7-8】下面删除前面实例中一直使用的book_viewl视图，执行语句如下:DROP VIEW IF EXISTS book_viewl;执行结果如图7-16所示。mysql DROP UIEU IF EXISTS book.uiewl；Query OK,0 rows affected 图7-16删除视图执行结果显示删除成功。下面验证一下视图是否真正被删除，执行SHOW CREATE VI EW语句查看,执行结果如图7T7所示。mysql SHOW CREATE UIEU book_viewl；ERROR 1146：Table,db_book.book_uiewl,doesnt exist图7-17查看视图是否删除成功成结果显示，视图book_viewl已经不存在了，说明DROP VI EW语句删除视图成功。7.4综合实例一一使用视图查询学生信息表在实际项目开发过程中的数据表中可能有很多的字段，但某个模块可能只需要其中的几个。为 了提高查询速度和简化操作，可以将该模块需要的字段单独提取出来放在某个视图中，例如本实例 涉及到学生表和成绩表，在建立的视图中只含有与学生成绩有关的字段，如图7-18所示。三二)ysql create uiew scoreinfo as select sno，sname,yw,wy,sx from tb-studenttb-sco e where tb_student.id=tb_score.sid；(uery OK,0 rows affected iysql select*from score inf o；sno a snane i yw i wy i sx!0312315!0312316:031231?!0312318华星全林 小星T 刘金4 6 5 89 7 7?8 0 6 68 6 5 70 5 0 66 8 9 8rows in set 图7-18创建视图运行本实例，通过M ySQL视图查询学生成绩信息，运行结果如图7-19所示，查询结果显示的内 容即为视图中所有字段的内容。I-JT*视图的应用。学号姓名语文成绩外语成绩数学成绩0312315刘小华8860940312316金星星6085760312317黄小全5690750312318李小林768678图7-19学生成绩列表实现过程如下所示：(1)创建视图scoreinfo,通过该视图显示学生成绩信息，该视图的创建代码如下所示：create view scoreinfo as select sno,sname,yw,wy,sx from tb_student,tb_score where tb_student.id=tb_score.sid(2)建立数据库连接文件corm,php实现与M ySQL数源库的连接，并设置数据库字符集为UTF-8,代码 如下：query(set names utf8);设置编码格式?(3)查询视图scoreinfo中的内容，并显示查询结果，代码如下：query(select*from scoreinfo);/萩行查询$info=$sql-fetch_array(MYSQLI_ASSOC);获得查询结果集if($info=NULL)判断是否查询到成绩信息echo”暂无学生信息；else do 通过循环打印学生成绩信息?fetch_array(MYSQLI_ASSOC);?知销点梃落(1)视图由数据库中的一个表或多个表导出的虚拟表。.(2)定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。(3)创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多.张表中。(4)查看视图必须要有SHOW VI EW的权限。查看视图的方法主要包括DESCRI BE语句、SHOW TABL E.STATUS语句、SHOW CREATE VI EW语句等。如何查看用户是否具有创建视图的权限?如何更新视图？创建视图时应注意什么？1.2.3.灯敢