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]

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.

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

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 The SELECT statement cannot refer to prepared statement parameters.

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

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. 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 The tables named in the view definition must already exist.

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 AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)

mysql> SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| latin1            | latin1_swedish_ci   |
+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v;
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| utf8              | utf8_general_ci     |
+-------------------+---------------------+
1 row in set (0.00 sec)

The DEFINER and SQL SECURITY clauses specify the security context to be
used when checking access privileges at view invocation time. They were
addded in MySQL 5.0.13, but have no effect until MySQL 5.0.16.

CURRENT_USER also can be given as CURRENT_USER().

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

The default DEFINER value is the user who executes the CREATE VIEW
statement. (This is the same as DEFINER = CURRENT_USER.) If a user
value is given, 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.

If you specify the DEFINER clause, you cannot set the value to any user
but your own unless you have the SUPER privilege. 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.

The SQL SECURITY characteristic determines which MySQL account to use
when checking access privileges for the view when the view is executed.
The legal characteristic values are DEFINER and INVOKER. These indicate
that the view must be executable by the user who defined it or invoked
it, respectively. The default SQL SECURITY value is DEFINER.

As of MySQL 5.0.16 (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 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 At view execution time, 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 view execution 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.0.16 (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 need to be checked at runtime, and the user who must possess
the privileges is determined by the SQL SECURITY values of the function
f() and the view v.

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.0.13, 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.
ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The
default algorithm is UNDEFINED if no ALGORITHM clause is present. The
algorithm affects how MySQL processes the view.

For MERGE, the text of a statement that refers to the view and the view
definition are merged such that parts of the view definition replace
corresponding parts of the statement.

For TEMPTABLE, the results from the view are retrieved into a temporary
table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE
over TEMPTABLE if possible, because MERGE is usually more efficient and
because a view cannot be updatable if a temporary table is used.

A reason to choose TEMPTABLE explicitly is that locks can be released
on underlying tables after the temporary table has been created and
before it is used to finish processing the statement. This might result
in quicker lock release than the MERGE algorithm so that other clients
that use the view are not blocked as long.

A view algorithm can be UNDEFINED for three reasons:

o No ALGORITHM clause is present in the CREATE VIEW statement.

o The CREATE VIEW statement has an explicit ALGORITHM = UNDEFINED
  clause.

o ALGORITHM = MERGE is specified for a view that can be processed only
  with a temporary table. In this case, MySQL generates a warning and
  sets the algorithm to UNDEFINED.

As mentioned earlier, MERGE is handled by merging corresponding parts
of a view definition into the statement that refers to the view. The
following examples briefly illustrate how the MERGE algorithm works.
The examples assume that there is a view v_merge that has this
definition:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

Example 1: Suppose that we issue this statement:

SELECT * FROM v_merge;

MySQL handles the statement as follows:

o v_merge becomes t

o * becomes vc1, vc2, which corresponds to c1, c2

o The view WHERE clause is added

The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE c3 > 100;

Example 2: Suppose that we issue this statement:

SELECT * FROM v_merge WHERE vc1 < 100;

This statement is handled similarly to the previous one, except that
vc1 < 100 becomes c1 < 100 and the view WHERE clause is added to the
statement WHERE clause using an AND connective (and parentheses are
added to make sure the parts of the clause are executed with correct
precedence). The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

Effectively, the statement to be executed has a WHERE clause of this
form:

WHERE (select WHERE) AND (view WHERE)

The MERGE algorithm requires a one-to-one relationship between the rows
in the view and the rows in the underlying table. If this relationship
does not hold, a temporary table must be used instead. Lack of a
one-to-one relationship occurs if the view contains any of a number of
constructs:

o Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

o DISTINCT

o GROUP BY

o HAVING

o UNION or UNION ALL

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

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. To be more specific, a view is not updatable if it
contains any of the following:

o Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

o DISTINCT

o GROUP BY

o HAVING

o UNION or UNION ALL

o Subquery in the select list

o Join

o Non-updatable view in the FROM clause

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

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

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

o There must be no duplicate view column names.

o The view must contain all columns in the base table that do not have
  a default value.

o The view columns must be simple column references and not derived
  columns. A derived column is one that is not a simple column
  reference but is derived from an expression. These are examples of
  derived columns:

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

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.
