您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 常用命令大全：連線、帳號管理、資料庫操作與實用技巧

編輯：MySQL 資料庫相關知識

前言

MySQL 命令列工具是 DBA 與開發人員日常操作的核心介面。本文系統整理連線認證、密碼管理、使用者授權、資料庫/資料表 DDL/DML、查詢技巧等高頻命令，並附上實戰場景與避坑提醒，適合作為桌邊速查手冊。

一、連線與認證

基本語法

mysql -h<主機> -u<用戶> -p<密碼> -P<埠> -D<資料庫>

常見場景

場景	命令範例	說明
本機 root 免密登入	`mysql -uroot -p`	按 Enter 略過密碼（初始安裝）
遠端連線	`mysql -h192.168.1.100 -uroot -p'Pass123' -P3306`	-p 後無空格直接接密碼（不安全，建議互動式輸入）
使用登入路徑 (5.6.6+)	`mysql --login-path=prod`	`mysql_config_editor set --login-path=prod --host=... --user=... --password` 預存憑證
指定預設資料庫	`mysql -D mydb -uroot -p`	等同登入後 `USE mydb;`
執行單條 SQL 並退出	`mysql -uroot -p -e "SELECT VERSION();"`	腳本自動化常用

退出命令

exit 或 quit 或 \q

二、密碼管理

修改當前使用者密碼

# 5.7.6+ 官方推薦
ALTER USER USER() IDENTIFIED BY 'NewPass123!';

# 或使用 SET PASSWORD
SET PASSWORD = 'NewPass123!';

# 5.7.6 前舊語法 (仍相容)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPass123!');

管理員重設其他使用者密碼

ALTER USER 'app_user'@'%' IDENTIFIED BY 'NewPass456!';
FLUSH PRIVILEGES;

根密碼遺忘：緊急重置流程

停止服務：systemctl stop mysqld
跳過授權表啟動：mysqld_safe --skip-grant-tables &
免密登入：mysql -uroot

更新密碼：

USE mysql;
UPDATE user SET authentication_string=PASSWORD('NewRootPass!') WHERE User='root';
FLUSH PRIVILEGES;

正常重啟：systemctl restart mysqld

三、使用者與權限管理

建立使用者 (8.0 語法)

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';

授權模板

需求	GRANT 語句
唯讀存取特定庫	`GRANT SELECT ON mydb.* TO 'readonly'@'%';`
讀寫特定庫	`GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO 'rw_user'@'%';`
全權限 (含授權權限)	`GRANT ALL PRIVILEGES ON . TO 'admin'@'%' WITH GRANT OPTION;`
僅特定表權限	`GRANT SELECT,UPDATE ON mydb.orders TO 'order_svc'@'%';`
僅存儲程序執行權	`GRANT EXECUTE ON mydb.* TO 'proc_user'@'%';`

回收權限

REVOKE INSERT,UPDATE ON mydb.* FROM 'readonly'@'%';

查看權限

SHOW GRANTS FOR 'app_user'@'%';

刪除使用者

DROP USER 'old_user'@'%';

安全最佳實踐

最小權限原則：禁止使用 '%' 通配符帳號存取生產核心庫。
按服務拆分帳號：每個微服務/應用獨立帳號，便於審計與撤銷。
定期輪換密碼：配合 PASSWORD EXPIRE INTERVAL 90 DAY (8.0+) 強制輪換。
啟用密碼複雜度驗證：安裝 validate_password 元件。

四、資料庫與資料表操作 (DDL)

資料庫級

SHOW DATABASES;                    -- 列出所有庫
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE mydb;                -- 謹慎！不可逆
USE mydb;                          -- 切換當前庫

資料表級

SHOW TABLES;                       -- 列出當前庫所有表
DESCRIBE users;                    -- 表結構 (等同 SHOW COLUMNS FROM users)
SHOW CREATE TABLE users;           -- 完整建表 SQL (含索引、引擎、編碼)

CREATE TABLE logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    level ENUM('INFO','WARN','ERROR') NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_level_created (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL;
DROP TABLE old_table;

五、資料操作 (DML) 與查詢技巧

基本增刪改查

INSERT INTO users(name,email) VALUES('張三','zhang@example.com');
INSERT INTO users(name,email) VALUES('李四','li@example.com'),('王五','wang@example.com'); -- 批次插入

UPDATE users SET email='new@example.com' WHERE id=1;
DELETE FROM users WHERE id=1;               -- 條件刪除
TRUNCATE TABLE logs;                        -- 快速清空表 (DDL、不可回滾、重置 AUTO_INCREMENT)

SELECT * FROM users WHERE id=1;
SELECT id,name FROM users WHERE status=1 ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- 分頁

實用查詢技巧

需求	SQL 範例
前 N 筆	`SELECT * FROM t ORDER BY id LIMIT 5;`
分頁 (第 3 頁，每頁 20 筆)	`SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 40;`
匯出 CSV	`SELECT * FROM users INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';`
匯入 CSV	`LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;`
去重刪除 (保留最小 id)	`DELETE t1 FROM users t1 JOIN users t2 ON t1.email=t2.email AND t1.id > t2.id;`
字串拼接聚合	`SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ',') FROM users GROUP BY dept_id;`

六、命令列實用技巧

多行命令：未輸入分號前可換行繼續輸入，誤送分號可再輸入分號補齊。
歷史指令：↑/↓ 瀏覽歷史，history 查看完整列表。
提示符自訂：mysql --prompt='\\u@\\h [\\d]> ' 顯示用戶@主機 [資料庫]。
安全模式：mysql --safe-updates 防止無 WHERE 的 UPDATE/DELETE 執行。
垂直顯示結果：查詢結尾加 \G 代替 ;，適合寬表閱讀。

七、系統狀態與監控常用命令

SHOW PROCESSLIST;                    -- 當前連線/查詢 (加 FULL 顯示完整 SQL)
SHOW STATUS LIKE 'Threads_connected'; -- 當前連線數
SHOW VARIABLES LIKE 'max_connections'; -- 最大連線數設定
SHOW ENGINE INNODB STATUS;           -- InnoDB 內部狀態 (鎖、事務、Buffer Pool)
SHOW GLOBAL STATUS LIKE 'Slow_queries'; -- 慢查詢計數
SHOW OPEN TABLES WHERE In_use > 0;   -- 正在被鎖定的表