视图

视图

1. 含义：虚拟表，通过表动态生成的数据虚拟表

2. 创建视图语法：

   create view 视图名
   as
   复杂的查询语句;

3. 特点
   . 视图中只有查询逻辑，没有存储数据
   . 具有增删改查的功能，但是一般不建议增删改

   # 1. 创建视图
   ## 查询邮箱中包含a的员工名，部门名，工种信息
   DROP VIEW IF EXISTS myv1;
   CREATE VIEW myv1
   AS 
   SELECT last_name,department_name,job_title,email
   FROM employees e
   JOIN departments d ON e.`department_id`=d.`department_id`
   JOIN jobs j ON e.`job_id`=j.`job_id`
   
   SELECT last_name,department_name,job_title
   FROM myv1 
   WHERE email LIKE '%a%'
   
   ## 查询各部门平均工资级别
   CREATE VIEW myv2
   AS
   SELECT department_id,AVG(salary) ag
   FROM employees
   GROUP BY department_id
   
   SELECT myv2.*,g.grade_level
   FROM myv2
   JOIN job_grades g
   ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;
   
   ##查询最低平均工资的部门信息
   SELECT * FROM myv2 ORDER BY ag LIMIT 0,1;
   
   ##查询最低平均工资的部门名和工资
   SELECT d.department_name,m.ag
   FROM departments d
   JOIN (
   	SELECT * FROM myv2 ORDER BY ag LIMIT 0,1
   ) m
   ON d.`department_id`=m.department_id;
   
   # 2. 视图修改
   /*
   1. 方式一
   	create or replace view 视图名
   	as 
   	查询语句;
   2. 方式二
   	alter view 视图名
   	as 
   	查询语句
   */
   
   #3. 删除视图
   /*
   drop view 视图名,视图名,...
   */
   
   #4.查看视图
   /*
   方式一：desc 视图名;
   方式二：show create view 视图名;
   */
   
   # 5. 视图更新（会把原始表也同步更新）
   /*
   insert into 视图名 values(..),(..)
   update 视图名 set 列名=值 [条件]
   delete from 视图名[条件]
   */