{T}

视图

视图（View）是一种虚拟表，它基于 SQL 查询语句的结果集。视图本身不存储数据，而是在查询时动态生成。视图可以简化复杂查询、保护数据安全、提供数据独立性。

视图概述

什么是视图

视图是一张虚拟表，它的内容由 SELECT 查询定义。视图不存储实际数据，每次查询视图时，数据库会执行视图定义的查询语句。

视图的优点

优点说明
简化查询封装复杂查询，简化使用
数据安全可以隐藏敏感字段
数据独立修改底层表结构不影响应用
重用性一次定义，多次使用

视图的缺点

缺点说明
性能开销每次查询都要执行定义的 SQL
更新限制复杂视图可能无法更新
调试困难视图嵌套时难以定位问题

创建视图

基本语法

sql
CREATE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 条件;

创建简单视图

sql
-- 创建高于平均身高的球员视图
CREATE VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

-- 使用视图
SELECT * FROM player_above_avg_height;

创建带别名的视图

sql
CREATE VIEW player_info AS
SELECT 
    player_id AS id,
    player_name AS name,
    height AS h
FROM player;

创建多表连接视图

sql
CREATE VIEW player_team_info AS
SELECT 
    p.player_name,
    p.height,
    t.team_name
FROM player p
JOIN team t ON p.team_id = t.team_id;

创建计算字段视图

sql
CREATE VIEW player_score_detail AS
SELECT 
    game_id,
    player_id,
    (shoot_hits - shoot_3_hits) * 2 AS two_points,
    shoot_3_hits * 3 AS three_points,
    shoot_p_hits AS free_throws,
    score AS total_points
FROM player_score;

修改视图

ALTER VIEW

sql
ALTER VIEW player_above_avg_height AS
SELECT 
    player_id, 
    player_name, 
    height,
    team_id
FROM player
WHERE height > (SELECT AVG(height) FROM player);

CREATE OR REPLACE VIEW

sql
-- 如果视图存在则替换，不存在则创建
CREATE OR REPLACE VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

删除视图

基本语法

sql
DROP VIEW 视图名;

-- 如果存在则删除
DROP VIEW IF EXISTS player_above_avg_height;

删除多个视图

sql
DROP VIEW view1, view2, view3;

嵌套视图

视图可以基于其他视图创建：

sql
-- 基础视图
CREATE VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) FROM player);

-- 嵌套视图
CREATE VIEW player_above_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) FROM player_above_avg_height);

注意：嵌套视图会增加查询复杂度，影响性能，应避免过深嵌套。

可更新视图

可更新视图条件

视图满足以下条件时可以执行 INSERT、UPDATE、DELETE：

  1. 没有聚合函数（SUM、COUNT、AVG 等）
  2. 没有 DISTINCT
  3. 没有 GROUP BY、HAVING
  4. 没有 UNION、UNION ALL
  5. 没有子查询
  6. 只引用一个表

更新视图示例

sql
-- 创建可更新视图
CREATE VIEW student_view AS
SELECT id, name, score FROM students;

-- 通过视图更新数据
UPDATE student_view SET score = 90 WHERE id = 1;

-- 通过视图插入数据
INSERT INTO student_view (name, score) VALUES ('新学生', 80);

-- 通过视图删除数据
DELETE FROM student_view WHERE id = 1;

WITH CHECK OPTION

确保通过视图修改的数据仍然满足视图条件：

sql
CREATE VIEW high_score_students AS
SELECT id, name, score 
FROM students 
WHERE score >= 80
WITH CHECK OPTION;

-- 尝试将分数改为 70（会失败）
UPDATE high_score_students SET score = 70 WHERE id = 1;
-- Error: CHECK OPTION failed

视图应用场景

1. 简化复杂查询

sql
-- 创建复杂查询视图
CREATE VIEW player_height_grades AS
SELECT 
    p.player_name,
    p.height,
    h.height_level
FROM player p
JOIN height_grades h 
    ON p.height BETWEEN h.height_lowest AND h.height_highest;

-- 简化查询
SELECT * FROM player_height_grades 
WHERE height >= 1.90 AND height <= 2.08;

2. 数据格式化

sql
-- 格式化输出
CREATE VIEW player_team_formatted AS
SELECT 
    CONCAT(p.player_name, '(', t.team_name, ')') AS player_info
FROM player p
JOIN team t ON p.team_id = t.team_id;

-- 使用
SELECT * FROM player_team_formatted;
-- 结果：韦恩-艾灵顿(底特律活塞)

3. 数据安全

sql
-- 隐藏敏感字段
CREATE VIEW employee_public AS
SELECT id, name, department, position
FROM employee;
-- 不包含 salary、id_card 等敏感字段

-- 授权访问视图而非原表
GRANT SELECT ON employee_public TO public_user;

4. 数据统计

sql
-- 统计视图
CREATE VIEW department_stats AS
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employee
GROUP BY department;

-- 使用
SELECT * FROM department_stats;

5. 历史数据

sql
-- 创建历史数据视图
CREATE VIEW orders_2024 AS
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;

视图性能优化

1. 避免嵌套视图

sql
-- 差：嵌套视图
CREATE VIEW v1 AS SELECT * FROM table1;
CREATE VIEW v2 AS SELECT * FROM v1;
CREATE VIEW v3 AS SELECT * FROM v2;

-- 好：直接定义
CREATE VIEW v3 AS SELECT * FROM table1;

2. 使用索引

sql
-- 确保视图查询的表有适当索引
CREATE INDEX idx_player_height ON player(height);

3. 物化视图（MySQL 不原生支持）

sql
-- 使用表模拟物化视图
CREATE TABLE mv_player_stats AS
SELECT 
    team_id,
    COUNT(*) AS player_count,
    AVG(height) AS avg_height
FROM player
GROUP BY team_id;

-- 定期刷新
TRUNCATE TABLE mv_player_stats;
INSERT INTO mv_player_stats
SELECT team_id, COUNT(*), AVG(height)
FROM player
GROUP BY team_id;

视图管理

查看视图定义

sql
-- 查看视图结构
DESCRIBE view_name;
DESC view_name;

-- 查看视图创建语句
SHOW CREATE VIEW view_name;

-- 从 information_schema 查询
SELECT * FROM information_schema.VIEWS 
WHERE TABLE_NAME = 'view_name';

查看所有视图

sql
SELECT TABLE_NAME, TABLE_TYPE 
FROM information_schema.TABLES 
WHERE TABLE_TYPE = 'VIEW';

总结

视图操作

操作语法
创建CREATE VIEW 视图名 AS SELECT ...
修改ALTER VIEW 视图名 AS SELECT ...
删除DROP VIEW 视图名
查看SHOW CREATE VIEW 视图名

视图特点

特点说明
虚拟表不存储实际数据
动态生成查询时执行定义的 SQL
可更新简单视图支持 DML 操作
安全性可隐藏敏感字段

最佳实践

  1. 简化复杂查询：将常用复杂查询封装为视图
  2. 控制嵌套深度：避免视图嵌套视图
  3. 合理使用索引：确保底层表的查询效率
  4. 考虑性能：频繁使用的复杂查询考虑使用物化表
  5. 权限控制：通过视图限制数据访问