MySQL 学习笔记——基础 DDL 数据定义语言

DDL 数据定义语言

含义： DDL(Data Define Language) 数据库定义语言：库和表的管理、常见的数据类型介绍、常见约束。

一、库的管理

1、创建

语法：create database 库名;，create database if exists 库名;

2、删除

语法：drop database 库名;，drop database if exists 库名;

3、修改

只能通过修改文件名的方式修改数据库名。

二、表的管理

1、创建表 create

语法：

create table 表名(
    字段名 字段类型 [字段约束],
    字段名 字段类型 [字段约束]
)

2、修改表 alter

语法：

-- 1、修改列名
alter table 表名 change [column] 旧列名 新列名 类型;

-- 2、修改列的类型
alter table 表名 modify column 列名 新类型;

-- 3、添加新列
alter table 表名 add column 列名 类型;

-- 4、删除列
alter table 表名 drop column 列名;

-- 5、修改表名
alter table 表名 rename to 新表名;

-- 6、查看表结构
desc 表名;

3、删除表 delete

语法：drop table 表名;

4、复制表

语法：

-- 1、仅仅复制表的结构
create table 表名 like 被复制的表名;

-- 2、结构与数据同时复制
create table 表名 select * from 被复制的表;

-- 3、复制部分数据
create table 表名 select * from 被复制的表 where 筛选条件;

-- 4、复制指定列及其数据
create table 表名 select 字段1, 字段2.... from 被复制的表 where 筛选条件;

-- 5、复制指定列，不填充数据
create table 表名 select 字段1, 字段2.... from 被复制的表 where 0; -- 0代表false 所以筛选条件为false

5、常见的数据类型

a、数值型

① 整型

特点：

• 如果不设置有符号或无符号，默认为有符号。如果使用无符号则需要添加unsigned关键字，如：create table test(id int unsigned);。
• 如果插入的数值超过类型范围。MySQL8会显示数值超于临界值并插入失败。MySQL5 会显示数值超于临界值并插入临界值。
• 如果不设置长度，会有默认的长度
• 设置的长度如int(5)，代表了最大显示长度，如果数值长度不够就会使用0来填充。默认使用0填充功能不开启，如果需要开启则需要搭配zerofull关键字使用。

② 小数

分类：

• 浮点型
  • float(M, D)
  • double(M, D)
• 定点型
  • dec(M, D)
  • decimal(M, D)

特点：

• M代表整数部位+小数部分的总长度，D 代表小数部位的总长度。如果超出范围则插入临界值，MySQL8.0 会显示超出临界值，插入失败。MySQL5.7 会显示超出临界值，插入成功。
• M和D都可以省略。默认情况下，float和double根据插入值来确认精度。decimal为decimal(10, 0)只保存证整数。
• 定点型的精度较高，如果要求插入数值的精度较高如：货币运算等。其他类型主要使用float或double就行。

b、字符型（串数据）

① 较短的文本：char，varchar

特点：

	写法	M的含义	特点	空间的耗费情况	效率
char	char(M)	最大的字符数，可以省略，默认为1。	固定长度的字符	比较耗费	高一点点
varchar	varchar(M)	最大的字符数，不可省略。	可变长度的字符	比较节省	低一点点

② 较长的文本：

• text、blob（较长的二进制数据）
• binary、varbinary用于保存较短的二进制数据。

③ 其他类型：

• Enum

  特点：要求插入的值必须属于列表中指定的值之一。当成员列表长度为1 ~ 255时，存储需要1个字节。当成员列表长度为255 ~ 65535时，则存储需要2个字节。枚举的值不区分大小写。

  create tabl test (f1 enum('1', '2', '3'));
  insert into test values('1');
  

• Set

  特点：和Enum类型相似，里面可以保存0 ~ 64个成员。和Enum最大的区别是：Set类型一次可以选取多个成员，而Enum只能选取一个成员。 根据成员数量的不同，存储所占的字节也不同。Set的值也不区分大小写。

  成员数量	字节数量
  1 ~ 8	1
  9 ~ 16	2
  17 ~ 24	3
  25 ~ 32	4
  33 ~64	8

  create table test1 (
  	f1 set('a', 'b', 'c', 'd', 'e');
  );
  
  insert into test1 values('a');
  insert into test1 values('a,b');
  insert into test1 values('a,d');
  

c、日期型

需要注意的点：

