MySQL view is a virtual table whose contents are defined by a query; the view contains a series of named column and row data, and the row and column data comes from the table referenced by the query that customizes the view, and in the reference view Dynamically generated, simply put, the 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.
Suppose we have a student table as follows
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.
CREATE OR REPLACE VIEW 视图名 AS(SELECT [...] FROM [...] );
CREATE OR REPLACE VIEW stu_age_view AS(SELECT * FROM stu );
DROP VIEW 视图名称;
The outer select statement calls these intermediate results (temporary tables)
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);
还有两个需要注意的东西是
更新数据时不能插入或更新不符合视图限制条件的记录。
比如上面我们查询了一张年龄大于20的视图,那么在这张视图里面更新数据时如果年龄小于20则会报错。
为可选参数,决定了检查测试的范围,默认值为CASCADED
【相关推荐:mysql视频教程】
The above is the detailed content of what is mysql view. For more information, please follow other related articles on the PHP Chinese website!