1. 引言T-SQLTransact-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; GO2.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) ); GO2.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) ); -- 行子查询返回一行多列 -- 查找与特定员工ID101部门和薪水都相同的其他员工 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 公用表表达式CTECTE 使复杂查询更清晰、易读支持递归查询。-- 非递归 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) NEmployees; DECLARE ColumnName NVARCHAR(128) NFirstName; DECLARE SearchValue NVARCHAR(100) NJohn; DECLARE SQL NVARCHAR(MAX); -- 构建动态 SQL 语句使用参数化查询防止 SQL 注入 SET SQL NSELECT * FROM QUOTENAME(TableName) N WHERE QUOTENAME(ColumnName) N LIKE Value; -- 执行动态 SQL EXEC sp_executesql SQL, NValue 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可以节省存储空间并提升查询速度。6. 总结本文系统介绍了 T-SQL 中建库建表、模糊查询和高级查询的核心知识与实战技巧。从基础的CREATE DATABASE/TABLE到灵活的LIKE模糊匹配再到复杂的子查询、连接、窗口函数和 CTE这些是进行高效数据库开发与数据分析的必备武器。 T-SQL 从入门到精通：建库建表、模糊查询与高级查询实战指南
1. 引言T-SQLTransact-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; GO2.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) ); GO2.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) ); -- 行子查询返回一行多列 -- 查找与特定员工ID101部门和薪水都相同的其他员工 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 公用表表达式CTECTE 使复杂查询更清晰、易读支持递归查询。-- 非递归 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) NEmployees; DECLARE ColumnName NVARCHAR(128) NFirstName; DECLARE SearchValue NVARCHAR(100) NJohn; DECLARE SQL NVARCHAR(MAX); -- 构建动态 SQL 语句使用参数化查询防止 SQL 注入 SET SQL NSELECT * FROM QUOTENAME(TableName) N WHERE QUOTENAME(ColumnName) N LIKE Value; -- 执行动态 SQL EXEC sp_executesql SQL, NValue 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可以节省存储空间并提升查询速度。6. 总结本文系统介绍了 T-SQL 中建库建表、模糊查询和高级查询的核心知识与实战技巧。从基础的CREATE DATABASE/TABLE到灵活的LIKE模糊匹配再到复杂的子查询、连接、窗口函数和 CTE这些是进行高效数据库开发与数据分析的必备武器。