MySQL 常用 Command Line 指令

MySQL 常用 Command Line 指令

本文所列為在 Ubuntu 環境下一些 MySQL 常用 Command Line 指令, 包含 mysql 介面的指令如 help, show, ... 等等, 及標準的 SQL 語法如 create, select, update, delete, ... 等等。

順道一提 MariaDB 是 MySQL 非 Oracle 管轄的版本, 幾乎 100% 相容於 mysql (介面指令也用 mysql), 雖然底層有用到不同技術, 但應用層面上可完全將兩者都當做是 MySQL 來使用。

---

MySQL 介面指令說明, 指令後不可以加註解, 指令可不用加結尾分號

mysql> help  
mysql> help functions
mysql> help contents
mysql> help <content_category_item>
mysql> help <conntet_topic_of_category_item>
mysql> help '%search_string%'

基本上, 非執行 SQL 查詢的 MySQL 介面指令都不需要以分號結尾。

---

Basic Information

Starting point:

# schema
mysql> SHOW {DATABASES | SCHEMAS};
mysql> SELECT * FROM information_schema.SCHEMATA;
mysql> SHOW CREATE DATABASE myDbName;

# table
mysql> SHOW TABLES [{FROM | IN} myDbName];
mysql> SELECT * FROM information_schem.TABLES WHERE TABLE_SCHEMA = myDbNAME;
mysql> SHOW CREATE TABLE myDbName.myTblName;
mysql> DESC myDbName.myTblName;

# column
mysql> SHOW COLUMNS {FROM | IN} myTblName {FROM | IN} myDbName;
mysql> SHOW COLUMNS {FROM | IN} myDbName.myTblName;
mysql> SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = myDbName AND TABLE_NAME = myTblName;
mysql> DESC myDbName.myTblName myColName;

查詢資料庫預設 character set 及 collation

# schema
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "schemaname";

# table 
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "schemaname" AND T.table_name = "tablename";

# column
SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "schemaname" AND table_name = "tablename" AND column_name = "columnname";

---

EXPLAIN, DESCRIBE, DESC, SHOW COLUMNS:

mysql> EXPLAIN myTable;                    # EXPLAIN = DESCRIBE = DESC
mysql> SHOW COLUMNS FROM myTable;
mysql> EXPLAIN myTable myField;
mysql> EXPLAIN myTable '%search_string%';

---

USE

USE 告知 client 端之後的 SQL 語法若未指定資料庫名稱,則都是針對所選定的資料庫進行的。

mysql> USE myDatabase

---

CREATE

CREATE DATABASE myDataBASE CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE TABLE myTableName (myField1Name myField1Type, myField2Name myField2Type);

# create table example
CREATE TABLE birds (
bird_id INT AUTO_INCREMENT PRIMARY KEY,
scientific_name VARCHAR(255) UNIQUE,
common_name VARCHAR(50),
family_id INT,
description TEXT
);

---

INSERT

INSERT INTO myTable VALUES(myField1Value, myField2Value, myField3Value);
INSERT INTO myTable VALUES(myRow1Fld1, myRow1Val1), (myRow2Fld1,myRow2Val1);

# insert data example
INSERT INTO birds (scientific_name, common_name)
VALUES ('Charadrius vociferus', 'Killdear'),
('Gavia immer', 'Great Northern Loon');

---

SELECT

SELECT * FROM myTable;
SELECT * FROM myTable\c               # 語尾加 \c, 放棄本次輸入的語法
SELECT * FROM myTable\G               # 語尾加 \G, 查詢結果逐欄列行顯示
SELECT * FROM myTable WHERE myField = myFieldValue;
SELECT myTbl1Fld, myTbl2Fld FROM myTbl1 JOIN myTbl2 WHERE myJoinFld1InTbl1 = myJoinFld1InTbl2;
SELECT myTbl1Fld, myTbl2Fld FROM myTbl1 JOIN myTbl2 WHERE myJoinFld1InTbl2 = myJoinFld1InTbl1;

---

UPDATE

UPDATE myTable SET myField = myNewValue WHERE myCondField = myCondValue;
UPDATE myTable SET mySet1, mySet2, mySetN WHERE myCondField = myCondValue;

---

DROP

DROP DATABASE myDatabase;

---

資料備份

$ mysqldump -uUserID -pPassword db_name > DB_BackupFileName.sql
$ mysqldump -uUserID -pPassword db_name table_name > Table_BackupFileName.sql

---

資料倒帶

小心使用, 會重建 table 並重填資料!

$ mysql -uUserID -pPassword db_name < DB_or_Table_BackupFileName.sql

---

複製 Table 及資料

CREATE TABLE table_new LIKE table_old;
INSERT INTO table_new SELECT * FROM table_old;
或
CREATE TABLE table_new SELECT * FROM table_old;

---

ALERT TABLE

ALERT TABLE table_name
DROP COLUMN column_name,
ADD COLUMN column_name column_type,
ADD COLUMN column_name column_type AFTER another_column_name,
CHANGE COLUMN column_name change_to_column_name change_to_column_type,
CHANGE COLUMN old_column_name new_column_name new_column_type DEFAULT defautl_value,
ALTER column_name SET DEFAULT default_value,
ALTER column_name DROP DEFAULT;

---

AUTO_INCREMENT

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = myTableName;
ALTER TABLE myTableName AUTO_INCREMENT = new_value;

---

參考資料:

Learning MySQL and MariaDB, Russell J.T. Dyer, O'REILLY