mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

14.1 创建和使用视图

一、什么是视图？

视图（View）是一种虚拟的表，其内容由查询定义。与真实的表不同，视图不存储数据，它只是在执行时动态地从基础表中获取数据。你可以把视图看作是一个“保存的SQL查询”，它提供了一个窗口，让你以特定的方式查看表中的数据。

例如，如果你经常需要查询学生的姓名及其班级名称，可以创建一个视图封装这个连接查询，之后就可以像查表一样简单地查询视图。

为什么需要使用视图？

简化复杂查询：将多表连接、嵌套子查询等复杂逻辑封装在视图中，之后每次只需查询视图即可，无需重复编写复杂的SQL语句。
增强数据安全性：通过视图可以隐藏表中的某些敏感列，只向用户暴露必要的数据。例如，员工表中可以创建一个不包含薪资列的视图，普通用户只能看到姓名、部门等信息。
逻辑数据独立性：当基础表的结构发生变化时（例如拆分或重命名列），只需修改视图定义，而不需要修改所有依赖该表的应用程序代码。
数据一致性：视图始终反映基础表的最新数据，因为每次查询视图都会重新执行内部的SQL，不会展示过期的快照。

二、创建视图的基本语法

CREATE [OR REPLACE] VIEW 视图名 [(列名列表)]
AS 查询语句
[WITH CHECK OPTION];

OR REPLACE：如果视图已存在，则替换它。
列名列表：可选，用于为视图的列指定别名。如果不提供，则使用查询中的列名。
查询语句：任何合法的 SELECT 语句，可以包含连接、子查询、分组等。
WITH CHECK OPTION：用于可更新视图，防止对视图的修改超出视图定义的范围（稍后详述）。

示例数据

我们沿用之前的 students 和 classes 表：

students

id	name	class_id
1	张三	101
2	李四	102
3	王五	101

classes

class_id	class_name
101	计算机1班
102	软件2班

三、创建简单视图

示例1：创建学生-班级视图

CREATE VIEW student_class AS
SELECT s.id, s.name AS student_name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

现在，我们可以像查询普通表一样查询这个视图：

SELECT * FROM student_class;

结果：

id	student_name	class_name
1	张三	计算机1班
2	李四	软件2班
3	王五	计算机1班

示例2：指定视图列名

CREATE VIEW student_class2 (stu_id, stu_name, class) AS
SELECT s.id, s.name, c.class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.class_id;

查询：

SELECT stu_id, stu_name FROM student_class2;

四、使用视图

视图的使用与表几乎完全相同，可以在 SELECT、INSERT、UPDATE、DELETE 语句中使用（但可更新性有限制）。

查询视图

SELECT * FROM student_class WHERE class_name = '计算机1班';

视图与表一起使用

SELECT v.student_name, s.age
FROM student_class v
JOIN students s ON v.id = s.id;

五、更新视图（可更新视图）

视图并非总是可更新的。如果视图满足以下条件，则可以对其执行 INSERT、UPDATE、DELETE 操作，这些操作会直接作用于基础表：

视图的查询中：

没有使用聚合函数（如 SUM、AVG、MAX 等）。
没有使用 DISTINCT、GROUP BY、HAVING、UNION、UNION ALL。
没有使用子查询（某些情况下有限制）。
没有使用连接（某些数据库允许简单连接，但 MySQL 允许使用连接？实际上 MySQL 中如果视图使用连接，则不可更新）。

视图中的列必须直接映射到基础表的列（不能是表达式或函数）。

可更新视图示例

CREATE VIEW active_students AS
SELECT id, name, class_id
FROM students
WHERE status = 'active';  -- 假设有 status 列

-- 可以通过视图更新
UPDATE active_students SET name = '张三丰' WHERE id = 1;

不可更新视图示例

上面的 student_class 视图包含了连接，因此不可更新。尝试更新会报错。

WITH CHECK OPTION 的作用

当对视图进行插入或更新时，WITH CHECK OPTION 会确保新数据仍然满足视图定义中的 WHERE 条件。例如：

CREATE VIEW active_students AS
SELECT id, name, class_id
FROM students
WHERE status = 'active'
WITH CHECK OPTION;

此时，如果尝试通过视图将某行的 status 更新为 'inactive'，操作将被拒绝，因为这会使该行不再属于视图的范围。

六、查看和删除视图

查看已有视图

与查看表类似，可以使用：

SHOW TABLES;  -- 也会列出视图

或者更精确地：

SHOW FULL TABLES WHERE Table_type = 'VIEW';

查看视图的定义：

SHOW CREATE VIEW 视图名;

删除视图

DROP VIEW [IF EXISTS] 视图名;

例如：

DROP VIEW IF EXISTS student_class;

七、视图的注意事项

性能：视图本身不存储数据，每次查询都会执行其内部的 SQL。如果视图定义复杂，频繁查询可能影响性能。
依赖关系：如果基础表结构发生变化（如删除列），视图可能失效。此时需要重新创建或修改视图。
索引：不能直接在视图上创建索引，但可以利用基础表的索引。
权限：可以授予用户对视图的访问权限，而不授予对基础表的权限，从而实现数据隐藏。

八、综合练习

基于 orders 表（包含 order_id、customer_id、amount、order_date）和 customers 表（包含 customer_id、name），创建一个视图显示每个订单的详细信息（包括客户姓名），并确保视图是可更新的？注意：如果视图包含连接，则不可更新，因此这里只作为查询视图。

需求：

创建视图 order_details，包含订单ID、客户姓名、订单金额、订单日期。
查询该视图，筛选出金额大于1000的订单。
尝试通过视图更新订单金额，观察是否成功。

解答：

-- 1. 创建视图（含连接，不可更新）
CREATE VIEW order_details AS
SELECT o.order_id, c.name AS customer_name, o.amount, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- 2. 查询
SELECT * FROM order_details WHERE amount > 1000;

-- 3. 尝试更新（会失败）
UPDATE order_details SET amount = 2000 WHERE order_id = 1;
-- 报错：不可更新视图

小结

视图是基于 SQL 查询的虚拟表，不存储数据。
创建视图使用 CREATE VIEW 语句，可简化复杂查询、增强安全性和逻辑独立性。
视图可以像表一样查询，但更新受限制（只有简单视图可更新）。
使用 WITH CHECK OPTION 可以强制视图的更新操作符合视图定义。
了解视图的局限性，合理使用视图可以提高开发效率和系统可维护性。