视图

视图（View）是一种虚拟表，它基于SQL查询语句的结果集。视图不存储实际数据，每次查询视图时都会执行其定义的SQL语句。视图可以简化复杂查询、提供数据安全性和逻辑独立性。

创建视图

基本语法

-- 创建视图的基本语法
CREATE VIEW 视图名 AS
SELECT 语句;

-- 创建示例表
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INTEGER REFERENCES departments(dept_id),
    salary NUMERIC(10, 2),
    hire_date DATE,
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO departments (dept_name, location) VALUES
    ('技术部', '北京'),
    ('销售部', '上海'),
    ('人事部', '广州');

INSERT INTO employees (emp_name, dept_id, salary, hire_date, status) VALUES
    ('张三', 1, 20000, '2020-01-15', 'active'),
    ('李四', 1, 18000, '2021-03-20', 'active'),
    ('王五', 1, 16000, '2022-06-10', 'active'),
    ('赵六', 2, 15000, '2021-01-01', 'active'),
    ('钱七', 2, 12000, '2022-08-15', 'inactive'),
    ('孙八', 3, 14000, '2021-05-20', 'active');

-- 创建简单视图
CREATE VIEW active_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE status = 'active';

-- 查询视图
SELECT * FROM active_employees;

-- 如果视图已存在则替换
CREATE OR REPLACE VIEW active_employees AS
SELECT emp_id, emp_name, salary, hire_date
FROM employees
WHERE status = 'active';

创建带列名的视图

-- 指定视图的列名
CREATE VIEW employee_summary (
    员工编号,
    员工姓名,
    部门名称,
    薪资
) AS
SELECT e.emp_id, e.emp_name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

SELECT * FROM employee_summary;

创建临时视图

-- 临时视图只在当前会话中存在
CREATE TEMP VIEW temp_high_salary AS
SELECT * FROM employees WHERE salary > 15000;

-- 会话结束后自动删除
SELECT * FROM temp_high_salary;

查询视图

视图可以像普通表一样查询：

-- 基本查询
SELECT * FROM active_employees;

-- 带条件查询
SELECT * FROM active_employees WHERE salary > 15000;

-- 排序
SELECT * FROM active_employees ORDER BY salary DESC;

-- 分组统计
SELECT 
    dept_name,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employee_summary
GROUP BY dept_name;

-- 与其他表连接
SELECT 
    es.员工姓名,
    es.部门名称,
    d.location
FROM employee_summary es
JOIN departments d ON es.部门名称 = d.dept_name;

-- 在子查询中使用视图
SELECT * FROM employee_summary
WHERE 薪资 > (SELECT AVG(薪资) FROM employee_summary);

可更新视图

简单视图可以进行INSERT、UPDATE、DELETE操作，这些操作会直接影响底层表。

可更新视图的条件

视图必须满足以下条件才能更新：

• 不包含DISTINCT
• 不包含聚合函数
• 不包含GROUP BY、HAVING
• 不包含UNION、INTERSECT、EXCEPT
• 不包含窗口函数
• 不包含子查询（FROM子句除外）
• 不包含不可更新的视图

更新视图示例

-- 创建可更新视图
CREATE VIEW tech_employees AS
SELECT emp_id, emp_name, salary, status
FROM employees
WHERE dept_id = 1;

-- 通过视图更新数据
UPDATE tech_employees 
SET salary = 20000 
WHERE emp_name = '王五';

-- 验证更新
SELECT * FROM employees WHERE emp_name = '王五';

-- 通过视图插入数据（需要包含所有必要列）
INSERT INTO tech_employees (emp_name, salary, status, dept_id)
VALUES ('新员工', 15000, 'active', 1);

-- 通过视图删除数据
DELETE FROM tech_employees WHERE emp_name = '新员工';

使用WITH CHECK OPTION

-- WITH CHECK OPTION确保通过视图修改的数据仍然满足视图条件
CREATE VIEW high_salary_employees AS
SELECT emp_id, emp_name, salary, dept_id
FROM employees
WHERE salary > 15000
WITH CHECK OPTION;  -- 确保更新后仍满足条件

-- 这个更新会成功（修改后仍满足salary > 15000）
UPDATE high_salary_employees SET salary = 18000 WHERE emp_name = '李四';

-- 这个更新会失败（修改后不满足salary > 15000）
-- UPDATE high_salary_employees SET salary = 10000 WHERE emp_name = '李四';
-- 错误：new row violates check option for view "high_salary_employees"

-- LOCAL CHECK OPTION（只检查当前视图条件）
CREATE VIEW local_check_view AS
SELECT * FROM high_salary_employees
WHERE dept_id = 1
WITH LOCAL CHECK OPTION;

-- CASCADED CHECK OPTION（检查所有底层视图条件，默认行为）
CREATE VIEW cascaded_check_view AS
SELECT * FROM high_salary_employees
WHERE dept_id = 1
WITH CASCADED CHECK OPTION;

物化视图

物化视图（Materialized View）将查询结果实际存储在磁盘上，适合用于不经常变化但查询复杂的数据。

创建物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW dept_stats AS
SELECT 
    d.dept_name,
    COUNT(e.emp_id) AS emp_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- 查询物化视图
SELECT * FROM dept_stats;

-- 物化视图可以创建索引
CREATE INDEX idx_dept_stats_name ON dept_stats(dept_name);

刷新物化视图

-- 刷新物化视图（全量刷新）
REFRESH MATERIALIZED VIEW dept_stats;

-- 并发刷新（不阻塞查询，需要有唯一索引）
CREATE UNIQUE INDEX idx_dept_stats_unique ON dept_stats(dept_name);
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats;

-- 查看物化视图是否正在被使用
SELECT * FROM pg_stat_user_tables 
WHERE relname = 'dept_stats';

