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
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.
* The definition cannot refer to a TEMPORARY table, and you cannot create a
TEMPORARY view.
* Any tables named in the view definition must exist at definition time.
* You cannot associate a trigger with a view.
* For valid identifiers to use as view names, see Identifier Names.

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, and LOCK IN SHARE MODE.

The PROCEDURE clause cannot be used in a view definition, and it cannot be
used if a view is referenced in the FROM clause.

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:

CREATE VIEW v (mycol) AS SELECT 'abc';

SET sql_mode = '';

SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol | 
+-------+

SET sql_mode = 'ANSI_QUOTES';

SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   | 
+-------+

The DEFINER and SQL SECURITY clauses determine which MariaDB account to use
when checking access privileges for the view when a statement is executed that
references the view. They were added 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 MariaDB
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:

* If you do not have the SUPER privilege, or, from MariaDB 10.5.2, the SET
USER 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.
* If you have the SUPER privilege, or, from MariaDB 10.5.2, the SET USER
privilege, you can specify any syntactically legal account name. If the
account does not actually exist, a warning is generated.
* 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. 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.

View privileges are checked like this:

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

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 an extension to standard SQL. It affects how
MariaDB processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or
UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is
present. See View Algorithms for more information.

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. See Inserting and Updating
with Views.

WITH CHECK OPTION
-----------------

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 Inserting and Updating with Views.

IF NOT EXISTS
-------------

MariaDB starting with 10.1.3
----------------------------
The IF NOT EXISTS clause was added in MariaDB 10.1.3

When the IF NOT EXISTS clause is used, MariaDB will return a warning instead
of an error if the specified view already exists. Cannot be used together with
the OR REPLACE clause.

Atomic DDL
----------

MariaDB starting with 10.6.1
----------------------------
MariaDB 10.6.1 supports Atomic DDL and CREATE VIEW is atomic.

Examples
--------

CREATE TABLE t (a INT, b INT) ENGINE = InnoDB;

INSERT INTO t VALUES (1,1), (2,2), (3,3);

CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;

SELECT * FROM v;
+------+------+
| a    | a2   |
+------+------+
|    1 |    2 |
|    2 |    4 |
|    3 |    6 |
+------+------+

OR REPLACE and IF NOT EXISTS:

CREATE VIEW v AS SELECT a, a*2 AS a2 FROM t;
ERROR 1050 (42S01): Table 'v' already exists

CREATE OR REPLACE VIEW v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected (0.04 sec)

CREATE VIEW IF NOT EXISTS v AS SELECT a, a*2 AS a2 FROM t;
Query OK, 0 rows affected, 1 warning (0.01 sec)

SHOW WARNINGS;
+-------+------+--------------------------+
| Level | Code | Message                  |
+-------+------+--------------------------+
| Note  | 1050 | Table 'v' already exists |
+-------+------+--------------------------+

URL: https://mariadb.com/kb/en/create-view/