第二章数据库与表的基本操作（DDL）

DDL（Data Definition Language）用来定义数据的"容器"——数据库、表、列。这一章你会学到如何创建和管理它们。

2.1 数据库管理

创建数据库

sql

CREATE DATABASE school;

就这么简单，一行命令就创建了一个名为 school 的空数据库。

你也可以在创建时指定一些选项：

sql

CREATE DATABASE school
    OWNER = postgres          -- 指定所有者
    ENCODING = 'UTF8'         -- 字符编码（推荐始终使用 UTF8）
    LC_COLLATE = 'en_US.UTF-8' -- 排序规则
    TEMPLATE = template0;     -- 模板数据库

大多数情况下，直接 CREATE DATABASE school; 就够了，默认配置已经很合理。

查看与切换数据库

sql

-- psql 中查看所有数据库
\l

-- 切换到 school 数据库
\c school

\l 的输出类似：

    Name    |  Owner   | Encoding |   Collate   |    Ctype    |
------------+----------+----------+-------------+-------------+
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

template0 和 template1 是系统模板数据库，不要动它们。

删除数据库

sql

DROP DATABASE school;

注意：删除数据库是不可恢复的操作，所有数据一起消失。生产环境请务必先备份。

如果数据库可能不存在，加上 IF EXISTS 避免报错：

sql

DROP DATABASE IF EXISTS school;

2.2 模式（Schema）

在第一章我们提到，数据库内部还有一层叫模式（Schema）。它的作用是在同一个数据库内对表进行逻辑分组。

数据库 school
 ├── public（默认模式，不指定模式时表都建在这里）
 │    ├── students
 │    └── courses
 └── archive（归档数据）
      └── old_students

sql

-- 创建模式
CREATE SCHEMA archive;

-- 在指定模式下建表
CREATE TABLE archive.old_students (
    id   INTEGER,
    name TEXT
);

-- 查询时指定模式
SELECT * FROM archive.old_students;

-- 查看当前数据库所有模式
\dn

入门阶段，你的所有表默认都在 public 模式下，暂时不需要关心 Schema。知道有这个概念就行。

2.3 数据类型详解

选对数据类型是建好表的基础。PostgreSQL 的数据类型非常丰富，这里只介绍最常用的。

数字类型

类型	存储大小	范围	适用场景
`SMALLINT`	2 字节	-32768 ~ 32767	状态码、小范围数字
`INTEGER`	4 字节	-21亿 ~ 21亿	最常用的整数类型
`BIGINT`	8 字节	极大范围	ID 自增可能超过 21 亿时
`NUMERIC(p,s)`	可变	任意精度	金额等需要精确计算的场景
`REAL`	4 字节	6 位小数精度	科学计算（允许微小误差）
`DOUBLE PRECISION`	8 字节	15 位小数精度	科学计算（允许微小误差）

sql

-- NUMERIC 示例：总共 10 位，小数点后 2 位
price NUMERIC(10, 2)   -- 可以存 99999999.99

经验法则：整数用 INTEGER，钱用 NUMERIC，其他小数用 REAL 或 DOUBLE PRECISION。

自增主键

类型	说明
`SERIAL`	自增整数（相当于 `INTEGER` + 自动序列）
`BIGSERIAL`	自增大整数
`GENERATED ALWAYS AS IDENTITY`	SQL 标准写法（PostgreSQL 10+，更推荐）

sql

-- 传统写法
id SERIAL PRIMARY KEY

-- 标准写法（推荐）
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

两种写法效果相同：每插入一行，id 自动递增 1、2、3...

文本类型

类型	说明
`VARCHAR(n)`	最多 n 个字符的变长字符串
`CHAR(n)`	固定 n 个字符，不足补空格（很少用）
`TEXT`	不限长度的变长字符串

sql

name VARCHAR(50)    -- 最多 50 个字符
bio  TEXT           -- 不限长度

PostgreSQL 小知识：在 PostgreSQL 中，VARCHAR(n) 和 TEXT 性能几乎没有差别。如果你不确定长度限制，直接用 TEXT 也完全可以。

日期和时间类型

类型	示例值	说明
`DATE`	`2025-09-01`	只有日期
`TIME`	`14:30:00`	只有时间
`TIMESTAMP`	`2025-09-01 14:30:00`	日期 + 时间
`TIMESTAMPTZ`	`2025-09-01 14:30:00+08`	日期 + 时间 + 时区（推荐）
`INTERVAL`	`3 days 2 hours`	时间间隔

