萬盛學電腦網

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

SQL Server 數據查詢與操作核心語法:SELECT、WHERE、JOIN、聚合、子查詢詳解

SQL Server T-SQL 查詢核心語法精講

掌握 SELECT 語句的各種子句組合,是高效檢索、分析數據的基石。本文系統梳理選擇列表、條件過濾、連接查詢、聚合分組、子查詢等核心知識點。

1. 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; -- 組合去重

2. WHERE 條件過濾

運算符類別運算符示例
比較=Status = 'Shipped'
<> / !=Status <> 'Cancelled'
> / >=OrderDate > '2024-01-01'
< / <=Qty <= 100
!> / !<非標準,不推薦
IS NULL / IS NOT NULLRegion IS NULL
IS [NOT] DISTINCT FROM (SQL 標準)安全比較 NULL
邏輯AND / OR / NOT(A > 10 AND B < 20) OR C = 'X'
IN / NOT INCategoryID IN (1,2,3)
BETWEEN / NOT BETWEENOrderDate BETWEEN '2024-01-01' AND '2024-12-31'
LIKE / NOT LIKEProductName LIKE 'A%'
EXISTS / NOT EXISTSEXISTS (SELECT 1 FROM ... WHERE ...)
ANY / SOME / ALLPrice > ANY (SELECT Price FROM ...)

3. 通配符與模式匹配

通配符含義示例匹配
%任意長度字符(含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 '/';

4. 連接查詢 (JOIN)

-- 內連接:匹配行
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;

5. 聚合與分組

-- 基礎聚合
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)),
  ()
);

6. 子查詢與 CTE

-- 標量子查詢 (單值)
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;

7. 排序、分頁、集合運算

-- 排序
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;

8. 寫入操作 (DML)

-- 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);

性能優化提示

  • 避免 `SELECT *`,明確列出所需列
  • WHERE/JOIN/ORDER BY 列建立適當索引
  • 用 EXISTS 替代 IN (大子查詢)
  • 避免函數作用於索引列:`WHERE YEAR(Date) = 2024` → `WHERE Date >= '2024-01-01' AND Date < '2025-01-01'`
  • 大批量寫入用 `BULK INSERT` / `BCP` / `SqlBulkCopy`
  • 查詢計劃分析:`SET STATISTICS IO, TIME ON` / 實際執行計劃

熟練掌握 T-SQL 查詢語法,配合索引策略與執行計劃分析,是釋放 SQL Server 性能潛力的關鍵。

copyright © 萬盛學電腦網 all rights reserved