Oracle 5.0, Reference Manual

Page: 1071 / 3232

CREATE VIEW
Syntax
1051
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
[577] 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
[578] privilege. If the
OR REPLACE
clause is present,
you must also have the
DROP
[577] privilege for the view.
CREATE VIEW
might also require the
SUPER
[578] privilege, depending on the
DEFINER
value, as described later in this section.
When a view is referenced, privilege checking occurs as described later in this section.
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;
Within a database, base tables and views share the same namespace, so a base table and a view
cannot have the same name.
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.
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.0.72, when you modify an existing view, the server saves a
backup of the current view definition under the view 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,
an error occurs 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)
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;