一、视图概述
- 视图是虚拟的表。在视图创建之后,可以用与表基本相同的方式利用它们。甚至可以添加和更新数据(添加和更新数据存在某些限制,下面介绍)
- 重要的是视图仅仅是用来查询存储在别处的数据的一种设置。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。所以在添加和更改表数据的之后,视图返回的是改变之后的数据
- 视图的使用另外可参阅文章:
视图的规则和限制
- 与表一样,视图必须唯一的命名(不能给视图取与别的视图或表相同的名字)。
- 对于可以创建的视图数目没有限制。
- 为了创建视图,必须具有足够的访问权限。这些权限通常由数据管理人员授予。
- 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造另一个视图。
- order by可以用在视图中,但如果从该视图检索数据select也含有order by,那么该视图中的order by将被覆盖。
- 视图不能索引,也不能有关联的触发器或默认值。
- 视图可以和表一起使用。例如,编写一条连接表和视图的select语句。
为什么使用视图
- 重用SQL语句。
- 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
- 使用表的组成部分而不是整个表。
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
性能问题
- 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。
- 如果你用多个连接和过滤建立了复杂的视图或者嵌套了视图,可能会发现性能下降的很厉害。
- 因此,在部署使用了大量视图的应用前,应该进行测试。
二、视图的创建
CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名 [{属性清单}]
AS SELECT 语句 [WITH [CASCADED|LOCAL] CHECK OPTION];
ALGORITHM:选择的算法 | UNDEFINED:表示MySQL自动选择所需使用的算法 MERGE:表示将视图的语句与视图的定义合并,使得视图定义的某一部分取代语句的对应部分 TEMPTABLE:将视图的结果存入临时表 |
WITH CHECK OPTION:表示更新视图时要保证在该视图的权限范围之内 | CASCADED:表示更新视图时要满足所有相关视图和表的条件 LOCAL:表示更新视图时,要满足该视图本身的定义的条件即可 |
- 创建视图的时候,必须拥有对视图的CREATE VIEW权限,拥有对SELECT语句所选列的操作权限,以及拥有对该语句里其他地方所引用的所有列的SELECT权限。
- 例如:
CREATE VIEW reader_view1 AS SELECT * FROM reader;
- 例如:
CREATE VIEW reader_view2 AS SELECT rno,rname from reader;
在默认情况下,视图里的列名与其SELECT语句里列出的输出列名相同。如果想要显式地改用其他的列名,那么需要在视图定义里,紧跟视图名字的后面用括号列出那些新名字。例如:
CREATE VIEW reader_view2(no,name) AS SELECT rno,rname from reader;
- 当显式设置名字之后,在查询视图时必须使用视图定义时的字段名字。
三、视图的使用
- 方法:当做表使用查询即可。
- 注意:在使用视图时,只能引用在该视图定义里列出的那些列。也就是说,即使某个列属于基表(basetable),但它未列在视图定义里,你也不能引用它。
演示案例
SELECT * FROM reader_view1;SELECT * FROM reader_view2 WHERE rno='R10002';
使用视图与计算字段
- 视图对于简化计算字段的使用特别有用。
- 下面检索某个特定订单中的物品,计算每种物品的总价格。
select prod_id,quantity,item_price,quantity*item_price as expanded_price
from orderitems
where order_num=20005;
- 现在先定义一个视图(不指定订单编号)
create view orderitemsexpanded
as select order_num,prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems;
- 然后检索订单20005的详细内容
select * from orderitemsexpanded where order_num=20005;
三、视图的删除
- 视图的删除,只是删除视图的定义,但是表中的数据仍然存在。
- 视图不存在,删除也不会报错。
格式:
drop view [if exists] viewname1,viewname2...;
- 例如:
drop view reader_view3;
四、视图结构的查看
desc
describe view_name;
desc view_name;
SHOW TABLE STATUS
- 例如:
show table status like 'reader_view1'\G
SHOW CREATE VIEW
show create view view_name;
INFORMATION_SCHEMA
- 查看information_schema数据库下的views表。
- 例如:
select * from information_schema.views where table_name='view_name';
五、视图结构的修改
- 当表的结构发生变化时,可以修改视图来及时更新视图与表之间的一致性
方式一
- 通过create or replace view修改(如果没有则创建,有则修改)。
- 格式:
create or replace [algorithm = {undefined | merge | temptable}] view 视图名 as select查询语句;
方式二
- 通过alter修改(只能修改不能创建)
- 格式:
alter view 视图名 as select查询语句;
六、视图数据的更新
- 重点:视图是可更新的(即insert、update、delete)。对视图的更新就是对基表的更新。
- 如果一个视图所引用的SELECT语句由下面格式组成,则不能对视图进行更新:
- 分组(使用group by和having)。
- 连接。
- 子查询。
- 并。
- 聚合函数(min、count、sum等)。
- distinct。
- 导出(计算)列。
- 因为视图主要用于数据检索,所以才会有上面这么多的约束(上面的限制来自于MySQL 5版本,未来的MySQL可能会进行变更),之所以有这些限制是因为:上面的这些SELECT操作可能涉及到低层表的多个行,而在视图中只对应一行,如果更新视图,则无法辨别出应该更新低层表的哪一行。
演示案例
- 下面修改汪茹的dept为经管院