通过这份 MariaDB 和 MySQL 速查表提升您的数据库知识

阅读本文并下载我们的免费速查表，开始使用开源数据库。

图片来源

Opensource.com

当您编写应用程序或为服务器配置应用程序时，最终您将需要存储持久信息。有时，配置文件（例如 INI 或 YAML 文件）就可以满足需求。其他时候，使用 XML 或 JSON 或类似格式设计的自定义文件格式会更好。

但有时您需要一些可以验证输入、快速搜索信息、建立相关数据之间的连接以及通常能熟练处理用户工作的东西。这正是数据库的设计目的，而 MariaDB（MySQL 的一个分支，由其一些原始开发者创建）是一个很好的选择。我在本文中使用 MariaDB，但这些信息同样适用于 MySQL。

通过编程语言与数据库交互是很常见的。因此，存在适用于 Java、Python、Lua、PHP、Ruby、C++ 和许多其他语言的 SQL 库。但是，在使用这些库之前，了解数据库引擎中发生了什么以及您选择数据库的重要性会有所帮助。本文介绍了 MariaDB 和 mysql 命令，让您熟悉数据库处理数据的基本原理。

如果您还没有 MariaDB，请按照我的文章中关于在 Linux 上安装 MariaDB 的说明进行操作。如果您不是在 Linux 上，请使用 MariaDB 下载页面上提供的说明。

与 MariaDB 交互

您可以使用 mysql 命令与 MariaDB 交互。首先，使用 ping 子命令验证您的服务器是否已启动并正在运行，并在提示时输入您的 MariaDB 密码

$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

为了方便探索 SQL，请打开一个交互式 MariaDB 会话

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.
Commands end with ; or \g.
[...]
Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.

MariaDB [(none)]>

这会将您置于 MariaDB 子 shell 中，并且您的提示符现在是 MariaDB 提示符。您常用的 Bash 命令在这里不起作用。您必须使用 MariaDB 命令。要查看 MariaDB 命令列表，请键入 help（或仅键入 ?）。这些是用于自定义您的 shell 的 MariaDB shell 管理命令，因此它们很有用，但它们不是 SQL 语言的一部分。

学习 SQL 基础知识

结构化查询语言 (SQL) 以其提供的功能命名：一种以可预测且一致的语法查询数据库内容的方法，以便接收有用的结果。SQL 的阅读方式很像普通的英语句子，如果有点机械化的话。例如，如果您已登录数据库服务器并且需要了解您要处理的内容，请键入 SHOW DATABASES; 并按 Enter 键查看结果。

SQL 命令以分号结尾。如果您忘记分号，MariaDB 会假定您想在下一行继续您的查询，您可以在下一行继续查询或用分号结束查询。

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

这显示存在四个数据库：information_schema、mysql、performance_schema 和 test。要向数据库发出查询，您必须选择要 MariaDB 使用的数据库。这通过 MariaDB 命令 use 完成。一旦您选择了一个数据库，您的 MariaDB 提示符就会更改以反映活动的数据库。

MariaDB [(none)]> use test;
MariaDB [(test)]>

显示数据库表

数据库包含表，可以将表可视化为类似于电子表格的方式：作为一系列行（在数据库中称为记录）和列。行和列的交叉点称为字段。

要查看数据库中可用的表（您可以将它们视为多页电子表格中的选项卡），请再次使用 SQL 关键字 SHOW

MariaDB [(test)]> SHOW TABLES;
empty set

test 数据库没有什么可看的，因此请使用 use 命令切换到 mysql 数据库。

MariaDB [(test)]> use mysql;
MariaDB [(mysql)]> SHOW TABLES;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
[...]
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

此数据库中有更多表！mysql 数据库是此 MariaDB 实例的系统管理数据库。它包含重要数据，包括用于管理数据库权限的完整用户结构。它是一个重要的数据库，您不必总是直接与之交互，但在 SQL 脚本中操作它并不少见。当您学习 MariaDB 时，理解 mysql 数据库也很有用，因为它可以帮助演示一些基本的 SQL 命令。

检查表

