MySQL 子查詢與連接進階：比較運算子、ANY/SOME/ALL、IN、EXISTS、多表更新

子查詢三大類型

1. 比較運算子：=, >, <, >=, <=, <> + 子查詢
2. IN / NOT IN：集合成員資格
3. EXISTS / NOT EXISTS：存在性檢查（回傳布林）

比較運算子子查詢

單行結果

-- 價格高於平均價的商品
SELECT goods_id, goods_name, goods_price
FROM tdb_goods
WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

多行結果 + ANY/SOME/ALL

• ANY/SOME：滿足任一即可。> ANY 等同 > (最小值)。
• ALL：必須滿足所有。> ALL 等同 > (最大值)。

-- 價格高於「筆記本」類別中任一商品的商品
SELECT * FROM tdb_goods
WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate='筆記本');

-- 價格高於「筆記本」類別中所有商品的商品
SELECT * FROM tdb_goods
WHERE goods_price > ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate='筆記本');

ANY/SOME 同義，寫在子查詢前。

IN / NOT IN 子查詢

-- 屬於「筆記本」或「超級本」類別的商品
SELECT * FROM tdb_goods
WHERE goods_cate IN (SELECT cate_name FROM cates WHERE cate_name IN ('筆記本','超級本'));

-- 等價性：IN = =ANY，NOT IN = <>ALL

EXISTS 子查詢

-- 存在至少一筆「筆記本」類別商品的品牌
SELECT * FROM brands b
WHERE EXISTS (SELECT 1 FROM tdb_goods g WHERE g.brand_id = b.brand_id AND g.goods_cate='筆記本');

EXISTS 只關心是否有行回傳，不關心具體值，常用 SELECT 1 或 SELECT *。效能通常優於 IN（尤其子查詢大、外查詢小時）。

INSERT ... SELECT：查詢結果寫入新表

-- 建立分類表並寫入去重分類名
CREATE TABLE cates (cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(40));
INSERT INTO cates(cate_name) SELECT DISTINCT goods_cate FROM tdb_goods;

多表更新

分步驟：INNER JOIN 更新

UPDATE tdb_goods AS g
INNER JOIN cates AS c ON g.goods_cate = c.cate_name
SET g.goods_cate = c.cate_id;

一步到位：CREATE ... SELECT

-- 建立品牌表並同步寫入
CREATE TABLE brands (
    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(40)
) SELECT DISTINCT brand_name FROM tdb_goods;

注意：若欄位名衝突（如雙方都有 brand_name），更新時需使用別名：

UPDATE tdb_goods AS g
INNER JOIN brands AS b ON g.brand_name = b.brand_name
SET g.brand_name = b.brand_id;

欄位型別調整：ALTER TABLE CHANGE

ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

將字串外鍵轉為整數外鍵（事實外鍵），效能優於物理外鍵。