T-SQL 查询基础
2.1 T-SQL 语言规范与 SELECT 逻辑执行顺序
T-SQL（Transact-SQL）是微软对 ANSI SQL 标准的扩展实现，在标准 SQL 基础上增加了流程控制、事务处理、存储过程等编程能力。理解 SELECT 语句的逻辑执行顺序是写出高效 SQL 的基础。
2.1.1 SELECT 逻辑执行顺序
SQL 的书写顺序与实际执行顺序并不相同：
书写顺序 逻辑执行顺序
────────── ──────────────
SELECT ←(6) 1. FROM（含 JOIN）
FROM ←(1) 2. ON
JOIN ←(2) 3. WHERE
ON ←(3) 4. GROUP BY
WHERE ←(4) 5. HAVING
GROUP BY ←(5) 6. SELECT
HAVING ←(6) 7. DISTINCT
ORDER BY ←(8) 8. ORDER BY
TOP / OFFSET←(9) 9. TOP / OFFSET...FETCH
这个顺序解释了很多常见问题： - SELECT 别名不能在 WHERE 中使用（WHERE 在 SELECT 之前执行） - 聚合函数不能在 WHERE 中使用（需用 HAVING） - ORDER BY 可以引用 SELECT 别名（ORDER BY 最后执行）
-- 演示逻辑执行顺序带来的规则
-- 以下查询会报错：WHERE 不能引用 SELECT 别名
-- SELECT ProductName, UnitPrice * 1.1 AS AdjustedPrice
-- FROM Products
-- WHERE AdjustedPrice > 100; -- 错误！
-- 正确写法：在 WHERE 中重写表达式，或用子查询/CTE
SELECT ProductName, UnitPrice * 1.1 AS AdjustedPrice
FROM Products
WHERE UnitPrice * 1.1 > 100; -- OK
-- 或用 CTE
WITH Priced AS (
SELECT ProductName, UnitPrice * 1.1 AS AdjustedPrice
FROM Products
)
SELECT * FROM Priced WHERE AdjustedPrice > 100;
2.1.2 T-SQL 基本数据类型
-- 创建演示用数据库
CREATE DATABASE SqlServerTutorial
COLLATE Chinese_PRC_CI_AS;
GO
USE SqlServerTutorial;
GO
-- 数值类型演示
CREATE TABLE #DataTypesDemo (
-- 整数类型
col_tinyint TINYINT, -- 0~255, 1字节
col_smallint SMALLINT, -- -32768~32767, 2字节
col_int INT, -- ~±21亿, 4字节
col_bigint BIGINT, -- ~±922亿亿, 8字节
-- 精确小数
col_decimal DECIMAL(18, 4), -- 推荐用于金额
col_numeric NUMERIC(10, 2), -- 与 DECIMAL 等价
col_money MONEY, -- 4位小数，8字节，但有精度问题（不推荐）
-- 近似小数
col_float FLOAT, -- 64位IEEE 754
col_real REAL, -- 32位IEEE 754
-- 字符串
col_char CHAR(10), -- 定长，不足补空格
col_varchar VARCHAR(200), -- 变长，<=8000字节
col_nchar NCHAR(10), -- Unicode定长
col_nvarchar NVARCHAR(400), -- Unicode变长（常用于中文）
col_nvarcharmax NVARCHAR(MAX), -- Unicode，最多2GB
-- 日期时间
col_date DATE, -- 仅日期，3字节
col_time TIME(7), -- 仅时间，精度到100纳秒
col_datetime DATETIME, -- 日期+时间，精度3.33ms
col_datetime2 DATETIME2(7), -- 日期+时间，精度100ns（推荐）
col_datetimeoffset DATETIMEOFFSET(7), -- 含时区
col_smalldatetime SMALLDATETIME,-- 精度1分钟
-- 其他
col_bit BIT, -- 0/1/NULL，相当于布尔
col_uniqueidentifier UNIQUEIDENTIFIER, -- GUID，16字节
col_varbinary VARBINARY(MAX), -- 二进制数据
col_xml XML -- XML数据
);
-- 类型转换：CAST vs CONVERT
SELECT
CAST(3.14159 AS DECIMAL(10,2)) AS CastDemo,
CONVERT(VARCHAR(20), GETDATE(), 120) AS ConvertDate_ISO, -- 2024-01-15 10:30:00
CONVERT(VARCHAR(10), GETDATE(), 23) AS ConvertDate_YYYYMMDD, -- 2024-01-15
CONVERT(VARCHAR(8), GETDATE(), 112) AS ConvertDate_Compact, -- 20240115
TRY_CAST('abc' AS INT) AS SafeCast_NULL, -- NULL（不抛错）
TRY_CONVERT(INT, '123.45') AS SafeConvert_NULL; -- NULL
2.1.3 条件表达式：CASE / IIF / COALESCE / NULLIF
-- CASE 表达式（两种语法）
SELECT OrderID,
-- 搜索型（推荐，更灵活）
CASE
WHEN TotalAmount >= 10000 THEN '大单'
WHEN TotalAmount >= 1000 THEN '中单'
WHEN TotalAmount >= 100 THEN '小单'
ELSE '微单'
END AS OrderCategory,
-- 简单型
CASE Status
WHEN 1 THEN '待付款'
WHEN 2 THEN '待发货'
WHEN 3 THEN '已发货'
WHEN 4 THEN '已完成'
ELSE '未知'
END AS StatusDesc,
-- IIF（简化的二值选择，SQL Server 2012+）
IIF(IsVIP = 1, '会员', '普通') AS MemberType,
-- COALESCE（返回第一个非NULL值）
COALESCE(Remark, Description, '无备注') AS FinalRemark,
-- NULLIF（两值相等返回NULL，防止除零）
SalesAmount / NULLIF(Quantity, 0) AS UnitPrice
FROM Orders;
2.2 内置函数大全
2.2.1 字符串函数
-- ============ 字符串处理函数 ============
DECLARE @str NVARCHAR(100) = N' Hello, SQL Server 2022! ';
SELECT
-- 长度与截取
LEN(@str) AS Len_NoTrailing, -- 不含尾部空格
DATALENGTH(@str) AS DataLength_Bytes, -- 实际字节数
LEFT(@str, 5) AS Left5,
RIGHT(@str, 5) AS Right5,
SUBSTRING(@str, 3, 10) AS Sub_3_10,
-- 去除空白
LTRIM(@str) AS LTrimmed,
RTRIM(@str) AS RTrimmed,
TRIM(@str) AS Trimmed, -- SQL Server 2017+
TRIM(' ' FROM @str) AS TrimCustom,
-- 大小写
UPPER(@str) AS Upper_,
LOWER(@str) AS Lower_,
-- 查找与替换
CHARINDEX('SQL', @str) AS CharIndex_, -- 返回位置
CHARINDEX('SQL', @str, 10) AS CharIndex_From10, -- 从第10位开始找
REPLACE(@str, 'SQL', 'My SQL') AS Replaced,
-- 填充
CONCAT('Name:', N'张三') AS Concat_,
CONCAT_WS(', ', 'A','B','C') AS ConcatWS_, -- SQL Server 2017+
FORMAT(12345.678, 'N2', 'zh-CN') AS FormatNum, -- 12,345.68
STRING_SPLIT('a,b,c,d', ',') AS StringSplit_Ref, -- 返回表（需 FROM 子句）
-- 字符串反转
REVERSE(@str) AS Reversed,
-- 重复
REPLICATE('*', 10) AS Repeated,
-- 填充至固定宽度
RIGHT('000000' + CAST(42 AS VARCHAR), 6) AS PaddedLeft; -- 000042
-- STRING_AGG 替代 FOR XML PATH 聚合字符串（SQL Server 2017+）
SELECT
CategoryID,
STRING_AGG(ProductName, ', ')
WITHIN GROUP (ORDER BY ProductName) AS Products
FROM Products
GROUP BY CategoryID;
-- PATINDEX：使用通配符查找（比 CHARINDEX 更灵活）
SELECT PATINDEX('%[0-9]%', 'ABC123DEF'); -- 返回 4
-- STRING_SPLIT 实际用法（需 FROM/CROSS APPLY）
SELECT value
FROM STRING_SPLIT('北京,上海,广州,深圳', ',');
2.2.2 数值与数学函数
SELECT
ABS(-42) AS Abs_,
CEILING(3.1) AS Ceil_, -- 4
FLOOR(3.9) AS Floor_, -- 3
ROUND(3.456, 2) AS Round2, -- 3.46
ROUND(3.455, 2, 1) AS Truncate2, -- 3.45（第三参数非0=截断）
POWER(2, 10) AS Power2_10, -- 1024
SQRT(144) AS Sqrt_, -- 12
LOG(100, 10) AS Log10_, -- 2
EXP(1) AS E_,
PI() AS Pi_,
RAND() AS RandomFloat, -- 0~1随机数
-- 生成范围随机整数 [min, max]
CAST(RAND() * (100 - 1) + 1 AS INT) AS Random1to100,
-- 取模
17 % 5 AS Modulo; -- 2
2.2.3 日期时间函数
DECLARE @dt DATETIME2(7) = '2024-06-15 14:30:45.1234567';
SELECT
-- 获取当前时间
GETDATE() AS GetDate_DateTime, -- 含时区无关的本地时间
GETUTCDATE() AS GetUTCDate,
SYSDATETIME() AS SysDateTime2, -- DATETIME2精度
SYSDATETIMEOFFSET() AS SysDateTimeOffset, -- 含时区偏移
SYSUTCDATETIME() AS SysUTCDateTime2,
-- 提取部分
YEAR(@dt) AS Year_,
MONTH(@dt) AS Month_,
DAY(@dt) AS Day_,
DATEPART(WEEKDAY, @dt) AS Weekday_, -- 1=周日
DATENAME(MONTH, @dt) AS MonthName_,
DATEPART(ISO_WEEK, @dt) AS ISOWeek,
-- 加减
DATEADD(DAY, 30, @dt) AS Add30Days,
DATEADD(MONTH, -1, @dt) AS Minus1Month,
DATEADD(SECOND, 3600, @dt) AS Add1Hour,
-- 差值
DATEDIFF(DAY, '2024-01-01', @dt) AS DiffDays,
DATEDIFF(MONTH, '2023-01-01', @dt) AS DiffMonths,
-- 格式化（推荐 FORMAT 函数）
FORMAT(@dt, 'yyyy-MM-dd HH:mm:ss', 'zh-CN') AS Formatted,
FORMAT(@dt, 'D', 'zh-CN') AS LongDate, -- 2024年6月15日
-- 截断到日期开始
CAST(CAST(@dt AS DATE) AS DATETIME2) AS TruncToDay,
DATETRUNC(MONTH, @dt) AS TruncToMonth, -- SQL Server 2022+
-- 构造日期
DATEFROMPARTS(2024, 12, 31) AS DateFromParts,
DATETIME2FROMPARTS(2024,12,31,23,59,59,0,0) AS DT2FromParts,
-- 时区转换（SQL Server 2016+）
@dt AT TIME ZONE 'China Standard Time' AS ChinaTime;
-- 实用场景：获取本月第一天和最后一天
SELECT
DATETRUNC(MONTH, GETDATE()) AS FirstDayOfMonth, -- 2022+
EOMONTH(GETDATE()) AS LastDayOfMonth,
EOMONTH(GETDATE(), 1) AS LastDayNextMonth,
-- 兼容旧版本写法
CAST(CONVERT(VARCHAR(7), GETDATE(), 120) + '-01' AS DATE) AS FirstDayCompat;
2.3 多表关联与子查询
2.3.1 各类 JOIN 语义与性能考量
-- 准备演示数据
CREATE TABLE #Customers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
CityID INT
);
CREATE TABLE #Cities (
CityID INT PRIMARY KEY,
CityName NVARCHAR(50)
);
CREATE TABLE #Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
Amount DECIMAL(10,2),
OrderDate DATE
);
INSERT INTO #Customers VALUES
(1, N'张三', 1), (2, N'李四', 2), (3, N'王五', NULL), (4, N'赵六', 99);
INSERT INTO #Cities VALUES (1, N'北京'), (2, N'上海'), (3, N'广州');
INSERT INTO #Orders VALUES
(101, 1, 500.00, '2024-01-10'),
(102, 1, 300.00, '2024-02-15'),
(103, 2, 800.00, '2024-01-20'),
(104, 5, 200.00, '2024-03-01'); -- CustomerID=5 不存在
-- INNER JOIN：只返回两侧均匹配的行
SELECT c.CustomerName, ci.CityName, o.Amount
FROM #Customers c
INNER JOIN #Cities ci ON c.CityID = ci.CityID
INNER JOIN #Orders o ON c.CustomerID = o.CustomerID;
-- LEFT JOIN：保留左表全部行
SELECT c.CustomerName,
ci.CityName,
ISNULL(SUM(o.Amount), 0) AS TotalAmount
FROM #Customers c
LEFT JOIN #Cities ci ON c.CityID = ci.CityID
LEFT JOIN #Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName, ci.CityName;
-- RIGHT JOIN（不常用，可用 LEFT JOIN 改写）
-- FULL OUTER JOIN：保留两侧所有行
SELECT c.CustomerName, o.OrderID, o.Amount
FROM #Customers c
FULL OUTER JOIN #Orders o ON c.CustomerID = o.CustomerID;
-- CROSS JOIN：笛卡尔积（慎用！）
SELECT c.CustomerName, ci.CityName AS PotentialCity
FROM #Customers c
CROSS JOIN #Cities ci; -- 4×3=12行
-- SELF JOIN：自连接（查找组织层级等）
CREATE TABLE #Employees (
EmpID INT PRIMARY KEY, EmpName NVARCHAR(50), ManagerID INT
);
INSERT INTO #Employees VALUES
(1,N'王总',NULL),(2,N'张经理',1),(3,N'李主管',2),(4,N'陈员工',3);
SELECT e.EmpName AS Employee, m.EmpName AS Manager
FROM #Employees e
LEFT JOIN #Employees m ON e.ManagerID = m.EmpID;
2.3.2 APPLY 运算符：侧向关联
APPLY 是 SQL Server 特有的强大运算符，允许右侧表表达式引用左侧表的列。
-- CROSS APPLY vs OUTER APPLY（类似 INNER JOIN vs LEFT JOIN）
-- 场景：取每个客户最近3笔订单
SELECT c.CustomerName,
o.OrderID,
o.Amount,
o.OrderDate
FROM #Customers c
CROSS APPLY (
SELECT TOP 3 OrderID, Amount, OrderDate
FROM #Orders
WHERE CustomerID = c.CustomerID -- 引用外层 c.CustomerID
ORDER BY OrderDate DESC
) o;
-- OUTER APPLY：没有订单的客户也保留
SELECT c.CustomerName,
o.OrderID,
o.Amount
FROM #Customers c
OUTER APPLY (
SELECT TOP 1 OrderID, Amount
FROM #Orders
WHERE CustomerID = c.CustomerID
ORDER BY Amount DESC
) o;
-- APPLY 配合表值函数（TVF）
CREATE FUNCTION dbo.fn_GetOrderStats (@CustomerID INT)
RETURNS TABLE AS RETURN (
SELECT
COUNT(*) AS OrderCount,
SUM(Amount) AS TotalAmount,
MAX(OrderDate) AS LastOrderDate
FROM Orders
WHERE CustomerID = @CustomerID
);
SELECT c.CustomerName, s.OrderCount, s.TotalAmount, s.LastOrderDate
FROM Customers c
CROSS APPLY dbo.fn_GetOrderStats(c.CustomerID) s;
2.3.3 子查询：标量、行集、EXISTS
-- 标量子查询（返回单值）
SELECT OrderID, Amount,
(SELECT AVG(Amount) FROM Orders) AS AvgAmount,
Amount - (SELECT AVG(Amount) FROM Orders) AS DiffFromAvg
FROM Orders;
-- IN 子查询
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT DISTINCT CustomerID
FROM Orders
WHERE Amount > 500
);
-- EXISTS 子查询（比 IN 通常更高效，尤其子查询返回大量行时）
SELECT c.CustomerName
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
AND o.Amount > 500
);
-- NOT EXISTS（查找没有订单的客户）
SELECT c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);
-- 相关子查询（Correlated Subquery，每行执行一次，注意性能）
SELECT o1.OrderID,
o1.Amount,
(SELECT SUM(o2.Amount)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID
AND o2.OrderDate <= o1.OrderDate) AS RunningTotal
FROM Orders o1
ORDER BY o1.CustomerID, o1.OrderDate;
-- 注意：上面的累计求和建议改用窗口函数 SUM() OVER(...) 提升性能
2.4 数据增删改与 MERGE
2.4.1 INSERT 的多种形式
-- 单行 INSERT
INSERT INTO Products (ProductName, Price, StockQty, CategoryID)
VALUES (N'Surface Pro 11', 9999.00, 100, 1);
-- 多行 INSERT（单语句，性能好）
INSERT INTO Products (ProductName, Price, StockQty, CategoryID)
VALUES
(N'Surface Laptop 6', 8999.00, 50, 1),
(N'Surface Studio 2+', 25999.00, 20, 1),
(N'Xbox Series X', 3699.00, 200, 2);
-- INSERT...SELECT（从其他表复制数据）
INSERT INTO ProductsArchive
SELECT *, GETDATE() AS ArchivedAt
FROM Products
WHERE StockQty = 0;
-- SELECT INTO（创建新表并插入，生产环境慎用，会锁 tempdb 分配）
SELECT *
INTO #TempProducts
FROM Products
WHERE CategoryID = 1;
-- INSERT 配合 OUTPUT（获取插入后的自增ID或计算值）
DECLARE @InsertedRows TABLE (
ProductID INT, ProductName NVARCHAR(100), InsertedAt DATETIME2
);
INSERT INTO Products (ProductName, Price, StockQty, CategoryID)
OUTPUT inserted.ProductID, inserted.ProductName, SYSDATETIME()
INTO @InsertedRows
VALUES (N'HoloLens 2', 29999.00, 10, 3);
SELECT * FROM @InsertedRows;
2.4.2 UPDATE 与 DELETE 最佳实践
-- 基本 UPDATE
UPDATE Products
SET Price = Price * 1.1,
UpdatedAt = GETDATE()
WHERE CategoryID = 1
AND StockQty > 0;
-- UPDATE 联表（FROM 子句）
UPDATE p
SET p.Price = p.Price * d.DiscountRate
FROM Products p
JOIN Discounts d ON p.CategoryID = d.CategoryID
WHERE d.EffectiveDate <= GETDATE();
-- UPDATE 配合 OUTPUT（同时获取新旧值）
UPDATE Products
SET Price = Price * 0.9
OUTPUT deleted.ProductID,
deleted.Price AS OldPrice,
inserted.Price AS NewPrice
WHERE StockQty > 100;
-- DELETE 基本用法
DELETE FROM OrderItems
WHERE OrderID IN (
SELECT OrderID FROM Orders WHERE Status = 'Cancelled'
);
-- 大量删除时分批处理（避免长事务锁表）
DECLARE @BatchSize INT = 5000;
DECLARE @RowsDeleted INT = 1;
WHILE @RowsDeleted > 0
BEGIN
DELETE TOP (@BatchSize)
FROM OrderItems
WHERE CreatedDate < DATEADD(YEAR, -3, GETDATE());
SET @RowsDeleted = @@ROWCOUNT;
-- 稍微等待，让其他事务有机会执行
IF @RowsDeleted > 0 WAITFOR DELAY '00:00:00.1';
END;
-- TRUNCATE TABLE（DDL操作，速度极快，但无法带 WHERE，会重置标识列）
TRUNCATE TABLE TempStagingTable;
2.4.3 MERGE：Upsert 的标准写法
MERGE 是 SQL Server 2008+ 引入的合并语句，可以在一条语句中完成 INSERT / UPDATE / DELETE。
-- 场景：将 ProductsStaging 的数据合并到 Products
-- 如果产品已存在则更新，不存在则插入，源中没有的目标产品则删除
MERGE INTO Products AS Target
USING ProductsStaging AS Source
ON Target.SKU = Source.SKU
-- 匹配：更新
WHEN MATCHED AND (
Target.Price <> Source.Price OR
Target.StockQty <> Source.StockQty
) THEN
UPDATE SET
Target.Price = Source.Price,
Target.StockQty = Source.StockQty,
Target.UpdatedAt = GETDATE()
-- 目标不存在，源存在：插入
WHEN NOT MATCHED BY TARGET THEN
INSERT (SKU, ProductName, Price, StockQty, CategoryID, CreatedAt)
VALUES (Source.SKU, Source.ProductName, Source.Price,
Source.StockQty, Source.CategoryID, GETDATE())
-- 源不存在，目标存在：删除（可选）
WHEN NOT MATCHED BY SOURCE THEN
DELETE
-- 输出操作结果
OUTPUT $action AS MergeAction,
deleted.SKU AS OldSKU,
inserted.SKU AS NewSKU;
-- ⚠️ MERGE 的已知 BUG：
-- 1. 当 Source 中有重复 SKU 时，同一 Target 行可能被多次操作
-- 2. 建议在 USING 子句中先 SELECT DISTINCT，或使用 GROUP BY 去重
MERGE INTO Products AS Target
USING (
SELECT SKU, MAX(Price) AS Price, MAX(StockQty) AS StockQty
FROM ProductsStaging
GROUP BY SKU -- 确保 Source 唯一
) AS Source
ON Target.SKU = Source.SKU
WHEN MATCHED THEN UPDATE SET Target.Price = Source.Price
WHEN NOT MATCHED THEN INSERT (SKU, Price) VALUES (Source.SKU, Source.Price);
2.5 NULL 值处理
NULL 是 SQL 中最容易犯错的领域，需要专门重视：
-- NULL 的特殊行为
SELECT
NULL = NULL, -- NULL（不是 TRUE！）
NULL <> NULL, -- NULL
1 + NULL, -- NULL（任何运算遇到NULL结果为NULL）
CONCAT('a', NULL, 'b'), -- 'ab'（CONCAT自动忽略NULL）
'a' + NULL + 'b'; -- NULL（+运算符遇NULL结果为NULL）
-- 正确判断 NULL
SELECT * FROM Customers WHERE Phone IS NULL;
SELECT * FROM Customers WHERE Phone IS NOT NULL;
-- ISNULL vs COALESCE
SELECT
ISNULL(NULL, '默认值') AS ISNull_Result, -- SQL Server特有，只接受2参数
COALESCE(NULL, NULL, '默认值') AS Coalesce_Result; -- ANSI标准，接受多参数
-- NULL 在聚合中被忽略（除了 COUNT(*)）
SELECT
COUNT(*) AS TotalRows, -- 含NULL行
COUNT(Phone) AS NonNullPhone, -- 不含NULL
AVG(Amount) AS AvgAmount, -- 忽略NULL行计算平均
SUM(Amount) AS TotalAmount, -- NULL被忽略，等价于0
MIN(Phone) AS MinPhone, -- 忽略NULL
MAX(Phone) AS MaxPhone;
-- NULL 排序（默认排到最后，ASC）
SELECT * FROM Customers ORDER BY Phone ASC; -- NULL 最后
SELECT * FROM Customers ORDER BY Phone DESC; -- NULL 最后
-- SQL Server 没有 NULLS FIRST/LAST，用 CASE 模拟
SELECT * FROM Customers
ORDER BY CASE WHEN Phone IS NULL THEN 0 ELSE 1 END, Phone; -- NULL 排最前
-- 表连接中的 NULL（ON 条件中 NULL=NULL 不成立）
-- 所以 INNER JOIN 会丢失 CityID=NULL 的行
2.6 小结
| 主题 | 关键点 |
|---|---|
| 执行顺序 | FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY |
| 类型转换 | 优先 TRY_CAST/TRY_CONVERT 避免运行时错误 |
| JOIN | 明确语义选择；EXISTS 通常比 IN 更高效 |
| APPLY | SQL Server 特有，适合行级子查询和 TVF 配合 |
| MERGE | 注意重复源数据 BUG，USING 子句需唯一 |
| NULL | IS NULL 判断；聚合自动忽略；JOIN 中 NULL 不等价 |
下一章：高级查询：CTE 与窗口函数
文章版权声明：除非注明，否则均为边学边练网络文章，版权归原作者所有
