CREATE VIEW
Syntax
1052
mysql>
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.
• As of MySQL 5.0.52, aliases for column names in the
SELECT
statement are checked against the
maximum column length of 64 characters (not the maximum alias length of 256 characters).
ORDER BY
is permitted 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
,
LOCK IN SHARE MODE
, and
PROCEDURE
.
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:
mysql>
CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)
mysql>
SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql>
SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)
mysql>
SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql>
SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc |
+-------+
1 row in set (0.00 sec)
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
