DDL：创建与管理表结构

2.1 什么是 DDL

DDL（Data Definition Language，数据定义语言） 是用于定义数据库结构的 SQL 语句集合。DDL 操作的对象是"结构"本身，而不是数据内容。

DDL 语句执行后通常会自动提交（Auto-Commit），不需要手动 COMMIT，也无法 ROLLBACK（MySQL 中如此；PostgreSQL 中 DDL 可以在事务中执行并回滚，这是其优势之一）。

语句	作用
CREATE	创建数据库、表、索引、视图、存储过程等
ALTER	修改已有结构（加列、改类型、加约束等）
DROP	彻底删除数据库对象（包括数据，不可恢复）
TRUNCATE	清空表中所有数据，但保留表结构

2.2 数据库操作

SQL-- 创建数据库（MySQL）
CREATE DATABASE blog_db
  CHARACTER SET utf8mb4        -- 推荐 utf8mb4，完整支持 emoji
  COLLATE utf8mb4_unicode_ci;   -- 排序规则（ci = 不区分大小写）

-- 创建数据库（PostgreSQL）
CREATE DATABASE blog_db
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  TEMPLATE template0;

-- 选择数据库（MySQL）
USE blog_db;

-- 查看当前使用的数据库
SELECT DATABASE();      -- MySQL
SELECT current_database(); -- PostgreSQL

-- 删除数据库（危险！不可恢复）
DROP DATABASE blog_db;

-- 删除前先检查是否存在（避免报错）
DROP DATABASE IF EXISTS blog_db;

2.3 CREATE TABLE 语法详解

SQLCREATE TABLE 表名 (
  -- 列定义格式：列名  数据类型  [约束...]
  列名  数据类型  NOT NULL,
  列名  数据类型  DEFAULT 默认值,
  列名  数据类型  UNIQUE,

  -- 表级约束（多列约束写在这里）
  PRIMARY KEY (列名),
  FOREIGN KEY (列名) REFERENCES 其他表(列名)
);

2.4 数据类型详解

整数类型

类型	字节	范围（有符号）	范围（无符号）	适用场景
TINYINT	1	-128 ~ 127	0 ~ 255	状态码、布尔值替代
SMALLINT	2	-32,768 ~ 32,767	0 ~ 65,535	年份、小范围编号
INT / INTEGER	4	-21亿 ~ 21亿	0 ~ 42亿	最常用的 ID、计数器
BIGINT	8	-9.2×10¹⁸ ~ 9.2×10¹⁸	极大	雪花 ID、高并发系统 ID

浮点与精确数值类型

类型	精度	说明
FLOAT	~7 位有效数字	单精度浮点，存在精度误差，不适合金融计算
DOUBLE	~15 位有效数字	双精度浮点，仍有精度问题
DECIMAL(M,D)	精确	M 总位数，D 小数位数。金融计算必须用此类型

字符串类型

类型	存储方式	最大长度	适用场景
CHAR(n)	定长，不足补空格	255 字符	MD5 哈希（固定32位）、固定格式编码
VARCHAR(n)	变长，实际长度+1~2字节	65,535 字节	姓名、标题、邮箱（最常用）
TEXT	变长，独立存储	65,535 字节	文章正文、评论内容
MEDIUMTEXT	变长	16 MB	较大文本
LONGTEXT	变长	4 GB	超大文本
JSON	验证后存储	—	MySQL 5.7+/PostgreSQL 支持，灵活属性

日期与时间类型

类型	格式	范围	时区	说明
DATE	YYYY-MM-DD	1000-9999年	无	生日、纯日期
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	无	纯时间或时间差
DATETIME	YYYY-MM-DD HH:MM:SS	1000-9999年	不转换	存储为写入时的字面值
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-2038年	自动转换UTC	创建/更新时间戳（推荐）

2.5 约束（Constraints）

约束是数据库在数据写入时自动执行的规则，保证数据的完整性和一致性。

PRIMARY KEY — 主键

主键唯一标识一行数据，隐含了 NOT NULL 和 UNIQUE 两个约束。

