MySQL 數據庫操作命令大全：庫/表/數據/備份/權限一網打盡
MySQL 不區分大小寫，關鍵字建議大寫提高可讀性。本文匯總 DDL、DML、DCL、備份恢復、元數據查詢等高頻命令，適合開發、運維隨時查閱。
一、庫級操作
| 操作 | 命令 | 範例 |
|---|
| 創建庫 | CREATE DATABASE [IF NOT EXISTS] db_name [CHARSET charset] [COLLATE collation]; | CREATE DATABASE thinkgamer CHARSET utf8mb4; |
| 刪除庫 | DROP DATABASE [IF EXISTS] db_name; | DROP DATABASE thinkgamer; |
| 切換庫 | USE db_name; | USE thinkgamer; |
| 查看當前庫 | SELECT DATABASE(); | — |
| 顯示所有庫 | SHOW DATABASES; | — |
| 查看庫創建語句 | SHOW CREATE DATABASE db_name; | — |
二、表級操作（DDL）
創建表
CREATE TABLE cyan (
學號 INT(3) NOT NULL PRIMARY KEY,
姓名 CHAR(3) NOT NULL,
性別 CHAR(1) NOT NULL DEFAULT '1',
愛好 CHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
NOT NULL 禁止空值PRIMARY KEY 主鍵（隱含 NOT NULL + UNIQUE）AUTO_INCREMENT 自增（僅整數類型）DEFAULT 默認值ENGINE=InnoDB 顯式指定引擎
修改表結構
| 操作 | 命令 | 範例 |
|---|
| 添加列 | ALTER TABLE tbl ADD col_def [FIRST|AFTER col_name]; | ALTER TABLE cyan ADD 愛好 CHAR(20); |
| 修改列定義 | ALTER TABLE tbl MODIFY col_def; | ALTER TABLE cyan MODIFY 姓名 VARCHAR(20); |
| 重命名列 | ALTER TABLE tbl CHANGE old_col new_col col_def; | ALTER TABLE cyan CHANGE 性別 gender CHAR(1); |
| 刪除列 | ALTER TABLE tbl DROP COLUMN col_name; | ALTER TABLE cyan DROP COLUMN 愛好; |
| 添加主鍵 | ALTER TABLE tbl ADD PRIMARY KEY (col); | ALTER TABLE cyan ADD PRIMARY KEY (學號); |
| 添加唯一索引 | ALTER TABLE tbl ADD UNIQUE INDEX idx_name (col); | ALTER TABLE cyan ADD UNIQUE INDEX uk_name (姓名); |
| 添加普通索引 | ALTER TABLE tbl ADD INDEX idx_name (col); | ALTER TABLE cyan ADD INDEX idx_gender (性別); |
| 刪除索引 | ALTER TABLE tbl DROP INDEX idx_name; | ALTER TABLE cyan DROP INDEX idx_gender; |
| 重命名表 | RENAME TABLE old_name TO new_name; | RENAME TABLE cyan TO you_class; |
| 刪除表 | DROP TABLE [IF EXISTS] tbl_name; | DROP TABLE cyan; |
| 清空表（保留結構） | TRUNCATE TABLE tbl_name; | TRUNCATE TABLE cyan; |
三、數據操作（DML）
插入
-- 全列插入
INSERT INTO cyan VALUES (1, 'Tom', '1', '籃球');
-- 指定列插入
INSERT INTO cyan (學號, 姓名) VALUES (2, 'Jerry');
-- 批次插入
INSERT INTO cyan (學號, 姓名) VALUES (3, 'Alice'), (4, 'Bob');
-- 插入查詢結果
INSERT INTO cyan (學號, 姓名) SELECT id, name FROM temp_students;
查詢
-- 全列
SELECT * FROM cyan;
-- 指定列
SELECT 學號, 姓名 FROM cyan;
-- 條件過濾
SELECT * FROM cyan WHERE 性別 = '1';
-- 分頁（MySQL 8.0+ 推薦游標分頁）
SELECT * FROM cyan ORDER BY 學號 LIMIT 10 OFFSET 20;
-- 聚合
SELECT 性別, COUNT(*) FROM cyan GROUP BY 性別;
更新
-- 單表
UPDATE cyan SET 姓名 = 'Mary', 愛好 = '羽毛球' WHERE 學號 = 1;
-- 多表關聯更新
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.name = 'A', t2.name = 'B' WHERE t1.id = 1;
刪除
-- 條件刪除
DELETE FROM cyan WHERE 學號 = 1;
-- 多表關聯刪除
DELETE t1, t2 FROM t1 JOIN t2 ON t1.id = t2.id JOIN t3 ON t2.id = t3.id WHERE t1.id = 1;
四、備份與恢復
| 場景 | 命令 |
|---|
| 整庫備份 | mysqldump -u user -p db_name > backup.sql |
| 單表備份 | mysqldump -u user -p db_name table_name > backup.sql |
| 只備份結構（無數據） | mysqldump -u user -p -d --add-drop-table db_name > struct.sql |
| 指定字符集導出 | mysqldump -u user -p --default-character-set=utf8mb4 --set-charset=gbk --skip-opt db_name > out.sql |
| 恢復（命令行） | mysql -u user -p db_name < backup.sql |
| 恢復（客戶端內） | SOURCE /path/backup.sql; |
五、權限管理（DCL）
-- 創建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'strongpass';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%' WITH GRANT OPTION;
-- 查看權限
SHOW GRANTS FOR 'app'@'%';
-- 回收權限
REVOKE INSERT ON mydb.* FROM 'app'@'%';
-- 修改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'newpass';
-- 刪除用戶
DROP USER 'app'@'%';
六、元數據與狀態查詢
| 目的 | 命令 |
|---|
| 當前用戶 | SELECT USER(); |
| 版本號 | SELECT VERSION(); 或 SELECT @@version; |
| 表狀態 | SHOW TABLE STATUS [LIKE 'pattern']; |
| 表結構 | DESC tbl_name; 或 SHOW COLUMNS FROM tbl_name; |
| 建表語句 | SHOW CREATE TABLE tbl_name\G |
| 索引信息 | SHOW INDEX FROM tbl_name; |
| 進程列表 | SHOW PROCESSLIST; 或 SHOW FULL PROCESSLIST; |
| 全局狀態 | SHOW GLOBAL STATUS LIKE 'pattern'; |
| 全局變量 | SHOW GLOBAL VARIABLES LIKE 'pattern'; |
| 字符集支持 | SHOW CHARACTER SET; |
| 校對規則 | SHOW COLLATION; |
| 默認字符集 | SHOW VARIABLES LIKE 'character_set_%'; |
七、實用技巧
\G 結尾垂直顯示結果，適合寬表。tee /path/log.txt 記錄會話輸出；notee 關閉。pager less -SFX 設置分頁器，長結果可左右滾動。- 批量執行 SQL 文件：
mysql -u user -p db_name < batch.sql。
收藏此命令大全，日常開發運維無需東查西找。
