MySQL 二轮学习笔记·进阶篇·(四) 视图
视图
1. 基本语法
创建视图
create [or replace] view 视图名 [(列1...)]
as
select 查询语句
[with [cascaded | local] check option]
CREATE OR REPLACE VIEW v_user_simple
AS
SELECT id, name, phone
FROM user
WHERE status = 1; -- 仅包含“状态为正常(status=1)”的用户
CREATE OR REPLACE VIEW v_dept_emp_stats (dept_id, dept_name, emp_count, avg_salary)
AS
SELECT
d.id AS dept_id,
d.name AS dept_name,
COUNT(u.id) AS emp_count, -- 员工数
AVG(u.salary) AS avg_salary -- 平均薪资
FROM dept d
LEFT JOIN user u ON d.id = u.dept_id -- 关联部门和员工表
GROUP BY d.id, d.name; -- 按部门分组
查询视图
语法与查表相同
修改视图
alter view 视图名 as 查询语句
删除视图
drop view if exists 视图1...
2.检查选项
cascaded
cascaded级联检查是检查选项的默认模式,核心逻辑是:不仅检查当前视图的查询条件,还会递归检查所有底层依赖的视图的查询条件
假设存在 “视图链”:视图A
依赖视图B
,视图B
依赖底层表T
。当通过视图A
修改数据时:先检查数据是否符合视图A
的查询条件;再递归检查数据是否符合视图B
的查询条件;只有都符合,才允许修改;否则拒绝。
例子:
创建一个依赖v_user_active
的视图v_user_active_zh
(仅活跃的 “张姓用户”),用CASCADED
:
-- 创建底层user表
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
status TINYINT -- 0:禁用,1:活跃
);
-- 创建视图v_user_active(仅活跃用户),带CASCADED检查
CREATE OR REPLACE VIEW v_user_active
AS
SELECT id, name, status
FROM user
WHERE status = 1 -- 视图条件:仅活跃用户
WITH CASCADED CHECK OPTION;
-- 创建视图v_user_active_zh(依赖v_user_active,仅张姓活跃用户)
CREATE OR REPLACE VIEW v_user_active_zh
AS
SELECT id, name, status
FROM v_user_active -- 依赖底层视图v_user_active
WHERE name LIKE '张%' -- 当前视图条件:张姓
WITH CASCADED CHECK OPTION;
尝试通过v_user_active_zh
更新数据,触发级联检查:
-- 1. 先插入一条张姓活跃用户(符合两个视图条件)
INSERT INTO v_user_active_zh (name, status) VALUES ('张三', 1);
-- 2. 尝试通过v_user_active_zh将“张三”的name改为“李四”
UPDATE v_user_active_zh
SET name = '李四'
WHERE id = 1;
更新被拒绝,CASCADED
会递归检查:先检查当前视图v_user_active_zh
的条件:name LIKE '张%'
→ 更新后name=李四
不符合,直接拒绝;若当前视图条件符合,还会检查底层视图v_user_active
的status=1
条件。
local
仅检查 “当前视图” 的查询条件,不递归检查底层依赖的视图条件。
以 “视图链(视图 A 依赖视图 B,视图 B 依赖表 T)” 为例,当通过视图 A 修改数据时:仅检查数据是否符合 “当前视图 A” 的查询条件;不检查 “底层视图 B” 的查询条件;只要符合当前视图 A 的条件,就允许修改(即使不符合底层视图 B 的条件)。
local检查逻辑简单,性能高,但可能出现 “数据不在底层视图” 的矛盾情况。
- 若视图用于 “数据录入”(如仅允许录入活跃用户),且需确保数据始终在视图中,用
CASCADED
(默认模式,更安全); - 若视图是 “临时筛选层”(如仅筛选张姓用户),底层视图的条件由其他逻辑控制,用
LOCAL
(更灵活,性能更高)。
3.更新及作用
通过视图间接更新数据表,但并非所有视图都可更新,需满足 “视图与底层表的映射关系是唯一的”(即每行视图数据对应底层表的唯一一行)。
一个视图被认为是可更新的,通常需要满足以下条件:
(1)基于单表(核心条件)
- 视图的
FROM
子句必须只包含一个基表或另一个可更新视图。 - 反例:如果视图是由
JOIN
多个表创建的,那么在大多数数据库系统中,它默认是不可直接更新的(除非使用触发器或特定规则,如 PostgreSQL 的INSTEAD OF
触发器)。
(2)不包含聚合函数或分组
- 视图的查询中不能包含
GROUP BY
、HAVING
子句,以及SUM()
,COUNT()
,AVG()
,MAX()
,MIN()
等聚合函数。 - 原因:聚合操作将多行数据汇总成一行,无法确定如何将修改反向应用到原始的明细行上。
(3)不包含集合操作
- 视图的查询中不能包含
UNION
,UNION ALL
,INTERSECT
,EXCEPT
等集合操作。 - 原因:结果集来自多个独立的查询,引擎无法确定数据具体来自哪个基表的哪部分。
(4)不包含窗口函数(分析函数)
- 虽然窗口函数不像聚合函数那样会减少行数,但它们通常用于计算,不直接对应基表的可修改列,因此通常也会导致视图不可更新。
(5)不包含 DISTINCT
关键字
- 使用
DISTINCT
去重后,可能无法唯一确定要修改的基表行。 - 反例:
CREATE VIEW v AS SELECT DISTINCT col1 FROM table1;
(6)不包含计算列或常量
- 视图中选择的列必须是基表中简单的列引用。不能是表达式、常量或函数计算的结果。
- 可更新:
SELECT emp_id, emp_name, salary FROM employees;
(salary
是基表列) - 不可更新:
SELECT emp_id, emp_name, salary * 1.1 AS new_salary FROM employees;
(new_salary
是计算列) - 不可更新:
SELECT ‘Staff’ AS type, emp_name FROM employees;
(type
是常量)
(7)必须包含基表的所有不能为空的列(针对 INSERT
操作)
- 这是针对
INSERT
操作的特殊要求。如果你要通过视图插入一条新记录,视图必须包含基表中所有被定义为NOT NULL
且没有默认值的列。否则,数据库无法完成插入,因为它无法为那些缺失的必需列提供值。 - 注意:即使视图满足其他所有条件,但缺少必要的
NOT NULL
列,你仍然可能无法进行INSERT
,但UPDATE
和DELETE
可能不受影响。
一个视图可以被更新(INSERT/UPDATE/DELETE),当且仅当它长成这样:
CREATE VIEW 视图名 AS
SELECT 列1, 列2, 列3...
FROM 单张表
WHERE 简单的过滤条件;
即使是可更新视图,也存在以下关键限制,需在业务中规避:
- 无法更新 “视图未包含的字段”:若视图未包含
salary
字段,无法通过视图更新salary
(如UPDATE v_user_active SET salary=6000
会报错,因salary
不在视图中)。 多表关联视图的更新限制:若视图来自多表关联(如user关联
dept),仅允许更新 “单个表的字段”,无法同时更新两个表的字段。例:视图v_user_dept包含user.name和dept.name,仅能单独更新user.name或dept.name,无法在一条UPDATE中同时更新两者。
NOT NULL
约束的影响:若底层表字段有NOT NULL
约束,但视图未包含该字段,通过视图INSERT
时会报错(因无法为NOT NULL
字段赋值)。
视图的核心价值在于 “封装与隔离”:
- 对 “数据安全”,封装 “非敏感字段”,隔离敏感数据;
- 对 “复杂查询”,封装 “多表关联 / 计算逻辑”,隔离底层复杂度;
- 对 “统一口径”,封装 “指标计算规则”,隔离业务逻辑差异。
本文系作者 @xiin 原创发布在To Future$站点。未经许可,禁止转载。
暂无评论数据