您当前的位置: 首页 >  sql

暂无认证

  • 1浏览

    0关注

    92582博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文

MySQL view视图

发布时间:2019-05-09 20:12:59 ,浏览量:1

一、视图概述
  • 视图是虚拟的表。在视图创建之后,可以用与表基本相同的方式利用它们。甚至可以添加和更新数据(添加和更新数据存在某些限制,下面介绍)
  • 重要的是视图仅仅是用来查询存储在别处的数据的一种设置。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。所以在添加和更改表数据的之后,视图返回的是改变之后的数据
  • 视图的使用另外可参阅文章:https://blog.csdn.net/qq_41453285/article/details/104147709。
视图的规则和限制
  • 与表一样,视图必须唯一的命名(不能给视图取与别的视图或表相同的名字)。
  • 对于可以创建的视图数目没有限制。
  • 为了创建视图,必须具有足够的访问权限。这些权限通常由数据管理人员授予。
  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造另一个视图。
  • 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为经管院

关注
打赏
1653961664
查看更多评论
立即登录/注册

微信扫码登录

0.0532s