返回所有指南
SQL··阅读约需 4 分钟

SQL DDL 速查表:详解 CREATE、ALTER 与 DROP

使用此备忘单掌握 SQL DDL。学习 PostgreSQL、MySQL 和 SQL Server 的 CREATE TABLE、ALTER TABLE、数据类型和约束。

CREATE TABLE、ALTER TABLE 和 DROP TABLE 示例,针对同一个 products 表
在本指南中
  1. 什么是 SQL DDL?
  2. CREATE TABLE
  3. 列约束
  4. 表级约束
  5. ALTER TABLE
  6. CREATE TYPE
  7. DROP TABLE
  8. 快速参考
  9. 参考资料与延伸阅读

除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。

什么是 SQL DDL?

数据定义语言 (DDL) 是用于定义和管理数据库结构的 SQL 子集。与处理数据操作(INSERT、SELECT、UPDATE、DELETE)的 DML(数据操作语言)不同,DDL 专注于架构本身——创建、修改和删除诸如表、类型和约束等数据库对象。

三个核心 DDL 命令是 CREATE, ALTER, 以及 DROP。对于使用关系数据库的任何开发人员来说,掌握这些内容至关重要。

将 PostgreSQL、MySQL、SQL Server 和 SQLite 等不同的 SQL 方言转化为一个 ERD
DDL 语法因数据库而异,但优秀的 SQL 转 ERD 工具可以将这些语句转换为相同的有用模型:表、键和关系。

CREATE TABLE

最基本的 DDL 语句。它定义了一个新表及其列、数据类型和约束。

基本语法

CREATE TABLE table_name (
  column_name data_type [constraints],
  ...
  [table_constraints]
);

完整示例

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email TEXT UNIQUE NOT NULL,
  department_id INTEGER REFERENCES departments(id),
  salary NUMERIC(10, 2) DEFAULT 0 CHECK (salary >= 0),
  hire_date DATE DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT true
);

按数据库分类的常见数据类型

分类PostgreSQLMySQLSQL Server
自增SERIALINT AUTO_INCREMENTINT IDENTITY(1,1)
可变文本VARCHAR(n), TEXTVARCHAR(n), TEXTNVARCHAR(n), NVARCHAR(MAX) (JSON 文本); json 在 SQL Server 2025 中
布尔值BOOLEANTINYINT(1)BIT
时间戳TIMESTAMPDATETIMEDATETIME2
UUIDUUIDCHAR(36)UNIQUEIDENTIFIER
JSONJSONBJSONNVARCHAR(MAX) (JSON 文本); json 在 SQL Server 2025 中

列约束

约束对表中的数据强制执行规则:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  -- PRIMARY KEY: uniquely identifies each row
  id SERIAL PRIMARY KEY,

  -- NOT NULL: column cannot contain NULL
  name VARCHAR(200) NOT NULL,

  -- UNIQUE: no duplicate values allowed
  sku VARCHAR(50) UNIQUE,

  -- DEFAULT: automatic value when not provided
  status VARCHAR(20) DEFAULT 'draft',

  -- CHECK: validates data with a condition
  price NUMERIC(10, 2) CHECK (price >= 0),

  -- REFERENCES: creates a foreign key relationship
  category_id INTEGER REFERENCES categories(id)
);

表级约束

对于复合键和多列约束,请在表级别定义它们:

CREATE TABLE students (id INTEGER PRIMARY KEY);
CREATE TABLE courses (id INTEGER PRIMARY KEY);

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrolled_at TIMESTAMP DEFAULT now(),
  grade CHAR(2),

  PRIMARY KEY (student_id, course_id),

  CONSTRAINT fk_student
    FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_course
    FOREIGN KEY (course_id) REFERENCES courses(id),

  CONSTRAINT chk_grade
    CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

ALTER TABLE

通过添加或更改列和约束来修改现有表的结构。这里的示例使用 PostgreSQL;SQL Server 在 ADD 中省略了 COLUMN,而 SQLite 对许多约束更改需要重建表。

添加外键

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(id);

添加列

ALTER TABLE users
  ADD COLUMN phone VARCHAR(20);

添加唯一约束

ALTER TABLE users
  ADD CONSTRAINT uq_users_email
  UNIQUE (email);

添加检查约束

ALTER TABLE products
  ADD CONSTRAINT chk_price_positive
  CHECK (price > 0);

CREATE TYPE

定义可自定义的复用类型(PostgreSQL 特性,Erd Go 亦支持):

枚举类型

CREATE TYPE order_status AS ENUM (
  'pending', 'processing', 'shipped',
  'delivered', 'cancelled'
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  status order_status DEFAULT 'pending'
);

复合类型

CREATE TYPE address AS (
  street VARCHAR(200),
  city VARCHAR(100),
  state CHAR(2),
  zip VARCHAR(10)
);

CREATE TABLE companies (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  headquarters address
);

DROP TABLE

DROP TABLE 删除表定义及其数据。使用 DELETE 来删除选定的行,同时保留表结构。此 PostgreSQL 示例创建了一个暂存表,然后将其删除:

CREATE TABLE staging_orders (
  id INTEGER PRIMARY KEY
);

DROP TABLE IF EXISTS staging_orders;

IF EXISTS 处理了丢失的表;它不会绕过依赖性检查。PostgreSQL 通常会拒绝删除,因为其他对象依赖于该表。在选择之前,请检查依赖项 CASCADE, 这也可能会删除依赖对象。DDL 事务行为因数据库引擎而异。

快速参考

语句目的示例
DROP TABLE删除表及其数据DROP TABLE IF EXISTS staging_orders;
CREATE TABLE定义新表CREATE TABLE users (...);
CREATE TYPE定义自定义类型CREATE TYPE status AS ENUM (...);
ALTER TABLE ADD添加列/约束ALTER TABLE t ADD COLUMN c INT;
PRIMARY KEY唯一行标识符id SERIAL PRIMARY KEY
FOREIGN KEY引用另一张表REFERENCES users(id)
UNIQUE无重复值email TEXT UNIQUE
NOT NULL需要值name VARCHAR(100) NOT NULL
DEFAULT默认值status TEXT DEFAULT 'active'
CHECK验证数据CHECK (price >= 0)

参考资料与延伸阅读

更多文章