MySQL数据表DDL实战：建表、改表、删表与设计规范

数据库中的表也是数据库服务结构中的重要组成部分；

环境准备-创建数据库

mysql > create database db01;

1、创建数据表信息

通过数据库服务管理工具，图形操作创建数据库表信息:

(1)依次点击【Schemas】-【db01】-【Tables】,右键选择【Create Table...】

图11

(2)定义表名为【stu】，添加注释【学生表】，其他内容定义如下：

Column Name	Datatype	Default/Expression
id	INT
name	VARCHAR(45)
age	TINYINT	18
gender	ENUM('男','女','其他')	其他

图12

(3)点击【apply】后，会将图形操作的配置信息转换为相应的建表语句

Day003-数据库服务基础命令应用-图13

完整建表语句参考：

CREATE TABLE `student` (
  `id` int NOT NULL COMMENT '学号信息',
  `name` varchar(45) NOT NULL COMMENT '学生名',
  `age` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'

说明：企业中创建表建议使用第三方工具

创建表的基本语法格式：

create table <表名> (
     <字段名1> <类型1> ,
    …
    <字段名n> <类型n>);

以上是创建表的具体格式信息，其中create table是关键字，不能更改，但是大小写可以变化。

实战情况：需要创建一个学生信息表：

# 创建数据库db14
mysql> create database db14;

# 切换数据库环境
mysql> use db14;

#查看是否切换数据库成功
mysql> select database();
+------------+
| database() |
+------------+
| db14       |
+------------+
1 row in set (0.00 sec)

# 创建数据表信息
mysql > create table stu1(
id int(10) not null,
name varchar(20) not null,
age tinyint(2)  NOT NULL default '0',
dept varchar(16)  default NULL
);

# 查看db14库下的所有数据表
mysql> show tables;
+----------------+
| Tables_in_db14 |
+----------------+
| stu1           |
+----------------+
1 row in set (0.00 sec)

# 查看表结构
mysql > desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | tinyint     | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 获取创建表语句
mysql> show create table stu1\G
*************************** 1. row ***************************
       Table: stu1
Create Table: CREATE TABLE `stu1` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` tinyint NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

2、查看数据表信息

# 创建数据库db14
mysql> create database db14;

# 切换数据库环境
mysql> use db14;

# 查看数据库中所有表信息
mysql > show tables;

# 查看数据库中名字带t的表
mysql> show tables like '%t%';

# 查看数据库中指定表数据结构信息
mysql > desc stu1;

# 查看数据库中指定表创建语句信息
mysql > show create table stu1;

3、修改数据表信息

(1) 修改数据表属性信息

# 创建数据库db14
mysql> create database db14;

# 创建数据表信息
mysql > create table db14.stu1(
id int(10) not null,
name varchar(20) not null,
age tinyint(2)  NOT NULL default '0',
dept varchar(16)  default NULL
);

# 修改数据表名称信息
## 方式一
mysql> rename table stu1 to stu2;
## 方式二
mysql > alter table stu2 rename stu3;

# 查看表名称信息是否修改
mysql > show tables;

# 修改数据表编码信息，修改表结构中字符集编码信息
mysql > alter table db14.stu2 charset utf8mb4;

#查看表字符编码信息情况
mysql > show create table db14.stu2;

(2) 修改数据表结构信息

数据表结构调整命令语法

#利用alter在数据表中添加新的表结构字段
mysql > alter table <表名> add column <字段名称> <数据类型> <约束与属性> [comment '注释'] [选项参数];

#利用alter在数据表中删除已有表结构字段
mysql > alter table <表名> drop column <字段名称>;

#利用alter在数据表中修改已有表结构字段（数据类型 约束与属性）
mysql > alter table <表名> modify column <字段名称> <数据类型> <约束与属性> [comment '注释'] [选项参数];

#利用alter在数据表中修改已有表结构字段（字段名称 数据类型 约束与属性）
mysql > alter table <表名> change column <旧字段名称> <新字段名称> <数据类型> <约束与属性> [comment '注释'] [选项参数];

#利用alter在数据表中删除已有表结构字段（约束与属性）
mysql > alter table <表名> drop index <字段名称> ;

具体实际操作过程（添加新的表结构字段）

# 创建数据库db14
mysql> create database db14;

# 创建数据表信息
mysql > create table db14.stu3(
id int(10) not null,
name varchar(20) not null,
age tinyint(2)  NOT NULL default '0',
dept varchar(16)  default NULL
);

#在学生表中，添加新的表结构字段列（追加字段列-单列操作）
mysql > alter table db14.stu3 add column telno char(11) not null unique key comment '手机号';

#在学生表中，添加新的表结构字段列（插入字段列-单列操作）
mysql > alter table db14.stu3 add column wechat varchar(64) not null unique key comment '微信号' after age;

#在学生表中，添加新的表结构字段列（插入首行列-单列操作）
mysql > alter table db14.stu3 add column sid int not null unique key comment '微信号' first;

#查看表结构字段信息变化
mysql> desc db14.stu3;
+--------+----------------------------+------+-----+---------+----------------+
| Field  | Type                       | Null | Key | Default | Extra          |
+--------+----------------------------+------+-----+---------+----------------+
| sid    | int                        | NO   | UNI | NULL    |                |
| id     | int                        | NO   | PRI | NULL    | auto_increment |
| name   | varchar(45)                | NO   |     | NULL    |                |
| age    | tinyint unsigned           | YES  |     | 18      |                |
| wechat | varchar(64)                | NO   | UNI | NULL    |                |
| gender | enum('男','女','其他')     | YES  |     | 其他    |                |
| telno  | char(11)                   | NO   | UNI | NULL    |                |
+--------+----------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

具体实际操作过程（删除已有表结构字段）

#在学生表中，删除字段sid
mysql > alter table db14.stu3 drop column sid;

具体实际操作过程（修改已有表结构字段）

#在学生表中，修改已有表结构字段列（修改表结构数据类型）
mysql > alter table db14.stu3 modify name varchar(64);

#在学生表中，修改已有表结构字段列，最后带有保持原有配置的属性信息，否则其他属性信息会被还原为默认
mysql > alter table db14.stu3 modify name varchar(64) not null comment '学生名';

#在学生表中，修改已有表结构字段列（修改表结构字段名称）
mysql > alter table db14.stu3 change name stuname varchar(64) not null comment '学生名';
或者
mysql > alter table db14.stu3 change column name stuname varchar(64) not null comment '学生名';

#在学生表中，修改已有表结构字段列（修改表结构属性信息）了解即可
mysql > alter table db14.stu3 modify name varchar(64) not null unique comment '学生名称';

#在学生表中，修改已有表结构字段列（删除表结构属性信息）了解即可
mysql > alter table db14.stu3 drop index `name`;

#查看表结构字段信息变化
mysql > desc db14.stu3;

4、删除数据表信息

# 数据表删除命令语法
mysql > drop table <表名>；

# 删除操作过程，会将定义的表结构和表中数据内容一并删除
mysql > drop table db14.stu3;

# 删除操作过程，只是清空表中数据内容，但保留定义的表结构信息
mysql > truncate table db14.stu3;

数据定义语句定义数据表规范说明：

创建数据表名称规范：要和业务有关（含库前缀），不要有大写字母，不要数字开头，不要含有系统关键字信息，名称不要太长；
创建数据表属性规范：属性信息显示设置，引擎选择InnoDB，字符集选择utf8/utf8mb4，表信息添加注释；
创建数据列属性规范：名称要有意义，不要含有系统关键字信息，名称不要太长；
创建数据类型的规范：数据类型选择合适的、足够的、简短的；
创建数据约束的规范：每个表中必须都要有主键，最好是和业务无关列，实现自增功能，建议每个列都非空（避免索引失效）/加注释
删除数据表操作规范：对于普通用户不能具有删表操作，需要操作删表时需要严格审核
修改数据表结构规范：在数据库8.0之前，修改数据表结构需要在业务不繁忙时进行，否则会产生严重的锁

如果出现紧急修改表结构信息需求时，可以使用工具进行调整，比如使用：pt-osc、gh-ost，从而降低对业务影响

企业数据库面试题目分析练习：

01 请查看以下建表语句给出规范和优化建议：(物流公司日常工作表信息)

create table 't_area_distribution_cost' (
    'id' bigint(20) not null auto_increment comment '主键',
    'city_id' varchar(200),
    'city_name' varchar(200),
    'warehouse_id' varchar(200),
    'warehouse_name' varchar(200)  ,
    'station_region_id' varchar(200),
    'station_region_name' varchar(200),
    'replenish_type' varchar(200),
    'distribution_cost' varchar(200),
    'c_t' varchar(200) default '0' comment '创建时间',
    'create_user' varchar(200) default '0' comment '创建人ID',
    'creater' varchar(200) comment '创建人',
    'u_t' varchar(200) default '0' comment '修改时间',
    'update_user' varchar(200) default '0' comment '修改人ID',
    'updater' varchar(200),
    'is_deleted' varchar(200) comment '删除标记(1 ，删除；0，不删除，有效)',
    primary key ('id'),
    key 'i_abc_city_id' ('city_id') comment '城市ID索引',
    key 'i_abc_warehouse_id' ('warehouse_id'),
    key 'i_abc_station_region_id' ('station_region_id')
) ENGINE=innodb default charset=utf8 comment='区域配送运费设置';

问题解答分析：

# 修改建议01：表明信息略长可以进行调整
create table 't_area_distribution_cost'

# 修改建议02：数据类型信息设定尽量合适
'city_id' varchar(200),
'city_name' varchar(200),

# 修改建议03：定义索引信息没有设置非空
'city_id' varchar(200),
'warehouse_id' varchar(200),
'station_region_id' varchar(200),

# 修改建议04：表中字段列信息可以加注释
 'city_id' varchar(200),
'city_name' varchar(200),
'warehouse_id' varchar(200),
'warehouse_name' varchar(200)  ,
'station_region_id' varchar(200),

02 研发同学需要紧急上线，需要DBA审核SQL，请问以下语句需要如何评估后上线执行，请写审核SQL要点

alter table t_enter_cooperate_info add account_day  INT not null default 0 comment '账期天数',
alter table t_enter_cooperate_info add account_detay_day INT not null default 0 comment '账期付款天数',
alter table t_pop_basic add account_day INT not null default 0 comment '账期天数',
alter table t_pop_basic add account_detay_day INT not null default 0 comment '账期付款天数'

问题解答分析：

本身语句是没有任何问题的，但需要说明，尽量业务繁忙时不要进行发布，选择夜里业务不繁忙时进行发布；

在进行SQL语句信息审核时，需要了解SQL语句的含义和作用：

alter table t_enter_cooperate_info add account_day  INT not null default 0 comment '账期天数',
alter table t_enter_cooperate_info add account_detay_day INT not null default 0 comment '账期付款天数',
-- 表示在t_enter_cooperate_info表中 添加两列信息 并设置相应属性和注释信息

alter table t_pop_basic add account_day INT not null default 0 comment '账期天数',
alter table t_pop_basic add account_detay_day INT not null default 0 comment '账期付款天数'
-- 表示在t_pop_basic add表中 添加两列信息 并设置相应属性和注释信息

通过对以上SQL语句信息解读，可以看出语句操作属于DDL操作，线上操作DDL语句可能会产生比较严重的锁进制等待（死锁问题）；

可以结合企业的数据业务存储的负载压力（TPS），可能当前时间段的TPS数值较高，原则上不建议进行线上操作；

但是考虑到业务需求的紧急情况，建议使用PT-osc工具进行数据库线上操作，减少对线上业务的影响，但不能提高操作效率；

评论区