如何创建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脫衣器

Video Face Swap

使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！

熱工具

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

中文版，非常好用

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學

1670

CakePHP 教程

1428

Laravel 教程

1329

PHP教程

1274

C# 教程

1256

Related knowledge

如何在真我手機上建立資料夾？ Mar 23, 2024 pm 02:30 PM

標題：真我手機新手指南：如何在真我手機上建立資料夾？在現今社會，手機已成為人們生活中不可或缺的工具。而真我手機作為一款備受歡迎的智慧型手機品牌，其簡潔、實用的作業系統備受用戶喜愛。在使用真實我手機的過程中，很多人可能會遇到需要整理手機中的檔案和應用程式的情況，而建立資料夾就是一種有效的方式。本文將介紹如何在真我手機上建立資料夾，幫助使用者更好地管理自己的手機內容。第

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

很多朋友表示想知道在格力+軟體裡該怎麼去創建家庭，下面為大家帶來了操作方法，想要了解的朋友和我一起來看看吧。首先，開啟手機上的格力+軟體，並登入。接著，在頁面底部的選項列中，點選最右邊的「我的」選項，即可進入個人帳戶頁面。 2.來到我的頁面後，在“家庭”下方的選項裡有一個“創建家庭”，找到後在它的上面點擊進入。 3.接下來跳到建立家庭的頁面裡，根據提示在輸入框裡輸入要設定的家庭名稱，輸入好後在右上角點選「儲存」按鈕。 4.最後在頁面下方會彈出一個「儲存成功」的提示，代表家庭已經成功創建好了。

如何在GIMP中創造像素藝術 Feb 19, 2024 pm 03:24 PM

本文將引起您的興趣，如果您有意在Windows上使用GIMP進行像素藝術創作。 GIMP是一款著名的圖形編輯軟體，不僅免費開源，還能幫助使用者輕鬆創造美麗的圖像和設計。除了適用於初學者和專業設計師外，GIMP也可以用於製作像素藝術，這種數位藝術形式是利用像素作為唯一構建塊來進行繪製和創作的。如何在GIMP中建立像素藝術以下是在WindowsPC上使用GIMP建立像素圖片的主要步驟：下載並安裝GIMP，然後啟動應用程式。創造一個新的形象。調整寬度和高度的大小。選擇鉛筆工具。將筆刷類型設定為像素。設定

如何創建您的 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是一個強大且靈活的網路應用框架，它以Python為基礎，提供了許多開發Web應用所需的工具和功能。本文將帶領你從命令列開始，創建你的第一個Django專案。在開始之前，請確保你已經安裝了Python和Django。步驟一：建立專案目錄首先，開啟命令列窗口，並建立新的目錄

理解SpringBoot和SpringMVC之間的差異及比較 Dec 29, 2023 am 09:20 AM

對比SpringBoot與SpringMVC，了解它們的差異隨著Java開發的不斷發展，Spring框架已經成為了許多開發人員和企業的首選。在Spring的生態系中，SpringBoot和SpringMVC是兩個非常重要的組件。雖然它們都是基於Spring框架的，但在功能和使用方式上卻有一些區別。本文將聚焦在SpringBoot與Sprin

Word視圖有哪幾種 Mar 19, 2024 pm 06:10 PM

我猜想，很多同學都想學習word的排版技巧，但小編偷偷告訴大家，在學習排版技巧之前需要先了解清楚word視圖，在Word2007中提供了5種視圖供用戶選擇，這5種視圖包括頁面視圖、閱讀版視圖、Web版視圖、大綱視圖和普通視圖，今天就和小編了解這5種word視圖吧。 1.頁面視圖頁面視圖可以顯示Word2007文件的列印結果外觀，主要包括頁首、頁尾、圖形物件、分欄設定、頁面邊距等元素，是最接近列印結果的頁面視圖。 2.閱讀版視圖閱讀版視圖以圖書的分欄樣式顯示Word2007文檔，Office

lambda 表達式的語法和結構有什麼特色？ Apr 25, 2024 pm 01:12 PM

Lambda表達式是無名稱的匿名函數，其語法為：(parameter_list)->expression。它們具有匿名性、多樣性、柯里化和閉包等特徵。在實際應用中，Lambda表達式可用於簡潔地定義函數，如求和函數sum_lambda=lambdax,y:x+y，並透過map()函數應用於列表來進行求和操作。

See all articles

如何创建MySQL5的视图

熱AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

Video Face Swap

熱門文章

熱工具

記事本++7.3.1

SublimeText3漢化版

禪工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

熱門話題