MySQL 命令列實戰大全：連線、帳號、資料庫操作、匯入匯出與進階技巧

一、連線 MySQL

# 本機
mysql -u root -p
mysql -uroot -p123456  # 不推薦明文密碼

# 遠端
mysql -h 110.110.110.110 -u root -p

# 指定資料庫直接進入
mysql -u root -p db_name

# 退出
exit / quit / \q

二、修改密碼

# mysqladmin（舊版/通用）
mysqladmin -u root -p'oldpass' password 'newpass'

# SQL（5.7+ 推薦）
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';
FLUSH PRIVILEGES;

# 8.0 指定驅動
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpass';

三、帳號管理

-- 建立用戶（8.0）
CREATE USER 'test1'@'%' IDENTIFIED BY 'abc';

-- 授權：全庫全權限（危險）
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'test1'@'%';

-- 授權：特定庫、本地連線（安全）
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY 'abc';

-- 移除密碼
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'test2'@'localhost' IDENTIFIED BY '';

-- 撤銷/刪除
REVOKE ALL ON *.* FROM 'test1'@'%';
DROP USER 'test1'@'%';
FLUSH PRIVILEGES;

四、操作技巧

• 命令未輸入分號直接換行 → 輸入 ; 繼續執行
• 上下鍵調取歷史命令
• \G 結尾垂直顯示結果（適合寬表）
• \c 取消當前命令輸入

五、資料庫操作

SHOW DATABASES;
USE db_name;
SHOW TABLES;
DESCRIBE tbl_name;
SHOW CREATE TABLE tbl_name;

CREATE DATABASE db_name;
DROP DATABASE db_name;

CREATE TABLE tbl (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
DROP TABLE tbl;
TRUNCATE TABLE tbl;
DELETE FROM tbl;

SELECT * FROM tbl;
SELECT * FROM tbl WHERE id=1;

六、匯入匯出

# 匯出
mysqldump -u root -p db_name > backup.sql
mysqldump -u root -p db_name tbl1 tbl2 > tables.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --single-transaction --master-data=2 db_name | gzip > backup.sql.gz

# 匯入
mysql -u root -p db_name < backup.sql
mysql -u root -p < all.sql

# 客戶端內匯入
SOURCE /path/to/backup.sql;

七、進階管理範例

# 建立 root 等效超級用戶
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

# 僅資料操作權限
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'rw_user'@'%' IDENTIFIED BY 'pass';

# 特定 IP、特定表
GRANT SELECT,INSERT,UPDATE,DELETE ON student.student TO 'user'@'192.168.1.100' IDENTIFIED BY 'pass';

# 查看當前用戶/版本/時間
SELECT USER(), VERSION(), NOW();

# 刷新權限
FLUSH PRIVILEGES;

八、資料表結構變更

-- 新增欄位
ALTER TABLE tbl ADD COLUMN col_name VARCHAR(10) DEFAULT '';

-- 修改欄位名/型別/預設值
ALTER TABLE tbl CHANGE old_col new_col INT(3) UNSIGNED DEFAULT 1;

-- 修改預設值
ALTER TABLE tbl ALTER COLUMN col_name SET DEFAULT '0';

-- 修改型別
ALTER TABLE tbl MODIFY COLUMN col_name VARCHAR(20);

-- 新增主鍵
ALTER TABLE tbl ADD COLUMN id INT(10) AUTO_INCREMENT PRIMARY KEY;

-- 重命名表
ALTER TABLE tbl RENAME new_tbl;

九、資料操作範例

INSERT INTO tbl VALUES ('value1', 'M');
UPDATE tbl SET sex='F' WHERE name='value1';
DELETE FROM tbl WHERE id=1;

-- 表間資料拼接插入
INSERT INTO tx SELECT t1.col1, CONCAT(t1.col2, t2.col1) FROM t1, t2;

十、常見問題排查