SQL 自学笔记（9）创建和管理数据库和数据表

创建和管理数据库和数据表

存储和管理数据需要我们创建并管理数据库以及数据库中的各种表。本文介绍如何创建和管理数据库、数据表。包括了数据库的创建、管理；数据表的创建、修改、命名和删除。

1 数据管理

对数据进行管理，首先要存储数据。一般按照 创建数据库 -> 选择字段 -> 创建数据表 -> 存储数据 的方式进行管理。

1.1 命名规则

• 数据库名、表名不得超过 30 个字符，变量名不得超过 29 个字符
• 只含 A-Z a-z 0-9 共 63 个字符
• 名中不要包含 空格
• 同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 不要使用关键字，不得不使用时使用 `` 框选

1.2 MySQL 中的数据类型

数据类型类别	数据类型	关键字/特点	描述
整数类型	TINYINT	1字节，有符号范围：-128到127，无符号范围：0到255	非常小的整数
	SMALLINT	2字节，有符号范围：-32768到32767，无符号范围：0到65535	小整数
	MEDIUMINT	3字节，有符号范围：-8388608到8388607，无符号范围：0到16777215	中等大小的整数
	INT	4字节，有符号范围：-2147483648到2147483647，无符号范围：0到4294967295	标准整数
	BIGINT	8字节，有符号范围：-2^63到2^63-1，无符号范围：0到2^64-1	大整数
浮点数类型	FLOAT	4字节，单精度浮点数	单精度浮点数，精度约为7位小数
	DOUBLE	8字节，双精度浮点数	双精度浮点数，精度约为15位小数
	DECIMAL(M, D)	定点数，M为总位数，D为小数位数	精确小数，适用于财务计算
日期时间类型	DATE	格式：‘YYYY-MM-DD’	日期值，仅包含日期
	TIME	格式：‘HH:MM:SS’	时间值，仅包含时间
	DATETIME	格式：‘YYYY-MM-DD HH:MM:SS’	日期和时间值
	TIMESTAMP	格式：‘YYYY-MM-DD HH:MM:SS’，范围：‘1970-01-01 00:00:01’到’2038-01-19 03:14:07’	时间戳，自动更新
	YEAR	格式：‘YYYY’	年份值
字符串类型	CHAR(M)	固定长度字符串，M为字符数，最大255	定长字符串，适合存储固定长度的数据
	VARCHAR(M)	可变长度字符串，M为最大字符数，最大65535	变长字符串，适合存储长度不固定的数据
	TINYTEXT	最大长度255字符	非常小的文本字符串
	TEXT	最大长度65535字符	文本字符串
	MEDIUMTEXT	最大长度16777215字符	中等长度的文本字符串
	LONGTEXT	最大长度4294967295字符	长文本字符串
	BINARY(M)	固定长度二进制字符串，M为字节数，最大255	定长二进制字符串
	VARBINARY(M)	可变长度二进制字符串，M为最大字节数，最大65535	变长二进制字符串
枚举类型	ENUM('val1', 'val2', ...)	枚举类型，只能存储列出的值之一	枚举类型，适合存储有限个数的可能值
集合类型	SET('val1', 'val2', ...)	集合类型，可以存储多个列出的值	集合类型，适合存储多个可能值
二进制大对象	TINYBLOB	最大长度255字节	非常小的二进制对象
	BLOB	最大长度65535字节	二进制大对象
	MEDIUMBLOB	最大长度16777215字节	中等大小的二进制对象
	LONGBLOB	最大长度4294967295字节	长二进制对象
空间数据类型	GEOMETRY	空间数据类型，存储几何对象	几何对象
	POINT	点类型	二维平面上的点
	LINESTRING	线类型	二维平面上的线
	POLYGON	多边形类型	二维平面上的多边形
	MULTIPOINT	多点类型	多个点的集合
	MULTILINESTRING	多线类型	多条线的集合
	MULTIPOLYGON	多多边形类型	多个多边形的集合
	GEOMETRYCOLLECTION	几何集合类型	多个几何对象的集合
JSON类型	JSON	存储JSON格式数据	用于存储JSON格式的数据

以上由 DeepSeek 总结 ，并不重要，现实使用时可以查询官方文档。

2 创建和管理数据库

2.1 创建数据库

• 方法1： 直接创建，完全默认

CREATE DATABASE 数据库名;

• 方法2：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

可以指定数据库的字符集（如 utf8mb4 ），确保数据存储时使用正确的编码。

• 方法3【推荐】：先判断是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

注意：数据库不能改名！

如果需要重命名，则需要：创建新数据库；复制原数据库数据导入新库；删除旧库。

2.2 使用数据库

