Syntax
------

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

Description
-----------

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 afterwards 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. A SHOW CREATE VIEW shows that such queries are
rewritten and column names are included in the view definition.

The view definition must be a query that does not return errors at view
creation times. However, the base tables used by the views might be altered
later and the query may not be valid anymore. In this case, querying the view
will result in an error. CHECK TABLE helps in finding this kind of problems.

The ALGORITHM clause affects how MariaDB 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.

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.

MySQL until 5.1.28
------------------
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:

MariaDB [test]> 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:

CREATE TABLE t (qty INT, price INT);

INSERT INTO t VALUES(3, 50);

CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

A view definition is subject to the following restrictions:

* The SELECT statement cannot contain a subquery in the FROM clause.
* The SELECT statement cannot refer to system or user variables.
* Within a stored program, the definition cannot refer to program parameters
or local variables.
* The SELECT statement cannot refer to prepared statement parameters.
* Any table or view referred to in the definition must exist. However, after a