DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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