视图 #
一、视图概述 #
1.1 视图概念 #
text
视图类型
┌─────────────────────────────────────────────────────────────┐
│ │
│ 普通视图: │
│ ├── 虚拟表，不存储数据 │
│ ├── 每次查询时执行定义的SQL │
│ └── 简化复杂查询 │
│ │
│ 物化视图: │
│ ├── 存储查询结果 │
│ ├── 需要手动刷新 │
│ └── 提高查询性能 │
│ │
│ 视图优点: │
│ ├── 简化复杂查询 │
│ ├── 提供数据抽象层 │
│ ├── 实现数据安全 │
│ └── 保持数据一致性 │
│ │
└─────────────────────────────────────────────────────────────┘
二、创建视图 #
2.1 基本语法 #
sql
-- 创建视图
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';
-- 使用视图
SELECT * FROM active_users;
-- 创建带列名的视图
CREATE VIEW user_summary (user_id, user_name, total_orders)
AS
SELECT
u.id,
u.name,
COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
SELECT * FROM user_summary;
2.2 复杂视图 #
sql
-- 多表连接视图
CREATE VIEW order_details AS
SELECT
o.id AS order_id,
o.order_no,
u.name AS customer_name,
u.email,
o.amount,
o.status,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id;
-- 聚合视图
CREATE VIEW sales_summary AS
SELECT
DATE(created_at) AS sale_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE(created_at);
-- 使用窗口函数的视图
CREATE VIEW ranked_products AS
SELECT
product_name,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;
2.3 视图选项 #
sql
-- OR REPLACE: 替换已存在的视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, status
FROM users
WHERE status = 'active';
-- TEMPORARY: 创建临时视图
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';
-- CHECK OPTION: 更新时检查条件
CREATE VIEW active_users_check AS
SELECT * FROM users WHERE status = 'active'
WITH CHECK OPTION;
-- 更新时必须满足 status = 'active'
UPDATE active_users_check SET status = 'inactive' WHERE id = 1;
-- 会报错，因为违反视图条件
三、修改视图 #
3.1 修改视图定义 #
sql
-- 使用 CREATE OR REPLACE
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';
-- 使用 ALTER VIEW
ALTER VIEW active_users RENAME TO v_active_users;
-- 删除并重建
DROP VIEW active_users;
CREATE VIEW active_users AS
SELECT id, name, email, phone
FROM users
WHERE status = 'active';
3.2 视图依赖 #
sql
-- 查看视图依赖
SELECT
dependent_view.relname AS view_name,
source_table.relname AS source_table
FROM pg_depend
JOIN pg_class dependent_view ON pg_depend.objid = dependent_view.oid
JOIN pg_class source_table ON pg_depend.refobjid = source_table.oid
WHERE dependent_view.relkind = 'v';
-- 删除依赖视图
DROP VIEW order_details CASCADE; -- 同时删除依赖此视图的对象
四、可更新视图 #
4.1 可更新视图条件 #
text
可更新视图条件
┌─────────────────────────────────────────────────────────────┐
│ │
│ 视图可以更新，需要满足: │
│ ├── FROM 子句只有一个表 │
│ ├── 不包含聚合函数 │
│ ├── 不包含 DISTINCT │
│ ├── 不包含 GROUP BY │
│ ├── 不包含 HAVING │
│ ├── 不包含 UNION/INTERSECT/EXCEPT │
│ └── 不包含子查询 │
│ │
│ 简单视图通常可以更新 │
│ 复杂视图通常只能读取 │
│ │
└─────────────────────────────────────────────────────────────┘
4.2 更新视图示例 #
sql
-- 可更新视图
CREATE VIEW user_emails AS
SELECT id, name, email
FROM users;
-- 通过视图插入
INSERT INTO user_emails (id, name, email)
VALUES (gen_random_uuid(), 'Test', 'test@example.com');
-- 通过视图更新
UPDATE user_emails
SET email = 'new@example.com'
WHERE name = 'Test';
-- 通过视图删除
DELETE FROM user_emails WHERE name = 'Test';
五、物化视图 #
5.1 创建物化视图 #
sql
-- 创建物化视图
CREATE MATERIALIZED VIEW sales_report AS
SELECT
DATE(created_at) AS sale_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(created_at);
-- 查询物化视图
SELECT * FROM sales_report;
-- 刷新物化视图
REFRESH MATERIALIZED VIEW sales_report;
-- 带数据的物化视图
CREATE MATERIALIZED VIEW product_sales AS
SELECT
p.product_name,
COUNT(oi.id) AS sale_count,
SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.product_name
WITH DATA; -- 立即填充数据
5.2 管理物化视图 #
sql
-- 刷新物化视图
REFRESH MATERIALIZED VIEW sales_report;
-- 删除数据 (保留定义)
REFRESH MATERIALIZED VIEW sales_report WITH NO DATA;
-- 查看物化视图内容
SELECT * FROM sales_report;
-- 删除物化视图
DROP MATERIALIZED VIEW sales_report;
5.3 物化视图使用场景 #
text
物化视图使用场景
┌─────────────────────────────────────────────────────────────┐
│ │
│ 适用场景: │
│ ├── 复杂聚合查询 │
│ ├── 数据仓库报表 │
│ ├── 实时性要求不高 │
│ └── 查询频繁但数据变化慢 │
│ │
│ 不适用场景: │
│ ├── 需要实时数据 │
│ ├── 数据频繁变化 │
│ └── 存储空间有限 │
│ │
│ 刷新策略: │
│ ├── 定时刷新 (cron job) │
│ ├── 事件触发刷新 │
│ └── 手动刷新 │
│ │
└─────────────────────────────────────────────────────────────┘
六、视图管理 #
6.1 查看视图 #
sql
-- 查看所有视图
SELECT * FROM information_schema.views
WHERE table_schema = 'public';
-- 查看视图定义
SHOW CREATE VIEW active_users;
-- 查看视图列
SELECT * FROM information_schema.columns
WHERE table_name = 'active_users'
AND table_schema = 'public';
-- 查看物化视图
SELECT * FROM pg_class WHERE relkind = 'm';
6.2 视图权限 #
sql
-- 授予视图查询权限
GRANT SELECT ON active_users TO readonly_user;
-- 授予视图所有权限
GRANT ALL ON active_users TO admin_user;
-- 回收权限
REVOKE SELECT ON active_users FROM readonly_user;
七、视图最佳实践 #
7.1 设计原则 #
text
视图设计原则
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 视图命名规范 │
│ ├── 使用 v_ 前缀 │
│ └── 名称清晰表达用途 │
│ │
│ 2. 视图职责单一 │
│ ├── 每个视图一个目的 │
│ └── 避免过于复杂 │
│ │
│ 3. 性能考虑 │
│ ├── 视图不存储数据 │
│ ├── 复杂视图影响性能 │
│ └── 考虑使用物化视图 │
│ │
│ 4. 安全考虑 │
│ ├── 限制列访问 │
│ ├── 限制行访问 │
│ └── 使用权限控制 │
│ │
└─────────────────────────────────────────────────────────────┘
7.2 使用示例 #
sql
-- 数据安全视图
CREATE VIEW v_user_public AS
SELECT id, name
FROM users;
-- 只暴露公开信息
-- 业务逻辑封装视图
CREATE VIEW v_order_summary AS
SELECT
o.id,
o.order_no,
u.name AS customer,
o.amount,
CASE o.status
WHEN 'pending' THEN '待处理'
WHEN 'paid' THEN '已支付'
WHEN 'shipped' THEN '已发货'
WHEN 'completed' THEN '已完成'
ELSE '未知'
END AS status_text
FROM orders o
JOIN users u ON o.user_id = u.id;
-- 报表视图
CREATE VIEW v_daily_sales AS
SELECT
DATE(created_at) AS sale_date,
COUNT(*) AS orders,
SUM(amount) AS revenue,
COUNT(DISTINCT user_id) AS customers
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);
八、总结 #
视图要点：
| 类型 | 说明 |
|---|---|
| 普通视图 | 虚拟表，不存储数据 |
| 可更新视图 | 可以通过视图修改数据 |
| 物化视图 | 存储查询结果 |
| 临时视图 | 会话级别，自动删除 |
| 安全视图 | 限制数据访问 |
下一步，让我们学习序列！
最后更新：2026-03-27