此实例的 mysql 数据库中列出的最后一个表名为 user。此表包含有关允许访问数据库的用户的数据。目前，只有一个 root 用户，但您可以添加具有不同权限的其他用户来控制每个用户是否可以查看、更新或创建数据。要了解 MariaDB 用户可以拥有的所有属性，您可以查看表中的列标题

> SHOW COLUMNS IN user;
MariaDB [mysql]> SHOW columns IN user;
+-------------+---------------+------+-----+----------+
| Field       | Type          | Null | Key | Default  |
+-------------+---------------+------+-----+----------+
| Host        | char(60)      | NO   | PRI |          |
| User        | char(80)      | NO   | PRI |          |
| Password    | char(41)      | NO   |     |          |
| Select_priv | enum('N','Y') | NO   |     | N        |
| Insert_priv | enum('N','Y') | NO   |     | N        |
| Update_priv | enum('N','Y') | NO   |     | N        |
| Delete_priv | enum('N','Y') | NO   |     | N        |
| Create_priv | enum('N','Y') | NO   |     | N        |
| Drop_priv   | enum('N','Y') | NO   |     | N        |
[...]
47 rows in set (0.001 sec)

创建新用户

无论您是需要同伴的帮助来管理数据库，还是您正在设置供计算机使用的数据库（例如，在 WordPress、Drupal 或 Joomla 安装中），在 MariaDB 中都需要一个额外的用户帐户是很常见的。您可以通过将用户添加到 mysql 数据库的 user 表中来创建 MariaDB 用户，也可以使用 SQL 关键字 CREATE 来提示 MariaDB 为您执行此操作。后者具有一些辅助函数，因此您不必手动生成所有信息

> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';

查看表字段

您可以使用 SELECT 关键字查看数据库表中的字段和值。在本例中，您创建了一个名为 tux 的用户，因此请选择 user 表中的列

> SELECT user,host FROM user;
+------+------------+
| user | host       |
+------+------------+
| root | localhost  |
[...]
| tux  | localhost  |
+------+------------+
7 rows in set (0.000 sec)

授予用户权限

通过查看 user 表上的列列表，您可以探索用户的状态。例如，新用户 tux 没有执行任何数据库操作的权限。使用 WHERE 语句，您只能查看 tux 的记录

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | N           | N           | N           |
+------+-------------+-------------+-------------+

使用 GRANT 命令修改用户权限

> GRANT SELECT on *.* TO 'tux'@'localhost';
> FLUSH PRIVILEGES;

验证您的更改

> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux  | Y           | N           | N           |
+------+-------------+-------------+-------------+

用户 tux 现在拥有从所有表中选择记录的权限。

创建自定义数据库

到目前为止，您只与默认数据库进行了交互。大多数人很少在用户管理之外与默认数据库进行太多交互。通常，您会创建一个数据库并用包含自定义数据的表填充它。

创建 MariaDB 数据库

您可能已经可以猜到如何在 MariaDB 中创建一个新数据库。它很像创建一个新用户

> CREATE DATABASE example;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
[...]

使用 use 命令使这个新数据库成为您的活动数据库

> use example;

创建表

创建表比创建数据库更复杂，因为您必须定义列标题。MariaDB 提供了许多方便的功能供您在创建列时使用，包括数据类型定义、自动递增选项、避免空值的约束、自动时间戳等等。

这是一个描述一组用户的简单表

> CREATE table IF NOT EXISTS member (
    -> id INT auto_increment PRIMARY KEY,
    -> name varchar(128) NOT NULL,
    -> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.030 sec)

此表通过使用自动递增函数为每一行提供唯一的标识符。它包含一个用于用户名字段，该字段不能为空（或 null），并在创建记录时生成时间戳。

使用 INSERT SQL 关键字使用一些示例数据填充此表

> INSERT INTO member (name) VALUES ('Alice');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Bob');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Carol');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('David');
Query OK, 1 row affected (0.011 sec)

验证表中的数据

