[数据库]-视图

视图

含义

虚拟表，可以和普通表一样使用。MySQL5.1版本出现的新特性，是通过表动态生成的数据，也就是说行和列的数据都来自定义识图的查询中使用的表，只保存sql逻辑，不保存查询结果

类似于Java中包装一个方法/函数

应用场景

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句较为复杂

视图的创建

视图的好处

• 有利于重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

# 视图的创建

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字句中的表

*/