1.视图的概念和使用
视图是从一个或多个表中导出来的表,它是一种虚拟存在的表,表的结构和数据都依赖基本表。通过视图不仅可以看到存放在基本表中的数据,还可以对数据进行查询、添加、删除和修改。
#选择数据库shop
use shop;
#创建view_goods视图
create view view_goods as select id,name,price,price*0.8 p from sh_goods limit 3;
#查询视图
select * from view_goods;
#删除视图
drop view view_goods;
或者也可以写成下面:
#在创建视图的SELECT语句中移除LIMIT子句
create view view_goods as select id,name,price,price * 0.8 p from sh_goods;
#在查询视图时使用LIMIT子句
select * from view_goods limit 3;
视图的优点:
A.简化查询语句
B.安全性
C.逻辑数据独立性
2.注意:
(1)在默认情况下,新创建的视图保存在当前选择的数据库中。若要明确指定在某个数据库中创建视图,在创建时应将名称指定为“数据库名.视图名”。
(2)在show tables的查询结果中会包含已经创建的视图。
(3)在同一个数据库中,视图名称和已存在的表名称不能相同。
(4)or replace子句,必须具有视图的drop权限。
3.视图管理
(1)创建视图
在多表上创建视图(在sh_goods表和sh_goods_category表上创建视图)
#创建视图
create view view_goods_cate as
select g.id,g.name,c.name category_name from sh_goods g
left join sh_goods_category c
on g.category_id=c.id;
#查询视图
select * from view_goods_cate limit 3;
(2)自定义列表名称
#创建视图
create view view_goods_promo(sn,title,promotion_price)as
select id,name,price*0.8 from sh_goods;
#查看视图
select * from view_goods_promo where sn<=3;
在创建视图时,自定义列表名称于as后select字段列表的顺序是一致。自定义列表名称的数量必须于select字段列表的数量一致,若不一致,MySQL会报错,视图将无法创建。
(3)视图的安全控制
在创建视图时指定DEFINER和SQL SECURITY可以控制视图的安全。
#创建测试用户shop_test
create user shop_test;
#创建第1个视图,权限控制使用默认值
create view view_goods_t1 as select id,name from sh_goods limit 1;
#创建第2个视图,设置definer为shop_test用户
create definer=‘shop_test’ view view_goods_t2 as select id,name from sh_goods limit 1;
#创建第3个视图,设置SQL SECURITY为INVOKER
create sql security invoker view view_goods_t3 as select id,name from sh_goods limit 1;
#为shop_test用户赋予前面创建的3个视图的select权限
grant select on view_goods_t1 to ‘shop_test’;
grant select on view_goods_t2 to ‘shop_test’;
grant select on view_goods_t3 to ‘shop_test’;
接下来重新打开一个命令窗口,通过mysql -ushop_test登录shop_test用户,然后执行如下操作来测试视图是否可用
use shop;
#第1个视图的definer为root,该用户有sh_goods表的select权限
select * from view_goods_t1;
#第2个视图的definer为shop_test,该用户没有sh_goods表的select权限(执行会报错)
select * from view_goods_t2;
#第3个视图的SQL SECURITY为INVOKER,
#会判断当前用户shop_test有无sh_goods表的select权限(执行会报错)
select * from view_goods_t3;
4.查看视图
查看视图是指查看数据库中已经存在的视图的定义。查看视图必须要有哦SHOW VIEW的权限。
(1)查看视图的字段信息
desc view_goods_cate;
(2)查看视图的状态信息
show table status like ‘view_goods_cate’\G
(3)查看视图的创建语句
show create view view_goods_cate\G
5.修改视图
修改视图是指修改数据库中存在的视图的定义。
(1)替换已有视图
#创建视图
create view view_goods as select id,name,price from sh_goods;
#修改已有视图
create or replace view view_goods as select id,name from sh_goods;
#查看修改结果
desc view_goods;
(2)使用alter view语句修改视图
#修改视图
alter view view_goods as select id from sh_goods;
#查看修改结果
desc view_goods;
6.删除视图
#删除视图
drop view view_goods;
7.视图数据操作
因为视图是一个虚拟表,不保存数据,当通过视图操作数据时,实际操作的是基本表中的数据。
(1)添加数据
#创建视图
create view view_category as select id,name from sh_goods_category;
#添加数据
insert into view_category values(17,‘图书’);
#查询添加后的数据
select id,name from sh_goods_category where id=17;
(2)修改数据
#修改数据
update view_category set name =‘家电’ where id=17;
#查询修改后的数据
select id,name from sh_goods_category where id=17;
(3)删除数据
#删除数据
delete from view_category where id=17;
#查询数据是否已删除
select id,name from sh_goods_category where id=17;