Analysis of mysql View Function and Usage Example

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

Generally speaking, a view is the result set returned after an SELECT statement is executed.

// Single table view 
  CREATE [ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]
    VIEW  View name  [( Attribute list )]
    AS SELECT  Statement 
    [WITH [CASCADED|LOCAL] CHECK OPTION];
  // Returns the result of the query as 1 Virtual tables, which will change according to the changes in the database 
  CREATE VIEW work_view(ID, Name, Addr) AS SELECT id,name,address FROM work;
  // Create views on multiple tables 
  //ALGORITHM=MERGE ALGORITHM Have 3 The parameters are: merge , TEMPTABLE , UNDEFINED ( merge Merge tables, temptable Unable to 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;

View and table have a 1-to-1 relationship: if there are no other constraints (such as fields not in the view and required fields in the basic table), you can add, delete and modify data;

The relationship between view and table is 1-to-many: if only the data of one table is modified and there are no other constraints (such as the fields that are not in the view and are required fields in the basic table), the data modification operation can be carried out, such as the following statement, and the operation is successful;

Differences between views and temporary tables

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 and a virtual table Temporary tables are objective table type objects Create TEMPORARY table Their structure is one table and one snapshot. Shortcut that can look like 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

Temporary tables are visible only on the current connection, and will automatically drop when this connection is closed. In the same query statement, you can only look up the temporary table once. The show tables statement does not enumerate temporary tables, but lists memory tables. You cannot rename a temporary table with rename. However, you can replace it with alter table:

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 table structure is not lost. Memory tables can also be regarded as one kind of temporary tables.

Establishment 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

For more readers interested in MySQL related content, please check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Related Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.