• date只能保存日期，time只能保存时间，year只能保存年份。
• datetime占用的存储大，保存时间范围大1000-01-01 ~ 9999-12-31。timestamp占用的存储小，保存时间范围小19700101080001到2038年的某个时间。
• timestamp和实际时区有关，更能反应实际的日期，而datetime则只能反映插入时的当地时区。
• timestamp的属性受MySQL版本和SQLMode的影响很大。

6、常见的字段约束

a、字段约束分类

分类：

• not null：非空，用于保证字段的值不为空。比如：姓名、密码等。
• default：默认，保证该字段有默认值。比如：性别。
• primary key：主键，保证该字段的值具有唯一性并且非空。比如：id。
• unique：唯一，用于保证该字段的值具有唯一性，可以为空。比如：城市编号等。
• check：检查，【MySQL不支持，不报错但是不起作用】如：设置性别 必须为 男 或者 女，其他值就不行。
• foreign key：外键，用于限制两个表的关系，用于保证该字段值必须来自于主表的关联列的值。在从表中添加外键约束，用于应用主表中某列的值。比如：部门编号、工种编号等。

添加的时机：

• 创建表的时候
• 修改表的时候

约束添加的分类：

• 列级分类：

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

• 表级分类：除了非空、默认，其他的都支持。

create table 表名(
	字段名 字段类型 列级约束,
    字段名 字段类型 列级约束,
    表级约束
)

① 主键和唯一的对比：

	保证唯一性	是否允许为空	表中约束的数量	是否组合
主键	可以	不可以	最多1个	可以，不推荐
唯一	可以	可以	可以有多个	可以，不推荐

create table student (
	id int,
    stuName varchar(20),
    seat int,
    age int,
    
    constraint pk primary key(id,stuName), # 组合主键
    constraint uq unique(seat,age),        # 组合唯一
);

② 外键的特点：

• 要求在从表设置外键关系
• 从表的外键列的类型和主表的关联列的类型一致或兼容，名称无要求。
• 主表的关联列必须是一个key（主键、唯一、外键），一般情况是主键。
• 插入数据时，应先插入主表再插入从表。删除数据时，先删除从表在删除主表

b、创建表时添加列级约束

create table student (
	id int primary key, # 主键
    stuName varchar(20) not null, # 非空
    gender char(1) check(gender='男' or gender in '女'), # 检查 MySQL无效，但不报错。
    seat int unique, # 唯一
    age int default 14, # 默认值
    majorId int references major(id) # 外键设置不生效, major为主表
);

c、创建表时添加表级约束

语法：[constraint 约束名] 约束类型(字段名) 如果不起名，会使用默认名。默认名为字段名。

create table student (
	id int,
    stuName varchar(20),
    gender char(1),
    seat int,
    age int,
    majorId int,
    
    constraint pk primary key(id), # 添加主键 【MySQL】主键会生效，但是主键名设置不成功。
    constraint uq unique(seat),   # 唯一键
    constraint ck check(gender='男' or gender in '女'), # 检查 MySQL无效，但不报错。
    constraint fk_student_major foreign key(majorid) references major(id), # 外键
);

d、修改表时添加约束

-- 1、添加非空约束
alter table student modify column stuName varchar(20) not null;

-- 2、添加默认约束
alter table student modify column age int default 18;

-- 3、添加主键约束
-- 列级约束的写法
alter table student modify column id int primary key;
-- 表级约束的写法
alter table student add primary key(id);

-- 4、添加唯一约束
-- 列级约束的写法
alter table student modify column seat int unique;
-- 表级约束的写法
alter table student add unique(id);

-- 5、添加外键约束
alter table student add constraint fk_student_major foreign key references major(id);

总结：

• 添加列级约束：alter table 表名 modify column 字段名 字段类型 新约束;
• 添加表级约束：alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];

e、修改表时删除约束

-- 1、删除非空约束
alter table student modify column stuName varchar(20) null;

-- 2、删除默认约束
alter table student modify column age int;

-- 3、删除主键
alter table student modify column id int;
alter table student drop primary key;

-- 4、删除唯一键
alter table student drop index seat;

-- 5、删除唯一约束
alter table student drop foreign key fk_student_major;

f、标识列（自增长）

create table student(
	id int primary key auto_increment;
);

特点：

• 标识列不一定要和主键搭配，但要求是一个key。
• 一个表至多有一个标识列。
• 标识列的类型只能是数值型。
• 标识列通过set auto_increment_incremment=数值设置步长。

-- 1、修改表时设置标识列
alter table student modify column id int primary key auto_increment;

-- 2、修改表时删除标识列
alter table student modify column id int primary key;