Shulou(Shulou.com)06/01 Report--

This paper gives an example to illustrate the function and usage of mysql view. Share with you for your reference, the details are as follows:

Generally speaking, a view is the result set returned after the execution of a SELECT statement.

/ / single table view CREATE [ALGORITHM] = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view name [(attribute list)] AS SELECT statement [WITH [CASCADED | LOCAL] CHECK OPTION]; / / return the query result as a virtual table and change the CREATE VIEW work_view (ID, Name, Addr) AS SELECT id,name,address FROM work according to the change of the database / / create a view on multiple tables / / ALGORITHM=MERGE ALGORITHM has three parameters: merge, TEMPTABLE, UNDEFINED (merge merge table, temptable cannot update information, undefined) CREATE ALGORITHM=MERGE VIEW work_view2 (ID,NAME,SALARY) AS SELECT work.id,name,salary FROM work,salary WHERE work.id=salary.id WITH LOCAL CHECK OPTION

Convenient operation, especially query operation, reduce complex SQL statements and enhance readability

There is an one-to-one relationship between the view and the table: if there are no other constraints (such as fields that are not in the view, which are required in the basic table), you can add, delete and modify data.

There is an one-to-many relationship between the view and the table: if you modify the data of only one table without other constraints (such as fields that are not in the view, which are required in the basic table), you can change the data, such as the following statement. The operation is successful.

The difference between views and temporary tables

View is just a pre-compiled SQL statement, and does not save the actual data temporary table is stored in tempdb the actual table physical space allocation is different, try not to allocate space, temporary table view is a snapshot, is a virtual table temporary table is an objective table type object Create TEMPORARY table their structure is a table, a snapshot. A shortcut that can think of a visual image as a federated table

Create a temporary table

CREATE TEMPORARY TABLE tmp_table (name VARCHAR (10) NOT NULL,value INTEGER NOT NULL)

Import query results directly into temporary tables

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

The temporary table is visible only on the current connection and will automatically drop when the connection is closed. You can look up temporary tables only once in the same query statement. The show tables statement does not enumerate temporary tables, but lists memory tables. You can't rename a temporary table with rename. However, you can alter table instead:

Memory table: the table structure is built on disk and the data is in memory. When the service is stopped, the data in the table is lost, but the structure of the table is not lost. Memory tables can also be seen as a kind of temporary tables.

Creation of memory table:

CREATE TEMPORARY TABLE tmp_table (name VARCHAR (10) NOT NULL,value INTEGER NOT NULL) TYPE = HEAP

Note: TYPE = HEAP is required.

Memory tables must use the memory storage engine

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.