Analysis of mysql view functions and usage examples

This article uses examples to illustrate the functions and usage of MySQL views. Share with you for your reference, the details are as follows:

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

//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 as 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 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;

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

The view and the table are in a one-to-one relationship : If there are no other constraints (such as fields that are not in the view but are required fields in the basic table), data addition, deletion and modification operations can be performed;

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

Difference between views and temporary tables

• A view is just a precompiled SQL statement and does not store actual data.
• Temporary tables are actual tables stored in tempdb.
• The allocation of physical space is different. Try not to allocate space. Temporary tables will allocate space.
• A view is a snapshot, a virtual table
• A temporary table is an objective table type object. Create TEMPORARY table
• Their structures are a table and a snapshot. You can think of a view as a shortcut to a joint table.

Creating a Temporary Table

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

Import query results directly into a temporary table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

The temporary table is only visible to the current connection and will be automatically dropped when the connection is closed. You can only search a temporary table once in the same query statement. The show tables statement does not list temporary tables, but it does list memory tables. You cannot use rename to rename a temporary table. However, you can alter table instead:

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

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

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

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