视图的概念
视图是一张虚表,将查询结果集保存起来,作为视图使用。实际存在的表叫作基本表。
视图的作用
- 安全性。grant授权用户只操作视图、只读,可以保护基本表中的数据。
- 提高查询性能。视图只是基本表的一部分,查视图比查全表快。尤其是多表查询的时候,查视图一张表比连接多张表查询要快。
视图的常用操作
#创建视图
create view view_computer_dep as (select * from tb_student where dep_id=1); #把计算机系的学生信息保存为视图
#以后要查询计算机系的学生信息直接从视图中查,肯定比从tb_student全表里查要快。把多表查询的结果集保存为视图一张虚表,查询性能提升更加明显。
#从视图中查数据
select * from view_computer_dep;
#修改视图
create or replace view view_computer_dep as (select id,name from tb_student where dep_id=1); #视图名要相同。会使用新的结果集替换原来的结果集。
#必须要有create or,不能直接replace,语法不允许。
#如果视图不存在,会自动创建
#删除视图
drop view view_computer_dep;
创建视图的完整语法
create [algorithm=merge|temptable|undefined] view view_computer_dep as (select * from tb_student where dep_id=1) [with check option] ;
主要注意一下2个可选参数。
algorithm指定视图的执行机制,有3个可选的值:
(1)merge 合并
合并有2层含义,一是sql语句合并,比如说select * from view_computer_dep 操作视图,执行时会用视图定义替换视图名,实际执行的是select * from (select * from tb_student where dep_id=1);二是操作合并,对视图中的记录可以进行增改删查(实际是对基本表进行增改删查),所以对视图中记录的增改删会同步到基本表。
此种方式不会创建临时表,每次都是操作基本表,并不会提高查询性能。
(2)temptable 临时表
(select * from tb_student where dep_id=1) as view_computer_dep;
select * from view_computer_dep;
把对基本表的查询结果保存为临时表,每次操作的都是临时表。
此种方式可以提高查询性能,但只能对视图进行查询操作,不能进行增改删。
(3)undefined 未定义
缺省此参数时默认就是undefined,由数据库决定是使用merge还是使用temptable,mysql是使用merge。
如果使用merge,还可以设置一个可选参数:with check option 是否检查条件。
创建视图时设置了条件where dep_id=1(过滤基本表),即视图中的记录都是dep_id=1的。
如果设置了with check option,那往视图中插入记录时记录的dep_id必须是1,必须要满足设置的条件,update更新视图中的记录时,dep_id=1这一个字段也不能改。要保证视图中的记录都满足条件。
不可更新的视图
就算使用merge,也不是所有视图中的记录都可以增删改的。
create view view_computer_dep as (select * from tb_student where dep_id=1)
as指定视图的数据来源,如果里面使用了以下任何一种,创建的视图都是不可更新的:
- 聚合函数
- group by子句
- having子句
- distinct关键字
- union运算符
- from来源于多个表或者来源于不可更新的视图
一句话,不是直接来源于一个基本表的,对视图中的记录都只能进行查询操作,不能进行增改删。
比如使用了sum():create view view_computer_dep as (select sum(salary) from tb_employees );
你要update更新视图中的sum这个字段,怎么同步到基本表?同步不了。