共计 7731 个字符，预计需要花费 20 分钟才能阅读完成。
自动写代码机器人，免费开通
丸趣 TV 小编给大家分享一下 MySQL 中 DDL 数据定义语句有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
一、DDL 数据定义语句
库的管理
创建：create database [if not exists] 库名;
修改：若需要修改库名, 直接修改文件夹
删除：DROP DATABASE IF EXISTS 库名;
表的管理
创建
create table IF NOT EXISTS 表名 (
列名 列的类型 [长度、约束],
列名 列的类型 [长度、约束],
列名 列的类型[长度、约束],
…
)
修改
alter table 表名 add|drop|modify|change column 列名 [列类型 约束];
删除
drop table 表名;
【库的管理】# 创建库 CREATE DATABASE IF NOT EXISTS books;# 修改库的字符集 ALTER DATABASE books CHARACTER SET gbk;# 删除库 DROP DATABASE IF EXISTS books;【表的管理】# 1. 创建表 CREATE TABLE IF NOT EXISTS book(
id INT,
bName VARCHAR(20),
authorID INT,
publishDate DATETIME);DESC book;CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10));DESC author;# 2. 修改表 # 修改列名 ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的类型或约束 ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# 添加新列 ALTER TABLE author ADD COLUMN annual DOUBLE; # 添加新列作为第一个字段 ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后 ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 删除列 ALTER TABLE author DROP COLUMN annual;# 修改表名 ALTER TABLE author RENAME TO book_author;# 3. 表的删除 DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4. 表的复制 INSERT INTO author VALUES(1, 小樱 , 日本),(2, 悟空 , 中国),(3, 绿巨人 , 美国),(4, 哪吒 , 中国 # 仅复制表的结构 CREATE TABLE author2 LIKE author;# 复制表的结构 + 数据 CREATE TABLE author3 SELECT * FROM author;# 只复制部分数据 CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation= 中国 # 仅复制某些字段 CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;二、数据类型
2.1 整型
整数类型别名字节无符号范围有符号范围 Tinint 微整型 10~255-128~127Smallin 小整型 20~65535-32768~32767Mediumint 中整型 30~1677215-8388608~8388607Int 或 Integer 整型 40~4294967295-2147483648~2147483647Bigint 大整型 80~9223372036854775807*2+1-9223372036854775808~9223372036854775807
【整型】# 1. 默认为有符号, 可以添加 unsigned 设置为无符号 CREATE TABLE IF NOT EXISTS tab_int(
t1 INT, # 有符号
t2 INT UNSIGNED, # 无符号
t3 INT ZEROFILL # 添加 zerofill 后自动变更为无符号整型, 位数不够 0 填充.);DESC tab_int;SELECT * FROM tab_int;# 2. 如果插入的数值超出了整型的范围, 会报 out of range 异常 INSERT INTO tab_int VALUES(2147483648,1);# 3. 如果不设置长度, 会有默认的长度, 位数不够时 0 填充(前提是字段有 ZEROFILL).2.2 小数
浮点数类型字节范围 float(M,D)4-2^128 ~ +2^128double(M,D)8-2^1024 ~ +2^1024 定点数类型 \\DEC(M,D)M+ 2 最大取值范围与 double 相同，给定 decimal 的有效范围由 M 和 D 决定
注意：
M: 整数部位 + 小数部位的总长度
D: 小数部位
D 和 M 都省略时：
1、如果是 decimal 类型, 则 M 默认为 10,D 默认为 0;
2、如果是 floact 和 double, 会根据插入的数值的精确度来决定精度。
3、定点型的精确度较高, 如果要求插入数值的精确度较高如货币运算则考虑使用。
2.3 字符型
字符串类型 M 是否可以省略特点空间耗费效率范围 char(M)M 可以省略, 默认为 1 定长比较耗费高 M 为 0~255 之间的整数 varchar(M)M 不可以省略可变长比较节省低 M 为 0~65535 之间的整数
binary 和 varbinary 类型，类似于 char 和 varchar，不同的是它们包含二进制字符而不包含非二进制字符，即保存较短的二进制。
Bit(M)类型，字节为 1~8，范围为 Bit(1)~Bit(8)。
Enum 类型，即枚举类型，要求插入的值必须属于列表中指定的值之一，如果列成员为 1~255，则需要 1 个字节存储；如果列成员为 255~65535，则需要 2 个字节存储，最多需要 65535 个成员。
Set 类型，和 Enum 类似，可以保存 0~64 个成员。和 Enum 最大的区别是：Set 类型一次可以选取多个成员，而 Enum 只能选一个，根据成员个数不同，存储所占的字节也不同。
成员数字节数 1~819~16217~24325~32433~648
【枚举】CREATE TABLE tab_set(s1 SET( a , b , c INSERT INTO tab_set VALUES( a INSERT INTO tab_set VALUES( c,a INSERT INTO tab_set VALUES( a,b,c # 插入后, 内部会进行排序, 如插入 c,a 会变成 a,c2.4 日期类型
日期和时间类型特点字节最小值最大值 date 只保存日期 41000-01-019999-12-31datetime 保存日期 + 时间 81000-01-01 00:00:009999-12-31 23:59:59timestamp(使用较多)保存日期 + 时间 41870010800012038 年的某个时刻 time 只保存时间 3 -838:59:59838:59:59year 只保存年 119012155
timestamp 和实际时区有关，更能反映实际的日期；datetime 则只能反映出插入时的当地时区。
timestamp 的属性受 Mysql 版本和 SQLMode 的影响很大。
【日期类型】CREATE TABLE tab_date(
t1 DATETIME, # 不受时区影响
t2 TIMESTAMP # 受时区影响);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon= +8:00 # 设置时区 SHOW VARIABLES LIKE time_zone # 显示当前时区二、六大约束
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。
NOT NULL 非空约束，规定某个字段不能为空 UNIQUE 唯一约束，规定某个字段在整个表汇中是唯一的 PRIMARY KEY 主键(唯一且非空)FOREIGN KEY 外键 CHECK 检查约束(mysql 中不支持)DEFAULT 默认值, 保证该字段有默认值
列级约束：六大约束语法上都支持，但外键约束没有效果。
表级约束：除了非空、默认，其他都支持。
常用的做法是：其他约束都写在列级，外键约束写在表级。
主键和唯一对比是否保证唯一性是否允许为空允许有几个是否允许组合 (不推荐) 主键√×至多一个主键允许组合主键唯一√√(允许有一个 null)可以有多个唯一允许组合唯一
外键的特点：
要求在从表设置外键关系。
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。
主表的关联列必须是一个 key(一般是主键或唯一)
插入数据时，先插入主表再插入从表；删除数据时，先删除从表再删除主表。
列级约束和表级约束比较位置支持的约束类型是否可以起别名列级约束列的后面语法都支持，但外键没有效果不可以表级约束所有列的下面默认和非空不支持，其他支持可以(主键没有效果)
【列级约束】直接在字段名和类型后面追加约束类型。注意：只支持默认、非空、主键、唯一，不支持外键约束 CREATE DATABASE students;USE students;CREATE TABLE major(
id INT PRIMARY KEY,# 主键
majorName VARCHAR(20));CREATE TABLE stuinfo(
id INT PRIMARY KEY,# 主键
stuName VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) CHECK(gender= 男 OR gender= 女),# 检查
seat INT UNIQUE,# 唯一
age INT DEFAULT 18, # 默认约束
majorId INT REFERENCES major(id) # 外键);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引, 外键, 唯一【表级约束】# 语法:[CONSTRAINT 约束名 ] 约束类型(字段名)DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),# 主键
CONSTRAINT uq UNIQUE(seat), # 唯一
CONSTRAINT ck CHECK(gender= 男 OR gender= 女),# 检查(不报错, 但无效)
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键);SHOW INDEX FROM stuinfo;【修改表时添加约束】# 添加非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主键 ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一键 ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外键 ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表时删除约束】# 删除非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 删除默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT;# 删除主键 ALTER TABLE stuinfo DROP PRIMARY KEY;# 删除唯一 ALTER TABLE stuinfo DROP INDEX seat;# 删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;学习了约束，尝试完成籼米的测试题
1、列级约束:
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列约束不支持起名字
表级约束:
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 实际上主键起了名字也没效果
2、与 1 类似
3、
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
四、标识列
标识列又称为自增长列，其实也可以将标识列纳入约束的范围。
含义：可以不用手动的插入值，系统提供默认的序列值。
特点：
1. 标识列不一定非要和主键搭配，但要求是一个 key。
2. 一个表至多可以有一个表示列。
3. 表示列的类型只能为数值型。
4. 标识列可以通过 SET auto_increment_increment=3; 设置步长，也可以通过手动插入值来设置起始值。
【创建表时设置标识列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT, # 设置自动自增
NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL, 花花 INSERT INTO tab_identity(NAME) VALUE( Hudie SELECT * FROM tab_identity;SHOW VARIABLES LIKE %auto_increment% SET auto_increment_increment=3;# 设置步长为 3# 起始值可以通过改变第一条记录的值来更改 TRUNCATE TABLE tab_identity;【修改表时设置标识列】CREATE TABLE tab_identity(
id INT, # 设置自动自增
NAME VARCHAR(20));# 设置主键和标识列 ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT; 修改表时删除标识列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;
五、级联删除与置空
级联删除：
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
级联置空：
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
【演示级联删除、级联置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major(
id INT PRIMARY KEY,
majorName VARCHAR(20));INSERT INTO majorVALUES(1, Java),(2, Python),(3, Go CREATE TABLE IF NOT EXISTS stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT);INSERT INTO stuinfoSELECT 1, join1 , 女 ,NULL,NULL,1 UNION ALLSELECT 2, join2 , 女 ,NULL,NULL,1 UNION ALLSELECT 3, join3 , 女 ,NULL,NULL,2 UNION ALLSELECT 4, join4 , 女 ,NULL,NULL,2 UNION ALLSELECT 5, join5 , 女 ,NULL,NULL,1 UNION ALLSELECT 6, join6 , 女 ,NULL,NULL,3 UNION ALLSELECT 7, join7 , 女 ,NULL,NULL,3 UNION ALLSELECT 8, join8 , 女 ,NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 传统方式添加外键 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 删除 major 表的 3 号专业 # 方式 1: 级联删除# 先删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联删除 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式 2: 级联置空# 先删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联置空 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;以上是“MySQL 中 DDL 数据定义语句有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
向 AI 问一下细节
![Frank#smith[Kfyzeiuqujbpygze,2,5]](https://cravatar.cn/avatar/ebd2642d86a4b40100d29dc76ec89b0d?s=96&d=monsterid&r=r)
