PostgreSQL 语法（建议收藏）

PostgreSQL 语法入门：从零开始掌握数据库核心操作

你是否在开发项目时，遇到过数据存储、查询效率低的问题？或者在使用数据库时，总是对 SQL 语法感到困惑？PostgreSQL 是一个功能强大且高度可靠的开源关系型数据库系统，它不仅支持标准 SQL，还提供了许多高级特性，如 JSON 支持、全文检索、数组类型等。掌握 PostgreSQL 语法，是每一位开发者提升数据处理能力的关键一步。

在本文中，我将带你从基础语法开始，逐步深入，通过真实场景和可运行代码，让你真正理解 PostgreSQL 语法的逻辑与用法。无论你是刚接触数据库的初学者，还是有一定经验的中级开发者，都能从中获得实用价值。

数据定义语言（DDL）：创建与管理数据库对象

在使用数据库之前，我们首先要学会如何定义它。DDL（Data Definition Language）是用于创建、修改和删除数据库结构的语言。它就像盖房子前的图纸设计，决定了后续所有数据的“居住空间”。

创建数据库与模式

在 PostgreSQL 中，数据库是最高级别的容器，一个实例可以管理多个数据库。我们先创建一个名为 shop_db 的数据库：

-- 创建数据库，名字为 shop_db
CREATE DATABASE shop_db;

注释：这条命令会创建一个名为 shop_db 的新数据库。如果已存在同名数据库，会报错。使用 CREATE DATABASE IF NOT EXISTS shop_db; 可避免重复创建。

接着，我们可以创建一个模式（Schema），它相当于数据库内的“文件夹”，用于组织表和视图。例如，为电商系统创建一个 products 模式：

-- 创建名为 products 的模式
CREATE SCHEMA products;

注释：模式用于逻辑隔离不同业务模块的表。比如 products 用于商品信息，orders 用于订单记录。

创建数据表

有了数据库和模式，接下来就是创建表。我们以一个商品表为例：

-- 创建商品表，使用 products 模式
CREATE TABLE products.product_info (
    product_id SERIAL PRIMARY KEY,          -- 自增主键，唯一标识商品
    name VARCHAR(100) NOT NULL,             -- 商品名称，不能为空
    price DECIMAL(10,2) CHECK (price >= 0), -- 价格，必须大于等于 0
    stock_quantity INT DEFAULT 0,           -- 库存数量，默认为 0
    created_at TIMESTAMP DEFAULT NOW()      -- 创建时间，默认为当前时间
);

注释：

SERIAL 是 PostgreSQL 的自增类型，等价于 BIGSERIAL（64 位自增）

PRIMARY KEY 确保 product_id 唯一且不为空

CHECK (price >= 0) 保证价格不能为负数

DEFAULT NOW() 自动插入当前时间戳

表名使用 模式名.表名 格式，确保在指定模式下创建

数据操作语言（DML）：插入、查询、更新与删除数据

DDL 定义了结构，DML 则负责数据的增删改查。这部分是日常开发中最常接触的语法，掌握它，就等于掌握了数据库的核心操作。

插入数据（INSERT）

向 product_info 表中插入几条商品数据：

-- 插入三条商品记录
INSERT INTO products.product_info (name, price, stock_quantity)
VALUES 
    ('iPhone 15', 6999.00, 50),
    ('MacBook Air', 9999.00, 30),
    ('AirPods Pro', 1899.00, 100);

注释：

INSERT INTO ... VALUES 是标准插入语法

指定了列名，可以避免遗漏或顺序错误

未指定 product_id，由 SERIAL 自动分配

price 使用 DECIMAL(10,2) 类型，精确到小数点后两位

查询数据（SELECT）

查询是最常用的数据库操作。我们从表中取出所有商品信息：

-- 查询所有商品信息
SELECT * FROM products.product_info;

注释：* 表示选择所有列。生产环境中建议明确写出列名，提高可读性和性能。

进一步，我们只查看价格高于 5000 的商品：

-- 查询价格大于 5000 的商品
SELECT name, price, stock_quantity
FROM products.product_info
WHERE price > 5000;

注释：

WHERE 用于条件筛选

只返回需要的列，避免不必要的数据传输

条件表达式 price > 5000 是布尔逻辑判断

更新与删除数据（UPDATE & DELETE）

