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

Home

Database

Mysql Tutorial

如何创建MySQL5的视图

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:04 PM

create r create Basic how view grammar

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

Copy after login

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]

Copy after login

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>

Copy after login

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

Copy after login

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)

Copy after login

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;

Copy after login

Example 1: Suppose that we issue this statement:

SELECT * FROM v_merge;

Copy after login

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;

Copy after login

Example 2: Suppose that we issue this statement:

SELECT * FROM v_merge WHERE vc1 < 100;

Copy after login

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

Copy after login

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

WHERE (select WHERE) AND (view WHERE)

Copy after login

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>)
```
Copy after login

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;

Copy after login

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;

Copy after login

This update is not allowable because it attempts to update a derived column:

UPDATE v SET col2 = 0;

Copy after login

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>

Copy after login

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'

Copy after login

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

Statement of this Website

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

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Repo: How To Revive Teammates

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

3 weeks ago By DDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7315

Java Tutorial

1625

CakePHP Tutorial

1349

Laravel Tutorial

1261

PHP Tutorial

1208

Related knowledge

How to create pixel art in GIMP Feb 19, 2024 pm 03:24 PM

This article will interest you if you are interested in using GIMP for pixel art creation on Windows. GIMP is a well-known graphics editing software that is not only free and open source, but also helps users create beautiful images and designs easily. In addition to being suitable for beginners and professional designers alike, GIMP can also be used to create pixel art, a form of digital art that utilizes pixels as the only building blocks for drawing and creating. How to Create Pixel Art in GIMP Here are the main steps to create pixel pictures using GIMP on a Windows PC: Download and install GIMP, then launch the application. Create a new image. Resize width and height. Select the pencil tool. Set the brush type to pixels. set up

How to create a family with Gree+ Mar 01, 2024 pm 12:40 PM

Many friends expressed that they want to know how to create a family in Gree+ software. Here is the operation method for you. Friends who want to know more, come and take a look with me. First, open the Gree+ software on your mobile phone and log in. Then, in the options bar at the bottom of the page, click the "My" option on the far right to enter the personal account page. 2. After coming to my page, there is a "Create Family" option under "Family". After finding it, click on it to enter. 3. Next jump to the page to create a family, enter the family name to be set in the input box according to the prompts, and click the "Save" button in the upper right corner after entering it. 4. Finally, a "save successfully" prompt will pop up at the bottom of the page, indicating that the family has been successfully created.

Understand the differences and comparisons between SpringBoot and SpringMVC Dec 29, 2023 am 09:20 AM

Compare SpringBoot and SpringMVC and understand their differences. With the continuous development of Java development, the Spring framework has become the first choice for many developers and enterprises. In the Spring ecosystem, SpringBoot and SpringMVC are two very important components. Although they are both based on the Spring framework, there are some differences in functions and usage. This article will focus on comparing SpringBoot and Spring

How to Create a Contact Poster for Your iPhone Mar 02, 2024 am 11:30 AM

In iOS17, Apple has added a contact poster feature to its commonly used Phone and Contacts apps. This feature allows users to set personalized posters for each contact, making the address book more visual and personal. Contact posters can help users identify and locate specific contacts more quickly, improving user experience. Through this feature, users can add specific pictures or logos to each contact according to their preferences and needs, making the address book interface more vivid. Apple in iOS17 provides iPhone users with a novel way to express themselves, and added a personalizable contact poster. The Contact Poster feature allows you to display unique, personalized content when calling other iPhone users. you

A first look at Django: Create your first Django project using the command line Feb 19, 2024 am 09:56 AM

Start the journey of Django project: start from the command line and create your first Django project. Django is a powerful and flexible web application framework. It is based on Python and provides many tools and functions needed to develop web applications. This article will lead you to create your first Django project starting from the command line. Before starting, make sure you have Python and Django installed. Step 1: Create the project directory First, open the command line window and create a new directory

What are the views in Word? Mar 19, 2024 pm 06:10 PM

I guess that many students want to learn the typesetting skills of Word, but the editor secretly tells you that before learning the typesetting skills, you need to understand the word views clearly. In Word2007, 5 views are provided for users to choose. These 5 views include pages. View, reading layout view, web layout view, outline view and normal view, let’s learn about these 5 word views with the editor today. 1. Page view Page view can display the appearance of the print result of the Word2007 document, which mainly includes headers, footers, graphic objects, column settings, page margins and other elements. It is the page view closest to the print result. 2. Reading layout view Reading layout view displays Word2007 documents and Office in the column style of a book

How to create mdf file Feb 18, 2024 pm 01:36 PM

MDF file is a common database file format and it is one of the main files of Microsoft SQL Server database. In database management systems, MDF files are used to save the main data of the database, including tables, indexes, stored procedures, etc. Creating an MDF file is one of the key steps in creating a database. Some common methods will be introduced below. Using SQLServerManagementStudio(SSMS)SQLServerManag

How to create a folder on Realme Phone? Mar 23, 2024 pm 02:30 PM

Title: Realme Phone Beginner’s Guide: How to Create Folders on Realme Phone? In today's society, mobile phones have become an indispensable tool in people's lives. As a popular smartphone brand, Realme Phone is loved by users for its simple and practical operating system. In the process of using Realme phones, many people may encounter situations where they need to organize files and applications on their phones, and creating folders is an effective way. This article will introduce how to create folders on Realme phones to help users better manage their phone content. No.

See all articles