15 個 SQL 入門必會的基礎查詢語句：從建表到存儲過程完整實戰

SQL 是關聯式資料庫的通用語言，掌握核心查詢模式是所有資料從業者的基本功。本文以餐廳訂單管理為實戰場景，建立三張核心表，依循建表 → 基礎查詢 → 聚合/日期/條件 → 多表聯結 → 進階分析 (CTE/視圖/PIVOT/存儲過程/函數) 的學習路徑，呈現 15 個必會查詢範例，程式碼可直接複製執行。

場景資料模型

• ItemMasters (產品主檔)：Item_Code (PK)、Item_Name、Price、Tax、Discount...
• OrderMasters (訂單主檔)：Order_No (PK)、Table_ID、Description...
• OrderDetails (訂單明細)：Order_Detail_No (PK)、Order_No (FK)、Item_Code (FK)、Qty...

建表與測試資料腳本： (請先執行建立環境)

-- 完整建表腳本請參考原文，此處略去以節省篇幅
-- 關鍵點：PK、FK 關聯、叢集索引、預設值

1. 基礎 SELECT 查詢

-- 常數輸出與別名
SELECT 'My Name Is SYED SHANU' AS [MY NAME];
SELECT 'My Name' AS Column1, 'Is' AS Column2, 'SYED SHANU' AS Column3;

-- 全欄位與指定欄位
SELECT * FROM ItemMasters;
SELECT Item_Code, Item_Name AS Item, Price, Description, IN_DATE FROM ItemMasters;

2. 聚合與標量函數

-- 聚合
SELECT COUNT(*) TotalRows, AVG(Price) AvgPrice, MAX(Price) MaxPrice,
       MIN(Price) MinPrice, SUM(Price) PriceTotal FROM ItemMasters;

-- 標量字串/數學函數
SELECT UPPER(Item_Name) Uppers, LOWER(Item_Name) Lowers,
       SUBSTRING(Item_Name, 2, 3) MidValue, LEN(Item_Name) Lens,
       ROUND(Price, 0) Rounded FROM ItemMasters;

3. 日期函數實戰

-- 格式化輸出
SELECT GETDATE() CurrentDT,
       FORMAT(GETDATE(), 'yyyy-MM-dd') AS DateOnly,
       FORMAT(GETDATE(), 'HH-mm-ss') AS TimeOnly,
       CONVERT(VARCHAR(10), GETDATE(), 110) AS Conv110,
       REPLACE(CONVERT(NVARCHAR, GETDATE(), 106), ' ', '/') AS CustomFmt;

-- 日期運算
SELECT DATEPART(yyyy, GETDATE()) AS Yr, DATEPART(mm, GETDATE()) AS Mo,
       DATEPART(dd, GETDATE()) AS Dy, DATEPART(hour, GETDATE()) AS Hr;
SELECT DATEADD(day, 12, GETDATE()) AS Add12Days, DATEADD(day, -4, GETDATE()) AS Minus4Days;
SELECT DATEDIFF(year, '2003-08-05', GETDATE()) AS YearDiff,
       DATEDIFF(day, DATEADD(day,-24,GETDATE()), GETDATE()) AS DayDiff;

4. TOP、ORDER BY、DISTINCT

-- 前 2 筆
SELECT TOP 2 Item_Code, Item_Name, Price FROM ItemMasters;
-- 後 2 筆 (需 ORDER BY DESC)
SELECT TOP 2 Item_Code, Item_Name, Price FROM ItemMasters ORDER BY Item_Code DESC;
-- 去重
SELECT DISTINCT Item_Name, Price, Description FROM ItemMasters;

5. WHERE 條件過濾

-- 精確比對
SELECT * FROM ItemMasters WHERE Item_Name = 'COKE';
-- 模糊比對 (以 C 開頭)
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%';
-- 多條件 AND/OR
SELECT * FROM ItemMasters WHERE Item_Name LIKE 'C%' AND Price >= 40;
-- 日期區間
SELECT * FROM ItemMasters WHERE IN_DATE BETWEEN '2014-09-22' AND '2014-09-23';
-- IN 清單
SELECT * FROM ItemMasters WHERE Item_Name IN ('Coffee','Chiken Burger') ORDER BY Item_Code DESC;

6. GROUP BY 與 HAVING

-- 單欄分組聚合
SELECT Item_Name, COUNT(*) Cnt, AVG(Price) AvgP FROM ItemMasters GROUP BY Item_Name;
-- 多欄分組
SELECT Order_No, Item_Code, SUM(Qty) TotalQty FROM OrderDetails WHERE Qty >= 2
GROUP BY Order_No, Item_Code ORDER BY Order_No DESC, Item_Code;
-- HAVING 過濾聚合後結果
SELECT Order_No, SUM(Qty) TotalQty FROM OrderDetails
GROUP BY Order_No HAVING SUM(Qty) > 4;

7. 子查詢

-- WHERE IN 子查詢
SELECT * FROM ItemMasters WHERE Item_Code IN
    (SELECT Item_Code FROM ItemMasters WHERE Price > 40);
