十、视图
1.1 视图概念
视图(View)是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
视图其实就是一个select返回的结果集,用于方便我们查询而创建的"临时表",简化我们的查询语句。
视图的优势:
- 方便: 使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
- 安全: 使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
- 数据独立: 一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;
Tips:使用视图的大部分情况是为了保障数据安全性,提高查询效率;
- 创建测试表:
DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; insert into `class`(`id`,`name`) values (1,'Java'), (2,'UI'), (3,'产品'); DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵六',1), (5,'孙七',3), (6,'王八',3);
1.2 创建视图
1.2.1 视图创建语法
1、完整的创建视图语法:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
2、OR REPLACE: 如图视图名重复,则表示替换原有视图
-- 创建视图 create view test1 as select * from student; -- 创建视图,如果该视图存在那么就覆盖 create or replace view test1 as select * from student;
1.2.2 参数解释
1、ALGORITHM
- ALGORITHM: 表示创建视图时所使用的算法
- UNDEFINED: 默认值,没有指定算法时一般采用merge算法
- MERGE: 合并算法,在基于视图创建新的视图时,将创建旧视图时所使用的select语句与将要创建新的视图的select语句进行合并处理,效率较高。
- TEMPTABLE: 临时表算法,在基于视图创建新的视图时,先执行旧视图的select语句,然后再执行新视图的select语句,查询两次,效率较低。
create view a1 as select * from student; -- 创建视图 create algorithm=merge view a2 as select * from a1; -- 创建视图 create algorithm=temptable view a3 as select * from a1; -- 创建视图
- 需要注意的是,视图创建算法如果是tamptable,那么该视图不允许修改:
2、DEFINER
- **DEFINER:**指出谁是视图的创建者
- **USER:**指定创建视图的用户,
- CURRENT_USER:当前登录的用户为创建视图的用户(默认值)
-- 创建d1视图,创建者默认为当前用户 create view d1 as select * from student; -- 创建用户 create user 'xiaohui'@'localhost' identified by 'admin'; -- 创建视图,指定创建者为xiaohui用户 create definer='xiaohui'@'localhost' view d2 as select * from student; -- 查看视图d1的创建语句 show create view d1; -- 查看视图d2的创建语句 show create view d2;
3、SQL SECURITY
- **SQL SECURITY:**设置操作的视图的安全策略
- **INVOKER:**验证查询的用户是否拥有权限访问视图及视图所引用的对象,也就是即需要拥有对视图的权限,又要拥有视图中锁涉及到的表的权限;
- DEFINER:访问该视图时验证是否拥有对视图本身的权限;(默认值)
create definer='root'@'localhost' sql security invoker view test_01 as -- invoker权限 select * from student; create definer='root'@'localhost' sql security definer view test_02 as -- definer权限 select * from student; -- 创建zhangsan用户 create user 'zhangsan'@'%' identified by 'admin'; -- 赋予访问视图的权限 grant select on db04.test_01 to zhangsan; grant select on db04.test_02 to zhangsan; -- 查看zhangsan用户的权限 show grants for 'zhangsan'@'%';
- 使用zhangsan用户登录另一个窗口执行
mysql> select * from test_01; # 不能访问test_01视图 ERROR 1356 (HY000): View 'test.test_01' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> select * from test_02; # 可以访问test02视图 +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 钱七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 5 rows in set (0.00 sec)
Tips:发现不能访问test_01视图;因为zhangsan用户并没有student表的访问权限;
- 给zhangsan用户赋予访问student的权限:
grant select on test.student to zhangsan; mysql> select * from test_01; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 钱七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 5 rows in set (0.00 sec)
测试完毕删除用户:
drop user 'zhangsan'@'%';
4、WITH
重新插入测试数据:
truncate student; insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵六',1), (5,'孙七',3), (6,'王八',3);
- WITH: 更改视图数据时,对更改的数据进行检查
- 普通检查策略:当更改视图中的数据时,如更改之后的结果不符合当初创建该视图的权限时,则不允许;
- CASCADED检查策略:具备普通检查策略的功能;另外,在创建视图时,如果该视图引用到了其他视图,那么在对该视图进行insert插入时,既要符合当前视图的创建条件,也要符合其他视图的创建条件;需要注意的是,普通检查策略也具备该功能;
- LOCAL检查策略: 具备普通检查策略的功能;另外,在创建视图时,如果该视图引用到了其他视图,那么在对该视图进行insert插入时,只需要符合当前视图的创建条件即可;
【普通检查策略】:
-- 创建一个普通视图 create view v1 as select * from student where class_id=1; -- 创建一个带数据检查的视图 create view v2 as select * from student where class_id=1 with check option; -- 修改v1视图,修改之后的数据已经不符合当初创建v1视图的条件了,但依旧可以修改 update v1 set class_id=2 where id=1; -- 修改v2视图,修改之后的数据已经不符合当初创建v1视图的条件了,修改失败 update v2 set class_id=2 where id=2;
重新创建表:
DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `age` int(11), `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student` values (1,'张三',20,1), (2,'李四',25,1), (3,'王五',30,2), (4,'赵六',35,1), (5,'孙七',40,3), (6,'王八',45,3);
【CASCADED/LOCAL检查策略】:
-- 没有检查策略 create view v1 as select * from student where class_id=1; -- 普通检查策略 create view v2 as select * from v1 where age<=40 with check option; -- local检查策略 create view v3 as select * from v1 where age<=40 with local check option; -- cascaded检查策略 create view v4 as select * from v1 where age<=40 with cascaded check option; -- v1视图没有任何检查策略,任意的insert都可以执行成功 insert into v1 values(100,'aa',80,2); -- v2具备普通检查策略,插入的数据需要符合当初创建v2时的条件,也要符合创建v1的条件 insert into v2 values(300,'aa',20,1); -- 既符合v1,也符合v2,成功 insert into v2 values(400,'aa',20,2); -- 不符合v1的条件,错误 insert into v2 values(400,'aa',50,1); -- 不符合v2的条件,错误 -- v3具备local检查策略,插入的数据需要符合创建自身视图时的条件即可 insert into v3 values(500,'aa',20,1); -- 既符合v1,也符合v3,成功 insert into v3 values(600,'aa',20,2); -- 只符合v3,成功 insert into v3 values(700,'aa',50,1); -- 不符合v3,错误 -- v4具备cascaded检查策略,插入视图的数据需要当初创建v4的条件,也要符合创建v1的条件 insert into v4 values(800,'aa',20,1); -- 既符合v1,也符合v4,成功 insert into v4 values(900,'aa',20,2); -- 不符合v1,错误 insert into v4 values(1000,'aa',50,1); -- 不符合v4,错误
5、小结:
- 对于update/insert,有with check option,要保证update/insert后,数据要被视图查询出来
- 对于delete,有无with check option都一样
- 对于没有where子句的视图,使用with check option是多余的
1.3 修改视图
1.4.1 创建时修改
- 语法
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
- 示例:
create or replace view test2 as select * from student where class_id=1;
1.4.2 使用alter语句更改视图
- 语法
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { USER | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
- 重新插入测试数据:
truncate student; insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵六',1), (5,'孙七',3), (6,'王八',3);
- 示例:
alter view test2 as select * from student where class_id=3; alter view test2 as select id,name from student; alter view test2 as select * from student where name='张三';
1.4.3 更新视图内容
- 示例:
mysql> select * from test2; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | +----+------+----------+ 1 row in set (0.00 sec) mysql> select * from student; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql> update test2 set name='zs' where id=1; # 修改视图,源表和视图都会更新 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test2; Empty set (0.00 sec) mysql> select * from student; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | zs | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql>
- 注意:创建视图时,有下列内容之一,视图将不能做DML操作:
- 视图中包含了源表中的非空列,且非空列没有设置默认值,视图将不支持insert操作
- 在定义视图时,from子句中包含多个表(连接查询),视图将不支持insert和delete操作
- 在定义视图时,SELECT语句后的字段列表中使用了数学表达式或子查询,视图将不支持insert和update操作
- 在定义视图时,SELECT语句后的字段列表中使用 DISTINCT、聚合函数、GROUP BY、HAVING、UNION等,视图将不支持insert、delete、update操作
- 在定义视图时,SELECT语句中包含了子查询,而子查询中引用了from后面的表,视图将不支持insert、update、delete
- 如果视图中有计算列,则不能更新
- 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”,视图将不支持insert和delete操作
1.4 删除视图
删除视图时,只能删除视图的定义,不会删除数据,也就是说不动基表
- 语法
DROP VIEW [IF EXISTS] view_name [, view_name] ...
- 示例:
drop view test1,test2;
1.5 视图的使用
1.5.1查询视图
mysql> select * from test1; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql>
1.5.2 修改视图:
需要注意的是:对视图进行任何的新增、修改、删除将会对直接作用到源表
mysql> update test1 set name='zhangsan' where id=1; # 修改视图中的记录 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test1; # 查看视图 +----+----------+----------+ | id | name | class_id | +----+----------+----------+ | 1 | zhangsan | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+----------+----------+ 6 rows in set (0.00 sec) mysql> select * from student; # 查看源表,发现源表的数据也修改了 +----+----------+----------+ | id | name | class_id | +----+----------+----------+ | 1 | zhangsan | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+----------+----------+ 6 rows in set (0.00 sec) mysql>
1.5.3 删除视图中的记录:
mysql> delete from test1 where id=1; # 删除视图中的数据 Query OK, 1 row affected (0.01 sec) mysql> select * from test1; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 5 rows in set (0.00 sec) mysql> select * from student; # 源表的数据也被删除了 +----+------+----------+ | id | name | class_id | +----+------+----------+ | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 5 rows in set (0.00 sec) mysql>
1.5.4 新增记录到视图:
mysql> insert into test1 values(1,'张三',1); # 在视图中插入一条新的记录 Query OK, 1 row affected (0.01 sec) mysql> select * from test1; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql> select * from student; # 源表中也新增了一条记录 +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql>
Tips:视图实质上是对源表的引用,对视图的任何操作都会落实到对应的表,如insert、update、delete等语句,drop删除视图不会影响源表,但源表如果被删除了(drop),那么视图将变得不可用。同样,对源表进行任何的insert、update、delete等语句,对应的视图上的记录也会发生变化;
mysql> select * from test1; # 查询test1视图中的记录 +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 6 | 王八 | 3 | +----+------+----------+ 6 rows in set (0.00 sec) mysql> update student set name='zs' where id=1; # 对student(源表)进行修改 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into student values(100,'Test',3); # 对student(源表)进行插入 Query OK, 1 row affected (0.00 sec) mysql> delete from student where id=6; # 对student(源表)进行删除 Query OK, 1 row affected (0.00 sec) mysql> select * from test1; # 查询test1视图 +-----+------+----------+ | id | name | class_id | +-----+------+----------+ | 1 | zs | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵六 | 1 | | 5 | 孙七 | 3 | | 100 | Test | 3 | +-----+------+----------+ 6 rows in set (0.00 sec) mysql>