<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 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 [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;
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.
Same as ordinary table
select * from view_test;
#删除视图 drop view [if exists] view_name [,view_name2]...[restrict|cascade] #举例 drop view view_test;
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!