what is mysql view

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 表名 ;

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

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

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 [...] );

Copy after login

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

Delete view

DROP VIEW 视图名称;

Copy after login

View mechanism

Replacement formula

Reification formula

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 ;

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参数（三个）

用参数创建视图

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语句中引用了不可更新视图
• 只要视图当中的数据不是来自于基表，就不能够直接修改