MySQL：视图

最新推荐文章于 2020-06-15 15:56:44 发布

阅读量456

点赞数

分类专栏： # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45730091/article/details/103788403

版权

MySQL 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

视图概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的定义

视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影。

同一张表可以创建多个视图。

创建视图的语法： create view view_name as select 语句

视图名跟表名是一个级别的名字，隶属于数据库；
该语句的含义可以理解为：就是将该select命名为该名字（视图名）；
视图也可以设定自己的字段名，而不是select语句本身的字段名——通常不设置。
视图的使用，几乎跟表一样！

视图的作用

可以简化查询

案例1：查询平均价格前3高的栏目。

传统的sql语句写法

select  cat_id,avg(shop_price) as pj from ecs_goods group by cat_id  order by pj desc  limit 3;

创建一个视图，简化查询。

create view  ecs_goods_v1  as select  cat_id,avg(shop_price) pj from ecs_goods group by cat_id;

查询平均价格前3高的栏目,我们只需查询视图即可。

select * from ecs_goods_v1 order by pj desc  limit 3;

案例2:查询出商品表，以及所在的栏目名称；

传统写法：

select goods_id,goods_name,b.cat_name,shop_price  from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;

创建一个视图

create view ecs_goods_v2 as select goods_id,goods_name,b.cat_name,shop_price  from ecs_goods a left join ecs_category b on a.cat_id=b.cat_id;

查询视图；

select * from ecs_goods_v2;

可以进行权限控制

把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据，比如某张表，用户表为例，2个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是呢，又不想开放用户表中的密码字段。

再比如一个goods表，两个网站搞合作，可以相互查询对方的商品表，比如进货价格字段不能让对方查看。
在这里插入图片描述
第一步：创建一个视图，视图中不能包含in_price字段。

create view goods_v1 as select id,goods_name,shop_price  from goods;

第二步：创建一个用户，授予查询权限，只能操作goods_v1表（视图）

grant select on goods_v1  to ‘user1’@’%’ identified by ‘123456’;

查询视图

语法：select * from 视图名称 [where 条件]
视图和表一样，可以添加where 条件

查询视图的定义

show create 视图名称

修改视图

alter view view_name as select XXXX

删除视图

drop view 视图名称

查看视图结构

和表一样的，语法：desc 视图名称

查看所有视图

和表一样，语法：show tables;
注意：没有show views语句；

视图与表的关系

视图是表的查询结果，自然表的数据改变了，影响视图的结果。

（1）视图的数据与表的数据一一对应时，可以修改。

在这里插入图片描述

（2）视图增删该也会影响表，但是视图并不是总是能增删该的。

create view lmj as select cat_id,max(shop_price) as lmj from goods group by cat_id;

update lmj set lmj=1000 where cat_id=4;

报错：ERROR 1288 (HY000): The target table lmj of the UPDATE is not updatable

（3）对于视图insert还应注意，视图必须包含表中没有默认值的列。

在这里插入图片描述

以上对user_v1的操作，就类似于，如下sql语句的操作，原因就是age字段没有默认值

create table user(
    id int not null,
    name varchar(32) not null,
    age tinyint not null 
)engine myisam charset utf8;
insert into user values(12,'xiaofan');

注意：一般来说，视图只是用来查询的，不应该执行增删改的操作。

视图算法

merge： 当引用视图时，引用视图的语句与定义视图的语句合并（默认）。
意味着，视图只是一个语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析，形成一条 select语句。
temptable： 当引用视图时，根据视图的创建语句建立一个临时表。
根据创建语句瞬间创建一张临时表，然后查询视图的语句，从该临时表查数据。
undefined： 未定义，自动让系统帮你选。

比如如下视图的算法为 merge
在创建视图时的语句：where shop_price>1000;
查询视图时的语句：where shop_price<3000;
那么查此视图时，真正发生的是where (select where) and (view where)
where shop_price <3000 and shop_price >1000;
分析出最终语句还是去查goods表