備忘單：提升你的 MariaDB 和 MySQL 數(shù)據(jù)庫(kù)技能
當(dāng)你寫一個(gè)程序或配置一個(gè)服務(wù)時(shí)，你最終都要持久化存儲(chǔ)信息。有時(shí)候，你只需要一個(gè) INI 或者 YAML 配置文件就夠了。而有時(shí)候，一個(gè)自定義格式的 XML 或者 JSON 或其他類似的文件會(huì)更好。
但也有時(shí)候你需要校驗(yàn)輸入、快速查詢信息、關(guān)聯(lián)數(shù)據(jù)、通常還要熟練地處理你的用戶的請(qǐng)求。這就是設(shè)計(jì)數(shù)據(jù)庫(kù)的目的，而 MariaDB(由 MySQL 的原始開(kāi)發(fā)人員開(kāi)發(fā)的一個(gè)分支) 是一個(gè)極佳的選項(xiàng)。在本文中我使用的是 MariaDB，但這些信息同樣適用于 MySQL。
通過(guò)編程語(yǔ)言與數(shù)據(jù)庫(kù)進(jìn)行交互是很普遍的。正因如此，出現(xiàn)了大量 Java、Python、Lua、PHP、Ruby、C++ 和其他語(yǔ)言的 SQL 庫(kù)。然而，在使用這些庫(kù)之前，理解數(shù)據(jù)庫(kù)引擎做了什么以及為什么選擇數(shù)據(jù)庫(kù)是重要的對(duì)我們會(huì)很有幫助。本文介紹 MariaDB 和 mysql 命令來(lái)幫助你熟悉數(shù)據(jù)庫(kù)處理數(shù)據(jù)的基本原理。
如果你還沒(méi)有安裝 MariaDB，請(qǐng)查閱我的文章 在 Linux 上安裝 MariaDB。如果你沒(méi)有使用 Linux，請(qǐng)參照 MariaDB 下載頁(yè)面提供的指導(dǎo)方法。
一、與 MariaDB 交互
你可以使用 mysql 命令與 MariaDB 進(jìn)行交互。首先使用子命令 ping 確認(rèn)你的服務(wù)是運(yùn)行著的，在提示后輸入密碼：
- $ mysqladmin -u root -p ping
- Enter password:
- mysqld is alive
為了易于讀者理解，打開(kāi)一個(gè)交互式的 MariaDB 會(huì)話：
- $ 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)]>
你現(xiàn)在是在一個(gè) MariaDB 子 shell 中，提示符是 MariaDB 提示符。普通的 Bash 命令在這里不能使用，只能用 MariaDB 命令。輸入 help (或 ?)查看命令列表。這些是你的 MariaDB shell 的管理命令，使用它們可以定制你的 shell，但它們不屬于 SQL 語(yǔ)言。
二、學(xué)習(xí) SQL 基本知識(shí)
結(jié)構(gòu)化查詢語(yǔ)言是基于它們的能力定義的：一種通過(guò)有規(guī)則且一致的語(yǔ)法來(lái)查詢數(shù)據(jù)庫(kù)中的內(nèi)容以得到有用的結(jié)果的方法。SQL 看起來(lái)像是普通的英文語(yǔ)句，有一點(diǎn)點(diǎn)生硬。例如，如果你登入數(shù)據(jù)庫(kù)服務(wù)器，想查看有哪些庫(kù)，輸入 SHOW DATABASES; 并回車就能看到結(jié)果。
SQL 命令以分號(hào)作為結(jié)尾。如果你忘記輸入分號(hào)，MariaDB 會(huì)認(rèn)為你是想在下一行繼續(xù)輸入你的查詢命令，在下一行你可以繼續(xù)輸入命令也可以輸入分號(hào)結(jié)束命令。
- MariaDB [(NONE)]> SHOW DATABASES;
- +--------------------+
- | DATABASE |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 ROWS IN SET (0.000 sec)
上面的例子顯示當(dāng)前有四個(gè)數(shù)據(jù)庫(kù)：information_schema、mysql、performance_schema 和 test。你必須指定 MariaDB 使用哪個(gè)庫(kù)，才能對(duì)該庫(kù)使用查詢語(yǔ)句。指定數(shù)據(jù)庫(kù)的命令是 use。當(dāng)你選擇了一個(gè)庫(kù)后，MariaDB 提示框會(huì)切換為選擇的庫(kù)。
- MariaDB [(NONE)]> USE test;
- MariaDB [(test)]>
1. 顯示數(shù)據(jù)庫(kù)的表
數(shù)據(jù)庫(kù)里有表，與電子表格類似：有一系列的行(在數(shù)據(jù)庫(kù)中稱為記錄)和列。一個(gè)行和一個(gè)列唯一確定一個(gè)字段。
查看一個(gè)數(shù)據(jù)庫(kù)中可用的表(可以理解為多表單電子表格中的一頁(yè))，使用 SQL 關(guān)鍵字 SHOW：
- MariaDB [(test)]> SHOW TABLES;
- empty SET
test 數(shù)據(jù)庫(kù)是空的，所以使用 use 命令切換到 mysql 數(shù)據(jù)庫(kù)：
- 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)
這個(gè)數(shù)據(jù)庫(kù)中有很多表!mysql 數(shù)據(jù)庫(kù)是這個(gè) MariaDB 實(shí)例的系統(tǒng)管理數(shù)據(jù)庫(kù)。它里面包含重要數(shù)據(jù)，比如用來(lái)管理數(shù)據(jù)庫(kù)權(quán)限的用戶結(jié)構(gòu)。這個(gè)數(shù)據(jù)庫(kù)很重要，你不需要經(jīng)常直接與它交互，但是使用 SQL 腳本來(lái)操作它卻很常見(jiàn)。當(dāng)你學(xué)習(xí) MariaDB 時(shí)理解 mysql 數(shù)據(jù)庫(kù)很有用，因?yàn)樗兄谡f(shuō)明一些基本的 SQL 命令。
2. 檢查一個(gè)表
這個(gè)實(shí)例的 mysql 數(shù)據(jù)庫(kù)的最后一個(gè)表名為 USER。這個(gè)表包含了可以訪問(wèn)這個(gè)數(shù)據(jù)庫(kù)的用戶。當(dāng)前里面只有一個(gè) root 用戶，但是你可以添加不同權(quán)限的用戶，賦予它們查看、更新或創(chuàng)建數(shù)據(jù)的權(quán)限。你可以查看一個(gè)表的列首來(lái)了解一個(gè) 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)
3. 創(chuàng)建一個(gè)新的用戶
不論你是否需要一個(gè)普通的賬號(hào)來(lái)管理數(shù)據(jù)庫(kù)或者為計(jì)算機(jī)配置數(shù)據(jù)庫(kù)(例如安裝 WordPress、Drupal 或 Joomla時(shí))，在 MariaDB 中多建一個(gè)用戶賬號(hào)是很普遍的。你可以通過(guò)向 mysql 數(shù)據(jù)庫(kù)的 USER 表中添加一個(gè)用戶或使用 SQL 關(guān)鍵字 CREATE 來(lái)提示 MariaDB 創(chuàng)建一個(gè) MariaDB 用戶。使用 CREATE 來(lái)創(chuàng)建新用戶會(huì)默認(rèn)執(zhí)行一些有用的方法，因此你不需要手動(dòng)生成所有的信息：
- > CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';
4. 查看表的字段
你可以使用 SELECT 關(guān)鍵字來(lái)查看數(shù)據(jù)庫(kù)表的字段和值。這本例中，你創(chuàng)建了一個(gè)名為 tux 的用戶，因此查詢 USER 表中的列：
- > SELECT USER,host FROM USER;
- +------+------------+
- | USER | host |
- +------+------------+
- | root | localhost |
- [...]
- | tux | localhost |
- +------+------------+
- 7 ROWS IN SET (0.000 sec)
5. 為一個(gè)用戶賦予權(quán)限
通過(guò)查看 USER 表列出的信息，你可以看到用戶的狀態(tài)。例如，新用戶 tux 對(duì)這個(gè)數(shù)據(jù)庫(kù)沒(méi)有任何權(quán)限。使用 WHERE 語(yǔ)句你可以只查 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 命令修改用戶的權(quán)限：
- > GRANT SELECT ON *.* TO 'tux'@'localhost';
- > FLUSH PRIVILEGES;
驗(yàn)證你的修改：
- > 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 用戶現(xiàn)在有了從所有表中查詢記錄的權(quán)限。
三、創(chuàng)建自定義的數(shù)據(jù)庫(kù)
到目前為止，你一直在與默認(rèn)的數(shù)據(jù)庫(kù)進(jìn)行交互。除了用戶管理，大部分人很少會(huì)與默認(rèn)的數(shù)據(jù)庫(kù)進(jìn)行交互。通常，你會(huì)用自定義的數(shù)據(jù)來(lái)填充創(chuàng)建的數(shù)據(jù)庫(kù)。
1. 創(chuàng)建一個(gè) MariaDB 數(shù)據(jù)庫(kù)
你可能已經(jīng)可以自己在 MariaDB 中創(chuàng)建新數(shù)據(jù)庫(kù)了。創(chuàng)建數(shù)據(jù)庫(kù)跟新建用戶差不多。
使用 use 命令來(lái)把這個(gè)新建的數(shù)據(jù)庫(kù)作為當(dāng)前使用的庫(kù)：
- > CREATE DATABASE example;
- Query OK, 1 ROW affected (0.000 sec)
- > SHOW DATABASES;
- +--------------------+
- | DATABASE |
- +--------------------+
- | example |
- [...]
使用 use 命令來(lái)把這個(gè)新建的數(shù)據(jù)庫(kù)作為當(dāng)前使用的庫(kù)：
- > USE example;
2. 創(chuàng)建一個(gè)表
創(chuàng)建表比創(chuàng)建數(shù)據(jù)庫(kù)要復(fù)雜，因?yàn)槟惚仨毝x列首。MariaDB 提供了很多方便的函數(shù)，可以用于創(chuàng)建列，引入數(shù)據(jù)類型定義，自增選項(xiàng)，對(duì)空值的約束，自動(dòng)時(shí)間戳等等。
下面是用來(lái)描述一系列用戶的一個(gè)簡(jiǎn)單的表：
- > 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)
這個(gè)表通過(guò)使用一個(gè)自動(dòng)遞增的方法來(lái)唯一標(biāo)識(shí)每一行。表示用戶名字的字段不能為空(或 null)，每一行被創(chuàng)建時(shí)會(huì)自動(dòng)生成時(shí)間戳。
使用 SQL 關(guān)鍵字 INSERT 向這個(gè)表填充一些示例數(shù)據(jù)：
- > 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)
驗(yàn)證一下表里的數(shù)據(jù)：
- > 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)
3. 同時(shí)增加多行數(shù)據(jù)
再創(chuàng)建一個(gè)表：
- > 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)
填充一些示例數(shù)據(jù)，這次使用 VALUES 快捷方式，這樣你可以一次添加多行數(shù)據(jù)。VALUES 關(guān)鍵字需要一個(gè)用括號(hào)包圍的列表作為參數(shù)，也可以用逗號(hào)分隔的多個(gè)列表作為參數(shù)。
- > 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 |
- +----+-----------+
四、關(guān)聯(lián)多個(gè)表
現(xiàn)在你有兩個(gè)表，之間沒(méi)有關(guān)聯(lián)。兩個(gè)表的數(shù)據(jù)是獨(dú)立的，但是你可能需要表一中的一個(gè)值來(lái)識(shí)別表二的記錄。
你可以在表一中新增一列對(duì)應(yīng)表二中的值。因?yàn)閮蓚€(gè)表都有唯一的標(biāo)識(shí)符(自動(dòng)遞增的 id 字段)，關(guān)聯(lián)的它們的最簡(jiǎn)單的方式是，使用表一中的 id 字段作為表二的查詢條件。
在表一中創(chuàng)建一列用來(lái)表示表二中的一個(gè)值：
- > 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 分配給每個(gè)成員。因?yàn)橛涗浺呀?jīng)存在，使用 UPDATE 關(guān)鍵字而不是 INSERT。尤其是當(dāng)你想查詢某行然后再更新某列值時(shí)。語(yǔ)法上，表達(dá)方式有點(diǎn)倒裝，先更新后查詢：
- > UPDATE member SET os=1 WHERE name='Alice';
- Query OK, 1 ROW affected (0.007 sec)
- ROWS matched: 1 Changed: 1 Warnings: 0
要填充數(shù)據(jù)，請(qǐng)對(duì)其他名字重復(fù)執(zhí)行這個(gè)過(guò)程。為了數(shù)據(jù)的多樣性，在四行記錄中分配三個(gè)不同的值。
連接表：
現(xiàn)在這兩個(gè)表彼此有了關(guān)聯(lián)，你可以使用 SQL 來(lái)展示關(guān)聯(lián)的數(shù)據(jù)。數(shù)據(jù)庫(kù)中有很多種連接方式，你可以盡請(qǐng)嘗試。下面的例子是關(guān)聯(lián) 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 字段。
在圖形化的應(yīng)用中，你可以想象 os 字段可以在下拉菜單中設(shè)置，值的來(lái)源是 linux 表中的 distro 字段。通過(guò)使用多個(gè)表中獨(dú)立卻有關(guān)聯(lián)的數(shù)據(jù)，你可以保證數(shù)據(jù)的一致性和有效性，使用 SQL 你可以動(dòng)態(tài)地關(guān)聯(lián)它們。
五、下載 MariaDB 和 MySQL 備忘單
MariaDB 是企業(yè)級(jí)的數(shù)據(jù)庫(kù)。它是健壯、強(qiáng)大、高效的數(shù)據(jù)庫(kù)引擎。學(xué)習(xí)它是你向管理 web 應(yīng)用和編寫語(yǔ)言庫(kù)邁出的偉大的一步。你可以下載 MariaDB 和 MySQL 備忘單，在你使用 MariaDB 時(shí)可以快速參考。
