MySQL 多表操作與備份處理：關聯查詢、結構修改、批次匯入實戰

情境：員工基本資料表 + 文章發表記錄表

表 1：mytable (員工基本資料)

CREATE TABLE mytable (
    name VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    birthaddr VARCHAR(20)
);

+----------+------+------------+-----------+
| name     | sex  | birth      | birthaddr |
+----------+------+------------+-----------+
| abccs    | f    | 1977-07-07 | china     |
| mary     | f    | 1978-12-12 | usa       |
| tom      | m    | 1970-09-02 | usa       |
+----------+------+------------+-----------+

表 2：title (文章發表記錄)

CREATE TABLE title (
    writer VARCHAR(20) NOT NULL,
    title VARCHAR(40) NOT NULL,
    senddate DATE
);

+--------+-------+------------+
| writer | title | senddate   |
+--------+-------+------------+
| 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 |
+--------+-------+------------+

多表關聯查詢

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

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

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

範例 2：查詢文章 a2 的作者、出生地、出生日期

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

+-------+--------+-----------+------------+
| title | writer | birthaddr | birth      |
+-------+--------+-----------+------------+
| a2    | abccs  | china     | 1977-07-07 |
+-------+--------+-----------+------------+

關鍵點：同名欄位需加表名前綴

若 title 表的作者欄位也叫 name，必須寫 mytable.name 和 title.name 區分。

表結構修改

新增欄位

ALTER TABLE mytable ADD COLUMN single CHAR(1);

修改資料

UPDATE mytable SET single = 'y' WHERE name = 'abccs';

+----------+------+------------+-----------+--------+
| name     | sex  | birth      | birthaddr | single |
+----------+------+------------+-----------+--------+
| abccs    | f    | 1977-07-07 | china     | y      |
| mary     | f    | 1978-12-12 | usa       | NULL   |
| tom      | m    | 1970-09-02 | usa       | NULL   |
+----------+------+------------+-----------+--------+

備份與批次處理

資料匯出

mysqldump -u root -p 資料庫名 表名 > backup.sql

批次匯入文字檔

# 建立 mytable.txt (Tab 分隔，欄位順序同 CREATE TABLE)
abccs	f	1977-07-07	china
mary	f	1978-12-12	usa
tom	m	1970-09-02	usa

# 匯入
LOAD DATA LOCAL INFILE 'mytable.txt' INTO TABLE mytable;

資料表複製

CREATE TABLE new_table LIKE mytable;
INSERT INTO new_table SELECT * FROM mytable;

刪除操作

DELETE FROM mytable WHERE name = 'tom';     -- 刪除特定記錄
TRUNCATE TABLE mytable;                     -- 快速清空全表 (重置自增 ID)
DROP TABLE mytable;                         -- 刪除表結構與資料