如何创建MySQL5的视图-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

如何创建MySQL5的视图

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:04 PM

creatercreateBasichowviewgrammar

基本语法： 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. (完)

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

win11怎么创建本地连接Jun 29, 2023 pm 12:55 PM

win11怎么创建本地连接？有小伙伴在升级到win11系统之后，发现一些功能没办法正常使用，有小伙伴使用本地连接得时候，发现没有办法正常连接，不能正常连接的话，就没有办法上网，那么我们应该如何解决呢。小编下面整理了win11创建本地连接步骤，感兴趣的话，跟着小编一起往下看看吧！win11创建本地连接步骤1、点击桌面开始菜单，打开设置面板，如图所示。2、找到网络和Internet选项，如图所示。3、点击设置拨号连接按钮，如图所示。4、点击该栏目下的设置新连接选项，如图所示。5、最后点击设置新网络图

Win11如何创建电源计划Jun 29, 2023 pm 01:15 PM

　　Win11如何创建电源计划？电源计划是管理计算机如何使用和节省电源的硬件和系统设置的集合。近期有用户在问Win11如何创建电源计划？其实方法很简单，还不清楚应该如何操作的朋友们可以来看看下面这篇Win11自定义电源计划的技巧，希望你会喜欢。　　Win11自定义电源计划的技巧　　在Windows11上创建自定义电源计划　　打开开始菜单并键入控制面板。　　从搜索结果中选择控制面板。　　在控制面板中，将查看方式选项更改为大图标。　　接下来，选择电源选项。　　单击电源选项菜单中的创建电源计划选项。　

如何在 Microsoft Word 中创建用户输入提示Apr 14, 2023 pm 04:40 PM

假设您有一个要求，您必须从50个人那里收集数据。您可以将Word文件发送给他们，他们可以轻松填写。但是您需要所有50个文档中的格式和对齐方式以及其他所有内容都相同。好吧，如果您将原始Word文件提供给这50个人，而不是50个相同的文档，您将得到50个完全不同的文档，不用说。那么，有解决办法吗？当然，您知道我们总有适合您的解决方案！让我们谈谈模板！Word模板是您的任务的完美解决方案。通过使用Word模板，您可以在用户打开模板文档时提示他们输入一些数据。他们可以在用户提

使用Yii框架创建电影网站Jun 21, 2023 am 09:04 AM

随着互联网的普及以及人们对电影的热爱，电影网站成为了一个受欢迎的网站类型。在创建一个电影网站时，一个好的框架是非常必要的。Yii框架是一个高性能的PHP框架，易于使用且具有出色的性能。在本文中，我们将探讨如何使用Yii框架创建一个电影网站。安装Yii框架在使用Yii框架之前，需要先安装框架。安装Yii框架非常简单，只需要在终端执行以下命令：composer

MySQL表设计教程：创建一个简单的用户积分表Jul 02, 2023 am 10:12 AM

MySQL表设计教程：创建一个简单的用户积分表标题：MySQL表设计教程：创建一个简单的用户积分表导语：在开发常见的用户系统中，积分系统是一个重要的组成部分。本文将教你如何使用MySQL创建一个简单的用户积分表，并附带代码示例，帮助你更好地理解和实践该表设计。正文：确定表的名称和字段首先，我们需要确定表的名称和表中所需的字段。对于用户积分表，我们可以将其命名

哈医大临床药学就业是否有前途(哈医大临床药学就业前景怎么样)Jan 02, 2024 pm 08:54 PM

哈医大临床药学就业前景如何尽管全国就业形势不容乐观，但药科类毕业生仍然有着良好的就业前景。总体来看，药科类毕业生的供给量少于需求量，各医药公司和制药厂是吸纳这类毕业生的主要渠道，制药行业对人才的需求也在稳步增长。据介绍，近几年药物制剂、天然药物化学等专业的研究生供需比甚至达到1∶10。临床药学专业就业方向：临床医学专业学生毕业后可在医疗卫生单位、医学科研等部门从事医疗及预防、医学科研等方面的工作。就业岗位：医药代表、医药销售代表、销售代表、销售经理、区域销售经理、招商经理、产品经理、产品专员、护

如何使用MySQL创建日志表实现系统日志功能Jul 02, 2023 am 09:13 AM

如何使用MySQL创建日志表实现系统日志功能在开发和运维系统中，系统日志是非常重要的一部分，能够记录系统的运行状态和异常情况，为故障排查和性能优化提供依据。MySQL是一种广泛使用的关系型数据库，它具有性能稳定、易于管理和无限扩展等优点，因此可以使用MySQL来创建日志表来实现系统日志的功能。本文将介绍如何使用MySQL创建日志表，并提供相关代码示例。步骤一

如何使用PHP API创建Twitter机器人Jun 20, 2023 am 08:32 AM

随着社交媒体的普及，越来越多的人开始利用Twitter等社交媒体平台来进行营销和推广。这种方式很有效，但需要花费大量的时间和精力来保持活跃度。如果你想在Twitter上推广自己的品牌或服务，但又没有足够的时间或资源来管理一个活跃的Twitter账户，那么你可以考虑使用Twitter机器人。Twitter机器人是一种自动化工具，它可以帮助你在Twitter上自

See all articles