企业官网建设流程全解析

PostgreSQL 是一个开源的关系型数据库管理系统，支持标准 SQL、事务、复杂查询，同时还提供 JSONB、数组、全文搜索、GIS 等能力。

如果你刚开始学习 PostgreSQL，没有必要一上来就研究 MVCC、VACUUM、WAL 这些底层机制。第一阶段更重要的是先掌握日常开发最常用的内容：

• Database、Schema、Table
• 常见数据类型
• 表的创建与约束
• CRUD
• WHERE、NULL、排序和分页
• 聚合查询
• JOIN
• 子查询
• 事务

下面按照这个顺序简单梳理一下。

1. Database、Schema 和 Table

PostgreSQL 中常见的层级关系可以理解为：

PostgreSQL Server └── Database └── Schema └── Table

例如一个电商系统：

shop ├── public │ ├── users │ ├── products │ └── orders │ └── analytics └── daily_sales

这里：

• shop是 Database
• public和analytics是 Schema
• users、orders是 Table

Schema 可以理解为 Database 内部的命名空间。

例如：

SELECT*FROMpublic.users;

这里的：

public.users

其实就是：

schema.table

PostgreSQL 默认通常会使用publicSchema，所以平时我们也经常直接写：

SELECT*FROMusers;

Schema 的作用主要包括：

业务隔离 权限隔离 避免表名冲突

例如不同 Schema 中可以同时存在：

sales.orders archive.orders

2. 创建一张表

先创建一张最常见的用户表：

CREATETABLEusers(idBIGINTGENERATED ALWAYSASIDENTITYPRIMARYKEY,usernameTEXTNOTNULL,emailTEXTUNIQUE,ageINTEGER,is_activeBOOLEANDEFAULTTRUE,created_at TIMESTAMPTZDEFAULTNOW());

这里包含几个比较常见的数据类型：

BIGINT INTEGER TEXT BOOLEAN TIMESTAMPTZ

以及常见约束：

PRIMARY KEY NOT NULL UNIQUE DEFAULT

比如：

usernameTEXTNOTNULL

表示username不能为空。

emailTEXTUNIQUE

表示 email 不允许重复。

3. PostgreSQL 常见数据类型

PostgreSQL 支持的数据类型很多，入门阶段先掌握常用的即可。

整数：

SMALLINT INTEGER BIGINT

字符串：

TEXT VARCHAR

精确小数：

NUMERIC

布尔值：

BOOLEAN

时间：

DATE TIMESTAMP TIMESTAMPTZ

例如金额通常可以写成：

priceNUMERIC(10,2)

而普通字符串在 PostgreSQL 中经常直接使用：

TEXT

如果你需要限制长度，再使用：

VARCHAR(50)

例如：

usernameVARCHAR(50)

TIMESTAMP 和 TIMESTAMPTZ

PostgreSQL 中经常会看到：

TIMESTAMP TIMESTAMPTZ

对于 Web 服务或者存在跨时区场景的系统，通常更常使用：

created_at TIMESTAMPTZDEFAULTNOW()

因为它更适合表达一个明确的时间点。

4. Identity 和自增 ID

很多表都会使用自动生成的主键。

PostgreSQL 可以使用：

GENERATED ALWAYSASIDENTITY

例如：

CREATETABLEusers(idBIGINTGENERATED ALWAYSASIDENTITYPRIMARYKEY,usernameTEXTNOTNULL);

插入时：

INSERTINTOusers(username)VALUES('Alice');

不需要手动传入id，PostgreSQL 会自动生成。

老项目中你可能还会看到：

SERIAL

例如：

idSERIALPRIMARYKEY

新项目中通常可以优先考虑IDENTITY。

5. 基本 CRUD

CRUD 是日常开发最常用的一组操作：

Create Read Update Delete

INSERT

插入数据：

INSERTINTOusers(username,email,age)VALUES('Alice','alice@example.com',25);

也可以一次插入多条：

INSERTINTOusers(username,email,age)VALUES('Bob','bob@example.com',28),('Tom','tom@example.com',30);

SELECT

查询：

SELECTid,username,emailFROMusers;

实际项目中通常更推荐明确写出需要的字段，而不是长期使用：

SELECT*

UPDATE

修改数据：

UPDATEusersSETage=26WHEREid=1;

DELETE

删除数据：

DELETEFROMusersWHEREid=1;

UPDATE和DELETE执行时一定要特别注意WHERE。

例如：

UPDATEusersSETis_active=FALSE;

会更新整张表。

6. WHERE、NULL 和 COALESCE

条件查询通常使用WHERE：

SELECT*FROMusersWHEREage>=18ANDis_active=TRUE;

常见条件包括：

= <> > < >= <= AND OR IN BETWEEN LIKE

例如：

SELECT*FROMusersWHEREageBETWEEN18AND30;

NULL

SQL 中的NULL表示未知值或者缺失值。

不能写：

WHEREemail=NULL

而应该写：

WHEREemailISNULL;

判断不为空：

WHEREemailISNOTNULL;

COALESCE

COALESCE可以返回第一个非 NULL 的值。

例如：

SELECTCOALESCE(nickname,username,'Anonymous')FROMusers;

