SQL详细语法教程（一）--数据定义语言（DDL）

以下对 MySQL 中 DDL（数据定义语言）核心语法 进行 超详细拆解，从基础逻辑到实际场景、细节注意事项全涵盖，帮你彻底吃透数据库 / 表结构操作 👇

一、基础 - SQL-DDL - 数据库操作

核心目标：管理 “数据库” 这个大容器，决定要操作哪些库、怎么创建 / 删除库。

1. 创建数据库（CREATE DATABASE）

CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集] [COLLATE 排序规则];

• IF NOT EXISTS：
  • 作用：避免 “数据库已存在” 时报 ERROR 1007 (HY000): Can't create database 'mydb'; database exists 错误。
  • 场景：脚本初始化环境时，不确定库是否存在，加它更安全。
• CHARACTER SET：
  • 常见值：utf8（老旧，仅支持 Basic Multilingual Plane 字符，缺 emoji 等）、utf8mb4（完整 Unicode，必选！存 emoji、特殊符号）、gbk（国内项目历史场景）。
  • 影响：数据库里所有表、字段默认继承该字符集，若表 / 字段没单独指定，就用库的设置。
• COLLATE：
  • 作用：决定字符串比较规则（如大小写是否敏感、排序规则）。
  • 搭配示例：utf8mb4_general_ci（不区分大小写，排序快）、utf8mb4_unicode_ci（更符合 Unicode 标准，排序稍慢）、utf8mb4_bin（区分大小写，按二进制值比较）。

完整示例（创建支持 emoji 的博客数据库）：

CREATE DATABASE IF NOT EXISTS blog_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 删除数据库（DROP DATABASE）

DROP DATABASE [IF EXISTS] 数据库名;

• 危险点：删库会把库里所有表、数据 永久删除，无法恢复！生产环境必须谨慎，最好先备份。
• IF EXISTS：避免 “删不存在的库” 报错，脚本化操作常用。

示例（清理测试库）：

DROP DATABASE IF EXISTS test_db;

3. 切换数据库（USE）

操作表前必须指定 “当前在哪 个库”，否则 SQL 不知道表属于哪个库：

USE 数据库名;

示例（进入博客库操作表）：

USE blog_db;
-- 之后执行 CREATE TABLE、SELECT 等，默认操作 blog_db 里的表

4. 查看数据库（SHOW DATABASES）

• 查看服务器上 所有数据库（需对应权限，普通用户可能看不到系统库）：

  SHOW DATABASES;
  

• 想精准筛选？结合 LIKE（MySQL 特有的模糊匹配）：

  SHOW DATABASES LIKE 'blog%'; -- 找以 blog 开头的库
  

二、基础 - SQL-DDL - 表操作 - 创建 & 查询

核心目标：设计表结构（字段、类型、约束），并查看表的元数据（结构、创建语句等）。

1. 创建表（CREATE TABLE）

语法骨架（超详细版）：

CREATE TABLE [IF NOT EXISTS] 表名 (字段1 数据类型 [字段约束] [COMMENT '字段说明'],字段2 数据类型 [字段约束] [COMMENT '字段说明'],...[CONSTRAINT 约束名] 表级约束（如 PRIMARY KEY、FOREIGN KEY）,[COMMENT '表说明']
) 
[ENGINE=存储引擎] 
[CHARACTER SET 字符集] 
[COLLATE 排序规则] 
[ROW_FORMAT=行格式];

逐部分拆解：

• IF NOT EXISTS：避免重复建表报错，脚本初始化必备。
• 字段定义：
  • 数据类型：前面 “数据类型” 部分会详细讲，比如 INT（整数）、VARCHAR（字符串）等。
  • 字段约束：
    • NOT NULL：字段值 必须填，不能为 NULL（比如用户手机号、用户名）。
    • UNIQUE：字段值 全局唯一（比如用户邮箱，全表不能重复）。
    • DEFAULT：默认值（比如 create_time 设为当前时间 DEFAULT CURRENT_TIMESTAMP）。
    • COMMENT：给字段加说明（方便维护，比如 COMMENT '用户真实姓名'）。
