初学者学习 MySQL/MariaDB – 第 1 部分
在本文中，我们将展示如何创建数据库（也称为模式）、表（具有数据类型），并解释如何在MySQL / MariaDB服务器上对数据执行数据操作语言（DML）操作。
假设您之前已1)在 Linux 系统上安装了必要的软件包，并且2)执行了mysql_secure_installation以提高数据库服务器的安全性。如果没有，请按照以下指南安装 MySQL/MariaDB 服务器。
为简洁起见，我们将在本文中专门引用MariaDB ，但这里概述的概念和命令也适用于MySQL 。
创建数据库、表和授权用户
众所周知，数据库可以简单地定义为有组织的信息集合。具体来说，MariaDB是一个关系数据库管理系统 ( RDBMS )，使用结构化查询语言对数据库执行操作。此外，请记住，MariaDB 可互换使用数据库和模式这两个术语。
为了在数据库中存储持久信息，我们将使用存储数据行的表。通常，两个或多个表会以某种方式相互关联。这是表征关系数据库使用的组织的一部分。
创建新数据库
要创建一个名为 的新数据库BooksDB，请使用以下命令进入 MariaDB 提示符（系统将提示您输入 root MariaDB 用户的密码）：
[root@Example ~]# 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 视为假，将任何其他值视为真。
- 如果将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 个字节。例如，西班牙语字母 ñ 表示一个字符，但占用 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_INCREMENT用于将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。
选择、插入、更新和删除行
我们首先填充表格。为什么？因为在将记录插入BooksTBLAuthorsTBL之前，我们需要有值。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中的记录。然后让我们更新Paulo Coelho的“炼金术士”的价格，并再次SELECT该特定记录。
请注意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 ” 。
To do so, we will use the DELETE statement as follows:
MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;
As in the case of UPDATE, it is a good idea to do a SELECT first in order to view the record(s) that may potentially be impacted by the DELETE.
Also, don’t forget to add the WHERE clause and a condition (BookID=6) to select the specific record to be removed. Otherwise, you run the risk of deleting all the rows in the table!
If you desire to concatenate two (or more) fields, you can use the CONCAT statement. For example, let’s say we want to return a result set that consist of one field with the book name and author in the form of “The Alchemist (Paulo Coelho)” and another column with the price.
This will require a JOIN between AuthorsTBL and BooksTBL on the common field shared by both tables (AuthorID):
MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
As we can see, CONCAT allows us to join multiple string expressions separated by commas. You’ll also noticed that we chose the alias Description to represent the result set of the concatenation.
The output of the above query is shown in the below image:
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)
Create User to Access the BookstoreDB Database
Using root to perform all DML operations in a database is a bad idea. To avoid this, we can create a new MariaDB user account (we’ll name it bookstoreuser) and assign all necessary permissions for 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 'example'; 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)
Having a dedicated, separate user for each database will prevent damages to the entire database should a single account become compromised.
Extra MySQL Tips
To clear up the MariaDB prompt, type the following command and press Enter:
MariaDB [BookstoreDB]> \! clear
To inspect the configuration of a given table, do:
MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];
For example,
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)
A quick inspection reveals that the BookIsAvailable field admits NULL values. Since we don’t want to allow that, we’ll ALTER the table as follows:
MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;
(Feel free to show the columns again – the highlighted YES in the above image should now be a NO).
Finally, to view all the databases on your server, do:
MariaDB [BookstoreDB]> SHOW DATABASES; OR MariaDB [BookstoreDB]> SHOW SCHEMAS;
[root@Example ~]# 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和information_schema（可供所有用户使用）之外的任何数据库）：
概括
在本文中，我们解释了如何运行DML操作以及如何在 MariaDB 数据库上创建数据库、表和专用用户。此外，我们还分享了一些技巧，这些技巧可能会让您作为系统/数据库管理员的生活更轻松。
如果您对本文有任何疑问，请随时告诉我们！请随意使用下面的评论表与我们联系。