MySQL 多表關聯查詢與資料維護操作實務

多表關聯查詢

範例資料

mytable (員工基本資料)

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

title (員工發表文章)

+--------+-------+------------+
| 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 |
+--------+-------+------------+

內部連結 (INNER JOIN)

-- 查詢 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    |
+-------+------+-------+

反向查詢：由文章找作者背景

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

欄位同名時的別名寫法

若兩表關聯欄位同名（如皆為 name），必須加上表別名：

SELECT m.name, t.title FROM mytable m, title t WHERE m.name = t.name;

資料維護操作

1. 新增欄位 (ALTER TABLE ADD)

ALTER TABLE mytable ADD COLUMN single CHAR(1);

2. 修改資料 (UPDATE)

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

3. 新增記錄 (INSERT)

INSERT INTO mytable VALUES ('abc', 'f', '1966-08-17', 'china', 'n');

4. 刪除記錄 (DELETE)

DELETE FROM mytable WHERE name = 'abc';

5. 刪除資料表 (DROP TABLE)

DROP TABLE table1, table2;  -- 可一次刪多表

6. 刪除資料庫 (DROP DATABASE)

DROP DATABASE db_name;

警告： DROP 操作不可逆，生產環境務必先備份。

備份與批次執行

邏輯備份 (mysqldump)

# 在 shell 執行
mysqldump --opt abccs > abccs.dbb

產生純 SQL 文字檔，可跨版本、跨平台還原。

批次執行 SQL 檔

建立 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 > mytest.out       # 輸出到檔案