MySQL数据库视图入门篇

一、基本语法

1. 创建视图

视图是一张虚拟表，仅存储查询逻辑，不存储实际数据。

创建视图：CREATE [OR REPLACE] VIEW 视图名 AS SELECT语句;

说明一：OR REPLACE 是可选项，表示如果视图已存在则覆盖替换。

说明二：AS 后面紧跟封装数据的查询语句（基表来源）。

创建示例：

CREATE OR REPLACE VIEW stu_1 AS SELECT id, name FROM student WHERE id <= 10;
or replace //创建视图可以不加
create or replace view //视图创建
as+基表 //连接数据（对数据进行封装）
select id, name from student where id <= 10 ; //数据来源

2. 查询视图

• 查看创建视图的完整 SQL 语句（显示定义细节/默认参数）：

  查询视图：SHOW CREATE VIEW 视图名;

• 查看视图封装好的数据：

  查询视图：SELECT * FROM 视图名 [WHERE 条件...];

  说明：视图结果仅包含封装时选取的字段（如只选了 id 和 name，则查询结果不会出现 sex 等未封装的字段），但后续可以对该结果追加 WHERE、ORDER BY 等条件。

3. 修改视图

• 方式一（覆盖式）：

  覆盖原内容：CREATE OR REPLACE VIEW 视图名 AS 新SELECT语句;

• 方式二（专用修改语法）：

  修改内容：ALTER VIEW 视图名 AS 新SELECT语句;

  说明：修改后，可通过 SELECT * FROM 视图名 验证最新结果。

4. 删除视图

删除视图：DROP VIEW [IF EXISTS] 视图名;

说明一：IF EXISTS 为可选项，加上后只在视图存在时才执行删除。

说明二：删除视图不会影响基表中的原始数据。

二、检查选项（WITH CHECK OPTION）

1. 视图插入数据的问题

①创建视图：
create or replace view stu_1 as select id , name from student where id <= 20 ;
②查询视图：
select * from stu_1 ;
③插入新数据：
insert into stu_1 values(6 , `Tom`) ;
//新插入的数据会增加在视图的基表：student上，通过查询视图可以查看
④插入不满足视图查询条件的数据：
insert into stu_1 values(30 , `Tom`) ;
//插入的数据会增加在视图的基表上，无法通过查询视图查看，因为创建视图的条件为id<=20

说明一：插入满足条件的数据（如 id=6）会成功，且能通过视图查询到。

说明二：插入不满足条件的数据（如 id=30）虽然也会成功插入基表，但无法通过该视图查询到（因为被 WHERE 条件过滤了）。

2. 使用检查选项强制过滤

在创建视图语句末尾加上 WITH CHECK OPTION，MySql会通过视图检查正在修改的每一行，筛选符合视图定义（条件）的语句， 可阻止插入不符合视图条件的数据。

筛选语句：WITH CASCADED/LOCAL CHECK OPTION

示例：

①创建视图：
create or replace view stu_1 as select id , name from student where id <= 20 with cascaded chock option ;
②插入语句：
insert into stu_1 values(30 , `Tom`) ;

结果：此时执行 INSERT INTO stu_1 VALUES(30, 'Tom'); 会被数据库拒绝，阻止添加数据插入基表，也不会被检索到，因为id=30 > 20 。

3. CASCADED 与 LOCAL 的区别

检查选项分为 CASCADED（级联）和 LOCAL（本地），默认为 CASCADED。

• CASCADED（级联）：
  当通过视图插入/修改数据时，不仅检查当前视图的条件，还会强制检查该视图所依赖的所有上游视图的条件（即便上游视图未显式添加 WITH CHECK OPTION，也会被级联加上检查约束，但不影响上游视图原本的创建语句）。
  示例：

  ①创建视图v1：
  create view v1 as select id , name from student where id <= 20 ;
  ②在视图v1的基础上创建视图v2：
  create view v2 as select id , name from v1 where id > 10 with cascaded chock option ;
  //创建视图v2后，筛选条件为v1和v2的交集： id > 10 和  id <= 20 

  过程：v1 无检查选项（条件 id <= 20），在 v1 基础上创建 v2（条件 id > 10）并带上 WITH CASCADED CHECK OPTION。
  结果：插入数据时，会同时校验 id > 10 和 id <= 20（交集）。

• LOCAL（本地）：
  只检查当前视图自身的条件；对于上游依赖视图，仅当上游视图本身带有 WITH CHECK OPTION 时才会去检查，否则跳过。
  示例：

  ①创建视图v3：
  create view v3 as select id , name from student where id <= 20 ;
  ②在视图v3的基础上创建视图v4：
  create view v4 as select id , name from v3 where id > 10 with local chock option ;
  //创建视图v4后，筛选条件为id > 10，因为v3创建时没有检查选项 

  过程：v3 无检查选项（条件 id <= 20），在 v3 基础上创建 v4（条件 id > 10）并带上 WITH LOCAL CHECK OPTION。
  结果：插入数据时，仅检查 id > 10，因为 v3 没有检查选项，故不检查 id <= 20。

三、视图的更新限制与作用

1. 可更新视图的条件

视图中的行与基表中的行必须存在 一一对应关系，即不能改变原表行数的行为。 
如果定义视图的查询语句存在以下操作，则视图不可更新（无法 INSERT / UPDATE / DELETE）：

• 聚合函数或窗口函数（如 SUM()、MIN()、MAX()、COUNT() 等）

• 去重关键字 DISTINCT

• 分组子句 GROUP BY 或 HAVING

• 使用 UNION 或 UNION ALL 进行结果集合并（纵向增加行）

2. 视图的主要作用

• 简化操作：将复杂的多表联查或频繁使用的条件封装成视图，避免每次编写繁杂的 SQL。

• 数据安全：通过视图屏蔽基表中的敏感字段（如手机号、邮箱），让特定用户仅能操作视图暴露的数据。

• 逻辑数据独立性：当基表结构发生变化时（如将字段 name 改名为 stu_name），可通过修改视图定义（使用 AS stu_name 别名）保持原有查询结果不变，从而屏蔽底层变更对上层应用的影响。

四、综合案例

案例一：屏蔽敏感字段

题目：为保证数据库表安全，需要让开发人员在操作tb_user表时，只能看到用户的基础字段，屏蔽用户的手机号和邮箱两个字段。
tb_user表如下：

实操：

①创建视图：
create or replace view tb_user_view as 
select id,name,profession,age,gender,status,createtime 
from tb_user ;
②查询视图：
select * from tb_user_view ;

案例二：简化多表联查

题目：查询每个学生所修的课程，课程设计三张表：student（学生表）、course（课程表）、student_course（选课关系表），需要将三张表的关联查询封装成视图，便于后续重复使用 。
student学生表：

course课程表：

student_course选课表：

实操：

①创建表联查：
select a.id,b.name student_name,c.name course_name 
from student_course as a,student as b,course as c 
where a.studentid = b.id and a.courseid = c.id;
[规划写法：
select a.id,b.name student_name,c.name course_name
from student_course as a 
inner join student as b on a.studentid = b.id 
inner join course as c on a.courseid = c.id;]
②创建视图：
create view tb_stu_course_view as 
select a.id,b.name student_name,c.name course_name 
from student_course as a,student as b,course as c 
where a.studentid = b.id and a.courseid = c.id;
③查询视图：
select * from tb_stu_course_view；