終端機操作 MySQL(MariaDB) 指令~

平時習慣透過介面方式來管理資料庫，趁著假日期間再來玩一下指令操作模式，

順道記錄一些透過終端機介面操作MySQL (or mariaDB) 的方式，便於之後再參考

(如果在windows 環境，請先將 mysql (…\mysql\bin) 加入環境變數中)

在這裡著重在操作及設定方面，不會提到太多CRUD相關操作

啟用 MySQL 服務

開啟終端機，輸入以下指令，啟用 mysql 服務

mysqld

終端機視窗要持續開啟才行

登入 MySQL

開啟新的終端機視窗，登入MySQL

C:>mysql -u 使用者帳號 -p密碼

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

退出 MySQL

MariaDB [(none)]> exit
Bye

檢查使用者及資料庫版本

檢查資料庫版本

MariaDB [example]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.13-MariaDB |
+-----------------+
1 row in set (0.00 sec)

檢查目前登入使用者

MariaDB [example]> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

顯示目前資料庫支援的儲存引擎

一般來說，最常使用的是 MyISAM 與 InnoDb

MariaDB [example]>SHOW ENGINES \G

顯示已經建立的 database

MariaDB [(none)]> SHOW databases;
+--------------------------+
| Database                 |
+--------------------------+
| example                  |
+--------------------------+
1 rows in set (0.00 sec)

資料庫(database)

建立資料庫

建立 example 資料庫

MariaDB [(none)]>CREATE DATABASE example;

刪除資料庫

MariaDB [(none)]> DROP DATABASE example;
Query OK, 1 row affected (0.07 sec)

選擇資料庫

MariaDB [zsystem]> USE example;
Database changed

資料表(table)

建立資料表

MariaDB [example]> create table users(name varchar(10), join_date date);
Query OK, 0 rows affected (0.06 sec)

可同時指定儲存引擎、語系編碼

MariaDB [example]> create table users(name varchar(10), join_date date) ENGINE=myisam DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

查看資料表

MariaDB [example]> DESC users;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(10) | YES  |     | NULL    |       |
| join_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

或者用下面方式顯示更詳細的資訊

MariaDB [example]> SHOW CREATE TABLE users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `name` varchar(10) DEFAULT NULL,
  `join_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

刪除資料表

MariaDB [example]> DROP table users;
Query OK, 0 rows affected (0.02 sec)

清空資料表內資料

MariaDB [example]> DELETE FROM users;
Query OK, 3 rows affected (0.02 sec)

修改資料表

將 name 欄位調整為 varchar(20)

MariaDB [example]> ALTER TABLE users MODIFY name varchar(20);

Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

驗證結果

MariaDB [example]> DESC users;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| join_date | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

變更欄位名稱，將 name 改名為 names

MariaDB [example]> ALTER TABLE users CHANGE name names varchar(10);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

變更資料表名稱，將 users 改名為 user

MariaDB [example]> ALTER TABLE users RENAME user;

Query OK, 0 rows affected (0.02 sec)

變更資料表儲存引擎

MariaDB [example]> ALTER TABLE user ENGINE = myisam;

Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

資料表基本 CRUD

CRUD 大概就再看一下吧~ 或直接跳過亦可

insert

MariaDB [example]> INSERT INTO user (`names`, `join_date`) VALUES('adam','2018-01-01');
Query OK, 1 row affected (0.00 sec)

select

MariaDB [example]> SELECT * FROM user;
+-------+------------+
| names | join_date  |
+-------+------------+
| adam  | 2018-01-01 |
+-------+------------+
1 row in set (0.00 sec)

update

MariaDB [example]> UPDATE user SET names='Adam', join_date='2018-02-01' WHERE names='adam';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

delete

MariaDB [example]> DELETE FROM user WHERE names='Adam';
Query OK, 1 row affected (0.00 sec)

取得最後一筆插入資料

用 LAST_INSERT_ID() 來取得最後一筆插入的ID；如果一次插入多筆值，則取當時插入的第一條紀錄的ID。

CREATE TABLE custom(id int(10) NOT NULL AUTO_INCREMENT, name varchar(10), PRIMARY KEY(id)) ENGINE=innodb;
Query OK, 0 rows affected (0.06 sec)

MariaDB [example]> INSERT INTO custom(`name`) VALUES('robin'),('eric'),('brown');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [example]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

MariaDB [example]> INSERT INTO custom(`name`) VALUES('tonny');
Query OK, 1 row affected (0.01 sec)

MariaDB [example]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

DCL 語句

建立使用者及設定資料庫權限

DBA 常用來管理開設帳號及設定權限的方式，使用方式如下:

建立一個 test 使用者，密碼為 123456，僅具有example資料庫的select及insert 權限

MariaDB [example]> CREATE USER 'test'@'localhost' IDENTIFIED by '123456'; 

Query OK, 0 rows affected (0.00 sec)

MariaDB [example]> GRANT SELECT, INSERT ON example.* TO 'test'@'localhost';

Query OK, 0 rows affected (0.00 sec)

回收特定權限

回收 test 使用者的 insert 權限

MariaDB [(none)]> revoke insert on example.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

接下來，若插入資料則會出現權限不足的警示

MariaDB [example]> insert into user (names, join_date) values('b','2018-02-01');
ERROR 1142 (42000): INSERT command denied to user 'test'@'localhost' for table 'user'

查詢參考

MySQL 內建說明可以方便快速查詢功能，一般來說都是用問號+要查詢的關鍵字來查詢

例如，透過 ?+content 可以顯示目前提供的查詢分類

MariaDB [example]> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

從這些分類中，例如，想要查詢MySQL支援那些資料類型，可以執行 ? Data Types

MariaDB [example]> ? DATA Types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   ...

同樣的，如果要知道某個指令有哪些功能，怎麼使用，也可以用 ?+指令

例如，查詢 show 指令能看到甚麼，只要執行 ?+show

MariaDB [example]> ? show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
...

優化資料表

在使用 MyISAM 資料表時，頻繁更新、刪除動作會產生碎片化的問題，需要在每隔一段時間執行優化指令

MariaDB [example]> OPTIMIZE TABLE user;

檢查及修復

透過 CHECK TABLE 可以檢查資料表狀態

MariaDB [example]> CHECK TABLE user;

+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| example.user | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

若表格有損壞，可以用 REPAIR TABLE 來修復損壞的資料表

MariaDB [example]> REPAIR TABLE user;

+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| example.user | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.03 sec)

錯誤排解

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061 “Unknown error”)

請先確認是否有正常啟用 MySQL 服務

**ERROR 1142 (42000): INSERT command denied to XXX **

無 INSERT 權限