MYSQL：视图

探索MySQL的“任意门”：深入理解视图 (View)

在数据库操作中，我们经常会遇到一些需要反复执行的复杂查询，比如多表连接。每次都重写一遍不仅效率低下，还容易出错。为了解决这个问题，MySQL提供了一个非常强大的功能——视图（View）。我们可以把视图理解成一张“虚拟表”，它像一扇“任意门”，让我们能够用简单的方式去访问复杂的查询结果。

1. 本文简述

• 了解视图的核心应用场景。
• 掌握如何创建和使用视图。

2. 什么是视图？

视图，本质上是一个基于特定查询结果集构建的虚拟表。

这里有几个关键点需要我们理解：

• 虚拟性：视图本身并不独立存储任何数据。我们看到的视图内容，实际上是它背后那条SELECT查询语句动态生成的结果。
• 依赖性：视图的数据完全依赖于它所引用的“基表”（也就是我们实际存储数据的那些表）。基表数据变了，视图查询出的结果也会跟着变。
• 操作性：尽管是虚拟的，但我们可以像操作普通表一样，对视图进行查询，甚至在满足特定条件时进行更新或删除操作。

3. 如何创建视图

创建视图的语法非常直观。

语法

CREATE VIEW view_name [(column_list)] AS select_statement;

小思考

这里的 CREATE VIEW 是固定的关键字，view_name 是我们为视图起的名字，而 AS 之后跟着的，就是定义这个视图核心逻辑的 SELECT 查询语句。我们甚至可以为视图的列预先指定一个列表 (column_list)，让它的列名和基表不同。

4. 视图的实践应用

接下来，我们通过几个具体的场景，来看看视图在实际工作中是如何发挥作用的。

场景一：简化复杂的多表查询

假设我们需要查询所有学生的详细信息及其各科考试成绩，这通常需要连接学生表、班级表、课程表和成绩表。

• 直接使用真实表查询

  -- 这是一条典型的多表连接查询
  select
    s.student_id, s.name, s.sno, s.age, s.gender, s.enroll_date,
    c.class_id, c.`name`,
    co.course_id, co.`name`,
    sc.score_id, sc.score
  from
    student s, class c, course co, score sc
  where
    s.class_id = c.class_id
    and sc.student_id = s.student_id
    and sc.course_id = co.course_id
  order by
    s.student_id;
  

思考与分析

在实际项目中，如果每次需要这些数据时，我们都去手写或复制上面这条复杂的SQL语句，会严重影响开发效率，而且难以维护。这时，最好的办法就是将这条查询封装成一个视图。

• 创建视图来封装查询

  我们可以用两种方式来创建这个视图：

  方式一：在 SELECT 语句中使用别名

  create view v_student_score as
  select
    s.student_id, s.name, s.sno, s.age, s.gender, s.enroll_date,
    c.class_id as class_id, c.`name` as class_name,
    co.course_id as course_id, co.`name` as course_name,
    sc.score_id as score_id, sc.score
  from
    student s, class c, course co, score sc
  where
    s.class_id = c.class_id
    and sc.student_id = s.student_id
    and sc.course_id = co.course_Id
  order by
    s.student_id;
  

  方式二：在视图名后直接指定列名

  create view v_student_score_v1 (id, name, sno, age, gender, enroll_date, class_id, class_name, course_id, course_name, score_id, score) as
  select
    s.student_id, s.name, s.sno, s.age, s.gender, s.enroll_date,
    c.class_id, c.`name`,
    co.course_id, co.`name`,
    sc.score_id, sc.score
  from
    student s, class c, course co, score sc
  where
    s.class_id = c.class_id
    and sc.student_id = s.student_id
    and sc.course_id = co.course_id;
  

小思考：关于列名冲突

在定义视图时，如果 SELECT 语句的结果集中包含了重名的列（例如，多个表都有 name 字段），我们就必须为这些列定义别名，以避免在最终的视图结果中产生冲突。上面的两种方式都有效地解决了这个问题。

• 查询视图

  创建好视图后，获取同样的数据就变得异常简单：

  select * from v_student_score;
  select * from v_student_score_v1;
  

小探讨：视图中的 ORDER BY 是个“坑”吗？

细心的朋友可能已经发现，v_student_score 视图的定义中包含了 ORDER BY 子句，而 v_student_score_v1 则没有。这是一个非常关键的区别，也是初学者容易遇到的一个“坑”。

