(8) View in MySQL

Concept

??视图 is a virtual existence table, which is basically the same as using a normal table for users who use views. The view does not exist in the database, and the data in the view is generated dynamically.
?? Advantages of views relative to ordinary tables:

• Simple: Using views does not need to be concerned with the corresponding structural conditions of the subsequent tables, and for the consumer, the view is a filtered result set.
• Security: Users can only access the result set that they are allowed to query.
• Data independence: Once the view structure is determined, changes to the actual table have no effect on the user of the image.

View Actions

View operations are divided into,,,, 创建视图 修改视图 删除视图 查看视图定义 .

Create a view, modify a view

#创建视图create  [or  replace ] [algorithm={undefined| Merge  |temptable}]view  view_name[(column_list)] as  select_statement[with  [ cascaded  | local ] check  option ] #修改视图 alter  [Algorithm={undefined| Merge  |temptable}]view  view_name[(column_list)] as  select_statement[with  [ cascaded  | local ] check  option ] 

Example:

create view view_test asselect t1.sid,t1.username,t2.departmentfrom test1 t1 left join test2 t2 on t1.sid=t2.sid;

Description

• The FROM keyword cannot contain sub-queries;
• Views that cannot be updated: Contains aggregate functions/group/distinct/having/union, constant views, select contains subqueries, jion,from a view that cannot be updated, where a subquery references a table in the FROM clause.
• With[cascaded|local] Check option is absolutely allowed to update the data so that the record no longer satisfies the view criteria. Among them local-只需满足本视图条件就可以更新 , cascaded-必须满足所有针对该视图的所有视图的条件才可以更新 . The default is cascaded.

Viewing View data

Same as normal table

select * from view_test;

；

Delete a view

#删除视图drop view [if exists] view_name [,view_name2]...[restrict|cascade]#举例drop view view_test;

View View State

Starting with MySQL5.1, the show Tables command is used to display not only the table name but also the view name.

(8) View in MySQL