【重学 MySQL】四十五、数据库的创建、修改与删除
【重学 MySQL】四十五、数据库的创建、修改与删除
- 一条数据存储的过程
- 数据输入
- 数据验证
- 数据处理
- 数据存储
- 数据持久化
- 反馈与日志
- 注意事项
- 标识符命名规则
- 基本规则
- 长度限制
- 保留字与特殊字符
- 命名建议
- 示例
- MySQL 中的数据类型
- 创建数据库
- 创建数据库时指定字符集和排序规则
- 查看数据库
- 修改数据库
- 修改数据库的字符集和排序规则
- 删除数据库
- 注意事项
- 总结
在 MySQL 中，数据库（也称为 schema）是用于存储和组织数据的一种逻辑结构。数据库的创建、修改和删除是数据库管理的基本操作。
一条数据存储的过程
在数据库系统中，一条数据存储的过程通常涉及多个步骤，从数据的输入到其在数据库中的持久化存储。
数据输入
首先，用户或应用程序需要将数据输入到系统中。这可以通过多种方式实现，例如：
- 用户界面（UI）：用户通过图形界面（如网页表单、桌面应用程序窗口等）输入数据。
- 应用程序接口（API）：外部应用程序通过调用API将数据发送到数据库系统。
- 批量导入：使用文件（如CSV、Excel等）批量导入数据。
数据验证
在数据被存储之前，通常需要进行验证以确保其准确性和完整性。这包括：
- 数据类型检查：确保数据符合预期的格式和类型（如字符串、数字、日期等）。
- 约束检查：验证数据是否满足数据库表定义的约束条件（如主键唯一性、外键关联、非空约束等）。
- 业务逻辑验证：根据应用程序的业务逻辑检查数据的有效性（如年龄必须在0到120岁之间）。
数据处理
如果数据通过了验证，接下来可能需要进行一些处理，例如：
- 数据转换：将数据转换为适合存储的格式（如日期格式转换、数据编码转换等）。
- 数据清洗：去除或纠正数据中的错误、重复或不一致。
- 数据标准化：将数据转换为统一的标准格式。
数据存储
处理后的数据将被存储到数据库中。这通常涉及以下步骤：
- 数据库连接：建立与数据库的连接（如使用数据库驱动程序或ORM框架）。
- SQL语句生成：根据要存储的数据生成相应的SQL语句（如INSERT、UPDATE等）。
- 执行SQL语句：将SQL语句发送到数据库服务器执行。
- 事务管理：确保数据存储操作的原子性、一致性、隔离性和持久性（ACID特性）。
数据持久化
一旦数据被成功存储到数据库中，它将被持久化，即即使数据库系统崩溃或重启，数据也不会丢失（假设使用了适当的备份和恢复策略）。
反馈与日志
最后，系统通常会向用户或应用程序提供反馈，表明数据存储操作的成功或失败。此外，还会记录相关的日志信息，以便进行审计、调试和故障排除。
注意事项
- 安全性：在数据存储过程中，必须确保数据的安全性，包括防止未经授权的访问、篡改和泄露。
- 性能：为了提高数据存储的效率，可以采取一些优化措施，如索引的使用、批量插入等。
- 一致性：确保数据存储后与应用程序的其他部分保持一致，避免数据不一致导致的错误或冲突。
综上所述，数据存储是一个复杂而关键的过程，涉及多个步骤和考虑因素。通过仔细规划和实施这些步骤，可以确保数据的准确性、完整性和安全性。
标识符命名规则
MySQL中的标识符用于标识数据库、表、列等对象的名称。
基本规则
- 字符组成：标识符可以由字母（a-z、A-Z）、数字（0-9）、下划线（_）和美元符号（$）组成。
- 首字符：标识符的第一个字符必须是字母或下划线，不能是数字。
- 大小写敏感性：MySQL对标识符的大小写敏感性取决于具体的存储引擎和操作系统。但在大多数情况下，MySQL在Windows系统上对标识符不区分大小写，而在Linux系统上则区分大小写。为了避免混淆，建议统一使用小写字母。
长度限制
- 数据库名、表名不得超过30个字符
- 变量名不得超过29个字符
保留字与特殊字符
- 保留字：MySQL保留了一些关键字作为内部命令或函数的名称。当使用这些保留字作为标识符时，需要使用反引号（`）将其括起来，以避免冲突。
- 特殊字符：除了字母、数字、下划线和美元符号外，其他特殊字符通常不允许出现在标识符中。如果需要使用特殊字符，可以考虑使用反引号将其括起来（但请注意，这并不是一种推荐的做法，因为它可能会降低代码的可读性）。
命名建议
- 简洁明了：标识符应该简洁明了，能够清晰地表达出对象的含义。避免使用过长或含糊不清的名称。
- 避免保留字：尽量避免使用MySQL的保留字作为标识符，以减少潜在的冲突和混淆。
- 一致性：在命名时保持一致性，例如使用相同的命名风格（如驼峰命名法、下划线分隔法等）来命名数据库、表和列等对象。
示例
以下是一些符合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 不提供直接修改数据库名称的语句。但是，你可以通过以下步骤间接地修改数据库名称：
- 创建一个新的数据库。
- 将旧数据库中的所有表和数据复制到新数据库中。
- 删除旧数据库（可选）。
修改数据库的字符集和排序规则
你可以使用 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;
注意事项
- 权限：创建、修改和删除数据库需要相应的权限。通常，这些操作只能由具有管理员权限的用户（如
root用户）执行。 - 数据备份：在删除数据库之前，确保已经备份了重要数据。删除数据库是不可逆的操作，数据将无法恢复。
- 数据库名称：数据库名称在 MySQL 服务器上必须是唯一的，并且符合命名规则（例如，不能使用保留字）。
总结
- 使用
CREATE DATABASE语句创建数据库。 - 使用
SHOW DATABASES语句查看所有数据库。 - 使用
ALTER DATABASE语句修改数据库的字符集和排序规则（但无法直接修改数据库名称）。 - 使用
DROP DATABASE语句删除数据库。