PostgreSQL的“仓库-房间-货架”游戏，你能建出电商数据库和表吗？

一、PostgreSQL 数据库的层级结构

在开始创建数据库和表之前，我们需要先理解 PostgreSQL 的数据组织层级——这是后续操作的基础。PostgreSQL 用“集群-数据库-模式-表”的层级结构管理数据，就像一个“仓库-房间-货架-盒子”的模型：

1. 数据库集群（Cluster）：整个 PostgreSQL 服务实例，相当于一个大仓库，包含所有数据文件和配置。
2. 数据库（Database）：集群中的独立“房间”，每个数据库完全隔离（比如电商系统的ecommerce数据库和博客系统的blog数据库互不干扰）。
3. 模式（Schema）：数据库内的“货架”，用来组织表、视图等对象（默认模式是public）。
4. 表（Table）：模式中的“盒子”，存储具体的行和列数据。

我们可以用流程图直观展示这种关系：

graph TD
    A[PostgreSQL 集群] --> B[数据库1: ecommerce]
    A --> C[数据库2: blog]
    B --> D[模式1: public]
    B --> E[模式2: admin]
    D --> F[表1: users]
    D --> G[表2: orders]
    E --> H[表3: products]

二、创建 PostgreSQL 数据库

数据库是存储表的容器，创建数据库有命令行工具和SQL语句两种方式，我们逐一讲解。

2.1 用 createdb 命令行工具创建

createdb 是 PostgreSQL 自带的命令行工具，适合快速创建数据库。语法如下：

createdb [选项] 数据库名

常见选项：

• -h：指定数据库主机（默认localhost）；
• -p：指定端口（默认5432）；
• -U：指定连接用户（默认当前系统用户）；
• -O：指定数据库所有者。

示例：创建一个名为ecommerce的数据库，所有者为postgres用户：

createdb -U postgres ecommerce

2.2 用 CREATE DATABASE SQL 语句创建

如果已经通过psql或其他客户端连接到 PostgreSQL，可以用 SQL 语句创建数据库，灵活性更高。语法如下：

CREATE DATABASE 数据库名
[WITH 参数1 = 值1 参数2 = 值2 ...];

常用参数：

• OWNER：数据库所有者（默认当前用户）；
• ENCODING：字符编码（推荐UTF8，支持中文）；
• LC_COLLATE：排序规则（比如en_US.utf8或zh_CN.utf8）；
• TEMPLATE：基于哪个模板创建（默认template1）。

示例：创建一个支持中文的电商数据库：

CREATE DATABASE ecommerce
WITH 
  OWNER = postgres
  ENCODING = 'UTF8'
  LC_COLLATE = 'zh_CN.utf8'  -- 中文排序规则（提示：排序规则不兼容，可更换或删除）
  LC_CTYPE = 'zh_CN.utf8'    -- 中文字符分类（提示：排序规则不兼容，可更换或删除）
  TABLESPACE = pg_default    -- 表空间（默认即可）
  CONNECTION LIMIT = -1;     -- 无连接数限制

查询当前数据库支持的中文排序规则：

SELECT collname FROM pg_collation WHERE collname LIKE 'zh%';

2.3 验证数据库是否创建成功

用psql连接到 PostgreSQL，输入l命令（List Databases），如果看到ecommerce出现在列表中，说明创建成功：

psql -U postgres
postgres=# l

三、创建 PostgreSQL 表

表是存储数据的核心对象，创建表需要定义列名、数据类型和约束（确保数据的完整性）。

3.1 表的基本结构

创建表的 SQL 语句是CREATE TABLE，基本语法：

CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
    [表级约束]
);

3.2 关键概念：数据类型

PostgreSQL 支持丰富的数据类型，常用的有：

详细数据类型参考：PostgreSQL 数据类型文档

3.3 关键概念：约束

约束是保证数据正确性的规则，常用约束包括：

1. 主键（PRIMARY KEY）：唯一标识表中的行，非空且唯一（一个表只能有一个主键）；
2. 外键（FOREIGN KEY）：关联其他表的主键，保证数据一致性；
3. 非空（NOT NULL）：字段不能为NULL；
4. 唯一（UNIQUE）：字段值唯一；
5. 检查（CHECK）：自定义条件（比如age >= 18）。

3.4 实战：创建电商系统的表

我们以电商系统为例，创建users（用户表）和orders（订单表），完整流程如下：

步骤1：连接到目标数据库

首先用c命令切换到ecommerce数据库：

c ecommerce;

步骤2：创建users表（用户表）

users表需要存储用户的ID、用户名、邮箱、密码哈希和注册时间：

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,  -- SERIAL：自增整数（自动创建序列），作为主键
    username VARCHAR(50) NOT NULL UNIQUE,  -- 用户名：非空且唯一
    email VARCHAR(100) NOT NULL UNIQUE,    -- 邮箱：非空且唯一
    password_hash VARCHAR(255) NOT NULL,   -- 密码哈希：非空（不能存明文！）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 注册时间：默认当前时间
);

说明：

• SERIAL类型会自动创建一个序列（比如users_user_id_seq），每次插入数据时自动生成下一个ID；
• DEFAULT CURRENT_TIMESTAMP表示如果插入时不指定created_at，自动填充当前时间。