删除物化视图

-- 删除物化视图
DROP MATERIALIZED VIEW dept_stats;

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

视图管理

查看视图信息

-- 查看所有视图
\dv

-- 查看视图定义
\d+ active_employees

-- 使用SQL查询视图信息
SELECT 
    viewname AS 视图名称,
    viewowner AS 所有者,
    definition AS 定义
FROM pg_views
WHERE schemaname = 'public';

-- 查看物化视图
SELECT 
    schemaname,
    matviewname AS 物化视图名称,
    matviewowner AS 所有者
FROM pg_matviews;

-- 查看视图的列信息
SELECT 
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_name = 'active_employees'
ORDER BY ordinal_position;

修改视图

-- 使用CREATE OR REPLACE修改视图定义
CREATE OR REPLACE VIEW active_employees AS
SELECT 
    emp_id, 
    emp_name, 
    salary, 
    hire_date,
    status
FROM employees
WHERE status = 'active';

-- 重命名视图
ALTER VIEW active_employees RENAME TO active_staff;

-- 设置视图所有者
ALTER VIEW active_staff OWNER TO postgres;

-- 设置视图Schema
ALTER VIEW active_staff SET SCHEMA public;

删除视图

-- 删除视图
DROP VIEW active_staff;

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

-- 级联删除（删除依赖该视图的对象）
DROP VIEW employee_summary CASCADE;

视图应用场景

简化复杂查询

-- 创建复杂的统计视图
CREATE VIEW sales_report AS
SELECT 
    d.dept_name AS 部门,
    COUNT(e.emp_id) AS 员工数,
    ROUND(AVG(e.salary), 2) AS 平均薪资,
    ROUND(SUM(e.salary), 2) AS 薪资总额,
    MAX(e.salary) AS 最高薪资,
    MIN(e.salary) AS 最低薪资,
    COUNT(CASE WHEN e.status = 'active' THEN 1 END) AS 活跃员工数
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

-- 使用视图简化查询
SELECT * FROM sales_report ORDER BY 员工数 DESC;

数据安全

-- 创建只显示部分列的视图（隐藏敏感信息）
CREATE VIEW employee_public AS
SELECT 
    emp_id,
    emp_name,
    dept_id,
    hire_date
FROM employees;
-- 不显示salary和status等敏感字段

-- 授予用户访问视图的权限，而不是表的权限
GRANT SELECT ON employee_public TO readonly_user;

数据逻辑独立性

-- 当底层表结构变化时，只需修改视图定义
-- 原始视图
CREATE VIEW employee_info AS
SELECT emp_id, emp_name, salary FROM employees;

-- 如果employees表拆分为employee_basic和employee_salary
-- 只需修改视图定义，应用程序无需修改
CREATE OR REPLACE VIEW employee_info AS
SELECT 
    b.emp_id, 
    b.emp_name, 
    s.salary
FROM employee_basic b
JOIN employee_salary s ON b.emp_id = s.emp_id;

行级安全

-- 创建基于用户的行级视图
CREATE VIEW my_department_employees AS
SELECT e.*
FROM employees e
WHERE e.dept_id = (
    SELECT dept_id FROM employees WHERE emp_name = CURRENT_USER
);

-- 或者使用会话变量
CREATE VIEW filtered_employees AS
SELECT * FROM employees
WHERE dept_id = current_setting('app.current_dept_id')::INTEGER;

视图最佳实践

命名规范

-- 使用有意义的前缀或后缀
CREATE VIEW vw_employee_summary AS ...;   -- vw前缀
CREATE VIEW employee_summary_view AS ...; -- view后缀

-- 物化视图使用mv前缀
CREATE MATERIALIZED VIEW mv_daily_stats AS ...;

性能考虑

-- 1. 避免嵌套过深的视图
-- 不好的做法：视图套视图
CREATE VIEW view1 AS SELECT * FROM employees;
CREATE VIEW view2 AS SELECT * FROM view1;
CREATE VIEW view3 AS SELECT * FROM view2;

-- 2. 对于复杂查询，考虑使用物化视图
CREATE MATERIALIZED VIEW mv_complex_report AS
SELECT ... -- 复杂的聚合查询

-- 3. 定期刷新物化视图
-- 可以使用pg_cron扩展设置定时任务
-- SELECT cron.schedule('refresh_stats', '0 2 * * *', 
--     'REFRESH MATERIALIZED VIEW mv_daily_stats');

-- 4. 为物化视图创建合适的索引
CREATE INDEX idx_mv_report_date ON mv_daily_stats(report_date);

视图文档化

-- 使用COMMENT为视图添加注释
COMMENT ON VIEW employee_summary IS '员工汇总视图，包含员工基本信息和部门名称';
COMMENT ON MATERIALIZED VIEW dept_stats IS '部门统计物化视图，每日刷新';

-- 查看视图注释
SELECT 
    c.relname AS 视图名称,
    d.description AS 说明
FROM pg_class c
LEFT JOIN pg_description d ON c.oid = d.objoid
WHERE c.relkind = 'v'  -- v表示视图
AND d.description IS NOT NULL;

视图与表的区别

特性	视图	物化视图	表
存储数据	否	是	是
实时性	实时	需刷新	实时
更新操作	受限	受限	完全支持
性能	依赖查询	查询快	查询快
存储空间	极小	较大	大

小结

本章介绍了PostgreSQL视图的主要内容：

内容	说明
普通视图	虚拟表，不存储数据
可更新视图	满足条件的视图可以更新
WITH CHECK OPTION	确保更新后仍满足视图条件
物化视图	存储查询结果，适合复杂查询
视图权限	可以通过视图控制数据访问

视图是数据库开发中的重要工具，下一章我们将学习函数的创建和使用。