下载掌阅APP，畅读海量书库
立即打开
有了动物园数据库zoo，接下来我们开始学习如何创建表（Table）。MySQL的表是存储数据的基本单位，主要用来对数据进行分类，例如动物信息存放在动物表，饲养员信息存放在员工表等。一个表包含若干列（字段），每列（字段）有名称和数据类型。同时，表需要指定主题来唯一标识每一行。
我们可以对表进行各种操作，如增、删、改、查等。
有以下两种常用的方法可以创建表：
01 在客户端单击zoo数据库，若是Workbench，则右击zoo数据库，在弹出的快捷菜单中选择【Create Table...】命令；若是DataGrip，则右击zoo数据库，在弹出的快捷菜单中依次选择【New】|【Table】命令。
02 在弹出来的页面中填写表名、字段名称、字段类型等信息，如图2-7和图2-8所示。
03 所有信息填写完成后，在Workbench中单击【Apply】按钮，在DataGrip中单击【Execute】按钮。
不同的字段（Column）用于存储不同的信息，比如name字段用于存储动物名称，age字段用于存储动物年龄，description字段用于存储动物详细介绍，create_time字段用于存储数据创建时间，update_time字段用于存储数据更新时间，is_delete字段用于存储数据是否被删除（0表示未被删除，1表示已被删除）。
图2-7 使用Workbench创建动物表animal
图2-8 使用DataGrip创建动物表animal
字段还有类型，例如int表示整型，varchar表示字符串类型，datetime表示日期时间类型等。
最后是id字段，id字段是主键且不能为空（勾选【Not Null】复选框表示该字段必填）。所谓主键就是用来唯一标识一行的字段。字段类型、主键等内容在后续章节会详细介绍。
使用SQL语句创建动物表animal，具体的SQL语句如下：
-- 注释，zoo是动物园数据库
create table 'zoo'.'animal' (
`id` int not null comment '主键',
`name` varchar(50) not null comment '动物名称',
`age` smallint not null comment '年龄',
`description` text null comment '详细介绍',
`create_time` datetime not null comment '创建时间',
`update_time` datetime not null comment '更新时间',
`is_delete` tinyint not null comment '是否删除，1 表示已删除，0 表示未删除',
primary key (`id`));
使用“create table `zoo`.`animal`”表示在zoo数据库下创建animal表。SQL语句创建表与第一种方式一样，需要指定字段类型、主键、字段备注等信息。
这里简单分析其中一个字段，其他字段类似：
-- `id`字段分析
`id` int not null comment '主键',
说明：
注意 每个字段定义完成后，需要用英文逗号（,）作为结尾，否则SQL执行后会提示异常。create语句有非常详细的语法，语法一旦错误，MySQL就无法让SQL执行通过。关于create语句详细的语法内容，读者可以参考MySQL官方文档（https://dev.mysql.com/doc/refman/8.0/en/create-table.html）。
有了动物表之后，下一步我们往表里插入数据，插入数据的方法也有很多，这里列举在工作中程序员经常使用的插入数据的方法。
insert语句的语法如下：
insert into [数据库名].[表名](字段1，字段2，... ) values (字段值1，字段值2，...);
示例：
insert into `zoo`.`animal`(id, name, age, description, create_time, update_time,
is_delete)
values (1,'东北虎','3','东北虎：是猫科、豹属动物。是虎的亚种之一。','2022-12-01 00:00:00',
'2022-12-01 00:00:00',0);
其中，表的字段可以省略：
insert into `zoo`.`animal` values (1,'东北虎','3','东北虎：是猫科、豹属动物。是虎的亚
种之一。','2022-12-01 00:00:00', '2022-12-01 00:00:00',0);
字段和字段值必须一一对应，例如，description字段不是必填字段，若我们不想插入description字段的值，则可以使用如下语句：
insert into `zoo`.`animal`(id, name, age, create_time, update_time, is_delete)
values (2,'熊猫','1','2022-12-01 00:00:00', '2022-12-01 00:00:00',0);
使用
（编辑当前行）、
（插入新行）、
（删除选中行）按钮来编辑数据，具体如图2-9所示，操作十分简单，这里不再详细描述。
使用+（添加行）按钮和－（删除行）按钮来编辑数据，如果要修改数据，就直接单击要修改的数据进行编辑即可，如图2-10所示。
图2-9 使用Workbench编辑数据
图2-10 使用DataGrip编辑数据
全世界的动物种类繁多，比如中国最大的动物园长隆野生动物园拥有500多种动物，如果每种动物都通过手工方式来录入数据库，显然是不合适的，一般都是先整理到Excel表格或整理为CSV格式的数据，再通过DataGrip客户端批量导入数据库。我们来看看如何用DataGrip将CSV格式的数据导入到数据库，具体步骤如下：
01 准备CSV格式数据，数据之间使用逗号分隔，如图2-11所示。
02 在DataGrip客户端中右击animal表，在弹出的快捷菜单中选择Import Data from File命令，选择animal.csv文件进行导入，如图2-12所示。
图2-11 CSV格式动物数据
图2-12 通过DataGrip导入CSV格式数据
注意 如果使用Workbench导入CSV格式的数据，那么会出现各式各样的问题，这也是推荐读者使用DataGrip客户端的原因。
无规矩不成方圆。在企业中，公司要么定义自己的建表规范，要么参考大厂的建表规范，比较有名的就是《阿里巴巴Java开发手册》。该手册是阿里巴巴集团的技术团队的集体智慧结晶和经验总结，经历了多次大规模一线实战的检验并不断进行完善，公开到业界后，众多社区开发者踊跃参与，共同打磨完善，系统化地整理成册。推荐读者把该手册作为工作中的参考词典，在创建表时遵守该手册规约。这里列举几条：
（1）【强制】表达是与否概念的字段必须使用is_xxx的方式进行命名，数据类型是unsigned tinyint（1表示是，0表示否）。
正例：表达逻辑删除的字段名is_deleted，1表示已删除，0表示未删除。
（2）【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下画线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
正例：aliyun_admin、rdc_config、level3_name。反例：AliyunAdmin、rdcConfig、level_3_name。
（3）【强制】表名不使用复数名词。
（4）【强制】主键索引名为pk_字段名，唯一索引名为uk_字段名，普通索引名则为idx_字段名。说明：pk_即primary key，uk_即unique key，idx_即index。
（5）【强制】表必备三字段：id、create_time、update_time。
（6）【推荐】库名与应用名称尽量一致。
（7）【推荐】表的命名最好遵循“业务名称_表的作用”。
（8）【参考】合适的字符存储长度不但能节约数据库表空间、节约索引存储，更重要的是能提升检索速度。
注意 这里只是列举了几条，其中【强制】代表必须遵守的规约，更多内容请读者仔细阅读《阿里巴巴Java开发手册》。
有时创建的表和数据并非我们所期望的，这时就会涉及对表和数据的删除操作，日常工作中常用的几种删除方式如下。
语法如下：
drop table table1,table2 ...
示例：删除表animal_1。
drop table animal_1;
可以同时删除多张表，例如，删除表animal_1和animal_2：
drop table animal_1,animal_2;
警告 drop语句会删除表定义和所有表数据。如果表已分区，则该语句将删除表定义、其所有分区、存储在这些分区中的所有数据以及与删除的表关联的所有分区定义。drop语句删除表时也会删除该表的所有触发器。因此，在工作中，使用drop删除表要特别慎重。
提示 为防止删除表造成不可挽回的损失，也可以对表进行复制，方法是：在DataGrip客户端中选中表animal，右击，在弹出的快捷菜单中选择Copy table to...命令，对animal表进行复制，同时重命名为animal_1、animal_2。
如果不想删除表结构，只想删除表中的数据，可以使用delete语句。语法如下：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
语法很长，读者只需要关注加粗语法即可。
示例：
-- 删除animal_1表中所有数据
delete from animal_1;
-- 删除animal_1表中id=1的数据
delete from animal_1 where id = 1;
如果没有指定where子句，MySQL表中的所有记录将被删除。可以在where子句中指定任何条件。
truncate语句的语法很简单，语法如下：
truncate [table] tbl_name
truncate table可以完全清空表数据。从逻辑上讲，truncate table类似于删除所有行的delete语句，或一系列drop table和create table语句。
上述3种删除方法的区别如表2-1所示。
表2-1 3种删除方法的区别
工作中如果发现创建的表字段名、字段类型、字段注释等信息有问题，那么在测试环境下一般采用MySQL客户端工具进行修改；如果是正式环境，则需要提交SQL语句进行修改。因此，这两种方式读者都需要掌握。
使用DataGrip工具，右击需要修改的表，在弹出的快捷菜单中选择Modify Table...命令，根据需要调整表和表结构信息即可，如图2-13所示。
图2-13 通过DataGrip修改表和表结构
MySQL修改表语句的定义非常长，具体如下：
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX | KEY} [index_name]
[index_type] (key_part,...) [index_option] ...
| ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
(key_part,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
| DROP {CHECK | CONSTRAINT} symbol
| ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
| ALTER [COLUMN] col_name {
SET DEFAULT {literal | (expr)}
| SET {VISIBLE | INVISIBLE}
| DROP DEFAULT
}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
...此处省略多页代码
关于ALTER TABLE的更多内容，可参考MySQL官方文档定义（https://dev.mysql.com/doc/refman/8.0/en/alter-table.html）。由于ALTER TABLE内容很多，涉及的知识点也很多，因此这里先挑几点工作中常用的进行讲解。
如果表的名称或者备注不合理，那么可以通过如下语句进行修改：
-- rename 原表 to 目标表，可以重命名表名称
rename table animal to animal_m;
-- 修改表备注信息
alter table animal comment '动物表';
以2.2.1节的animal表为例：
-- auto-generated definition
create table animal
(
id int not null comment '主键' primary key,
name varchar(50) not null comment '动物名称',
age smallint not null comment '年龄',
description text null comment '详细介绍',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '更新时间',
is_delete tinyint not null comment '是否删除，1 表示已删除，0 表示未删除'
)
假如想添加英文名字段en_name来存储动物的英文名称，比如猫-Cat、老虎-tiger，则具体SQL语句如下：
-- animal表添加en_name字段
alter table animal add en_name varchar(255) null comment '英文名';
说明：
如果想同时给表增加多个字段，则可以在alter table语句中使用多个add子句，以逗号分隔，具体示例如下：
-- 同时添加多个字段
alter table animal
add kingdom varchar(64) null comment '界',
add phylum varchar(64) null comment '门';
删除表字段的示例如下：
-- 删除字段en_name
alter table animal drop column en_name;
-- 与添加多字段类似，可以同时删除多个字段
alter table animal
drop column kingdom,
drop column phylum;
注意 如果表只包含一列，则不能删除该列。如果打算删除该表，则改用drop table语句。如果从表中删除列，则字段所对应的索引也会被删除。如果删除组合索引的所有列，则索引也会被删除。
读者可以自己练习给animal表添加界（Kingdom）、门（Phylum）、纲（Class）、目（Order）、科（Family）、属（Genus）、种（Species）等字段。
（1）rename子句：
rename可以更改字段名称（列名），但不能更改其定义。示例如下：
-- 重命名en_name为en_name_v2
alter table animal rename column en_name to en_name_v2;
在不更改列定义的情况下，rename比modify更方便。
（2）modify子句：
modify可以更改字段定义（列定义），但不能更改其名称。示例如下：
-- 修改animal表字段长度为255，备注信息为名称
alter table animal modify name varchar(255) not null comment '名称';
在不重命名列定义的情况下，modify比change更方便。
（3）change子句：
change子句可以同时重命名列并更改其定义。示例如下：
-- 可同时修改字段名称和字段定义
alter table animal change en_name_v2 en_name varchar(64) not null comment '英文名';
change比modify或rename有更多的功能，但是也有代价：如果不重命名列，则change需要对列进行两次命名；如果仅重命名列，则change要求重新指定列定义。
仍然以animal表为例：
create table animal
(
id int not null comment '主键' primary key,
name varchar(50) not null comment '动物名称',
age smallint not null comment '年龄',
description text null comment '详细介绍',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '更新时间',
is_delete tinyint not null comment '是否删除，1 表示删除，0 表示未删除',
en_name varchar(255) null comment '英文名'
)
工作中表的字段会很多，有时候显示器不够大会导致有些字段只能通过滚动屏幕才能看清，因此，我们往往会把重要的字段放在前面，不重要的字段放在后面。比如上述animal表，如果我们希望把en_name字段放在name后面，可以使用after子句：
-- 调整en_name顺序，放在name后面
alter table animal modify en_name varchar(255) null comment '英文名' after name;
本节主要讲解表以及表字段的调整（增／删／改表字段以及调整表字段顺序），关于表索引、主键等内容，会在后续的章节进行详细介绍。
之所以要导出表结构或者表数据，是因为研发人员在开发环境研发完毕后，要将这些表和数据部署到测试环境，研发人员不可能在测试环境或者生产环境重新再创建一遍表或者重新初始化表数据。表结构和表数据的导入与导出如图2-14所示。
图2-14 表结构和表数据的导入与导出
可以采取两种方式进行表结构（表数据）的导出：
右击zoo数据库，在弹出的快捷菜单中选择Export With 'mysqldump'命令，在弹出的对话框中填写相应的信息，如图2-15所示。
图2-15 使用DataGrip导出表数据
mysqldump是MySQL的数据备份工具，Path to mysqldump用于配置mysqldump工具的路径；Out path用于配置导出的文件名称和路径；而中间部分，例如Add drop table（配置是否添加drop语句，当表存在时，先删除表再创建表）、Lock tables（配置是否锁表）等用于配置导出的相关的配置，读者可先不用关注。
通过DataGrip客户端调用mysqldump工具进行导出，与直接调用mysqldump工具进行导出本质是一样的，接下来演示如何调用mysqldump工具导出数据。
mysqldump工具可以备份数据，并将数据导出到CVS或者XML格式文件，调用语法如下：
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
示例一： 对单个数据库进行备份。
-- 备份动物园数据库 -u指定用户名，-p指定密码，zoo是数据库名称
-- > backup-zoo.sql 表示导出到backup-zoo.sql文件中
mysqldump -u root -p zoo > backup-zoo.sql
Enter password: 此处输入root用户对应的密码
示例二： 可以使用一个命令转储多个数据库。
-- 导出多个数据库zoo 和 zoo_v2
mysqldump -u root -p --databases zoo zoo_v2 > multi-backup-file.sql
示例三： 导出数据库中的表。
-- 导出zoo数据库的animal表
mysqldump -u root -p zoo animal > back-zoo-single-table.sql
-- 导出zoo数据库的animal和 animal_1表
mysqldump -u root -p zoo animal animal_1 > back-zoo-multi-table.sql
示例四： 导出所有数据库。
-- 导出所有数据库
mysqldump -u root -p --all-databases > all_databases.sql
关于mysqldump工具的更多内容，读者可参考官方文档（https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html）。