跳到主要内容

SQL 视图

视图（View）是一种虚拟表，它基于 SQL 语句的结果集。本章将详细介绍视图的创建、使用和最佳实践。

什么是视图？​

视图是一个 SELECT 语句的命名结果集，它不存储实际数据，每次访问时动态生成。

为什么使用视图？​

-- 假设我们经常需要查询用户的订单统计
-- 不使用视图：每次都要写复杂的 SQL
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- 使用视图：简化查询
CREATE VIEW user_order_stats AS
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

-- 查询时就像使用普通表一样
SELECT * FROM user_order_stats WHERE order_count > 5;

视图的优点​

  • 简化复杂查询：将复杂查询封装为视图
  • 数据安全：限制用户访问特定列或行
  • 逻辑独立：修改底层表结构时，视图可以保持不变
  • 数据一致性：确保不同用户看到一致的数据格式

视图的缺点​

  • 性能开销：每次访问都要执行查询
  • 更新限制：不是所有视图都支持更新
  • 调试困难：复杂视图可能难以调试

创建视图​

基本语法​

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

简单视图​

-- 创建员工基本信息视图
CREATE VIEW employee_basic AS
SELECT 
    id,
    name,
    department,
    position
FROM employees;

-- 使用视图
SELECT * FROM employee_basic WHERE department = '技术部';

复杂视图​

