Linux中國

備忘單：提升你的 MariaDB 和 MySQL 資料庫技能

129 views0
By
Share

當你寫一個程序或配置一個服務時，你最終都要持久化存儲信息。有時候，你只需要一個 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 確認你的服務是運行著的，在提示後輸入密碼：

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

為了易於讀者理解，打開一個互動式的 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 命令。輸入 help （或 ?）查看命令列表。這些是你的 MariaDB shell 的管理命令，使用它們可以定製你的 shell，但它們不屬於 SQL 語言。

學習 SQL 基本知識

結構化查詢語言是基於它們的能力定義的：一種通過有規則且一致的語法來查詢資料庫中的內容以得到有用的結果的方法。SQL 看起來像是普通的英文語句，有一點點生硬。例如，如果你登入資料庫伺服器，想查看有哪些庫，輸入 SHOW DATABASES; 並回車就能看到結果。

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 使用哪個庫，才能對該庫使用查詢語句。指定資料庫的命令是 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 表中添加一個用戶或使用 SQL 關鍵字 CREATE 來提示 MariaDB 創建一個 MariaDB 用戶。使用 CREATE 來創建新用戶會默認執行一些有用的方法，因此你不需要手動生成所有的信息：

> 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），每一行被創建時會自動生成時間戳。

使用 SQL 關鍵字 INSERT 向這個表填充一些示例數據：

> 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 關鍵字而不是 INSERT。尤其是當你想查詢某行然後再更新某列值時。語法上，表達方式有點倒裝，先更新後查詢：

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

要填充數據，請對其他名字重複執行這個過程。為了數據的多樣性，在四行記錄中分配三個不同的值。

連接表

現在這兩個表彼此有了關聯，你可以使用 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 你可以動態地關聯它們。

下載 MariaDB 和 MySQL 備忘單

MariaDB 是企業級的資料庫。它是健壯、強大、高效的資料庫引擎。學習它是你向管理 web 應用和編寫語言庫邁出的偉大的一步。你可以下載 MariaDB 和 MySQL 備忘單，在你使用 MariaDB 時可以快速參考。

via: https://opensource.com/article/20/10/mariadb-mysql-cheat-sheet

作者：Seth Kenlon 選題：lujun9972 譯者：lxbwolf 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive

對這篇文章感覺如何？

太棒了
0
不錯
0
愛死了
0
不太好
0
感覺很糟
0
雨落清風。心向陽
    Previous
    Next

    You may also like

    Leave a reply

    您的電子郵箱地址不會被公開。 必填項已用 * 標註

    此站點使用Akismet來減少垃圾評論。了解我們如何處理您的評論數據。

    More in:Linux中國