SELECT 核心語法結構
SELECT [ALL | DISTINCT] [TOP (n) [WITH TIES]]
FROM
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY [ASC | DESC]]
[OFFSET ROWS FETCH NEXT ROWS ONLY] -- SQL 2012+
[FOR ] -- 進階輸出
1. 基礎查詢與別名
-- 查詢所有欄位
SELECT * FROM dbo.Products;
-- 指定欄位 + 別名 (AS 可省略但建議保留)
SELECT ProductID AS ID, ProductName AS Name, UnitPrice AS Price
FROM dbo.Products;
-- 運算式與函數
SELECT ProductName, UnitPrice * UnitsInStock AS TotalValue, GETDATE() AS QueryTime
FROM dbo.Products;
2. WHERE 條件篩選
-- 比較運算子
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01' AND Freight > 100;
-- 邏輯運算子 (優先順序：NOT > AND > OR，括號改變順序)
SELECT * FROM Customers
WHERE (Country = 'USA' OR Country = 'Canada') AND Region IS NOT NULL;
-- 範圍與集合
SELECT * FROM Products WHERE UnitPrice BETWEEN 10 AND 50;
SELECT * FROM Employees WHERE City IN ('Seattle', 'Redmond', 'Kirkland');
-- 模糊查詢 (索引失效警示)
SELECT * FROM Customers WHERE ContactName LIKE 'A%'; -- 可用索引
SELECT * FROM Customers WHERE ContactName LIKE '%Smith'; -- 全掃描
-- NULL 判斷
SELECT * FROM Suppliers WHERE Fax IS NULL;
SELECT * FROM Suppliers WHERE Fax IS NOT NULL;
3. JOIN 多表關聯查詢
| JOIN 類型 | 語法 | 結果集 | 適用場景 |
|---|
| INNER JOIN | A JOIN B ON A.ID = B.AID | 雙方都符合條列 | 主從表關聯、維度表查詢 |
| LEFT JOIN | A LEFT JOIN B ON A.ID = B.AID | 左表全保留，右表無配對則 NULL | 主表帶明細、報表不遺漏主記錄 |
| RIGHT JOIN | A RIGHT JOIN B ON A.ID = B.AID | 右表全保留 | 較少用，可改 LEFT JOIN 交換順序 |
| FULL JOIN | A FULL JOIN B ON A.ID = B.AID | 雙方全保留 | 雙向對帳、合併異源資料 |
| CROSS JOIN | A CROSS JOIN B | 笛卡兒積 (M×N 列) | 生成測試資料、矩陣運算 |
實戰範例：訂單明細含產品名稱
SELECT o.OrderID, o.OrderDate, c.CompanyName AS Customer,
p.ProductName, od.UnitPrice, od.Quantity, od.Discount
FROM dbo.Orders o
INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
INNER JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
INNER JOIN dbo.Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01'
ORDER BY o.OrderID, od.ProductID;
4. 排序 ORDER BY
-- 多欄位排序：先按國家升序，同國家按城市降序
SELECT * FROM Customers ORDER BY Country ASC, City DESC;
-- 排序運算式/別名
SELECT ProductName, UnitPrice * UnitsInStock AS StockValue
FROM Products ORDER BY StockValue DESC;
-- NULL 排序：NULLS FIRST / LAST (SQL Server 2022+ / PostgreSQL)
-- SQL Server 傳統做法：
ORDER BY CASE WHEN Discount IS NULL THEN 1 ELSE 0 END, Discount DESC;
5. 分頁查詢 (現代標準語法)
-- SQL Server 2012+ 標準語法
DECLARE @PageSize INT = 20, @PageIndex INT = 3;
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY ProductID
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- 參數化存儲過程
CREATE PROCEDURE dbo.GetProductsPage
@PageIndex INT = 1, @PageSize INT = 20, @TotalCount INT OUTPUT
AS
BEGIN
SELECT @TotalCount = COUNT(*) FROM dbo.Products;
SELECT ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY ProductID
OFFSET (@PageIndex - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
END;
6. LIMIT / TOP 對比 (跨資料庫)
| 資料庫 | 語法 | 範例 |
|---|
| SQL Server | TOP / OFFSET-FETCH | SELECT TOP 10 * FROM T / OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY |
| MySQL / PostgreSQL / SQLite | LIMIT / OFFSET | SELECT * FROM T LIMIT 20 OFFSET 10 |
| Oracle 12c+ | OFFSET-FETCH | OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY |
| Oracle 11g- | ROWNUM 子查詢 | SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM T) a WHERE ROWNUM <= 30) WHERE rn > 10 |
7. 常見陷阱與最佳實踐
- SELECT * 禁用於生產代碼：網路傳輸、計畫快取、Schema 變更衝擊。明確列出欄位。
- WHERE 條件勿對欄位用函數：
WHERE YEAR(OrderDate)=2024 → 索引失效。改 WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'。
- 參數化查詢防注入：
WHERE Name = @Name，絕不拼接字串。
- DISTINCT 濫用：掩蓋 JOIN 重複列問題，優先修正 JOIN 條件/關聯基數。
- ORDER BY 無索引支援 = 大排序：大資料量需覆蓋索引或 Top-N 排序優化。
8. 進階：條件彙總 / 樞紐分析
-- 按年月統計訂單數與金額
SELECT
YEAR(OrderDate) AS Year,
MONTH(OrderDate) AS Month,
COUNT(*) AS OrderCount,
SUM(TotalAmount) AS TotalSales,
SUM(CASE WHEN Status = 'Shipped' THEN 1 ELSE 0 END) AS ShippedCount
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;