• 查看所有数据库

SHOW DATABASES;

• 查看当前使用的数据库

SELECT DATABASE();

未使用数据库时返回 NULL

• 查看数据库的所有表

SHOW TABLES [FROM 数据库名];

若省略 FROM 数据库名 则查看当前使用的数据库

• 查看数据库创建时的相关信息

SHOW CREATE DATABASE 数据库名;

• 使用某个数据库

USE 数据库名;

因为数据表均存储在不同数据库下，所以在对表操作前，必须先指定使用某个数据库。

2.3 更改字符集

虽然更改数据库名理论上是不可行，不推荐的。但修改字符集非常简单，只需一行指令即可。

ALTER DATABASE 数据库名 CHARACTER SET 字符集; -- e.g. : gbk, utf8

2.4 删除数据库

DROP DATABASE IF EXISTS 数据库名;

其中 IF EXISTS 先判断是否存在，存在则删除。可以省略，但【不推荐】。

3 创建表

在数据库创建完成后，进入/使用指定的数据库，而后就可以开始创建和管理数据表。

3.1 CREATE TABLE 创建新的数据表

格式

CREATE TABLE [IF NOT EXISTS] 表名
(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	...
	[表约束条件]
);
-- [] 表示可省略

例如：不做约束

CREATE TABLE emp
(
    emp_id   INT,
    emp_name VARCHAR(20), -- 20 为长度
    salary   DOUBLE,
    birthday DATE
);

我们可以查看一下表：

DESC emp;

例如：增加约束条件，指定自增性质，指定表的主键【推荐指定主键】

CREATE TABLE dept
(
    dept_id   INT AUTO_INCREMENT, -- 增加约束条件: 自增
    dept_name VARCHAR(14),
    location  VARCHAR(13),
    PRIMARY KEY (dept_id)         -- 表的约束条件: 指定 deptno 为主键
);

同样，我们可以查看一下表：

DESC dept;

3.2 AS 其他表名 创建表同时导入数据

使用 CREATE TABLE 新表 AS 其他表的数据 在创建新表的同时，插入数据。

格式

CREATE TABLE 新表
(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	...
	[表约束条件]
)
AS
SELECT 列名1, 列名2, ...
FROM 其他表
[WHERE 条件];

其中，指定新表属性和字段的 (字段1, ..., 表约束条件]) 部分可以省略。如果不省略，则要满足后面填充数据时的匹配。即 SELECT 语句找出的数据和新表的列要一一对应。

例如：选取数据库 atguigudb 的 employees 表中收入高于 13000 的员工的部分信息。

CREATE TABLE emp_high_salary
AS
SELECT e.employee_id, e.last_name, e.department_name, e.salary, e.hire_date
FROM atguigudb.employees e
WHERE e.salary > 13000;

查看这个新表的信息，继承了表的信息（执行 DESC emp_high_salary; ）

查看具体表的内容，发现已经填充了数据

SELECT *
FROM test.emp_high_salary;

3.3 查看数据表结构

除了上面的采用 DESC 的方式查看表

DESC 表名;

也可以采用 SHOW CREATE TABLE 查看

SHOW CREATE TABLE 表名;

SHOW CREATE TABLE 查看的表结构更为详细

4 修改表

使用 ALTER TABLE 语句修改表的结构

4.1 新增一列 ADD

格式

ALTER TABLE 表名 
	ADD 字段名 字段类型 
		[FIRST|AFTER 字段名];
-- [] 代表可选
-- A|B 代表或者, A 或 B

例如：在表 emp 中新增一列 job_id 使得其在字段 salary 的后面

ALTER TABLE emp
    ADD job_id varchar(15)
        AFTER salary;

4.2 修改一列 MODIFY

可以修改列的 数据类型 长度 默认值 位置

格式

ALTER TABLE 表名 
	MODIFY 字段名1 字段类型 [DEFAULT 默认值] 
		[FIRST|AFTER 字段名2];

例如：修改表 emp_high_salary 的 salary 的数据类型和默认值，并放置在 last_name 后

ALTER TABLE emp_high_salary
    MODIFY salary double(9, 2) DEFAULT 1000
        AFTER last_name;

默认值的修改只对以后存储的新数据有影响

4.3 对列重命名 CHANGE

格式

ALTER TABLE 表名 
	CHANGE 列名 新列名 新数据类型;

例如：将表 emp_high_salary 的 department_name 改为 dept_name

ALTER TABLE emp_high_salary
    CHANGE department_name dept_name varchar(15);

4.4 删除某列 DROP

格式

ALTER TABLE 表名 
	DROP 字段名;

例如：删除之前在表 emp 中新增的 job_id

