EP06. “视图、索引通用语法与日期类型”

首页 › PC 工具 › Calculating · SQL · EP06

约 11 分钟· #EP06#SQL

🔒 登录后可标记已读

同一段 JOIN + GROUP BY 的查询要用到第五次的时候，与其每次复制贴上，不如包成一个视图（View）直接当表来查
视图不会另外存一份数据，只是把 SELECT 语句包起来，每次查询都是即时重新算出来的结果
索引的建立语法在不同数据库大同小异，这篇只补几个跨数据库的差异点，什么时候该建/怎么建复合索引这些细节看 EP02
PostgreSQL、MySQL、SQL Server 的日期类型命名和精度都不一样，跨数据库搬代码最容易在这里踩坑
前置知识：先看过 EP01、EP02，熟悉 SELECT/JOIN 和索引的基本概念

重点内容

视图（View）是什么

视图是一张「虚拟表」——数据库只存这张表背后的 SELECT 语句（也就是「怎么算出这些数据」的逻辑），不存实际的数据副本。每次查询视图，数据库都是当场重新跑一次底层的 SELECT，再把结果当成表返回给你。

📌 视图不会让查询变快：它只是帮你省下重复写同一段 SQL 的功夫，如果底层的 SELECT 本身很慢（比如没建对索引），查视图一样慢，视图本身不具备加速效果。

建立与查询视图

假设常常要看「每个用户下过几张订单、总共花了多少钱」，与其每次都手写一遍 JOIN + GROUP BY，可以包成一个视图：

-- 建立视图
CREATE VIEW user_order_summary AS
SELECT
  u.id AS user_id,
  u.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 之后直接把它当表来查
SELECT * FROM user_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC;

视图也可以叠加 WHERE 条件，只暴露部分数据。例如只想给客服团队看「还没完成的订单」，不给他们看金额敏感的完整表：

CREATE VIEW pending_orders AS
SELECT id, user_id, status, created_at
FROM orders
WHERE status IN ('pending', 'processing');

更新与移除视图

改视图定义的语法，各数据库不一样：

-- PostgreSQL / MySQL / Oracle：CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW user_order_summary AS
SELECT
  u.id AS user_id,
  u.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true          -- 新增一个条件
GROUP BY u.id, u.name;

-- SQL Server：用 ALTER VIEW
-- ALTER VIEW user_order_summary AS SELECT ...

移除视图：

DROP VIEW user_order_summary;

📌 视图不是每个都能直接写入：像上面 user_order_summary 这种带 JOIN + GROUP BY 的视图，只能拿来读，没办法直接对它 UPDATE/INSERT。只有单表、没有聚合函数、没有 DISTINCT 的简单视图才可能是「可更新视图」，实际能不能写入以所用数据库的规则为准。

索引通用语法（简记，详情看 EP02）

建索引的基本语法在各数据库几乎一样：CREATE INDEX idx_name ON table_name(column);——什么时候该建索引、复合索引栏位顺序怎么排、部分索引怎么用，EP02 已经讲得很细，这里不重复。这篇只补一个 EP02 没提到的点：索引建好之后要拿掉，各数据库的语法不一样：

数据库	移除索引语法
PostgreSQL / DB2 / Oracle	`DROP INDEX idx_name;`
MySQL	`ALTER TABLE table_name DROP INDEX idx_name;`
SQL Server	`DROP INDEX table_name.idx_name;`

日期类型：PostgreSQL / MySQL / SQL Server 怎么选

三套数据库对「日期」「日期时间」的命名和精度都不太一样，直接影响建表时该选哪个类型：

用途	PostgreSQL	MySQL	SQL Server
只存日期	`DATE`	`DATE`	`DATE`
日期 + 时间	`TIMESTAMP`	`DATETIME`	`DATETIME2`（建议）/ `DATETIME`
带时区的日期时间	`TIMESTAMPTZ`	无原生类型，要另存时区栏位	`DATETIMEOFFSET`
只存时间	`TIME`	`TIME`	`TIME`
精简型日期时间	—	—	`SMALLDATETIME`（精度到分钟）

实际建表时，同一个「订单建立时间」栏位，三套数据库的写法长这样：

-- PostgreSQL：推荐用 TIMESTAMPTZ，自动处理时区换算
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- MySQL
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- SQL Server
CREATE TABLE orders (
  id INT IDENTITY PRIMARY KEY,
  created_at DATETIME2 DEFAULT SYSDATETIME()
);

💡 团队里用 PostgreSQL 处理跨时区业务（比如电商平台的用户分布在不同国家），栏位优先选 TIMESTAMPTZ 而不是 TIMESTAMP——前者存的是绝对时间点，查询时会自动按连线的时区设定换算显示，不用自己手动加减时差。

学完你会

✅ 用 CREATE VIEW 把重复用到的 JOIN + GROUP BY 查询包成一张虚拟表
✅ 分清楚哪些视图能直接更新、哪些只能读
✅ 给电商表的「时间」栏位在 PostgreSQL/MySQL/SQL Server 里各自选对日期类型

适用版本

视图部分 CREATE OR REPLACE VIEW 语法适用于 PostgreSQL/MySQL/Oracle，SQL Server 需改用 ALTER VIEW；日期类型差异已在上面表格列出，实际使用前建议对照所用数据库的官方文档确认。

常见错误

❌ 把带 JOIN/GROUP BY 的视图当成普通表直接 UPDATE/INSERT——这类视图通常不可写，只能拿来读，硬写会直接报错
❌ 以为建了视图查询就会变快——视图只是省事、不用重复写 SQL，底层查询该有的索引还是要建，不然视图一样慢
❌ MySQL 的日期时间栏位该用 DATETIME 却选了 TIMESTAMP——TIMESTAMP 在 MySQL 有时间范围限制（到 2038 年封顶），存较久远的日期要用 DATETIME
❌ SQL Server 里把 TIMESTAMP 当成时间类型来用——SQL Server 的 TIMESTAMP 其实是自动生成的二进制版本号（等同 ROWVERSION），根本不是时间，要存日期时间应该用 DATETIME2
💡 索引和视图一样都不占查询逻辑本身的位置，改索引/加视图都不需要动到应用程式代码，是很适合先做效能优化再评估要不要动代码的一步

Sources

Blog / Website：