小编给大家分享一下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 表名;

【库的管理】#创建库CREATEDATABASEIFNOTEXISTSbooks;#修改库的字符集ALTERDATABASEbooksCHARACTERSETgbk;#删除库DROPDATABASEIFEXISTSbooks;【表的管理】#1.创建表CREATETABLEIFNOTEXISTSbook(idINT,bNameVARCHAR(20),authorIDINT,publishDateDATETIME);DESCbook;CREATETABLEauthor(idINT,au_nameVARCHAR(20),nationVARCHAR(10));DESCauthor;#2.修改表#修改列名ALTERTABLEbookCHANGECOLUMNpublishDatepubDateDATETIME;#修改列的类型或约束ALTERTABLEbookMODIFYCOLUMNpubdateTIMESTAMP;#添加新列ALTERTABLEauthorADDCOLUMNannualDOUBLE;#添加新列作为第一个字段ALTERTABLEauthorADDCOLUMNnewT1INTFIRST;#添加新列在指定的列后ALTERTABLEauthorADDCOLUMNnewT2INTAFTERnewT1;#删除列ALTERTABLEauthorDROPCOLUMNannual;#修改表名ALTERTABLEauthorRENAMETObook_author;#3.表的删除DROPTABLEIFEXISTSbook_author;SHOWTABLES;#4.表的复制INSERTINTOauthorVALUES(1,'小樱','日本'),(2,'悟空','中国'),(3,'绿巨人','美国'),(4,'哪吒','中国');#仅复制表的结构CREATETABLEauthor2LIKEauthor;#复制表的结构+数据CREATETABLEauthor3SELECT*FROMauthor;#只复制部分数据CREATETABLEauthor4SELECTid,au_nameFROMauthorWHEREnation='中国';#仅复制某些字段CREATETABLEauthor5SELECTid,au_nameFROMauthorWHERE1=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设置为无符号CREATETABLEIFNOTEXISTStab_int(t1INT,#有符号t2INTUNSIGNED,#无符号t3INTZEROFILL#添加zerofill后自动变更为无符号整型,位数不够0填充.);DESCtab_int;SELECT*FROMtab_int;#2.如果插入的数值超出了整型的范围,会报outofrange异常INSERTINTOtab_intVALUES(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

【枚举】CREATETABLEtab_set(s1SET('a','b','c'));INSERTINTOtab_setVALUES('a');INSERTINTOtab_setVALUES('c,a');INSERTINTOtab_setVALUES('a,b,c');#插入后,内部会进行排序,如插入c,a会变成a,c

2.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的影响很大。

