表基础

表是关系型数据库保存业务数据的基本结构。可以把表理解为一张有明确规则的二维表格：列定义允许保存什么数据，行保存一条条具体记录，约束负责阻止不符合规则的数据进入表中。

本节以通用 SQL 为主，介绍表从创建到删除的基本操作。不同数据库在自增列、修改列和临时表等功能上存在方言差异，实际使用时还应核对目标数据库及其版本的文档。

表由什么组成

下面是一张简化的用户表：

`user_id`	`username`	`email`	`status`	`created_at`
1	alice	alice@example.com	active	2026-09-15 09:00:00
2	bob	bob@example.com	disabled	2026-09-15 10:30:00

其中：

表（table）：同一类业务对象的记录集合，例如用户、商品或订单；
列（column）：记录的某个属性，每列都有名称、数据类型和可选约束；
行（row）：一条完整记录，也常称为元组（tuple）；
字段值（value）：某一行与某一列交叉位置上的数据；
表结构（schema）：列、类型、约束等定义，不包括表中的具体记录。

关系型数据库中的表并不保证行的物理顺序。查询结果需要固定顺序时，必须显式使用 ORDER BY。

数据定义语言

用于定义数据库对象结构的 SQL 通常称为 数据定义语言（Data Definition Language，DDL）。表相关的核心语句包括：

语句	作用
`CREATE TABLE`	创建表
`ALTER TABLE`	修改已有表的结构
`TRUNCATE TABLE`	快速清空表中的全部数据
`DROP TABLE`	删除表及其结构

DDL 可能获得较强的表锁，并且不同数据库对 DDL 事务回滚的支持不同。生产环境执行结构变更前，应先确认锁表影响、回滚方式和备份策略。

创建表

CREATE TABLE 的基本结构如下：

CREATE TABLE table_name (
  column_name data_type column_constraint,
  column_name data_type column_constraint,
  table_constraint
);

例如，创建一张用户表：

CREATE TABLE users (
  user_id BIGINT GENERATED ALWAYS AS IDENTITY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(254) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  birth_date DATE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT pk_users PRIMARY KEY (user_id),
  CONSTRAINT uq_users_username UNIQUE (username),
  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT ck_users_status
    CHECK (status IN ('active', 'disabled'))
);

这条语句同时定义了列名、数据类型、是否允许空值、默认值，以及主键、唯一性和检查约束。GENERATED ... AS IDENTITY 是标准 SQL 风格的自增标识列，但具体语法因数据库而异。

避免重复创建

部分数据库支持：

CREATE TABLE IF NOT EXISTS users (
  user_id BIGINT PRIMARY KEY
);

表已存在时，这种写法通常不会报错，但它不会自动核对或更新已有表结构。数据库迁移中仍应使用有版本记录的迁移脚本，不要依赖它修复结构差异。

选择数据类型

数据类型决定列能保存的数据范围、存储方式和可执行的运算。应根据业务含义选择类型，而不是把所有内容都保存成字符串。

常见类型

数据类别	常见 SQL 类型	适用场景
整数	`SMALLINT`、`INTEGER`、`BIGINT`	数量、序号、整数标识
精确小数	`DECIMAL(p, s)`、`NUMERIC(p, s)`	金额、税率等需要精确计算的数据
近似数值	`REAL`、`FLOAT`、`DOUBLE PRECISION`	测量值、科学计算结果
字符串	`CHAR(n)`、`VARCHAR(n)`、`TEXT`	编码、名称、描述
日期时间	`DATE`、`TIME`、`TIMESTAMP`	日期、时间点
布尔	`BOOLEAN`	是/否状态
二进制	`BINARY`、`VARBINARY`、`BLOB`	文件内容、二进制数据

类型名称、最大长度、时区语义和隐式转换规则并不完全统一。例如，并非所有数据库都原生支持 BOOLEAN 或无长度限制的 TEXT。

类型选择原则

范围足够且不过度：预计可能超过约 21 亿的整数不要使用常见的 32 位 INTEGER。
金额使用精确小数：DECIMAL(12, 2) 表示最多 12 位有效数字，其中 2 位位于小数点后；不要用浮点数保存需要精确对账的金额。
固定长度与可变长度分开：只有确实固定长度的数据才考虑 CHAR(n)，普通文本通常使用 VARCHAR(n) 或数据库支持的文本类型。
日期时间使用专用类型：不要用字符串保存日期时间，否则排序、校验和日期运算都会更困难。
标识符不等于数值：电话号码、邮政编码等即使只含数字，也可能有前导零且不参与算术运算，通常应保存为字符串。
明确时区语义：记录跨时区的时间点时，要确认数据库的带时区类型及应用层转换规则。

