你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL视图(view)

[日期:2013-07-10] 来源:Linux社区  作者:huanghualiang [字体: ]

.A view acts as a virtual table. Views are available in binary releases from 5.0.1 and up.
视图是从5.0 开始支持,是一张虚拟表,在二进制的5.0.1以及更高的版本都支持。

.Updatable and Insertable Views—updateinsert 视图(5.0 开始就支持
A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t; #col2 是派生列

This view is not insertable because col2 is derived from an expression. But it is updatable if the update does not try to update col2. This update is permissible:
UPDATE v SET col1 = 0;#
允许

This update is not permissible because it attempts to update a derived column:
UPDATE v SET col2 = 0;#
不允许
3.The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.
In a WITH CHECK OPTION clause for an updatable view, the
LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined
CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED. Consider the definitions for the following table and set of views:
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Here the v2 and v3 views are defined in terms of another view, v1. v2 has a LOCAL check option, so inserts are tested only against the v2 check. v3 has a CASCADED check option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences:
mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

V3 创建在v1上,且用WITH CASCADED CHECK OPTION; 对下层的视图v1 的定义检查,违反了v1的定义,报错!!

相关阅读:MySQL视图表创建与修改 http://www.linuxidc.com/Linux/2012-02/53842.htm

linux
相关资讯       MySQL视图 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款