第12课: 视图

VIEW、创建视图、更新视图

视图基础概念

视图是基于SQL查询结果的虚拟表，不存储实际数据，只保存查询定义。视图可以简化复杂查询、提高安全性、保持数据独立性。

视图工作原理示意图

创建视图

-- 示例1: 创建简单视图
CREATE VIEW view_active_users AS
SELECT id, username, email, created_at
FROM users
WHERE status = 'active';

-- 使用视图
SELECT * FROM view_active_users;

-- 示例2: 创建多表连接视图
CREATE VIEW view_order_details AS
SELECT
    o.order_id,
    u.username,
    u.email,
    o.total_amount,
    o.status,
    o.order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 示例3: 创建聚合视图
CREATE VIEW view_user_statistics AS
SELECT
    u.id,
    u.username,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spending,
    MAX(o.order_date) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

视图的优势

-- 示例4: 简化复杂查询
CREATE VIEW view_product_sales AS
SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    COUNT(oi.order_item_id) AS sales_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.category, p.price;

-- 使用视图进行查询
SELECT * FROM view_product_sales
WHERE category = '电子产品'
ORDER BY total_revenue DESC
LIMIT 10;

-- 示例5: 数据安全（隐藏敏感字段）
CREATE VIEW view_user_public AS
SELECT id, username, created_at
FROM users;
-- 不包含 password, email 等敏感信息

更新视图

-- 示例6: 修改视图定义
CREATE OR REPLACE VIEW view_active_users AS
SELECT id, username, email, phone, created_at
FROM users
WHERE status = 'active' AND deleted_at IS NULL;

-- 示例7: 通过视图更新数据（简单视图可更新）
-- 可更新的视图
CREATE VIEW view_product_prices AS
SELECT product_id, product_name, price
FROM products;

-- 通过视图更新数据
UPDATE view_product_prices
SET price = 299.99
WHERE product_id = 1;

-- 通过视图插入数据
INSERT INTO view_product_prices (product_name, price)
VALUES ('新商品', 199.99);

视图的限制

以下情况的视图不可更新：

• 包含聚合函数（SUM、COUNT、AVG等）
• 包含DISTINCT、GROUP BY、HAVING
• 包含UNION或UNION ALL
• 包含子查询
• FROM子句中包含不可更新的视图

WITH CHECK OPTION

-- 示例8: 使用 WITH CHECK OPTION
CREATE VIEW view_expensive_products AS
SELECT product_id, product_name, price
FROM products
WHERE price > 100
WITH CHECK OPTION;

-- 这个更新会失败，因为不满足视图条件
UPDATE view_expensive_products
SET price = 50
WHERE product_id = 1;  -- 错误：CHECK OPTION failed

管理视图

-- 查看所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 查看视图定义
SHOW CREATE VIEW view_active_users;

-- 查看视图结构
DESC view_active_users;

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

实战场景：报表视图

-- 示例9: 销售日报视图
CREATE VIEW view_daily_sales_report AS
SELECT
    DATE(order_date) AS sale_date,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT user_id) AS customer_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE(order_date);

-- 使用视图生成报表
SELECT * FROM view_daily_sales_report
WHERE sale_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY sale_date DESC;

-- 示例10: 用户消费等级视图
CREATE VIEW view_user_levels AS
SELECT
    u.id,
    u.username,
    u.email,
    COALESCE(SUM(o.total_amount), 0) AS total_spending,
    COUNT(o.order_id) AS order_count,
    CASE
        WHEN COALESCE(SUM(o.total_amount), 0) >= 10000 THEN 'VIP'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 5000 THEN '高级会员'
        WHEN COALESCE(SUM(o.total_amount), 0) >= 1000 THEN '普通会员'
        ELSE '新用户'
    END AS user_level
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 查询VIP用户
SELECT * FROM view_user_levels
WHERE user_level = 'VIP'
ORDER BY total_spending DESC;

物化视图（MySQL 不直接支持）

MySQL 不直接支持物化视图，但可以通过表+触发器模拟：

-- 创建物化视图表
CREATE TABLE materialized_product_sales AS
SELECT
    p.product_id,
    p.product_name,
    COUNT(oi.order_item_id) AS sales_count,
    SUM(oi.quantity) AS total_quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

-- 创建触发器保持数据同步
DELIMITER //
CREATE TRIGGER refresh_materialized_view
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    -- 更新物化视图数据
    UPDATE materialized_product_sales
    SET sales_count = sales_count + 1,
        total_quantity = total_quantity + NEW.quantity
    WHERE product_id = NEW.product_id;
END //
DELIMITER ;