MySQL视图-摩杜云开发者社区

视图本质就是将结果集缓存起来
由于结果集是一张虚拟的表, 所以视图也是一张虚拟的表
由于结果集是建立在表的基础上的, 所以视图也是建立在表的基础上的

视图的作用

视图可以用来简化SQL语句
视图可以用来隐藏表的结构
视图可以用来提升数据安全性

创建视图语法

create view 视图名称 as select 语句;

视图数据操作

drop table if exists person;
create table person(
  id int primary key auto_increment,
  name varchar(50),
  city varchar(50)
)

INSERT INTO person VALUES(null, "BNTang", "上海");

SELECT name, city FROM person;

CREATE VIEW person_view as SELECT name, city FROM person;

SELECT * FROM person_view;

INSERT INTO person_view values ('it6666', '武汉');

UPDATE person_view set city= '香港' WHERE name='it6666';

DELETE FROM person_view WHERE name = 'it6666';

注意点

由于视图保存的是结果集, 由于结果集是基于原始表的
所以操作视图中的数据, 本质上操作的是原始表中的数据

修改视图内容

alter view 视图名称 as select 语句;

alter view person_view as select name, score from person;

删除视图

drop view [if exists] 视图名;

视图完整语句

create [algorithm={merge||temptable||undefined}]
view 视图名称
as select 语句
[with check option];

视图算法

merge: 合并式(替代式)算法

将视图的语句和外层的语句合并之后再执行
该算法允许更新数据

SELECT name, city FROM person;

CREATE algorithm=merge VIEW person_view1 AS SELECT name, city FROM person;

SELECT * FROM person_view1;

SELECT * FROM (SELECT name, city FROM person) as t;

temptable: 临时表(具代式)算法

将视图生成一个临时表, 再执行外层的语句
该算法不允许更新数据

CREATE algorithm=temptable VIEW person_view2 AS SELECT name, city FROM person;

SELECT * FROM person_view2;

(SELECT name, city FROM person) as t;

SELECT * FROM t;

undefined: 未定义算法

由MySQL自己决定使用如上的哪一种算法, 默认就是undefined
一般情况下会自动选择merge算法

CREATE VIEW person_view3 AS SELECT name, city FROM person;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `person_view3` AS select `person`.`name` AS `name`,`person`.`city` AS `city` from `person`

视图限制

with check option
在 with check option 的选项下
要保证数据 update 之后也要符合 where 的条件
要保证 insert 之后的数据能被视图查询出来
对于 delete, 有无 with check option 都一样
对于没有 where 字句的视图，使用 with check option 是多余的
默认情况下哪怕插入的数据和更新的数据不符合创建视图条件, 我们也是可以通过视图来插入和更新的

SELECT name, city, score FROM person WHERE score >= 60;

CREATE VIEW person_view AS SELECT name, city, score FROM person WHERE score >= 60;

INSERT INTO person_view values('it6666', '台湾', 33);

UPDATE person_view set score = 55 WHERE name='ww';

如果想让插入和更新的数据必须符合创建视图的条件, 那么就可以在创建视图的时候添加限制条件

SELECT name, city, score FROM person WHERE score >= 60;

CREATE VIEW person_view AS SELECT name, city, score FROM person WHERE score >= 60 with check option;

INSERT INTO person_view values('it6666', '台湾', 33); # 报错, 由于不符合条件

UPDATE person_view set score=55 WHERE name='ww';      # 报错, 由于不符合条件

视图更新限制

如果视图的算法是 merge 算法, 那么可以更新视图
如果没有指 with check option, 那么无论数据符不符合创建视图条件都可以更新
如果指定了 with check option, 那么只有符合创建视图条件才可以更新
除此之外由于视图是一张虚拟表, 视图是基于原始表的, 更新视图的本质就是更新原始表
所以只有原始表中存在的原始数据才可以更新, 通过其它方式生成的数据都不可以更新

SELECT city, avg(score) as avgScore FROM person GROUP BY city;

CREATE VIEW person_view AS SELECT city, avg(score) as avgScore FROM person GROUP BY city;

SELECT * FROM person_view;

UPDATE person_view set avgScore=11 WHERE city='北京';

更新限制

聚合函数
DISTINCT关键字
GROUP BY子句
HAVING 子句
UNION 运算符
FROM 子句包含多张表
SELECT 语句中应用了不可更新的形势图