-- 创建销售统计视图
CREATE VIEW sales_statistics AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    COUNT(oi.id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id AND o.status = 'completed'
GROUP BY p.id, p.name, c.name;

-- 使用视图查询热门商品
SELECT * FROM sales_statistics 
ORDER BY total_revenue DESC 
LIMIT 10;

带条件的视图​

-- 创建活跃用户视图
CREATE VIEW active_users AS
SELECT 
    id,
    name,
    email,
    last_login_at
FROM users
WHERE 
    status = 'active' 
    AND last_login_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 只有活跃用户会被包含
SELECT * FROM active_users;

带计算列的视图​

-- 创建产品利润视图
CREATE VIEW product_profit AS
SELECT 
    id,
    name,
    cost_price,
    selling_price,
    selling_price - cost_price AS profit,
    ROUND((selling_price - cost_price) / cost_price * 100, 2) AS profit_margin
FROM products;

-- 查询高利润商品
SELECT * FROM product_profit WHERE profit_margin > 30;

查看视图​

查看视图定义​

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

-- 从信息模式查询
SELECT 
    TABLE_NAME AS view_name,
    VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'your_database';

查看数据库中的视图​

-- 查看所有表和视图
SHOW FULL TABLES;

-- 只查看视图
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'VIEW' 
AND TABLE_SCHEMA = 'your_database';

修改视图​

使用 CREATE OR REPLACE​

-- 如果视图存在则替换，不存在则创建
CREATE OR REPLACE VIEW employee_basic AS
SELECT 
    id,
    name,
    department,
    position,
    hire_date  -- 新增列
FROM employees;

使用 ALTER VIEW​

-- 修改视图定义
ALTER VIEW employee_basic AS
SELECT 
    id,
    name,
    department,
    position,
    hire_date,
    salary  -- 新增列
FROM employees;

删除视图​

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

-- 删除多个视图
DROP VIEW IF EXISTS view1, view2, view3;

可更新视图​

不是所有视图都支持 INSERT、UPDATE、DELETE 操作。

可更新视图的条件​

视图必须满足以下条件才是可更新的：

  1. 没有 DISTINCT、GROUP BY、HAVING
  2. 没有 UNION、INTERSECT、EXCEPT
  3. 没有聚合函数（SUM、COUNT、AVG 等）
  4. 没有子查询（某些情况下允许）
  5. 没有不可更新的视图嵌套

更新视图示例​

-- 创建可更新视图
CREATE VIEW active_employees AS
SELECT 
    id,
    name,
    department,
    salary
FROM employees
WHERE status = 'active';

-- 通过视图更新数据
UPDATE active_employees 
SET salary = salary * 1.1 
WHERE department = '技术部';

-- 通过视图插入数据
INSERT INTO active_employees (name, department, salary)
VALUES ('新员工', '技术部', 8000);

-- 通过视图删除数据
DELETE FROM active_employees WHERE id = 100;

WITH CHECK OPTION​

使用 WITH CHECK OPTION 确保通过视图修改的数据仍然满足视图条件：

-- 创建带检查选项的视图
CREATE VIEW high_salary_employees AS
SELECT 
    id,
    name,
    department,
    salary
FROM employees
WHERE salary > 10000
WITH CHECK OPTION;

-- 尝试更新为不满足条件的值
UPDATE high_salary_employees 
SET salary = 8000 
WHERE id = 1;
-- 错误！因为更新后 salary <= 10000，不满足视图条件

-- 尝试插入不满足条件的行
INSERT INTO high_salary_employees (name, department, salary)
VALUES ('测试', '技术部', 5000);
-- 错误！salary <= 10000 不满足视图条件

CASCADE 和 LOCAL 检查​

-- CASCADE：检查所有底层视图的条件（默认）
CREATE VIEW high_tech_employees AS
SELECT * FROM high_salary_employees
WHERE department = '技术部'
WITH CASCADED CHECK OPTION;

-- LOCAL：只检查当前视图的条件
CREATE VIEW high_tech_employees_local AS
SELECT * FROM high_salary_employees
WHERE department = '技术部'
WITH LOCAL CHECK OPTION;

视图实战案例​

案例 1：数据安全视图​

限制用户只能看到自己的数据：

-- 创建用户专属视图
CREATE VIEW my_orders AS
SELECT 
    id,
    total,
    status,
    created_at
FROM orders
WHERE user_id = CURRENT_USER_ID();

-- 授权给普通用户
GRANT SELECT ON my_orders TO 'app_user'@'%';
-- 不授权访问原始 orders 表

案例 2：数据脱敏视图​

隐藏敏感信息：

-- 创建脱敏视图
CREATE VIEW user_public_info AS
SELECT 
    id,
    name,
    CONCAT(LEFT(email, 3), '***@', SUBSTRING(email, INSTR(email, '@') + 1)) AS masked_email,
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone,
    created_at
FROM users;

-- 非管理员用户只能访问脱敏视图
GRANT SELECT ON user_public_info TO 'analyst'@'%';

案例 3：报表视图​

简化报表查询：

-- 月度销售报表视图
CREATE VIEW monthly_sales_report AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- 查询最近 12 个月的报表
SELECT * FROM monthly_sales_report LIMIT 12;

案例 4：层级数据视图​

处理组织架构等层级数据：

-- 部门层级视图
CREATE VIEW department_hierarchy AS
WITH RECURSIVE dept_tree AS (
    -- 基础查询：顶级部门
    SELECT 
        id,
        name,
        parent_id,
        CAST(name AS CHAR(1000)) AS path,
        1 AS level
    FROM departments
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询：子部门
    SELECT 
        d.id,
        d.name,
        d.parent_id,
        CONCAT(dt.path, ' > ', d.name),
        dt.level + 1
    FROM departments d
    JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

-- 查询部门层级
SELECT * FROM department_hierarchy WHERE name LIKE '%技术%';

案例 5：历史数据视图​

追踪数据变更：

-- 创建产品价格历史视图
CREATE VIEW product_price_history AS
SELECT 
    p.id,
    p.name,
    ph.old_price,
    ph.new_price,
    ph.changed_at,
    ph.changed_by
FROM products p
JOIN product_history ph ON p.id = ph.product_id
WHERE ph.field_name = 'price'
ORDER BY ph.changed_at DESC;

-- 查看产品价格变化
SELECT * FROM product_price_history WHERE id = 100;

视图性能优化​

视图与性能​

视图本身不存储数据，每次访问都会执行底层查询：

-- 这个视图每次访问都会执行复杂的聚合
CREATE VIEW complex_stats AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order,
    MAX(created_at) AS last_order_date
FROM orders
GROUP BY user_id;

-- 多次访问会多次执行
SELECT * FROM complex_stats WHERE user_id = 1;
SELECT * FROM complex_stats WHERE user_id = 2;

优化建议​

1. 使用索引​

确保底层表的查询字段有索引：

-- 视图查询使用了 user_id 和 created_at
CREATE VIEW recent_orders AS
SELECT * FROM orders 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 确保有对应的索引
CREATE INDEX idx_created_at ON orders(created_at);

2. 避免嵌套视图​

-- 不好：嵌套视图
CREATE VIEW view1 AS SELECT * FROM users WHERE status = 'active';
CREATE VIEW view2 AS SELECT * FROM view1 WHERE department = '技术部';
CREATE VIEW view3 AS SELECT * FROM view2 WHERE salary > 10000;

-- 好：直接定义
CREATE VIEW high_salary_tech_users AS
SELECT * FROM users 
WHERE status = 'active' 
AND department = '技术部' 
AND salary > 10000;

3. 使用物化视图（MySQL 8.0+）​

MySQL 原生不支持物化视图，但可以使用表模拟：

-- 创建汇总表
CREATE TABLE order_summary (
    user_id INT PRIMARY KEY,
    order_count INT,
    total_spent DECIMAL(12, 2),
    last_updated TIMESTAMP
);

-- 定时更新汇总表（使用事件或定时任务）
INSERT INTO order_summary (user_id, order_count, total_spent, last_updated)
SELECT 
    user_id,
    COUNT(*),
    SUM(total),
    NOW()
FROM orders
GROUP BY user_id
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    total_spent = VALUES(total_spent),
    last_updated = NOW();

-- 使用汇总表代替视图
SELECT * FROM order_summary WHERE user_id = 1;

视图最佳实践​

1. 命名规范​

-- 好的命名：清晰表明视图用途
CREATE VIEW view_active_users AS ...
CREATE VIEW view_order_statistics AS ...
CREATE VIEW view_product_sales AS ...

-- 或使用后缀
CREATE VIEW active_users_v AS ...
CREATE VIEW order_stats_v AS ...

2. 添加注释​

-- 创建视图时添加注释
CREATE VIEW user_order_summary AS
SELECT 
    u.id AS user_id,
    u.name,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
WITH CHECK OPTION;

-- 通过视图属性添加说明
ALTER VIEW user_order_summary 
COMMENT '用户订单汇总视图，包含订单数量和总金额';

3. 限制视图复杂度​

-- 避免：过度复杂的视图
CREATE VIEW overly_complex AS
SELECT ... FROM (
    SELECT ... FROM (
        SELECT ... FROM (
            -- 多层嵌套
        )
    )
);

-- 推荐：拆分为多个简单视图或使用 CTE

4. 权限管理​

-- 创建只读视图并授权
CREATE VIEW customer_info AS
SELECT id, name, email, created_at FROM customers;

-- 授予只读权限
GRANT SELECT ON customer_info TO 'app_readonly'@'%';

-- 撤销对原表的直接访问
REVOKE ALL ON customers FROM 'app_readonly'@'%';

小结​

本章我们学习了：

  1. 视图概念：虚拟表，基于 SELECT 语句的结果集
  2. 创建视图：CREATE VIEW、CREATE OR REPLACE VIEW
  3. 可更新视图：通过视图修改数据的条件和限制
  4. WITH CHECK OPTION：确保修改后的数据满足视图条件
  5. 视图应用：数据安全、数据脱敏、报表查询
  6. 性能优化：索引、避免嵌套、物化视图替代

练习​

  1. 创建一个视图，显示每个分类的商品数量和平均价格
  2. 创建一个可更新视图，并测试 WITH CHECK OPTION 的效果
  3. 设计一个数据脱敏视图，隐藏用户的敏感信息
  4. 使用视图实现一个简单的权限控制系统

参考资源​