PostgreSQL 基础:建库建表与 CRUD

连接 PostgreSQL

假设已经通过 Docker 或系统包管理器安装了 PostgreSQL. 最常用的连接方式:

# 命令行客户端(psql)
$ psql -h localhost -p 5432 -U postgres

# 指定数据库
$ psql -h localhost -U postgres -d mydb

# Docker 方式快速启动一个临时实例
$ docker run --name pg-learn -e POSTGRES_PASSWORD=123456 -p 5432:5432 -d postgres:16

进入 psql 后,常用元命令:

命令 作用
\l 列出所有数据库
\c dbname 切换到指定数据库
\dt 列出当前库的所有表
\d tablename 查看表结构
\q 退出 psql

数据库操作

# 创建数据库
CREATE DATABASE shop;

# 删除数据库(谨慎!)
DROP DATABASE IF EXISTS shop;

# 创建时指定编码和所有者
CREATE DATABASE shop
  OWNER = postgres
  ENCODING = 'UTF8';

PostgreSQL vs MySQL

PostgreSQL 没有 USE dbname 语法. 切换数据库需要在 psql 中使用 \c dbname,或者新建连接时指定 -d 参数. 每个连接只能绑定一个数据库.

常用数据类型

PostgreSQL 的类型系统比 MySQL 更丰富.后端开发中最常用的类型:

类别 类型 说明
整数 SMALLINT 2 字节,-32768 ~ 32767
INTEGER 4 字节,约 ±21 亿(最常用)
BIGINT 8 字节,约 ±9.2×10¹⁸
浮点/精确 REAL / DOUBLE PRECISION 浮点数,有精度损失
NUMERIC(p, s) 精确小数,金额必用
字符串 VARCHAR(n) 变长字符串,限制最大长度
TEXT 变长字符串,无长度限制(推荐)
时间 TIMESTAMP 日期+时间(不带时区)
TIMESTAMPTZ 带时区的时间戳(推荐)
布尔 BOOLEAN true / false / null
自增 SERIAL / BIGSERIAL 自增整数(本质是序列 + DEFAULT)
JSON JSONB 二进制 JSON,可索引,可查询
UUID UUID 128 位全局唯一标识

TEXT vs VARCHAR

在 PostgreSQL 中,TEXT 和 VARCHAR 的底层存储完全一致,性能无差异. 如果不需要强制长度约束,直接用 TEXT 更省心. 这一点和 MySQL 不同.

建表与约束

CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  username    VARCHAR(50) NOT NULL UNIQUE,
  email       TEXT NOT NULL,
  password    TEXT NOT NULL,
  balance     NUMERIC(10, 2) DEFAULT 0.00,
  is_active   BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now()
);

常用约束一览:

约束 含义 示例
PRIMARY KEY 主键(NOT NULL + UNIQUE) id BIGSERIAL PRIMARY KEY
NOT NULL 不允许 NULL email TEXT NOT NULL
UNIQUE 唯一 username VARCHAR(50) UNIQUE
DEFAULT 默认值 balance NUMERIC DEFAULT 0
CHECK 检查条件 CHECK (balance >= 0)
REFERENCES 外键 user_id BIGINT REFERENCES users(id)

修改表结构(ALTER TABLE):

# 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

# 删除列
ALTER TABLE users DROP COLUMN phone;

# 修改列类型
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

# 添加约束
ALTER TABLE users ADD CONSTRAINT chk_balance CHECK (balance >= 0);

# 删除表
DROP TABLE IF EXISTS users CASCADE;

CRUD 操作

INSERT - 插入

# 插入单行
INSERT INTO users (username, email, password)
VALUES ('alice', 'alice@example.com', 'hashed_pw');

# 插入多行
INSERT INTO users (username, email, password) VALUES
  ('bob',   'bob@example.com',   'hashed_pw'),
  ('carol', 'carol@example.com', 'hashed_pw');

# 插入并返回生成的 id(PostgreSQL 特有,非常实用)
INSERT INTO users (username, email, password)
VALUES ('dave', 'dave@example.com', 'hashed_pw')
RETURNING id, created_at;

SELECT - 查询

# 查询所有列
SELECT * FROM users;

# 指定列 + 条件
SELECT id, username, balance FROM users WHERE is_active = true;

# 模糊匹配
SELECT * FROM users WHERE email LIKE '%@gmail.com';

# NULL 判断(注意:不能用 = NULL)
SELECT * FROM users WHERE phone IS NULL;

# 多条件组合
SELECT * FROM users
WHERE is_active = true
  AND balance > 100
  AND created_at > '2024-01-01';

UPDATE - 更新

# 单行更新
UPDATE users SET balance = balance + 50.00 WHERE id = 1;