SQL-- MySQL：自增主键
CREATE TABLE users (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

-- PostgreSQL：SERIAL 或 GENERATED AS IDENTITY（推荐）
CREATE TABLE users (
  id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- UUID 主键（分布式系统常用，避免 ID 泄露)
CREATE TABLE users (
  id    CHAR(36) DEFAULT (UUID()) PRIMARY KEY  -- MySQL 8.0+
);

NOT NULL、UNIQUE、DEFAULT

SQLCREATE TABLE users (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50)  NOT NULL,              -- 不能为空
  email    VARCHAR(255) NOT NULL UNIQUE,       -- 不能为空且唯一
  role     VARCHAR(20)  NOT NULL DEFAULT 'user', -- 默认值 'user'
  is_active TINYINT(1) NOT NULL DEFAULT 1,     -- 布尔，默认激活
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CHECK — 检查约束

SQLCREATE TABLE products (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0), -- 价格不能为负
  stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),   -- 库存不能为负
  status ENUM('draft', 'published', 'archived') DEFAULT 'draft'
);

-- CHECK 约束在 MySQL 8.0.16+ 才真正强制执行

FOREIGN KEY — 外键约束

外键确保引用完整性：子表（orders）中的 user_id 必须在父表（users）中存在对应的 id。

SQLCREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  total      DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- 外键约束
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE    -- 删除用户时，同时删除其所有订单
    ON UPDATE CASCADE    -- 用户 id 修改时，同步更新订单中的 user_id
);

ON DELETE / ON UPDATE 行为	说明
CASCADE	同步删除/更新子表对应行（谨慎使用删除级联）
SET NULL	将子表外键列设为 NULL（外键列需允许 NULL）
RESTRICT	如果子表有关联数据，拒绝删除/更新父表记录（默认）
NO ACTION	与 RESTRICT 相同（标准 SQL 名称）

2.6 ALTER TABLE 修改表结构

SQL-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改列类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- 重命名列（MySQL 8.0+）
ALTER TABLE users RENAME COLUMN phone TO mobile;

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- 重命名表
ALTER TABLE users RENAME TO app_users;

-- PostgreSQL 修改列类型（语法稍有不同）
ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

2.7 DROP TABLE vs TRUNCATE TABLE

操作	删除结构	删除数据	可回滚	重置自增
DROP TABLE	是	是	否	—
TRUNCATE TABLE	否	是（全部）	否（MySQL）/ 是（PostgreSQL）	是
DELETE FROM	否	是（可条件）	是	否

2.8 实战：创建博客系统数据库

综合运用本章所有知识，创建一个包含用户、文章、评论三张表的博客系统：

SQL-- ① 创建数据库
CREATE DATABASE IF NOT EXISTS blog_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE blog_db;

-- ② 用户表
CREATE TABLE users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50)  NOT NULL UNIQUE,
  email      VARCHAR(255) NOT NULL UNIQUE,
  password_hash CHAR(60)  NOT NULL,          -- bcrypt 哈希固定 60 位
  bio        TEXT,
  avatar_url VARCHAR(512),
  role       ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
             ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ③ 文章表
CREATE TABLE posts (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  title      VARCHAR(200) NOT NULL,
  slug       VARCHAR(200) NOT NULL UNIQUE,   -- URL 友好路径，如 hello-world
  content    LONGTEXT NOT NULL,
  status     ENUM('draft', 'published', 'archived')
             NOT NULL DEFAULT 'draft',
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  published_at TIMESTAMP NULL DEFAULT NULL,   -- 草稿时为 NULL
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
             ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE,

  INDEX idx_posts_user_id (user_id),
  INDEX idx_posts_status_published (status, published_at)
) ENGINE=InnoDB;

-- ④ 评论表
CREATE TABLE comments (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id    INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  parent_id  INT UNSIGNED NULL DEFAULT NULL,  -- 回复功能：NULL 表示顶级评论
  content    TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_comments_post
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_parent
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,

  INDEX idx_comments_post_id (post_id),
  INDEX idx_comments_user_id (user_id)
) ENGINE=InnoDB;

表关系图（文字描述）