MySQL 数据库管理基础

简介

在本实验中，我们将探索使用 MySQL 进行数据库管理的基本概念。我们将涵盖创建和删除数据库、选择数据库以及检索元数据等内容。无论你是初学者还是经验丰富的开发人员，这些技能对于任何与数据库打交道的人来说都是必不可少的。通过本实验，你将为 MySQL 数据库的基本操作打下坚实的基础。

学习目标

完成本实验后，你将能够：

• 使用多种方法创建和删除数据库
• 选择并切换不同的数据库
• 检索有关 MySQL 服务器和数据库的重要元数据
• 理解 MySQL 中数据库名称的大小写敏感性

创建和列出数据库

在这一步中，我们将学习如何使用 MySQL 命令行客户端创建数据库并列出现有数据库。这是任何数据库管理员或开发人员的基本技能。

首先，打开一个终端窗口。在 LabEx 环境中，你将使用 Ubuntu Linux 系统。终端通常会自动打开，如果没有，你可以通过点击终端图标来打开它。

现在，启动 MySQL 客户端：

sudo mysql -u root

在 LabEx 虚拟机中，root 用户无需输入密码。

进入 MySQL 客户端后，你会看到类似 MariaDB [(none)]> 的提示符。这表明你已连接到 MariaDB 服务器（MySQL 的一个分支），但尚未选择特定的数据库。

列出可用数据库

首先，列出 MySQL 服务器上可用的数据库：

SHOW DATABASES;

你应该会看到类似以下的输出：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.000 sec)

该命令列出了服务器上当前可用的所有数据库。让我们简要了解一下这些默认数据库的作用：

• information_schema：提供对数据库元数据访问的数据库。
• mysql：包含用户账户和权限信息的系统数据库。
• performance_schema：用于在底层监控 MySQL 服务器执行情况的数据库。
• sys：提供了一组对象，用于帮助解释由 Performance Schema 收集的数据。

创建数据库

现在，创建一个名为「Hello_World」的新数据库。在 MySQL 中，我们使用 CREATE DATABASE 命令：

CREATE DATABASE Hello_World;

执行此命令后，MySQL 将返回：

Query OK, 1 row affected (0.000 sec)

这表示数据库已成功创建。为了确认，再次列出数据库：

SHOW DATABASES;

现在你应该能在列表中看到「Hello_World」了：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

太棒了！你刚刚创建了第一个数据库。

数据库名称的大小写敏感性

理解 MySQL 的一个重要概念是：数据库名称是大小写敏感的。这意味着「Hello_World」和「hello_world」会被视为两个不同的数据库。让我们演示一下：

CREATE DATABASE hello_world;
SHOW DATABASES;

现在你应该能在列表中同时看到「Hello_World」和「hello_world」：

MariaDB [(none)]> CREATE DATABASE hello_world;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

这证明了「Hello_World」和「hello_world」被视为两个独立的数据库。在设计数据库时，通常建议使用一致的命名规范以避免混淆。许多开发人员更喜欢使用全小写字母来命名数据库，以避免潜在的大小写敏感性问题。

使用 mysqladmin 创建数据库

在这一步中，我们将探索使用 mysqladmin 工具创建数据库的另一种方法。该工具是一个命令行实用程序，允许你在不进入 MySQL 提示符的情况下执行一些管理操作。

首先，退出 MySQL 客户端。你可以输入 exit 或 quit 并按回车键：

exit

回到常规命令行提示符后，使用 mysqladmin 创建一个新数据库：

sudo mysqladmin -u root create hello_world2

在 LabEx 虚拟机中，由于 root 数据库账户使用本地套接字认证，因此需要配合 sudo 使用 mysqladmin。

我们刚才使用的命令解析如下：

• sudo：以所需的系统权限运行命令。
• -u root：指定以 root 数据库用户身份连接。
• create：我们要执行的操作。
• hello_world2：我们要创建的数据库名称。

现在，重新登录 MySQL 客户端以验证新数据库是否已创建：

sudo mysql -u root

进入 MySQL 客户端后，列出数据库：

SHOW DATABASES;

你应该能在列表中看到「hello_world2」：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.000 sec)

mysqladmin 工具提供了一种直接从命令行创建数据库的快捷方式，这对于脚本编写和自动化任务非常有用。当你需要在大型脚本或自动化流程中创建数据库时，它特别方便。

删除数据库

现在我们已经创建了几个数据库，接下来学习如何删除它们。在数据库管理中，知道如何删除数据库与创建数据库同样重要。我们将只保留「Hello_World」数据库，并删除其他数据库。

使用 MySQL 客户端删除数据库

如果尚未登录，请先登录 MySQL 客户端：

sudo mysql -u root

现在，删除「hello_world」数据库：

DROP DATABASE hello_world;
SHOW DATABASES;

你应该会发现「hello_world」已不在列表中：

MariaDB [(none)]> DROP DATABASE hello_world;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| hello_world2       |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)

注意：使用 DROP DATABASE 命令时要格外小心。它会永久删除数据库及其所有内容，且不会要求确认。在生产环境中，在执行此类操作之前，通常应确保已做好备份和安全防护措施。

使用 mysqladmin 删除数据库

现在，使用 mysqladmin 删除「hello_world2」数据库。这种方法被认为更安全，因为它会在删除数据库之前提示确认。

