MySQL 常用命令大全速查手冊
備份與還原
| 操作 | 命令 |
|---|
| 完整備份 (含結構+資料) | mysqldump -h host -u user -p --opt db_name > backup.sql |
| 僅結構 | mysqldump -h host -d -u user -p db_name > structure.sql |
| 單表備份 | mysqldump --opt --add-drop-table -u user -p db_name tbl_name > dump.sql |
| 含密碼自動化 (不安全，僅測試) | mysqldump -h host -u user --password=123456 --opt db_name > backup.sql |
| 還原 (登入後) | mysql> SOURCE backup.sql; |
| 還原 (命令列) | mysql -u user -p db_name < backup.sql |
資料查詢與處理
| 操作 | SQL |
|---|
| 查詢結果導出檔案 | SELECT title FROM book INTO OUTFILE '/tmp/outfile.txt'; |
| 查重複記錄 (依 peopleId) | SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1); |
| 去重查詢 (DISTINCT) | SELECT * FROM phome_ecms_wma WHERE title IN (SELECT DISTINCT title FROM phome_ecms_wma); |
| 刪除重複保留一筆 | SELECT *,COUNT(DISTINCT title) INTO OUTFILE '/tmp/t.bak' FROM t GROUP BY title; DELETE FROM t; LOAD DATA INFILE '/tmp/t.bak' REPLACE INTO TABLE t CHARACTER SET utf8; |
編碼與字符集
SHOW VARIABLES LIKE 'character_set_%'; -- 查看所有字符集設定
SET NAMES utf8; -- 等同於設定 client/results/connection 三者為 utf8
結構管理 (DDL)
| 操作 | SQL |
|---|
| 修改欄位型別 | ALTER TABLE t CHANGE col col DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'; |
| 新增欄位 | ALTER TABLE host ADD ks_mac VARCHAR(100); |
| 刪除欄位 | ALTER TABLE t DROP col; |
| 重命名表 | ALTER TABLE t1 RENAME t2; |
| 加普通索引 | ALTER TABLE t ADD INDEX idx_name (col); |
| 加主鍵 | ALTER TABLE t ADD PRIMARY KEY (id); |
| 加唯一索引 | ALTER TABLE t ADD UNIQUE uk_card (cardnumber); |
| 刪除索引 | ALTER TABLE t DROP INDEX idx_name; |
遠程授權
GRANT ALL PRIVILEGES ON db_name.* TO 'root'@'192.168.1.9' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
快速參考卡
\G 結尾：垂直顯示結果 (適合寬表)SHOW PROCESSLIST;：查看當前執行緒SHOW ENGINES;：引擎支援狀態SHOW CREATE TABLE t;：完整建表語句EXPLAIN SELECT ...;：執行計劃分析
