Mysql系列—DDL常见操作汇总（四）

一、DDL定义

DDL：全称为Data Define Language数据定义语言，主要用来对数据库、表进行一些管理操作。

如：建库、删库、建表、修改表、删除表、对列的增删改等等…

注： 文中涉及的语法用[]包含的内容为可选项。

二、库的管理

创建库：

create database [if not exists] 库名;

删除库：

drop database [if exists] 库名;

建库通用的写法：

drop database if exists 旧库名;
create database 新库名；

列出库信息：

show databases like ‘库名’;

三、表的管理

创建表：

create table 表名(
	字段名1	类型[(宽度)]  [约束条件]	[comment '字段说明'],
	字段名2	类型[(宽度)]  [约束条件]	[comment '字段说明'],
	字段名3	类型[(宽度)]  [约束条件]	[comment '字段说明']
	...
)[表的一些设置];

注意：

• 在同一张表中，字段名不能相同；
• 宽度和约束条件为可选参数，字段名和类型是必填参数；
• 最后一个字段后不能加逗号；
• 类型是用来限制字段必须以何种数据类型来存储记录；
• 类型其实也是对字段的约束（约束字段下的记录/数据必须为该类型）；
• 类型后写的约束条件是在类型之外的额外添加的约束

约束说明：

• not null：标识字段不能为空
  

---

---

• default value：为该字段设置默认值，默认值为value
  

上面插入时未设置b的值，自动取默认值 0

---

---

• primary key：标识该字段为该表的主键，可以唯一的标识记录，插入重复会报错

两种写法，如下：

方式1：跟在列后

---

方式2：在所有列定义之后定义

插入重复的值，会报违反主键约束。

方式2支持多字段作为主键，多个之间用逗号隔开，语法：primary key(字段1，字段2，字段3…)。

示例：

---

---

• foreign key：为表中的字段设置外键

语法：foreign key(当前表的列名) references 引用的外键表(外键表中的字段名)

说明：表示demo7中demo6_a字段的值来源于表demo6中的字段a。

注意几点：

• 两张表中需要建立外键关系的字段，类型需要一致；
• 要设置外键的字段不能为主键；
• 被引用的字段（即引用外键表的字段）需要为 主键 或者 唯一键；
• 被插入的值在外键表必须存在，如上面的demo7中插入demo6_a为2的时候报错了，原因：2的值在demo6表中不存在。

---

---

• unique key：标识该字段的值是唯一的
  支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败。

定义有两种方式。

方式 1：跟在字段后

---

方式 2：在所有列定义之后定义

方式2支持多字段作为唯一值属性，多个之间用逗号隔开，语法：unqiue key(字段1，字段2，字段3…)。

示例：

---

---

• auto_increment：标识该字段的值自动增长（整型类型，而且为主键）
  

字段a为自动增长，默认值从1开始，每插入一条记录+1；
关于自动增长字段的初始值、步长可以在Mysql中进行设置，比如设置初始值为10000，每次步增10。

附加内容：修改Mysql自动增长字段的默认初始值以及步长

1、查看当前数据库的自增长设置

show variables like ‘auto_inc%’;

2、修改默认初始值以及步长

set @@auto_increment_increment=2;
set @@auto_increment_offset=2;

注意：

自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中自增列的最大值作为当前值，如果表数据被清空后，数据库重启后，自增列的值将从初始值开始。

下面演示一下：

上面删除了demo11表中数据，然后又重新插入了一条数据，然后看a的值变成了3，证实了上面说的。

下面执行下面操作：

删除demo11数据，重启Mysql，插入数据，然后再看a的值是不是被初始化到系统默认的初始值了？

删除表:

drop table [if exists] 表名；

修改表名:

alter table 表名 rename [to] 新表名;

设置表备注:

alter table 表名 comment '备注信息';

复制表:
1、只复制表结构

create table 表名 like 被复制的表名;

示例：

2、复制表结构和数据

create table 表名 [as] select 字段... from 被复制的表名

示例：

表结构和数据都过来了！

四、列的管理

添加列：

alter table 表名 add column 列名 类型 [列约束];

示例：

修改列：

alter table 表名 modify column 列名 新类型 [约束];

或者

alter table 表名 change column 列名 新列名 新类型 [约束];

注意： modify不能修改列名，change可以修改列名

我们看一下demo15的表结构：

我们将字段c名字和类型修改一下，如下：

删除列：

alter table 表名 drop column 列名;

示例：

---

再说一个额外话题：有外键的表如何删除？

解决思路：我们使外键约束失效，这里有一个属性：FOREIGN_KEY_CHECKS，当它为1时，即FOREIGN_KEY_CHECKS = 1代表外键约束有效；当它为0时，即FOREIGN_KEY_CHECKS = 0代表外键约束无效，这时我们就可以删除了！

//1、查看FOREIGN_KEY_CHECKS 
SELECT @@FOREIGN_KEY_CHECKS;

//2、设置FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS=0;

//3、对表做删除、更新等操作

//4、回复外键约束
SET FOREIGN_KEY_CHECKS=1;