語法結構
SELECT col1, col2 ...
FROM table_name
WHERE condition1 [AND|OR condition2] ...
常用運算符總表
| 運算符 | 含義 | 示例 |
|---|
| = | 等於 | age = 18 |
| <> / != | 不等於 | status <> 0 |
| >, <, >=, <= | 比較 | score >= 60 |
| BETWEEN ... AND ... | 範圍（含邊界） | age BETWEEN 18 AND 60 |
| IN (val1, val2...) | 集合匹配 | city IN ('北京','上海') |
| LIKE | 模糊匹配 | name LIKE '張%' |
| IS NULL / IS NOT NULL | 空值判斷 | email IS NULL |
| AND / OR / NOT | 邏輯組合 | age > 18 AND status = 1 |
字符串比較大小寫敏感性
- 默認不區分大小寫：
'sanjay' = 'Sanjay' 為真
- 強制區分：
WHERE BINARY tutorial_author = 'sanjay' 或 WHERE tutorial_author COLLATE utf8mb4_bin = 'sanjay'
- 主鍵/唯一索引列查詢最快，建議優先使用。
命令行示例
mysql> SELECT * FROM tutorials_tbl WHERE tutorial_author = 'Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 3 | JAVA Tutorial | Sanjay | 2007-05-21 |
+-------------+----------------+-----------------+-----------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM tutorials_tbl WHERE BINARY tutorial_author = 'sanjay';
Empty set (0.02 sec)
PHP 參數化查詢（防注入必須）
<?php
$mysqli = new mysqli('localhost', 'root', 'rootpassword', 'TUTORIALS', 3306);
if ($mysqli->connect_errno) { die('連接失敗: ' . $mysqli->connect_error); }
$author = 'Sanjay'; // 來自用戶輸入
$stmt = $mysqli->prepare("SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl WHERE tutorial_author = ?");
$stmt->bind_param("s", $author);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "ID: {$row['tutorial_id']} | 標題: {$row['tutorial_title']} | 作者: {$row['tutorial_author']} | 日期: {$row['submission_date']}<br>";
}
$stmt->close();
$mysqli->close();
?>
進階技巧
WHERE 1=1 作為動態拼接條件的起始點，便於後續 AND col = ?。- 範圍查詢優於函數：
WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01' 可用索引，WHERE MONTH(create_time) = 1 導致全表掃描。
IN 列表過長（>1000）時考慮臨時表或 JOIN 優化。
