如何创建MySQL5的视图

基本语法：

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 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> 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> 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:

0

顶一下

相关文章

运用Mysql语句生成后门木马的具

Mysql数据库的安全配置、实用技

MySQL使用指南（一）

MySQL数据库中用GRANT语句增添

DBA解决MySQL服务器启动和关闭

提数据高分页效率

MySQL简易备份方法

如何安装并测试MySQL

如何恢复/修复MS SQL数据库的MD

通过查询分析器的调试功能进行

上一篇： 修改MySQL的默认密码
下一篇： 在FreeBSD下安装mysql5的注意点