What is a view?
For example, if there is a basic table, user 1 can view the data and can view fields 1 and 2, and user 2 can view the fields 3 and 4, then these two users There are different views for the same table
First of all, from two perspectives, one is the system perspective and the other is the user perspective
System perspective
The view is a virtual table, that is, the data corresponding to the view is not actually stored. Only the definition of the view is stored in the database. When the data of the view is operated, the system is based on The definition of the view to operate the basic table associated with the view
user perspective
View is a table derived from one or more tables (or views). It can be queried, modified, deleted and updated just like a table.
View operation
# Syntax
# Create view
mysql>: create view view name[(aliases)] as select statement;
eg>: create view v1 as select dep, max(salary) from emp group by dep;< br />
# Create or replace view
mysql>: create or replace view name[(alias)] as select statement;
mysql>: alter view name[(alias)] as select statement;
eg>: create or replace view v1(dep_name, max_salary) as select dep, max(salary) from emp group by dep;
eg>: alter view v1(name, salary) as select dep, max(salary) from emp group by dep;
# Delete view
mysql>: drop view view name
eg>: drop view v1;
# Views can be used as normal tables to complete table-join query
select name, dep_name, salary
from emp join v1
on emp.dep=v1.dep_name and emp.salary= v1.max_salary;
# Prerequisite: View additions, deletions, and modifications can be directly mapped to real tables (essentially operating on real tables)
# Views can be added, deleted, and modified. The essence of deletion is to directly operate on the real table of the created view.
create or replace view v2 as select id,name,age, salary from emp;
update v2 set sa lary=salary+1 where id=1;
delete from v2 where id=1;
create or replace view v3 as select * from emp;
insert into v3 values( 1,'yangsir','male', 66, 1.11,'Shanghai','Naga','Faculty Department');
# Summary: Operating the view will affect the real table, The opposite will also affect
update emp set salary=salary+1 where id=1;
Advantages of View
1. Simplify data Query and processing improve reusability, just like a function
2. Refactor the database without affecting the operation of the program (database reconstruction, encapsulating a view, the same as the previous table structure)< /p>
3. Simplify the management of user permissions, improve security, and different views for different users
4. Simplify queries, make data clearer, and facilitate data sharing