MySQL精讲之三：DDL数据定义语句

概述MySQL精讲之三：DDL数据定义语句

免费学习推荐：mysql视频教程

文章目录

一、DDL数据定义语句二、数据类型2.1 整型2.2 小数2.3 字符型2.4 日期类型二、六大约束四、标识列五、级联删除与置空

---

一、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	微整型	1	0~255	-128~127
Smallin	小整型	2	0~65535	-32768~32767
Mediumint	中整型	3	0~1677215	-8388608~8388607
Int或Integer	整型	4	0~4294967295	-2147483648~2147483647
Bigint	大整型	8	0~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^128
double(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~8	1
9~16	2
17~24	3
25~32	4
33~64	8

【枚举】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,c

2.4 日期类型

日期和时间类型	特点	字节	最小值	最大值
date	只保存日期	4	1000-01-01	9999-12-31
datetime	保存日期+时间	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp(使用较多)	保存日期+时间	4	187001080001	2038年的某个时刻
time	只保存时间	3	-838:59:59	838:59:59
year	只保存年	1	1901	2155

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教程(视频)

总结

以上是内存溢出为你收集整理的MySQL精讲之三：DDL数据定义语句全部内容，希望文章能够帮你解决MySQL精讲之三：DDL数据定义语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。