开发者必看！PostgreSQL 核心介绍 + 高频常用SQL大全

11次阅读

在当下后端开发、数据分析、物联网存储场景中，数据库的选择直接决定项目的稳定性和扩展性。MySQL 凭借轻量化、易上手成为入门首选，而 PostgreSQL（简称 PG）则以「全能、稳定、高拓展」成为企业级中高端项目的核心数据库。

今天这篇博文，带大家从零认识 PostgreSQL，梳理开发中100%用得到的高频SQL语句，告别零散查文档，新手也能直接上手实操。

一、什么是 PostgreSQL？

PostgreSQL 是一款开源、免费、跨平台的对象关系型数据库（ORDBMS），遵循 BSD 开源协议，无商业版权限制，可免费用于个人、企业商用项目。它源自加州大学伯克利分校的 Ingres 项目，历经数十年迭代，是目前全球最标准、功能最完备的开源数据库之一。

不同于传统关系型数据库，PG 兼具关系型数据库的稳定性和非关系型数据库的灵活性，既支持标准 SQL 语法，又兼容 JSON、数组、地理信息、自定义类型等高级特性，被称为「最先进的开源数据库」。

二、PostgreSQL 核心优势（为什么选它？）

1. 极致稳定，事务百分百可靠

完全兼容 ACID 事务特性，支持事务回滚、断点续执行，杜绝数据脏读、幻读。金融、政务、电商订单等对数据一致性要求极高的场景，优先选择 PG。同时内置 MVCC（多版本并发控制），高并发读写场景下性能损耗极低。

2. 功能全能，适配多场景

除了基础的增删改查，原生支持JSON/JSONB、数组、全文检索、地理空间索引、窗口函数、CTE递归查询，无需额外插件即可实现复杂数据处理，尤其适合数据分析、复杂业务查询场景。

3. 高拓展、高性能

支持自定义函数、自定义数据类型、存储过程、触发器，可按需拓展数据库能力；支持分区表、并行查询、海量数据索引优化，轻松支撑千万、亿级数据存储与查询。

4. 开源免费、生态完善

BSD 协议允许自由修改、商用，无版权风险；跨 Windows、Linux、Mac 全平台，同时兼容绝大多数编程语言和框架，社区活跃、文档完善，问题排查成本极低。

三、PG 适用场景 vs 不适用场景

✅ 优先使用 PostgreSQL 的场景

复杂业务系统：金融、ERP、政务、订单系统（强事务、高数据一致性）
数据分析场景：报表统计、多维查询、海量数据聚合计算
非结构化数据存储：JSON 业务数据、用户画像、日志结构化存储
地理信息系统：GIS 地图、位置轨迹数据存储查询
高并发、大数据量长期存储项目

❌ 不推荐场景

极简小型项目、个人博客、低并发静态数据存储（MySQL 更轻量化、部署更简单，资源占用更低）。

四、PostgreSQL 高频常用SQL 大全（可直接复用）

整理开发日常必用、高频 SQL，涵盖数据库操作、表结构、增删改查、高级查询、索引、权限管理，适配 PG 专属语法，区别于 MySQL，避免踩坑。

1. 数据库基础操作

# 创建数据库（指定编码、所有者）
CREATE DATABASE test_db OWNER postgres ENCODING 'UTF8' LC_COLLATE 'zh_CN.UTF-8' LC_CTYPE 'zh_CN.UTF-8';

# 查看所有数据库
\l

# 切换数据库
\c test_db

# 删除数据库（谨慎使用）
DROP DATABASE IF EXISTS test_db;

2. 数据表结构操作

PG 常用基础数据类型：int、bigint、varchar(n)、text、timestamp、boolean、jsonb（推荐替代 json，支持索引）

