連接與基礎導航
# 連接本地/遠程
mysql -h localhost -u root -p
mysql -h 192.168.1.100 -u admin -p -P 3306
# 退出
EXIT; \q QUIT
庫/表結構管理
| 操作 | 命令 | 示例 |
|---|
| 創建庫 | CREATE DATABASE | CREATE DATABASE test CHARSET utf8mb4; |
| 刪除庫 | DROP DATABASE | DROP DATABASE test; |
| 查看庫 | SHOW DATABASES | SHOW DATABASES LIKE 'test%'; |
| 切換庫 | USE | USE test; |
| 創建表 | CREATE TABLE | CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20)) ENGINE=InnoDB; |
| 刪除表 | DROP TABLE | DROP TABLE IF EXISTS t; |
| 查看表 | SHOW TABLES | SHOW TABLES FROM test; |
| 表結構 | DESCRIBE / SHOW COLUMNS | DESC t; SHOW COLUMNS FROM t; |
| 建表語句 | SHOW CREATE TABLE | SHOW CREATE TABLE t\G |
數據增刪改查 (CRUD)
插入
INSERT INTO t (name, age) VALUES ('Alice', 25);
INSERT INTO t (name, age) VALUES ('Bob', 30), ('Carol', 28);
INSERT INTO t SELECT name, age FROM t2 WHERE age > 20;
查詢
SELECT * FROM t;
SELECT id, name FROM t WHERE age > 25 ORDER BY age DESC LIMIT 10;
SELECT COUNT(*), SUM(age), AVG(age), MAX(age), MIN(age) FROM t;
SELECT DISTINCT age FROM t;
更新
UPDATE t SET age = 26 WHERE name = 'Alice';
UPDATE t SET age = age + 1 WHERE age < 30;
刪除
DELETE FROM t WHERE name = 'Alice';
TRUNCATE TABLE t; -- 重置自增 ID，不可回滾，速度快
高級查詢技巧
模糊與正則
SELECT * FROM t WHERE name LIKE 'A%'; -- 以 A 開頭
SELECT * FROM t WHERE name LIKE '%test%'; -- 包含 test
SELECT * FROM t WHERE name REGEXP '^[A-C]'; -- 正則：A/B/C 開頭
分頁與排序
SELECT * FROM t ORDER BY id DESC LIMIT 0, 10; -- 第 1 頁
SELECT * FROM t ORDER BY id DESC LIMIT 10 OFFSET 20; -- 第 3 頁
連接查詢
SELECT a.id, a.name, b.order_no
FROM users a LEFT JOIN orders b ON a.id = b.user_id;
去重統計
SELECT COUNT(DISTINCT name) FROM t;
SELECT name, COUNT(*) AS cnt FROM t GROUP BY name HAVING cnt > 1;
用戶權限管理
-- 創建用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass123!';
-- 授權
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app'@'%';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';
-- 刷新生效
FLUSH PRIVILEGES;
-- 查看權限
SHOW GRANTS FOR 'app'@'%';
-- 回收權限
REVOKE INSERT ON mydb.* FROM 'app'@'%';
-- 修改密碼
ALTER USER 'app'@'%' IDENTIFIED BY 'NewPass456!';
-- 或 5.7-
SET PASSWORD FOR 'app'@'%' = PASSWORD('NewPass456!');
備份與恢復
# 全庫備份
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > all.sql
# 單庫備份（含結構+數據）
mysqldump -u root -p --databases mydb > mydb.sql
# 僅結構
mysqldump -u root -p --no-data --databases mydb > structure.sql
# 單表備份
mysqldump -u root -p mydb t1 t2 > tables.sql
# 壓縮備份
mysqldump -u root -p mydb | gzip > mydb.sql.gz
# 恢復
mysql -u root -p mydb < mydb.sql
gunzip < mydb.sql.gz | mysql -u root -p mydb
# 遷移到新服務器
mysqldump -u root -p mydb | mysql -h new_host -u root -p -C mydb
表結構變更 (DDL)
-- 增加列
ALTER TABLE t ADD COLUMN email VARCHAR(100) AFTER name;
-- 刪除列
ALTER TABLE t DROP COLUMN email;
-- 修改列類型/屬性
ALTER TABLE t MODIFY COLUMN name VARCHAR(50) NOT NULL DEFAULT '';
ALTER TABLE t CHANGE COLUMN old_name new_name VARCHAR(50);
-- 重命名表
RENAME TABLE t TO t_new;
ALTER TABLE t RENAME TO t_new;
-- 索引管理
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD UNIQUE INDEX uk_email (email);
ALTER TABLE t ADD PRIMARY KEY (id);
ALTER TABLE t DROP INDEX idx_name;
系統監控與診斷
-- 進程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 狀態變量
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
-- 變量配置
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_%';
-- InnoDB 引擎狀態
SHOW ENGINE INNODB STATUS\G
-- 慢查詢日志分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
字符集與編碼
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';
SET NAMES utf8mb4;
