MySQL view function and usage example analysis

This article describes the functions and usage of mysql view. Share it for your reference, as follows:

In layman's terms, a view is the result set returned after execution of a SELECT statement.

//Single table view  CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
    VIEW View name [(Property List)]
    AS SELECT Statement
    [WITH [CASCADED|LOCAL] CHECK OPTION];
  //Return the query result into a virtual table, which will change according to the database changes  CREATE VIEW work_view(ID, Name, Addr) AS SELECT id,name,address FROM work;
  //Create views 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 ,name,salary FROM work,salary WHERE = WITH LOCAL CHECK OPTION;

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

Views and tables are one-to-one relationships: If there are no other constraints (such as fields that are not in the view, which are required fields in the basic table), you can add, delete and modify data operations;

Views and tables are one-to-many relationships: If you only modify the data of one table and there are no other constraints (such as fields that are not in the view are required fields in the basic table), you can perform data modification operations, such as the following statement, and the operation is successful;

The difference between view and temporary table

• The view is just a precompiled SQL statement and does not save the actual data
• Temporary tables are actual tables stored in tempdb
• The allocation of physical space is different. If you try not to allocate space, the temporary table will allocate space.
• The view is a snapshot, a virtual table
• Temporary table is an objective table type object Create TEMPORARY table
• Their structure is a table and a snapshot. You can use visual images as a shortcut to the joint table

Create a temporary table

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

Directly import query results into temporary table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

The temporary table is only visible in the current connection, and will automatically drop when the connection is closed. In the same query statement, you can only look up temporary tables once. The show tables statement does not enumerate temporary tables, but will list memory tables. You cannot 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, and the structure of the table will not be lost. Memory tables can also be regarded as a type of temporary table.

Establishment of memory tables:

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

Note: TYPE = HEAP must be available.

Memory tables must use memory storage engine

For more information about MySQL related content, please check out the topic of this site:MySQL query skills》、《Summary of MySQL transaction operation skills》、《MySQL stored procedure skills》、《Summary of MySQL database lock related skills"and"A summary of commonly used functions of MySQL》

I hope this article will be helpful to everyone's MySQL database calculation.