• 表级约束：
  • 主键（PRIMARY KEY）：
    • 作用：唯一标识一行数据，不允许重复、不允许 NULL，常用 INT 配合 AUTO_INCREMENT（自增）。
    • 写法：可以字段级（id INT PRIMARY KEY），也可表级（适合多字段联合主键）：

      sql

      CONSTRAINT pk_user PRIMARY KEY (id, username); -- 多字段主键（示例，实际少用）
      

  • 外键（FOREIGN KEY）：
    • 作用：关联另一张表的主键，实现 “父子表” 关系（比如订单表 order 关联用户表 user 的 id）。
    • 写法（表级约束）：
      • ON DELETE CASCADE：父表（user）删数据，子表（order）关联数据 自动级联删除。
      • ON UPDATE CASCADE：父表主键更新，子表外键 自动级联更新（需谨慎，可能影响性能）。
  • 唯一约束（UNIQUE）：
    • 表级写法（适合多字段联合唯一，比如 “用户名 + 邮箱” 组合唯一）：

      sql

      CONSTRAINT uk_user UNIQUE (username, email);
      

• 存储引擎（ENGINE）：
  • InnoDB：MySQL 5.5+ 后默认，支持 事务（BEGIN/COMMIT/ROLLBACK）、外键、行级锁（高并发写入友好），适合业务表（订单、用户）。
  • MyISAM：不支持事务、外键，但 查询性能高、支持 全文索引，适合日志表、纯查询表（比如新闻文章表）。
  • MEMORY：数据存内存，速度极快但重启数据库会丢失，适合临时表、缓存数据。
• ROW_FORMAT：
  • 控制行存储方式，InnoDB 常用 DYNAMIC（变长字段更高效）、COMPRESSED（压缩存储，节省空间但稍影响写入）。

完整示例（创建带外键的 “用户 - 文章” 表）：

