PostgreSQL: PostgreSQL基础综合练习

Q: JSONB metadata 里的 year 字段用什么类型？

JSONB 内部的数字默认是 numeric 类型。提取时用 ->>'year' （返回 TEXT）再 ::INTEGER 转型。如果频繁按 year 查询，建议加一个 GENERATED 列或表达式索引。

最后更新：2026-08-26

学完了基础 6 课，是时候把所有知识串联起来做一个完整项目了——本文带你从零搭建一个在线书店的数据库。

1. 你将学到

需求分析到表设计的完整流程
创建数据库与切换连接
设计带约束的多张关联表
批量插入与 UPSERT 数据导入
简单查询验证数据正确性
修改表结构与清理

2. 一个独立开发者的真实故事

(1) 痛点：独立完成数据库初始化

Alice 是一名独立开发者，她需要为在线书店项目搭建数据库。需求如下：

5 张核心表：用户、书籍、分类、订单、评论
用户邮箱唯一，密码不能为空
书籍价格必须 > 0，ISBN 唯一
订单关联用户和书籍
评论必须有评分（1-5 分）
需要导入一批初始数据，部分书籍可能已存在（需 UPSERT）

(2) 完整数据库搭建流程

Alice 用以下步骤从零完成数据库初始化：

SQL

-- Step 1: Create database
CREATE DATABASE bookstore;

-- Step 2: Connect to the new database
\c bookstore

-- Step 3-7: Create tables with proper types and constraints
-- (full SQL below in Section 6)

(3) 收益

完整的数据库设计经验（从需求到 SQL）
5 张表 + 约束 + 外键级联 = 生产级设计
UPSERT 批量导入 = 现实场景技能
30 分钟完成 = 独立交付能力

3. 需求分析

(1) 在线书店 ER 图

erDiagram
    USERS ||--o{ ORDERS : places
    BOOKS ||--o{ ORDER_ITEMS : contains
    CATEGORIES ||--o{ BOOKS : has
    USERS ||--o{ REVIEWS : writes
    BOOKS ||--o{ REVIEWS : receives
    ORDERS ||--|| ORDER_ITEMS : includes

    USERS {
        int id PK
        varchar email UK
        varchar name
        varchar password_hash
        timestamptz created_at
    }
    CATEGORIES {
        int id PK
        varchar name UK
        text description
    }
    BOOKS {
        int id PK
        varchar isbn UK
        varchar title
        decimal price
        int stock
        int category_id FK
        jsonb metadata
    }
    ORDERS {
        int id PK
        int user_id FK
        varchar status
        decimal total
        timestamptz created_at
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int book_id FK
        int quantity
        decimal unit_price
    }
    REVIEWS {
        int id PK
        int user_id FK
        int book_id FK
        smallint rating
        text content
        timestamptz created_at
    }

(2) 表与约束清单

表	列数	关键约束
users	5	email UNIQUE NOT NULL, password_hash NOT NULL
categories	3	name UNIQUE
books	7	isbn UNIQUE, price > 0 CHECK, stock >= 0 CHECK, category_id FK
orders	5	user_id FK CASCADE, status CHECK, total >= 0 CHECK
order_items	5	order_id FK CASCADE, book_id FK RESTRICT, quantity > 0 CHECK
reviews	6	user_id FK SET NULL, book_id FK CASCADE, rating 1-5 CHECK

4. 逐步搭建

(1) 创建数据库并连接

▶ 示例：创建书店数据库

SQL

-- Create the bookstore database
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- Connect to the new database
\c bookstore

TEXT 📖 仅展示

CREATE TABLE

(2) 创建分类表

▶ 示例：categories 表

SQL

-- Categories table: book genres
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Insert initial categories
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Books about software development and programming languages'),
    ('Database', 'Database design, SQL, and data management'),
    ('Data Science', 'Machine learning, statistics, and data analysis'),
    ('DevOps', 'CI/CD, cloud computing, and infrastructure'),
    ('Web Development', 'Frontend and backend web technologies');

TEXT 📖 仅展示

INSERT 0 1

(3) 创建用户表

▶ 示例：users 表

SQL

-- Users table: bookstore customers
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample users
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice_1234567890abcdefghijklmnopqrs'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob_1234567890abcdefghijklmnopqrstuvwx'),
    ('charlie@example.com', 'Charlie', '$2a$12$hash_charlie_1234567890abcdefghijklmno')
RETURNING id, email, name;

TEXT 📖 仅展示

INSERT 0 1

(4) 创建书籍表

▶ 示例：books 表

SQL

-- Books table: the core product
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample books with metadata
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb),
    ('9781098118283', 'Kubernetes Up and Running', 54.99, 45, 4,
     '{"author": "Brendan Burns", "pages": 300, "edition": 3}'::jsonb),
    ('9781718500417', 'CSS in Depth', 42.99, 90, 5,
     '{"author": "Keith Grant", "pages": 432}'::jsonb)
RETURNING id, title, price;

TEXT 📖 仅展示

INSERT 0 1

(5) 创建订单和订单项表

▶ 示例：orders + order_items 表

SQL

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Order items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

-- Insert a sample order
INSERT INTO orders (user_id, status, total_amount) VALUES
    (1, 'paid', 84.98)
RETURNING id;

-- Insert order items (assume order id = 1)
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99),
    (1, 2, 1, 44.99);

TEXT 📖 仅展示

INSERT 0 1

(6) 创建评论表

▶ 示例：reviews 表

SQL

-- Reviews table: user reviews for books
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample reviews
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent Python Tips',
     'This book covers many practical Python tips that I use daily in my work.'),
    (2, 2, 4, 'Good PostgreSQL Introduction',
     'Great for beginners, but could use more advanced topics like partitioning.'),
    (3, 3, 5, 'Must-have for Data Scientists',
     'Comprehensive coverage of NumPy, Pandas, Matplotlib, and Scikit-learn.')
RETURNING id, rating, title;

TEXT 📖 仅展示

INSERT 0 1

5. UPSERT 批量导入

▶ 示例：批量导入书籍（部分已存在）

SQL

-- Import new batch of books: some already exist (by isbn), some are new
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 35.99, 150, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 49.99, 100, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781119557265', 'SQL for Data Analysis', 34.99, 200, 2,
     '{"author": "Ulka Rodgers", "pages": 288}'::jsonb),
    ('9781484254555', 'PostgreSQL High Performance', 59.99, 30, 2,
     '{"author": "Ibragimov", "pages": 400}'::jsonb)
