视图

视图是虚拟表：只保存查询 SQL 逻辑、不存储数据，可用来简化复杂查询、控制字段可见范围、屏蔽真实表结构的变化。

一、介绍

  • 视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
  • 通俗地讲，视图只保存了查询的 SQL 逻辑，不保存查询结果，所以创建视图时主要工作就落在创建这条 SQL 查询语句上。

二、语法

操作语法
创建CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
查看创建语句SHOW CREATE VIEW 视图名称;
查看视图数据SELECT * FROM 视图名称 ...;
修改方式一CREATE [OR REPLACE] VIEW ...（重建覆盖）
修改方式二ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [...]
删除DROP VIEW [IF EXISTS] 视图名称 [, 视图名称] ...

完整演练：

-- 创建视图
CREATE OR REPLACE VIEW stu_v_1 as SELECT id, name from student WHERE id <= 10;

-- 查询视图
SHOW CREATE VIEW stu_v_1;          -- 查看创建视图的语句
SELECT * FROM stu_v_1;             -- 查询视图数据
SELECT * FROM stu_v_1 WHERE id < 3;

-- 修改视图
CREATE OR REPLACE VIEW stu_v_1 as SELECT id, name, no from student WHERE id <= 10;
ALTER VIEW stu_v_1 as SELECT id, name FROM student where id <= 10;

-- 删除视图
DROP View if EXISTS stu_v_1;

视图名称后可直接跟列名列表 视图名称(列名列表)，用于给查询出的列起别名。

三、检查选项（WITH CHECK OPTION）

使用 WITH CHECK OPTION 子句创建视图时，MySQL 会通过视图检查正在更改的每个行（插入、更新、删除），使其符合视图的定义；MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。检查范围由两个选项决定：CASCADED 和 LOCAL，默认值为 CASCADED。

选项检查范围
WITH CASCADED CHECK OPTION检查本视图自身的条件，并级联检查其依赖的所有视图的条件
WITH LOCAL CHECK OPTION只检查本视图自身的条件，不检查依赖视图的条件

未加 CHECK OPTION 的视图，自己 where 里的条件不参与校验——插入的行只是「在视图中看不到」，并不会被拦下。

3.1 CASCADED（默认）

-- ① 视图自身带检查选项
CREATE OR REPLACE VIEW stu_v_1 as SELECT id, name from student WHERE id <= 20 WITH CASCADED CHECK OPTION;
INSERT INTO stu_v_1 VALUES(6, 'Tom');
INSERT INTO stu_v_1 VALUES(30, 'Tom');

-- ② 重建 stu_v_1（去掉检查选项），再基于它建带级联检查的 stu_v_2
CREATE OR REPLACE VIEW stu_v_1 as SELECT id, name from student WHERE id <= 20;
INSERT INTO stu_v_1 VALUES(5, 'Tom');
INSERT INTO stu_v_1 VALUES(25, 'Tom');
CREATE OR REPLACE VIEW stu_v_2 as SELECT id, name from stu_v_1 WHERE id >= 10 WITH CASCADED CHECK OPTION;
INSERT INTO stu_v_2 VALUES(7, 'Tom');
INSERT INTO stu_v_2 VALUES(26, 'Tom');
INSERT INTO stu_v_2 VALUES(15, 'Tom');

-- ③ 基于 stu_v_2 再建一层（不带检查选项）
CREATE OR REPLACE VIEW stu_v_3 as SELECT id, name from stu_v_2 WHERE id <= 15;
INSERT INTO stu_v_3 VALUES(11, 'Tom');
INSERT INTO stu_v_3 VALUES(17, 'Tom');
INSERT INTO stu_v_3 VALUES(28, 'Tom');

视图层级：student ← stu_v_1(id ≤ 20) ← stu_v_2(id ≥ 10, CASCADED) ← stu_v_3(id ≤ 15)，实测结果如下：

语句结果原因
insert into stu_v_1 values(6)成功6 ≤ 20，满足 stu_v_1
insert into stu_v_1 values(30)失败30 > 20 违反 stu_v_1 → CHECK OPTION failed 'stu_v_1'
insert into stu_v_1 values(5) / (25)成功stu_v_1 已去掉检查选项，自身条件不再校验
insert into stu_v_2 values(7)失败违反 stu_v_2 自身条件（7 ≥ 10 不成立）
insert into stu_v_2 values(26)失败26 满足 stu_v_2，但级联检查 stu_v_1（26 ≤ 20 不成立）
insert into stu_v_2 values(15)成功同时满足 stu_v_2（≥ 10）与 stu_v_1（≤ 20）
insert into stu_v_3 values(11)成功级联链上 stu_v_2（≥ 10）、stu_v_1（≤ 20）均满足
insert into stu_v_3 values(17)成功stu_v_3 无检查选项，自身 id ≤ 15 不校验；stu_v_2、stu_v_1 均通过
insert into stu_v_3 values(28)失败级联链上 stu_v_1 的 id ≤ 20 不成立（28 > 20）

3.2 LOCAL

-- stu_v_4 不带检查选项
CREATE OR REPLACE VIEW stu_v_4 as SELECT id, name from student WHERE id <= 15;
INSERT INTO stu_v_4 VALUES(5, 'Tom');
INSERT INTO stu_v_4 VALUES(16, 'Tom');

