Skip to content

Views

创建虚拟表 使用 views: simplifying 查询, security, and materialized views.

你将学到

  • 什么 view 是 and 如何它 acts as 虚拟表
  • 创建 views 到 simplify 复杂查询
  • Using views for security and 抽象
  • Updatable 对比非-updatable views
  • Materialized views for 缓存 expensive 查询

概念讲解

什么是视图?

view 是一种 named, stored query that behaves like a virtual table. It does not store data itself (unless it is a materialized view) — it stores a SELECT statement and runs it every time you query the view. Views are useful for simplifying complex queries, providing a stable API over changing schemas, and restricting access to sensitive columns.

Simplifying Complex Queries

If you frequently join four tables with the same conditions, you can create a view that encapsulates that join. Then applications query the view as if it were a single table. This keeps application SQL simple and centralizes the join logic in one place. If the underlying schema changes, you update the view definition and all consumers automatically adapt.

Security and Abstraction

Views can hide columns or rows from users. You can grant a user SELECT access to a view without granting access to the underlying tables. For example, a employee_public view that excludes the salary column lets a reporting tool see employee data without exposing salaries. This is column-level security via views.

Updatable Views

In many databases, a simple view (single table, no aggregates, no DISTINCT, no JOIN) is updatable — you can INSERT, UPDATE, and DELETE through the view. Complex views with joins or aggregates are read-only. Check your database documentation for the exact rules.

Materialized Views

A materialized view stores the query result physically, like a real table. It is refreshed on demand (REFRESH MATERIALIZED VIEW). This is far faster for expensive aggregate queries that do not need real-time data. The trade-off is staleness — the materialized view may not reflect the latest changes until you refresh it.

示例代码

              -- Create a view that simplifies a common join
CREATE VIEW order_summary AS
SELECT
  u.name        AS customer_name,
  o.id          AS order_id,
  o.total       AS order_total,
  o.status      AS order_status,
  o.created_at  AS order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status != 'cancelled';

-- Query the view like a regular table
SELECT * FROM order_summary
WHERE order_date >= '2025-01-01'
ORDER BY order_total DESC;

-- A view for security: hide the salary column
CREATE VIEW employee_directory AS
SELECT id, name, department, email FROM employees;

-- Materialized view for an expensive aggregate (PostgreSQL)
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  DATE(created_at) AS day,
  SUM(total)       AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW daily_revenue;
            

Four view 模式: simplifying 连接 view, querying 它, security view 那个 hides salary, and materialized view for daily revenue 使用 refresh.

动手试一试

  • SQL Formatter

    格式 CREATE VIEW and materialized view 定义.

  • JSON Path

    JSONPath projections 是相似到 views — 选择 subset.

常见错误

错误写法

创建 view over view over view, hurting 性能

正确做法

每个 view layer 添加查询复杂度. 数据库通常 flattens nested views, 但 deeply nested views 是困难到 调试. Keep view nesting shallow.

错误写法

Forgetting 到 refresh materialized views

正确做法

Materialized views go stale. 设置 scheduled refresh (cron 作业 or 触发) based on 如何 fresh 数据需要到 是.

练习 Views

2 个练习

练习 Views

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误