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

一、PostgreSQL 数据库的层级结构

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

数据库集群（Cluster）：整个 PostgreSQL 服务实例，相当于一个大仓库，包含所有数据文件和配置。
数据库（Database）：集群中的独立“房间”，每个数据库完全隔离（比如电商系统的ecommerce数据库和博客系统的blog数据库互不干扰）。
模式（Schema）：数据库内的“货架”，用来组织表、视图等对象（默认模式是public）。
表（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 支持丰富的数据类型，常用的有：

类型分类	示例类型	说明
数值型	`INT`（整数）、`BIGINT`（长整数）、`NUMERIC(10,2)`（精确小数，保留2位）	存储金额、数量等
字符串型	`VARCHAR(50)`（可变长度字符串，最长50）、`TEXT`（无长度限制）	存储用户名、邮箱等
日期时间型	`TIMESTAMP`（带时区的时间戳）、`DATE`（日期）、`TIME`（时间）	存储下单时间、注册时间等
布尔型	`BOOLEAN`	存储状态（比如`is_active`表示用户是否激活）

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

3.3 关键概念：约束

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

主键（PRIMARY KEY）：唯一标识表中的行，非空且唯一（一个表只能有一个主键）；
外键（FOREIGN KEY）：关联其他表的主键，保证数据一致性；
非空（NOT NULL）：字段不能为NULL；
唯一（UNIQUE）：字段值唯一；
检查（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', 'alice@example.com', 'hashed_pass_123'),  -- 有效数据
('bob', 'bob@example.com', '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;

结果：

username	order_id	order_total	order_date
alice	1	99.99	2024-05-20 14:30:00
bob	2	199.50	2024-05-20 14:31:00

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

题目1：写出创建名为blog的数据库的SQL语句，要求：
- 所有者为admin用户；
- 字符编码为UTF8；
- 连接数限制为100。答案：
```
CREATE DATABASE blog
WITH 
  OWNER = admin
  ENCODING = 'UTF8'
  CONNECTION LIMIT = 100;
```
解析：参考CREATE DATABASE的参数说明（链接）。
题目2：为什么orders表要使用FOREIGN KEY (user_id) REFERENCES users(user_id)？答案：确保订单数据的一致性——不能存在“属于不存在的用户”的订单。如果删除users表中的某条用户数据，ON DELETE CASCADE会自动删除关联的订单（避免无效数据）。解析：参考外键约束文档（链接）。
题目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 官方文档链接，建议深入阅读：

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

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