初学者学习 MySQL/MariaDB - 第 1 部分
在本文中，我们将展示如何创建数据库（也称为架构）、表（具有数据类型），并解释如何执行数据操作语言（DML ）对 MySQL/MariaDB 服务器上的数据进行操作。
假设您之前已经在Linux系统上安装了1)必要的软件包，并且2)执行了mysql_secure_installation以提高数据库服务器的安全性。如果没有，请按照以下指南安装 MySQL/MariaDB 服务器。
- 在 Linux 系统中安装最新的 MySQL 数据库
- 在 Linux 系统中安装最新的 MariaDB 数据库
为简洁起见，我们将在本文中专门提及 MariaDB，但此处概述的概念和命令也适用于 MySQL。
创建数据库、表和授权用户
如您所知，数据库可以简单地定义为有组织的信息集合。具体来说，MariaDB是一个关系数据库管理系统（RDBMS），使用结构查询语言对数据库执行操作。此外，请记住，MariaDB 可以互换使用术语“数据库”和“模式”。
为了在数据库中存储持久信息，我们将使用存储数据行的表。通常，两个或多个表会以某种方式相互关联。这是组织的一部分，以关系数据库的使用为特征。
创建新数据库
要创建名为 BooksDB 的新数据库，请使用以下命令输入 MariaDB 提示符（系统将提示您输入 MariaDB 根用户的密码）：
[root@TecMint ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
创建数据库后，我们需要在其上创建至少两个表。但首先让我们探讨一下数据类型的概念。
MariaDB 数据类型简介
正如我们之前所解释的，表是我们将在其中保存持久信息的数据库对象。每个表由两个或多个字段（也称为列）组成，这些字段可以存储给定的数据类型（信息类型）。
MariaDB 中最常见的数据类型如下（您可以在 MariaDB 官方在线文档中查阅完整列表）：
数字：
- BOOLEAN 将 0 视为 false，将任何其他值视为 true。
- TINYINT，如果与 SIGNED 一起使用，则覆盖范围为 -128 到 127，而 UNSIGNED 范围为 0 到 255。
- SMALLINT，如果与 SIGNED 一起使用，则覆盖范围为 -32768 到 32767。UNSIGNED 范围为 0 到 65535。
- INT，如果与 UNSIGNED 一起使用，则涵盖从 0 到 4294967295 的范围，否则涵盖 -2147483648 到 2147483647 的范围。
注意：在 TINYINT、SMALLINT 和 INT 中，假定默认为 SIGNED。
DOUBLE(M, D)，其中M是总位数，D是小数点后的位数，表示双精度浮点数。如果指定 UNSIGNED，则不允许负值。
细绳 ：
- VARCHAR(M) 表示可变长度的字符串，其中 M 是允许的最大列长度（以字节为单位）（理论上为 65,535）。在大多数情况下，字节数与字符数相同，但某些字符可能占用多达 3 个字节。例如，西班牙语字母ñ代表1个字符，但占用2个字节。
- TEXT(M) 表示最大长度为 65,535 个字符的列。但是，正如 VARCHAR(M) 所发生的那样，如果存储多字节字符，则实际最大长度会减少。如果指定了M，则该列将创建为可以存储如此数量字符的最小类型。
- MEDIUMTEXT(M) 和 LONGTEXT(M) 与 TEXT(M) 类似，只是允许的最大长度为 16,777,215 和 4,294,967,295 个字符，分别。
日期和时间：
- DATE 表示YYYY-MM-DD 格式的日期。
- TIME 表示HH:MM:SS.sss 格式的时间（小时、分钟、秒和毫秒）。
- DATETIME 是 DATE 和 TIME 的组合，格式为 YYYY-MM-DD HH:MM:SS。
- TIMESTAMP 用于定义添加或更新行的时刻。
查看这些数据类型后，您将能够更好地确定需要将哪种数据类型分配给表中的给定列。
例如，一个人的名字可以轻松地适合 VARCHAR(50)，而博客文章则需要 TEXT 类型（选择 M 作为根据您的具体需求）。
使用主键和外键创建表
在我们深入创建表之前，我们需要回顾一下关系数据库的两个基本概念：主和外键。
主键包含唯一标识表中每一行或记录的值。另一方面，外键用于在两个表中的数据之间创建链接，并控制外键所在表中可以存储的数据。主键和外键通常都是 INT。
为了进行说明，我们使用 BookstoreDB 并创建两个名为 AuthorsTBL 和 BooksTBL 的表，如下所示。 NOT NULL 约束指示关联字段需要 NULL 以外的值。
此外，当向表中插入新记录时，AUTO_INCRMENT 用于将INT 主键列的值加一。
MariaDB [(none)]> USE BookstoreDB;
MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);
MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);
MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
-> BookID INT NOT NULL AUTO_INCREMENT,
-> BookName VARCHAR(100) NOT NULL,
-> AuthorID INT NOT NULL,
-> BookPrice DECIMAL(6,2) NOT NULL,
-> BookLastUpdated TIMESTAMP,
-> BookIsAvailable BOOLEAN,
-> PRIMARY KEY(BookID),
-> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]>
现在我们可以继续将记录插入到 AuthorsTBL 和 BooksTBL 中。
选择、插入、更新和删除行
我们将首先填充 AuthorsTBL 表。为什么？因为在将记录插入 BooksTBL 之前，我们需要获得 AuthorID 的值。
从 MariaDB 提示符处执行以下查询：
MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');
之后，我们将从 AuthorsTBL 中选择所有记录。请记住，我们需要每条记录的 AuthorID 来为 BooksTBL 创建 INSERT 查询。
如果要一次检索一条记录，可以使用 WHERE 子句来指示返回行必须满足的条件。例如，
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
或者，您可以同时选择所有记录：
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName |
+----------+-----------------+
| 1 | Agatha Christie |
| 2 | Stephen King |
| 3 | Paulo Coelho |
+----------+-----------------+
3 rows in set (0.00 sec)
MariaDB [BookstoreDB]>
现在，我们为 BooksTBL 创建 INSERT 查询，使用相应的 AuthorID 来匹配每本书的作者。 BookIsAvailable 中的值 1 表示该书有库存，否则为 0：
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
-> VALUES ('And Then There Were None', 1, 14.95, 1),
-> ('The Man in the Brown Suit', 1, 23.99, 1),
-> ('The Stand', 2, 35.99, 1),
-> ('Pet Sematary', 2, 17.95, 0),
-> ('The Green Mile', 2, 29.99, 1),
-> ('The Alchemist', 3, 25, 1),
-> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7 Duplicates: 0 Warnings: 0
此时，我们将执行SELECT来查看BooksTBL中的记录。然后，让我们更新保罗·科埃略的《炼金术士》的价格，并再次选择该特定记录。
请注意 BookLastUpdated 字段现在如何显示不同的值。正如我们之前所解释的，TIMESTAMP 字段显示插入记录或上次修改记录时的值。
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| 1 | And Then There Were None | 1 | 14.95 | 2016-10-01 23:31:41 | 1 |
| 2 | The Man in the Brown Suit | 1 | 23.99 | 2016-10-01 23:31:41 | 1 |
| 3 | The Stand | 2 | 35.99 | 2016-10-01 23:31:41 | 1 |
| 4 | Pet Sematary | 2 | 17.95 | 2016-10-01 23:31:41 | 0 |
| 5 | The Green Mile | 2 | 29.99 | 2016-10-01 23:31:41 | 1 |
| 6 | The Alchemist | 3 | 25.00 | 2016-10-01 23:31:41 | 1 |
| 7 | By the River Piedra I Sat Down and Wept | 3 | 18.95 | 2016-10-01 23:31:41 | 0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName | AuthorID | BookPrice | BookLastUpdated | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
| 6 | The Alchemist | 3 | 22.75 | 2016-10-01 23:35:00 | 1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]>
虽然我们不会在这里这样做，但如果不再使用记录，您也可以删除它。例如，假设我们要从 BooksTBL 中删除“The Alchemist”。
为此，我们将使用 DELETE 语句，如下所示：
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
与UPDATE的情况一样，最好先执行SELECT，以便查看可能受影响的记录删除。
另外，不要忘记添加 WHERE 子句和条件 (BookID=6) 以选择要删除的特定记录。否则，您将面临删除表中所有行的风险！
如果您希望连接两个（或更多）字段，可以使用 CONCAT 语句。例如，假设我们要返回一个结果集，其中一个字段包含书名和作者（格式为“炼金术士（保罗·科埃略）”），另一列包含价格。
这需要在 AuthorsTBL 和 BooksTBL 之间在两个表共享的公共字段 (AuthorID) 上进行 JOIN：
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
正如我们所看到的，CONCAT 允许我们连接多个以逗号分隔的字符串表达式。您还会注意到，我们选择了别名 Description 来表示串联的结果集。
上述查询的输出如下图所示：
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie) | 14.95 |
| The Man in the Brown Suit (Agatha Christie) | 23.99 |
| The Stand (Stephen King) | 35.99 |
| Pet Sematary (Stephen King) | 17.95 |
| The Green Mile (Stephen King) | 29.99 |
| The Alchemist (Paulo Coelho) | 25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) | 18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)
创建用户来访问 BookstoreDB 数据库
使用root在数据库中执行所有DML操作是一个坏主意。为了避免这种情况，我们可以创建一个新的 MariaDB 用户帐户（我们将其命名为 bookstoreuser）并为 BookstoreDB 分配所有必要的权限：
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
每个数据库都有一个专用的、单独的用户，可以防止单个帐户受到威胁时对整个数据库造成损害。
额外的 MySQL 技巧
要清除 MariaDB 提示符，请键入以下命令并按 Enter：
MariaDB [BookstoreDB]> \! clear
要检查给定表的配置，请执行以下操作：
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
例如，
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID | int(11) | NO | PRI | NULL | auto_increment |
| BookName | varchar(100) | NO | | NULL | |
| AuthorID | int(11) | NO | MUL | NULL | |
| BookPrice | decimal(6,2) | NO | | NULL | |
| BookLastUpdated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1) | YES | | NULL | |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)
快速检查发现 BookIsAvailable 字段允许 NULL 值。由于我们不想允许这种情况发生，因此我们将更改该表，如下所示：
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
（随意再次显示列 - 上图中突出显示的 YES 现在应该是 NO）。
最后，要查看服务器上的所有数据库，请执行以下操作：
MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;
[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| BookstoreDB |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database |
+--------------------+
| BookstoreDB |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
下图显示了以 bookstoreuser 身份访问 MariaDB 提示符后执行上述命令的结果（请注意，此帐户如何无法“查看”除 BookstoreDB 和 BookstoreDB 之外的任何数据库）information_schema（适用于所有用户）：
概括
在本文中，我们解释了如何运行DML操作以及如何在MariaDB数据库上创建数据库、表和专用用户。此外，我们还分享了一些技巧，可以让您作为系统/数据库管理员的生活变得更加轻松。
- MySQL 数据库管理部分 – 1
- MySQL 数据库管理部分 – 2
- MySQL 性能调优和优化 – 第 3 部分
如果您对本文有任何疑问，请随时告诉我们！请随时使用下面的评论表与我们联系。