# 多列更新
UPDATE users
SET email = 'new@example.com', is_active = false
WHERE username = 'bob';

# 更新并返回结果
UPDATE users SET balance = balance * 1.1
WHERE is_active = true
RETURNING id, username, balance;

DELETE - 删除

# 条件删除
DELETE FROM users WHERE is_active = false;

# 删除并返回被删除的行
DELETE FROM users WHERE id = 99 RETURNING *;

# 清空表(保留表结构,重置序列)
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

RETURNING 子句

这是 PostgreSQL 的杀手级特性之一. INSERT/UPDATE/DELETE 都支持 RETURNING,在一次网络往返中完成"写入 + 读回". 后端开发中可以减少一次额外的 SELECT 查询.

JOIN 速览

这里只做速查. 关键区别在于 NULL 的处理:

JOIN 类型 返回行 典型场景
INNER JOIN 两表都匹配的行 查询"有订单的用户"
LEFT JOIN 左表全部 + 右表匹配(不匹配填 NULL) 查询"所有用户及其订单(含无订单的)"
RIGHT JOIN 右表全部 + 左表匹配 用得少,LEFT JOIN 换表顺序即可
FULL OUTER JOIN 两表各自不匹配的行也保留 数据比对,找差异
CROSS JOIN 笛卡尔积 生成组合,日历表 
# 查询每个用户的订单数(含无订单的用户)
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;

示例数据库

后续所有篇章的练习统一使用以下电商场景数据库.请先执行这段建表脚本:

-- 用户表
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  username    VARCHAR(50) NOT NULL UNIQUE,
  email       TEXT NOT NULL,
  city        VARCHAR(50),
  balance     NUMERIC(10, 2) DEFAULT 0.00,
  is_vip      BOOLEAN DEFAULT false,
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- 商品分类表
CREATE TABLE categories (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(50) NOT NULL UNIQUE
);

