视图

第九章：视图

💡 视图是数据库与用户交互的窗口，是一张基于查询的虚拟表

9.1 视图概念

9.1.1 什么是视图

定义：视图是建立在表的查询基础上的虚拟表，本身不存储数据，数据仍然存储在基表中。

视图的特点：

特点	说明
虚拟表	本身不存储数据
基于查询	是一个SELECT语句的封装
实时性	基表数据变化，视图自动更新
安全性	可以隐藏敏感数据

9.1.2 视图的用途

用途	说明	示例
数据安全	隐藏敏感字段	员工视图不显示工资
简化查询	封装复杂的多表连接	一个视图代替三表JOIN
数据独立	基表结构变化时，视图可以保持不变	逻辑独立性
权限控制	不同用户看不同的视图	学生只能看自己的成绩

9.2 创建视图

9.2.1 基本语法

CREATE VIEW 视图名 AS
SELECT语句;

-- 带WITH CHECK OPTION
CREATE VIEW 视图名 AS
SELECT语句
WITH CHECK OPTION;

9.2.2 简单视图

-- 创建只显示部分列的视图
CREATE VIEW 学生基本信息 AS
SELECT 学号, 姓名, 性别, 班级
FROM 学生;

-- 查询视图
SELECT * FROM 学生基本信息;

9.2.3 带条件的视图

-- 创建女生视图
CREATE VIEW 女生信息 AS
SELECT 学号, 姓名, 出生日期, 班级
FROM 学生
WHERE 性别 = '女';

-- 创建高分课程视图
CREATE VIEW 大于等于3学分课程信息 AS
SELECT * FROM 课程
WHERE 学分数 >= 3;

9.2.4 多表连接视图

-- 创建学生选课视图（三表连接）
CREATE VIEW 学生选课成绩视图 AS
SELECT
    学生.学号,
    学生.姓名,
    课程.课程名称,
    选课成绩.成绩
FROM 学生
INNER JOIN 选课成绩 ON 学生.学号 = 选课成绩.学号
INNER JOIN 课程 ON 选课成绩.课程编号 = 课程.课程编号;

-- 使用视图
SELECT * FROM 学生选课成绩视图 WHERE 姓名 = '张三';

9.2.5 带聚合函数的视图

-- 创建学生平均成绩视图
CREATE VIEW 学生平均成绩 AS
SELECT
    学号,
    AVG(成绩) AS 平均成绩,
    COUNT(*) AS 选课门数
FROM 选课成绩
GROUP BY 学号;

-- 使用视图
SELECT * FROM 学生平均成绩 ORDER BY 平均成绩 DESC;

9.2.6 WITH CHECK OPTION

💡 WITH CHECK OPTION 确保通过视图进行的数据修改必须满足视图的WHERE条件

-- 不带 WITH CHECK OPTION
CREATE VIEW 计算机学院学生 AS
SELECT * FROM 学生 WHERE 学院编号 = '01';

-- 可以插入不满足条件的数据（不安全！）
INSERT INTO 计算机学院学生 VALUES ('S999', '测试', '男', '02');
-- 插入成功，但在视图中看不到（学院编号='02'不满足条件）

-- ============================================

-- 带 WITH CHECK OPTION（推荐）
CREATE VIEW 计算机学院学生_安全 AS
SELECT * FROM 学生 WHERE 学院编号 = '01'
WITH CHECK OPTION;

-- 尝试插入不满足条件的数据
INSERT INTO 计算机学院学生_安全 VALUES ('S999', '测试', '男', '02');
-- 报错！CHECK OPTION failed

9.3 通过视图操作数据

9.3.1 通过视图查询

-- 视图查询与表查询完全相同
SELECT * FROM 学生基本信息;
SELECT * FROM 学生基本信息 WHERE 性别 = '男';
SELECT 姓名, 班级 FROM 学生基本信息 ORDER BY 班级;

9.3.2 通过视图插入数据

-- 通过视图插入数据（会插入到基表）
INSERT INTO 学生基本信息 (学号, 姓名, 性别, 班级)
VALUES ('S100', '新学生', '男', '22软件1班');

-- 验证：基表也有了这条数据
SELECT * FROM 学生 WHERE 学号 = 'S100';

9.3.3 通过视图修改数据

-- 通过视图修改数据
UPDATE 学生基本信息
SET 班级 = '22软件2班'
WHERE 学号 = 'S100';

-- 验证：基表数据也被修改
SELECT * FROM 学生 WHERE 学号 = 'S100';

9.3.4 通过视图删除数据

-- 通过视图删除数据
DELETE FROM 学生基本信息
WHERE 学号 = 'S100';

-- 验证：基表数据也被删除
SELECT * FROM 学生 WHERE 学号 = 'S100';

9.3.5 视图的可更新性限制

⚠️ 不是所有视图都可以更新！

不可更新的视图：

情况	原因
包含聚合函数	AVG、SUM、COUNT等无法逆向更新
包含DISTINCT	无法确定更新哪一行
包含GROUP BY	分组后无法确定原始行
包含UNION	涉及多个查询结果集
包含子查询（在SELECT中）	无法确定更新目标
多表连接（某些情况）	无法确定更新哪个表

