T-SQL 从入门到精通：建库建表、模糊查询与高级查询实战指南-开发者社区

1. 引言

T-SQL（Transact-SQL）是 Microsoft SQL Server 和 Azure SQL Database 使用的 SQL 方言，它在标准 SQL 的基础上扩展了变量、流程控制、错误处理等功能，是数据库开发与管理的核心语言。无论是初学者还是有经验的开发者，掌握 T-SQL 的建库建表、数据查询（尤其是模糊查询和高级查询）都是必备技能。本文将系统性地讲解这些核心操作，并提供大量可直接运行的代码示例。

2. 数据库与数据表操作

2.1 创建数据库

使用CREATE DATABASE语句创建数据库，可以指定文件组、文件大小和增长策略。

-- 创建名为 SampleDB 的数据库 CREATE DATABASE SampleDB ON PRIMARY ( NAME = SampleDB_Data, FILENAME = 'C:\SQLData\SampleDB.mdf', SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) LOG ON ( NAME = SampleDB_Log, FILENAME = 'C:\SQLData\SampleDB.ldf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 2MB ); GO -- 切换到新创建的数据库 USE SampleDB; GO

2.2 创建数据表

使用CREATE TABLE语句定义表结构，包括列名、数据类型、约束（主键、外键、非空、默认值等）。

-- 创建员工表 Employees CREATE TABLE Employees ( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, -- 自增主键 FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, Email NVARCHAR(100) UNIQUE, -- 唯一约束 HireDate DATE DEFAULT GETDATE(), -- 默认值为当前日期 DepartmentID INT, Salary DECIMAL(10, 2) CHECK (Salary >= 0), -- 检查约束 CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) -- 外键约束 ); -- 创建部门表 Departments CREATE TABLE Departments ( DepartmentID INT IDENTITY(1,1) PRIMARY KEY, DepartmentName NVARCHAR(100) NOT NULL, Location NVARCHAR(100) ); GO

2.3 修改与删除表

-- 为 Employees 表添加新列 ALTER TABLE Employees ADD PhoneNumber NVARCHAR(20); -- 修改列的数据类型 ALTER TABLE Employees ALTER COLUMN PhoneNumber VARCHAR(15); -- 删除表中的列 ALTER TABLE Employees DROP COLUMN PhoneNumber; -- 删除表（谨慎操作！） DROP TABLE Employees; DROP TABLE Departments; -- 删除数据库（谨慎操作！） DROP DATABASE SampleDB;

3. 模糊查询（LIKE 与通配符）

模糊查询用于匹配部分字符串，是数据检索中非常实用的功能，主要使用LIKE运算符配合通配符。

3.1 通配符详解

%：匹配任意长度（包括零长度）的任意字符序列。
_：匹配任意单个字符。
[]：匹配指定范围或集合内的任意单个字符。
[^]：匹配不在指定范围或集合内的任意单个字符。

3.2 基础模糊查询示例

-- 假设我们有一个 Products 表，包含 ProductName 列 -- 1. 查找以 'Apple' 开头的产品 SELECT * FROM Products WHERE ProductName LIKE 'Apple%'; -- 2. 查找以 'Phone' 结尾的产品 SELECT * FROM Products WHERE ProductName LIKE '%Phone'; -- 3. 查找包含 'Pro' 的产品 SELECT * FROM Products WHERE ProductName LIKE '%Pro%'; -- 4. 查找第二个字符是 'a' 的产品名 SELECT * FROM Products WHERE ProductName LIKE '_a%'; -- 5. 查找以 'A' 或 'B' 或 'C' 开头的产品 SELECT * FROM Products WHERE ProductName LIKE '[ABC]%'; -- 6. 查找不以 'A'、'B'、'C' 开头的产品 SELECT * FROM Products WHERE ProductName LIKE '[^ABC]%'; -- 7. 结合使用：查找名称长度为5，且以 'Pro' 结尾的产品 SELECT * FROM Products WHERE ProductName LIKE '__Pro'; -- 两个下划线代表两个任意字符

3.3 转义通配符

当需要搜索包含通配符本身（如 % 或 _）的字符串时，需要使用ESCAPE子句。

-- 查找包含下划线 '_' 的产品名 SELECT * FROM Products WHERE ProductName LIKE '%\_%' ESCAPE '\'; -- 查找以 '25%' 开头的折扣信息 SELECT * FROM Discounts WHERE DiscountCode LIKE '25!%%' ESCAPE '!'; -- 使用 ! 作为转义符

4. 高级查询技巧

4.1 子查询

子查询是嵌套在主查询中的查询，可用于WHERE、FROM、SELECT等子句中。

