MySQL 数据库操作

1. 查看所有数据库

-- 使用 `show databases;` 查看当前 MySQL 服务器上的所有数据库
show databases;

-- 输出示例（假设当前有三个数据库）
+--------------------+
| Database           |
+--------------------+
| information_schema | -- 系统内置数据库，存储数据库和表的元数据
| my_database        | -- 用户自建的数据库
| test               | -- 测试数据库
+--------------------+

-- 解释：
-- `show databases;` 命令会从 MySQL 的系统表中读取数据库列表，显示当前服务器上所有存在的数据库。 
-- 该操作不涉及大量数据，因此通常会被缓存，后续重复执行该命令时速度更快。

2. 查看数据库的创建语句

-- 使用 `show create database 数据库名称;` 查看某个数据库的创建语句
show create database 数据库名称;

-- 输出示例
+----------------+---------------------------------------------------------------------+
| Database       | Create Database                                                     |
+----------------+---------------------------------------------------------------------+
| my_database    | CREATE DATABASE `my_database` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------------+---------------------------------------------------------------------+

-- 解释：
-- 此命令会返回创建指定数据库时的完整 SQL 语句，包括字符集设置等信息。
-- 该信息是从 MySQL 的元数据表中获取，通常不会缓存，但由于数据量较小，查询速度依然很快。
-- 在上面的输出中，字符集设置部分可能根据数据库的实际配置有所不同。

3. 创建一个数据库

-- 使用 `create database 数据库名称;` 创建一个新的数据库
create database 数据库名称;

-- 输出示例
Query OK, 1 row affected (0.01 sec)

-- 解释：
-- 该命令用于创建一个新的数据库。`Query OK` 表示操作成功，受影响的行数为 1，表示创建了一个新的数据库。
-- 如果数据库创建成功，MySQL 会立即更新系统表，并将操作结果缓存到内存中，以便快速响应后续查询。

4. 创建数据库前判断是否存在

-- 使用 `create database if not exists 数据库名称;` 在创建前判断数据库是否已存在
create database if not exists 数据库名称;

-- 输出示例（数据库已存在的情况）
Query OK, 1 row affected, 1 warning (0.01 sec)

-- 输出示例（数据库不存在并创建成功）
Query OK, 1 row affected (0.01 sec)

-- 解释：
-- 该命令在创建数据库时首先会检查数据库是否已经存在，避免重复创建。当数据库已存在时，MySQL 会返回一个警告信息而不是错误。
-- `if not exists` 是防止重复创建的关键字，尤其在自动化脚本中非常实用。

5. 创建数据库并设置字符编码

-- 使用 `create database 数据库名 character set 字符集名称;` 创建数据库时指定字符集
create database 数据库名称 character set utf8mb4;

-- 输出示例
Query OK, 1 row affected (0.01 sec)

-- 解释：
-- 该命令在创建数据库时同时设置字符集，确保所有数据的存储和显示采用一致的编码格式。`utf8mb4` 是推荐的字符集，可以处理更多字符，包括表情符号。
-- MySQL 会将字符集信息存储在系统表中，并在后续查询或数据操作时优先使用该字符集。

6. 修改数据库的字符集

-- 使用 `alter database 数据库名称 character set 字符集名称;` 修改现有数据库的字符集
alter database 数据库名称 character set utf8mb4;

-- 输出示例
Query OK, 1 row affected (0.01 sec)

-- 解释：
-- 该命令用于更改现有数据库的字符集，修改成功后，所有新建表和插入的数据都会采用新字符集。旧数据不会自动转换，需手动调整。
-- MySQL 会更新元数据表以反映字符集更改，并将结果存储在缓存中，确保后续查询的快速响应。

7. 删除一个数据库

-- 使用 `drop database 数据库名称;` 删除指定的数据库
drop database 数据库名称;

-- 输出示例
Query OK, 0 rows affected (0.00 sec)

-- 输出示例（删除不存在的数据库时）
drop database if exists 不存在的数据库名称;

-- Query OK, 0 rows affected (0.01 sec)

