[教學] MariaDB/MySQL 常用指令操作與語法範例

魏子靖

「MariaDB」相對於MySQL來說，大家應該比較陌生，但其實二者可以說是系出同門，簡單的來說，MariaDB是MySQL的一個分支，完全免費，有興趣了解他們的恩怨情仇(誤)的讀者可以直接前往WiKi查看，在此就不多贅言。本文的重點是在記錄一些自己在學習的過程中所常用的一些指令，除了當自己的備忘錄外，也順便提供給初學者一個比較快速的入門參考


【MariaDB下載安裝】
在「下載頁面」中，MariaDB提供了許多不同平台的安裝檔，大家可以取適合自己的版本，因我個人都是在Linux的平台上操作，所以我習慣到「repositories」頁面直接採用官方提供的方法來安裝：第(1)步選擇使用哪一種Linux平台，再來選擇版本、和MariaDB的版本後，下方就會自動出現安裝的步驟，只要跟著網頁的步驟就可以順利安裝

安裝過程中會跳出視窗讓用戶輸入root的密碼

【常用指令】
登入資料庫

mysql -u root -p
  • -u：指定使用者
  • -p：代表密碼，後面如果沒有直接輸入密碼，那系統會出現提示訊息讓用戶輸入，這種方式可以避免密碼以明文的方式呈現。如果要直接以指令上輸入密碼，請直接連在「-p」後面，中間不要有空格，例如：mysql -u root -p12345

登入後即可以由歡迎畫面看到MariaDB的版本

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.2.6-MariaDB-10.2.6+maria~xenial-log mariadb.org binary distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

查詢資料庫版本
除了利用登入時的資訊可以看到版本號外，也可以在登入MariaDB後，執行以下的語法來查詢

select version();

建立資料庫
none代表目前沒有實質使用任何的資料庫，以下指令為建立一個名為「demo」的資料庫

MariaDB [(none)]> create database demo;

查看已建立的資料庫

MariaDB [(none)]> show databases;

使用某個資料庫(進入)
進入資料庫後，none會變成目前登入的資料庫名字

MariaDB [(none)]> use demo;
Database changed
MariaDB [demo]>

建立表格
建立一個名為「result」的表格，由於我是用複製貼上的方式來建立表格，所以才會出現「->」這個符號，因為前幾行我還沒有用「分號」來做結尾

MariaDB [(none)]> use demo;
Database changed
MariaDB [demo]> create table result (
    ->       event_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->       status varchar(30),
    ->       date datetime,
    ->       primary key (event_id)
    ->  );

查看已建立的表格

MariaDB [demo]> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| result         |
+----------------+
1 row in set (0.00 sec)

更新root密碼
登入資料庫後，執行以下的更新步驟：

use mysql;
update user set password=PASSWORD("新密碼") where User='root';
flush privileges;
quit

刪除表格資料
是刪除表格內的資料，不是表格本身喔

truncate table table_name;
或
delete from table_name;

若要刪除某幾列的資料，則可以加上一些篩選條件，例如：

delete from table_name where column_name = "value";

刪除資料庫

drop database database_name;

刪除表格

drop table table_name;

查詢表格欄位資訊(一)
有時想要查當初建立表格時的欄位資訊，可以使用此指令來查詢

show columns from table_name from database_name;
或
show columns from database_name.table_name;

呈現的畫面如下：(以下資訊取自官網)

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

查詢表格欄位資訊(二)
和使用「show columns」的指令一樣，但比較簡潔一點，說明可參考官方文件

describe table_name;
或
desc table_name;

查詢使用者及登入的主機位置
登入MariaDB後，執行以下指令查詢：

select user, host from mysql.user;

查詢的結果範例如下：

+---------+-----------------------+
| user    | host                  |
+---------+-----------------------+
| test123 | %                     |
| root    | %                     |
| root    | 127.0.0.1             |
| root    | ::1                   |
| root    | localhost             |
| test456 | localhost             |
| root    | localhost.localdomain |
+---------+-----------------------+

變更使用者名稱
承上，透過查詢使用者以及登入主機後，可以藉由這二個資訊來變更使用者名稱，例如為了安全性考量，通常建議不要使用預設的root帳號來登入資料庫，因此可以直接使用以下的指令範例將帳號「root」更名為「iamghost」

rename user 'root'@'127.0.0.1' to 'iamghost'@'127.0.0.1';

建立使用者
建立使用者，我們可以授權該此使用者不同的權限去存取資料庫

  • username：使用者的帳號
  • hostname：由哪個主機登入，一般來說會直接指定localhost，如果要遠端登入的話，則會對給予適當的IP，若是將此值設為「%」，則表示允許任何主機位置連線
  • user-name-password：使用者密碼
create user 'username'@'hostname' identified by 'user-name-password';

建立使用者之後，必需授權才可以存取資料庫，所以如果你以新使用者登入，並查詢可用的資料庫時，你會發現沒有之前建立的資料庫

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

授與使用者權限
比較常用的指令如下，此範例代表授權「’username’@’hostname’」這個使用者可以使用「dbname」這個資料庫下的所有表格

grant all on dbname.* to 'username'@'localhost';
或
grant SELECT,INSERT,UPDATE,DELETE ON dbname.* to 'username'@'hostname';

查詢使用者權限
使用者和Host的資訊，可以藉由上面查詢使用者資訊的範例指令取得，接下來就可以組成「’user’@’hostname’」的格式來查詢權限

show grants for 'user'@'hostname';

查詢目前使用者的權限

show grants;
或
SHOW GRANTS FOR CURRENT_USER;
或
SHOW GRANTS FOR CURRENT_USER();

移除使用者權限
一般性的語法架構如下，基本上可以先查出之前Grant了什麼權限給user，然後再依此撤銷

revoke all privileges on dbname.table from 'username'@'hostname'
或
revoke all privileges, grant option from 'username'@'hostname';

變更使用者權限
基本上就是先移除原本授予的權限，然後再重新授權
刪除使用者

drop user 'username'@'hostname';

【CentOS安裝MariaDB：補充資料】
快速安裝

yum install mariadb-server

啟動MariaDB Server

systemctl start mariadb.service

設定開機時自動啟動MariaDB

systemctl enable mariadb.service

安全性設定

mysql_secure_installation

該設定時會詢問幾個問題，簡述如下：

  • Enter current password for root (enter for none)：輸入root密碼，第一次設定時預設值是空的，所以直接按Enter即可，接著會詢問是否要設定root密碼，請輸入「Y」進行設定
  • Remove anonymous users? [Y/n]：是否要移除匿名使用者？建議輸入「Y」來移除
  • Disallow root login remotely? [Y/n]：是否要關閉root遠端登入的功能？依自己需求決定，一般基於安全性考量，建議輸入「Y」來關閉
  • Remove test database and access to it? [Y/n]：是否要移除測試的資料庫？建議選擇「Y」來移除
  • Reload privilege tables now? [Y/n]：是否要重新載入表格權限？建議選擇「Y」

若不執行 mysql_secure_installation 指令，因MariaDB在「CentOS」中安裝時，過程不會跳出詢問root密碼的視窗，其預設密碼為空，因此可以直接執行以下的指令按Enter直接登入MariaDB

mysql -u root -p

延伸閱讀：
．[Mariadb] 如何解決資料庫中文亂碼(問號)的編碼問題
．[教學] MariaDB/MySQL備份 – 如何匯出、匯入資料庫或表格