【MySQL】20-MySQL如何创建和管理表超详细汇总

1. 创建表

创建表有两种方式。需要用户具备创建表的权限。

1.方式一

CREATE TABLE IF NOT EXISTS 表名(
字段1名称 字段1数据类型,
字段2名称 字段2数据类型,
...
);

举个例子：

要在数据库 atguigudb 下，创建名为 myemp1 的表。包含三个字段，分别是 INT 类型的 id ，VARCHAR 类型的 emp_name (占15个长度) ，以及 DATE 类型的 hire_date 。

CREATE TABLE IF NOT EXISTS myemp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE
);

可以通过下列语句查看表结构：

DESC myemp1;

输出：

SHOW CREATE TABLE myemp1;

输出：

【注意】

如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集。

2.方式二

方式二是基于现有的表去创建新的表，同时导入数据。

CREATE TABLE 表名
AS
SELECT 现有的表的字段
FROM 现有的表;

举个栗子 ：

我要从已有的表 employees 选择 employee_id 、last_name 和 salary 三个字段，创建一个新的表 myemp2 。

CREATE TABLE myemp2
AS
SELECT employee_id, last_name, salary
FROM employees;

查看一下表的结构：

DESC myemp2;

输出：

用此方法创建的新表，其字段的名称、数据类型、属性等与原表是一样的。

注意，此方法创建的新表中，是保留原表的数据的。

SELECT * FROM myemp2;

查询结果：

这意味着，我们可以结合前面章节所讲的 SELECT 丰富的查询语句操作，基于现有的很多表，灵活地创建出不同的新表。

举个例子：

使用多表查询员工表 employees 和部门表 depatments ，创建新表：

CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id, e.last_name l_name, d.department_name dept_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

可以看到，SELECT 字段后使用了别名，那么新表中的字段就会以别名为新名称。

查看新表 myemp3 ：

SELECT * FROM myemp3;

查询结果：

【例子1】创建一个表 employees_copy ，实现对 employees 表的复制，包括表数据。

CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

查询新表 employees_copy ：

SELECT * FROM employees_copy;

查询结果：

把整个表的字段结构、数据内容都复制过来了。对此表的增删改查不影响原来的 employees 表。

【例子2】创建一个表 employees_blank ，实现对 employees 表的复制，不包括表数据。

这题看起来很难，其实新表中的数据是复制 SELECT 查询语句的结果集。那么要想只复制字段，不复制内容，只需要在 SELECT 语句上的 WHERE 过滤条件写上绝对是 FALSE 的条件 (如 1=2 ) ，让查询结果返回空即可。

CREATE TABLE employees_blank
AS
SELECT *
FROM employees
WHERE 1 = 2;

查询新表 employees_blank ：

SELECT * FROM employees_blank;

查询结果：

DESC employees_blank;

查询结果：

2. 修改表

修改表主要靠关键字 ALTER 实现。下面以 myemp1 这张表为例讲解。

DESC myemp1;

2.1 添加字段

添加字段的语句为：

ALTER TABLE 表名
ADD 要添加的字段名 字段类型;

举个栗子：

往表 myemp1 中添加一个工资 salary 字段。

ALTER TABLE myemp1
ADD salary DOUBLE(10, 2);

第2行代码，我们声明 salary 为一个 DOUBLE 类型，10 代表该 DOUBLE 类型长度一共为10位，2 代表小数点后保留 2 位有效数字，因此一共有8位整数。

查看字段是否添加成功：

DESC myemp1;

查询结果：

可以看到，默认是添加到表 myemp1 中的最后一个字段。

那如果我想添加到任意一个位置，应该如何操作呢？

要在任意位置添加字段，通过关键字 FIRST 和 AFTER… 来操作。

【例子1】把电话号码 phone_number 添加到表 myemp1 的第一个字段。

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

【例子2】把邮箱 email 添加到表 myemp1 的 id 字段的后面。

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER id;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

2.2 修改字段

通过关键字 MODIFY 可以修改字段的数据类型、长度、默认值 (等讲到约束的时候再讲) 。

但在实际开发中，一般不可能修改字段的数据类型，如果用了一段时间之后才发现字段数据类型定义错误，那将是非常大的工作失误。

在实际开发中，修改长度倒是有可能的。比如一开始定义的长度短了，后面我们可以把它修改为长一点的。

【例子】把表 myemp1 的字段 emp_name 的长度改为 25 的。

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25);

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

2.3 重命名字段