步骤3：创建orders表（订单表）

orders表需要关联users表（通过user_id外键），并记录订单金额、时间：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,  -- 关联用户表的user_id
    order_total NUMERIC(10,2) NOT NULL CHECK (order_total > 0),  -- 订单金额：精确到分，且必须大于0
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 外键约束：user_id必须存在于users表的user_id中
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

关键约束说明：

• CHECK (order_total > 0)：确保订单金额不为0或负数；
• FOREIGN KEY (user_id) REFERENCES users(user_id)：orders的user_id必须是users表中已有的ID；
• ON DELETE CASCADE：如果删除users表中的某条用户数据，orders表中关联的订单会自动删除（避免无效订单）。

步骤4：验证表是否创建成功

用d命令（List Tables）查看当前数据库的表：

d

会看到users和orders表出现在列表中，说明创建成功。

四、插入与查询数据（验证表结构）

创建表后，我们插入一些测试数据，验证表的约束是否生效。

4.1 插入用户数据

INSERT INTO users (username, email, password_hash)
VALUES 
('alice', '[email protected]', 'hashed_pass_123'),  -- 有效数据
('bob', '[email protected]', 'hashed_pass_456');     -- 有效数据

注意：不需要指定user_id和created_at，因为SERIAL和DEFAULT会自动填充。

4.2 插入订单数据

INSERT INTO orders (user_id, order_total)
VALUES 
(1, 99.99),  -- 用户1（alice）的订单，金额99.99
(2, 199.50); -- 用户2（bob）的订单，金额199.50

测试无效数据：如果尝试插入order_total = 0的订单：

INSERT INTO orders (user_id, order_total) VALUES (1, 0);

会报错：

ERROR:  check constraint "orders_order_total_check" violated

这说明检查约束生效了！

4.3 查询关联数据

用JOIN语句查询用户的订单信息：

SELECT 
    u.username,    -- 用户名
    o.order_id,    -- 订单ID
    o.order_total, -- 订单金额
    o.order_date   -- 下单时间
FROM users u
JOIN orders o ON u.user_id = o.user_id;

结果：

五、课后 Quiz：巩固所学知识

1. 题目1：写出创建名为blog的数据库的SQL语句，要求：

   • 所有者为admin用户；
   • 字符编码为UTF8；
   • 连接数限制为100。 答案：

   CREATE DATABASE blog
   WITH 
     OWNER = admin
     ENCODING = 'UTF8'
     CONNECTION LIMIT = 100;
   

   解析：参考CREATE DATABASE的参数说明（链接）。

2. 题目2：为什么orders表要使用FOREIGN KEY (user_id) REFERENCES users(user_id)？ 答案：确保订单数据的一致性——不能存在“属于不存在的用户”的订单。如果删除users表中的某条用户数据，ON DELETE CASCADE会自动删除关联的订单（避免无效数据）。 解析：参考外键约束文档（链接）。

3. 题目3：users表中的username字段有UNIQUE约束，插入重复的用户名会发生什么？ 答案：会报错ERROR: duplicate key value violates unique constraint "users_username_key"，因为违反了唯一约束。 解析：参考唯一约束文档（链接）。

六、常见报错及解决方案

在创建数据库或表时，你可能遇到以下错误，我们给出原因、解决办法和预防建议：

6.1 错误1：ERROR: database "ecommerce" already exists

• 原因：要创建的数据库已经存在。
• 解决：删除现有数据库（DROP DATABASE ecommerce;），或换一个数据库名。
• 预防：用l命令查看现有数据库，避免重复。

6.2 错误2：ERROR: syntax error at or near "SERIAL"

• 原因：SERIAL拼写错误（比如写成SERAL），或在不支持的位置使用（比如外键字段不能用SERIAL）。
• 解决：检查SERIAL的拼写，确保只在主键或自增字段使用。
• 预防：参考SERIAL类型文档（链接）。

6.3 错误3：ERROR: insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"

• 原因：插入的user_id在users表中不存在（比如插入user_id = 3，但users表中只有1和2）。
• 解决：先插入对应的用户数据，或修正user_id为存在的值。
• 预防：插入订单前，用SELECT * FROM users WHERE user_id = ?检查用户是否存在。

6.4 错误4：ERROR: permission denied to create database

• 原因：当前用户没有创建数据库的权限。
• 解决：切换到超级用户（比如postgres）创建，或给用户授予CREATEDB权限：

  ALTER USER myuser CREATEDB;
  

• 预防：创建用户时明确授予权限（CREATE USER myuser WITH CREATEDB;）。

参考链接

以下是本文参考的 PostgreSQL 官方文档链接，建议深入阅读：

1. 数据库概念：www.postgresql.org/docs/17/
2. 创建数据库：www.postgresql.org/docs/17/tut…
3. 创建表：www.postgresql.org/docs/17/tut…
4. CREATE DATABASE 语句：www.postgresql.org/docs/17/sql…
5. CREATE TABLE 语句：www.postgresql.org/docs/17/sql…
6. 数据类型：www.postgresql.org/docs/17/dat…
7. 约束：www.postgresql.org/docs/17/ddl…
8. 错误代码：www.postgresql.org/docs/17/err…