视图VIEW

-- 创建视图
CREATE VIEW user_posts AS
SELECT 
    u.username,
    p.title,
    p.created_at
FROM users u
JOIN posts p ON u.id = p.user_id;

-- 使用视图（就像查询表一样）
SELECT * FROM user_posts;

-- 活跃用户视图
CREATE VIEW active_users AS
SELECT * FROM users
WHERE is_deleted = FALSE
  AND last_login > NOW() - INTERVAL '30 days';

-- 商品统计视图
CREATE VIEW product_stats AS
SELECT 
    p.id,
    p.name,
    COUNT(oi.id) as order_count,
    SUM(oi.quantity) as total_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

-- 使用视图
SELECT * FROM product_stats
WHERE total_sold > 100
ORDER BY total_sold DESC;

-- 简单视图（可更新）
CREATE VIEW beijing_users AS
SELECT * FROM users WHERE city = '北京';

-- 可以插入（会插入到原表）
INSERT INTO beijing_users (username, city)
VALUES ('测试', '北京');

-- 复杂视图（不可更新）
CREATE VIEW user_stats AS
SELECT user_id, COUNT(*) FROM posts GROUP BY user_id;
-- 这个不能INSERT/UPDATE

-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT * FROM users WHERE is_active = TRUE;

-- 删除视图
DROP VIEW active_users;

-- 查看所有视图
\dv