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

首页

数据库

mysql教程

如何创建MySQL5的视图

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:04 PM

create r 创建基本如何视图语法

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

热AI工具

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

免费脱衣服图片

Clothoff.io

AI脱衣机

AI Hentai Generator

免费生成ai无尽的。

显示更多

热工具

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

中文版，非常好用

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

显示更多

热门话题

gmail邮箱登陆入口在哪里

7470

CakePHP 教程

1377

steam的账户名称是什么格式

win11激活密钥永久

NYT连接提示和答案

显示更多

Related knowledge

如何在GIMP中创建像素艺术 Feb 19, 2024 pm 03:24 PM

本文将引起您的兴趣，如果您有意在Windows上使用GIMP进行像素艺术创作。GIMP是一款著名的图形编辑软件，不仅免费开源，还能帮助用户轻松创建出美丽的图像和设计。除了适用于初学者和专业设计师外，GIMP也可以用于制作像素艺术，这种数字艺术形式是利用像素作为唯一构建块来进行绘制和创作的。如何在GIMP中创建像素艺术以下是在WindowsPC上使用GIMP创建像素图片的主要步骤：下载并安装GIMP，然后启动应用程序。创建一个新的形象。调整宽度和高度的大小。选择铅笔工具。将笔刷类型设置为像素。设置

如何在真我手机上创建文件夹？ Mar 23, 2024 pm 02:30 PM

标题：真我手机新手指南：如何在真我手机上创建文件夹？在当今社会，手机已经成为人们生活中必不可少的工具。而真我手机作为一款备受欢迎的智能手机品牌，其简洁、实用的操作系统备受用户喜爱。在使用真我手机的过程中，很多人可能会遇到需要整理手机中的文件和应用的情况，而创建文件夹就是一种有效的方式。本文将介绍如何在真我手机上创建文件夹，帮助用户更好地管理自己的手机内容。第

格力+如何创建家庭 Mar 01, 2024 pm 12:40 PM

很多朋友表示想知道在格力+软件里该怎么去创建家庭，下面为大家带来了操作方法，想要了解的朋友和我一起来看看吧。首先，打开手机上的格力+软件，并登录。接着，在页面底部的选项栏中，点击最右边的“我的”选项，即可进入个人账户页面。2.来到我的页面后，在“家庭”下方的选项里有一个“创建家庭”，找到后在它的上面点击进入。3.接下来跳转到创建家庭的页面里，根据提示在输入框里输入要设置的家庭名称，输入好后在右上角点击“保存”按钮。4.最后在页面下方会弹出一个“保存成功”的提示，代表家庭已经成功创建好了。

理解SpringBoot和SpringMVC之间的差异及比较 Dec 29, 2023 am 09:20 AM

对比SpringBoot与SpringMVC，了解它们的差异随着Java开发的不断发展，Spring框架已经成为了许多开发人员和企业的首选。在Spring的生态系统中，SpringBoot和SpringMVC是两个非常重要的组件。虽然它们都是基于Spring框架的，但在功能和使用方式上却有一些区别。本文将重点对比一下SpringBoot与Sprin

如何创建您的 iPhone 联系人海报 Mar 02, 2024 am 11:30 AM

在iOS17中，Apple为其常用的“电话”和“通讯录”应用程序新增了联系人海报功能。这一功能允许用户为每个联系人设置个性化的海报，使通讯录更具可视化和个性化。联系人海报可以帮助用户更快速地识别和定位特定联系人，提高了用户体验。通过这一功能，用户可以根据自己的喜好和需求，为每个联系人添加特定的图片或标识，使通讯录界面更加生动iOS17中的Apple为iPhone用户提供了一种新颖的方式来表达自己，并添加了可个性化的联系海报。联系人海报功能允许您在呼叫其他iPhone用户时展示独特的个性化内容。您

Django初探：用命令行创建你的首个Django项目 Feb 19, 2024 am 09:56 AM

Django项目开启之旅：从命令行开始，创建你的第一个Django项目Django是一个强大而又灵活的Web应用框架，它以Python为基础，提供了许多开发Web应用所需的工具和功能。本文将带领你从命令行开始，创建你的第一个Django项目。在开始之前，请确保你已经安装了Python和Django。步骤一：创建项目目录首先，打开命令行窗口，并创建一个新的目录

Word视图有哪几种 Mar 19, 2024 pm 06:10 PM

我猜想，很多同学都想学习word的排版技巧，但小编偷偷告诉大家，在学习排版技巧之前需要先了解清楚word视图，在Word2007中提供了5种视图供用户选择，这5种视图包括页面视图、阅读版式视图、Web版式视图、大纲视图和普通视图，今天和小编了解一下这5种word视图吧。1.页面视图页面视图可以显示Word2007文档的打印结果外观，主要包括页眉、页脚、图形对象、分栏设置、页面边距等元素，是最接近打印结果的页面视图。2.阅读版式视图阅读版式视图以图书的分栏样式显示Word2007文档，Office

如何创建在线word Mar 21, 2024 pm 01:06 PM

你知道如何创建在线word文档吗？在线word文档可以实现多人协作编辑在线文档，有着大容量云存储的空间，文档可以集中存储，并且可以多设备登录，可以说无论你在哪一个设备上都能查看并编辑。最重要的是支持一键分享，把你的文档分享给同事特别方便。今天我们就来介绍一下如何创建在线word文档，其实方法很简单，需要的朋友可以参考一下。1，首先在电脑上打开wpsoffice软件，然后在新建文件页面中，打开文字项目栏，再选择新建在线文档选项。2，随后会打开新建文档页面，这里我们可以选择在线文档模板或者空白文档。

See all articles

如何创建MySQL5的视图

热AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

热门文章

热工具

记事本++7.3.1

SublimeText3汉化版

禅工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

热门话题