MySQL 常用命令大全：DCL、DDL、DML、備份還原、權限重置一網打盡

MySQL 常用命令大全：DCL、DDL、DML、備份還原、權限重置一網打盡

一、DCL：權限控制

-- 授權遠程訪問（通配符 % 代表所有 IP）
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
-- 指定 IP 段
GRANT ALL PRIVILEGES ON *.* TO 'exam'@'192.168.0.%' IDENTIFIED BY 'password' WITH GRANT OPTION;
-- 創建用戶
CREATE USER 'usr' IDENTIFIED BY 'pwd';
-- 創建庫並指定字符集
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

二、DDL：結構定義

SHOW DATABASES;
SHOW TABLES [FROM db_name];
SHOW TABLE STATUS [FROM db_name];
DESC tbl_name;
SHOW FULL COLUMNS FROM tbl_name;
SHOW INDEX FROM tbl_name [FROM db_name];
SHOW CREATE TABLE tbl_name;

三、DML 與常用查詢

SHOW STATUS;           -- 伺服器狀態
SHOW VARIABLES;        -- 系統變數
SHOW PROCESSLIST;      -- 執行緒列表
SHOW GRANTS FOR user;  -- 用戶權限
KILL connection_id;    -- 終止會話

-- 兩表聯動更新（MySQL 不支援 UPDATE ... FROM）
UPDATE moa_ios_devicetoken m1, moa_ios_devicetoken m2
SET m1.device_token = m2.device_token
WHERE m1.user_id = m2.user_id
  AND m1.line_id IN (1,2,4)
  AND m2.line_id IN (5,6,8);

四、mysqldump 備份與還原

# 導出結構
mysqldump -u root -p -d dbname > db_struct.sql
# 導出單表結構
mysqldump -u root -p -d dbname tbl_name > tbl_struct.sql
# 導出結構+存儲過程/函數/觸發器，不含資料
mysqldump -u root -p -R --no-data dbname > db_obj.sql
# 完整導出（結構+對象+資料）
mysqldump -u root -p -R dbname > db_full.sql
# 還原
mysql -u root -p dbname < db_full.sql
# 或在 mysql 客戶端
SOURCE /path/db_full.sql;

五、修改表結構速查

-- 增加欄位
ALTER TABLE tb ADD col_name datatype [NULL|NOT NULL];
-- 重命名欄位
ALTER TABLE tb CHANGE old_col new_col datatype [NULL|NOT NULL];
-- 修改類型
ALTER TABLE tb MODIFY col_name new_datatype [NULL|NOT NULL];
-- 刪除欄位
ALTER TABLE tb DROP COLUMN col_name;
-- 修改註釋
ALTER TABLE tb MODIFY col_name datatype COMMENT '備註';

六、服務管理

# Linux
systemctl start|stop|restart mysqld
# Windows
net start mysql
net stop mysql
# 關閉（需密碼）
mysqladmin -u root -p shutdown

七、註釋語法

• # 註釋（單行）
• -- 註釋（雙減號**後必須跟一個空格**）
• /* 多行註釋 */

八、忘記 root 密碼重置

mysqld_safe --skip-grant-tables &
mysql -u root mysql
UPDATE user SET authentication_string=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;
-- 移除 skip-grant-tables 並重啟

九、Windows ZIP 免安裝包部署

# 解壓後進入 bin 目錄
mysqld --install          # 註冊為 Windows 服務
# 設定服務自啟或手動啟動
net start mysql

十、刪除重複記錄經典三步法

-- 1. 取重複鍵的最小 ID 存入臨時表
CREATE TABLE tmp_dup SELECT MIN(id) AS min_id, dup_key FROM tbl GROUP BY dup_key HAVING COUNT(*) > 1;
-- 2. 級聯刪除重複行（保留最小 ID）
DELETE t FROM tbl t JOIN tmp_dup d ON t.dup_key = d.dup_key WHERE t.id > d.min_id;
-- 3. 刪除臨時表
DROP TABLE tmp_dup;

十一、Excel 導入 MySQL 速成法

Excel 構造 INSERT 語句字串（用 CONCATENATE 或 &），複製到文本檔存為 .sql，再 SOURCE 導入。