MySQL 數據庫操作語法大全：從基礎到進階的完整參考手冊

MySQL 數據庫操作語法大全：從基礎到進階的完整參考手冊

本文匯總 MySQL 日常開發、運維、管理的核心語法，涵蓋連接認證、用戶權限、庫表操作、數據增刪改查、備份恢複、性能優化等全場景，作為開發者與 DBA 的桌面速查手冊。

連接與認證

# 本地連接
mysql -u root -p                    # 交互式輸入密碼
mysql -u root -p'password'          # 直接指定 (注意無空格)
mysql -u root --password='password' # 長參數形式

# 遠程連接
mysql -h 192.168.1.100 -u user -p'pass' -P 3306 db_name

# 退出
exit / quit / \q

用戶與權限管理

創建用戶

GRANT privileges ON db.table TO 'user'@'host' [WITH GRANT OPTION];

-- 權限層級
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';      -- 表級 DML
GRANT ALL PRIVILEGES ON app_db.* TO 'admin'@'%';                    -- 庫級全權
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' WITH GRANT OPTION;         -- 全局超級管理員

-- 刷新生效
FLUSH PRIVILEGES;

修改密碼

REVOKE SELECT ON app_db.* FROM 'app'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'dba'@'%';

DROP USER 'user'@'host';

查看權限

-- 創建
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE IF NOT EXISTS db_name;  -- 避免報錯

-- 查看
SHOW DATABASES;
SHOW CREATE DATABASE db_name;

-- 切換
USE db_name;

-- 刪除
DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;  -- 避免報錯

-- 當前庫
SELECT DATABASE();

表操作 (DDL)

創建表

SHOW TABLES;
SHOW TABLES LIKE 'prefix_%';
DESC table_name;          -- 簡略
SHOW CREATE TABLE table_name\G  -- 完整 DDL
SHOW COLUMNS FROM table_name;
SHOW INDEX FROM table_name\G;

修改表結構

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
DROP TABLE t1, t2, t3;  -- 批量

數據操作 (DML)

插入 (INSERT)

-- 基礎
SELECT * FROM t;
SELECT id, name, price FROM t WHERE status = 1;
SELECT * FROM t WHERE name LIKE '%關鍵字%' ORDER BY created_at DESC LIMIT 10;

-- 分頁 (大偏移優化)
SELECT * FROM t WHERE id > 10000 ORDER BY id LIMIT 20;  -- 利用主鍵游標

-- 聚合
SELECT status, COUNT(*) AS cnt, AVG(price) AS avg_price 
FROM t GROUP BY status HAVING cnt > 5;

-- 連接查詢
SELECT u.name, o.order_no, o.amount 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1;

-- 子查詢 / CTE (8.0+)
WITH top_users AS (SELECT user_id, SUM(amount) total FROM orders GROUP BY user_id ORDER BY total DESC LIMIT 10)
SELECT u.*, tu.total FROM users u JOIN top_users tu ON u.id = tu.user_id;

更新 (UPDATE)

DELETE FROM t WHERE status = 0;
DELETE FROM t WHERE id IN (1,2,3);
DELETE FROM t ORDER BY created_at LIMIT 1000;  -- 批量刪除避免鎖表
TRUNCATE TABLE t;  -- 快速清空表 (DDL, 不觸發觸發器, 不可回滾)

備份與恢複

邏輯備份 (mysqldump)

# 全量恢複
mysql -u root -p < full_backup.sql

# 指定庫恢複
mysql -u root -p db_name < db_backup.sql

# 並行恢複 (工具: mydumper/myloader 或自研分表並行)

物理備份 (Percona XtraBackup - 熱備份)

# 開啟慢查詢日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

# 分析工具
pt-query-digest /var/log/mysql/slow.log > slow_report.txt
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

執行計劃分析

-- 顯式事務
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK;

-- 保存點
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO SAVEPOINT sp1;

-- 自動提交控制
SET autocommit = 0;  -- 關閉
SET autocommit = 1;  -- 開啟 (默認)

常用系統命令