输入 exit 或 quit 退出 MySQL 客户端，然后在终端中运行以下命令：

sudo mysqladmin -u root drop hello_world2

系统会提示你确认该操作：

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'hello_world2' database [y/N] y
Database "hello_world2" dropped

输入「y」并按回车键确认。这个额外的确认步骤有助于防止意外删除数据库。

现在，重新登录 MySQL 客户端并验证「hello_world2」是否已被删除：

SHOW DATABASES;

你应该看到：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| Hello_World        |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.000 sec)

我们已经成功清理了多余的数据库，只留下了我们需要的那些。

选择和使用数据库

现在我们已经创建并删除了数据库，接下来学习如何选择和使用特定的数据库。这是一项关键技能，因为你在 MySQL 中的大部分工作都将在特定的数据库内进行。

选择数据库

要选择一个要操作的数据库，请使用 USE 命令，后跟数据库名称：

USE Hello_World;

你应该会看到：

Database changed

这表明你现在正在「Hello_World」数据库中工作。除非另有说明，否则你随后运行的所有命令都将在该数据库的上下文中执行。

列出数据库中的表

要查看当前数据库中的表，请使用 SHOW TABLES; 命令：

SHOW TABLES;

由于我们尚未在「Hello_World」数据库中创建任何表，你将看到：

Empty set (0.00 sec)

对于新数据库，这是正常的。当你创建表时，它们就会出现在此列表中。

让我们切换到另一个数据库并列出其表，看看在已填充数据的数据库中它是如何工作的：

USE mysql;
SHOW TABLES;

你将看到「mysql」系统数据库中的表列表：

MariaDB [mysql]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.001 sec)

这演示了如何在数据库之间切换并查看其内容。「mysql」数据库包含 MySQL 用于管理权限、用户账户和其他内部数据的系统表。在自己的应用程序中，你将在自定义数据库中创建和管理自己的表。

检索 MySQL 元数据

在最后一步中，我们将探索如何从 MySQL 检索各种类型的元数据。元数据是「关于数据的数据」——它提供的是有关数据库系统本身的信息，而不是存储在其中的数据。这些信息对于理解和管理 MySQL 环境至关重要。

服务器版本

要获取 MySQL 服务器版本，请使用：

SELECT VERSION();

你将看到类似以下的输出：

MariaDB [mysql]> SELECT VERSION();
+----------------------------------+
| VERSION()                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0.000 sec)

当你需要了解当前 MySQL 版本支持哪些功能，或者在排查特定版本的问题时，这些信息非常有用。

当前数据库

要查看当前正在使用的数据库：

SELECT DATABASE();

输出将显示当前数据库名称，如果未选择数据库，则显示 NULL：

MariaDB [mysql]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.000 sec)

当你同时处理多个数据库并需要确认当前正在操作哪一个时，这会很有帮助。

当前用户

要查看当前用户：

SELECT USER();

你将看到类似以下的输出：

MariaDB [mysql]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

这显示了你登录的 MySQL 用户以及你连接的主机。它对于验证当前权限和连接详细信息非常有用。

服务器状态和变量

要查看所有服务器状态指标：

SHOW STATUS;

该命令提供了大量有关 MySQL 服务器当前状态的信息，包括各种计数器和统计数据。

要查看所有服务器配置变量：

SHOW VARIABLES;

该命令向你展示了 MySQL 服务器的配置方式，包括内存使用、连接限制以及许多其他参数的设置。

这两个命令都会产生大量的输出。你可以在终端中滚动查看，也可以过滤特定信息。例如，要查看与缓冲池（buffer pool）相关的变量：

SHOW VARIABLES LIKE '%buffer%';

这些命令提供了有关 MySQL 服务器配置和当前状态的详尽信息。理解这些元数据对于优化性能、排查问题以及确保服务器配置符合你的需求至关重要。

例如，你可以使用 SHOW STATUS 来检查已执行的查询数量：

SHOW STATUS LIKE 'Questions';

这可能会给你类似以下的输出：

MariaDB [mysql]> SHOW STATUS LIKE 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 15    |
+---------------+-------+
1 row in set (0.001 sec)

这告诉你自服务器启动以来已执行了多少条语句。

同样，你可以使用 SHOW VARIABLES 来检查重要的配置设置，例如允许的最大数据包大小：

SHOW VARIABLES LIKE 'max_allowed_packet';

这可能会给你类似以下的输出：

MariaDB [mysql]> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.001 sec)

这显示了单个数据包或任何生成的/中间字符串的最大大小，这在处理大量数据时非常重要。

了解如何检索和解释这些元数据是任何数据库管理员或开发人员的宝贵技能。它使你能够深入了解数据库系统的行为和性能，这对于优化和排查故障至关重要。

总结

在本实验中，我们涵盖了 MySQL 数据库管理的基本方面：

1. 使用 MySQL 客户端和 mysqladmin 工具创建数据库
2. 列出现有数据库并了解其用途
3. 理解 MySQL 中数据库名称的大小写敏感性
4. 安全地删除数据库以及执行此操作时保持谨慎的重要性
5. 选择和切换数据库
6. 检索有关 MySQL 服务器和数据库的重要元数据

这些技能构成了使用 MySQL 数据库的基础。随着你在数据库学习之旅中的深入，你将在此基础上执行更复杂的数据库操作和管理任务。