《数据库原理及MySQL应用（微课版）》课件 第11章视图

第11章

视图教学内容11.1

视图简介11.2

创建视图11.3查看视图11.4修改视图11.5删除视图11.6更新视图>>视图简介

1)视图的概念视图是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。使用视图能够带来许多好处1.视图能简化用户操作2.视图能够对机密数据提供安全保护3.视图提供了一定程度上的数据逻辑独立性2)为什么使用视图创建视图的关键字createview，SQL语法如下所示：create[orreplace][algorithm={undefined|merge|temptable}]viewview_name[(column_list)]asselect_statement[with[cascaded|local]checkoption];>>创建视图【示例11-1】查看root用户的权限示例11-1运行效果图selectselect_priv,create_view_priv,show_view_priv,drop_priv,super_privfrommysql.userwhereuser='root';>>创建视图“select_priv”、“create_view_priv”、“show_view_priv”、“drop_priv”、“super_priv”分别表示SELECT权限、CREATEVIEW权限、SHOWVIEW权限、DROP权限以及SUPER权限；root用户具有以上三种权限【示例11-2】单表上创建view1_emp视图示例11-2运行效果图createviewview1_empasselectempno,ename,job,mgr,hiredate,deptnofromemp;>>创建视图1)单表上创建视图【示例11-3】查询view1_emp视图中的数据示例11-3运行效果图select*fromview1_emp;上述案例可以加深我们对视图的理解：视图就是一条SELECT语句执行后返回的结果集，只不过是使用视图将这条SELECT语句进行了封装，下次再需要进行相同的查询操作时，便可以直接使用该视图，而不需要再次编写相同的SQL语句，方便重用。【示例11-4】多表上创建view2_emp视图【示例11-5】查询view2_emp视图中的数据createviewview2_empasselecte.empno,e.ename,e.job,d.deptno,d.dname,d.locfromempeinnerjoindeptdone.deptno=d.deptno;2)多表上创建视图select*fromview2_emp;【示例11-5】运行结果：【示例11-6】其他视图上创建view3_emp视图【示例11-7】查询view3_emp视图中的数据createviewview3_empasselectempno,ename,job,deptno,dnamefromview2_empwhereloc='NewYork';3)其他视图上创建视图select*fromview3_emp;【示例11-7】运行结果：【示例11-8】使用SHOWTABLES语句查看视图【示例11-8】运行效果：showtables;>>查看视图1)SHOWTABLES语句查看视图SHOWTABLESTATUS的使用语法格式如下所示：【示例11-9】使用SHOWTABLESTATUS语句查看视图showtablestatus[{from|in}db_name][like'pattern'];2)SHOWTABLESTATUS语句查看视图showtablestatuslike'view1_emp';DESCRIBE或者DESC不仅能够查看表的设计信息，还能查看视图的设计信息，其具体的语法格式如下所示：或者可以使用简写的方式，如下所示：describeview_name;3)DESCRIBE语句查看视图descview_name;【示例11-10】使用DESCRIBE语句查看视图或者：describeview1_emp;descview1_emp;使用SHOWCREATEVIEW语句，语法格式如下所示：【示例11-11】使用SHOWCREATEVIEW语句查看视图showcreateviewview_name;4)SHOWCREATEVIEW语句查看视图showcreateviewview1_emp;【示例11-12】在views表中查看视图【示例11-12】运行效果：select*frominformation_schema.viewswheretable_name='view1_emp';5)在views表中查看视图【示例11-13】使用CREATEORREPLACE语句修改视图【示例11-13】运行效果：createorreplaceviewview1_empasselectename,job,mgr,hiredate,deptnofromemp;>>修改视图1)CREATEORREPLACE语句修改视图【示例11-14】使用DESC查看view1_emp视图中的字段【示例11-14】运行效果：descview1_emp;ALTER语句修改视图的SQL语法如下所示：alter[algorithm={undefined|merge|temptable}]viewview_name[(column_list)]asselect_statement[with[cascaded|local]checkoption];2)ALTERVIEW语句修改视图【示例11-15】使用ALTERVIEW语句修改视图alterviewview1_empasselectename,job,mgr,deptnofromemp;【示例11-16】使用DESC再次查看view1_emp视图中的字段descview1_emp;DROPVIEW语句删除视图的SQL语法如下所示：dropview[ifexists]view_name1[,view_name2]...>>删除视图【示例11-17】使用DROPVIEW语句删除视图【示例11-18】使用SHOWTABLES语句查看视图是否删除成功dropviewview2_emp,view3_emp;showtables;【示例11-18】运行结果：数据库中已经不存在view2_emp和view3_emp两张视图，说明其已经被成功删除。【示例11-19】使用INSERT语句更新视图【示例11-20】使用SELECT语句查看view1_emp视图中是否存在添加的数据insertintoview1_empvalues(8000,'Tom','analyst',7566,'1982-10-12',20);>>更新视图1)使用INSERT语句更新视图select*fromview1_empwheredeptno=20;【示例11-20】执行结果【示例11-21】使用SELECT语句查看emp表中是否存在添加的数据【示例11-21】执行结果：select*fromwheredeptno=20;示例20和示例21查询结果，说明使用INSERT语句更新视图的操作实际上影响的是创建视图的基本表。【示例11-22】使用DELETE语句更新视图【示例11-22】执行结果：deletefromview1_empwhereename='Tom';2)使用DELETE语句更新视图【示例11-23】使用SELECT语句查看view1_emp视图中是否已经删除数据【示例11-23】执行结果：select*fromview1_empwheredeptno=20;【示例11-24】使用SELECT语句查看emp表中是否已经删除数据【示例11-24】执行结果：select*fromwheredeptno=20;示例23和示例24查询结果显示，使用DELETE语句更新视图的操作实际上影响的也是创建视图的基本表。【示例11-25】使用UPDATE语句更新视图【示例11-25】执行结果：updateview1_empsetjob='analyst'whereename='Smith';3)使用UPDATE语句更新视图【示例11-26】使用SELECT语句查看view1_emp视图中是否已经修改数据【示例11-26】执行结果：select*fromview1_empwhereename='Smith';【示例11-27】使用SELECT语句查看emp表中是否已经修改数据【示例11-27】执行结果：select*fromempwhereename='Smith';使用UPDATE语句更新视图操作实际上影响的也是创建视图的基本表。虽然使用INSERT、DELETE以及UPDATE语句可以实现视图的更新操作，但并不是所有的视图都可以执行更新操作，因为视图的更新操作具有很多限制条件，当视图中包含如下所示的一种或者多种情况时视图是不可以更新的：4)更新视图时的限制条件(1)视图中包含多行函数，如SUM()、MIN()、MAX()、COUNT()等。【示例11-28】不可更新视图—使用COUNT()函数【示例11-28】执行结果：createviewview4_empasselectcount(*)fromemp;【示例11-29】不可更新视图—验证view4_emp视图是否能被更新【示例11-29】执行结果：updateview4_empsettotal_num=10wheretotal_num=14;(2)视图中包含DISTINCT、GROUPBY、HAVING、UNION或者UNIONALL关键字。【示例11-30】不可更新视图—使用GROUPBY关键字【示例11-30】执行结果：createviewview5_empasselectdeptnofromempgroupbydeptno;【示例11-31】不可更新视图—验证view5_emp视图是否能被更新【示例11-31】执行结果：deletefromview5_empwheredeptno=10;(3)视图中的SELECT语句包含子查询。【示例11-32】不可更新视图—在SELECT语句中使用子查询【示例11-32】执行结果：createviewview6_empasselectename,job,deptnofromempwheresal>(selectsalfromempwhereename='Clark');【示例11-33】不可更新视图—验证view6_emp视图是否能被更新【示例11-33】执行结果：deletefromview6_empwhereename='Jones';(4)视图引用的只是文字值（也成为常量视图，这种情况下，根本没有要更新的基础表）。【示例11-34】不可更新视图—常量视图【示例11-34】执行结果：createviewview7_empasselectpi()pi;【示例11-35】不可更新视图—验证view7_emp视图是否能被更新【示例11-35】执行结果：deletefromview7_empwherepilike3.141593;(5)视图是根据不可更新视图构建的。【示例11-36】不可更新视图—使用不可更新视图构建新视图【示例11-36】执行结果：createviewview8_empasselect*fromview4_emp;【示例11-37】不可更新视图—验证view8_emp视图是否能被更新【示例11-37】执行结果：updateview8_empsettotal_num=10wheretotal_num=14;(6)创建视图时指定了ALGORITHM=TEMPTABLE。【示例11-38】不可更新视图—ALGORITHM=TEMPTABLE【示例11-38】执行结果：createalgorithm=temptableviewview9_empasselectempno,ename,job,mgr,hiredate,deptnofromemp;【示例11-39】不可更新视图—验证view9_emp视图是否能被更新【示例11-39】执行结果：deletefromview9_empwhereename='Jones';（7）如果视图是连接视图（在创建视图时使用了JOIN），那么在更新视图时需要谨慎处理。1)首先创建测试表和视图，其SQL语句如下所示：createtablet1(xinteger);createtablet2(cinteger);createviewvmatasselectsum(x)assfromt1;createviewvupasselect*fromt2;createviewvjoinasselect*fromvmatjoinvuponvmat.s=vup.c;2)INSERT更新视图的注意事项：如果要更新的视图是连接视图，那么必须保证组成该视图的所有组件都是可更新的。insertintovjoin(c)values(1);系统会提示错误，因为组成vjoin视图的两个视图中的vmat视图是不可更新视图。3)UPDATE更新视图的注意事项：如果要更新的视图是连接视图，那么至少要保证组成该视图的一个组件必须是可更新的（这与INSERT不同）。updatevjoinsetc=c+1;如下所示的SQL语句是正确的，因为字段c来自于连接视图中的可更新视图。updatevjoinsetx=x+1;但是下面的SQL语句系统会提示错误，因为字段x来自于连接视图中的不可更新视图。4)DELETE更新视图的注意事项：如果要更新的视图是连接视图，那么不允许进行删除操作（这与INSERT和UPDATE不同）。deletevjoinwhere...;如下所示的SQL语句（伪代码）统会提示错误