创建 / 修改视图

Oracle

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

-- Drop
DROP VIEW active_customer;

CREATE OR REPLACE VIEW 既可创建也可修改。对于在特定条件下可更新的视图，可添加 WITH CHECK OPTION。

PostgreSQL

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

DROP VIEW active_customer;

使用 CREATE OR REPLACE 时，新列只能添加在末尾；修改列的类型/顺序需要先 DROP。简单视图可自动更新。

MySQL

CREATE OR REPLACE VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

-- Modify
ALTER VIEW active_customer AS SELECT id, name FROM customer;
DROP VIEW active_customer;

定义通过 CREATE OR REPLACE VIEW 或 ALTER VIEW 更新。执行方式可通过 ALGORITHM = MERGE/TEMPTABLE 设置；WITH CHECK OPTION 可保护可更新视图。

SQL Server

CREATE OR ALTER VIEW active_customer AS
SELECT id, name, email FROM customer WHERE status = 'ACTIVE';

DROP VIEW active_customer;

CREATE OR ALTER VIEW（2016 SP1+）用一条语句完成创建/修改。旧版本中先 CREATE，再 ALTER VIEW。视图语句必须是批处理中的第一条语句。

MongoDB

// Read-only view (on top of an aggregation pipeline)
db.createView("active_customer", "customer", [ { $match: { status: "ACTIVE" } }, { $project: { name: 1, email: 1 } } ]);

db.active_customer.drop();

db.createView 基于源集合和聚合管道创建只读视图。视图不可写入；要修改定义，需删除后重建（或使用 collMod）。

Redis

# Not applicable: Redis has no view concept; derived views are provided on the application
# side or with RedisJSON/RediSearch indexes.

不支持基于查询定义的视图。类似需求可保存预计算的键，或用 RediSearch 构建可搜索索引。

Elasticsearch

# Filtered logical view with an alias
POST _aliases
{ "actions": [ { "add": { "index": "customer", "alias": "active_customer",
  "filter": { "term": { "status": "ACTIVE" } } } } ] }

没有传统的 SQL 视图；带过滤条件的别名（filtered alias）是最接近的等价物，会自动对查询应用过滤。列选择请在查询中使用 _source 过滤。