-- 这个视图不可更新
CREATE VIEW 不可更新视图 AS
SELECT 学号, AVG(成绩) AS 平均分
FROM 选课成绩
GROUP BY 学号;

-- 尝试更新会报错
UPDATE 不可更新视图 SET 平均分 = 90 WHERE 学号 = 'S001';
-- 错误：The target table is not updatable

9.4 修改视图

9.4.1 ALTER VIEW 语句

-- 修改视图（重新定义）
ALTER VIEW 学生基本信息 AS
SELECT 学号, 姓名, 性别, 班级, 出生日期  -- 增加了出生日期
FROM 学生;

9.4.2 CREATE OR REPLACE VIEW

-- 如果存在则替换，不存在则创建
CREATE OR REPLACE VIEW 学生基本信息 AS
SELECT 学号, 姓名, 性别, 班级, 联系电话
FROM 学生;

9.5 删除视图

-- 删除单个视图
DROP VIEW 视图名;

-- 安全删除（存在才删除）
DROP VIEW IF EXISTS 视图名;

-- 删除多个视图
DROP VIEW IF EXISTS 视图1, 视图2, 视图3;

9.6 查看视图信息

9.6.1 查看视图列表

-- 查看当前数据库的所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 或者从information_schema查询
SELECT TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = '数据库名';

9.6.2 查看视图定义

-- 方法1：SHOW CREATE VIEW
SHOW CREATE VIEW 视图名\G

-- 方法2：从information_schema查询
SELECT VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = '数据库名'
  AND TABLE_NAME = '视图名';

9.6.3 查看视图结构

-- 与查看表结构相同
DESC 视图名;
DESCRIBE 视图名;

9.7 重命名视图

-- MySQL没有直接重命名视图的语法
-- 需要先删除再创建，或使用RENAME TABLE

-- 方法1：RENAME TABLE（也可用于视图）
RENAME TABLE 旧视图名 TO 新视图名;

-- 方法2：删除后重建
DROP VIEW IF EXISTS 旧视图名;
CREATE VIEW 新视图名 AS ...;

9.8 视图实战示例

9.8.1 安全性视图

-- 创建不包含敏感信息的员工视图
CREATE VIEW 员工公开信息 AS
SELECT 员工编号, 姓名, 部门, 职位
FROM 员工;
-- 不显示：工资、身份证号、家庭住址等

-- 授予普通用户只能访问视图
GRANT SELECT ON 数据库名.员工公开信息 TO '普通用户'@'localhost';

9.8.2 简化查询的视图

-- 创建订单详情视图（简化三表连接）
CREATE VIEW 订单详情视图 AS
SELECT
    o.订单号,
    o.订单日期,
    c.客户名称,
    c.联系电话,
    p.商品名称,
    od.数量,
    od.单价,
    od.数量 * od.单价 AS 小计
FROM 订单 o
INNER JOIN 客户 c ON o.客户ID = c.客户ID
INNER JOIN 订单明细 od ON o.订单号 = od.订单号
INNER JOIN 商品 p ON od.商品ID = p.商品ID;

-- 使用时就简单了
SELECT * FROM 订单详情视图 WHERE 客户名称 = '张三';
SELECT 订单号, SUM(小计) AS 总金额 FROM 订单详情视图 GROUP BY 订单号;

9.8.3 统计分析视图

-- 创建销售统计视图
CREATE VIEW 月度销售统计 AS
SELECT
    DATE_FORMAT(订单日期, '%Y-%m') AS 月份,
    COUNT(*) AS 订单数,
    SUM(金额) AS 总销售额,
    AVG(金额) AS 平均订单金额
FROM 订单
GROUP BY DATE_FORMAT(订单日期, '%Y-%m');

-- 使用
SELECT * FROM 月度销售统计 ORDER BY 月份 DESC;

9.9 视图 vs 临时表 vs 派生表

特性	视图	临时表	派生表（子查询）
存储	不存储数据	存储数据	不存储数据
持久性	永久存在	会话结束删除	查询结束即消失
可更新	部分可以	可以	不可以
性能	每次执行查询	已存储，较快	每次执行查询
使用范围	全局	当前会话	当前查询

9.10 快速参考

-- 创建视图
CREATE VIEW 视图名 AS SELECT语句;
CREATE VIEW 视图名 AS SELECT语句 WITH CHECK OPTION;
CREATE OR REPLACE VIEW 视图名 AS SELECT语句;

-- 修改视图
ALTER VIEW 视图名 AS SELECT语句;

-- 删除视图
DROP VIEW IF EXISTS 视图名;

-- 查看视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW 视图名;
DESC 视图名;

-- 通过视图操作数据
SELECT * FROM 视图名;
INSERT INTO 视图名 (列) VALUES (值);
UPDATE 视图名 SET 列=值 WHERE 条件;
DELETE FROM 视图名 WHERE 条件;

---

⬅️ 索引 🏠 00-数据库 ➡️ MySQL 编程基础