ON CONFLICT (isbn)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = books.stock + EXCLUDED.stock
RETURNING id, title,
    CASE WHEN xmax = 0 THEN 'NEW' ELSE 'UPDATED' END AS operation;

TEXT 📖 仅展示

 id |            title             | operation
----+------------------------------+-----------
  1 | Effective Python             | UPDATED
  2 | Learning PostgreSQL          | UPDATED
  6 | SQL for Data Analysis        | NEW
  7 | PostgreSQL High Performance  | NEW

6. 验证查询

▶ 示例：数据完整性验证

SQL

-- 1. Count records in each table
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

-- 2. Verify foreign key relationships
SELECT o.id AS order_id, u.name AS customer,
       b.title, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;

-- 3. Verify constraints work (should fail)
-- INSERT INTO books (isbn, title, price, stock) VALUES ('test', 'Test', -10, 5);
-- ERROR: check constraint "books_price_check" violated

-- INSERT INTO reviews (user_id, book_id, rating) VALUES (1, 1, 6);
-- ERROR: check constraint "reviews_rating_check" violated

-- 4. Verify UPSERT results
SELECT isbn, title, price, stock
FROM books
WHERE isbn IN ('9780134685991', '9781119557265')
ORDER BY isbn;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

7. 修改表结构

▶ 示例：ALTER TABLE 迭代改进

SQL

-- Add a published_year column to books
ALTER TABLE books ADD COLUMN published_year INTEGER;

-- Add an is_featured flag to books
ALTER TABLE books ADD COLUMN is_featured BOOLEAN DEFAULT false;

-- Update published_year from metadata JSONB
UPDATE books
SET published_year = (metadata->>'year')::INTEGER
WHERE metadata ? 'year';

-- Add a phone column to users
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Verify changes
\d books
\d users

TEXT 📖 仅展示

-- SQL 语句执行成功

8. 完整示例：一键初始化脚本

SQL

-- ============================================
-- Complete example: Bookstore database init script
-- Run this from psql connected to postgres DB
-- ============================================

-- 1. Create database
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- 2. Connect to the new database
\c bookstore

-- 3. Create all tables in dependency order
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Insert sample data
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Software development and programming languages'),
    ('Database', 'Database design, SQL, and data management'),
    ('Data Science', 'Machine learning and data analysis');

INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob');

INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb);

INSERT INTO orders (user_id, total_amount) VALUES (1, 84.98);
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99), (1, 2, 1, 44.99);

INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent', 'Practical Python tips.'),
    (2, 2, 4, 'Good intro', 'Great for beginners.');

-- 5. Verify
SELECT 'categories' AS t, COUNT(*) FROM categories
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

TEXT 📖 仅展示

     t      | count
------------+-------
 categories |     3
 users      |     2
 books      |     3
 orders     |     1
 reviews    |     2

❓ 常见问题

Q 创建表的顺序重要吗？

A 重要！因为外键引用的表必须先存在。正确的顺序是：先建被引用的表（categories, users），再建引用它们的表（books, orders, reviews），最后建关联表（order_items）。

Q ON DELETE CASCADE 和 RESTRICT 怎么选？

A CASCADE 适合"子记录随父记录一起删除"的场景（如删用户→删订单）。RESTRICT 适合"有子记录就不允许删父记录"的场景（如订单项引用的书不能被删除）。一般原则：业务上允许级联删的用 CASCADE，不允许的用 RESTRICT。

Q UPSERT 时 xmax = 0 是什么意思？

A xmax 是 PG 系统列，记录删除/更新该行的事务 ID。INSERT 操作不设 xmax（值为 0），UPDATE/DELETE 会设 xmax。因此 xmax = 0 表示行是新插入的，xmax != 0 表示行是被更新的。这是一个 PG 内部技巧。

Q 临时表（TEMP TABLE）和普通表有什么区别？

A 临时表只在当前会话中可见，会话结束自动删除。适合存储导入中间数据、计算临时结果。优点：不污染公共命名空间，无需手动清理。

Q JSONB metadata 里的 year 字段用什么类型？

A JSONB 内部的数字默认是 numeric 类型。提取时用 ->>'year'（返回 TEXT）再 ::INTEGER 转型。如果频繁按 year 查询，建议加一个 GENERATED 列或表达式索引。

Q 这个书店数据库能用于生产吗？

A 结构上可以，但还缺几个生产要素：1）索引优化（后续课程）；2）审计字段（updated_at）；3）软删除（deleted_at）；4）数据库用户权限（非 root 连接）；5）备份策略。后续课程逐步补充。

📖 小节

数据库设计流程：需求分析 → ER 图 → 建表顺序 → 约束设计 → 数据导入 → 验证
建表顺序遵循外键依赖：被引用表先建，引用表后建
约束保护数据质量：UNIQUE（唯一性）/ CHECK（条件校验）/ FK（引用完整性）/ NOT NULL（非空）
UPSERT 批量导入：ON CONFLICT DO UPDATE 处理重复数据
RETURNING 子句验证操作结果
ALTER TABLE 迭代改进表结构（加列、改约束）
综合查询验证数据完整性：JOIN 连表 + COUNT 统计

📝 作业

基础题（难度⭐）：按照本课的步骤，从零创建 bookstore 数据库和所有 6 张表，插入样本数据后运行验证查询，确保每张表的记录数与示例一致。
进阶题（难度⭐⭐）：在 bookstore 数据库上执行 UPSERT：导入 4 条书籍数据（其中 2 条 ISBN 已存在），已存在的书籍更新价格和增加库存，新书籍正常插入。用 RETURNING 显示每条记录是 NEW 还是 UPDATED。
挑战题（难度⭐⭐⭐）：给 bookstore 数据库添加一张 book_authors 多对多关联表（一本书可以有多个作者，一个作者可以写多本书），以及 authors 作者表。设计表结构（含外键和约束），插入 3 个作者和关联数据，然后写一条 JOIN 查询显示每本书的所有作者名称。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL基础综合练习

1. 你将学到

2. 一个独立开发者的真实故事

(1) 痛点：独立完成数据库初始化

(2) 完整数据库搭建流程

(3) 收益

3. 需求分析

(1) 在线书店 ER 图

(2) 表与约束清单

4. 逐步搭建

(1) 创建数据库并连接

▶ 示例：创建书店数据库

(2) 创建分类表

▶ 示例：categories 表

(3) 创建用户表

▶ 示例：users 表

(4) 创建书籍表

▶ 示例：books 表

(5) 创建订单和订单项表

▶ 示例：orders + order_items 表

(6) 创建评论表

▶ 示例：reviews 表

5. UPSERT 批量导入

▶ 示例：批量导入书籍（部分已存在）

6. 验证查询

▶ 示例：数据完整性验证

7. 修改表结构

▶ 示例：ALTER TABLE 迭代改进

8. 完整示例：一键初始化脚本

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好