视图

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_activestatus=1条件。

local

仅检查 “当前视图” 的查询条件,不递归检查底层依赖的视图条件。

以 “视图链(视图 A 依赖视图 B,视图 B 依赖表 T)” 为例,当通过视图 A 修改数据时:仅检查数据是否符合 “当前视图 A” 的查询条件;不检查 “底层视图 B” 的查询条件;只要符合当前视图 A 的条件,就允许修改(即使不符合底层视图 B 的条件)。

local检查逻辑简单,性能高,但可能出现 “数据不在底层视图” 的矛盾情况。

  • 若视图用于 “数据录入”(如仅允许录入活跃用户),且需确保数据始终在视图中,用CASCADED(默认模式,更安全);
  • 若视图是 “临时筛选层”(如仅筛选张姓用户),底层视图的条件由其他逻辑控制,用LOCAL(更灵活,性能更高)。

3.更新及作用

通过视图间接更新数据表,但并非所有视图都可更新,需满足 “视图与底层表的映射关系是唯一的”(即每行视图数据对应底层表的唯一一行)。

一个视图被认为是可更新的,通常需要满足以下条件:

(1)基于单表(核心条件)
  • 视图的 FROM 子句必须只包含一个基表或另一个可更新视图。
  • 反例:如果视图是由 JOIN 多个表创建的,那么在大多数数据库系统中,它默认是不可直接更新的(除非使用触发器或特定规则,如 PostgreSQL 的 INSTEAD OF 触发器)。
(2)不包含聚合函数或分组
  • 视图的查询中不能包含 GROUP BYHAVING 子句,以及 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,但 UPDATEDELETE 可能不受影响。

一个视图可以被更新(INSERT/UPDATE/DELETE),当且仅当它长成这样:

CREATE VIEW 视图名 AS
SELECT 列1, 列2, 列3... 
FROM 单张表 
WHERE 简单的过滤条件;

即使是可更新视图,也存在以下关键限制,需在业务中规避:

  1. 无法更新 “视图未包含的字段”:若视图未包含salary字段,无法通过视图更新salary(如UPDATE v_user_active SET salary=6000会报错,因salary不在视图中)。
  2. 多表关联视图的更新限制:若视图来自多表关联(如user关联
    dept),仅允许更新 “单个表的字段”,无法同时更新两个表的字段。

    例:视图v_user_dept包含user.name和dept.name,仅能单独更新user.name或dept.name,无法在一条UPDATE中同时更新两者。

  3. NOT NULL约束的影响:若底层表字段有NOT NULL约束,但视图未包含该字段,通过视图INSERT时会报错(因无法为NOT NULL字段赋值)。

视图的核心价值在于 “封装与隔离”:

  • 对 “数据安全”,封装 “非敏感字段”,隔离敏感数据;
  • 对 “复杂查询”,封装 “多表关联 / 计算逻辑”,隔离底层复杂度;
  • 对 “统一口径”,封装 “指标计算规则”,隔离业务逻辑差异。
分类: Java-Backend 标签: MySQL

评论

暂无评论数据

暂无评论数据

目录