MySQL创建和管理表

完整的数据存储过程：

本文主要涉及到存储数据的容器（数据库、表），不涉及到具体的数据。 需要清楚的下面的操作都是需要有相应的权限，在练习中我们一般以root用户进行，所以都可以进行，但实际开发中需要注意。

1. 创建与管理数据表

1.1 创建数据库

CREATE DATABASE 数据库名
-- 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集
-- 保证不会出错
CREATE DATABASE IF NOT EXISTS 数据库名
1
2
3
4
5

1.2 使用数据库

--查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
-- 查看指定库下所有的表
SHOW TABLES FROM 数据库名;
-- 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
1
2
3
4
5
6
7

1.3 修改数据库

-- 更改数据库字符集gbk、utf8等
ALTER DATABASE 数据库名 CHARACTER SET 字符集;
-- 删除指定的数据库
DROP DATABASE 数据库名;
-- 不会报错的删除方式
DROP DATABASE IF EXISTS 数据库名;
1
2
3
4
5
6

2. 创建与管理表

方式1 白手起家

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);
1
2
3
4
5
6
7

注意事项：
必须指定：
表名
列名(或字段名)，数据类型，长度
可选指定：
约束条件
默认值
例子：

CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);
1
2
3
4
5
6
7
8

其中的INT(2)，这里的2表示int类型的显示宽度，默认的宽度是11。在MySQL 8.x版本中，不再推荐为INT类型指定显示长度

方式2 借助已存在的表

CREATE TABLE emp1 AS SELECT * FROM employees;
-- 创建空表，只保留字段但没有数据
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

-- 查看创建表的结构
SHOW CREATE TABLE 表名;
1
2
3
4
5
6
7
8
9
10
11
12

3. 修改表

3.1对列（属性）的修改

3.1.1追加一个列

关键字：ADD

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;
--
ALTER TABLE dept80
ADD job_id varchar(15);
1
2
3
4

默认追加在最后一列。

3.1.2修改一个列

关键词：MODIFY
可以修改列的数据类型，长度、默认值和位置

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名
2】;
--
ALTER TABLE dept80
MODIFY salary double(9,2) default 1000;
1
2
3
4
5

数据库我们不轻易修改，例如最简单的修改数据库名字的做法其实是将旧的数据库中的数据拷贝到新的数据库中，再将旧的数据库删除。

3.1.3重命名一个列

关键词：CHANGE

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;
--
ALTER TABLE dept80
CHANGE department_name dept_name varchar(15);
1
2
3
4

3.1.4删除一个列

关键词：DROP

ALTER TABLE 表名 DROP 【COLUMN】字段名
--
ALTER TABLE dept80
DROP COLUMN job_id;
1
2
3
4

3.2 表的修改

3.2.1 重命名表

RENAME TABLE emp
TO myemp;
--
ALTER table dept
RENAME [TO] detail_dept; -- [TO]可以省略
1
2
3
4
5

3.2.2删除表

在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
--
1
2

3.2.3清空表

TRUNCATE TABLE语句：
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

TRUNCATE TABLE detail_dept;

--
SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2
1
2
3
4
5
6
7
8
9

3.2.3 注意事项

表删除 操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信
息提示，因此执行删除操时应当慎重。在删除表前，最好对表中的数据进行 备份 ，这样当操作失误时可
以对数据进行恢复，以免造成无法挽回的后果。
同样的，在使用 ALTER TABLE 进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进
行完整的 备份 ，因为数据库的改变是 无法撤销 的，如果添加了一个不需要的字段，可以将其删除；相
同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

后记

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚 。多个操作同时执行时，一个操作不行，直接回滚到最初状态。