萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 多表操作入門:關聯查詢、連接類型與實戰範例

從單表到多表:掌握 MySQL 關聯查詢核心技能

真實業務中,資料分散在多張關聯表中。本文以員工表 mytable 與文章表 title 為例,循序漸進講解多表查詢語法、連接類型與別名技巧。

範例資料準備

表 1:員工基本資料 mytable

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

多表查詢:隱式內連接(舊語法)

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

結果:

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

缺點:WHERE 混雜關聯條件與過濾條件,可讀性差,易漏寫關聯條件導致笛卡兒積。

多表查詢:顯式 JOIN 語法(ANSI SQL-92 標準,推薦)

INNER JOIN(內連接)—— 只回傳雙方都匹配的列

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

LEFT JOIN(左連接)—— 保留左表所有列,右表無匹配則 NULL

-- 查詢所有員工及其文章(沒發文也要列出)
SELECT m.name, m.sex, t.title
FROM mytable m
LEFT JOIN title t ON m.name = t.writer;

結果包含 mary(有文章)與 tom(有文章),若有員工從未發文,title 顯示 NULL。

RIGHT JOIN / FULL JOIN

  • RIGHT JOIN:保留右表所有列(較少用,可改寫 LEFT JOIN)。
  • MySQL 不支援 FULL JOIN,可用 LEFT JOIN UNION RIGHT JOIN 模擬。

欄位同名時的別名技巧

若兩表關聯欄位同名(如皆為 name),必須加表前綴或別名區分:

SELECT m.name, m.sex, t.title
FROM mytable m
JOIN title t ON m.name = t.name  -- 這裡 t.name 指 title 表的 name 欄位
WHERE m.name = 'abccs';

最佳實務:永遠給表起別名(m、t),所有欄位引用均加前綴 m.name、t.title,消除歧義、提升可讀性。

多表查詢效能提示

  1. 關聯欄位必須建索引:mytable.name、title.writer 加索引,否則大表 JOIN 會全表掃描。
  2. 小表驅動大表:MySQL 優化器傾向用小表結果集驅動大表索引查詢;必要時用 STRAIGHT_JOIN 強制順序。
  3. 只 SELECT 需要的欄位:避免 SELECT * 傳輸無用資料、佔用網路與記憶體。
  4. 善用 EXPLAIN:觀察 type(ref/eq_ref 佳)、rows(預估掃描列數)、Extra(Using where/Using index)。

延伸:多表 UPDATE / DELETE

-- 多表 UPDATE(同步文章作者性別到文章表冗余欄位)
UPDATE title t
INNER JOIN mytable m ON t.writer = m.name
SET t.author_sex = m.sex;

-- 多表 DELETE(刪除從未發文的員工記錄)
DELETE m
FROM mytable m
LEFT JOIN title t ON m.name = t.writer
WHERE t.writer IS NULL;
copyright © 萬盛學電腦網 all rights reserved