MySQL视图创建、修改、删除、查看

#视图：虚拟表 通过表动态生成的数据 只保存sql逻辑 不保存查询结构

#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id
WHERE s.stuname LIKE '张%';

/*
应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
*/
CREATE VIEW v1
AS 
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id;

SELECT stuname,majorname FROM v1 WHERE s.stuname LIKE '张%';


#一、创建视图
/*
create view 视图名
as
查询语句；
*/

#案例：查询邮箱中包含a字符的员工名、部门名和工种信息
CREATE VIEW v3
AS 
SELECT last_name,department_name,j.*
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON j.job_id=e.job_id;

SELECT last_name,department_name,job_id,job_title,min_salary,max_salary
FROM v3 WHERE email LIKE '%a%';


#案例：查询各部门的平均工资级别
CREATE VIEW v5
AS 
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT v.*,grade_level
FROM v5 v
INNER JOIN job_grades j
ON v.ag BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#案例：查询平均工资最低的部门信息

SELECT MIN(ag),d.*
FROM v5
INNER JOIN departments d ON d.department_id=v5.department_id;

#案例：查询平均工资最低的部门名和工资

SELECT department_name ,MIN(ag)
FROM v5
INNER JOIN departments d ON d.department_id=v5.department_id;

/*
视图的好处
1、重用sql语句
2、简化复杂的sql操作，不必知道它的查询细节
3、保护数据，提高安全性
*/


#二、视图的修改
#方式一
/*
create or replace view 试图名  #如果存在则替换，如果不存在则创建
as 
查询语句;
*/
CREATE OR REPLACE VIEW v5
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#方式二
/*
alter view 试图名
as 
查询语句；

*/
ALTER VIEW v5
AS
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

#三、删除视图
/*
drop view 视图名,视图名,....;
*/

DROP VIEW v3,v5;

#四、查看视图

DESC v5;

SHOW CREATE VIEW v5;