• v_student_score (包含 ORDER BY)

  • 理论上：在标准SQL中，视图被视为一个无序的数据集合，就像表一样。因此，在 CREATE VIEW 语句中直接使用 ORDER BY 通常是不被允许的，或者会被数据库的优化器忽略。正确的做法应该是在查询视图时再指定排序规则，例如 SELECT * FROM v_student_score ORDER BY enroll_date;。
  • 在MySQL中：MySQL是个特例，它允许你在创建视图时使用 ORDER BY。但是，我们强烈不推荐这样做。因为当你的查询变得复杂（比如又对这个视图进行了 JOIN 操作），MySQL为了提升查询效率，很可能不再遵守视图中定义的排序规则。依赖它会导致不可预期的结果。

• v_student_score_v1 (不包含 ORDER BY)

  • 这种方式完全符合SQL标准和最佳实践。它清晰地分离了“数据是什么”（视图的定义）和“数据怎么看”（查询时的排序）。这才是更规范、更可靠的做法。

场景二：保护敏感数据

假设我们希望提供一个查询接口，只允许用户查看学生的姓名和总分，而隐藏像学号、各科成绩这样的敏感信息。

• 直接使用真实表查询

  select
    s.name,
    sum(sc.score) as total
  from
    student s, score sc
  where
    s.student_id = sc.student_id
  group by
    sc.student_id
  order by
    s.student_id;
  

思考与分析

如果直接开放对基表的查询权限，那么查询者可以轻易地在 SELECT 列表中添加 s.sno 等字段来获取他不该看到的信息。

• 通过创建视图实现访问控制

  create view v_student_total_points as
  select
    s.student_id,
    s.name,
    sum(sc.score) as total
  from
    student s, score sc
  where
    s.student_id = sc.student_id
  group by
    s.student_id
  order by
    s.student_id;
  

小思考：视图如何实现安全过滤

当我们创建了 v_student_total_points 这个视图后，它暴露给外界的就只有 student_id, name, 和 total 这三列。用户即使对这个视图执行 SELECT *，也无法获取到原始表中的其他任何字段。这样，视图就充当了一个安全过滤层，精准地控制了数据的访问权限。

• 视图与真实表的连接查询

  视图也可以像普通表一样，参与到更复杂的查询中。

  select * from v_student_total_points v, student s where v.student_id = s.student_id;
  

5. 修改视图与基表的数据

视图与基表之间的数据是联动的。

• 通过修改真实表，会影响视图

  我们来试试修改唐三藏的JAVA成绩为99分。

  update score set score = 99 where student_id = 1 and course_id = 1;
  

  操作完成后，再去查询我们之前创建的 v_student_score 视图，会发现唐三藏的成绩已经同步更新了。

  

• 通过修改视图，同样会影响基表

  现在，我们尝试通过视图来修改唐三藏的计算机网络成绩。

  update v_student_score set score = 99 where score_id = 3;
  -- 执行结果: ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
  

  

思考与分析

更新失败了！错误信息提示我们 UPDATE 和 ORDER BY 的使用不当。这正是因为我们在创建 v_student_score 视图时包含了 ORDER BY 子句，导致这个视图成为了一个不可更新的视图。

现在，我们换成之前创建的、没有 ORDER BY 的 v_student_score_v1 视图再试一次。

update v_student_score_v1 set score = 99 where score_id = 3;
-- 执行结果: Query OK, 1 row affected...

这次成功了！我们再去检查原始的 score 表，会发现数据已经被真实地修改了。

select * from score where student_id = 1 and course_id = 5;

5.1 注意事项：哪些视图不可更新？

从上面的例子我们可以看到，并非所有的视图都支持更新操作。总的来说，修改真实表会影响视图，反之亦然。

以下是一些常见的导致视图不可更新的情况：

• 视图的定义中包含了聚合函数（如 SUM(), COUNT()）。
• 包含了 DISTINCT、GROUP BY、HAVING 子句。
• 包含了 UNION 或 UNION ALL 操作。
• SELECT 列表中包含了子查询。
• FROM 子句中引用了另一个不可更新的视图。

6. 删除视图

如果一个视图不再需要，我们可以用 DROP VIEW 命令将其删除。

语法

drop view view_name;

7. 视图的优点总结

最后，我们来总结一下使用视图能带给我们哪些好处：

1. 简化性：将复杂的查询逻辑封装在视图中，让后续的数据调用变得极其简单。
2. 安全性：通过视图作为一道屏障，可以精确控制用户能访问哪些数据，隐藏敏感信息。
3. 逻辑数据独立性：当底层表的结构发生变化时（比如字段改名或拆分表），我们可能只需要修改视图的定义，而所有依赖这个视图的应用程序代码都无需改动。这实现了应用与数据库在一定程度上的解耦。
4. 列名重塑：视图允许我们为列重命名，使其更具可读性或符合特定的业务语境。