How to create MySQL5 view_mysql

Source: Internet
Author: User
Basic syntax for creating views of 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 your 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:

The SELECT statement cannot contain a subquery in the FROM 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 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.

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.

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 has Des 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 clses 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> 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;

+ ------------------- + --------------------- +


+ ------------------- + --------------------- +

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

+ ------------------- + --------------------- +


+ ------------------- + --------------------- +

| Utf8 | utf8_general_ci |

+ ------------------- + --------------------- +

1 row in set (0.00 sec)

The optional ALGORITHM clause is a MySQL 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 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.