# 创建数据表（含主键、默认值、非空、时间自动更新）
CREATE TABLE IF NOT EXISTS user_info (
    id BIGSERIAL PRIMARY KEY,  -- 自增主键（PG专属，替代 AUTO_INCREMENT）
    username VARCHAR(50) NOT NULL,
    age INT DEFAULT 0,
    phone VARCHAR(20) UNIQUE,
    profile JSONB,  -- 存储JSON结构化数据
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 查看表结构
\d user_info

# 修改表-新增字段
ALTER TABLE user_info ADD COLUMN email VARCHAR(100);

# 修改表-修改字段类型
ALTER TABLE user_info ALTER COLUMN age TYPE SMALLINT;

# 修改表-删除字段
ALTER TABLE user_info DROP COLUMN IF EXISTS email;

# 删除数据表
DROP TABLE IF EXISTS user_info;

3. 基础增删改查（CRUD）

# 新增数据（单条）
INSERT INTO user_info (username, age, phone, profile) 
VALUES ('张三', 25, '13800138000', '{"city":"北京","gender":"男"}');

# 新增数据（批量）
INSERT INTO user_info (username, age, phone)
VALUES 
('李四', 28, '13900139000'),
('王五', 23, '13700137000');

# 基础查询（全量、指定字段、条件筛选）
SELECT * FROM user_info;
SELECT username, age FROM user_info WHERE age >= 25;

# JSONB字段查询（PG核心特色语法）
SELECT username, profile->>'city' AS city 
FROM user_info 
WHERE profile @> '{"city":"北京"}';

# 更新数据
UPDATE user_info SET age = 26, update_time = CURRENT_TIMESTAMP WHERE username = '张三';

# 删除数据
DELETE FROM user_info WHERE username = '王五';

# 清空表数据（速度快、不记录日志，慎用）
TRUNCATE TABLE user_info;

4. 高级查询（开发高频）

# 分页查询（PG专属 LIMIT/OFFSET）
SELECT * FROM user_info ORDER BY id DESC LIMIT 10 OFFSET 0;

# 聚合统计
SELECT 
    COUNT(*) AS total_num,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM user_info;

# 分组查询 + 条件过滤
SELECT age, COUNT(*) AS user_num 
FROM user_info 
GROUP BY age 
HAVING COUNT(*) >= 1;

# 去重查询
SELECT DISTINCT age FROM user_info;

# 模糊查询
SELECT * FROM user_info WHERE username LIKE '%张%';

5. 索引操作（优化查询必备）

# 创建普通索引
CREATE INDEX idx_user_name ON user_info(username);

# 创建JSONB字段索引（提升JSON查询性能）
CREATE INDEX idx_user_profile ON user_info USING GIN(profile);

# 创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON user_info(phone);

# 查看表索引
\d user_info

# 删除索引
DROP INDEX IF EXISTS idx_user_name;

6. 事务操作（保证数据一致性）

PG 默认自动提交事务，复杂操作建议手动开启事务

# 开启事务
BEGIN;

# 执行多条SQL
UPDATE user_info SET age = 27 WHERE username = '张三';
INSERT INTO user_info (username, age) VALUES ('赵六', 30);

# 提交事务（生效）
COMMIT;

# 回滚事务（撤销所有操作）
ROLLBACK;

7. 权限与用户管理

# 创建用户
CREATE USER test_user WITH PASSWORD '123456';

# 授予数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;

# 授予表查询/修改权限
GRANT SELECT, INSERT, UPDATE ON user_info TO test_user;

# 撤销权限
REVOKE ALL PRIVILEGES ON DATABASE test_db FROM test_user;

# 删除用户
DROP USER IF EXISTS test_user;

8. 常用运维优化SQL

# 查看SQL执行计划（性能优化核心）
EXPLAIN ANALYZE SELECT * FROM user_info WHERE age > 20;

# 收集表统计信息，优化查询器
ANALYZE user_info;

# 查看当前数据库连接
SELECT * FROM pg_stat_activity;

# 清理数据库冗余数据、释放空间
VACUUM ANALYZE;

五、PG 与 MySQL 核心区别（快速避坑）

自增主键：PG 用 BIGSERIAL，MySQL 用 AUTO_INCREMENT
JSON 存储：PG 优先 JSONB（支持索引、高效查询），MySQL JSON 功能较弱
事务能力：PG 事务一致性更强，高并发读写性能更稳定
复杂查询：PG 原生支持窗口函数、CTE、递归查询，适配复杂数据分析
资源占用：MySQL 轻量化、启动快；PG 功能全面，资源占用略高

六、总结

PostgreSQL 早已不是小众数据库，凭借稳定、全能、开源免费的优势，成为企业级开发、数据分析、复杂业务系统的首选数据库。对于开发者而言，掌握 PG 基础语法和特色功能（JSONB、窗口函数、事务优化），能极大提升复杂业务的开发效率。

本文整理的 SQL 均为日常开发高频复用语句，建议收藏留存，开发时直接开箱即用！后续会持续更新 PG 性能优化、索引调优、实战场景案例。

正文完

可以使用微信扫码关注公众号（ID：xzluomor）

AI Linux Windows 博客商业大数据开发者开源数据库物联网

发表至：软件开发

近一天内

0

Git默认编辑器从Nano切换到Vim，新手也能一步到位

Redis 之父为 DeepSeek V4 单独造推理引擎：当极简主义遇上大模型效率革命

告别复制粘贴！Chrome 内置 Ask Gemini，你的浏览器专属AI管家

MacBook 合盖用外接显示器，电池供电黑屏？终极解决指南

2026 macOS PostgreSQL 客户端最全测评｜Mac 开发者首选工具推荐