what is mysql view

##The operating environment of this tutorial: Windows 10 system, MySQL5.7 version, Dell G3 computer.

What is a MySQL view? View meaning and usage analysis

What is a view

A view is a virtual table whose content is defined by a query.
• Like a real table, a view contains a series of named column and row data.
• Row and column data come from the table referenced by the custom view's query and are dynamically generated when the view is referenced.
• Simply speaking, a view is a table composed of select results.

As an exampleUse the query table command

SELECT * FROM 表名 ;

If you find out, you can see a table, and what you see is called a view.

Characteristics of views

A view is a reference to several basic tables, a virtual table, and a query statement execution the result of.
• It does not store specific data (if the basic table data changes, the view will also change accordingly).
• It can perform addition, deletion, modification and query operations just like the basic table (the addition, deletion and modification operations are subject to conditional restrictions).

The role of the view

Improve security: Create a view and define the data operated by the view. Then bind the user permissions to the view. This method uses a feature: the grant statement can grant permissions to the view.
• 
  For example: the data queried by the administrator contains the password of each user, and the administrator does not want the user to see the password, he can create a view so that only the user can see the administrator. I want him to see the data
to improve query performance.
• Improves the independence of data.

Create a view

Suppose we have a student table as follows

Requirements: Create a view and query students older than 20

CREATE VIEW stu_age_view 
AS(SELECT * FROM stu WHERE age>20);

Click the view to open and you can see the table you just created

The content in the table is the requirement content
Based on the view, we can continue to query the desired content, such as querying people named ls who are over 21 years old. View-based query can improve efficiency and reduce operating costs.

Modify view

CREATE OR REPLACE VIEW 视图名  AS(SELECT  [...]  FROM [...] );

For example: the view "stu_age_view" created above is for students over 20 years old, now it is modified to all students .

CREATE OR REPLACE VIEW stu_age_view 
AS(SELECT * FROM stu );

Delete view

DROP VIEW 视图名称;

View mechanism

Replacement formula

When operating a view, the view name is directly replaced by the view definition

Reification formula

mysq |The result of view execution is first obtained, and the result forms an intermediate result and is temporarily stored in memory.

The outer select statement calls these intermediate results (temporary tables)

The difference between substitution and concreteness

• Replacement formula: Replacement method, after replacing the view formula, it is processed as a whole sq|.
• Specification: Specification method, first process the view results, and then process the external query requirements.

Let’s give an example to deepen our understanding When creating the view above, we have created a view that requires students older than 20

Then let’s check this view

SELECT * FROM stu_age_view ;

If it is

replacement, then its internal operating logic is

SELECT * FROM (SELECT *FROM stu WHERE age >20) tihuan;

直接将stu_age_view的代码替换出来；
如果是具化式，那么它就是先把符合条件的查询出来放在一张表（内存）里，然后直接查询这张表。

(SELECT * FROM stu WHERE age >20) AS TEMPTABLE;SELECT * FROM TEMPTABLE;

上面这两条语句无法运行，只是为了方便举例提出。

那么我们再说回创建视图

ALGORITHM参数（三个）

merge	TEMPTABLE	UNDEFINED
处理方式替换式，可以进行更新真实表中的数据	具化式，由于数据存储在临时表中，所以不可以进行更新操作	没有定义ALGORITHM参数，mysq更倾向于选择替换方式，因为它更加有效。

用参数创建视图

CREATE ALGORITHM = MERGE VIEW stu_age_viewAS(SELECT * FROM stu WHERE age >20);

还有两个需要注意的东西是

• WITH CHECK OPTION

更新数据时不能插入或更新不符合视图限制条件的记录。
比如上面我们查询了一张年龄大于20的视图，那么在这张视图里面更新数据时如果年龄小于20则会报错。

• LOCAL和CASCADED

为可选参数，决定了检查测试的范围，默认值为CASCADED

视图不可更新部分

• 聚合函数
• DISTINCT关键字
• GROUP BY子句
• HAVING子句
• UNION运算符
• FROM子句中包含多个表
• SELECT语句中引用了不可更新视图
• 只要视图当中的数据不是来自于基表，就不能够直接修改