ALTER TABLE emp
    DROP job_id;

5 重命名表

5.1 RENAME 方法

使用 RENAME ... TO 方法重命名表，格式为

RENAME TABLE 旧表名
	TO 新表名;

例如：更改表 emp 为新名字 employee_info

RENAME TABLE emp
    TO employee_info;

5.2 ALTER RENAME 方法

格式

ALTER table 旧表名
    RENAME [TO] 新表名;

例如：

ALTER table employee_info
    RENAME emp;

6 删除表

当当前表与其他表没有关联时，例如没有外键链接时，可以直接删除。格式为

DROP TABLE [IF EXISTS] 表名;

例如：删除之前常见的 emp_high_salary 表

DROP TABLE IF EXISTS emp_high_salary;

7 清空表内数据

只是删除表内的数据，并不删除表结构。

7.1 TRUNCATE 语句

格式

TRUNCATE TABLE 表名;

7.2 DELETE 语句

格式

DELETE TABLE 表名;

7.3 是否回滚 ROLLBACK

DELETE 支持回滚 ROLLBACK ，而 TRUNCATE 不支持。

例如：先查看原始表内容

mysql> SELECT * FROM emp_high_salary;
+-------------+-----------+-----------------+----------+------------+
| employee_id | last_name | department_name | salary   | hire_date  |
+-------------+-----------+-----------------+----------+------------+
|         100 | King      | Executive       | 24000.00 | 1987-06-17 |
|         101 | Kochhar   | Executive       | 17000.00 | 1989-09-21 |
|         102 | De Haan   | Executive       | 17000.00 | 1993-01-13 |
|         145 | Russell   | Sales           | 14000.00 | 1996-10-01 |
|         146 | Partners  | Sales           | 13500.00 | 1997-01-05 |
+-------------+-----------+-----------------+----------+------------+
5 rows in set (0.00 sec)

• 使用 DELETE 删除，然后回滚

-- 开始事务，暂不提交
START TRANSACTION;

START TRANSACTION; ：它标志着一个事务的开始。在事务中，可以执行多个 SQL 语句。在事务结束之前，所有的修改都不会真正生效，直到显式地提交事务（COMMIT;）或回滚事务（ROLLBACK;）

-- DELETE 删除
DELETE
FROM emp_high_salary
WHERE employee_id = 100;

SELECT *
FROM emp_high_salary;
-- 查看结果
+-------------+-----------+-----------------+----------+------------+
| employee_id | last_name | department_name | salary   | hire_date  |
+-------------+-----------+-----------------+----------+------------+
|         101 | Kochhar   | Executive       | 17000.00 | 1989-09-21 |
|         102 | De Haan   | Executive       | 17000.00 | 1993-01-13 |
|         145 | Russell   | Sales           | 14000.00 | 1996-10-01 |
|         146 | Partners  | Sales           | 13500.00 | 1997-01-05 |
+-------------+-----------+-----------------+----------+------------+
4 rows in set (0.00 sec)

-- 回滚
ROLLBACK;

SELECT *
FROM emp_high_salary; -- 查看结果
+-------------+-----------+-----------------+----------+------------+
| employee_id | last_name | department_name | salary   | hire_date  |
+-------------+-----------+-----------------+----------+------------+
|         100 | King      | Executive       | 24000.00 | 1987-06-17 |
|         101 | Kochhar   | Executive       | 17000.00 | 1989-09-21 |
|         102 | De Haan   | Executive       | 17000.00 | 1993-01-13 |
|         145 | Russell   | Sales           | 14000.00 | 1996-10-01 |
|         146 | Partners  | Sales           | 13500.00 | 1997-01-05 |
+-------------+-----------+-----------------+----------+------------+
5 rows in set (0.00 sec)

此时 ROLLBACK; 回滚事务结束，结果被提交给数据库，数据完成所有操作，显示最终结果。

• 但是 TRUNCATE 不支持回滚

TRUNCATE TABLE emp_high_salary;
-- 执行后，表中所有数据被清空，无法回滚

mysql> SELECT * FROM emp_high_salary;
Empty set (0.00 sec)

清除数据而已，表结构仍然存在

8 注意事项

• 在第 6 和第 7 部分：删除表和清除表数据需要十分谨慎。【建议先备份】然后进行删除。

• 除此之外，为了避免误操作，MySQL8.0 推出了 DDL（Data Definition Language）原子化 ，即操作要么成功要么失败，绝对不会出现部分成功的情况。

例如：在删除表时，如果 表1 存在，而 表2 不存在。此时同时删除 表1 和 表2 的操作会完全失败，不会出现 表1 被删除了，而 表2 不存在报错的情况。