-- INSERT 配合子查詢複製資料
INSERT INTO ItemMasters (Item_Code, Item_Name, Price, ...)
SELECT 'Item006', Item_Name, Price+4, ... FROM ItemMasters WHERE Item_Code='Item002';

8. 多表聯結 (JOIN)

-- 簡易 INNER JOIN 三表
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name,
       D.Notes, D.Qty, I.Price, I.Price * D.Qty AS TotalPrice
FROM OrderMasters M
INNER JOIN OrderDetails D ON M.Order_No = D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code = I.Item_Code
WHERE M.Table_ID LIKE 'T%';

-- LEFT/RIGHT/FULL OUTER JOIN 同理，換關鍵字即可

9. UNION / UNION ALL 合併結果集

-- 價格 <=44 與 >44 合併
SELECT Item_Code, Item_Name, Price, Description FROM ItemMasters WHERE Price <= 44
UNION ALL
SELECT Item_Code, Item_Name, Price, Description FROM ItemMasters WHERE Price > 44;
-- UNION 去重，UNION ALL 保留重複，效能較好

10. CTE (Common Table Expression) 遞迴與暫時結果集

DECLARE @sDate DATETIME = DATEADD(day,-5,GETDATE()), @eDate DATETIME = DATEADD(day,16,GETDATE());
WITH cte AS (
    SELECT @sDate AS StartDate, 'W'+CAST(DATEPART(wk,@sDate) AS VARCHAR)+'('+CONVERT(VARCHAR,@sDate,106)+')' AS SDT
    UNION ALL
    SELECT DATEADD(day,1,StartDate), 'W'+CAST(DATEPART(wk,DATEADD(day,1,StartDate)) AS VARCHAR)+'('+CONVERT(VARCHAR,DATEADD(day,1,StartDate),106)+')'
    FROM cte WHERE DATEADD(day,1,StartDate) <= @eDate
)
SELECT * FROM cte OPTION (MAXRECURSION 0);

11. 視圖 封裝複雜查詢

CREATE VIEW myUnionView AS
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.Qty, I.Price, I.Price*D.Qty AS TotalPrice
FROM OrderMasters M INNER JOIN OrderDetails D ON M.Order_No=D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code=I.Item_Code WHERE I.Price <= 44
UNION ALL
SELECT M.Order_No, M.Table_ID, D.Order_Detail_No, I.Item_Name, D.Notes, D.Qty, I.Price, I.Price*D.Qty
FROM OrderMasters M INNER JOIN OrderDetails D ON M.Order_No=D.Order_No
INNER JOIN ItemMasters I ON D.Item_Code=I.Item_Code WHERE I.Price > 44;

-- 使用視圖如同資料表
SELECT * FROM myUnionView WHERE Price > 40;

12. PIVOT 行轉欄

SELECT * FROM (
    SELECT Item_Name, Price AS TotAmount FROM ItemMasters
) src
PIVOT (SUM(TotAmount) FOR Item_Name IN ([Chiken Burger],[Coffee],[Coke])) pvt;

13. 預存程序 封裝商業邏輯

CREATE PROCEDURE usp_SelectPivot AS
BEGIN
    DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
    SELECT @cols = STUFF((SELECT ','+QUOTENAME(Item_Name) FROM ItemMasters GROUP BY Item_Name ORDER BY Item_Name FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'');
    SET @sql = N'SELECT '+@cols+N' FROM (SELECT Item_Name,Price AS TotAmount FROM ItemMasters) x PIVOT(SUM(TotAmount) FOR Item_Name IN ('+@cols+N')) p';
    EXEC sp_executesql @sql;
END;
GO
-- 執行
EXEC usp_SelectPivot;

14. 使用者定義函數 (UDF)

-- 純量函數：取得 ItemMasters 筆數+1
CREATE FUNCTION ufnSelectitemMaster() RETURNS INT AS
BEGIN DECLARE @c INT; SELECT @c=COUNT(*)+1 FROM ItemMasters; RETURN @c; END;
GO
SELECT dbo.ufnSelectitemMaster();

-- 取得指定月份最後一天
CREATE FUNCTION ufn_LastDayOfMonth(@DATE NVARCHAR(10)) RETURNS NVARCHAR(10) AS
BEGIN RETURN CONVERT(NVARCHAR(10), DATEADD(day,-1,DATEADD(month,1,CAST(SUBSTRING(@DATE,1,7)+'-01' AS DATETIME))), 120); END;
GO
SELECT dbo.ufn_LastDayOfMonth('2014-09-01');

15. 進階提示：開窗函數執行順序

OVER (PARTITION BY ... ORDER BY ...) 的分組與排序晚於 WHERE、GROUP BY、ORDER BY 執行。範例：

SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InsDT) AS Rows, *
FROM OP_Order WHERE InsDT > '2011-07-22';
-- 先依 WHERE 過濾，再對剩餘資料分組排序編號

完成這 15 個範例的練習，即可建立扎實的 SQL 查詢基礎，進階學翤效能調校、資料模型設計將更得心應手。