MySQL 数据库管理基础

介绍

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

学习目标

通过完成本实验，你将能够：

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

Skills Graph

创建和列出数据库

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

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

现在，让我们启动 MySQL 客户端：

sudo mysql -u root

在 LabEx VM 中，你不需要为 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 工具创建数据库的另一种方法。mysqladmin 是一个命令行工具，允许你在不进入 MySQL 提示符的情况下执行一些管理操作。

首先，让我们退出 MySQL 客户端。你可以通过输入 exit 或 quit 并按 Enter 键来完成：

exit

现在我们已经回到了常规的命令提示符下，让我们使用 mysqladmin 创建一个新的数据库：

mysqladmin -u root -p create hello_world2

当提示输入密码时，只需按 Enter 键（记住，在 LabEx VM 中，root MySQL 用户没有设置密码）。

我们刚刚使用的 mysqladmin 命令分解如下：

• -u root：指定我们以 root 用户身份连接。
• -p：告诉 mysqladmin 提示输入密码。
• 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 客户端，然后在终端中运行以下命令：

mysqladmin -u root -p drop hello_world2

你会被提示确认操作：

Enter password:
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 并按 Enter 键确认。这个额外的确认步骤可以帮助防止意外删除数据库。

现在，重新登录 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 服务器的配置方式，包括内存使用、连接限制和许多其他参数的设置。

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

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 数据库的基础。随着你在数据库学习中的深入，你将基于这些基础知识执行更复杂的数据库操作和管理任务。