重命名通过关键字 CHANGE 来实现。

【例子】把表 myemp1 中的 salary 字段改为 monthly_salary 。

ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10, 2);

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

改名称的同时，也可以同时把长度也改了。

【例子2】把表 myemp1 中的 email 字段改为 my_email 并将其长度改为50。

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

2.4 删除字段

删除字段使用关键字 DROP COLUMN来实现。

【例子】删除表 myemp1 中的 my_email 字段。

ALTER TABLE myemp1
DROP COLUMN my_email;

查看表 myemp1 的字段结构：

DESC myemp1;

查询结果：

3. 重命名表

重命名表有2种方式，推荐第一种。

• 方式一 (推荐) ：使用 RENAME TABLE … TO … 关键字。

【例子】把表 myemp1 重命名为 myemp11 。

RENAME TABLE myemp1
TO myemp11;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：

• 方式二：使用关键字 ALTER

【例子】把表 myemp2 重命名为 myemp22 。

ALTER TABLE myemp2
RENAME TO myemp22;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：

4. 删除表

删除表通过关键字 DROP TABLE 来实现。不光删除表结构，同时删除表中的数据，释放表所占据的存储空间。

DROP TABLE IF EXISTS 表名;

【例子】删除数据库 atguigudb 中的表 myemp3 。

DROP TABLE IF EXISTS myemp3;

查看数据库 atguigudb 中的表：

SHOW TABLES;

查询结果：

可以看到，表 myemp3 已经被删除了。

【注意】

删除数据库和删除表一样，是不能撤销和回滚的。删除操作一定要慎重！

5. 清空表

清空表通过关键字 TRUNCATE TABLE 来实现。清空表与删除表不同，清空表不删除表的表结构，仅仅清除表中的数据。

TRUNCATE TABLE 表名;

【例子】清空表 employees_copy 中的数据。

TRUNCATE TABLE employees_copy;

查看表 employees_copy 中的数据：

SELECT * FROM employees_copy;

查询结果：

可以看到表结构还保留，但是表结构还在。

6. DCL中COMMIT和ROLLBACK

那么清空表和删除表中的数据到底有什么区别呢？讲清楚这个问题之前，首先来讲一下DCL操作中的提交 COMMIT 和回滚 ROLLBACK 。

COMMIT : 提交数据。一旦执行COMMIT，则数据就被永久地保存在数据库中，意味着数据不能回滚。

ROLLBACK：回滚数据。一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

6.1 TRUNCATE和DELETE FROM对比

相同点：都可以对表中所有数据进行删除，同时保留表结构。

不同点：

• TRUNCATE TABLE：不可回滚的。
• DELETE FROM：数据可以回滚。

【阿里巴巴MySQL开发规范】

TRUNCATE TABLE 比 DELETE 速度更快，占用资源更少。因为前者不需要记录回滚日志，而后者需要一边删除，一边记录日志以备用户后悔进行回滚。

但是，正是由于 TRUNCATE TABLE 无事务且不触发 TRIGGER ，不可回滚的特性，使得在实际开发中有可能造成事故 (例如最核心的用户数据) 丢失。因此在实际开发中，往往更常用 DELETE 来清空表数据。宁愿多占用一点系统资源，也不愿意冒不可回滚的风险。

6.2 DDL和DML的对比

• DDL：DDL操作一旦执行，就不可以回滚。指令 SET AUTOCOMMIT = FALSE 对DDL无效。(因为 DDL 内部有自己的 COMMIT 操作，执行完DDL操作之后，一定会执行一次COMMIT，而此COMMIT不受此指令SET AUTOCOMMIT = FALSE的影响。)
• DML：默认情况下，也是不可回滚的。但是，如果在执行DML前，执行了SET AUTOCOMMIT = FALSE ，则执行的DML操作就可以实现回滚。

【例子1】使用DELETE FROM删除表 myemp3 中的所有数据，再回滚。

① 先提交一下 COMMIT：

COMMIT;

② 查看一下表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

③ 把自动提交设置为 FALSE：

SET autocommit = FALSE;

④ 使用 DELETE FROM 删除表 myemp3 中的所有数据：

DELETE FROM myemp3;

查询结果：

⑤ 回滚数据：

ROLLBACK;

⑥ 再次查询表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

可以看到，删除操作被成功撤销，回滚到最后一次COMMIT之后的版本。这也进一步说明 DELETE FROM 作为 DML 操作是可以回滚的。

