(mysql.info.gz) CREATE VIEW
Info Catalog
(mysql.info.gz) CREATE TABLE
(mysql.info.gz) Data Definition
(mysql.info.gz) DROP DATABASE
13.2.7 `CREATE VIEW' Syntax
---------------------------
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW VIEW_NAME [(COLUMN_LIST)]
AS SELECT_STATEMENT
[WITH [CASCADED | LOCAL] CHECK OPTION]
This statement creates a new view, or replaces an existing one if the
`OR REPLACE' clause is given. The SELECT_STATEMENT is a `SELECT'
statement that provides the definition of the view. The statement can
select from base tables or other views.
A view belongs to a database. By default, a new view is created in the
current database. To create the view explicitly in a given database,
specify the name as DB_NAME.VIEW_NAME when you create it.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Tables and views share the same namespace within a database, so a
database cannot contain a table and a view that have the same name.
Views must have unique column names with no duplicates, just like base
tables. By default, the names of the columns retrieved by the `SELECT'
statement are used for the view column names. To define explicit names
for the view columns, the optional column list can be given. In this
case, the number of names in COLUMN_LIST must be the same as the number
of columns retrieved by the `SELECT' statement.
Columns retrieved by the `SELECT' statement can be simple references to
table columns. They can also be expressions that use operators,
functions, constant values, and so forth.
A view can refer to columns of tables or views in other databases by
qualifying the table or view name with the proper database name.
A view definition is subject to the following constraints:
* The `SELECT' statement cannot contain a subquery in the `FROM'
clause.
* The `SELECT' statement cannot refer to user variables.
* Within a stored procedure, the definition cannot refer to procedure
parameters.
* Any table or view referred to in the definition must exist.
However, after a view has been created, it is possible to drop a
table or view that the definition refers to. To check a view
definition for problems of this kind, use the `CHECK TABLE'
statement.
* The definition cannot refer to a `TEMPORARY' table, and you cannot
create a `TEMPORARY' view.
* You cannot associate a trigger with a view.
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.
A view can be created from many kinds of `SELECT' statements. For
example, the `SELECT' can refer to a single table, a join of multiple
tables, or a `UNION'. The `SELECT' need not even refer to any tables.
The following example defines a view that selects two columns from
another table, as well as an expression calculated from those columns:
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
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
-> 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:
ql> 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'
The `CREATE VIEW' statement was added in MySQL 5.0.1. The `WITH CHECK
OPTION' clause was implemented in MySQL 5.0.2.
Info Catalog
(mysql.info.gz) CREATE TABLE
(mysql.info.gz) Data Definition
(mysql.info.gz) DROP DATABASE
automatically generated byinfo2html