如果nickname为空，就使用username。

7. 排序、分页和去重

ORDER BY

按照创建时间倒序：

SELECT*FROMusersORDERBYcreated_atDESC;

升序使用：

ASC

降序使用：

DESC

LIMIT 和 OFFSET

查询前 10 条：

SELECT*FROMusersLIMIT10;

简单分页：

SELECT*FROMusersORDERBYidLIMIT10OFFSET20;

代表跳过前 20 条，再返回 10 条。

需要注意的是，大数据量下使用很大的OFFSET可能存在性能问题，这部分可以留到后面的性能优化篇。

DISTINCT

去重：

SELECTDISTINCTageFROMusers;

8. GROUP BY 和 HAVING

PostgreSQL 中常见的聚合函数有：

COUNT() SUM() AVG() MAX() MIN()

例如统计用户数量：

SELECTCOUNT(*)FROMusers;

如果用户表中存在country字段，可以统计每个国家的用户数：

SELECTcountry,COUNT(*)ASuser_countFROMusersGROUPBYcountry;

如果只想保留用户数量大于 100 的国家：

SELECTcountry,COUNT(*)ASuser_countFROMusersGROUPBYcountryHAVINGCOUNT(*)>100;

可以简单理解：

WHERE 先过滤原始数据 HAVING 再过滤聚合结果

例如：

SELECTcountry,COUNT(*)FROMusersWHEREis_active=TRUEGROUPBYcountryHAVINGCOUNT(*)>100;

执行逻辑上可以理解为：

先筛选活跃用户 ↓ 按照 country 分组 ↓ 统计数量 ↓ 筛选数量大于 100 的结果

9. JOIN

真实项目中，数据通常不会全部放在一张表。

例如：

users orders

用户表：

users id username

订单表：

orders id user_id amount

INNER JOIN

查询订单以及对应的用户名：

SELECTu.username,o.idASorder_id,o.amountFROMusers uINNERJOINorders oONu.id=o.user_id;

INNER JOIN只会返回两边能够匹配的数据。

例如：

Alice 有订单 Bob 有订单 Tom 没有订单

查询结果中可能只有：

Alice Bob

LEFT JOIN

如果我们希望即使用户没有订单，也要查询出来：

SELECTu.username,o.idASorder_idFROMusers uLEFTJOINorders oONu.id=o.user_id;

结果可能变成：

Alice 1 Bob 2 Tom NULL

可以简单记：

INNER JOIN 只保留匹配数据 LEFT JOIN 保留左表全部数据

JOIN 是 SQL 中非常重要的一部分，日常业务开发中使用频率非常高。

10. 子查询和 EXISTS

查询有订单的用户，可以使用子查询：

SELECT*FROMusersWHEREidIN(SELECTuser_idFROMorders);

也可以写成：

SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_id=u.id);

EXISTS关注的是：

是否存在符合条件的数据。

不过不要简单记成：

EXISTS 一定比 IN 快

PostgreSQL 有自己的查询优化器。

最终性能需要结合：

数据量 索引 统计信息 执行计划

来判断。

后面学习 PostgreSQL 性能优化时，可以通过：

EXPLAIN EXPLAIN ANALYZE

进一步分析。

11. UNION 和 UNION ALL

如果想把两个查询结果合并，可以使用：

SELECTemailFROMcustomersUNIONSELECTemailFROMsuppliers;

UNION会自动去重。

如果不需要去重：

SELECTemailFROMcustomersUNIONALLSELECTemailFROMsuppliers;

通常UNION ALL会更高效，因为它不需要额外处理去重。

所以如果业务允许重复数据，可以优先考虑：

UNION ALL

12. 事务

最后再简单说一下事务。

假设现在有一个转账场景：

A 账户减少 100 B 账户增加 100

对应两条 SQL：

UPDATEaccountsSETbalance=balance-100WHEREid=1;UPDATEaccountsSETbalance=balance+100WHEREid=2;

如果第一条执行成功，第二条失败，就会造成数据异常。

因此这两个操作应该放到同一个事务中：

BEGIN;UPDATEaccountsSETbalance=balance-100WHEREid=1;UPDATEaccountsSETbalance=balance+100WHEREid=2;COMMIT;

如果执行过程中出现问题：

ROLLBACK;

可以回滚整个事务。

事务通常会涉及 ACID：

Atomicity 原子性 Consistency 一致性 Isolation 隔离性 Durability 持久性

入门阶段先理解：

一个事务中的操作应该作为一个整体执行。

至于 PostgreSQL 如何实现事务隔离、并发控制以及数据持久化，就会涉及：

事务隔离级别 MVCC 锁 WAL

这些更适合放到进阶篇。

总结

对于 PostgreSQL 入门，我认为优先掌握下面这些内容就已经足够：

Database / Schema / Table 常见数据类型 CREATE TABLE IDENTITY INSERT SELECT UPDATE DELETE WHERE NULL COALESCE ORDER BY LIMIT DISTINCT COUNT GROUP BY HAVING INNER JOIN LEFT JOIN 子查询 EXISTS UNION UNION ALL BEGIN COMMIT ROLLBACK

掌握这些以后，基本已经可以应对大部分常见业务开发。