-- 标量子查询（返回单个值） -- 查找薪水高于平均薪水的员工 SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees); -- 列子查询（返回一列多行） -- 查找在 'Sales' 或 'Marketing' 部门的员工 SELECT EmployeeID, FirstName, LastName FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Sales', 'Marketing') ); -- 行子查询（返回一行多列） -- 查找与特定员工（ID=101）部门和薪水都相同的其他员工 SELECT EmployeeID, FirstName, LastName FROM Employees WHERE (DepartmentID, Salary) = ( SELECT DepartmentID, Salary FROM Employees WHERE EmployeeID = 101 ) AND EmployeeID != 101; -- 表子查询（在 FROM 子句中作为派生表） -- 计算每个部门的员工数量和平均薪水 SELECT d.DepartmentName, emp_stats.EmployeeCount, emp_stats.AvgSalary FROM Departments d JOIN ( SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary FROM Employees GROUP BY DepartmentID ) emp_stats ON d.DepartmentID = emp_stats.DepartmentID;

4.2 连接查询（JOIN）

-- 内连接（INNER JOIN）：只返回两个表都匹配的行 SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID; -- 左外连接（LEFT JOIN）：返回左表所有行，右表匹配不上则为 NULL SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID; -- 右外连接（RIGHT JOIN）：返回右表所有行，左表匹配不上则为 NULL SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID; -- 全外连接（FULL JOIN）：返回两个表的所有行，匹配不上则为 NULL SELECT e.FirstName, e.LastName, d.DepartmentName FROM Employees e FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID; -- 交叉连接（CROSS JOIN）：返回两个表的笛卡尔积 SELECT e.FirstName, d.DepartmentName FROM Employees e CROSS JOIN Departments d;

4.3 窗口函数

窗口函数在不减少行数的情况下，对一组行进行计算，非常适合排名、累计、移动平均等分析。

-- ROW_NUMBER(): 为结果集中的每一行分配一个唯一的序号 SELECT EmployeeID, FirstName, LastName, Salary, DepartmentID, ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptSalaryRank FROM Employees; -- RANK() 和 DENSE_RANK(): 处理并列排名 SELECT EmployeeID, FirstName, Salary, DepartmentID, RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankWithGaps, DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankNoGaps FROM Employees; -- 累计求和与移动平均 SELECT OrderDate, DailySales, SUM(DailySales) OVER (ORDER BY OrderDate) AS CumulativeSales, AVG(DailySales) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3Days FROM SalesDaily;

4.4 公用表表达式（CTE）

CTE 使复杂查询更清晰、易读，支持递归查询。

-- 非递归 CTE：计算部门平均薪水并筛选 WITH DeptAvgSalary AS ( SELECT DepartmentID, AVG(Salary) AS AvgSalary FROM Employees GROUP BY DepartmentID ) SELECT e.FirstName, e.LastName, e.Salary, d.DepartmentName, das.AvgSalary FROM Employees e JOIN Departments d ON e.DepartmentID = d.DepartmentID JOIN DeptAvgSalary das ON e.DepartmentID = das.DepartmentID WHERE e.Salary > das.AvgSalary; -- 递归 CTE：生成组织结构树（假设 Employees 表有 ManagerID 列） WITH OrgHierarchy AS ( -- 锚点成员：顶级管理者（ManagerID IS NULL） SELECT EmployeeID, FirstName, LastName, ManagerID, 1 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL -- 递归成员：下属员工 SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, oh.Level + 1 FROM Employees e INNER JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID ) SELECT * FROM OrgHierarchy ORDER BY Level, EmployeeID;

4.5 动态 SQL

动态 SQL 允许在运行时构建和执行 SQL 语句，灵活性高但需注意 SQL 注入风险。

-- 声明变量 DECLARE @TableName NVARCHAR(128) = N'Employees'; DECLARE @ColumnName NVARCHAR(128) = N'FirstName'; DECLARE @SearchValue NVARCHAR(100) = N'John'; DECLARE @SQL NVARCHAR(MAX); -- 构建动态 SQL 语句（使用参数化查询防止 SQL 注入） SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' LIKE @Value'; -- 执行动态 SQL EXEC sp_executesql @SQL, N'@Value NVARCHAR(100)', @Value = @SearchValue + N'%';

5. 性能优化与最佳实践

索引是查询性能的基石：为频繁用于WHERE、JOIN、ORDER BY的列创建索引。
避免在 WHERE 子句中对列进行函数操作：如WHERE YEAR(OrderDate) = 2023会导致索引失效，应改为WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'。
模糊查询优化：以通配符%开头的LIKE查询（如LIKE '%keyword'）无法使用索引。如果业务允许，尽量使用后缀匹配（LIKE 'keyword%'）。
使用 EXISTS 代替 IN：当子查询返回大量数据时，EXISTS通常比IN性能更好。
选择合适的数据类型：使用最小的、最合适的数据类型（如用INT而不是BIGINT）可以节省存储空间并提升查询速度。