sql

-- 获取当前时间
SELECT NOW();                              -- 2025-09-01 14:30:00.123456+08
SELECT CURRENT_DATE;                       -- 2025-09-01
SELECT NOW() + INTERVAL '7 days';          -- 7 天后
SELECT NOW() - INTERVAL '1 month';         -- 1 个月前

建议：存储时间时优先使用 TIMESTAMPTZ（带时区），避免时区换算的麻烦。

布尔类型

sql

is_active BOOLEAN DEFAULT true

PostgreSQL 中布尔值的合法写法很多，但推荐统一使用 true / false：

表示 true	表示 false
`TRUE`, `'t'`, `'yes'`, `'1'`	`FALSE`, `'f'`, `'no'`, `'0'`

PostgreSQL 特色类型（了解）

sql

-- JSON / JSONB：存储半结构化数据
config JSONB DEFAULT '{}'

-- 数组：原生支持
tags TEXT[] DEFAULT '{}'

-- UUID：全局唯一标识符
id UUID DEFAULT gen_random_uuid()

这些类型会在第七章展开介绍。

2.4 创建表

基本语法

sql

CREATE TABLE students (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    age         INTEGER CHECK (age BETWEEN 1 AND 150),
    gender      CHAR(1) CHECK (gender IN ('M', 'F')),
    is_active   BOOLEAN DEFAULT true,
    enrolled_at TIMESTAMPTZ DEFAULT NOW()
);

让我们逐行解读：

列	类型	约束 / 默认值	含义
`id`	`INTEGER`	`GENERATED ALWAYS AS IDENTITY PRIMARY KEY`	自增主键
`name`	`VARCHAR(50)`	`NOT NULL`	姓名，不可为空
`email`	`VARCHAR(100)`	`UNIQUE`	邮箱，不可重复
`age`	`INTEGER`	`CHECK (age BETWEEN 1 AND 150)`	年龄，限制在 1~150
`gender`	`CHAR(1)`	`CHECK (gender IN ('M', 'F'))`	性别
`is_active`	`BOOLEAN`	`DEFAULT true`	是否在读，默认 true
`enrolled_at`	`TIMESTAMPTZ`	`DEFAULT NOW()`	入学时间，默认当前时间

查看表结构

\d students

输出：

                          Table "public.students"
   Column    |           Type           | Collation | Nullable |   Default
-------------+--------------------------+-----------+----------+-------------
 id          | integer                  |           | not null | generated always as identity
 name        | character varying(50)    |           | not null |
 email       | character varying(100)   |           |          |
 age         | integer                  |           |          |
 gender      | character(1)             |           |          |
 is_active   | boolean                  |           |          | true
 enrolled_at | timestamp with time zone |           |          | now()

2.5 约束详解

约束（Constraints）是数据库自动帮你检查数据合法性的规则，是数据质量的第一道防线。

PRIMARY KEY — 主键

每张表应该有一个主键，用来唯一标识每一行。主键自动具有 NOT NULL 和 UNIQUE 的效果。

sql

id INTEGER PRIMARY KEY

也支持复合主键（多列联合主键）：

sql

CREATE TABLE course_enrollment (
    student_id INTEGER,
    course_id  INTEGER,
    PRIMARY KEY (student_id, course_id)
);

NOT NULL — 非空

sql

name VARCHAR(50) NOT NULL

如果尝试插入 NULL，会报错：

sql

INSERT INTO students (name) VALUES (NULL);
-- ERROR: null value in column "name" violates not-null constraint

UNIQUE — 唯一

sql

email VARCHAR(100) UNIQUE

注意：UNIQUE 允许有多个 NULL（因为 NULL 不等于 NULL）。

CHECK — 自定义校验

sql

age INTEGER CHECK (age > 0 AND age < 200)

你可以写任意布尔表达式，甚至引用同一行的其他列：

sql

CHECK (end_date > start_date)

DEFAULT — 默认值

sql

is_active BOOLEAN DEFAULT true
created_at TIMESTAMPTZ DEFAULT NOW()

插入数据时如果不指定该列的值，会自动填入默认值。

FOREIGN KEY — 外键

外键用来建立两张表之间的关联关系，确保引用完整性。

sql

CREATE TABLE courses (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  INTEGER REFERENCES students(id),  -- 简写形式
    course_name VARCHAR(100) NOT NULL,
    score       NUMERIC(5,2)
);

