前言

表数据是存储在数据库表中的，对数据检索和操作的前提是数据所存储的表已经创建且存在。在日常的研发流程中，对业务层进行梳理整理之后，也需要我们构建数据库模型，创建对应规则的数据库表。

虽然，各式各种的数据库管理工具在数据库管理和表管理上给我们带来很多的便利，让我们不需要在自己手动的去书写语句去执行表的创建、更新等的工作。但是，熟练的表操作依旧是我们应该去扎实的基本功。

这篇文章，是我自己针对这方面学习的一些知识总结。

内容

创建表

CREATE TABLE语句用于创建表。表的创建必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出；

• 表列的名字和定义，用逗号分隔；

除了上述两样必要信息，还可以包含其他的关键字和选项。

    # `CREATE TABLE`常用的定义的关键字和选项
    # table_name: 表格名
    # col_name: 列名
    # data_type: 列的数据类型
    # engine_name: 引擎名称
    CREATE TABLE {IF NOT EXISTS} [table_name] (
        [col_name] [data_type] {NULL | NOT NULL} {DEFAULT [literal | (expr)]}
        {AUTO_INCREMENT} {UNIQUE} {PRIMARY KEY}
    ) ENGINE=[engine_name];
    
    # 例子
    CREATE TABLE customers (
        cust_id        int          NOT NULL AUTO_INCREAMENT,
        cust_name      char(50)     NOT NULL,
        cust_address   char(50)     NULL,
        cust_city      char(50)     NULL,
        cust_state     char(5)      NULL,
        cust_zip       char(10)     NULL,
        cust_country   char(50)     NULL,
        cust_contact   char(50)     NULL,
        cust_email     char(255)    NULL,
        PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
复制代码

表定义的语法中，括号内的是对表格的信息和特征定义，例如：表名、表引擎、临时表等等。而括号内是对表格的列的信息和特征的定义，例如：列名、列数据类型、空值定义等等。

IF NOT EXISTS

CREATE TABLE创建表格需要确保所创建表格的表名不存在在当前数据库中，否则，语句执行将出错。而IF NOT EXISTS能够保证在创建的表名重复时候不发生上面的报错，并且在不存在重复的情况下创建它。

然而，这样使用了IF NOT EXISTS的创建语句并不会检查已有表名是否与打算创建的表模式相匹配。它只是查看表名是否存在，不存在则创建。

NULL和NOT NULL

NULL和NOT NULL关键字定义列对于空值和缺值的判断限制。

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。NOT NULL值的列不接受该列没有值的行，在插入或更新行时，该列必须有值。

DEFAULT

DEFAULT关键字给列指定一个默认值。如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。

PRIMARY KEY

PRIMARY KEY关键字定义列为主键。

主键值必须唯一。表中的每个行必须具有唯一的主键值。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

AUTO_INCREMENT

AUTO_INCREMENT关键字定义整数和浮点数类型列在插入新数据时候的更新规则。

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动新增。每次执行一个INSERT操作时，自动对该列增量，给该列赋予下一个可用的值。

每个表只允许一个AUTO_INCREMENT列，这个列必须被索引，并且不能包含DEFAULT默认值。

在插入新数据后，可以通过使用last_insert_id()函数可以获得这个自增的列的值。

    SELECT last_insert_id();
复制代码

ENGINE=[engine_name]引擎类型

ENGINE=[engine_name]语句定义表格数据存储数据所使用的存储引擎。[engine_name]指定一个MySQL支持的引擎类型名称。不同的存储引擎，在存储性能、安全性、完整性上都具有自己的特点，适合不同的使用场景。

以下是几个常用的需要了解的引擎：

• innoDB是一个具有可靠的事务处理、行锁定和外键的引擎；

• MyISAM是一个性能极高的引擎，支持全文本搜索，但是不支持事务处理；

• MEMORY在功能上等同于MyISAM，但由于数据存储在内存而不是磁盘中，因此速度很快，特别适用于临时表；

更新表

表结构的设计是为了业务服务的，业务是不断发展并且提出更多业务需求的。而向原先的表结构提出更多的业务需求，也是必然之势，表结构不可能一成不变。

ALTER TABLE语法用于更新指定表的定义以及表的列定义。使用这个语法更改表结构，需要给出所要更改的表名、所做更改的表列这两项信息。

常见的表结构的更新大体上都是对表列的新增、删除和对表列的定义的更新。

    # 新增列
    ALTER TABLE [table_name] ADD [column_name] [data_type] [column_definition];
    
    # 删除列
    ALTER TABLE [table_name] DROP COLUMN [column_name];
    
    # 定义外键
    ALTER TABLE [table_name]
    ADD CONSTRAINT [foreign_key_id]
    FOREIGN KEY ([foreign_column_name])
    REFERENCES [referenced_table_name] ([referenced_column_name]);
复制代码

面对复杂的表结构和海量的数据量，普通的更新不能保护我们表结构和表数据的安全。因此，这时候的表结构更改一般需要手动删除过程，涉及一下步骤：

• 用新的列布局创建一个新表；

• 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；

• 检验包含所需数据的新表；

• 重命名旧表；

• 用旧表原来的名字重命名新表；

• 根据需要，重新创建触发器、存储过程、索引和外键；

删除表

DROP TABLE语句用删除整个表。表的删除操作没有确认过程，也不能撤销，将永远删除表。

    DROP TABLE [table_name];
复制代码

重命名表

RENAME TABLE语句用于重命名表。

    RENAME TABLE [table_name] TO [another_table_name];
复制代码

总结

表的操作是每个需要使用数据库的人应该了解的内容，即使我们已经有很优秀的数据库管理工具可以使用。

表的创建需要考虑到业务场景的拓展，业务的拓展应该以新增字段来进行业务支持，而复杂的表结构和海量数据的表，应该进行手动的复制和迁移表格，保证数据和应用的安全性。

参考资料

• 《MySQL必知必会》

• MySQL Documentation