1. View

• Concept

① View , It can be regarded as a virtual table , Itself does not store data . The essence of view , It can be regarded as stored SELECT sentence

② In the view SELECT The table involved in the statement , Called the base table

③ Do... For the view DML operation , It will affect the data in the corresponding base table . vice versa .

④ Deletion of the view itself , It will not cause the deletion of data in the base table .

⑤ Application scenarios of view ： For small projects , Views are not recommended . For large projects , Consider using views .

⑥ Benefits of views ： Simplify queries ; Control data access

1.1 Create view

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW  View name  [( Field list )] AS  Query statement  [WITH [CASCADED|LOCAL] CHECK OPTION]

create view  View name 
as  Query statement 

--  Create view 
CREATE VIEW v1
as
SELECT employee_id,last_name,salary
FROM atguigudb.employees 
WHERE department_id=80
--  Query view 
SELECT * FROM v1;
-- or REPLACE  View substitution exists    （） Specify alternate field name 
CREATE OR REPLACE VIEW v1(e_id,e_name,e_salary)
as
SELECT employee_id,last_name,salary
FROM atguigudb.employees 
WHERE department_id=80

--  Create a view based on the view 
CREATE VIEW v2
as
SELECT e_id,e_name
FROM v1

SELECT * from v2

--  Multiple tables associated query 
create OR REPLACE VIEW morev
as
SELECT e.employee_id,e.last_name,d.department_name
from atguigudb.departments d , atguigudb.employees e
WHERE d.department_id=e.department_id

SELECT * FROM morev

CREATE OR REPLACE VIEW morev2
as
SELECT e.employee_id,e.last_name,d.department_name
FROM atguigudb.departments d LEFT join atguigudb.employees e
ON d.department_id=e.department_id

--  Format view 
CREATE or REPLACE VIEW depart
as
SELECT CONCAT(e.last_name,'(',d.department_name,')') as emp_dept
from atguigudb.departments d , atguigudb.employees e
WHERE d.department_id=e.department_id

select * 
FROM depart

1.2 Update the view

select * FROM v1


# Update data for view , This will lead to the modification of data in the base table 
# Empathy , Update the data in the table , It will also lead to the modification of the data in the view 

# Delete data from view , It will also lead to the deletion of data in the table 

--  Update the view 
update v1
set e_name="DFP"
WHERE e_id=145

--  Modify the view 
--  It can be used or REPLACE
SHOW TABLES;

#6.  Delete view 


DROP VIEW v1

DROP VIEW IF EXISTS v1