> SELECT * FROM member;
+----+-------+---------------------+
| id | name  | startdate           |
+----+-------+---------------------+
|  1 | Alice | 2020-10-03 15:25:06 |
|  2 | Bob   | 2020-10-03 15:26:43 |
|  3 | Carol | 2020-10-03 15:26:46 |
|  4 | David | 2020-10-03 15:26:51 |
+----+-------+---------------------+
4 rows in set (0.000 sec)

一次添加多行

现在创建第二个表

> CREATE table IF NOT EXISTS linux (
    -> id INT auto_increment PRIMARY KEY,
    -> distro varchar(128) NOT NULL,
Query OK, 0 rows affected (0.030 sec)

使用一些示例数据填充它，这次使用一点 VALUES 快捷方式，以便您可以在一个命令中添加多行。VALUES 关键字需要括号中的列表，但它可以接受逗号分隔的多个列表

> INSERT INTO linux (distro)
 -> VALUES ('Slackware'), ('RHEL'),('Fedora'),('Debian');
Query OK, 4 rows affected (0.011 sec)
Records: 4  Duplicates: 0  Warnings: 0
> SELECT * FROM linux;
+----+-----------+
| id | distro    |
+----+-----------+
|  1 | Slackware |
|  2 | RHEL      |
|  3 | Fedora    |
|  4 | Debian    |
+----+-----------+

在表之间创建关系

您现在有两个表，但它们之间没有关系。它们各自包含独立的数据，但您可能需要将第一个表中的成员与第二个表中列出的特定项目关联起来。

为此，您可以为第一个表创建一个新列，该列对应于第二个表中的某些内容。由于两个表都设计了唯一标识符（自动递增的 id 字段），因此连接它们的最简单方法是将其中一个表的 id 字段用作另一个表的选择器。

在第一个表中创建一个新列来表示第二个表中的值

> ALTER TABLE member ADD COLUMN (os INT);
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0
> DESCRIBE member;
DESCRIBE member;
+-----------+--------------+------+-----+---------+------+
| Field     | Type         | Null | Key | Default | Extra|
+-----------+--------------+------+-----+---------+------+
| id        | int(11)      | NO   | PRI | NULL    | auto_|
| name      | varchar(128) | NO   |     | NULL    |      |
| startdate | timestamp    | NO   |     | cur[...]|      |
| os        | int(11)      | YES  |     | NULL    |      |
+-----------+--------------+------+-----+---------+------+

使用 linux 表的唯一 ID，为每个成员分配一个发行版。由于记录已经存在，因此使用 UPDATE SQL 关键字而不是 INSERT。具体来说，您要选择一行，然后更新一列的值。在语法上，这有点颠倒，更新发生在前面，选择匹配发生在后面

> UPDATE member SET os=1 WHERE name='Alice';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

对 member 表中的其他名称重复此过程，以使用数据填充它。为了多样性，在四行中分配三个不同的发行版（在一个发行版上加倍）。

连接表

现在这两个表相互关联，您可以使用 SQL 显示关联的数据。数据库中有许多类型的连接，一旦您了解了基础知识，就可以尝试所有连接。这是一个基本的连接，用于将 member 表的 os 字段中找到的值与 linux 表的 id 字段相关联

SELECT * FROM member JOIN linux ON member.os=linux.id;
+----+-------+---------------------+------+----+-----------+
| id | name  | startdate           | os   | id | distro    |
+----+-------+---------------------+------+----+-----------+
|  1 | Alice | 2020-10-03 15:25:06 |    1 |  1 | Slackware |
|  2 | Bob   | 2020-10-03 15:26:43 |    3 |  3 | Fedora    |
|  4 | David | 2020-10-03 15:26:51 |    3 |  3 | Fedora    |
|  3 | Carol | 2020-10-03 15:26:46 |    4 |  4 | Debian    |
+----+-------+---------------------+------+----+-----------+
4 rows in set (0.000 sec)

os 和 id 字段构成连接。

在图形应用程序中，您可以想象 os 字段可能由下拉菜单设置，下拉菜单的值从 linux 表的 distro 字段的内容中提取。通过对唯一但相关的数据集使用单独的表，您可以确保数据的一致性和有效性，并且由于 SQL，您可以在以后动态地关联它们。