Syntax:
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view, or replaces an existing
one if the OR REPLACE clause is given. If the view does not exist,
CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does
exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.

The select_statement is a SELECT statement that provides the definition
of the view. (When you select from the view, you select in effect using
the SELECT statement.) select_statement can select from base tables or
other views.

The view definition is "frozen" at creation time, so changes to the
underlying tables afterward do not affect the view definition. For
example, if a view is defined as SELECT * on a table, new columns added
to the table later do not become part of the view.

The ALGORITHM clause affects how MySQL processes the view. The DEFINER
and SQL SECURITY clauses specify the security context to be used when
checking access privileges at view invocation time. The WITH CHECK
OPTION clause can be given to constrain inserts or updates to rows in
tables referenced by the view. These clauses are described later in
this section.

The CREATE VIEW statement requires the CREATE VIEW privilege for the
view, and some privilege for each column selected by the SELECT
statement. For columns used elsewhere in the SELECT statement you must
have the SELECT privilege. If the OR REPLACE clause is present, you
must also have the DROP privilege for the view.

A view belongs to a database. By default, a new view is created in the
default 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;

Base tables and views share the same namespace within a database, so a
database cannot contain a base 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 clause can be given as a
list of comma-separated identifiers. The number of names in column_list
must be the same as the number of columns retrieved by the SELECT
statement.

*Note*: Prior to MySQL 5.1.29, When you modify an existing view, the
current view definition is backed up and saved. It is stored in that
table's database directory, in a subdirectory named arc. The backup
file for a view v is named v.frm-00001. If you alter the view again,
the next backup is named v.frm-00002. The three latest view backup
definitions are stored. Backed up view definitions are not preserved by
mysqldump, or any other such programs, but you can retain them using a
file copy operation. However, they are not needed for anything but to
provide you with a backup of your previous view definition. It is safe
to remove these backup definitions, but only while mysqld is not
running. If you delete the arc subdirectory or its files while mysqld
is running, you will receive an error the next time you try to alter
the view: mysql> ALTER VIEW v AS SELECT * FROM t; ERROR 6 (HY000):
Error on delete of '.\test\arc/v.frm-0004' (Errcode: 2)

Columns retrieved by the SELECT statement can be simple references to
table columns. They can also be expressions that use functions,
constant values, operators, and so forth.

Unqualified table or view names in the SELECT statement are interpreted
with respect to the default database. A view can refer to tables or
views in other databases by qualifying the table or view name with the
proper database name.

A view can be created from many kinds of SELECT statements. It can
refer to base tables or other views. It can use joins, UNION, and
subqueries. 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 |
+------+-------+-------+

A view definition is subject to the following restrictions:

o The SELECT statement cannot contain a subquery in the FROM clause.

o The SELECT statement cannot refer to system or user variables.

o Within a stored program, the definition cannot refer to program
  parameters or local variables.

o The SELECT statement cannot refer to prepared statement parameters.

o 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. In this case, use of the view results
  in an error. To check a view definition for problems of this kind,
  use the CHECK TABLE statement.

o The definition cannot refer to a TEMPORARY table, and you cannot
  create a TEMPORARY view.

o Any tables named in the view definition must exist at definition
  time.

o You cannot associate a trigger with a view.

ORDER BY is allowed in a view definition, but it is ignored if you
select from a view using a statement that has its own ORDER BY.

For other options or clauses in the definition, they are added to the
options or clauses of the statement that references the view, but the
effect is undefined. For example, if a view definition includes a LIMIT
clause, and you select from the view using a statement that has its own
LIMIT clause, it is undefined which limit applies. This same principle
applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that
follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE,
LOCK IN SHARE MODE, and PROCEDURE.

If you create a view and then change the query processing environment
by changing system variables, that may affect the results that you get
from the view:

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol | 
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   | 
+-------+
1 row in set (0.00 sec)

The DEFINER and SQL SECURITY clauses determine which MySQL account to
use when checking access privileges for the view when a statement is
executed that references the view. They were addded in MySQL 5.1.2. The
legal SQL SECURITY characteristic values are DEFINER and INVOKER. These
indicate that the required privileges must be held by the user who
defined or invoked the view, respectively. The default SQL SECURITY
value is DEFINER.