空值与默认值

`NULL` 与 `NOT NULL`

NULL 表示值缺失或未知，它不等于空字符串、数字 0 或布尔值 FALSE。

nickname VARCHAR(50),
email VARCHAR(254) NOT NULL

第一列允许 NULL，第二列必须有值。业务上必填的列应显式声明 NOT NULL，让数据库在所有写入入口统一执行规则。

查询空值要使用 IS NULL：

SELECT *
FROM users
WHERE birth_date IS NULL;

`DEFAULT`

默认值在插入语句没有为该列提供值时生效：

status VARCHAR(20) NOT NULL DEFAULT 'active',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

省略列与显式写入 NULL 是两件事：若列允许空值，显式写入 NULL 通常不会改用默认值。

-- status 使用默认值 active
INSERT INTO users (username, email)
VALUES ('carol', 'carol@example.com');

默认值适合表达稳定的数据库规则。会频繁变化、依赖外部服务或依赖复杂业务上下文的值，更适合由应用层计算后写入。

用约束保证数据正确

约束是数据库层的数据规则。相比只在应用代码中校验，约束能覆盖脚本、管理工具和其他服务等所有写入入口。

主键

PRIMARY KEY 唯一标识表中的每一行，主键值必须唯一且不能为 NULL：

CREATE TABLE products (
  product_id BIGINT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL
);

一张表只能有一个主键，但主键可以由多列组成：

CREATE TABLE order_items (
  order_id BIGINT,
  product_id BIGINT,
  quantity INTEGER NOT NULL,
  CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

这表示同一个订单中，同一种商品最多对应一行。

唯一约束

UNIQUE 要求列或列组合不出现重复值：

CONSTRAINT uq_users_email UNIQUE (email)

一张表可以有多个唯一约束。不同数据库对唯一约束中多个 NULL 的处理存在差异；需要严格规则时，应查阅目标数据库文档并编写测试。

检查约束

CHECK 要求写入的数据满足布尔条件：

price DECIMAL(12, 2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
CONSTRAINT ck_products_price CHECK (price >= 0),
CONSTRAINT ck_products_stock CHECK (stock >= 0)

检查约束适合限定单行内的取值范围和列间关系。跨行统计或跨表规则通常不能只靠普通 CHECK 完成。

外键

FOREIGN KEY 要求子表中的值能够在被引用表中找到，用来维护表之间的引用完整性：

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  ordered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users (user_id)
);

删除或修改被引用行时，可以配置相应动作：

FOREIGN KEY (user_id) REFERENCES users (user_id)
  ON UPDATE CASCADE
  ON DELETE RESTRICT

常见动作包括：

动作	含义
`RESTRICT` / `NO ACTION`	存在引用时拒绝操作，细节因数据库而异
`CASCADE`	把更新或删除传播到引用行
`SET NULL`	把引用列设为 `NULL`，因此该列必须允许空值
`SET DEFAULT`	把引用列设为默认值，数据库及约束必须支持

级联删除会扩大一次删除的影响范围，只有在它确实符合数据生命周期时才应使用。

列级与表级写法

只涉及一列的简单约束可以紧跟列定义：

email VARCHAR(254) NOT NULL UNIQUE

涉及多列的约束必须写在表级；大型项目也常统一使用表级命名约束，便于迁移脚本准确地修改或删除它们：

CONSTRAINT uq_members_tenant_email UNIQUE (tenant_id, email)

推荐使用稳定且能表达用途的约束名，例如 pk_users、uq_users_email、fk_orders_user 和 ck_products_price。

修改表结构

ALTER TABLE 用于修改已有表。不同数据库支持的子句差异较大，以下是常见形式。

添加列

ALTER TABLE users
ADD COLUMN last_login_at TIMESTAMP;

给已有数据的表添加 NOT NULL 列时，历史行也必须满足约束。常见迁移过程是：先添加可空列或带默认值的列，分批回填历史数据，确认没有空值后再设置 NOT NULL。

删除列

ALTER TABLE users
DROP COLUMN birth_date;

删除列会丢失该列全部数据，还可能影响索引、约束、视图、查询和应用代码。执行前应检查依赖并准备回滚方案。

修改列

修改数据类型、默认值或空值限制的语法最容易出现方言差异。下面是部分数据库支持的标准风格示意：

ALTER TABLE users
ALTER COLUMN username SET NOT NULL;

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

修改类型还可能因历史数据无法转换而失败。生产迁移前，应先查询不符合新规则的数据，并在测试环境验证迁移耗时和锁影响。

添加和删除约束

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

ALTER TABLE users
DROP CONSTRAINT uq_users_email;

添加约束时，数据库通常会校验已有数据；如果已经存在重复邮箱，新增唯一约束就会失败。

清空表与删除表

`DELETE`

DELETE 是数据操作语句，可以按条件删除行：

DELETE FROM users
WHERE status = 'disabled';

省略 WHERE 会删除全部行，但保留表结构：

DELETE FROM users;

`TRUNCATE TABLE`

TRUNCATE TABLE 用于清空整张表：

TRUNCATE TABLE users;

它通常不能使用 WHERE，并可能比逐行 DELETE 更快。不过，它对事务回滚、触发器、自增计数器和外键引用的处理因数据库而异，不能简单认为它只是更快的 DELETE。

`DROP TABLE`

DROP TABLE 删除表结构及其中的数据：

DROP TABLE users;

部分数据库支持避免目标不存在时报错：

DROP TABLE IF EXISTS users;

如果其他对象依赖该表，数据库可能拒绝删除，或要求使用 CASCADE 一并删除依赖对象。CASCADE 的影响可能远超一张表，使用前必须检查依赖关系。

三种操作的核心区别如下：

操作	可使用 `WHERE`	保留表结构	主要用途
`DELETE`	是	是	删除部分或全部数据
`TRUNCATE`	否	是	快速清空整张表
`DROP`	否	否	删除表本身

自增列的方言差异

不同数据库生成数字主键的常见写法不同：

数据库	常见写法示例
PostgreSQL	`BIGINT GENERATED ALWAYS AS IDENTITY`
MySQL	`BIGINT AUTO_INCREMENT`
SQL Server	`BIGINT IDENTITY(1, 1)`
SQLite	`INTEGER PRIMARY KEY`

即使主键由数据库自动生成，也不代表编号一定连续。事务回滚、并发写入和删除数据都可能留下空号，因此不要让业务逻辑依赖编号连续。

UUID 等非数字主键也很常见，但其生成方式、存储类型和索引性能取决于数据库及使用场景，不能仅因为“全局唯一”就无条件替代数字主键。

一份完整的建表示例

下面用商品表综合前面的知识点：

CREATE TABLE products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY,
  sku VARCHAR(40) NOT NULL,
  product_name VARCHAR(120) NOT NULL,
  price DECIMAL(12, 2) NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'on_sale',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT pk_products PRIMARY KEY (product_id),
  CONSTRAINT uq_products_sku UNIQUE (sku),
  CONSTRAINT ck_products_price CHECK (price >= 0),
  CONSTRAINT ck_products_stock CHECK (stock >= 0),
  CONSTRAINT ck_products_status
    CHECK (status IN ('on_sale', 'off_sale'))
);

