一、视图
1、视图
MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的, 只保存了sql逻辑,不保存查询结果。
2、应用场景
(1)多个地方用到同样的查询结果;
(2)该查询结果使用的 SQL 语句比较复杂;
3、示例
4、视图的好处
(1)重用 SQL 语句;
(2)简化复杂的 SQL 操作,不必知道它的查询细节;
(3)保护数据,提高安全性;
二、操作视图
1、创建视图
语法:
1 2 3 | create view view_name As select_statement [ with |cascaded| local | check option ] |
案例:
(1)查询姓名中包含a字符的员工名、部门名和工种信息
① 创建视图
1 2 3 4 5 6 | 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 j.job_id = e.job_id; |
② 使用
1 | SELECT * FROM v1 WHERE last_name LIKE '%a%' ; |
(2)查询各部门的平均工资级别
① 创建视图查看各个部门的平均工资
1 2 3 4 5 | CREATE VIEW v2 AS SELECT AVG (salary) ag, department_id FROM employees GROUP BY department_id; |
② 使用视图
1 2 3 4 | SELECT v2.`ag`, g.grade_level FROM v2 JOIN job_grades g ON v2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`; |
(3)查询平均工资最低的部门信息
1 | SELECT * FROM v2 ORDER BY ag LIMIT 1; |
(4)查询平均工资最低的部门名和工资
1 2 3 4 5 6 7 8 9 | CREATE VIEW v3 AS SELECT * FROM v2 ORDER BY ag LIMIT 1; SELECT d.*, v.ag FROM v3 v JOIN departments d ON v.`department_id` = d.`department_id`; |
2、修改视图
方式一:
语法:
1 2 3 | create [ or replace ] view view_name As select_statement [ with |cascaded| local | check option ] |
案例:
1 2 3 4 5 6 7 | CREATE OR REPLACE VIEW v3 AS SELECT AVG (salary), job_id FROM employees GROUP BY job_id; SELECT * FROM v3; |
方式二:
语法:
1 2 3 | alter view 视图名 as 查询语句; |
案例:
1 2 3 | ALTER VIEW v3 AS SELECT * FROM employees; |
3、查看视图
方式一:
语法:
1 | DESC view_name; |
方式二:
如果需要查询某个视图的定义,可以使用下面命令进行查看:
语法:
1 | show create view view_name \G |
4、删除视图
用户可以一次删除一个或者多个视图,前提是必须有该视图的 drop 权限。
语法:
1 | drop view [if exists] view_name,view_name …[ restrict | cascade ] |
5、更新视图
(1)插入
1 | INSERT INTO myv1 VALUES ( '张飞' , 'zf@qq.com' ); |
(2)修改
1 | UPDATE myv1 SET last_name = '张无忌' WHERE last_name= '张飞' ; |
(3)删除
1 | DELETE FROM myv1 WHERE last_name = '张无忌' ; |
插入、修改、删除对视图进行修改时也会对影响原始表。
视图的可更新性和视图中查询的定义有关系,以下类型的视图是不能更新的。
包含以下关键字的sql语句:分组函数、 distinct、 group by、having、 union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
测试:
① 包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
创建视图:
1 2 3 4 5 6 7 | CREATE OR REPLACE VIEW myv1 AS SELECT MAX (salary) m,department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; |
测试:更新
1 | UPDATE myv1 SET m=9000 WHERE department_id=10; |
报错:
② 常量视图
创建视图:
1 2 3 4 5 | CREATE OR REPLACE VIEW myv2 AS SELECT 'john' NAME ; SELECT * FROM myv2; |
测试:更新
1 | UPDATE myv2 SET NAME = 'lucy' ; |
③ Select中包含子查询
创建视图:
1 2 3 4 | CREATE OR REPLACE VIEW myv3 AS SELECT department_id,( SELECT MAX (salary) FROM employees) 最高工资 FROM departments; |
测试:更新
1 2 | SELECT * FROM myv3; UPDATE myv3 SET 最高工资=100000; |
④ join
创建视图:
1 2 3 4 5 6 | CREATE OR REPLACE VIEW myv4 AS SELECT last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; |
测试:更新
1 2 | UPDATE myv4 SET last_name = '张飞' WHERE last_name= 'Whalen' ; INSERT INTO myv4 VALUES ( '陈真' , 'xxxx' ); |
⑤ from 一个不能更新的视图
创建视图:
1 2 3 | CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv3; |
测试:更新
1 | UPDATE myv5 SET 最高工资=10000 WHERE department_id=60; |
⑥ where子句的子查询引用了from子句中的表
创建视图:
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE VIEW myv6 AS SELECT last_name,email,salary FROM employees WHERE employee_id IN ( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); |
测试:更新
1 2 | SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing' ; |
三、其他
1、视图和表的对比
2、
四、练习
1、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
1 2 3 4 5 | CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROM employees WHERE phone_number LIKE '011%' ; |
2、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX (salary) mx_dep,department_id FROM employees GROUP BY department_id HAVING MAX (salary)>12000; SELECT d.*,m.mx_dep FROM departments d JOIN emp_v2 m ON m.department_id = d.`department_id`; |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?