SELECT [ALL | DISTINCT] [TOP n [PERCENT]] 選取清單
FROM 資料表來源
[WHERE 搜尋條件]
[ORDER BY 排序清單];
SELECT Sname, Sage FROM Student;
SELECT * FROM Student; -- 開發除錯用,生產環境避免使用 *
SELECT DISTINCT Sname FROM Student;
SELECT TOP 3 * FROM Student; -- 前 3 筆
SELECT TOP 50 PERCENT * FROM Student; -- 前 50%
-- 方法一:直接指定
SELECT Sno 學號, Sname 姓名, Sage 年齡 FROM Student;
-- 方法二:使用 AS (建議,可讀性佳)
SELECT Sno AS 學號, Sname AS 姓名, Sage AS 年齡 FROM Student;
SELECT 100 - Sage AS 壽命 FROM Student;
SELECT * FROM SC WHERE Grade >= 90;
SELECT * FROM Student WHERE Sage = 19 AND Ssex = '男';
SELECT * FROM Student WHERE Sage = 19 OR Sage = 20;
SELECT * FROM Student WHERE NOT Sage = 19;
包含端點的閉區間。
SELECT * FROM Student WHERE Sage BETWEEN 18 AND 20;
SELECT * FROM Student WHERE Sage NOT BETWEEN 18 AND 19;
SELECT * FROM Student WHERE Sage IN (18, 19);
SELECT * FROM Student WHERE Sdept NOT IN ('CS', 'EE');
| 萬用字元 | 說明 | 範例 | 符合範例 |
|---|---|---|---|
| % | 零或多個任意字元 | LIKE '王%' | 王小明、王大同 |
| _ | 單一任意字元 | LIKE '_三_' | 張三豐、李三槍 |
| [...] | 括號內任一字元 | LIKE '[張李王]三' | 張三、李三、王三 |
| [^...] | 非括號內字元 | LIKE '[^張李王]三' | 趙三、孫三 |
SELECT * FROM Student WHERE Sname LIKE '王%';
SELECT * FROM Student WHERE Sname LIKE '%四%';
SELECT * FROM Student WHERE Sname IS NULL;
SELECT * FROM Student WHERE Sname IS NOT NULL;
SELECT * FROM Student
WHERE (Sage = 19 OR Sage = 20)
AND Ssex = '男'
AND Sdept IN ('CS', 'EE');
| 函數 | 說明 |
|---|---|
| SUM() | 數值欄位總和 |
| AVG() | 平均值 |
| MIN() / MAX() | 最小/最大值 |
| COUNT(欄位) | 非 NULL 個數 |
| COUNT(*) | 列數 (含 NULL) |
SELECT AVG(Grade) AS 平均分數 FROM SC;
SELECT SUM(Grade) AS 總分 FROM SC;
SELECT COUNT(Grade) AS 成績筆數, COUNT(*) AS 總列數 FROM SC;
OFFSET ... FETCH,舊版用 ROW_NUMBER()。掌握 SELECT 的投影 (欄位選取/別名/計算) 與選擇 (WHERE 條件/運算子/萬用字元) 技巧,是撰寫高效率查詢的基石。結合彙總函數與 GROUP BY,即可完成大多數報表與分析需求。