如何创建MySQL5的视图
2007-11-11 15:59:00 来源:WEB开发网- ·如何禁止修改系统时间
·将系统装到U盘中 如何自制XP@USB启动
·买电脑勿随便出手 从商家配置看JS如何
·再谈如何释放C盘空间 27招具体优化技
·如何让MM的脸通透可人
·如何卸载netware客户服务
·如何下载在线电影
·图文详解:如何调整光驱激光头功率
·如何与美丽的明星合影留念
·天下霸图2-如何收费经营
基本语法:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEWview_name
[(column_list
)] ASselect_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
This statement creates a new view, or replaces an existing one if the OR REPLACE
clause is given. The select_statement
is a SELECT
statement that provides the definition of the view. The statement can select from base tables or other views.
This 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 DELETE
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 name as db_name.view_name
when you create it.
MySQL(和PHP搭配之最佳组合)> 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 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.
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:
MySQL(和PHP搭配之最佳组合)>CREATE TABLE t (qty INT, price INT);
MySQL(和PHP搭配之最佳组合)>INSERT INTO t VALUES(3, 50);
MySQL(和PHP搭配之最佳组合)>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
MySQL(和PHP搭配之最佳组合)>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 theFROM
clause.The
SELECT
statement cannot refer to system or user variables.The
SELECT
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. 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 aTEMPORARY
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 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 you get from the view:
MySQL(和PHP搭配之最佳组合)>CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));
Query OK, 0 rows affected (0.00 sec)MySQL(和PHP搭配之最佳组合)>SET NAMES 'latin1';
Query OK, 0 rows affected (0.00 sec)MySQL(和PHP搭配之最佳组合)>SELECT * FROM v;
+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| latin1 | latin1_swedish_ci |+-------------------+---------------------+1 row in set (0.00 sec)MySQL(和PHP搭配之最佳组合)>SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)MySQL(和PHP搭配之最佳组合)>SELECT * FROM v;
+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| utf8 | utf8_general_ci |+-------------------+---------------------+1 row in set (0.00 sec)
The optional ALGORITHM
clause is a MySQL(和PHP搭配之最佳组合) extension to standard SQL. ALGORITHM
takes three values: MERGE
, TEMPTABLE
, or UNDEFINED
. The default algorithm is UNDEFINED
if no ALGORITHM
clause is present. The algorithm affects how MySQL(和PHP搭配之最佳组合) processes the view.
For MERGE
, 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.
For TEMPTABLE
, the results from the vie
更多精彩
赞助商链接