如何创建MySQL5的视图

, and so forth)

DISTINCT

GROUP BY

HAVING

UNION or UNION ALL

Subquery in the select list

Join

Non-updatable view in the FROM clause

A subquery in the WHERE clause that refers to a table in the FROM clause

Refers only to literal values (in this case, there is no underlying table to update)

ALGORITHM = TEMPTABLE (use of a temporary table always makes a view non-updatable)

With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

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;

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 allowable:

UPDATE v SET col1 = 0;

This update is not allowable because it attempts to update a derived column:

UPDATE v SET col2 = 0;

It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are disallowed even though they might be theoretically updatable, because the implementation uses temporary tables to process them.

For a multiple-table updatable view, INSERT can work if it inserts into a single table. DELETE is not supported.

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. 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


文章整理：西部数码--专业提供域名注册、虚拟主机服务

http://www.west263.com

以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!