If a user value is given for the DEFINER clause, it should be a MySQL
account in 'user_name'@'host_name' format (the same format used in the
GRANT statement). The user_name and host_name values both are required.
The definer can also be given as CURRENT_USER or CURRENT_USER(). The
default DEFINER value is the user who executes the CREATE VIEW
statement. This is the same as specifying DEFINER = CURRENT_USER
explicitly.

If you specify the DEFINER clause, these rules determine the legal
DEFINER user values:

o If you do not have the SUPER privilege, the only legal user value is
  your own account, either specified literally or by using
  CURRENT_USER. You cannot set the definer to some other account.

o If you have the SUPER privilege, you can specify any syntactically
  legal account name. If the account does not actually exist, a warning
  is generated.

o If the SQL SECURITY value is DEFINER but the definer account does not
  exist when the view is referenced, an error occurs.

Within a view definition, CURRENT_USER returns the view's DEFINER value
by default as of MySQL 5.1.12. For older versions, and for views
defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER
returns the account for the view's invoker. For information about user
auditing within views, see
http://dev.mysql.com/doc/refman/5.1/en/account-activity-auditing.html.

Within a stored routine that is defined with the SQL SECURITY DEFINER
characteristic, CURRENT_USER returns the routine's DEFINER value. This
also affects a view defined within such a program, if the view
definition contains a DEFINER value of CURRENT_USER.

As of MySQL 5.1.2 (when the DEFINER and SQL SECURITY clauses were
implemented), view privileges are checked like this:

o At view definition time, the view creator must have the privileges
  needed to use the top-level objects accessed by the view. For
  example, if the view definition refers to table columns, the creator
  must have privileges for the columns, as described previously. If the
  definition refers to a stored function, only the privileges needed to
  invoke the function can be checked. The privileges required when the
  function runs can be checked only as it executes: For different
  invocations of the function, different execution paths within the
  function might be taken.

o When a view is referenced, privileges for objects accessed by the
  view are checked against the privileges held by the view creator or
  invoker, depending on whether the SQL SECURITY characteristic is
  DEFINER or INVOKER, respectively.

o If reference to a view causes execution of a stored function,
  privilege checking for statements executed within the function depend
  on whether the function is defined with a SQL SECURITY characteristic
  of DEFINER or INVOKER. If the security characteristic is DEFINER, the
  function runs with the privileges of its creator. If the
  characteristic is INVOKER, the function runs with the privileges
  determined by the view's SQL SECURITY characteristic.

Prior to MySQL 5.1.2 (before the DEFINER and SQL SECURITY clauses were
implemented), privileges required for objects used in a view are
checked at view creation time.

Example: A view might depend on a stored function, and that function
might invoke other stored routines. For example, the following view
invokes a stored function f():

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

Suppose that f() contains a statement such as this:

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

The privileges required for executing statements within f() need to be
checked when f() executes. This might mean that privileges are needed
for p1() or p2(), depending on the execution path within f(). Those
privileges must be checked at runtime, and the user who must possess
the privileges is determined by the SQL SECURITY values of the view v
and the function f().

The DEFINER and SQL SECURITY clauses for views are extensions to
standard SQL. In standard SQL, views are handled using the rules for
SQL SECURITY INVOKER.

If you invoke a view that was created before MySQL 5.1.2, it is treated
as though it was created with a SQL SECURITY DEFINER clause and with a
DEFINER value that is the same as your account. However, because the
actual definer is unknown, MySQL issues a warning. To make the warning
go away, it is sufficient to re-create the view so that the view
definition includes a DEFINER clause.

The optional ALGORITHM clause is a MySQL extension to standard SQL. It
affects how MySQL processes the view. ALGORITHM takes three values:
MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if
no ALGORITHM clause is present. For more information, see
http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html.

Some views are updatable. That is, you can use them in statements such
as UPDATE, DELETE, or INSERT to update the contents of the underlying
table. For a view to be updatable, there must be a one-to-one
relationship between the rows in the view and the rows in the
underlying table. There are also certain other constructs that make a
view non-updatable.

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.

For more information about updatable views and the WITH CHECK OPTION
clause, see
http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html.

URL: http://dev.mysql.com/doc/refman/5.1/en/create-view.html