USE blog_db; -- 先切到 blog_db 库CREATE TABLE IF NOT EXISTS user (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，自增',username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一',email VARCHAR(100) NOT NULL UNIQUE COMMENT '用户邮箱',create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',COMMENT '用户信息表'
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE TABLE IF NOT EXISTS article (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文章ID，自增',title VARCHAR(200) NOT NULL COMMENT '文章标题',content TEXT COMMENT '文章内容',author_id INT NOT NULL COMMENT '关联的用户ID',create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',COMMENT '文章表',-- 表级外键约束CONSTRAINT fk_article_user FOREIGN KEY (author_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 查询表信息

• 查看库内所有表（SHOW TABLES）：

  USE blog_db;
  SHOW TABLES; -- 列出 blog_db 里的所有表（user、article 等）
  

• 查看表结构（DESC / SHOW COLUMNS）：

  DESC 表名; 
  -- 或等价写法 
  SHOW COLUMNS FROM 表名;
  

  输出解析（以 user 表为例）：

  Field	Type	Null	Key	Default	Extra
  id	int	NO	PRI	NULL	auto_increment
  username	varchar(50)	NO	UNI	NULL
  email	varchar(100)	NO	UNI	NULL
  create_time	datetime	YES		CURRENT_TIMESTAMP

  • Field：字段名
  • Type：数据类型
  • Null：是否允许 NULL 值
  • Key：索引类型（PRI 主键、UNI 唯一键、MUL 普通索引）
  • Default：默认值
  • Extra：额外属性（如 auto_increment 自增）

• 查看建表语句（SHOW CREATE TABLE）：

  SHOW CREATE TABLE 表名;
  

  作用：

  • 看完整的 CREATE TABLE 语句（包括引擎、字符集、约束细节），方便复制、迁移表结构。
  • 排查问题（比如引擎是不是 InnoDB、字符集是否正确）。

三、基础 - SQL-DDL - 数据类型及案例

核心目标：选对数据类型，既节省存储空间，又避免业务 bug（比如用 VARCHAR 存手机号，结果长度不够）。

MySQL 数据类型分 数值、字符串、日期时间、特殊类型 四大类，逐个拆解：

1. 数值类型

重点注意：

• DECIMAL vs FLOAT/DOUBLE：
  • 金融、电商场景（金额计算）必须用 DECIMAL，因为 FLOAT/DOUBLE 存的是 近似值（比如 0.1 用二进制存会有精度丢失，累加可能出错）。
  • FLOAT/DOUBLE 适合科学计算、对精度要求不高的场景（比如网站访问量统计）。

案例（订单表金额字段）：

CREATE TABLE order (id INT PRIMARY KEY AUTO_INCREMENT,amount DECIMAL(10,2) NOT NULL COMMENT '订单金额，精准到分',quantity INT NOT NULL COMMENT '商品数量',total DOUBLE COMMENT '临时统计值（非精准，仅展示）'
);

2. 字符串类型

关键对比 CHAR vs VARCHAR：

• CHAR：查询快（因为长度固定，MySQL 可直接定位），但费空间（比如存 'abc'，CHAR(10) 会存成 'abc ' 补 7 个空格）。适合 短、固定长度 的值（手机号、性别）。
• VARCHAR：省空间（存 'abc' 就是 3 个字符 + 1 个长度前缀），但查询稍慢（需要解析长度）。适合 变长、长度不固定 的值（用户名、文章标题）。

案例（用户表字段选型）：

CREATE TABLE user (id INT PRIMARY KEY AUTO_INCREMENT,mobile CHAR(11) NOT NULL COMMENT '手机号，固定11位',username VARCHAR(50) NOT NULL COMMENT '用户名，变长',avatar VARCHAR(255) COMMENT '头像OSS路径',bio TEXT COMMENT '个人简介（可能很长）'
);

3. 日期时间类型

关键对比 DATETIME vs TIMESTAMP：

• DATETIME：
  • 范围大（到 9999 年），不关联时区，存啥显示啥。
  • 适合 业务时间不涉及时区 的场景（比如国内电商，所有时间按北京时间存）。
• TIMESTAMP：
  • 范围小（到 2038 年，需注意！），但会 自动转换时区（存的时候转 UTC，查的时候转当前会话时区）。
  • 适合 多时区业务（比如海外 APP，用户在纽约、北京看时间自动适配）。
  • 常用 DEFAULT CURRENT_TIMESTAMP 记录 “创建时间”，ON UPDATE CURRENT_TIMESTAMP 记录 “更新时间”：

**案例**（时间字段综合应用）：
```sql
CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
publish_date DATE NOT NULL COMMENT '发布日期（仅年月日）',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间（精确到秒，北京时间）',
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间（随时区变化）',
duration TIME COMMENT '阅读时长（如 00:10:30 表示10分30秒）'
);

四、基础 - SQL-DDL - 表操作 - 修改 & 删除（深度版）

核心目标：灵活调整已存在的表结构，适应业务变化（如新增字段、修改类型），同时掌握安全删除表的操作。

1. 修改表结构（ALTER TABLE 全场景）

ALTER TABLE 是 DDL 中最复杂的语法之一，支持 增删改字段、约束、引擎、字符集 等，完整语法框架：

ALTER TABLE 表名[ADD 操作][MODIFY 操作][CHANGE 操作][DROP 操作][RENAME 操作][其他操作（如改引擎、字符集）];

常用操作详解：

• 添加字段（ADD）

  • 基础语法：ALTER TABLE 表名 ADD 字段名 数据类型 [约束] [FIRST | AFTER 已有字段名];
  • 可选位置：
    • FIRST：把新字段放表的第一列。
    • AFTER 字段名：把新字段放指定字段后面（默认放最后）。
  • 示例：

    -- 给 user 表加 age 字段，放 username 后面
    ALTER TABLE user ADD age TINYINT UNSIGNED AFTER username;-- 给 article 表加 is_top 字段（是否置顶），放第一列
    ALTER TABLE article ADD is_top TINYINT DEFAULT 0 FIRST;
    

• 修改字段（MODIFY vs CHANGE）

  • MODIFY：只改字段类型、约束、位置，不改字段名。

    -- 把 user 表的 age 字段从 TINYINT 改为 SMALLINT，且允许 NULL
    ALTER TABLE user MODIFY age SMALLINT UNSIGNED;-- 同时调整位置（放 email 后面）
    ALTER TABLE user MODIFY age SMALLINT UNSIGNED AFTER email;
    

  • CHANGE：既可以改字段名，也能改类型、约束、位置（功能更强）。

    -- 把 user 表的 age 字段改名为 user_age，类型改为 INT
    ALTER TABLE user CHANGE age user_age INT UNSIGNED;
    

  • 注意：修改字段可能导致数据丢失！例如：
    • 把 VARCHAR(100) 改成 VARCHAR(20)，超长的字符串会被截断。
    • 把 INT 改成 TINYINT，超出范围的数值会变成 0 或最大值。

• 删除字段（DROP）

  • 语法：ALTER TABLE 表名 DROP 字段名;
  • 警告：删字段会 永久删除该字段的所有数据，且无法恢复！操作前务必确认。
  • 示例：

    -- 删除 user 表的 user_age 字段
    ALTER TABLE user DROP user_age;
    

• 添加 / 删除约束

  • 添加主键：

    -- 给现有表加主键（表必须没有主键，且字段非空、唯一）
    ALTER TABLE user ADD PRIMARY KEY (id);
    

  • 删除主键：

    ALTER TABLE user DROP PRIMARY KEY;
    

  • 添加唯一约束：

    -- 给 email 加唯一约束（确保不重复）
    ALTER TABLE user ADD UNIQUE (email);
    -- 加命名的唯一约束（方便后续删除）
    ALTER TABLE user ADD CONSTRAINT uk_user_email UNIQUE (email);
    

  • 删除唯一约束（需知道约束名，可通过 SHOW CREATE TABLE 查看）：

    ALTER TABLE user DROP INDEX uk_user_email;
    

  • 添加外键：

    -- 给 article 表的 author_id 加外键，关联 user 表的 id
    ALTER TABLE article ADD CONSTRAINT fk_article_author FOREIGN KEY (author_id) REFERENCES user(id) ON DELETE CASCADE;
    

  • 删除外键（需知道外键名）：

    ALTER TABLE article DROP FOREIGN KEY fk_article_author;
    

• 修改表的其他属性

  • 改存储引擎：

    -- 把 article 表从 MyISAM 改成 InnoDB（支持事务）
    ALTER TABLE article ENGINE = InnoDB;
    

  • 改字符集和排序规则：

    -- 把 user 表字符集改成 utf8mb4
    ALTER TABLE user CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    

  • 重命名表：

    -- 方法1
    ALTER TABLE old_table RENAME TO new_table;
    -- 方法2（更简洁）
    RENAME TABLE old_table TO new_table;
    

2. 删除表（DROP TABLE）

• 基础语法：DROP TABLE [IF EXISTS] 表名1, 表名2, ...;（可一次删多张表）
• 注意事项：
  • 删表会删除表的 所有结构和数据，且无法恢复（除非有备份）。
  • 若表被其他表作为外键引用，直接删会报错，需先删外键约束或先删子表。
  • 生产环境删表前，必须通过审批，并确认数据已备份！
• 示例：

  -- 删单表
  DROP TABLE IF EXISTS test_table;-- 删多表
  DROP TABLE IF EXISTS temp1, temp2, temp3;
  

五、DDL 操作的注意事项（避坑指南）

1. 事务与 DDL：
   DDL 操作（如 CREATE TABLE、ALTER TABLE）会 自动提交事务，且无法回滚！执行前务必确认。

2. 性能影响：

   • 对大表执行 ALTER TABLE（如加字段、改类型）会 锁表，期间无法读写，可能导致业务中断。
   • 优化方案：用 pt-online-schema-change（Percona 工具）等工具，在线修改表结构不锁表。

3. 数据安全：

   • 任何 DDL 操作（尤其是 DROP、ALTER）前，必须备份数据！
   • 测试环境验证通过后，再在生产环境执行。

4. 命名规范：

   • 数据库、表、字段名用小写字母，下划线分隔（如 user_info，避免大小写敏感问题）。
   • 加 COMMENT 说明，方便后期维护（比如 COMMENT '用户注册时间'）。