MySQL 多表關聯查詢與備份維護實戰教程

多表操作核心：JOIN 與子查詢

以員工表 mytable 和文章表 title 為例，演示內連接、條件關聯、增刪改查與備份全流程。

建表示例數據

CREATE TABLE mytable (
    name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20)
);
INSERT INTO mytable VALUES
('abccs','f','1977-07-07','china'),
('mary','f','1978-12-12','usa'),
('tom','m','1970-09-02','usa');

CREATE TABLE title (
    writer VARCHAR(20), title VARCHAR(40), senddate DATE
);
INSERT INTO title VALUES
('abccs','a1','2000-01-23'),
('mary','b1','1998-03-21'),
('abccs','a2','2000-12-04'),
('tom','c1','1992-05-16'),
('tom','c2','1999-12-12');

多表查詢實例

查詢 abccs 的姓名、性別、文章：

SELECT m.name, m.sex, t.title
FROM mytable m, title t
WHERE m.name = t.writer AND m.name = 'abccs';

結果：

+-------+------+-------+
| name  | sex  | title |
+-------+------+-------+
| abccs | f    | a1    |
| abccs | f    | a2    |
+-------+------+-------+

查詢文章 a2 的作者出生地與生日：

SELECT t.title, t.writer, m.birthaddr, m.birth
FROM mytable m, title t
WHERE m.name = t.writer AND t.title = 'a2';

表結構修改

-- 增加列
ALTER TABLE mytable ADD COLUMN single CHAR(1);

-- 更新數據
UPDATE mytable SET single = 'y' WHERE name = 'abccs';

-- 插入新記錄
INSERT INTO mytable VALUES ('abc','f','1966-08-17','china','n');

-- 刪除記錄
DELETE FROM mytable WHERE name = 'abc';

-- 刪除表
DROP TABLE title;

-- 刪除庫
DROP DATABASE dbname;

數據庫備份與恢復

# 備份整庫
mysqldump --opt abccs > abccs.dbb

# 備份單表
mysqldump --opt abccs mytable > mytable.sql

# 恢復
mysql -u root -p abccs < abccs.dbb

批處理腳本執行

創建 mytest.sql：

USE abccs;
SELECT * FROM mytable;
SELECT name, sex FROM mytable WHERE name = 'abccs';

執行方式：

mysql < mytest.sql           # 直接輸出
mysql < mytest.sql | more    # 分頁查看
mysql < mytest.sql > out.txt # 導出文件

最佳實踐建議

• 多表查詢優先使用顯式 INNER JOIN ... ON 語法，可讀性更強
• 備份加上 --single-transaction (InnoDB) 實現熱備不鎖表
• 定期驗證備份文件可恢復性（mysql < backup.sql 測試）
• 生產環境建議使用 xtrabackup 或 mydumper 並行備份