MySQL DDL 数据定义语言 总结

1、什么是DDL

Data Define Language，数据定义语言。主要用来对数据库和表进行管理操作。主要包括建库、删库、建表、改表、删表、复制表、对表列的增删改等。

2、常用操作

2.1 创建库

CREATE DATABASE [IF NOT EXISTS] 库名 [DEFAULT CHARACTER SET 字符集名];

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

两点注意：

1. 字符集建议使用 utf8mb4（MySQL5.5.3以后增加），可以使用最多4个字节存储字符，所以可以存储emoji表情等。这才是真正意义上的utf8（MySQL的utf8只支持使用最多3个字节存储字符）。
2. 校对建议使用 utf8mb4_general_ci。虽然在遇到某些特殊语言或者字符时，排序没有utf8mb4_unicode_ci精确（但大多数情况下这些特殊字符的顺序并不需要那么精确）。而utf8mb4_general_ci比较和排序的时候更快。

2.2 修改库

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2.3 删除库

DROP DATABASE [IF EXISTS] 库名;

DROP DATABASE IF EXISTS test_db2;

2.4 创建表

CREATE TABLE [IF NOT EXISTS] 表名(
字段名1 类型[(宽度)] [约束条件] [COMMENT ‘字段说明’],
字段名2 类型[(宽度)] [约束条件] [COMMENT ‘字段说明’],
字段名3 类型[(宽度)] [约束条件] [COMMENT ‘字段说明’]
)[表的一些设置];

注意事项：
1.在同一张表中，字段名不能相同
2.宽度和约束条件为可选参数，字段名和类型则是必须的
3.最后一个字段后不能加逗号
4.类型是用来限制字段必须以何种数据类型来存储数据
5.类型也是对字段的约束（约束字段的数据必须为某种类型）
6.类型后写的约束条件是在类型之外额外添加的约束
7.字符类型尽量使用varchar，而不要使用char

约束说明：

• NOT NULL ：表示该字段不能为空
• DEFAULT value：为该字段设置默认值，默认值就是value。插入时未设置该字段的值，就使用默认值。
• PRIMARY KEY：表示该字段为该表的主键，可以唯一的标识记录，插入重复的主键会报错。
  • 写法1：在创建表时跟在 列后面 PRIMARY KEY
  • 写法2：在所有列定义之后定义 PRIMARY KEY(字段名)。支持联合主键（多个字段一起最为主键），写法为 PRIMARY KEY(字段名1,字段名2)
• FOREIGN KEY：[CONSTRAINT 这个外键约束的名字] FOREIGN KEY (当前表的字段名) REFERENCES 引用的外键表 (外键表中的字段名)。
  • 注意：
  • 1.两张表中需要建立外键关系的字段数据类型要一致
  • 2.要设置外键的字段不能为主键
  • 3.被引用的字段要为主键
  • 4.插入设置外键的字段的值必须在被引用的字段中存在
  • 5.删除被引用的字段所在记录必须先删除其他表中引用它的记录。
    举例：ALTER TABLE tb_employee ADD CONSTRAINT fk_employee_dept FOREIGN KEY (d_id) REFERENCES tb_dept(id);
• UNIQUE KEY(字段名)
  • 写法1：写在字段后
  • 写法2：在所有列定义之后定义
• AUTO_INCREMENT：标识该字段的值自动增长（整数类型且为主键）。默认值从1开始，每次+1。表数据被清空且数据库重启之后，自增列的值将从初始值开始；或者使用 TRUNCAT 表名（删除表并重建相同结构表，也叫截断表）也可以让自增列的值从初始值开始。

2.5 删除表及修改表

• 删除表
  语法： DROP TABLE [IF EXISTS] 表名;
  举例： DROP TABLE IF EXISTS stu;
• 修改表名
  语法： ALTER TABLE 表名 RENAME [TO] 新表名;
  举例： ALTER TABLE stu2 RENAME stu;
• 给表设置备注
  语法： ALTER TABLE 表名 COMMENT ‘备注信息’;
  举例： ALTER TABLE stu COMMENT ‘学生表’;

2.6 复制表

• 只复制表结构
  语法： CREATE TABLE 表名 LIKE 被复制的表名;
  举例： CREATE TABLE stu LIKE student;
• 复制表结构+数据
  语法： CREATE TABLE 表名[AS] SELECT 字段,… FROM 被复制的表名 [where子句];
  举例： CREATE TABLE stu2 SELECT * FROM student;

注意： 复制表结构+数据的方法并不能将主键及自增约束复制过来，如果需要，可分两步执行。
1.复制表结构
CREATE TABLE stu3 LIKE student;
2.复制表数据
INSERT INTO stu3 SELECT * FROM student;

2.7 表列的管理

• 增加列
  语法： ALTER TABLE 表名 ADD COLUMN 列名 类型 [列约束] [ FIRST|AFTER 字段名];
  举例： ALTER TABLE stu ADD COLUMN gender CHAR(1) DEFAULT ‘1’ COMMENT ‘性别 1男 2女’;
• 修改列
  语法1： ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 [约束];
  举例1： ALTER TABLE stu MODIFY COLUMN gender VARCHAR(2) DEFAULT ‘1’ COMMENT ‘性别 1男 2女’;
  语法2： ALTER TABLE 表名 CHANGE COLUMN 列名 新列名 新类型 [约束];
  举例2： ALTER TABLE stu CHANGE COLUMN gender sex CHAR(1) DEFAULT ‘1’ COMMENT ‘性别 1男 2女’;

注意：修改列的时候，modify不能修改列名，change可以。如果修改时不加约束，会清空原有约束。

• 删除列
  语法： ALTER TABLE 表名 DROP COLUMN 列名;
  举例： ALTER TABLE stu DROP COLUMN sex;

3、数据类型

参考这里