-- 基于 stu_v_4 建带 LOCAL 检查选项的 stu_v_5
CREATE OR REPLACE VIEW stu_v_5 as SELECT id, name from stu_v_4 WHERE id >= 10 WITH LOCAL CHECK OPTION;
INSERT INTO stu_v_5 VALUES(13, 'Tom');
INSERT INTO stu_v_5 VALUES(17, 'Tom');
INSERT INTO stu_v_5 VALUES(25, 'Tom');
INSERT INTO stu_v_5 VALUES(7, 'Tom');

-- 基于 stu_v_5 再建一层（不带检查选项）
CREATE OR REPLACE VIEW stu_v_6 as SELECT id, name from stu_v_5 WHERE id <= 20;
INSERT INTO stu_v_6 VALUES(14, 'Tom');

视图层级：student ← stu_v_4(id ≤ 15) ← stu_v_5(id ≥ 10, LOCAL) ← stu_v_6(id ≤ 20)，实测结果如下：

语句结果原因
insert into stu_v_4 values(5) / (16)成功stu_v_4 无检查选项，16 只是「在视图里看不到」
insert into stu_v_5 values(13)成功满足 stu_v_5 自身条件（13 ≥ 10）
insert into stu_v_5 values(17) / (25)成功虽然超过 stu_v_4 的 id ≤ 15，但 LOCAL 不校验依赖视图
insert into stu_v_5 values(7)失败违反 stu_v_5 自身条件 → CHECK OPTION failed 'stu_v_5'
insert into stu_v_6 values(14)成功stu_v_6 无检查选项，按链上 stu_v_5（≥ 10）校验通过

对比一句话：同样是「满足本视图、但超出依赖视图」的值（如 17、25），CASCADED 会被拦下，LOCAL 放行。

3.3 查看检查选项

SHOW CREATE VIEW stu_v_2;   -- 定义中带 WITH CASCADED CHECK OPTION

select table_name, check_option, is_updatable
from information_schema.views where table_schema = 'itheima';
-- check_option 取值：NONE / LOCAL / CASCADED

四、视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

#不可更新的情形
1聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT() 等）
2DISTINCT
3GROUP BY
4HAVING
5UNION 或者 UNION ALL
-- 使用聚合函数创建视图
create view stu_v_count as select count(*) from student;

-- 该视图不可插入
insert into stu_v_count values(10);
-- [HY000][1471] The target table stu_v_count of the INSERT is not insertable-into

五、作用

作用说明
简单视图不仅可以简化用户对数据的理解，也可以简化他们的操作；那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
安全数据库可以授权，但不能授权到数据库特定行和特定的列上；通过视图，用户只能查询和修改他们所能见到的数据
数据独立视图可帮助用户屏蔽真实表结构变化带来的影响

六、案例

① 屏蔽敏感字段

为保证数据库表的安全性，开发人员在操作 tb_user 表时，只能看到用户的基本字段，屏蔽手机号和邮箱两个字段：

CREATE OR REPLACE VIEW tb_user_view as SELECT id, name, age, gender from tb_user;

② 简化多表联查

查询每个学生所选修的课程（三张表联查），该功能在很多业务中都有使用，定义一个视图简化操作：

CREATE OR REPLACE VIEW sc_view as
SELECT s.name student, c.name cours, s.no no
from student s, student_course sc, course c
WHERE s.id = sc.studentid and c.id = sc.courseid;

SELECT * FROM sc_view;

速查

-- 创建 / 修改视图（OR REPLACE 表示存在则覆盖）
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

-- 修改视图的另一种写法
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

-- 查看视图
SHOW CREATE VIEW 视图名称;      -- 查看创建视图的语句（含 WITH ... CHECK OPTION）
SELECT * FROM 视图名称;          -- 查看视图数据
select table_name, check_option, is_updatable
from information_schema.views where table_schema = '库名';   -- NONE / LOCAL / CASCADED

-- 删除视图
DROP VIEW [IF EXISTS] 视图名称 [, 视图名称] ...;

-- 检查选项：写入的行必须满足视图条件
CREATE OR REPLACE VIEW stu_v_1 as SELECT id, name from student WHERE id <= 20 WITH CASCADED CHECK OPTION;
INSERT INTO stu_v_1 VALUES(6, 'Tom');     -- 满足 id <= 20 → 成功
INSERT INTO stu_v_1 VALUES(30, 'Tom');    -- CHECK OPTION failed 'stu_v_1'

-- 不可更新视图：含聚合函数
create view stu_v_count as select count(*) from student;
insert into stu_v_count values(10);       -- [HY000][1471] ... is not insertable-into
场景关键点
频繁使用的复杂查询定义成视图，调用方不必每次写全部条件
只想开放部分列 / 行用视图控制可见范围（授权到视图而非底表）
视图写入要校验条件WITH CHECK OPTION；默认 CASCADED（级联检查依赖视图），LOCAL 只查自身
视图不能写含聚合 / 窗口函数、DISTINCT、GROUP BY、HAVING、UNION [ALL]

相关

最近更新:
Contributors: liujw