【重学 MySQL】四十五、数据库的创建、修改与删除

在 MySQL 中，数据库（也称为 schema）是用于存储和组织数据的一种逻辑结构。数据库的创建、修改和删除是数据库管理的基本操作。

一条数据存储的过程

在数据库系统中，一条数据存储的过程通常涉及多个步骤，从数据的输入到其在数据库中的持久化存储。

数据输入

首先，用户或应用程序需要将数据输入到系统中。这可以通过多种方式实现，例如：

• 用户界面（UI）：用户通过图形界面（如网页表单、桌面应用程序窗口等）输入数据。
• 应用程序接口（API）：外部应用程序通过调用API将数据发送到数据库系统。
• 批量导入：使用文件（如CSV、Excel等）批量导入数据。

数据验证

在数据被存储之前，通常需要进行验证以确保其准确性和完整性。这包括：

• 数据类型检查：确保数据符合预期的格式和类型（如字符串、数字、日期等）。
• 约束检查：验证数据是否满足数据库表定义的约束条件（如主键唯一性、外键关联、非空约束等）。
• 业务逻辑验证：根据应用程序的业务逻辑检查数据的有效性（如年龄必须在0到120岁之间）。

数据处理

如果数据通过了验证，接下来可能需要进行一些处理，例如：

• 数据转换：将数据转换为适合存储的格式（如日期格式转换、数据编码转换等）。
• 数据清洗：去除或纠正数据中的错误、重复或不一致。
• 数据标准化：将数据转换为统一的标准格式。

数据存储

处理后的数据将被存储到数据库中。这通常涉及以下步骤：

• 数据库连接：建立与数据库的连接（如使用数据库驱动程序或ORM框架）。
• SQL语句生成：根据要存储的数据生成相应的SQL语句（如INSERT、UPDATE等）。
• 执行SQL语句：将SQL语句发送到数据库服务器执行。
• 事务管理：确保数据存储操作的原子性、一致性、隔离性和持久性（ACID特性）。

数据持久化

一旦数据被成功存储到数据库中，它将被持久化，即即使数据库系统崩溃或重启，数据也不会丢失（假设使用了适当的备份和恢复策略）。

反馈与日志

最后，系统通常会向用户或应用程序提供反馈，表明数据存储操作的成功或失败。此外，还会记录相关的日志信息，以便进行审计、调试和故障排除。

注意事项

• 安全性：在数据存储过程中，必须确保数据的安全性，包括防止未经授权的访问、篡改和泄露。
• 性能：为了提高数据存储的效率，可以采取一些优化措施，如索引的使用、批量插入等。
• 一致性：确保数据存储后与应用程序的其他部分保持一致，避免数据不一致导致的错误或冲突。

综上所述，数据存储是一个复杂而关键的过程，涉及多个步骤和考虑因素。通过仔细规划和实施这些步骤，可以确保数据的准确性、完整性和安全性。

标识符命名规则

MySQL中的标识符用于标识数据库、表、列等对象的名称。

基本规则

1. 字符组成：标识符可以由字母（a-z、A-Z）、数字（0-9）、下划线（_）和美元符号（$）组成。
2. 首字符：标识符的第一个字符必须是字母或下划线，不能是数字。
3. 大小写敏感性：MySQL对标识符的大小写敏感性取决于具体的存储引擎和操作系统。但在大多数情况下，MySQL在Windows系统上对标识符不区分大小写，而在Linux系统上则区分大小写。为了避免混淆，建议统一使用小写字母。

长度限制

• 数据库名、表名不得超过30个字符
• 变量名不得超过29个字符

保留字与特殊字符

