9-视图

# 视图的创建

USE myemployees;

/*
语法：
CREATE VIEW 视图名
AS 
查询语句;

*/


# 查询邮箱中包含a字符的员工名，部门名，和工种信息

# 创建视图（这里使用的sql92连接查询方法，还可以使用sql99 JOIN ON）
CREATE VIEW v1
AS
SELECT CONCAT(first_name,' ',last_name) 姓名, department_name 部门名,job_title 工种,email 
FROM employees e,jobs j, departments d
WHERE e.`job_id`=j.`job_id` AND e.`department_id`=d.`department_id` ;
# 使用视图
SELECT * FROM v1 WHERE email LIKE '%a%';


# 查询各部门的平均工资级别
# 首先创建视图查看每个部门的平均工资
CREATE VIEW v2
AS
SELECT AVG(salary) ag,department_id id
FROM employees
GROUP BY department_id;
# 利用视图查询各个部门的平均工资等级
SELECT department_name 部门名称,ag 平均工资 FROM v2
JOIN departments ON id=department_id

# 视图的修改

# 方式一
/*
这种方法表示如果视图存在，就根据后续的语句进行修改，如果不存在就新建视图

CREATE OR REPLACE VIEW 视图名
AS
查询语句;


*/

SELECT * FROM v2;

CREATE OR REPLACE VIEW v2
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

# 方式二
/*

语法：
ALTER VIEW 视图名
AS
查询语句;

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

# 视图的删除

/*
语法：
DROP VIEW 视图名,视图名,...

允许一次删除多个，但删除需要具备权限

*/

DROP VIEW v1,v2;

# 查看视图结构
DESC v1; 
# 或者
SHOW CREATE VIEW v1;

# 视图的更新

CREATE OR REPLACE VIEW v3
AS 
SELECT last_name,email
FROM employees;

SELECT * FROM v3;

# 简单视图支持增删改,并且增删改视图之后，会对利用到的原表进行更新
# 显然这是不安全的，所以我们一般会为视图设置权限，避免对其进行增删改操作，而是只查模式
# 添加
INSERT INTO v3 VALUES('Leslie','2333@gmail.com');
# 修改
UPDATE v3 SET email='@qq.com' WHERE last_name='Leslie';
# 删除
DELETE FROM v3 WHERE last_name='Leslie';

# 具备以下特点的视图不允许更新
/*

1. 包含以下关键字的sql语句：分组函数，DISTINCT.GROUP BY,HAVING,UNION
2. 常量视图
3. SELECT 中包含子查询
4. JOIN
5. FROM一个不能更新的视图
6. WHERE字句的子查询引用了FROM字句中的表

*/