企业官网建设流程全解析

PG实战笔记 | 13 PostgreSQL SQL基础语法（上）：DDL、DML、DQL

上一篇我们拆解了 PGDATA 目录，理解了 PostgreSQL 的“身体结构”。从这一篇开始，我们回到最日常的工作：写 SQL。DDL 建表、DML 增删改、DQL 查询，看起来是每个数据库都有的东西。但 PostgreSQL 在这三件事上，有自己的“性格”——尤其是它的数据类型丰富度，以及约束机制对数据质量的兜底能力，值得单独拿出来讲。

一、DDL：建表不只是“字段加类型”

DDL（Data Definition Language）负责定义数据库对象：表、索引、视图、约束。PostgreSQL 的CREATE TABLE语法与其他关系数据库大体相似，但细节里藏着差异。

CREATETABLEIFNOTEXISTSusers(user_id BIGSERIALPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(255)NOTNULLUNIQUE,ageSMALLINTCHECK(age>=0ANDage<200),created_at TIMESTAMPTZNOTNULLDEFAULTnow());

这段简单的建表语句已经包含了 PostgreSQL 的几个设计信号：BIGSERIAL是自增整数（底层是 sequence 加默认值），TIMESTAMPTZ是带时区的时间戳，CHECK约束直接内嵌在列定义里。

但真正让 PostgreSQL 的 DDL 与众不同的，是它原生支持的数据类型。

二、PG 的数据类型，到底丰富在哪里？

MySQL 也支持 JSON、空间类型，但 PostgreSQL 的丰富度是“原生自带、无需扩展”的。以下几个类型是日常开发中最能体现差异的。

1. JSONB：可索引的半结构化数据

PostgreSQL 同时提供JSON和JSONB。JSON保留原始文本格式，写入快但查询慢；JSONB以二进制格式存储，支持索引、包含查询、键存在性判断，写入稍慢但查询效率高得多。

CREATETABLEproducts(idSERIALPRIMARYKEY,nameTEXT,attributes JSONB);-- 写入INSERTINTOproducts(name,attributes)VALUES('机械键盘','{"brand": "Keychron", "switch": "红轴", "price": 499}');-- 查询：找出所有红轴的键盘SELECTnameFROMproductsWHEREattributes @>'{"switch": "红轴"}';

@>是“包含”操作符。配合 GIN 索引，JSONB 字段上的查询可以做到毫秒级。这意味着，你不需要为了存一个“标签”或“扩展属性”就引入 MongoDB——PostgreSQL 一个库就能覆盖。

2. 数组类型：一个字段存一组值

CREATETABLEarticles(idSERIALPRIMARYKEY,tagsTEXT[]);INSERTINTOarticles(tags)VALUES(ARRAY['postgresql','sql','database']);-- 查询包含 'postgresql' 标签的文章SELECTidFROMarticlesWHERE'postgresql'=ANY(tags);

数组类型适合存储标签、多选值、简单的集合数据。它支持ANY、ALL、数组操作符和 GIN 索引，不需要额外建关联表。

3. 范围类型：把“区间”当成一等公民

范围类型用于表示一段连续的值，比如时间区间、数值区间。PostgreSQL 内置INT4RANGE、TSRANGE、DATERANGE等。

CREATETABLEreservations(room_idINT,period TSRANGE,EXCLUDEUSINGGIST(room_idWITH=,periodWITH&&));

&&操作符判断两个范围是否重叠。配合EXCLUDE约束，可以实现“同一个房间不能有重叠预订”这样的业务规则——由数据库直接保证，而不是靠应用层逻辑。这是 PostgreSQL 独有的能力。

4. 其他“开箱即用”的类型

这些类型在其他数据库中通常需要字符串存储加应用层校验，而在 PostgreSQL 中，类型本身就已经完成了格式和语义的校验。

三、DML：写入与修改的“隐形守护”

DML（Data Manipulation Language）负责数据的增删改：INSERT、UPDATE、DELETE。语法本身不复杂，但 PostgreSQL 的约束系统会在每一次 DML 操作时“默默把关”。

INSERT：约束的第一次考验

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

如果username重复，UNIQUE约束会直接拒绝；如果age为负数，CHECK约束会拦截；如果必填字段缺失，NOT NULL约束会报错。数据质量问题在写入的那一刻就被拦住了，而不是等到报表跑出异常才被发现。

UPDATE：同样受约束约束

