SQL Server 投影查詢與選擇查詢完整教學

涵蓋 SELECT 基礎語法：投影 (選取欄位)、別名、TOP/DISTINCT、條件篩選 (WHERE)、邏輯運算子、BETWEEN/IN/LIKE/IS NULL、彙總函數等核心查詢技巧。

投影查詢

-- 指定欄位
SELECT Sname, Sage FROM Student;

-- 所有欄位
SELECT * FROM Student;

-- 去除重複
SELECT DISTINCT Sname FROM Student;

-- 前 N 筆 / 前 N%
SELECT TOP 3 * FROM Student;
SELECT TOP 50 PERCENT * FROM Student;

-- 欄位別名
SELECT Sno AS 學號, Sname AS 姓名, Sage AS 年齡 FROM Student;
SELECT Sno 學號, Sname 姓名, Sage 年齡 FROM Student;  -- 省略 AS

-- 計算欄位
SELECT 100 - Sage AS 壽命 FROM Student;

選擇查詢 (WHERE 條件)

比較運算子

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;

BETWEEN (閉區間)

SELECT * FROM Student WHERE Sage BETWEEN 18 AND 20;
SELECT * FROM Student WHERE Sage NOT BETWEEN 18 AND 19;

IN / NOT IN

SELECT * FROM Student WHERE Sage IN (18, 19);

LIKE 與萬用字元

SELECT * FROM Student WHERE Sname LIKE '王%';      -- 姓王
SELECT * FROM Student WHERE Sname LIKE '%四%';     -- 含「四」

IS NULL / IS NOT NULL

SELECT * FROM Student WHERE Sname IS NOT NULL;

彙總函數

SELECT AVG(Grade) AS 平均分數 FROM SC;
SELECT SUM(Grade) AS 總分 FROM SC;
SELECT COUNT(Grade) AS 筆數 FROM SC;   -- 不含 NULL
SELECT COUNT(*) AS 總列數 FROM SC;     -- 含 NULL

提醒： 大量資料表避免使用 SELECT * 或無條件 COUNT(*)，會造成全表掃描與鎖爭用。