萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 投影查詢與選擇查詢全攻略:SELECT 進階技巧

基本查詢架構


SELECT [ALL | DISTINCT] [TOP n [PERCENT]] 選取清單
FROM 資料表來源
[WHERE 搜尋條件]
[ORDER BY 排序清單];

投影查詢

選取特定欄位


SELECT Sname, Sage FROM Student;

選取所有欄位


SELECT * FROM Student;  -- 開發除錯用,生產環境避免使用 *

去除重複列 (DISTINCT)


SELECT DISTINCT Sname FROM Student;

限制回傳筆數 (TOP)


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

欄位別名 (AS)


-- 方法一:直接指定
SELECT Sno 學號, Sname 姓名, Sage 年齡 FROM Student;

-- 方法二:使用 AS (建議,可讀性佳)
SELECT Sno AS 學號, Sname AS 姓名, Sage AS 年齡 FROM Student;

計算欄位


SELECT 100 - Sage AS 壽命 FROM Student;

選擇查詢 (WHERE 子句)

關係運算子


SELECT * FROM SC WHERE Grade >= 90;

邏輯運算子 (AND, OR, NOT)


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);
SELECT * FROM Student WHERE Sdept NOT IN ('CS', 'EE');

模糊比對 (LIKE 與萬用字元)

萬用字元說明範例符合範例
%零或多個任意字元LIKE '王%'王小明、王大同
_單一任意字元LIKE '_三_'張三豐、李三槍
[...]括號內任一字元LIKE '[張李王]三'張三、李三、王三
[^...]非括號內字元LIKE '[^張李王]三'趙三、孫三

SELECT * FROM Student WHERE Sname LIKE '王%';
SELECT * FROM Student WHERE Sname LIKE '%四%';

NULL 判斷 (IS NULL / IS NOT NULL)


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;

實務提醒

  • 避免 SELECT *:大量資料表會造成網路傳輸與 I/O 浪費,且無法利用覆蓋索引。
  • 善用索引:WHERE、JOIN、ORDER BY 欄位應建立適當索引。
  • 參數化查詢:防止 SQL Injection,促進計畫重用。
  • 分頁查詢:SQL Server 2012+ 使用 OFFSET ... FETCH,舊版用 ROW_NUMBER()。

總結

掌握 SELECT 的投影 (欄位選取/別名/計算) 與選擇 (WHERE 條件/運算子/萬用字元) 技巧,是撰寫高效率查詢的基石。結合彙總函數與 GROUP BY,即可完成大多數報表與分析需求。

copyright © 萬盛學電腦網 all rights reserved