当商品价格调整或库存变动时，需要更新数据：

-- 将 iPhone 15 的价格调整为 6799
UPDATE products.product_info
SET price = 6799.00
WHERE name = 'iPhone 15';

注释：

SET 指定要修改的列和新值

WHERE 精准定位目标行，避免误改所有数据

若省略 WHERE，所有行都会被更新，非常危险！

删除已下架商品：

-- 删除库存为 0 的商品
DELETE FROM products.product_info
WHERE stock_quantity = 0;

注释：

DELETE 从表中移除行

建议先用 SELECT 验证 WHERE 条件是否准确，避免误删

数据类型与约束：让数据更“健康”

PostgreSQL 支持丰富的数据类型，合理选择类型不仅能节省存储空间，还能提升查询效率。同时，约束确保了数据的完整性。

常用数据类型对比

类型	用途	示例
`INTEGER`	32 位整数	100, -50
`BIGINT`	64 位整数	9223372036854775807
`DECIMAL(p,s)`	精确小数，适合金额	9999.99
`VARCHAR(n)`	可变长度字符串	'iPhone 15'
`TEXT`	长文本，不限长度	'详细商品描述...'
`TIMESTAMP`	时间戳，带时区	'2025-04-05 10:30:00'
`JSON`	存储 JSON 数据	'{"brand": "Apple"}'

注释：

VARCHAR(100) 最多存储 100 个字符，适合商品名称

DECIMAL(10,2) 保证金额精度，避免浮点误差

JSON 类型支持嵌套结构，适合存储配置或日志

约束类型详解

约束是保障数据质量的“守门员”。常见的约束有：

PRIMARY KEY：主键，唯一且非空
UNIQUE：唯一性，允许空值
NOT NULL：不能为空
CHECK：自定义条件校验
FOREIGN KEY：外键，引用其他表主键

示例：为订单表添加外键约束

-- 创建订单表，引用商品表
CREATE TABLE products.order_info (
    order_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products.product_info(product_id)
        ON DELETE CASCADE  -- 删除商品时，自动删除相关订单
);

注释：

FOREIGN KEY 建立表间关系

ON DELETE CASCADE 表示当商品被删除时，关联订单也自动删除

CHECK (quantity > 0) 防止下单数量为负

高级语法特性：让查询更智能

PostgreSQL 不止是“会写 SQL”，它还能让你写出更优雅、高效的查询语句。

使用别名简化复杂查询

当表名过长或查询复杂时，使用别名可以提升可读性：

-- 使用别名简化查询
SELECT p.name, o.quantity, o.total_amount
FROM products.product_info p
JOIN products.order_info o ON p.product_id = o.product_id
WHERE o.total_amount > 5000;

注释：

p 和 o 是表的别名，后续引用更简洁

JOIN 用于连接两个表，基于 product_id 匹配

聚合函数与分组查询

统计每类商品的总销售额：

-- 按商品名称分组，计算总销售额
SELECT 
    p.name,
    SUM(o.quantity * o.total_amount) AS total_revenue
FROM products.product_info p
JOIN products.order_info o ON p.product_id = o.product_id
GROUP BY p.name
ORDER BY total_revenue DESC;

注释：

SUM() 计算总和

GROUP BY 按商品名称分组

ORDER BY 降序排列，最赚钱的商品排前面

实用技巧与最佳实践

在实际开发中，掌握一些小技巧能极大提升效率和稳定性。

使用事务：多个操作应包裹在 BEGIN; ... COMMIT; 中，确保数据一致性
创建索引：对频繁查询的列（如 product_id）创建索引，加速查询
避免 SELECT *：只取需要的列，减少网络传输
定期备份：使用 pg_dump 工具备份数据库，防止数据丢失

总结

本文系统讲解了 PostgreSQL 语法的核心内容，从数据库创建、表定义，到数据操作、类型约束，再到高级查询技巧，层层递进，适合初学者快速入门，也适合中级开发者查漏补缺。

掌握 PostgreSQL 语法，不只是学会写 SQL，更是建立起对数据结构与逻辑的深刻理解。它能让你在处理复杂业务时，写出高效、安全、可维护的代码。

无论你是做 Web 开发、数据分析，还是构建微服务，PostgreSQL 都是一个值得深入学习的工具。从今天开始，动手写几条 SQL，让数据真正为你服务。