(8) view in mysql, view in mysql

(8) view in mysql, view in mysql
Concept

  ViewIt is a virtual table, which is basically the same as a normal table for users who use views. The view does not exist in the database, and the data in the view is dynamically generated.
Advantages of the optimize tables view over normal tables:

• Simple: when using a view, you do not need to worry about the corresponding Structure Conditions of the following table. For users, a view is a filtered result set.
• Security: users can only access the result set they are allowed to query.
• Data independence: Once the view structure is determined, changes to the actual table have no impact on the view User.

View operations

View operations are dividedCreate View,Modify View,Delete View,View view Definitions.

Create view and modify View

# Create a view create [or replace] [algorithm = {undefined | merge | temptable}] view view_name [(column_list)] as select_statement [with [cascaded | local] check option] # modify the view 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;

Note:

• The from keyword cannot be followed by a subquery;
• Views that cannot be updated: Include Aggregate functions/group/distinct/having/union, and constant views. select contains subqueries, jion, and from, which cannot be updated, where subquery references the table in the from clause.
• With [cascaded | local] check option definitely allows you to update data so that records no longer meet view conditions. WhereLocal-you only need to meet the conditions of this view to update,Cascaded-update can only be performed if all the view conditions for this view are met.. The default value is cascaded.

View data

Same as normal table

select * from view_test;

;

Delete View

# Delete a view drop view [if exists] view_name [, view_name2]... [restrict | cascade] # example drop view view_test;

View view status

From MySQL5.1, the show tables command not only displays the table name but also the view name.