您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

《MySQL必知必會》讀書筆記（三）：聚合函數、分組、聯結、全文搜尋與 DDL 實戰

編輯：MySQL 綜合教學

《MySQL必知必會》讀書筆記（三）：聚合函數、分組、聯結、全文搜尋與 DDL 實戰

本章內容密度極大，涵蓋聚合進階、GROUP BY 分組邏輯、多表聯結七大類型、UNION 組合查詢、全文搜尋三種模式、資料異動與 DDL 操作、存儲過程參數化調用，是從「會寫 SQL」邁向「寫好 SQL」的關鍵轉折點。

一、聚合函數進階用法

1. 去重聚合

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products WHERE vend_id = 1003;

相同價格僅計算一次，適用於去除重複干擾的統計場景。

2. 組合聚合

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

單次掃描完成多維統計，避免多次全表掃描。

二、GROUP BY 分組查詢

-- 無分組：回傳所有 vend_id（含重複）
SELECT vend_id FROM products;

-- 有分組：每廠商產品數
SELECT vend_id, COUNT(*) AS num_prods
FROM products GROUP BY vend_id;

核心規則： SELECT 非聚合欄位必須出現在 GROUP BY 中；分組後可接 HAVING 過濾聚合結果。

三、多表聯結：從笛卡爾積到外連接

1. 內連接（標準寫法）

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

2. 笛卡爾積警示

缺少 ON/WHERE 條件時，結果行數 = 表 A 行數 × 表 B 行數，資料量大時極其危險。

3. 外連接：保留單邊無匹配行

-- 左外連接：所有客戶，含無訂單者
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

MySQL 不支援舊式 *= 語法；LEFT/RIGHT 僅關聯順序不同，結果等價。

4. 攜帶聚合的聯結

-- 內連接：僅有訂單的客戶
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

-- 左外連接：含零訂單客戶
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

四、UNION 組合查詢

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);

預設 UNION 自動去重；
需保留重複行用 UNION ALL；
排序只能在最後整體 ORDER BY。

五、全文搜尋：FULLTEXT 索引與三種模式

1. 建立索引

CREATE TABLE notes (
    note_text TEXT,
    FULLTEXT(note_text)
) ENGINE=MyISAM;  -- 5.6+ InnoDB 也支援

大量導入資料時建議先關閉索引，導入完成後再啟用，避免即時維護開銷。

2. 三種搜尋模式

模式	語法	特點
自然語言	`MATCH(note_text) AGAINST('rabbit')`	相關性排序，不區分大小寫
查詢擴展	`AGAINST('anvils' WITH QUERY EXPANSION)`	兩階段：先搜關鍵字，再用高相關行關鍵字二次搜尋
布爾模式	`AGAINST('anvils' IN BOOLEAN MODE)`	支援 + - * ~ 等運算子，精準控制

六、資料異動與 DDL 速查

-- 插入查詢結果
INSERT INTO t1(f1,f2) SELECT f1,f2 FROM t2;

-- 多欄位更新
UPDATE t1 SET f1=v1, f2=v2 WHERE f3=v3;

-- 快速清空表（不記錄行刪除日誌）
TRUNCATE TABLE t1;

-- 建表範例（含引擎、自增、預設值、聯合主鍵）
CREATE TABLE vendors (
    vend_id INT NOT NULL AUTO_INCREMENT,
    vend_name CHAR(50) NOT NULL,
    vend_city CHAR(50) NULL,
    vend_age INT NOT NULL DEFAULT 1,
    PRIMARY KEY (vend_id, vend_name)
) ENGINE=InnoDB;

-- 外鍵不可跨引擎

-- 常用引擎特性對比
-- InnoDB：事務、行鎖、無全文（5.6前）；MEMORY：記憶體級速度；MyISAM：全文、表鎖、無事務

-- 修改表結構
ALTER TABLE t1 ADD f1 CHAR(20);
ALTER TABLE t1 DROP COLUMN f1;

-- 刪除/重命名表
DROP TABLE t1;
RENAME TABLE old_t TO new_t;

-- 存儲過程參數化
CREATE PROCEDURE sp(IN p1 INT, OUT p2 DECIMAL(8,2))
BEGIN
    SELECT MIN(f1) INTO p2 FROM t1 WHERE f2 = p1;
END;

CALL sp(@p1, @p2);
SELECT @p2;