掌握 SELECT 語句的各種子句組合,是高效檢索、分析數據的基石。本文系統梳理選擇列表、條件過濾、連接查詢、聚合分組、子查詢等核心知識點。
-- 全選 (生產環境避免使用)
SELECT * FROM Sales.Orders;
-- 顯式列名 + 別名 (推薦)
SELECT OrderID, CustomerID, OrderDate AS [下單日期]
FROM Sales.Orders;
-- 計算列 / 常量 / 表達式
SELECT OrderID,
UnitPrice * Qty * (1 - Discount) AS LineTotal,
'CN' AS Currency
FROM Sales.OrderDetails;
-- 去重
SELECT DISTINCT Country FROM Sales.Customers;
SELECT DISTINCT Country, City FROM Sales.Customers; -- 組合去重
| 運算符類別 | 運算符 | 示例 |
|---|---|---|
| 比較 | = | Status = 'Shipped' |
| <> / != | Status <> 'Cancelled' | |
| > / >= | OrderDate > '2024-01-01' | |
| < / <= | Qty <= 100 | |
| !> / !< | 非標準,不推薦 | |
| IS NULL / IS NOT NULL | Region IS NULL | |
| IS [NOT] DISTINCT FROM (SQL 標準) | 安全比較 NULL | |
| 邏輯 | AND / OR / NOT | (A > 10 AND B < 20) OR C = 'X' |
| IN / NOT IN | CategoryID IN (1,2,3) | |
| BETWEEN / NOT BETWEEN | OrderDate BETWEEN '2024-01-01' AND '2024-12-31' | |
| LIKE / NOT LIKE | ProductName LIKE 'A%' | |
| EXISTS / NOT EXISTS | EXISTS (SELECT 1 FROM ... WHERE ...) | |
| ANY / SOME / ALL | Price > ANY (SELECT Price FROM ...) | |
| 通配符 | 含義 | 示例匹配 |
|---|---|---|
| % | 任意長度字符(含0) | 'A%' → Apple, A, A_B |
| _ | 單個字符 | 'A_C' → ABC, AxC |
| [abc] | 字符集合中任一 | '[ABC]%' → Apple, Banana |
| [a-z] | 字符範圍 | '[0-9]%' → 123abc |
| [^abc] | 非字符集合 | '[^0-9]%' → 非數字開頭 |
-- 轉義通配符:查找含 % 的字符串
WHERE Comment LIKE '%/%%' ESCAPE '/';
-- 內連接:匹配行
SELECT o.OrderID, c.CompanyName
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID;
-- 左外連接:保留左表所有行
SELECT c.CustomerID, o.OrderID
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID;
-- 右外連接 / 完全外連接
-- 交叉連接:笛卡爾積
SELECT * FROM TableA CROSS JOIN TableB;
-- 自連接:員工-經理
SELECT e.EmpName AS Employee, m.EmpName AS Manager
FROM HR.Employees e
LEFT JOIN HR.Employees m ON e.ManagerID = m.EmployeeID;
-- 基礎聚合
SELECT COUNT(*) AS TotalOrders,
COUNT(DISTINCT CustomerID) AS UniqueCustomers,
SUM(UnitPrice * Qty) AS TotalRevenue,
AVG(UnitPrice) AS AvgPrice,
MIN(OrderDate) AS FirstOrder,
MAX(OrderDate) AS LastOrder
FROM Sales.Orders;
-- GROUP BY + HAVING
SELECT CustomerID, YEAR(OrderDate) AS OrderYear,
COUNT(*) AS OrderCount,
SUM(TotalAmount) AS YearlyTotal
FROM Sales.Orders
GROUP BY CustomerID, YEAR(OrderDate)
HAVING COUNT(*) > 5 AND SUM(TotalAmount) > 10000;
-- 分組集合 (SQL Server 2008+)
SELECT GROUPING_ID(CustomerID, YEAR(OrderDate)) AS GrpLevel,
CustomerID, YEAR(OrderDate), COUNT(*)
FROM Sales.Orders
GROUP BY GROUPING SETS (
(CustomerID, YEAR(OrderDate)),
(CustomerID),
(YEAR(OrderDate)),
()
);
-- 標量子查詢 (單值)
SELECT ProductName,
(SELECT AVG(UnitPrice) FROM Production.Products) AS AvgPrice
FROM Production.Products;
-- 相關子查詢 (逐行執行,慎用大數據量)
SELECT c.CustomerID, c.CompanyName
FROM Sales.Customers c
WHERE EXISTS (
SELECT 1 FROM Sales.Orders o
WHERE o.CustomerID = c.CustomerID
AND o.OrderDate > DATEADD(year, -1, GETDATE())
);
-- CTE (Common Table Expression) 遞歸/模塊化
WITH SalesCTE AS (
SELECT CustomerID, SUM(TotalAmount) AS TotalSales
FROM Sales.Orders
GROUP BY CustomerID
)
SELECT c.CompanyName, s.TotalSales
FROM SalesCustomers c
JOIN SalesCTE s ON c.CustomerID = s.CustomerID
WHERE s.TotalSales > 100000;
-- 遞歸 CTE:組織架構
WITH OrgCTE AS (
SELECT EmployeeID, ManagerID, EmpName, 0 AS Level
FROM HR.Employees WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.EmpName, o.Level + 1
FROM HR.Employees e JOIN OrgCTE o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgCTE ORDER BY Level, EmployeeID;
-- 排序
SELECT * FROM Sales.Orders
ORDER BY OrderDate DESC, CustomerID;
-- 分頁 (SQL Server 2012+)
SELECT * FROM Sales.Orders
ORDER BY OrderDate DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 集合運算
SELECT CustomerID FROM Sales.Customers WHERE Country = 'USA'
UNION -- 去重
SELECT CustomerID FROM Sales.Customers WHERE Country = 'Canada';
SELECT CustomerID FROM Sales.Customers WHERE Country = 'USA'
INTERSECT -- 交集
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate) = 2024;
SELECT CustomerID FROM Sales.Customers WHERE Country = 'USA'
EXCEPT -- 差集
SELECT CustomerID FROM Sales.Orders WHERE YEAR(OrderDate) = 2024;
-- INSERT
INSERT INTO Sales.Customers (CustomerID, CompanyName, Country)
VALUES ('NEWCU', 'New Customer Inc.', 'USA');
INSERT INTO Sales.Orders (CustomerID, OrderDate, ShipCountry)
SELECT CustomerID, GETDATE(), Country
FROM Sales.Customers WHERE CustomerID = 'NEWCU';
-- UPDATE (必加 WHERE)
UPDATE Sales.Customers
SET ContactName = 'New Contact', Phone = '123-4567'
WHERE CustomerID = 'NEWCU';
-- DELETE (謹慎,建議軟刪除 IsDeleted=1)
DELETE FROM Sales.Orders WHERE OrderID = 11078;
-- MERGE (UPSERT)
MERGE INTO Sales.Customers AS Target
USING (VALUES ('NEWCU', 'New Customer', 'USA')) AS Source (CustomerID, CompanyName, Country)
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN UPDATE SET CompanyName = Source.CompanyName
WHEN NOT MATCHED THEN INSERT (CustomerID, CompanyName, Country) VALUES (Source.CustomerID, Source.CompanyName, Source.Country);
熟練掌握 T-SQL 查詢語法,配合索引策略與執行計劃分析,是釋放 SQL Server 性能潛力的關鍵。