LumantoBlog

Appearance

MySQL 第三章：数据库与表 DDL ​

本章目标：掌握数据库和数据表的定义语言（DDL），能独立完成建库、建表、改表、删表以及约束设计。

1. DDL 是什么 ​

DDL（Data Definition Language）用于定义和管理数据库结构，不是改数据内容。

  • 操作对象：数据库、表、字段、约束、索引
  • 典型命令：CREATE、ALTER、DROP、TRUNCATE
  • 核心特点：改的是“结构”，会影响整个业务模型
graph LR
    A[需求分析] --> B[设计库和表]
    B --> C[编写DDL]
    C --> D[执行建库建表]
    D --> E[应用接入]
    E --> F[结构迭代 ALTER]

2. 数据库级操作 ​

2.1 创建数据库 ​

sql
CREATE DATABASE IF NOT EXISTS blog_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

2.2 查看数据库 ​

sql
SHOW DATABASES;

2.3 切换数据库 ​

sql
USE blog_db;

2.4 删除数据库 ​

sql
DROP DATABASE IF EXISTS blog_db;

生产环境慎用 DROP DATABASE，建议先做备份。

3. 数据表级操作 ​

3.1 创建表 ​

sql
CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.2 查看表结构 ​

sql
DESC users;
SHOW CREATE TABLE users;

3.3 删除表 ​

sql
DROP TABLE IF EXISTS users;

3.4 清空表数据 ​

sql
TRUNCATE TABLE users;

TRUNCATE 是重建表，速度快，但会重置自增 ID。

4. ALTER TABLE 改表实战 ​

4.1 新增字段 ​

sql
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) DEFAULT NULL;

4.2 修改字段类型或约束 ​

sql
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30) NOT NULL;

4.3 修改字段名 ​

sql
ALTER TABLE users
CHANGE COLUMN phone mobile_phone VARCHAR(30) NOT NULL;

4.4 删除字段 ​

sql
ALTER TABLE users
DROP COLUMN mobile_phone;

4.5 修改表名 ​

sql
ALTER TABLE users RENAME TO app_users;

5. 约束设计（主键、唯一、外键） ​

5.1 主键与唯一约束 ​

sql
CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE
);

5.2 外键约束 ​

sql
CREATE TABLE posts (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(120) NOT NULL,
  category_id INT NOT NULL,
  CONSTRAINT fk_posts_category
    FOREIGN KEY (category_id)
    REFERENCES categories(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

6. 实战案例：博客系统三表建模 ​

sql
CREATE TABLE authors (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  nickname VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE articles (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  author_id BIGINT NOT NULL,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  published_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_articles_author
    FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE article_tags (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  article_id BIGINT NOT NULL,
  tag_name VARCHAR(30) NOT NULL,
  CONSTRAINT fk_article_tags_article
    FOREIGN KEY (article_id) REFERENCES articles(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

7. 常见坑与规范建议 ​

7.1 常见坑 ​

  • 没有统一字符集，出现乱码
  • 金额字段用 FLOAT/DOUBLE，导致精度问题
  • 外键字段类型与被引用主键不一致
  • 直接在生产库执行高风险 DDL

7.2 建议规范 ​

  1. 表名、字段名统一小写下划线风格
  2. 每张表都建议有 created_at、updated_at
  3. 先在测试环境演练 DDL，再上线
  4. 对关键 DDL 做回滚预案和备份

8. 本章小结 ​

  • DDL 决定数据库“长什么样”，是所有 SQL 能力的基础。
  • 建库建表要重点关注：字段类型、约束、字符集、可维护性。
  • ALTER TABLE 是日常高频操作，建议先小步演练再上线。

9. 练习题 ​

  1. 写一条 SQL：创建 shop_db，字符集为 utf8mb4。
  2. 设计一个 products 表，至少包含主键、名称、价格、库存、创建时间。
  3. 给 products 表新增 status 字段，默认值为 1。
  4. 设计 orders 与 products 的外键关系，并写出建表 SQL。