【日期类型】CREATETABLEtab_date(t1DATETIME,#不受时区影响t2TIMESTAMP#受时区影响);INSERTINTOtab_dateVALUES(NOW(),NOW());SETtime_zon='+8:00';#设置时区SHOWVARIABLESLIKE'time_zone';#显示当前时区

二、六大约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。

NOT NULL非空约束，规定某个字段不能为空UNIQUE唯一约束，规定某个字段在整个表汇中是唯一的PRIMARY KEY主键(唯一且非空)FOREIGN KEY外键CHECK检查约束(mysql中不支持)DEFAULT默认值,保证该字段有默认值

列级约束：六大约束语法上都支持，但外键约束没有效果。

表级约束：除了非空、默认，其他都支持。

常用的做法是：其他约束都写在列级，外键约束写在表级。

主键和唯一对比是否保证唯一性是否允许为空允许有几个是否允许组合(不推荐)主键√×至多一个主键允许组合主键唯一√√(允许有一个null)可以有多个唯一允许组合唯一

外键的特点：

要求在从表设置外键关系。

从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。

主表的关联列必须是一个key(一般是主键或唯一)

插入数据时，先插入主表再插入从表；删除数据时，先删除从表再删除主表。

列级约束和表级约束比较位置支持的约束类型是否可以起别名列级约束列的后面语法都支持，但外键没有效果不可以表级约束所有列的下面默认和非空不支持，其他支持可以(主键没有效果)

【列级约束】直接在字段名和类型后面追加约束类型。注意：只支持默认、非空、主键、唯一，不支持外键约束CREATEDATABASEstudents;USEstudents;CREATETABLEmajor(idINTPRIMARYKEY,#主键majorNameVARCHAR(20));CREATETABLEstuinfo(idINTPRIMARYKEY,#主键stuNameVARCHAR(20)NOTNULL,#非空genderCHAR(1)CHECK(gender='男'ORgender='女'),#检查seatINTUNIQUE,#唯一ageINTDEFAULT18,#默认约束majorIdINTREFERENCESmajor(id)#外键);DESCstuinfo;SHOWINDEXFROMstuinfo;#查看表中所有的索引,外键,唯一【表级约束】#语法:[CONSTRAINT约束名]约束类型(字段名)DROPTABLEIFEXISTSstuinfo;CREATETABLEstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT,CONSTRAINTpkPRIMARYKEY(id),#主键CONSTRAINTuqUNIQUE(seat),#唯一CONSTRAINTckCHECK(gender='男'ORgender='女'),#检查(不报错,但无效)CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id)#外键);SHOWINDEXFROMstuinfo;【修改表时添加约束】#添加非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NOTNULL;#添加默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINTDEFAULT18;#添加主键ALTERTABLEstuinfoMODIFYCOLUMNidINTPRIMARYKEY;#添加唯一键ALTERTABLEstuinfoMODIFYCOLUMNseatINTUNIQUE;#添加外键ALTERTABLEstuinfoADDCONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id);DESCstuinfo;SHOWINDEXFROMstuinfo;【修改表时删除约束】#删除非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NULL;#删除默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINT;#删除主键ALTERTABLEstuinfoDROPPRIMARYKEY;#删除唯一ALTERTABLEstuinfoDROPINDEXseat;#删除外键ALTERTABLEstuinfoDROPFOREIGNKEYfk_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;设置步长，也可以通过手动插入值来设置起始值。

【创建表时设置标识列】DROPTABLEIFEXISTStab_identity;CREATETABLEtab_identity(idINTPRIMARYKEYAUTO_INCREMENT,#设置自动自增NAMEVARCHAR(20));INSERTINTOtab_identityVALUE(NULL,'花花');INSERTINTOtab_identity(NAME)VALUE('Hudie');SELECT*FROMtab_identity;SHOWVARIABLESLIKE'%auto_increment%';SETauto_increment_increment=3;#设置步长为3#起始值可以通过改变第一条记录的值来更改TRUNCATETABLEtab_identity;【修改表时设置标识列】CREATETABLEtab_identity(idINT,#设置自动自增NAMEVARCHAR(20));#设置主键和标识列ALTERTABLEtab_identityMODIFYCOLUMNidINTPRIMARYKEYAUTO_INCREMENT;修改表时删除标识列【】ALTERTABLEtab_identityMODIFYCOLUMNidINT;

五、级联删除与置空

级联删除：
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;

【演示级联删除、级联置空】DROPTABLEmajor,stuinfoCREATETABLEIFNOTEXISTSmajor(idINTPRIMARYKEY,majorNameVARCHAR(20));INSERTINTOmajorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATETABLEIFNOTEXISTSstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT);INSERTINTOstuinfoSELECT1,'join1','女',NULL,NULL,1UNIONALLSELECT2,'join2','女',NULL,NULL,1UNIONALLSELECT3,'join3','女',NULL,NULL,2UNIONALLSELECT4,'join4','女',NULL,NULL,2UNIONALLSELECT5,'join5','女',NULL,NULL,1UNIONALLSELECT6,'join6','女',NULL,NULL,3UNIONALLSELECT7,'join7','女',NULL,NULL,3UNIONALLSELECT8,'join8','女',NULL,NULL,1);SELECT*FROMmajor;SELECT*FROMstuinfo;#传统方式添加外键ALTERTABLEstuinfoADDCONSTRAINTfk_stu_majorFOREIGNKEY(majorid)REFERENCESmajor(id);#删除major表的3号专业#方式1:级联删除#先删除外键ALTERTABLEstuinfoDROPFOREIGNKEYfk_stu_major;#添加外键时添加级联删除ALTERTABLEstuinfoADDCONSTRAINTfk_stu_majorFOREIGNKEY(majorid)REFERENCESmajor(id)ONDELETECASCADE;DELETEFROMmajorWHEREid=3;#方式2:级联置空#先删除外键ALTERTABLEstuinfoDROPFOREIGNKEYfk_stu_major;#添加外键时添加级联置空ALTERTABLEstuinfoADDCONSTRAINTfk_stu_majorFOREIGNKEY(majorid)REFERENCESmajor(id)ONDELETESETNULL;DELETEFROMmajorWHEREid=2;

以上是“MySQL中DDL数据定义语句有哪些”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！