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. CREATE VIEW might also require the SUPER privilege, depending on the DEFINER value, as described later in this section.
When a view is referenced, privilege checking occurs.
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, use a fully qualified name
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Note - Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.
A VIEW can:
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 | +------+-------+-------+
VIEWmay or may not optimize as well as the equivalent
SELECT. It is unlikely to optimize any better.