Basic Syntax: CREATE [ORREPLACE] [ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}] VIEWview_name [(column_list)] ASselect_statement [WITH [CASCADED | LOCAL] CHECKOPTION] Thisstatementcreatesanewview, comment
Basic Syntax: CREATE [or replace] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 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
Basic Syntax:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name
[(column_list
)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
This statement creates a new view, or replaces an existing one ifOR REPLACE
Clause is given.select_statement
IsSELECT
Statement that provides the definition of the view. The statement can select from base tables or other views.
This statement requiresCREATE VIEW
Privilege for the view, and some privilege for each column selected bySELECT
Statement. For columns used elsewhere inSELECT
Statement you must haveSELECT
Privilege. IfOR REPLACE
Clause is present, you must also haveDELETE
Privilege for the view.
A view belongs to a database. By default, a new view is created in the current database. To create the view explicitly in a given database, specify the namedb_name.view_name
When you create it.
mysql> CREATE VIEW test.v AS SELECT * FROM t;
Tables and views share the same namespace within a database, so a database cannot contain a 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 bySELECT
Statement are used for the view column names. To define explicit names for the view columns, the optionalcolumn_list
Clause can be given as a list of comma-separated identifiers. The number of names incolumn_list
Must be the same as the number of columns retrieved bySELECT
Statement.
Columns retrieved bySELECT
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 inSELECT
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 your kindsSELECT
Statements. It can refer to base tables or other views. It can use joins,UNION
, And subqueries.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:
TheSELECT
Statement cannot contain a subquery inFROM
Clause.
TheSELECT
Statement cannot refer to system or user variables.
TheSELECT
Statement cannot refer to prepared statement parameters.
Within a stored routine, the definition cannot refer to routine parameters or local variables.
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 check a view definition for problems of this kind, useCHECK TABLE
Statement.
The definition cannot refer toTEMPORARY
Table, and you cannot createTEMPORARY
View.
The tables named in the view definition must already exist.
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 ownORDER BY
.
For other options or clses in the definition, they are added to the options or clses of the statement that references the view, but the effect is undefined. For example, if a view definition between desLIMIT
Clause, and you select from the view using a statement that has its ownLIMIT
Clause, it is undefined which limit applies. This same principle applies to options suchALL
,DISTINCT
, OrSQL_SMALL_RESULT
That followSELECT
Keyword, and to clses suchINTO
,FOR UPDATE
,LOCK IN SHARE MODE
, AndPROCEDURE
.
If you create a view and then change the query processing environment by changing system variables, that may affect the results 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 optionalALGORITHM
Clause is a MySQL extension to standard SQL.ALGORITHM
Takes three values:MERGE
,TEMPTABLE
, OrUNDEFINED
. The default algorithm isUNDEFINED
If noALGORITHM
Clause is present. The algorithm affects how MySQL processes the view.
ForMERGE
, 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.
ForTEMPTABLE
, The results from the view are retrieved into a temporary table, which then is used to execute the statement.
ForUNDEFINED
, MySQL chooses which algorithm to use. It prefersMERGE
OverTEMPTABLE
If possible, becauseMERGE
Is usually more efficient and because a view cannot be updatable if a temporary table is used.
A reason to chooseTEMPTABLE
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 releaseMERGE
Algorithm so that other clients that use the view are not blocked as long.
A view algorithm can beUNDEFINED
Three ways:
NoALGORITHM
Clause is present inCREATE VIEW
Statement.
TheCREATE VIEW
Statement has an explicitALGORITHM = UNDEFINED
Clause.
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 algorithmUNDEFINED
.
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 returns strate howMERGE
Algorithm works. The examples assume that there is a viewv_merge
That has this definition:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) ASSELECT 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:
v_merge
Becomest
*
Becomesvc1, vc2
, Which correspondsc1, c2
The viewWHERE
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, doesn't thatvc1 < 100
Becomesc1 < 100
And the viewWHERE
Clause is added to the statementWHERE
Clause usingAND
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);
When tively, the statement to be executed hasWHERE
Clause of this form:
WHERE (select WHERE) AND (view WHERE)
TheMERGE
Algorithm requires a one-to 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:
Aggregate functions (SUM()
,MIN()
,MAX()
,COUNT()
, And so forth)
DISTINCT
GROUP BY
HAVING
UNION
OrUNION ALL
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 suchUPDATE
,DELETE
, OrINSERT
To update the contents of the underlying table. for a view to be updatable, there must be a one-to 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:
Aggregate functions (SUM()
,MIN()
,MAX()
,COUNT()
, And so forth)
DISTINCT
GROUP BY
HAVING
UNION
OrUNION ALL
Subquery in the select list
Join
Non-updatable view inFROM
Clause
A subquery inWHERE
Clause that refers to a table inFROM
Clause
Refers only to literal values (in this case, there is no underlying table to update)
ALGORITHM = TEMPTABLE
(Use of a temporary table always makes a view non-updatable)
With respect to insertability (being updatableINSERT
Statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:
There must be no duplicate view column names.
The view must contain all columns in the base table that do not have a default value.
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.14159col1 + 3UPPER(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 becausecol2
Is derived from an expression. But it is updatable if the update does not try to updatecol2
. 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 withMERGE
Algorithm. For this to work, the view must use an inner join (not an outer join orUNION
). Also, only a single table in the view definition can be updated, soSET
Clause must name only columns from one of the tables in the view. Views that useUNION 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.
TheWITH CHECK OPTION
Clause can be given for an updatable view to prevent inserts or updates to rows tables t those for whichWHERE
Clause inselect_statement
Is true.
InWITH CHECK OPTION
Clause for an updatable view,LOCAL
AndCASCADED
Keywords determine the scope of check testing when the view is defined in terms of another view.LOCAL
Keyword restrictsCHECK 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 isCASCADED
. Consider the definitions for the following table and set of views:
mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
-> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
-> WITH CASCADED CHECK OPTION;
Herev2
Andv3
Views are defined in terms of another view,v1
.v2
HasLOCAL
Check option, so inserts are tested only againstv2
Check.v3
HasCASCADED
Check option, so inserts are tested not only against its own check, but against those of underlying views. The following statements contains strate these differences:
ql> INSERT INTO v2 VALUES (2);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
The updatability of views may be affected by the value ofupdatable_views_with_limit
System variable. (end)