UPDATEusersSETage=200WHEREusername='alice';-- ERROR: new row for relation "users" violates check constraint "users_age_check"

UPDATE 触发的约束检查与 INSERT 完全一致。这意味着，即使通过手工 SQL 修改数据，也无法绕过业务规则。

DELETE 与外键的“连锁反应”

-- 如果 orders 表有 FOREIGN KEY 引用 users，直接删除用户会失败DELETEFROMusersWHEREuser_id=1;-- ERROR: update or delete on table "users" violates foreign key constraint on table "orders"

外键的默认行为是NO ACTION：如果被引用的行仍被引用，删除会被拒绝。你也可以通过ON DELETE CASCADE让删除自动级联，但这需要明确的业务判断——PostgreSQL 把选择权交给你，而不是替你做决定。

四、DQL：SELECT 是 PostgreSQL 真正拉开差距的地方

DQL（Data Query Language）的核心是SELECT。基础的单表查询、WHERE过滤、ORDER BY排序，PostgreSQL 与其他数据库差别不大。但一旦进入复杂查询，差距就出现了。

窗口函数：在结果集内做“分组排名”

SELECTusername,amount,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYamountDESC)ASrankFROMorders;

窗口函数允许你在不折叠行的情况下，对结果集进行排名、累计、前后行比较。MySQL 直到 8.0 才原生支持窗口函数，而 PostgreSQL 长期以来的 SQL 标准遵循度更高，复杂分析查询往往一条 SQL 就能完成，不需要应用层多次查询再在内存中处理。

CTE 与递归查询

WITHtop_usersAS(SELECTuser_id,SUM(amount)AStotalFROMordersGROUPBYuser_idHAVINGSUM(amount)>10000)SELECTu.username,t.totalFROMtop_users tJOINusers uONu.user_id=t.user_id;

CTE（WITH子句）让复杂查询可以被拆分成可读的步骤。递归 CTE 还能处理树形结构（组织架构、分类目录等），这同样是 PostgreSQL 的强项。

连接：不只是 INNER 和 LEFT

PostgreSQL 完整支持FULL OUTER JOIN、LATERAL JOIN等高级连接方式。LATERAL JOIN允许右侧子查询引用左侧表的列，适合“每行取最新一条关联记录”这类场景——用一条 SQL 解决，而不是 N+1 查询。

五、约束：数据质量的“最后一道防线”

回到一个根本问题：约束到底保证了什么？

答案是：业务规则的一致性，不依赖调用方是否“守规矩”。无论是应用代码、手工 SQL、ETL 脚本还是数据迁移工具，只要往表里写数据，约束都会一视同仁地检查。

PostgreSQL 的约束体系包括：

其中EXCLUSION是 PostgreSQL 特有的约束类型，它把“不重叠”这种业务规则从应用层下沉到了数据库层。这是“数据质量”理念的极致体现：能由数据库保证的事情，就不要交给应用去“记得做”。

但约束也有代价：每一次写入都需要额外检查，UNIQUE和PRIMARY KEY会自动创建索引，EXCLUSION需要 GiST 索引。在写入吞吐极高的场景下，约束的开销需要被权衡。不过对于大多数业务系统来说，数据错误的修复成本远高于约束带来的写入开销。

六、总结

这一篇覆盖了 SQL 基础语法的三大块：

• DDL：CREATE TABLE不只是列名加类型，PostgreSQL 的原生丰富类型（JSONB、数组、范围、UUID、INET）让很多原本需要“外挂”的数据模型可以直接在关系库中表达。
• DML：INSERT、UPDATE、DELETE的每一次执行，都在接受约束系统的检验，数据质量问题在写入时就被拦截。
• DQL：SELECT的复杂查询能力（窗口函数、CTE、LATERAL JOIN）是 PostgreSQL 相比 MySQL 的显著优势，分析型场景往往能少写很多应用层代码。

一句话：PostgreSQL 的 SQL 基础语法看起来“标准”，但它的数据类型和约束体系，把“数据质量”从应用层的口头约定，变成了数据库层的硬性规则。

下一篇，我们进入 SQL 基础语法（下）：函数、聚合、分组——WHERE和HAVING到底谁先执行？聚合查询怎么优化？

思考题：
你的项目里有没有用过 PostgreSQL 的 JSONB、数组或范围类型？如果没有，哪些字段其实用这些类型会比“字符串 + 应用层解析”更合适？另外，你的表上有多少业务规则是靠应用代码保证的，而不是靠数据库约束？欢迎在评论区聊聊。