-- 指定欄位
SELECT col1, col2 FROM table_name;
-- 全欄位
SELECT * FROM table_name;
-- 單行
$sql = "SELECT * FROM article WHERE id=1";
-- 多行(推薦用於複雜查詢)
$sql = "SELECT * FROM article
WHERE id = 1";
SELECT * FROM article WHERE id IN (1,3,5);
SELECT CONCAT(id, '-', content) AS res FROM article WHERE id=1;
-- 返回 "1-article content"
SELECT LOCATE('hello', 'hello baby'); -- 返回 1
SELECT LOCATE('xyz', 'hello baby'); -- 返回 0(不存在)
-- 統計每個城市的客戶數
SELECT city, COUNT(*) FROM customer GROUP BY city;
-- 常搭配聚合函數:AVG(), MIN(), MAX(), SUM(), COUNT()
-- 僅顯示客戶數 > 10 的城市
SELECT city, COUNT(*), MIN(birth_day)
FROM customer
GROUP BY city
HAVING COUNT(*) > 10;
注意: HAVING 條件必須包含在 SELECT 列表或聚合表達式中;GROUP BY + HAVING較WHERE慢,能用 WHERE 先過濾盡量用 WHERE。
WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
編寫 SQL 時按此順序排列,邏輯最清晰。
SELECT DISTINCT city FROM customer ORDER BY id DESC;
-- 前 100 條
SELECT * FROM article LIMIT 100;
-- 第 101 條開始的所有記錄(MySQL 特有寫法)
SELECT * FROM article LIMIT 100, -1;
-- 標準分頁:第 3 頁,每頁 20 條
SELECT * FROM article LIMIT 40, 20;
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;
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。
<?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% 以上的業務查詢場景。複雜報表建議下推到數據庫層完成,減少應用層數據傳輸與處理開銷。