【例子2】使用 TRUNCATE TABEL 删除表 myemp3 中的所有数据，但不能回滚。

① 先提交一下 COMMIT：

COMMIT;

② 查看一下表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

③ 把自动提交设置为 FALSE。虽然此语句对 DDL 操作无效，但为了控制变量，还是执行改语句：

SET autocommit = FALSE;

④ 使用 TRUNCATE TABEL 删除表 myemp3 中的所有数据：

TRUNCATE TABLE myemp3;

执行完TRUNCATE 后改语句就自动COMMIT 了，所以回滚 ROLLBACK 到此 。所以删除数据就无法撤销了。

查询结果：

⑤ 回滚数据：

ROLLBACK;

⑥ 再次查询表 myemp3 中的数据：

SELECT * FROM myemp3;

查询结果：

可以看到，就算执行回滚 ROLLBACK 也无法撤销 TRUNCATE TABLE 的删除操作。

【结论】

DDL 操作是不可撤销的。进行DDL操作时要慎之又慎。

7. 阿里巴巴操作规范

7.1 命名规范

• 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

  • 正例：aliyun_admin, rdc_config, level3_name
  • 反例：AliyunAdmin, rdcConfig, level_3_name

• 【强制】禁用保留字。如 desc, range, match, delayed 等。

• 【强制】表必备三个字段：id, gmt_create, gmt_modified 。

  • 说明：其中 id 必为主键，类型为 BIGINT UNSIGNED，单表时自增、步长为 1 。gmt_create 、gmt_modified 的数据类型均为 DATETIME 类型，前者的单词为现在时表示主动式创建，而后者单词是过去分词表示被动式更新。

• 【推荐】表的命名最好遵循 “业务名称_表的作用” 。

  • 正例：alipay_task, force_project, trade_config

• 【推荐】数据库名与应用名称尽量一致。

• 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提示检索速度。

  • 正例：无符号值可以避免存储负数，且扩大了表示范围。

  对象	年龄区间	类型	字节	表示范围
  人	150岁之内	TINYINT UNSIGNED	1	无符号值：0~255
  龟	数百岁	SMALLINT UNSIGNED	2	无符号值：0~65535
  恐龙化石	数千万年	INT UNSIGNED	4	无符号值：0~43亿
  太阳	约50亿年	BIGINT UNSIGNED	8	无符号值：0~$1.84\times 10^{19}$

7.2 清空表和删除表的规范

表删除操作是把表的结构和表中的数据一起删除，并且 MySQL 在执行删除操作时，不会有任何的确认信息提示。因此执行删除操作时应当慎重。在删除表前，最好对表中的数据进行备份。这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份。因为数据库的改变是无法撤销的 (不可回滚的) 。如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将丢失。

7.3 MySQL8新特性：DDL的原子化

【原子化】是什么意思？

答：原子化的概念取自化学中原子是组成物质的最小元素的概念，不可分割 (但其实原子可以进一步分割成夸克，没关系，只是借用一下概念而已) 。关键点在于 ”不可分割“ 。在MySQL中，一个 ”事务“ 常常是由多条操作语句组成的。原子化要求：这一个事务中的所有操作语句，要么全部执行成功，要么如果有一条执行报错，就要回滚到初始状态 (即啥也没发生) 。不允许一部分执行成功，剩下的部分执行失败的情况，这就像原子一样是最小单位不可分割一样。故称为原子化。

在MySQL8之前，DDL 语句是不支持原子化的。而MySQL8之后就新增了 DDL 的原子化。

举个例子：分别在 MySQL5.7 和 MySQL8.0 中创建数据库 mytest ，并创建表 book1 ，然后再执行删除表操作，删除表 book1 和 book2 ，但表 book2 是不存在的，因此该删除语句必定失败报错。我们重点观察在执行失败的情况下，表 book1 是否被删除。

先分别执行创建数据库和表的语句：

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(255)
);

SHOW TABLES;

然后分别观察执行删除表操作的结果：

1.MySQL5.7版本

DROP TABLE book1, book2;

输出：

错误代码： 1051
Unknown table 'mytest.book2'

再次查看数据库中的所有表：

SHOW TABLES;

输出：

2.MySQL8.0版本

DROP TABLE book1, book2;

输出：

错误代码： 1051
Unknown table 'mytest.book2'

再次查看数据库中的所有表：

SHOW TABLES;

输出：

可以看到在MySQL8.0中，事务一旦执行失败，就会回滚。这就是8.0的新特性：DDL 操作的原子化。