-- 商品表
CREATE TABLE products (
  id           BIGSERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category_id  INTEGER REFERENCES categories(id),
  price        NUMERIC(10, 2) NOT NULL CHECK (price > 0),
  stock        INTEGER NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- 订单表
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  total       NUMERIC(10, 2) NOT NULL,
  status      VARCHAR(20) DEFAULT 'pending',  -- pending / paid / shipped / done / cancelled
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- 订单明细表
CREATE TABLE order_items (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL REFERENCES orders(id),
  product_id  BIGINT NOT NULL REFERENCES products(id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  unit_price  NUMERIC(10, 2) NOT NULL
);

-- 商品评价表
CREATE TABLE reviews (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id),
  product_id  BIGINT NOT NULL REFERENCES products(id),
  rating      SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  content     TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

插入示例数据:

-- 分类
INSERT INTO categories (name) VALUES
  ('电子产品'), ('图书'), ('服装'), ('食品');

-- 用户
INSERT INTO users (username, email, city, balance, is_vip) VALUES
  ('alice',   'alice@example.com',   '北京', 5000.00, true),
  ('bob',     'bob@example.com',     '上海', 1200.50, false),
  ('carol',   'carol@example.com',   '北京', 800.00,  true),
  ('dave',    'dave@example.com',    '广州', 300.00,  false),
  ('eve',     'eve@example.com',     '上海', 15000.00, true),
  ('frank',   'frank@example.com',   '深圳', 600.00,  false),
  ('grace',   'grace@example.com',   '北京', 2200.00, true),
  ('henry',   'henry@example.com',   '广州', 450.00,  false);

-- 商品
INSERT INTO products (name, category_id, price, stock) VALUES
  ('MacBook Pro 14',    1, 14999.00, 50),
  ('iPhone 15',         1, 6999.00,  200),
  ('AirPods Pro',       1, 1799.00,  500),
  ('深入理解计算机系统', 2, 139.00,   100),
  ('算法导论',          2, 128.00,   80),
  ('程序员的自我修养',   2, 89.00,    60),
  ('优衣库联名T恤',     3, 99.00,    1000),
  ('羽绒服',            3, 599.00,   200),
  ('坚果礼盒',          4, 168.00,   300),
  ('进口咖啡豆',        4, 88.00,    500);

-- 订单 + 明细
INSERT INTO orders (user_id, total, status, created_at) VALUES
  (1, 16798.00, 'done',      '2024-01-15 10:00:00+08'),
  (1, 1799.00,  'done',      '2024-02-20 14:30:00+08'),
  (2, 6999.00,  'shipped',   '2024-03-01 09:00:00+08'),
  (2, 228.00,   'done',      '2024-03-10 16:00:00+08'),
  (3, 139.00,   'done',      '2024-01-20 11:00:00+08'),
  (3, 14999.00, 'paid',      '2024-04-01 08:00:00+08'),
  (4, 99.00,    'cancelled', '2024-02-14 12:00:00+08'),
  (4, 256.00,   'done',      '2024-03-25 17:00:00+08'),
  (5, 22797.00, 'done',      '2024-01-05 09:30:00+08'),
  (5, 267.00,   'done',      '2024-02-28 15:00:00+08'),
  (6, 1799.00,  'pending',   '2024-04-05 10:00:00+08'),
  (7, 7266.00,  'done',      '2024-03-15 13:00:00+08'),
  (8, 168.00,   'done',      '2024-03-20 14:00:00+08');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 14999.00),  -- alice: MacBook
  (1, 3, 1, 1799.00),   -- alice: AirPods
  (2, 3, 1, 1799.00),   -- alice: AirPods
  (3, 2, 1, 6999.00),   -- bob: iPhone
  (4, 4, 1, 139.00),    -- bob: CSAPP
  (4, 6, 1, 89.00),     -- bob: 程序员的自我修养
  (5, 4, 1, 139.00),    -- carol: CSAPP
  (6, 1, 1, 14999.00),  -- carol: MacBook
  (7, 7, 1, 99.00),     -- dave: T恤
  (8, 9, 1, 168.00),    -- dave: 坚果
  (8, 10, 1, 88.00),    -- dave: 咖啡
  (9, 1, 1, 14999.00),  -- eve: MacBook
  (9, 2, 1, 6999.00),   -- eve: iPhone
  (9, 3, 1, 1799.00),   -- eve: AirPods(原价应为 799 折扣后)
  (10, 9, 1, 168.00),   -- eve: 坚果
  (10, 7, 1, 99.00),    -- eve: T恤
  (11, 3, 1, 1799.00),  -- frank: AirPods
  (12, 2, 1, 6999.00),  -- grace: iPhone
  (12, 10, 2, 88.00),   -- grace: 咖啡 x2
  (12, 6, 1, 89.00),    -- grace: 程序员的自我修养
  (13, 9, 1, 168.00);   -- henry: 坚果

-- 评价
INSERT INTO reviews (user_id, product_id, rating, content, created_at) VALUES
  (1, 1, 5, '性能强悍,开发利器', '2024-02-01 10:00:00+08'),
  (1, 3, 4, '降噪很好,通话一般', '2024-03-01 10:00:00+08'),
  (2, 2, 4, '手感不错,电池续航一般', '2024-03-15 10:00:00+08'),
  (2, 4, 5, '经典教材,常读常新', '2024-03-20 10:00:00+08'),
  (3, 4, 5, 'CSAPP 永远的神', '2024-02-01 10:00:00+08'),
  (5, 1, 5, '第二台了,稳定可靠', '2024-01-20 10:00:00+08'),
  (5, 2, 3, '发热有点严重', '2024-01-25 10:00:00+08'),
  (7, 2, 4, '总体满意', '2024-03-25 10:00:00+08'),
  (8, 9, 5, '送礼很合适', '2024-04-01 10:00:00+08'),
  (4, 9, 4, '性价比高', '2024-04-02 10:00:00+08');

练习

  1. 查询 VIP 用户 -- 查询所有 VIP 用户的 username 和 balance
  2. 分类筛选 -- 查询"电子产品"分类下价格超过 5000 的商品名和价格
  3. 时间过滤 -- 查询 2024 年 3 月之后创建的所有订单(含 3 月 1 日)
  4. LEFT JOIN 统计 -- 查询每个用户的用户名及其订单总数(包含没有订单的用户)
  5. RETURNING 子句 -- 给所有广州用户余额增加 100,返回修改后的 username 和 balance
参考答案
-- 1. VIP 用户
SELECT username, balance FROM users WHERE is_vip = true;
-- 结果: alice(5000), carol(800), eve(15000), grace(2200)

-- 2. 电子产品中价格 > 5000
SELECT p.name, p.price
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE c.name = '电子产品' AND p.price > 5000;
-- 结果: MacBook Pro 14(14999), iPhone 15(6999)

-- 3. 2024-03 及之后的订单
SELECT * FROM orders WHERE created_at >= '2024-03-01';
-- 返回 order id: 3,4,6,8,10,11,12,13

-- 4. 每用户订单数(含无订单)
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username
ORDER BY order_count DESC;
-- alice:2, bob:2, carol:2, dave:2, eve:2, frank:1, grace:1, henry:1

-- 5. RETURNING 用法
UPDATE users SET balance = balance + 100
WHERE city = '广州'
RETURNING username, balance;
-- dave: 400.00, henry: 550.00

第 4 题用 LEFT JOIN 确保没有订单的用户也出现在结果中,COUNT(o.id) 而非 COUNT(*) 是为了正确处理 NULL--当 o.id 为 NULL 时不计数.