Loading... <img class=" ls-is-cached lazyloaded" title="1480776820928760.jpg" src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" alt="mysql.jpg" data-src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" style=""> 这篇文章总结了mysql中常用的视图管理语句。 在本博客中,”mysql”是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。 mysql文章列表直达链接:<a href="https://www.zsythink.net/archives/tag/mysql/" target="_blank" rel="noopener">mysql知识点总结</a> <h2 id="wznav_0">视图的概念</h2> 视图是一个”虚表”,用大白话说,就是从已经存在的表的全部字段或数据中,挑选出来一部分字段或数据,组成另一张”并不存在的表”,这张虚表被称之”视图”,视图中的字段与对应的数据均来自已经存在的表,对于视图来说,这些已经存在的表就被称为”基表”,基表可以是一张表,也可以是多张表, 视图的本质可以理解为一条查询语句,视图中显示的结果,就是这条查询语句查询出的结果。 使用视图的理由 这个时候我们可能会有一个问题,既然视图中的字段均来自”基表”,为什么还要使用”视图”,我们直接使用”基表”就行了,干嘛还要用视图,这是有原因的,我们可以从以下几个方面理解。 1,简化操作 我们说过,视图中的数据可能来自于多张基表,如果基表有很多,而且基表之间存在复杂的关系,当我们需要找出某些数据时,可能需要执行一条复杂的sql语句,如果我们把这个复杂的sql语句创建为视图,我们就可以直接从视图的结果中使用简单的sql语句查询出需要的结果,这使我们容易理解和使用。 2,安全原因 安全原因往往是使用视图的主要原因,我们能通过视图,限制用户能够查看到的数据,比如我们创建了一张”供货商信息表”,这张表里面存放了公司20个大区所有供货商的数据,如果我授权了数据库用户A对这张表有查看权限,那么A用户将有权利查看”供货商信息表”中所有供货商的信息,如果想要限制A用户,让其只能查看前三个大区的供货商信息,我们就可以使用视图,将前三个大区中的供货商信息查询出来,将对应sql语句创建成”前三大区供货商视图”,然后授权A用户只能查看”前三大区供货商视图”,但是不能查看”供货商信息表”,那么数据库用户A则只能查看到前三个大区供货商的信息了,而不是能够看到所有供货商的信息,这就是我们之前所说的,视图是一张虚表,它的字段或者数据可能只是其他表中的一部分。 不使用视图的理由 mysql对于视图的优化并不完善,这样说并不准确,准确的说,应该是mysql对于子查询的优化不是很好,而使用视图本身往往就意味着使用子查询,所以,如果我们必须使用视图时,最好将视图中的sql语句尽量优化,或者说,数据量大的时候尽量避免使用视图。 <h2 id="wznav_1">创建视图</h2> 在创建视图之前,请先确定当前登录的数据库用户是否拥有创建视图的权限。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_1.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_1.png" style=""> 查询结果中,create_view_priv的值为Y,表示当前用户拥有创建视图的权利。 我们来创建一个最简单的视图 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_2.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_2.png" style=""> 我们可以把上图中创建视图的语句分成两个部分来看。 create view testvi as 当做第一部分,这部分语句实现的功能就是创建一个名叫testvi的视图,这个视图中的内容从as 后面的查询语句中获得。 select * from classes where classid <= 3;为上图中语句的第二部分,这部分语句的功能就是从classes表中查询出了classid号小于等于3的数据。 这两个部分结合在一起,就表示,我们将查询语句查询出的结果创建为一个名叫testvi的视图。 注意,视图是一种”虚表”,所以不能与已经存在的表重名。 此处我们先查询出classes表中的所有内容,方便与视图中的内容做对比。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_3.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_3.png" style=""> 视图创建完了,我们来查看一下视图中的内容,视图是一种”虚表”,我们查看视图就像查看表一样即可。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_4.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_4.png" style=""> 可以看到,即使我们查询出了testvi视图中的所有信息,也只有3条数据,这3条数据都来自于classes表,还记得我们在概念中提到的”安全原因”吗,这种场景就跟”安全原因”中提到的场景相同。 我们也可以使用如下语句创建视图 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">create or replace view testvi as select </span><span class="enlighter-g0">*</span><span class="enlighter-text"> from classes where classid </span><span class="enlighter-g1"><</span><span class="enlighter-text">= </span><span class="enlighter-n1">4</span><span class="enlighter-text">;</span></div> </div> </div> </div> create or replace view testvi as 表示,如果testvi这个视图如果不存在,那么则按照指定的查询语句创建视图,如果当下已经存在testvi这个视图,那么则使用当前视图覆盖之前的testvi视图,以当前的sql查询语句作为视图的语句。 我们发现,视图创建后,视图中的字段名与”基表”中的字段名称相同,我们也可以让视图使用自己的字段名,而不是使用基表中的字段名称,我们可以在创建视图时使用如下语句。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_5.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_5.png" style=""> 我们在概念中提到过,视图是由其他表中的一部分数据或者字段组成的,我们也可以只查询出”基表”中的部分字段,组成视图,示例如下 <img class=" lazyloading" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_6.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_6.png" style=""> 从上述的举例中我们可以看出,创建视图是很简单的,视图中的内容主要取决于我们的查询sql写成什么样。 所以,我们也可以通过sql语句,从多个表中查询出数据,创建视图。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_7.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_7.png" style=""> 其实在创建视图时,还可以指定mysql处理视图的算法,算法会影响MySQL处理视图的方式,mysql可以使用两种算法处理视图,这两种算法为MERGE算法和TEMPTABLE算法,我们可以在创建视图时,使用ALGORITHM指定使用哪种算法处理当前视图,ALGORITHM的值可以设置为MERGE、TEMPTABLE、UNDEFINED。如果没有ALGORITHM子句,默认值为UNDEFINED(表示用户未指定固定的算法,mysql会自动从MERGE、TEMPTABLE中选择一个算法处理视图),那么这些算法有什么不同呢,我们后面再解释,先看看创建时的示例语句。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_8.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_8.png" style=""> 上述语句表示指定使用merge算法处理视图。 那么,每种算法有什么不同呢。 1、MERGE算法:视图使用这种算法时,如果我们调用了视图,mysql会先将视图的定义转换成sql语句,然后把视图的sql语句与我们调用的语句整合,最后执行整合完毕的sql语句,什么意思呢,就以上图中的创建视图的sql语句为例,如果我们想要查看视图testvi中的所有数据,我们有可能会执行select * from testvi这条sql语句 ,如果testvi使用的是merge算法,那么mysql会先将执行的语句变成 select * from (select name,age from students;) ,然后执行最终改变过的语句,这就是merge算法对视图的处理过程,如果使用这种算法,即使定义视图时的sql语句不包含子查询,在我们调用视图的时候,最终执行的sql语句本身就一定包含子查询,而mysql对子查询的优化是不够的,当数据量大时,往往会影响性能,这就是不使用视图的理由。 2、TEMPTABLE算法:视图的结果将被置于临时表中,然后使用它执行语句,什么意思呢,还是以上图中的testvi视图为例,如果把上图中的merge改成temptable,当我们执行 select * from testvi时,mysql会先查询到会先将select name,age from students;的查询结果放置到临时表中,然后用户的语句再调用临时表中的数据,当视图使用这种算法时,视图只能用于查询,不能用于更新数据。 3、UNDEFINED:当algorithm设置为此值时,MySQL将选择所要使用的算法。如果可能,它倾向于MERGE而不是TEMPTABLE,这是因为MERGE通常更有效,而且如果使用了临时表,视图是不可更新的。 其实在创建视图的时候,还能通过 CHECK OPTION 定义视图的更新特性,但是由于使用较少,如果需要了解可以查看mysql官方文档。 <h2 id="wznav_2">删除视图</h2> 删除视图很简单,使用如下语句删除testvi视图 <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">drop view testvi;</span></div> </div> </div> </div> 如下语句表示如果视图testvi存在,则删除; <div class="enlighter-default enlighter-v-standard enlighter-t-enlighter enlighter-hover enlighter-overflow-scroll"> <div class="enlighter-toolbar"> <div class="enlighter-btn enlighter-btn-raw"></div> <div class="enlighter-btn enlighter-btn-copy"></div> <div class="enlighter-btn enlighter-btn-window"></div> </div> <div class="enlighter"> <div class=""> <div><span class="enlighter-text">drop view </span><span class="enlighter-k1">if</span><span class="enlighter-text"> exists testvi;</span></div> </div> </div> </div> <h2 id="wznav_3">查看视图</h2> 我们可以查看数据库中存在哪些视图,也可以查看视图的结构,还可以查看视图中的内容,我们一个一个来。 首先,如果我们想要查看某一数据库中的所有视图,可以使用如下语句查看: select * from information_schema.views where table_schema=’U_DB_NAME’; 示例如下: <img class=" lazyloading" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_9.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_9.png" style=""> 我们也可以查看视图的结构,就像查看表的结构一样; <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_10.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_10.png" style=""> 我们也可以查看视图中的数据内容,就像查看表一样; <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_11.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_11.png" style=""> <h2 id="wznav_4">修改视图</h2> 修改视图的语句如下: <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_12.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/010717_1738_12.png" style=""> <h2 id="wznav_5">更新视图中的数据</h2> 在大部分情况下,创建视图是为了查看数据的,很少会去更新视图中的数据,即使想要去更新视图中的数据,也需要满足一定的条件,不是所有视图中的的数据都是可更新的。 如果视图包含下述结构中的情况,那么它将是不可更新的: · 当视图设定了 ALGORITHM = TEMPTABLE。 · 聚合函数(SUM(), MIN(), MAX(), COUNT()等)。 · 位于选择列表中的子查询。 · FROM子句中存在不可更新视图。 · WHERE子句中的子查询,引用FROM子句中的表。 · 仅引用文字值(此时没有要更新的基表)。 · 基表中的其他字段中的约束不被满足。 · DISTINCT · GROUP BY · HAVING · UNION 或 UNION ALL · Join 注意:视图中虽然可以更新数据,但是有很多的限制。所以,一般我们只在查询时使用视图,而不要通过视图更新数据。 转载自朱双印日志https://www.zsythink.net/archives/960 Last modification:May 29, 2024 © Allow specification reprint Like 如果觉得我的文章对你有用,请随意赞赏