MySQL学习记录 - 13-视图view

根据尚硅谷的视频教程学习MySQL，学习记录-13 - 视图view。

含义：虚拟表，但是和普通表一样使用，只保存了sql逻辑，不保存查询结果，对应不占用物理空间
应用场景：

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

一、创建视图

CREATE VIEW 视图名 
as
查询语句;

案例1：查询姓名中包含a字符的员工名、部门名和工种信息

#将3张表合并的结果作为一个视图
CREATE VIEW v1
AS
SELECT last_name, department_name, job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;
#直接查询视图表中目标信息
SELECT * FROM v1 where last_name like '%a%';

结果如下：

案例2：查询各部门的平均工资级别

#创建视图，体现每个部门的平均工资
CREATE VIEW v2
AS
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;
#使用视图的结果，查询工资级别
SELECT department_id, grade
FROM v2 
JOIN sal_grade s
ON v2.avg_salary BETWEEN min_salary AND max_salary;

结果如下：

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

#在案例2中已经创建了视图：查询每个部门的平均工资，此处直接使用
SELECT * FROM v2 ORDER BY avg_salary LIMIT 1;

结果为：

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

#可以将案例3中的结果创建为新的视图
CREATE VIEW v3
AS
SELECT * FROM v2 ORDER BY avg_salary LIMIT 1;
#利用视图3的结果连接部门表，查看信息
SELECT d.*, v3.avg_salary
FROM departments d
JOIN v3
ON v3.department_id = d.department_id

结果为：

视图的好处：

1 重用SQL语句
2 简化复杂sql的操作，不必知道查询细节
3 保护数据，提高安全性

二、修改视图

#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;

案例1：更新v3试图，查询每个工种的平均工资

CREATE OR REPLACE VIEW v3
AS
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;
#检查数据
SELECT * FROM v3;

结果为：

#方式二
ALTER VIEW 视图名
AS
查询语句；

案例2：更改查询3为 employees表所有信息

ALTER VIEW v3
AS
SELECT * FROM employees;

此时检查视图3的结果为：

三、删除视图

DROP VIEW 视图名,视图名,.....;

四、查看视图

DESC 视图名

五、更新视图

1. 插入数据，注意，插入到视图中的数据也会被插入到原始表中

INSERT INTO 视图名 VALUES ( , , ,)

2. 修改数据，注意，修改到视图中的数据也会在原始表中被修改

update 视图名 SET 字段名 = 新值 WHERE 筛选条件

3. 删除数据，注意，视图中删除的数据也会在原始表中被删除

DELETE FROM 视图名 WHERE 筛选条件

具备以下特点的视图不允许修改
1 包含以下关键字的sql语句：分组函数，distinct，group by，having，union或者union all
2 常量视图
3 select 中包含子查询
4 join语句
5 from 一个不能更新的视图
6 where子句的子查询引用了from子句中的表

六、练习

1. 创建视图 empv1,要求查询电话号码以 ‘011’开头的员工姓名和工资、邮箱

CREATE OR REPLACE VIEW empv1
AS
SELECT last_name, salary, email, phone_number
FROM employees
WHERE phone_number LIKE '011%'

查询结果为：

2. 创建视图 empv2, 要求查询部门的最高工资高于12000的部门信息

CREATE OR REPLACE VIEW empv2
AS
SELECT d.*, MAX(salary)
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
HAVING max(salary) >12000

查询结果为：