-- 解释：
-- `drop database` 命令会删除指定的数据库及其包含的所有数据表。操作不可逆，删除后所有数据将无法恢复。
-- 如果数据库不存在，使用 `if exists` 可以避免错误提示，MySQL 只会返回 "0 rows affected"。
-- 删除操作会立即更新系统表，并清空相关缓存，确保后续查询中不再显示已删除的数据库。

8. 切换到指定数据库

-- 使用 `use 数据库名称;` 切换到目标数据库，后续操作将在此数据库中进行
use 数据库名称;

-- 输出示例
Database changed

-- 解释：
-- 成功切换数据库后，MySQL 会提示 "Database changed"，表明后续所有操作都将在该数据库中执行。
-- `use` 命令本身不会缓存，但切换后的数据库信息会保存在 MySQL 连接的会话中，直至会话结束。

9. 查询当前使用的数据库

-- 使用 `select database();` 查看当前正在使用的数据库
select database();

-- 输出示例
+----------------+
| database()     |
+----------------+
| 当前使用的数据库名称 |
+----------------+

-- 解释：
-- `select database();` 命令返回当前活动的数据库名称，便于确认当前的工作环境。
-- 该信息存储在会话缓存中，因此查询速度非常快。

10. 显示数据库中的所有表

-- 使用 `show tables;` 查看当前数据库中的所有表
show tables;

-- 输出示例（假设当前数据库有两张表）
+-------------------+
| Tables_in_数据库名称 |
+-------------------+
| users             |
| orders            |
+-------------------+

-- 解释：
-- `show tables;` 命令会列出当前活动数据库中的所有表，通常用于了解数据库结构。
-- 表信息会被 MySQL 缓存，因此后续查询速度较快。

11. 查看表的结构（字段信息）

-- 使用 `describe 表名称;` 或 `show columns from 表名称;` 查看表的结构
describe 表名称;
-- 或者
show columns from 表名称;

-- 输出示例
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
| email   | varchar(100)| NO   | UNI | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

-- 解释：
-- `describe` 和 `show columns from` 命令都会返回表的字段结构，包括字段名、数据类型、是否为空、索引类型、默认值和额外信息。
-- 字段信息通常会被缓存，特别是在频繁访问时，查询速度更快。

12. 查看表的创建语句

-- 使用 `show create table 表名称;` 查看某个表的创建语句
show create table 表名称;

-- 输出示例
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+

-- 解释：
-- 此命令返回表的完整创建语句，包括字段定义、索引、约束、存储引擎和字符集等详细信息。
-- 表的创建信息通常会保存在系统表中，不会频繁变动，因此查询结果可能会被 MySQL 缓存。

13. 重命名数据库（无直接命令）

-- MySQL 不支持直接重命名数据库，通常需要新建数据库并迁移数据
create database 新数据库名称;
-- 迁移数据时可以使用数据导出导入工具，如 mysqldump

14. 备份数据库

-- 使用 mysqldump 工具备份数据库
mysqldump -u 用户名 -p 数据库名称 > backup.sql

-- 输出示例
-- 生成的 backup.sql 文件包含数据库的结构和数据，用于备份或迁移。


-- 备份文件通常较大，MySQL 不会缓存该过程，但会优化导出性能以减少影响。

15. 恢复数据库

-- 使用 mysql 命令恢复数据库
mysql -u 用户名 -p 数据库名称 < backup.sql

-- 输出示例
-- 将 backup.sql 中的数据重新导入到指定数据库，恢复备份状态。
-- 恢复操作会占用较多资源，MySQL 会优先保证数据一致性，通常不涉及缓存。

16. 查看数据库的大小

-- 使用 SQL 语句查看数据库大小
select table_schema as '数据库名称', 
       round(sum(data_length + index_length) / 1024 / 1024, 2) as '数据库大小(MB)' 
from information_schema.tables 
group by table_schema;

-- 输出示例
+--------------------+------------------+
| 数据库名称         | 数据库大小(MB)  |
+--------------------+------------------+
| information_schema | 0.16             |
| my_database        | 12.34            |
| test               | 0.03             |
+--------------------+------------------+

-- 解释：
-- 查询结果展示了每个数据库的名称和其占用的存储空间（MB）。`information_schema.tables` 是一个系统表，包含了所有表和数据库的元数据信息。
-- 由于涉及大量数据统计，此查询不会缓存，但结果可以用于后续分析和优化。