PostgreSQL

这一课你将能做到

1. 用 SQL 语句创建表、插入数据、查询数据
2. 理解索引的作用并创建索引
3. 用 Node.js 连接 PostgreSQL 并执行查询

为什么要学

数据不能只存在内存里，重启就丢了。PostgreSQL 是关系型数据库，把数据持久化到硬盘，支持复杂查询、事务、并发访问。比 MySQL 功能更强，支持 JSON、数组、全文检索。

人话：PostgreSQL 是「文件柜」，把数据分门别类存好，随时能快速找到。

先建立图像

-- 创建表
CREATE TABLE course (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入数据
INSERT INTO course (title, description) VALUES
    ('HTML', '网页骨架'),
    ('CSS', '网页皮肤');

-- 查询数据
SELECT * FROM course WHERE title LIKE 'H%';

结果：

 id | title | description  | created_at
----+-------+--------------+----------------------------
  1 | HTML  | 网页骨架     | 2025-01-15 10:30:00

跟着做

步骤 1：安装与连接

# macOS
brew install postgresql
brew services start postgresql

# Ubuntu
sudo apt install postgresql
sudo systemctl start postgresql

# 连接
psql -U postgres

步骤 2：数据库与表操作

-- 创建数据库
CREATE DATABASE codeforge;

-- 切换数据库
\c codeforge

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 查看表结构
\d users

-- 删除表
DROP TABLE IF EXISTS users;

步骤 3：CRUD 操作

-- 插入
INSERT INTO users (username, email, password_hash)
VALUES ('admin', 'admin@example.com', 'hashed_password');

-- 查询
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;
SELECT * FROM users WHERE username LIKE 'adm%' ORDER BY created_at DESC LIMIT 10;

-- 更新
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

步骤 4：索引（加速查询）

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- 查看索引
\d users

-- 删除索引
DROP INDEX idx_users_email;

索引像书的目录，加速 WHERE email = '...' 查询，但减慢插入/更新（要维护索引）。

步骤 5：关联查询

-- 课程表
CREATE TABLE course (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category_id INTEGER
);

-- 分类表
CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 插入关联数据
INSERT INTO category (name) VALUES ('前端'), ('后端');
INSERT INTO course (title, category_id) VALUES
    ('HTML', 1),
    ('Node.js', 2);

-- JOIN 查询
SELECT c.title, cat.name AS category
FROM course c
JOIN category cat ON c.category_id = cat.id;

步骤 6：事务（保证数据一致性）

BEGIN;

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 如果出错，回滚
-- ROLLBACK;

-- 确认无误，提交
COMMIT;

步骤 7：Node.js 连接 PostgreSQL

npm install pg

const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    user: 'postgres',
    password: '123456',
    database: 'codeforge',
    port: 5432
});

// 查询
app.get('/api/users', async (req, res) => {
    try {
        const result = await pool.query('SELECT id, username, email FROM users');
        res.json({ code: 0, data: result.rows });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ code: 500, msg: '服务器错误' });
    }
});

// 参数化查询（防 SQL 注入）
app.get('/api/users/:id', async (req, res) => {
    const { id } = req.params;
    const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
    res.json({ code: 0, data: result.rows[0] });
});

讲清楚要点

1. 关系型数据库：数据存表，表之间用外键关联。
2. SQL 是声明式语言：告诉数据库「要什么」，不用管「怎么找」。
3. 索引加速查询：但占用空间、减慢写入，不是越多越好。
4. 事务保证一致性：要么全部成功，要么全部失败。
5. 参数化查询防注入：$1、$2 占位符，不要拼接字符串。

你可能会懵的地方

• PostgreSQL 和 MySQL 选哪个？ PostgreSQL 功能更强（JSON、数组、全文检索）；MySQL 更轻量、生态更大。中小项目都行，看团队熟悉度。

• SERIAL 和 UUID 选哪个？ SERIAL 是自增整数，简单高效；UUID 全局唯一，适合分布式。单机用 SERIAL 足够。

• VARCHAR 和 TEXT 区别？ VARCHAR 有长度限制，TEXT 无限制。VARCHAR 性能略好，能预估长度就用 VARCHAR。

• 索引越多越好吗？ 不是。索引占空间，插入/更新时要维护索引，减慢写入。只对常用查询字段建索引。

• 事务隔离级别？ READ COMMITTED（默认）、REPEATABLE READ、SERIALIZABLE。级别越高，并发性能越差，一致性越强。

错法对照

错法	问题	正解
SELECT * FROM users WHERE id = '1'	字符串隐式转换，可能不走索引	用参数化查询，传整数
拼接 SQL 字符串	SQL 注入风险	用 $1、$2 占位符
忘记 COMMIT	数据不持久化	确认无误后提交
大事务锁表	长时间锁定，并发差	拆成小事务，尽快提交
删除表不带 IF EXISTS	表不存在时报错	DROP TABLE IF EXISTS

卡点急救

1. 连接失败：检查 PostgreSQL 是否启动（pg_isready）、端口 5432、用户名密码。
2. 权限不足：用 GRANT ALL PRIVILEGES ON DATABASE codeforge TO user; 授权。
3. 查询慢：用 EXPLAIN ANALYZE SELECT ... 查看执行计划，确认是否走索引。

面试常问

• 索引原理？ B+ 树结构，叶子节点存数据或指针，非叶子节点存索引键。查询时从根节点二分查找，O(log n) 复杂度。

• 事务 ACID？ 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• MVCC 是什么？ 多版本并发控制，读不加锁，写时创建新版本。PostgreSQL 用 MVCC 实现高并发。

• 如何优化慢查询？ 加索引、优化 SQL（避免 SELECT *）、分页用 LIMIT + OFFSET 或游标、必要时拆表。

自测三问

1. SERIAL PRIMARY KEY 的作用？
2. 为什么参数化查询能防 SQL 注入？
3. BEGIN 和 COMMIT 之间出错怎么办？

小结一句话

PostgreSQL 用表存数据，SQL 查询，索引加速，事务保证一致性，参数化查询防注入。

延伸

• PostgreSQL 官方文档
• 下一课：Redis 内存数据库做缓存。