别再碎片化学 MySQL！DDL + 数据类型 + JSON 一篇讲透
目录
DDL概述
DDL的核心操作
DDL与DML的本质区别
数据库层面的DDL操作
1.创建数据库（CREATE DATABASE）
2.查看数据库
3.修改数据库（ALTER DATABASE）
4.删除数据库（DROP DATABASE）
数据类型详解（DDL的基础）
1.数据类型
2字符串类型
3.日期时间类型
4.枚举与集合类型
5.Json类型（Mysql 8.0增强）
表层面的DDL操作
1.创建表（CREATE TABLE）
2.查看表结构
3.复制表结构
4.修改表结构（ALTER TABLE）
添加列（ADD COLUMN）
修改列（MODIFY/CHANGE）
删除列（DROP COLUMN）
5.修改表名（RENAME）
修改表的字符集/引擎
删除表（DROP TABLE）
DDL概述
DDL（Data Definition language，数据定义语言）用于定义和管理数据库中的所有对象，包括：
- 数据库 Database
- 表 Table
- 索引 Index
- 视图 View
- 存储过程 Procedure
- 触发器 Trigger
- 用户 User
DDL的核心操作
| 操作关键字 | 英文全称 | 中文含义 |
| CREATE | Create | 创建 |
| ALTER | Alter | 修改 |
| DROP | Drop | 删除（整表/整库） |
| TRUNCATE | Truncate | 清空（删除所有数据，保留结构） |
| RENAME | Rename | 重命名 |
DDL与DML的本质区别
| 对比项 | DDL | DML |
| 操作对象 | 数据库结构（库，表，索引等） | 数据本身（行记录） |
| 典型命令 | CREATE，ALTER，DROP | INSERT，UPDATE，DELETE，SELECT |
| 事务支持 | Mysql 8.0+部分DDL支持事务（原子DDL） | 支持事务 |
| 是否可回滚 | Mysql 8.0+大部分可回滚 | 可回滚 |
| 执行速度 | 通常较快 | 取决于数据量 |
⚠️ Mysql 8.0 重要特性：原子DDL（Atomi DDL）
- DDL操作要么完全成功，要么完全回滚
- 例如： DROP TABLE t1,t2 如果t2不存在，t1也不会被删除
- 之前的版本中，t1会被删除，t2报错，导致不一致
数据库层面的DDL操作
1.创建数据库（CREATE DATABASE）
完整语法：
CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集] [COLLATE 排序规则];示例：
-- 最简方式 （使用默认字符集 utf8mb4） CREATE DATABASE school; --指定字符集和排序规则（推荐方式） CREATE DATABASE school CHARACTER SET utfmb4 COLLATE utf8mb4_unicode_ci; --避免重复报错（安全创建） CREATE DATABASE IF NOT EXISTS school CHARACTER SET utfmb4 COLLATE utf8mb4_unicode_ci; --查看创建语句 SHOW CREATE DATABASE school;2.查看数据库
--查看所有数据库 SHOW DATABASES; --查看数据库的创建信息 SHOW CREATE DATABASE school; --查看当前所在数据库 SELECT DATABASE(); --切换数据库 USE school;3.修改数据库（ALTER DATABASE）
--修改数据库字符集 ALTER DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; --注意:Mysql 8.0不支持直接命名数据库（需通过其他方式） --错误示例: RENAME DATABASE old_name TO new_name; -- Mysql 8.0不支持4.删除数据库（DROP DATABASE）
--删除数据库（谨慎操作） DROP DATABASE school; --安全删除（避免报错） DROP DATABASE IF EXISTS school; --删除后查看 SHOW DATABASES;⚠️警告：DROP DATABASE 会永久删除所有数据，无法恢复（除非有备份）。生产环境必须谨慎
数据类型详解（DDL的基础）
1.数据类型
| 数据类型 | 存储大小（字节） | 有符号范围 | 无符号范围 | 用途 |
| TINYINT | 1 | -128~127 | 0~255 | 年龄，状态码 |
| SMALLINT | 2 | -32768~32767 | 0~65535 | 小范围统计 |
| MEDIUMINT | 3 | ~8388608~8388607 | 0~16777215 | 中等范围 |
| INT/INTEGER | 4 | -21亿~21亿 | 0~42亿 | 主键ID（常用） |
| BIGINT | 8 | -9.22e18~9.22e18 | 0~1.84e19 | 大型系统ID |
| FLOAT | 4 | 约7位小数精度 | - | 科学计算 |
| DOUBLE | 8 | 约15位小数精度 | - | 高精度科学计算 |
| DECIMAL(M,D) | 可变 | 精确小数 | - | 金额，财务数据 |
选择建议：
-- 年龄用TINYINT UNSIGNED age TINYINT UNSIGNED -- 主键用 INT UNSIGNED 或 BIGINT id INT UNSIGNED AUTO_INCREMENT -- 金额必须用Decimal（避免精度丢失） price DECIMAL(10,2) -- 总位数10，小数2位 --状态码用TINYINT status TINYINT DEFAULT 1 -- 1 = 启用，0 = 禁用2字符串类型
| 数据类型 | 最大长度 | 存储方式 | 用途 |
| CHAR(M) | 0~255字符 | 固定长度 | 身份证号，手机号 |
| VARCHAR(M) | 0~65535字节（约16383字符） | 可变长度+1~2字前缀 | 用户名，标题，描述 |
| TINYTEXT | 255字节 | 可变 | 短文本 |
| TEXT | 65535字节 | 可变 | 文章内容，评论 |
| MEDIUMTEXT | 16777215字节（约16MB） | 可变 | 较大文本 |
| LONGTEXT | 4294967295字节（约4GB） | 可变 | 超大文本 |
| BLOB | 65535字节 | 可变 | 二进制数据（图片，文件） |
CHAR vs VARCHAR 对比：
| 对比项 | CHAR | VARCHAR |
| 长度定义 | 固定长度（最大255） | 可变长度（最大65535字节） |
| 存储空间 | 总是分配定义长度 | 按实际长度 + 额外字节 |
| 性能 | 读取速度快 | 读取速度稍慢 |
| 适用场景 | 长度固定的数据 | 长度变化的数据 |
-- 正确使用示例 phone CHAR(11) NOT NULL --手机号固定11位 id_card CHAR(18) NOT NULL -- 身份证固定18位 username VARCHAR(30) NOT NULL --用户名长度不固定 email VARCHAR(100) NOT NULL -- 邮箱长度变化 content TEXT -- 文章内容较长3.日期时间类型
| 数据类型 | 格式 | 范围 | 存储大小 | 用途 |
| DATE | YYYY-MM-DD | 1000-01-01~9999-12-31 | 3字节 | 生日，入职日期 |
| TIME | HH:MM:SS | -838:59:59~838:59:59 | 3字节 | 时间段，时长 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8字节 | 事件时间，创建时间 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01~2038-01-19 03:14:07 | 4字节 | 自动更新时间戳 |
| YEAR | YYYY | 1901~2155 | 1字节 | 年份统计 |
DATETIME vs TIMESTAMP 核心区别
| 对比项 | DATETIME | TIMESTAMP |
| 时区支持 | ❌不支持（存什么就是什么） | ✅支持（自动转换时区） |
| 存储大小 | 8字节 | 4字节 |
| 范围 | 更大（1000~9999年） | 较小（1970~2038年） |
| 自动更新 | 需手动设置 | 支持 CURRENT_TIMESTAMP |
-- 实际应用示例 birthday DATE NOT NULL, --只需要日期 created_at DATETIME DEFAULT CURRENT_TIMESTAMP, --创建时间 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, --更新时间（自动更新） start_time TIME, --时间段 enroll_year YEAR --入学年份4.枚举与集合类型
-- ENUM(枚举): 只能从列表中选一个值 gender ENUM('男','女','保密') DEFAULT '保密'， level ENUM('初级','中级','高级') DEFAULT '初级', -- SET(集合):可以从列表中选择多个值 hobby SET('篮球','足球','音乐','阅读') DEFAULT '阅读', -- 插入示例 INSERT INTO users (gender,hobbt) VALUES ('男','篮球，音乐');⚠️注意：ENUM和SET 虽然方便，但扩展性差，修改需要ALTER TABLE,建议用外键关键字关联字典替代
5.Json类型（Mysql 8.0增强）
-- 创建包含JSON字段的表 CREATE TABLE orders( id INT PRIMARY KEY AUTO_INCREMENT, order_data JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); --插入JSON数据 INSERT INTO orders(order_data) VALUES{ '{"customer":"张三", "items":[ {"name":"手机","price":2999}, {"name":"耳机","price":199} ], "total":3198}' ); --查询JSON字段 SELECT id, JSON_EXTRACT(order_data,'$.customer') AS customer, JSON_EXTRACT(order_data,'$.total') AS total FROM orders; -- Mysql 8.0简写方式（适用 -> 操作符） SELECT id, order_data ->>'$.customer' AS customer, order_data ->'$.total' AS total FROM orders; -- 条件查询JSON字段 SELECT * FROM orders; WHERE JSON_CONTAINS(orders_data->'$.items[*].name','"手机"');表层面的DDL操作
1.创建表（CREATE TABLE）
CREATE TABLE [IF NOT EXISTS] 表名( 列名1 数据类型 [约束] [默认值] [注释], 列名2 数据类型 [约束] [默认值] [注释], ... [表级约束], [索引定义] ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci [注释];实战示例：创建完整的学生表
CREATE TABLE IF NOT EXISTS students( --主键列 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID', --基本信息 student_no CHAR(10) NOT NULL UNIQUE COMMENT '学号(固定10位)', name VARCHAR(50) NOT NULL COMMENT '姓名', gender ENUM('男','女','保密') DEFAULT '保密' COMMENT '性别', age TINYINT UNSIGNED COMMENT '年龄', birthday DATE COMMENT '出生日期', --联系方式 phone CHAR(11) COMMENT '手机号', email VARCHAR(100) UNIQUE COMMENT '邮箱', --地址信息 province VARCHAR(30) COMMENT '省份', city VARCHAR(30) COMMENT '城市', address VARCHAR(200) COMMENT '详细地址', --状态与时间 status TINYINT DEFAULT 1 COMMENT '状态:1-在读 2-休学 3-毕业 0-退学', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', --索引定义 INDEX idx_name(name), INDEX idx_age(age), INDEX idx_status(status) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='学生信息表';2.查看表结构
-- 查看所有表 SHOW TABLES -- 查看表结构（三种方式） DESC students; --简单结构 DESCRIBE students; --万完整写法 SHOW COLUMNS FROM students; --详细信息3.复制表结构
-- 方式1:复制表结构（不包含数据） CREATE TABLE student_bak LIKE students; -- 方式2:复制表结构 + 数据 CREATE TABLE students_copy AS SELECT * FROM students; -- 方式3:仅复制部分字段和数据结构 WHERE 1=0 表示不复制数据 CREATE TABLE students_simple AS SELECT id, name, age FROM students WHERE 1=0;4.修改表结构（ALTER TABLE）
添加列（ADD COLUMN）
-- 在末尾添加列 ALTER TABLE students ADD COLUMN wechat VARCHAR(30) COMMENT '微信号'; -- 在指定位置添加列 ALTER TABLE students ADD COLUMN nickname VARCHAR(50) AFTER name; ALTER TABLE students ADD COLUMN class_id INT FIRST; -- 添加到最前面 -- 一次性添加多列 ALTER TABLE students ADD COLUMN height DECIMAL(5,2) COMMENT '身高（cm）', ADD COLUMN weight DECIMAL(5,2) COMMENT '体重（kg）' ;修改列（MODIFY/CHANGE）
-- MODIFY 修改列的类型 默认值 注释（不修改列名） ALTER TABLE students MODIFY age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄'; -- CHANGE 修改列名 类型 默认值 注释（可以改名） ALTER TABLE students CHANGE gender sex ENUM('男','女','保密') DEFAULT '保密'; -- 修改列的位置 ALTER TABLE stduents MODIFY email VARCHAR(100) AFTER phone;删除列（DROP COLUMN）
-- 删除单个列 ALTER TABLE students DROP COLUMN wechat; -- 删除多个列 ALTER TABLE students DROP COLUMN height, DROP COLUMN weight;5.修改表名（RENAME）
-- 重命名表 ALTER TABLE students RENAME TO students_info; -- 或 RENAME TABLE student_info TO students; -- 重命名多个表（批量） RENAME TABLE old_table1 TO new_table1, old_table2 TO new_total2;修改表的字符集/引擎
-- 修改字符集和排序规则 ALTER TABLE students CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 只修改默认字符集（不改已有数据） ALTER TABLE students DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 修改存储引擎 ALTER TABLE students ENGINE=InnoDB;删除表（DROP TABLE）
-- 删除单个表 DROP TABLE student_bak; -- 安全删除（避免报错） DROP TABLE IF EXISTS student_bak; -- 删除多个表 DROP TABLE IF EXISTS temp1, temp2, temp3; -- 删除表并重新创建（清空数据并重置自增） TRUNCATE TABLE students; -- 与DROP + CREATE等效