MySQL视图 #

┌─────────────────────────────────────┐
│            视图（View）             │
│         ┌───────────────┐           │
│         │  SELECT ...   │           │
│         └───────────────┘           │
│              ↓                      │
│    ┌─────────────────────────┐      │
│    │       基础表            │      │
│    │  users | orders | ...   │      │
│    └─────────────────────────┘      │
└─────────────────────────────────────┘

-- 视图的限制：
-- 1. 不能包含临时表
-- 2. 不能包含系统变量和用户变量
-- 3. 不能包含子查询中的FROM子句
-- 4. 某些情况下不能更新

CREATE VIEW view_name AS
SELECT statement;

-- 创建用户基本信息视图
CREATE VIEW v_user_info AS
SELECT id, name, email FROM users;

-- 使用视图
SELECT * FROM v_user_info;

-- 指定列名
CREATE VIEW v_user_basic (user_id, user_name) AS
SELECT id, name FROM users;

-- 创建订单统计视图
CREATE VIEW v_order_stats AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 使用视图
SELECT * FROM v_order_stats WHERE order_count > 0;

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

-- 使用视图
SELECT * FROM v_active_users;

-- 如果视图存在则替换
CREATE OR REPLACE VIEW v_user_info AS
SELECT id, name, email, created_at FROM users;

-- 可更新视图的条件：
-- 1. 没有聚合函数
-- 2. 没有DISTINCT
-- 3. 没有GROUP BY
-- 4. 没有HAVING
-- 5. 没有UNION
-- 6. 没有子查询

CREATE VIEW v_user_editable AS
SELECT id, name, email FROM users;

-- 可以通过视图更新数据
UPDATE v_user_editable SET name = 'John' WHERE id = 1;
INSERT INTO v_user_editable (id, name, email) VALUES (10, 'Test', 'test@example.com');
DELETE FROM v_user_editable WHERE id = 10;

-- 查看视图结构
DESC v_user_info;

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

-- 从information_schema查询
SELECT 
    view_name,
    view_definition
FROM information_schema.views
WHERE table_schema = 'mydb';

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

-- 或
SELECT table_name 
FROM information_schema.views 
WHERE table_schema = DATABASE();

-- 替换视图定义
CREATE OR REPLACE VIEW v_user_info AS
SELECT id, name, email, phone FROM users;

-- 修改视图
ALTER VIEW v_user_info AS
SELECT id, name, email, phone, created_at FROM users;

DROP VIEW view_name;

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

DROP VIEW v_user_info, v_order_stats;

-- 可更新视图必须满足：
-- 1. 没有聚合函数（SUM, COUNT, AVG等）
-- 2. 没有DISTINCT
-- 3. 没有GROUP BY
-- 4. 没有HAVING
-- 5. 没有UNION或UNION ALL
-- 6. 没有子查询
-- 7. 没有JOIN（某些情况）

-- 可更新视图示例
CREATE VIEW v_users AS
SELECT id, name, email FROM users;

-- 通过视图插入
INSERT INTO v_users (name, email) VALUES ('Test', 'test@example.com');

-- 通过视图更新
UPDATE v_users SET name = 'New Name' WHERE id = 1;

-- 通过视图删除
DELETE FROM v_users WHERE id = 1;

-- 包含聚合函数的视图不可更新
CREATE VIEW v_user_order_count AS
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 尝试更新会报错
UPDATE v_user_order_count SET name = 'New' WHERE id = 1;
-- ERROR 1288 (HY000): The target table v_user_order_count of the UPDATE is not updatable

-- WITH CHECK OPTION：确保通过视图修改的数据仍然满足视图条件
CREATE VIEW v_active_users AS
SELECT id, name, email, status
FROM users
WHERE status = 1
WITH CHECK OPTION;

-- 插入status=0的记录会失败
INSERT INTO v_active_users (name, email, status) VALUES ('Test', 'test@example.com', 0);
-- ERROR 1369 (HY000): CHECK OPTION failed 'mydb.v_active_users'

-- 更新status为0会失败
UPDATE v_active_users SET status = 0 WHERE id = 1;
-- ERROR 1369 (HY000): CHECK OPTION failed 'mydb.v_active_users'

-- 复杂查询封装为视图
CREATE VIEW v_product_sales AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    COUNT(o.id) AS order_count,
    SUM(o.quantity) AS total_quantity,
    SUM(o.quantity * o.price) AS total_amount
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN order_items o ON p.id = o.product_id
GROUP BY p.id;

-- 简单查询
SELECT * FROM v_product_sales WHERE order_count > 10;

-- 隐藏敏感字段
CREATE VIEW v_user_public AS
SELECT id, name, created_at FROM users;

-- 授权用户只能访问视图
GRANT SELECT ON mydb.v_user_public TO 'app_user'@'localhost';

-- 每日销售汇总视图
CREATE VIEW v_daily_sales AS
SELECT 
    DATE(order_date) AS sale_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(order_date);

-- 查询
SELECT * FROM v_daily_sales WHERE sale_date >= '2024-01-01';

-- 格式化数据视图
CREATE VIEW v_user_display AS
SELECT 
    id,
    CONCAT(name, ' (', email, ')') AS display_name,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS join_date,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
    END AS status_text
FROM users;

-- MySQL视图是虚拟表，每次查询时执行定义的SELECT
-- 没有物化视图功能

-- 查询视图
SELECT * FROM v_order_stats WHERE user_id = 1;

-- 等价于
SELECT * FROM (
    SELECT 
        u.id AS user_id,
        u.name AS user_name,
        COUNT(o.id) AS order_count,
        SUM(o.amount) AS total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
) AS t WHERE user_id = 1;

-- 1. 为视图查询的表创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 避免在视图中使用复杂计算
-- 3. 避免嵌套视图（视图引用视图）
-- 4. 考虑使用存储过程替代复杂视图

-- 视图命名：v_表名_功能
CREATE VIEW v_users_active;     -- 活跃用户视图
CREATE VIEW v_orders_daily;     -- 每日订单视图
CREATE VIEW v_products_sales;   -- 产品销售视图