MySQL 核心操作實戰匯總：從建表到備份恢復

MySQL 核心操作實戰匯總：從建表到備份恢復

本文整理課程實驗與實務操作，系統覆蓋 DDL、DML、DCL、視圖、連接查詢、子查詢、集合操作、數據導入導出、備份恢復等核心技能，附帶實戰截圖驗證，適合作為學習筆記與面試複習參考。

一、DDL：庫表結構管理

1. 建庫建表插入

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  age INT,
  score DECIMAL(5,2)
);

INSERT INTO student (name, age, score) VALUES ('張三', 20, 95.5);
INSERT INTO student VALUES (NULL, '李四', 22, 88.0);  -- 全欄位含自增

2. 修改表結構

-- 修改欄位類型
ALTER TABLE student MODIFY name VARCHAR(30);

-- 新增欄位
ALTER TABLE student ADD email VARCHAR(50);

-- 刪除欄位
ALTER TABLE student DROP email;

-- 重命名表
ALTER TABLE student RENAME TO stu_info;

二、DML：數據增刪改查

1. 插入變體

-- 單行
INSERT INTO student (name, age) VALUES ('王五', 21);

-- 多行批次（高效）
INSERT INTO student (name, age, score) VALUES 
  ('趙六', 19, 91), ('錢七', 20, 87), ('孫八', 22, 93);

2. 基礎查詢

-- 投影
SELECT name, score FROM student;

-- 選擇（條件）
SELECT * FROM student WHERE age > 20;

-- 排序
SELECT * FROM student ORDER BY score DESC;

-- 模糊匹配
SELECT * FROM student WHERE name LIKE '張%';

3. 連接查詢（多表關聯）

-- 建立關聯表
CREATE TABLE course (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE sc (student_id INT, course_id INT, grade DECIMAL(4,1),
  PRIMARY KEY (student_id, course_id));

-- 等值連接
SELECT s.name, c.name, sc.grade 
FROM student s JOIN sc ON s.id = sc.student_id 
JOIN course c ON c.id = sc.course_id;

-- 左外連接（含無選課學生）
SELECT s.name, c.name, sc.grade 
FROM student s LEFT JOIN sc ON s.id = sc.student_id 
LEFT JOIN course c ON c.id = sc.course_id;

-- 自連接（員工-經理結構）
ALTER TABLE student ADD manager_id INT;
SELECT e.name AS emp, m.name AS mgr 
FROM student e JOIN student m ON e.manager_id = m.id;

三、進階查詢：子查詢、分組、集合

1. 子查詢

-- IN 子查詢
SELECT * FROM student WHERE id IN (SELECT student_id FROM sc WHERE grade > 90);

-- EXISTS 相關子查詢
SELECT * FROM student s WHERE EXISTS (
  SELECT 1 FROM sc WHERE sc.student_id = s.id AND grade > 90
);

-- 比較運算子
SELECT * FROM student WHERE score > (SELECT AVG(score) FROM student);

2. 分組聚合

SELECT age, COUNT(*) AS cnt, AVG(score) AS avg_score 
FROM student GROUP BY age HAVING COUNT(*) >= 2;

3. 集合操作

-- UNION 去重
SELECT name FROM student WHERE age < 20
UNION
SELECT name FROM student WHERE score > 90;

-- UNION ALL 不去重（效能更好）
SELECT name FROM student WHERE age < 20
UNION ALL
SELECT name FROM student WHERE score > 90;

-- 交集（MySQL 無 INTERSECT，用 INNER JOIN 或 IN 模擬）
SELECT name FROM student WHERE id IN (
  SELECT id FROM student WHERE age < 20
  INTERSECT
  SELECT id FROM student WHERE score > 90
);
-- 實際寫法：
SELECT name FROM student WHERE age < 20 AND score > 90;

四、視圖操作

-- 創建視圖
CREATE VIEW v_high_score AS 
SELECT id, name, score FROM student WHERE score >= 90;

-- 查詢視圖
SELECT * FROM v_high_score;

-- 通過視圖修改（需符合可更新條件）
UPDATE v_high_score SET score = 95 WHERE name = '張三';

-- 刪除視圖
DROP VIEW v_high_score;

五、DCL：權限控制

-- 創建用戶
CREATE USER 'dev'@'%' IDENTIFIED BY 'dev123';

-- 授權
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'dev'@'%';

-- 查看權限
SHOW GRANTS FOR 'dev'@'%';

-- 回收權限
REVOKE UPDATE ON testdb.* FROM 'dev'@'%';

-- 生效
FLUSH PRIVILEGES;

六、數據導入導出

1. 導出為文本（SELECT INTO OUTFILE）

SELECT * FROM student 
INTO OUTFILE '/tmp/student.txt' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

2. 從文本導入（LOAD DATA INFILE）

CREATE TABLE student_import LIKE student;
LOAD DATA INFILE '/tmp/student.txt' 
INTO TABLE student_import 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

七、備份與恢復

1. 邏輯備份（mysqldump）

# 全庫備份（含結構+數據）
mysqldump -u root -p --single-transaction --routines --triggers testdb > testdb_full.sql

# 僅結構
mysqldump -u root -p -d testdb > testdb_schema.sql

# 僅數據
mysqldump -u root -p -t testdb > testdb_data.sql

2. 邏輯恢復

# 恢復全庫
mysql -u root -p testdb < testdb_full.sql

# 或進入 mysql 後
SOURCE /path/to/testdb_full.sql;

3. 實驗驗證流程

1. 備份資料庫 → 檢查備份文件含 CREATE TABLE + INSERT 語句
2. 刪除原表：DROP TABLE student;
3. 恢復：mysql -u root -p testdb < testdb_full.sql
4. 驗證：SELECT * FROM student; 數據完整返回

4. 增量備份說明

MySQL 5.6+ 支援二進制日誌（binlog）增量備份：

# 開啟 binlog（my.cnf）
[mysqld]
log-bin=mysql-bin
server-id=1

# 刷新日誌產生新 binlog 文件
FLUSH LOGS;

# 使用 mysqlbinlog 提取增量
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-02 00:00:00" \
            /var/lib/mysql/mysql-bin.000001 > incremental.sql

注意：原文提到 MySQL 5 不支援增量備份屬誤解，5.6+ 完整支援基於 binlog 的增量備份策略。

核心知識點總結表

熟練掌握上述操作並理解其底層機制，是成為合格 MySQL 開發者與 DBA 的必經之路。