设计思路如下：

product_id 只负责稳定标识记录，sku 作为业务编号另设唯一约束；
商品名称、价格、库存和状态是必需数据，因此使用 NOT NULL；
金额使用精确小数，库存使用整数；
默认值减少重复输入，但检查约束仍负责限制合法范围；
每个约束都有明确名称，后续迁移时更容易定位。

updated_at 的默认值只在插入时生效，普通 UPDATE 不会按照通用 SQL 自动刷新它。需要自动维护更新时间时，应根据项目架构选择由应用程序、触发器或数据库专有功能处理。

表设计检查清单

创建表前，可以依次检查：

一行数据代表什么，表是否只描述一类业务对象；
每列的名称和业务含义是否清晰；
数据类型能否覆盖合理范围，金额和时间是否使用正确类型；
必填列是否声明 NOT NULL；
默认值是否代表稳定规则，而不是掩盖缺失输入；
是否有稳定的主键，业务唯一性是否通过 UNIQUE 表达；
数值范围、状态集合和引用关系是否需要 CHECK 或外键；
约束是否有一致、可维护的名称；
是否确认了目标数据库的方言、字符集、排序规则和时区语义；
上线迁移是否评估了历史数据、锁表时间、依赖对象和回滚方案。

表结构是数据质量的第一道防线。好的设计不是堆叠尽可能多的约束，而是把长期稳定、能够由数据库可靠判断的业务规则准确地写进表定义中。

表由什么组成​

数据定义语言​

创建表​

避免重复创建​

选择数据类型​

常见类型​

类型选择原则​

空值与默认值​

NULL 与 NOT NULL​

DEFAULT​

用约束保证数据正确​

主键​

唯一约束​

检查约束​

外键​

列级与表级写法​

修改表结构​

添加列​

删除列​

修改列​

添加和删除约束​

清空表与删除表​

DELETE​

TRUNCATE TABLE​

DROP TABLE​

自增列的方言差异​

一份完整的建表示例​

表设计检查清单​