連線 MySQL 服務器
命令列客戶端
# 本地連線 (無密碼 root)
mysql -u root -p
# 提示輸入密碼，直接 Enter (初始無密碼)
# 本地連線 (有密碼)
mysql -u root -p'YourPass' # -p 緊跟密碼無空格，含特殊字元建議單引號
# 遠程連線
mysql -h 192.168.1.100 -u app_user -p'Pass' -P 3306
# 退出
exit 或 \q 或 Ctrl+D
連線參數速查
| 參數 | 含義 | 範例 |
|---|
| -h, --host | 主機地址 | -h localhost / 192.168.1.10 |
| -u, --user | 用戶名 | -u root |
| -p, --password | 密碼 (不建議明文) | -p 或 -p'secret' |
| -P, --port | 埠號 | -P 3306 |
| -D, --database | 預設資料庫 | -D mydb |
| -e, --execute | 執行單條 SQL 後退出 | -e "SELECT VERSION()" |
| --default-character-set | 連線字元集 | --default-character-set=utf8mb4 |
用戶與權限管理
修改 root 密碼 (三種方式)
# 1. mysqladmin (客戶端工具)
mysqladmin -u root -p'old' password 'new'
# 2. SQL 語句 (需登入)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
FLUSH PRIVILEGES;
# 3. SET PASSWORD (舊版兼容)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPass123!');
建立業務用戶 (最小權限原則)
-- 僅允許本地連線，擁有 mydb 所有權限
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPass123!';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';
-- 允許特定 IP 段讀寫
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnlyPass!';
GRANT SELECT ON mydb.* TO 'readonly'@'192.168.1.%';
-- 刪除用戶
DROP USER 'bad_user'@'%';
-- 查看權限
SHOW GRANTS FOR 'app_user'@'localhost';
資料庫與資料表核心操作
資料庫級
SHOW DATABASES;
CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE school;
DROP DATABASE school; -- 謹慎！
資料表級 (以教師表為例)
CREATE TABLE teacher (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主鍵',
name CHAR(10) NOT NULL COMMENT '姓名',
address VARCHAR(50) DEFAULT '深圳' COMMENT '地址',
hire_date DATE COMMENT '入職日期',
salary DECIMAL(10,2) DEFAULT 0 COMMENT '薪資',
is_active TINYINT(1) DEFAULT 1 COMMENT '在職狀態',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師信息表';
-- 查看結構
DESC teacher;
SHOW CREATE TABLE teacher\G
-- 修改表結構
ALTER TABLE teacher ADD COLUMN email VARCHAR(100) AFTER name;
ALTER TABLE teacher MODIFY COLUMN name VARCHAR(20) NOT NULL;
ALTER TABLE teacher DROP COLUMN is_active;
ALTER TABLE teacher RENAME TO teachers;
數據操作 (CRUD) 核心語法
插入 (INSERT)
-- 完整欄位
INSERT INTO teacher (name, address, hire_date, salary)
VALUES ('張三', '深圳南山', '2020-01-15', 8000.00);
-- 批量插入 (高效)
INSERT INTO teacher (name, address, hire_date, salary) VALUES
('李四', '深圳福田', '2019-03-01', 9500.00),
('王五', '廣州天河', '2021-07-10', 7200.00),
('趙六', '北京海淀', '2018-11-20', 12000.00);
-- 指定主鍵 (慎用)
INSERT INTO teacher (id, name, ...) VALUES (100, '指定ID', ...);
查詢 (SELECT)
-- 全表
SELECT * FROM teacher;
-- 條件過濾
SELECT id, name, salary FROM teacher WHERE salary > 8000 AND is_active = 1;
-- 排序分頁
SELECT * FROM teacher ORDER BY hire_date DESC LIMIT 10 OFFSET 0;
-- 聚合統計
SELECT address, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM teacher GROUP BY address HAVING cnt > 1;
更新 (UPDATE)
-- 單行
UPDATE teacher SET salary = 8500.00 WHERE id = 1;
-- 批量條件更新
UPDATE teacher SET salary = salary * 1.05 WHERE hire_date < '2020-01-01';
刪除 (DELETE)
-- 條件刪除
DELETE FROM teacher WHERE is_active = 0;
-- 清空表 (高效、重置自增、不可回滾)
TRUNCATE TABLE teacher;
文本數據導入導出
LOAD DATA 導入 (極速)
-- 數據文件 teacher.txt (Tab 分隔，NULL 用 \N)
# 1 張三 深圳南山 2020-01-15 8000.00
# 2 李四 深圳福田 2019-03-01 9500.00
LOAD DATA LOCAL INFILE 'teacher.txt'
INTO TABLE teacher
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, address, hire_date, salary);
SELECT ... INTO OUTFILE 導出
SELECT id, name, address, hire_date, salary
FROM teacher
INTO OUTFILE '/tmp/teacher_export.txt'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
備份與還原 (生產級工具 mysqldump)
邏輯備份
# 單庫備份 (含結構+數據，多行 INSERT，單事務)
mysqldump --single-transaction --routines --triggers --events \
-u root -p school > school_$(date +%F).sql
# 多庫備份
mysqldump --databases school hr finance > multi_db.sql
# 全量備份 (含系統庫)
mysqldump --all-databases > full_backup.sql
# 僅結構
mysqldump --no-data -u root -p school > school_structure.sql
# 僅數據
mysqldump --no-create-info -u root -p school > school_data.sql
還原
# 還原單庫 (需先建庫)
mysql -u root -p -e "CREATE DATABASE school CHARACTER SET utf8mb4;"
mysql -u root -p school < school_2024-01-15.sql
# 還原多庫/全量
mysql -u root -p < multi_db.sql
# 異機還原 (流式傳輸，無中間文件)
mysqldump -h src_host -u root -p school | mysql -h dst_host -u root -p school
常用 SHOW/DESC 信息收集命令
| 命令 | 用途 |
|---|
| SHOW DATABASES; | 列出所有庫 |
| SHOW TABLES [FROM db_name]; | 列出表 |
| DESC table_name; | 表結構 |
| SHOW CREATE TABLE table_name\G | 完整建表 SQL |
| SHOW INDEX FROM table_name; | 索引詳情 |
| SHOW TABLE STATUS [FROM db_name]; | 表統計 (行數、大小、自增值) |
| SHOW PROCESSLIST; | 當前線程活動 |
| SHOW VARIABLES LIKE 'max_connections'; | 配置參數查看 |
| SHOW GLOBAL STATUS LIKE 'Threads_connected'; | 運行狀態指標 |
新手避坑 10 條
- 分號結尾：SQL 語句必須以 `;` 或 `\g` 結束，多行輸入以分號為準。
- 反引號保護：表名、欄位名用 `` ` `` 包裹，防止保留字衝突：`SELECT \`select\` FROM \`order\`;`
- 字元集統一：庫/表/連線/應用**全鏈路 utf8mb4**，避免亂碼。
- 主鍵必備：每張表**必須**有主鍵 (最好是自增整數)，InnoDB 聚簇索引依賴主鍵。
- 避免 SELECT *：顯式列出欄位，減少 I/O、網絡、內存開銷。
- WHERE 欄位加索引：高頻查詢欄位建立索引，EXPLAIN 驗證 type=ref/range。
- 事務邊界清晰：InnoDB 預設自動提交，多步操作显式 `START TRANSACTION; ... COMMIT;`。
- 備份驗證：定期測試還原，**未驗證的備份 = 沒有備份**。
- 權限最小化：應用用戶僅授予必要庫/表權限，嚴禁 root 直連應用。
- 善用 \G 縱向顯示：寬表 `SHOW CREATE TABLE t\G` 避免換行難讀。
MySQL 入門看似命令繁多，實則邏輯自洽：**連線 → 權限 → 結構 → 數據 → 備份** 五環相扣。掌握上述核心命令與避坑原則，配合 `mysql --help`、`SHOW VARIABLES`、官方文檔，即可從零構建起專業級 MySQL 使用能力，為進階調優、架構設計打下堅實基礎。
