[教學] 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備份 – 如何匯出、匯入資料庫或表格