这意味着 courses.student_id 的值必须在 students.id 中存在，否则插入会报错。

完整写法可以指定删除行为：

sql

student_id INTEGER,
FOREIGN KEY (student_id) REFERENCES students(id)
    ON DELETE CASCADE      -- 删除学生时，自动删除对应的选课记录
    ON UPDATE CASCADE      -- 学生 id 更新时，自动更新选课记录

常见的 ON DELETE 选项：

选项	行为
`CASCADE`	级联删除关联数据
`SET NULL`	将外键列设为 NULL
`SET DEFAULT`	将外键列设为默认值
`RESTRICT`	拒绝删除（默认）

2.6 修改表结构（ALTER TABLE）

表创建后，难免需要调整。ALTER TABLE 是你的瑞士军刀。

添加列

sql

ALTER TABLE students ADD COLUMN phone VARCHAR(20);

删除列

sql

ALTER TABLE students DROP COLUMN phone;

修改列类型

sql

ALTER TABLE students ALTER COLUMN name TYPE TEXT;

重命名列

sql

ALTER TABLE students RENAME COLUMN name TO full_name;

添加 / 删除约束

sql

-- 添加非空约束
ALTER TABLE students ALTER COLUMN email SET NOT NULL;

-- 移除非空约束
ALTER TABLE students ALTER COLUMN email DROP NOT NULL;

-- 添加唯一约束
ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email);

-- 删除约束（按约束名称）
ALTER TABLE students DROP CONSTRAINT unique_email;

重命名表

sql

ALTER TABLE students RENAME TO learners;

2.7 删除表

sql

-- 删除表（表不存在会报错）
DROP TABLE students;

-- 安全删除（表不存在也不报错）
DROP TABLE IF EXISTS students;

-- 级联删除（同时删除依赖这张表的外键等）
DROP TABLE students CASCADE;

2.8 实战练习

跟着下面的步骤动手操作，把本章内容串起来。

sql

-- 1. 创建数据库并切换
CREATE DATABASE school;
\c school

-- 2. 创建学生表
CREATE TABLE students (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    age         INTEGER CHECK (age BETWEEN 1 AND 150),
    enrolled_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. 创建课程表（外键关联学生表）
CREATE TABLE courses (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id  INTEGER REFERENCES students(id) ON DELETE CASCADE,
    course_name VARCHAR(100) NOT NULL,
    score       NUMERIC(5,2)
);

-- 4. 查看表结构
\dt           -- 列出所有表
\d students   -- 学生表详情
\d courses    -- 课程表详情

-- 5. 修改表：给学生表加一列手机号
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
\d students   -- 确认变更

-- 6. 再删掉手机号列
ALTER TABLE students DROP COLUMN phone;

本章小结

你学到了	关键语句
创建 / 删除数据库	`CREATE DATABASE`, `DROP DATABASE`
数据类型选择	`INTEGER`, `TEXT`, `NUMERIC`, `TIMESTAMPTZ`, `BOOLEAN` 等
创建表	`CREATE TABLE ... (列名类型约束, ...)`
六种约束	`PRIMARY KEY`, `NOT NULL`, `UNIQUE`, `CHECK`, `DEFAULT`, `FOREIGN KEY`
修改表结构	`ALTER TABLE ... ADD/DROP/ALTER COLUMN`
删除表	`DROP TABLE`

下一章：第三章数据增删改（DML）

第二章 数据库与表的基本操作（DDL） ​

2.1 数据库管理 ​

创建数据库 ​

查看与切换数据库 ​

删除数据库 ​

2.2 模式（Schema） ​

2.3 数据类型详解 ​

数字类型 ​

自增主键 ​

文本类型 ​

日期和时间类型 ​

布尔类型 ​

PostgreSQL 特色类型（了解） ​

2.4 创建表 ​

基本语法 ​

查看表结构 ​

2.5 约束详解 ​

PRIMARY KEY — 主键 ​

NOT NULL — 非空 ​

UNIQUE — 唯一 ​

CHECK — 自定义校验 ​

DEFAULT — 默认值 ​

FOREIGN KEY — 外键 ​

2.6 修改表结构（ALTER TABLE） ​

添加列 ​

删除列 ​

修改列类型 ​

重命名列 ​

添加 / 删除约束 ​

重命名表 ​

2.7 删除表 ​

2.8 实战练习 ​

本章小结 ​