视图view,是一种有结构(有行有列)但是没结果(结构中不真实存放数据)的虚拟表。虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图的数据来源)。
一、视图定义
视图是由查询结果形成的一张虚拟表,是表通过某种运算得到的一个投影,同一张表可以创建多个视图。
create view 视图名 as select 语句
- 视图名跟表名是一个级别的名字,隶属于数据库。
- 可以理解为就是将该select语句命名为视图名。
- 视图也可以设定自己的字段名,而不是select语句本身的字段名。
- 视图的使用,几乎跟表一样。
二、视图作用
1.简化查询
--案例:查询平均价格前3高的栏目。
--传统的sql语句
select cat_id,avg(shop_price) as pj from goods group by cat_id order by pj desc limit 3;
--创建一个视图,简化查询
create view goods_v1 as select cat_id,avg(shop_price) pj from goods group by cat_id;
select * from goods_v1;
select * from goods_v1 order by pj desc limit 3;
--案例:查询出商品表,以及所在的栏目名称;
--传统sql
select goods_id,goods_name,b.cat_name,shop_price from goods a left join category b on a.cat_id = b.cat_id;
--创建视图
create view goods_v2 as select goods_id,goods_name,b.cat_name,shop_price from goods a left join category b on a.cat_id = b.cat_id;
select * from goods_v2;
2.权限控制
把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据。
比如以某张用户表为例,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 'someone'@'%' identified by '1234456';
--第三步:someone用户查询
select * from goods_v1;
三、视图操作
1.创建视图
create view 视图名 as select语句;
------ select语句可以是普通查询 、连接查询、可以是联合查询、可以是子查询。
-- 单表视图
create view view_v1 as select * from student;
-- 多表视图,视图基表有多张的时候,查询出来的字段名不能重复
create view my_v3 as
select s.*,c.c_name,c.room from student as s left join class c on s.c_id = c.id;
2.删除视图
drop view 视图名
drop view goos_v1;
3.修改视图
视图本身不可修改,但是视图的来源可以修改。
alter view 视图名 as 新的select语句;
-- 修改视图
alter view student_v1 as
select id,name,age,sex,height from student;
4.查看视图
指查看视图本身的结构。
- 视图是一张虚拟表,表的所有查看方式都适用于视图:show tables [like]/desc 视图名字/show create table 视图名。
- 视图一旦创建,系统会在视图对应的数据库文件夹下创建一个对应的结构文件: frm文件
--查看视图结构
--desc 视图名称
desc goods_v1;
--查看所有视图,和表一样
show tables;
-- 查看视图创建语句
show create view goods_v1\G
5.视图意义
- 视图可以节省SQL语句,将一条复杂的查询语句使用视图进行保存,以后可以直接对视图进行操作
- 视图操作是主要针对查询的,如果对视图结构进行处理(删除),不会影响基表数据(相对安全)。
- 视图往往是在大项目中使用,而且是多系统使用。可以对外提供有用的数据,但是隐藏关键(无用)的数据。
- 视图可以对外提供友好型,不同的视图提供不同的数据,对外就像专门设计的一样
- 视图可以更好(容易)的进行权限控制
6.视图数据操作
将数据直接在视图上进行操作,但是有很多限制。
新增视图数据:
- 多表视图不能新增数据
- 可以向单表视图插入数据 ,但是视图中包含的字段必须有基表中所有不能为空(或没有默认值)字段
- 视图是可以向基表插入数据
-- 多表视图插入数据,会报错
insert into v3 values(null,'itcast0008','张三丰','男',150,180,1,'PHP0326','D306');
-- 单表视图插入数据: 视图不包含所有不允许为空字段
insert into v1 values(null,'张无忌',68,'男',174,2);
-- 单表视图向基表插入数据
insert into v2 values(2,'PHP0326','D306');
修改视图数据:
with check option,如果对视图在新增的时候限定了某个字段有限制,那么在对视图进行数据更新操作时,系统会进行验证,要保证更新之后,数据依然可以被视图查询出来,否则不让更新。
更新操作和更新表一样。
-- 多表视图更新数据
update v3 set c_id = 3 where id = 5;
-- 视图: age字段限制更新
create view v4 as
select * from student where age > 30 with check option;
-- 表示视图的数据来源都是年龄大于30岁:where age > 30决定
-- with check option,决定通过视图更新的时候,不能将已经得到的数据age > 30的改成小于30的
-- 将视图可以查到的数据改成小于30
update v4 set age = 29 where id = 1;
-- 可以修改数据让视图可以查到,可以改,但是无效果
update v4 set age = 32 where id = 6;
删除视图数据:
多表视图不能删除数据。
delete from 视图名 [where条件]
查询视图数据:
查询视图数据和查询表一样,把视图当作一张表就行。
select * from 视图名 [where 条件]
四、视图与表
视图是表的查询结果,表的数据改变了,会影响视图的结果。
一般来说,视图只是用来查询的,不应该执行增删改的操作。
- 视图的数据与表的数据一一对应时,可以修改。对视图的修改,也会影响到数据库表的修改。
- 视图增删改也会影响表,但是视图并不是总是能增删改的。
- 对于视图insert还应注意,视图必须包含表中没有默认值的列。
五、视图算法
系统对视图以及外部查询视图的select语句的一种解析方式。
- undefined:未定义(默认的),这不是一种实际使用算法,是一种推卸责任的算法。告诉系统,视图没有定义算法,让系统自己看着办。
- temptable:临时表算法,系统应该先执行视图的select语句,后执行外部查询语句。
- merge:合并算法,系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(效率高: 常态)。
在创建视图的时候可以指定算法:
create algorithm = 指定算法 view 视图名 as select语句;
视图算法选择:如果视图的select语句中会包含一个查询子句,而且很有可能顺序比外部的查询语句要靠后,一定要使用算法temptable,其他情况可以不用指定(默认即可)。
-- 指定算法为临时表
create algorithm = temptable view v6 as select * from student order by height desc;