萬盛學電腦網

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

MySQL SELECT 進階技巧大全:從基礎查詢到多表關聯與 PHP 實戰

基礎查詢回顧

-- 指定欄位
SELECT col1, col2 FROM table_name;

-- 全欄位
SELECT * FROM table_name;

11 個實用技巧

1. SQL 多行書寫提升可讀性

-- 單行
$sql = "SELECT * FROM article WHERE id=1";

-- 多行(推薦用於複雜查詢)
$sql = "SELECT * FROM article 
        WHERE id = 1";

2. 批量查詢用 IN

SELECT * FROM article WHERE id IN (1,3,5);

3. CONCAT 字符串拼接

SELECT CONCAT(id, '-', content) AS res FROM article WHERE id=1;
-- 返回 "1-article content"

4. LOCATE 子串定位

SELECT LOCATE('hello', 'hello baby'); -- 返回 1
SELECT LOCATE('xyz', 'hello baby');   -- 返回 0(不存在)

5. GROUP BY 分組聚合

-- 統計每個城市的客戶數
SELECT city, COUNT(*) FROM customer GROUP BY city;

-- 常搭配聚合函數:AVG(), MIN(), MAX(), SUM(), COUNT()

6. HAVING 分組過濾

-- 僅顯示客戶數 > 10 的城市
SELECT city, COUNT(*), MIN(birth_day) 
FROM customer 
GROUP BY city 
HAVING COUNT(*) > 10;
注意: HAVING 條件必須包含在 SELECT 列表或聚合表達式中;GROUP BY + HAVING 較 WHERE 慢,能用 WHERE 先過濾盡量用 WHERE。

7. 子句執行順序

WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

編寫 SQL 時按此順序排列,邏輯最清晰。

8. DISTINCT 去重

SELECT DISTINCT city FROM customer ORDER BY id DESC;

9. LIMIT 分頁與偏移

-- 前 100 條
SELECT * FROM article LIMIT 100;

-- 第 101 條開始的所有記錄(MySQL 特有寫法)
SELECT * FROM article LIMIT 100, -1;

-- 標準分頁:第 3 頁,每頁 20 條
SELECT * FROM article LIMIT 40, 20;

10. 多表關聯查詢

隱式內連接(舊寫法,不推薦)

SELECT u.user_name 
FROM user u, member m 
WHERE u.id = m.id 
  AND m.reg_date >= '2006-12-28'
ORDER BY u.id DESC;

顯式 JOIN(推薦,語義清晰)

SELECT p.name, COUNT(*)
FROM unicom_record AS ur
LEFT JOIN sheet AS s ON ur.sheet_id = s.id
LEFT JOIN promotion_addr AS p ON s.promotion_addr_id = p.id
WHERE ur.status = 'COMPLETE'
  AND ur.`date` >= '2010-03-01'
  AND ur.`date` < '2010-04-01'
GROUP BY p.id;
避坑: 多表同名欄位必須加表別名限定(如 u.user_name),否則報錯 Column 'user_name' in field list is ambiguous。

11. PHP + MySQL 完整查詢範例(mysqli 預處理版)

<?php
$mysqli = new mysqli("localhost", "root", "root123", "test");
if ($mysqli->connect_errno) {
    die("連接失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8mb4"); // 推薦 utf8mb4

$sql = "SELECT username, email, regdate FROM user";
$stmt = $mysqli->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "用戶名: {$row['username']}
"; echo "電子郵件: {$row['email']}
"; echo "註冊日期: " . date("Y-m-d", $row['regdate']) . "

"; } $stmt->close(); $mysqli->close(); ?>
現代化建議: 已棄用 mysql_* 函數,請使用 mysqli 或 PDO,並採用預處理語句防止 SQL 注入。

總結

熟練掌握 JOIN、GROUP BY/HAVING、LIMIT、DISTINCT 等核心語法,配合索引設計與預處理語句,即可應對 90% 以上的業務查詢場景。複雜報表建議下推到數據庫層完成,減少應用層數據傳輸與處理開銷。

copyright © 萬盛學電腦網 all rights reserved