如何创建MySQL5的视图-mysql教程-PHP中文网

首页

数据库

mysql教程

如何创建MySQL5的视图

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:04 PM

creater创建基本如何视图语法

基本语法： 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

<STRONG>基本语法：</STRONG>

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW <EM class=replaceable><CODE>view_name</CODE></EM> [(<EM class=replaceable><CODE>column_list</CODE></EM>)]
    AS <EM class=replaceable><CODE>select_statement</CODE></EM>
    [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> <STRONG class=userinput><CODE>CREATE VIEW test.v AS SELECT * FROM t;</CODE></STRONG>

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> <STRONG class=userinput><CODE>CREATE TABLE t (qty INT, price INT);</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>INSERT INTO t VALUES(3, 50);</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>SELECT * FROM v;</CODE></STRONG>
+------+-------+-------+
| 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. 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 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> <STRONG class=userinput><CODE>CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));</CODE></STRONG>
Query OK, 0 rows affected (0.00 sec)

mysql> <STRONG class=userinput><CODE>SET NAMES 'latin1';</CODE></STRONG>
Query OK, 0 rows affected (0.00 sec)

mysql> <STRONG class=userinput><CODE>SELECT * FROM v;</CODE></STRONG>
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| latin1            | latin1_swedish_ci   |
+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> <STRONG class=userinput><CODE>SET NAMES 'utf8';</CODE></STRONG>
Query OK, 0 rows affected (0.00 sec)

mysql> <STRONG class=userinput><CODE>SELECT * FROM v;</CODE></STRONG>
+-------------------+---------------------+
| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |
+-------------------+---------------------+
| 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 view are retrieved into a temporary table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

A reason to choose TEMPTABLE 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 release than the MERGE algorithm so that other clients that use the view are not blocked as long.

A view algorithm can be UNDEFINED three ways:

No ALGORITHM clause is present in the CREATE VIEW statement.
The CREATE VIEW statement has an explicit ALGORITHM = 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 algorithm to UNDEFINED.

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 illustrate how the MERGE algorithm works. The examples assume that there is a view v_merge that has this definition:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT 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 becomes t
* becomes vc1, vc2, which corresponds to c1, c2
The view WHERE 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, except that vc1  becomes c1  and the view WHERE clause is added to the statement WHERE clause using an AND 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);

Effectively, the statement to be executed has a WHERE clause of this form:

WHERE (select WHERE) AND (view WHERE)

The MERGE 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 or UNION 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 such as UPDATE, DELETE, or INSERT 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 or UNION ALL
Subquery in the select list
Join
Non-updatable view in the FROM clause
A subquery in the WHERE clause that refers to a table in the FROM 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 updatable with INSERT 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.14159
col1 + 3
UPPER(col2)
col3 / col4
(<CODE>subquery</CODE>)
```

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 because col2 is derived from an expression. But it is updatable if the update does not try to update col2. 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 with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION 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.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. LOCAL keyword restricts the CHECK 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 is CASCADED. Consider the definitions for the following table and set of views:

mysql> <STRONG class=userinput><CODE>CREATE TABLE t1 (a INT);</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2</CODE></STRONG>
    -> <STRONG class=userinput><CODE>WITH CHECK OPTION;</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0</CODE></STRONG>
    -> <STRONG class=userinput><CODE>WITH LOCAL CHECK OPTION;</CODE></STRONG>
mysql> <STRONG class=userinput><CODE>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0</CODE></STRONG>
    -> <STRONG class=userinput><CODE>WITH CASCADED CHECK OPTION;</CODE></STRONG>

Here the v2 and v3 views are defined in terms of another view, v1. v2 has a LOCAL check option, so inserts are tested only against the v2 check. v3 has a CASCADED check option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences:

ql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> <STRONG class=userinput><CODE>INSERT INTO v3 VALUES (2);</CODE></STRONG>
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

The updatability of views may be affected by the value of the updatable_views_with_limit system variable. (完)

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL：世界上最受欢迎的数据库的简介Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。

MySQL的重要性：数据存储和管理Apr 12, 2025 am 12:18 AM

MySQL是一个开源的关系型数据库管理系统，适用于数据存储、管理、查询和安全。1.它支持多种操作系统，广泛应用于Web应用等领域。2.通过客户端-服务器架构和不同存储引擎，MySQL高效处理数据。3.基本用法包括创建数据库和表，插入、查询和更新数据。4.高级用法涉及复杂查询和存储过程。5.常见错误可通过EXPLAIN语句调试。6.性能优化包括合理使用索引和优化查询语句。

为什么要使用mysql？利益和优势Apr 12, 2025 am 12:17 AM

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能，支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎，支持事务和查询优化。3.易于使用，支持多种操作系统和编程语言。4.拥有强大的社区支持，提供丰富的资源和解决方案。

描述InnoDB锁定机制（共享锁，独家锁，意向锁，记录锁，间隙锁，下一键锁）。Apr 12, 2025 am 12:16 AM

InnoDB的锁机制包括共享锁、排他锁、意向锁、记录锁、间隙锁和下一个键锁。1.共享锁允许事务读取数据而不阻止其他事务读取。2.排他锁阻止其他事务读取和修改数据。3.意向锁优化锁效率。4.记录锁锁定索引记录。5.间隙锁锁定索引记录间隙。6.下一个键锁是记录锁和间隙锁的组合，确保数据一致性。

MySQL查询性能差的常见原因是什么？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。 1.没有索引导致查询缓慢，添加索引后可显着提升性能。 2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。 3.重构表结构和优化JOIN条件可改善表设计问题。 4.数据量大时，采用分区和分表策略。 5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什么时候应该使用复合索引与多个单列索引？Apr 11, 2025 am 12:06 AM

在数据库优化中，应根据查询需求选择索引策略：1.当查询涉及多个列且条件顺序固定时，使用复合索引；2.当查询涉及多个列但条件顺序不固定时，使用多个单列索引。复合索引适用于优化多列查询，单列索引则适合单列查询。

如何识别和优化MySQL中的慢速查询？（慢查询日志，performance_schema）Apr 10, 2025 am 09:36 AM

要优化MySQL慢查询，需使用slowquerylog和performance_schema：1.启用slowquerylog并设置阈值，记录慢查询；2.利用performance_schema分析查询执行细节，找出性能瓶颈并优化。

MySQL和SQL：开发人员的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统，SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎，SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询，如按条件过滤和排序。4.常见错误包括语法错误和性能问题，可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

See all articles