Introduce the concept and operation of views in mysql

Concept

 <a href="http://www.php.cn/code/8708.html" target="_blank">View</a> is a virtual table, for users who use view , basically the same as using a normal table. The view does not exist in the database, and the data in the view is dynamically generated.
 Advantages of views over ordinary tables:

• Simplicity: When using views, you do not need to care about the corresponding structural conditions of subsequent tables. For users, views are filtered results. set.

• Security: Users can only access the result sets for which they are allowed queries.

• Data independence: Once the view structure is determined, changes to the actual table have no impact on view users.

View operations

View operations are divided into Create view, Modify view, <a href="http://www.php.cn/php/php-tp-remove.html" target="_blank">Delete </a>View, View view definition.

Create view, modify 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 as
select t1.sid,t1.username,t2.department
from test1 t1 left join test2 t2 on t1.sid=t2.sid;

Create view

Note:

• The from keyword cannot contain a subquery;

• The view that cannot be updated: contains aggregate function/group/distinct /having/union, constant view, select contains subquery, jion, from a view that cannot be updated, where subquery refers to the table in the from clause.

• with[cascaded|local] check option Absolutely allows updating data so that the record no longer meets the view conditions. Among them, local-only the conditions of this view can be updated, cascaded-the conditions of all views for this view must be met before can be updated. The default is cascaded.

View view data

Same as ordinary table

select * from view_test;

View View data

Delete view

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

#举例
drop view view_test;

View view status

Starting from MySQL5.1, when using the show tables command Not only the table names but also the view names are displayed.

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Introduce the concept and operation of views in mysql. For more information, please follow other related articles on the PHP Chinese website!