1. 保留字：MySQL保留了一些关键字作为内部命令或函数的名称。当使用这些保留字作为标识符时，需要使用反引号（`）将其括起来，以避免冲突。
2. 特殊字符：除了字母、数字、下划线和美元符号外，其他特殊字符通常不允许出现在标识符中。如果需要使用特殊字符，可以考虑使用反引号将其括起来（但请注意，这并不是一种推荐的做法，因为它可能会降低代码的可读性）。

命名建议

1. 简洁明了：标识符应该简洁明了，能够清晰地表达出对象的含义。避免使用过长或含糊不清的名称。
2. 避免保留字：尽量避免使用MySQL的保留字作为标识符，以减少潜在的冲突和混淆。
3. 一致性：在命名时保持一致性，例如使用相同的命名风格（如驼峰命名法、下划线分隔法等）来命名数据库、表和列等对象。

示例

以下是一些符合MySQL标识符命名规则的示例：

• 正确的表名：user、order_items、customer_details
• 正确的列名：id、name、email、birth_date
• 使用反引号括起来的保留字作为标识符：SELECT（注意：这里只是为了演示，实际上不建议这样做）

请注意，虽然MySQL允许在必要时使用反引号来避免与保留字的冲突，但过度使用反引号可能会降低代码的可读性和可维护性。因此，在命名时应尽量避免使用保留字，并遵循MySQL的标识符命名规则。

MySQL 中的数据类型

在MySQL中，数据类型用于指定表中列可以存储数据的种类。MySQL支持多种数据类型，包括数值类型、日期和时间类型以及字符串（文本）。

数据类型	描述	示例
数值类型
TINYINT	非常小的整数	127, -128（有符号）；0~255, 0~-1（无符号）
SMALLINT	小的整数	32,767, -32,768（有符号）；0~65,535, 0~-1（无符号）
MEDIUMINT	中等大小的整数	8,388,607, -8,388,608（有符号）；0~16,777,215, 0~-1（无符号）
INT（或INTEGER）	标准整数	2,147,483,647, -2,147,483,648（有符号）；0~4,294,967,295, 0~-1（无符号）
BIGINT	大的整数	9,223,372,036,854,775,807, -9,223,372,036,854,775,808（有符号）；0~18,446,744,073,709,551,615, 0~-1（无符号）
FLOAT	单精度浮点数	3.14159
DOUBLE	双精度浮点数	3.141592653589793
DECIMAL	定点数，用于存储精确的小数	123.456
日期和时间类型
DATE	日期值	‘2023-10-01’
TIME	时间值	‘12:34:56’
DATETIME	日期和时间值	‘2023-10-01 12:34:56’
TIMESTAMP	时间戳，通常用于记录行的创建或更新时间	CURRENT_TIMESTAMP
YEAR	年份值	2023
字符串（文本）类型
CHAR	定长字符串	‘hello’
VARCHAR	可变长字符串	‘hello’
TINYTEXT	非常小的文本字符串	‘This is a tiny text.’
TEXT	小文本字符串	‘This is a text.’
MEDIUMTEXT	中等大小的文本字符串	‘This is a medium text.’
LONGTEXT	大的文本字符串	‘This is a very long text.’
ENUM	枚举类型，允许你指定一个值的集合	ENUM(‘small’, ‘medium’, ‘large’)
SET	集合类型，允许你指定一个字符串对象的集合	SET(‘apple’, ‘banana’, ‘cherry’)
BINARY	二进制字符串	b’010101’
VARBINARY	可变长的二进制字符串	b’010101’
TINYBLOB	非常小的BLOB	BLOB数据
BLOB	小BLOB	BLOB数据
MEDIUMBLOB	中等大小的BLOB	BLOB数据
LONGBLOB	大的BLOB	BLOB数据
JSON	用于存储JSON格式的数据	‘{“name”: “John”, “age”: 30}’

请注意，上述表格中的示例数据仅用于说明数据类型，实际存储的数据将取决于列的定义和插入的数据值。此外，MySQL还允许在定义数据类型时指定额外的属性，如字符集、排序规则、是否允许为空以及默认值等。

创建数据库

要创建一个新的数据库，可以使用 CREATE DATABASE 语句。基本语法如下：

CREATE DATABASE [IF NOT EXISTS] database_name;

示例：

CREATE DATABASE IF NOT EXISTS my_database;

创建数据库时指定字符集和排序规则

你也可以在创建数据库时指定字符集和排序规则。例如：

CREATE DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

查看数据库

要查看当前服务器上的所有数据库，可以使用 SHOW DATABASES 语句：

SHOW DATABASES;

修改数据库

MySQL 不提供直接修改数据库名称的语句。但是，你可以通过以下步骤间接地修改数据库名称：

1. 创建一个新的数据库。
2. 将旧数据库中的所有表和数据复制到新数据库中。
3. 删除旧数据库（可选）。

修改数据库的字符集和排序规则

你可以使用 ALTER DATABASE 语句来修改数据库的字符集和排序规则：

ALTER DATABASE database_name
CHARACTER SET new_character_set
COLLATE new_collation;

示例：

ALTER DATABASE my_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

删除数据库

要删除一个数据库，可以使用 DROP DATABASE 语句。删除数据库将永久删除该数据库中的所有表和数据。基本语法如下：

DROP DATABASE [IF EXISTS] database_name;

示例：

DROP DATABASE IF EXISTS my_database;

注意事项

1. 权限：创建、修改和删除数据库需要相应的权限。通常，这些操作只能由具有管理员权限的用户（如 root 用户）执行。
2. 数据备份：在删除数据库之前，确保已经备份了重要数据。删除数据库是不可逆的操作，数据将无法恢复。
3. 数据库名称：数据库名称在 MySQL 服务器上必须是唯一的，并且符合命名规则（例如，不能使用保留字）。

总结

• 使用 CREATE DATABASE 语句创建数据库。
• 使用 SHOW DATABASES 语句查看所有数据库。
• 使用 ALTER DATABASE 语句修改数据库的字符集和排序规则（但无法直接修改数据库名称）。
• 使用 DROP DATABASE 语句删除数据库。