根据尚硅谷的视频教程学习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 视图名
五、更新视图
- 插入数据,注意,插入到视图中的数据也会被插入到原始表中
INSERT INTO 视图名 VALUES ( , , ,)
- 修改数据,注意,修改到视图中的数据也会在原始表中被修改
update 视图名 SET 字段名 = 新值 WHERE 筛选条件
- 删除数据,注意,视图中删除的数据也会在原始表中被删除
DELETE FROM 视图名 WHERE 筛选条件
具备以下特点的视图不允许修改
1 包含以下关键字的sql语句:分组函数,distinct,group by,having,union或者union all
2 常量视图
3 select 中包含子查询
4 join语句
5 from 一个不能更新的视图
6 where子句的子查询引用了from子句中的表
六、练习
- 创建视图 empv1,要求查询电话号码以 ‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW empv1
AS
SELECT last_name, salary, email, phone_number
FROM employees
WHERE phone_number LIKE '011%'
查询结果为:
- 创建视图 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
查询结果为: