比較運算符
| 運算符 | 含義 | 示例 |
|---|
= | 等於 | WHERE id = 1001 |
<>, != | 不等於 | WHERE status <> 'deleted' |
<, >, <=, >= | 大小比較 | WHERE age >= 18 |
BETWEEN ... AND ... | 閉區間範圍 | WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' |
邏輯運算符與優先級
-- AND 優先級高於 OR，建議顯式加括號
SELECT * FROM student
WHERE (major = 'CS' OR major = 'EE') AND gender = 'M';
-- IN 替換多個 OR，優化器可轉為範圍查詢
SELECT * FROM student WHERE stu_id IN (1001, 1002, 1003);
-- NOT 取反
SELECT * FROM student WHERE major NOT IN ('CS', 'EE');
模糊查詢：LIKE 與通配符
| 通配符 | 匹配規則 | 示例 | 說明 |
|---|
% | 任意長度字符（含 0 個） | WHERE major LIKE '%工程' | 以「工程」結尾 |
_ | 單個字符 | WHERE name LIKE '王_' | 姓王、雙字名 |
ESCAPE | 轉義符 | WHERE code LIKE '%/_%' ESCAPE '/' | 查詢含底線的代碼 |
性能提示： 前導通配符 %xxx 無法利用索引，導致全表掃描。後綴 xxx% 可用索引範圍掃描。
聚合與分組查詢
-- 基礎聚合
SELECT COUNT(*), SUM(score), AVG(score), MAX(score), MIN(score) FROM exam;
-- 分組統計
SELECT class, COUNT(*) AS cnt, AVG(score) AS avg_score
FROM exam
GROUP BY class;
-- 分組後過濾（HAVING 用於聚合條件）
SELECT class, COUNT(*) AS cnt
FROM exam
GROUP BY class
HAVING COUNT(*) > 30;
約束定義速查
CREATE TABLE demo (
id INT AUTO_INCREMENT PRIMARY KEY, -- 主鍵：非空 + 唯一
email VARCHAR(100) NOT NULL UNIQUE, -- 非空 + 唯一
status TINYINT NOT NULL DEFAULT 1, -- 非空 + 默認值
parent_id INT,
CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES demo(id)
ON DELETE SET NULL ON UPDATE CASCADE
);
樣例表結構（附錄）
CREATE TABLE customers (
cust_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cust_name CHAR(50) NOT NULL,
cust_email VARCHAR(255)
);
CREATE TABLE orders (
order_num INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_date DATETIME NOT NULL,
cust_id INT NOT NULL,
FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
);
