📚 PostgreSQL 核心操作指南（15+ 版本）

精华速查手册 | 告别复杂文档 | 聚焦高频操作

🧩 基础操作四连击

➕ 数据插入

-- 单条插入带返回值（适合获取自增ID）
INSERT INTO users (username, email) 
VALUES ('Alice', 'alice@example.com') 
RETURNING id; -- ✨ 立刻返回生成ID

-- 批量插入（商品示例）
INSERT INTO products (name, price) 
VALUES 
('🖱️ 鼠标', 29.99),
('⌨️ 键盘', 59.99),
('🖥️ 显示器', 199.99);

➖ 数据删除

-- 关联删除美国客户的订单
DELETE FROM orders
USING customers -- 🤝 多表关联
WHERE orders.customer_id = customers.id
AND customers.country = 'US';

✏️ 数据更新

-- 给工程师加薪 5%
UPDATE employees
SET salary = salary * 1.05
FROM departments -- 🎯 精准定位部门
WHERE employees.dept_id = departments.id
AND departments.name = 'Engineering';

🔍 数据查询

-- 查询最近7天订单（CTE优雅写法）
WITH recent_orders AS (
    SELECT * FROM orders 
    WHERE order_date > NOW() - INTERVAL '7 days' -- ⏰ 时间筛选
)
SELECT customer_id, COUNT(*) 
FROM recent_orders
GROUP BY customer_id;

👥 用户权限管理

🔑 角色控制

-- 创建开发者角色（带有效期）
CREATE ROLE dev_user WITH 
    LOGIN 
    PASSWORD 'SecurePass123!' -- 🔐 强制加密
    VALID UNTIL '2024-12-31'; -- ⏳ 到期自动失效

-- 查看角色详情
\du+ -- 👀 显示所有角色权限

🛡️ 权限配置

-- 授权示例（最小权限原则）
GRANT 
    SELECT, INSERT, 
    UPDATE (price, stock) -- 🎚️ 列级控制
ON products 
TO dev_user;

-- 默认权限设置（影响未来新建表）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analyst_role; -- 📊 分析师只读权限

🆕 15+ 版本特性

功能	旧版本	v15+ 新特性
密码加密	md5	scram-sha-256（更安全🔒）
逻辑复制	全量复制	支持过滤订阅（节省资源📉）

-- 创建带过滤的订阅
CREATE SUBSCRIPTION sales_sub 
    CONNECTION 'host=primary.db dbname=main'
    PUBLICATION sales_pub
    WITH (copy_data = false); -- 🚫 不复制历史数据

🛠️ 运维工具箱

🔍 系统监控

-- 查看实时连接
SELECT * FROM pg_stat_activity; -- 🌐 显示所有会话

-- 检查锁状态
SELECT * FROM pg_locks; -- 🔒 排查锁冲突

💾 备份恢复

# 高效二进制备份
pg_dump -Fc -U postgres sales_db > sales.dump

# 恢复备份（保留原结构）
pg_restore -d new_sales -U postgres sales.dump

🆚 与 MySQL 主要差异

功能	PostgreSQL	MySQL
权限体系	角色继承 + 细粒度列权限	简单全局权限
事务控制	DDL 可回滚	DDL 自动提交
JSON支持	JSONB 高性能二进制存储	JSON 文本存储
对象引用	严格区分大小写	不区分（Windows/Mac）

💡 最佳实践

安全加固

ALTER ROLE admin_user SET search_path = secure_schema; -- 🛡️ 限制搜索路径

性能优化

VACUUM ANALYZE orders; -- 🧹 定期清理+统计信息更新

连接控制

# postgresql.conf 调优
max_connections = 100         -- 🚦 最大连接数
shared_buffers = 4GB          -- 💾 内存分配

📌 记住三个快捷命令：

\d+ 表名 → 查看表结构详情

\l → 列出所有数据库

\timing → 开启SQL执行时间显示