目录DDL概述DDL的核心操作DDL与DML的本质区别数据库层面的DDL操作1.创建数据库CREATE DATABASE2.查看数据库3.修改数据库ALTER DATABASE4.删除数据库DROP DATABASE数据类型详解DDL的基础1.数据类型2字符串类型3.日期时间类型4.枚举与集合类型5.Json类型Mysql 8.0增强表层面的DDL操作1.创建表CREATE TABLE2.查看表结构3.复制表结构4.修改表结构ALTER TABLE添加列ADD COLUMN修改列MODIFY/CHANGE删除列DROP COLUMN5.修改表名RENAME修改表的字符集/引擎删除表DROP TABLEDDL概述DDLData Definition language数据定义语言用于定义和管理数据库中的所有对象包括数据库 Database表 Table索引 Index视图 View存储过程 Procedure触发器 Trigger用户 UserDDL的核心操作操作关键字英文全称中文含义CREATECreate创建ALTERAlter修改DROPDrop删除整表/整库TRUNCATETruncate清空删除所有数据保留结构RENAMERename重命名DDL与DML的本质区别对比项DDLDML操作对象数据库结构库表索引等数据本身行记录典型命令CREATEALTERDROPINSERTUPDATEDELETESELECT事务支持Mysql 8.0部分DDL支持事务原子DDL支持事务是否可回滚Mysql 8.0大部分可回滚可回滚执行速度通常较快取决于数据量⚠️ Mysql 8.0 重要特性原子DDLAtomi DDLDDL操作要么完全成功要么完全回滚例如 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.数据类型数据类型存储大小字节有符号范围无符号范围用途TINYINT1-128~1270~255年龄状态码SMALLINT2-32768~327670~65535小范围统计MEDIUMINT3~8388608~83886070~16777215中等范围INT/INTEGER4-21亿~21亿0~42亿主键ID常用BIGINT8-9.22e18~9.22e180~1.84e19大型系统IDFLOAT4约7位小数精度-科学计算DOUBLE8约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字前缀用户名标题描述TINYTEXT255字节可变短文本TEXT65535字节可变文章内容评论MEDIUMTEXT16777215字节约16MB可变较大文本LONGTEXT4294967295字节约4GB可变超大文本BLOB65535字节可变二进制数据图片文件CHAR vs VARCHAR 对比对比项CHARVARCHAR长度定义固定长度最大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.日期时间类型数据类型格式范围存储大小用途DATEYYYY-MM-DD1000-01-01~9999-12-313字节生日入职日期TIMEHH:MM:SS-838:59:59~838:59:593字节时间段时长DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00~9999-12-31 23:59:598字节事件时间创建时间TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01~2038-01-19 03:14:074字节自动更新时间戳YEARYYYY1901~21551字节年份统计DATETIME vs TIMESTAMP 核心区别对比项DATETIMETIMESTAMP时区支持❌不支持存什么就是什么✅支持自动转换时区存储大小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 TABLECREATE 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) )ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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 10 表示不复制数据 CREATE TABLE students_simple AS SELECT id, name, age FROM students WHERE 10;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 ENGINEInnoDB;删除表DROP TABLE-- 删除单个表 DROP TABLE student_bak; -- 安全删除避免报错 DROP TABLE IF EXISTS student_bak; -- 删除多个表 DROP TABLE IF EXISTS temp1, temp2, temp3; -- 删除表并重新创建清空数据并重置自增 TRUNCATE TABLE students; -- 与DROP CREATE等效