View

 

1.Usage of View

create view [view_name]
as [select_query]

-- select query statement
select u.username, r.rolename from staff u
LEFT JOIN role_staff g
on u.userid = g.userid
LEFT JOIN role r
on g.roleid = r.roleid;

-- Create a view for this select query statement
create view staff_role_view
as (select u.username, r.rolename from staff u
LEFT JOIN role_staff g
on u.userid = g.userid
LEFT JOIN role r
on g.roleid = r.roleid)
;

Query View

select * from staff_role_view;

Show information of the view

DESC staff_role_view;

DESCRIBE staff_role_view;

2.Advantages of View

(1) Simplify data query operations and improve efficiency: View users don’t need to care about the corresponding logic within the view (association relationships, filter conditions), so a complex and frequently used SQL doesn’t need to be written repeatedly.

(2) Make the data in the base table safe: View users can only access the result set which they are allowed to query. Different views can be set for different users. For example, we can create a view to implement that a user can only obtain the name and age data of the staff table but not the gender data.

(3) Achieve data independence: Once the structure of the view is determined, the impact of changes in the talbe structure on users can be shielded. Adding columns to the source table has no impact on the view. If the source table changes its column name, it can be solved by modifying the view without affecting the view users.