SQL Server 数据库操作

SQL Server 数据库操作完整指南

目录

创建数据库
连接数据库
创建表
约束详解
插入数据
查询数据
多表联合查询
连接查询
高级查询
更新数据
删除数据
视图详解
存储过程详解
函数详解
触发器
事务处理
索引优化
安全性管理
备份和恢复
性能优化
删除表和数据库

1. 创建数据库

基本创建数据库

-- 创建基本数据库
CREATE DATABASE CompanyDB;-- 创建带参数的数据库
CREATE DATABASE CompanyDB_Advanced
ON PRIMARY
(NAME = 'CompanyDB_Advanced_Data',FILENAME = 'C:\Data\CompanyDB_Advanced.mdf',SIZE = 100MB,MAXSIZE = 1GB,FILEGROWTH = 10MB
)
LOG ON
(NAME = 'CompanyDB_Advanced_Log',FILENAME = 'C:\Data\CompanyDB_Advanced.ldf',SIZE = 10MB,MAXSIZE = 100MB,FILEGROWTH = 5MB
);-- 创建带多个文件组的数据库
CREATE DATABASE CompanyDB_FileGroups
ON PRIMARY
(NAME = 'CompanyDB_Primary',FILENAME = 'C:\Data\CompanyDB_Primary.mdf',SIZE = 50MB,FILEGROWTH = 10MB
),
FILEGROUP SecondaryFG
(NAME = 'CompanyDB_Secondary1',FILENAME = 'C:\Data\CompanyDB_Secondary1.ndf',SIZE = 50MB,FILEGROWTH = 10MB
),
(NAME = 'CompanyDB_Secondary2',FILENAME = 'C:\Data\CompanyDB_Secondary2.ndf',SIZE = 50MB,FILEGROWTH = 10MB
)
LOG ON
(NAME = 'CompanyDB_Log',FILENAME = 'C:\Data\CompanyDB_Log.ldf',SIZE = 20MB,FILEGROWTH = 5MB
);

查看数据库信息

-- 查看所有数据库
SELECT name, database_id, create_date FROM sys.databases;-- 查看当前数据库信息
SELECT DB_NAME() AS CurrentDatabase,@@VERSION AS SQLServerVersion;-- 查看数据库详细信息
SELECT database_id,name,create_date,compatibility_level,collation_name,user_access_desc,state_desc,recovery_model_desc
FROM sys.databases
WHERE name = 'CompanyDB';-- 查看数据库文件信息
SELECT DB_NAME(database_id) AS DatabaseName,type_desc,name AS LogicalName,physical_name,size * 8 / 1024 AS SizeMB,max_size,growth
FROM sys.master_files
WHERE database_id = DB_ID('CompanyDB');

---

2. 连接数据库

使用数据库

-- 切换到指定数据库
USE CompanyDB;-- 验证当前使用的数据库
SELECT DB_NAME() AS CurrentDatabase;-- 查看当前数据库的所有表
SELECT s.name AS SchemaName,t.name AS TableName,t.create_date,t.modify_date
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY s.name, t.name;

---

3. 创建表

创建单个表

-- 创建员工表
CREATE TABLE Employees (EmployeeID INT IDENTITY(1,1) PRIMARY KEY,FirstName NVARCHAR(50) NOT NULL,LastName NVARCHAR(50) NOT NULL,Email NVARCHAR(100) UNIQUE,Phone NVARCHAR(20),HireDate DATE DEFAULT GETDATE(),Salary DECIMAL(10,2) CHECK (Salary > 0),DepartmentID INT,IsActive BIT DEFAULT 1,CreatedDate DATETIME2 DEFAULT GETDATE(),ModifiedDate DATETIME2,CreatedBy NVARCHAR(50) DEFAULT SYSTEM_USER,ModifiedBy NVARCHAR(50)
);

创建多个相关表

-- 创建部门表
CREATE TABLE Departments (DepartmentID INT IDENTITY(1,1) PRIMARY KEY,DepartmentName NVARCHAR(100) NOT NULL UNIQUE,Location NVARCHAR(100),Budget DECIMAL(15,2),ManagerID INT,ParentDepartmentID INT,CreatedDate DATETIME2 DEFAULT GETDATE(),ModifiedDate DATETIME2
);-- 创建项目表
CREATE TABLE Projects (ProjectID INT IDENTITY(1,1) PRIMARY KEY,ProjectName NVARCHAR(200) NOT NULL,Description NVARCHAR(MAX),StartDate DATE,EndDate DATE,Budget DECIMAL(15,2),Status NVARCHAR(20) DEFAULT 'Planning',DepartmentID INT,ProjectManagerID INT,CreatedDate DATETIME2 DEFAULT GETDATE(),ModifiedDate DATETIME2,CONSTRAINT CK_Projects_Dates CHECK (EndDate >= StartDate)
);-- 创建员工项目关联表（多对多关系）
CREATE TABLE EmployeeProjects (EmployeeProjectID INT IDENTITY(1,1) PRIMARY KEY,EmployeeID INT NOT NULL,ProjectID INT NOT NULL,Role NVARCHAR(50),AssignedDate DATE DEFAULT GETDATE(),UnassignedDate DATE,HoursWorked DECIMAL(5,2) DEFAULT 0,UNIQUE(EmployeeID, ProjectID, AssignedDate),CONSTRAINT CK_EmployeeProjects_Hours CHECK (HoursWorked >= 0),CONSTRAINT CK_EmployeeProjects_Dates CHECK (UnassignedDate IS NULL OR UnassignedDate >= AssignedDate)
);-- 创建薪资历史表
CREATE TABLE SalaryHistory (SalaryHistoryID INT IDENTITY(1,1) PRIMARY KEY,EmployeeID INT NOT NULL,OldSalary DECIMAL(10,2),NewSalary DECIMAL(10,2),ChangeDate DATETIME2 DEFAULT GETDATE(),Reason NVARCHAR(200),ApprovedBy NVARCHAR(50),CONSTRAINT CK_SalaryHistory_Positive CHECK (NewSalary > 0)
);-- 创建技能表
CREATE TABLE Skills (SkillID INT IDENTITY(1,1) PRIMARY KEY,SkillName NVARCHAR(100) NOT NULL UNIQUE,SkillCategory NVARCHAR(50),Description NVARCHAR(500)
);-- 创建员工技能关联表
CREATE TABLE EmployeeSkills (EmployeeSkillID INT IDENTITY(1,1) PRIMARY KEY,EmployeeID INT NOT NULL,SkillID INT NOT NULL,ProficiencyLevel INT CHECK (ProficiencyLevel BETWEEN 1 AND 5),CertificationDate DATE,ExpiryDate DATE,UNIQUE(EmployeeID, SkillID)
);

---

4. 约束详解

主键约束（PRIMARY KEY）

-- 创建表时添加主键
CREATE TABLE Products (ProductID INT PRIMARY KEY,ProductName NVARCHAR(100) NOT NULL
);-- 复合主键
CREATE TABLE OrderDetails (OrderID INT,ProductID INT,Quantity INT,CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, ProductID)
);-- 为已存在的表添加主键
ALTER TABLE TableName 
ADD CONSTRAINT PK_TableName PRIMARY KEY (ColumnName);-- 删除主键
ALTER TABLE TableName 
DROP CONSTRAINT PK_TableName;

外键约束（FOREIGN KEY）

-- 创建表时添加外键
CREATE TABLE Orders (OrderID INT PRIMARY KEY,CustomerID INT,OrderDate DATE,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);-- 添加级联操作的外键
ALTER TABLE Employees 
ADD CONSTRAINT FK_Employees_Departments 
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
ON DELETE SET NULL
ON UPDATE CASCADE;-- 添加多列外键
ALTER TABLE OrderDetails
ADD CONSTRAINT FK_OrderDetails_Orders
FOREIGN KEY (OrderID, CustomerID) 
REFERENCES Orders(OrderID, CustomerID);-- 查看外键信息
SELECT f.name AS ForeignKeyName,OBJECT_NAME(f.parent_object_id) AS TableName,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,OBJECT_NAME(f.referenced_object_id) AS ReferencedTable,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumn
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id;

唯一约束（UNIQUE）

-- 创建表时添加唯一约束
CREATE TABLE Users (UserID INT PRIMARY KEY,Username NVARCHAR(50) UNIQUE,Email NVARCHAR(100),CONSTRAINT UQ_Users_Email UNIQUE (Email)
);-- 为已存在的表添加唯一约束
ALTER TABLE Employees 
ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);-- 添加多列唯一约束
ALTER TABLE Products 
ADD CONSTRAINT UQ_Products_NameCategory 
UNIQUE (ProductName, CategoryID);-- 删除唯一约束
ALTER TABLE Users 
DROP CONSTRAINT UQ_Users_Email;

检查约束（CHECK）

-- 创建表时添加检查约束
CREATE TABLE Products (ProductID INT PRIMARY KEY,ProductName NVARCHAR(100) NOT NULL,Price DECIMAL(10,2) CHECK (Price > 0),Stock INT,CONSTRAINT CK_Products_Stock CHECK (Stock >= 0)
);-- 为已存在的表添加检查约束
ALTER TABLE Employees 
ADD CONSTRAINT CK_Employees_Age 
CHECK (DATEDIFF(YEAR, BirthDate, GETDATE()) >= 18);-- 复杂的检查约束
ALTER TABLE Projects 
ADD CONSTRAINT CK_Projects_Budget_Status 
CHECK ((Status = 'Planning' AND Budget IS NULL) OR(Status IN ('Active', 'Completed') AND Budget IS NOT NULL)
);-- 使用函数的检查约束
ALTER TABLE Employees 
ADD CONSTRAINT CK_Employees_Email_Format 
CHECK (Email LIKE '%_@_%.__%');-- 禁用和启用检查约束
ALTER TABLE Employees NOCHECK CONSTRAINT CK_Employees_Age;
ALTER TABLE Employees CHECK CONSTRAINT CK_Employees_Age;

默认约束（DEFAULT）

-- 创建表时添加默认约束
CREATE TABLE AuditLog (LogID INT PRIMARY KEY,Action NVARCHAR(50),LogDate DATETIME2 DEFAULT GETDATE(),UserName NVARCHAR(50) DEFAULT SYSTEM_USER,IPAddress NVARCHAR(15) DEFAULT '0.0.0.0'
);-- 为已存在的列添加默认约束
ALTER TABLE Employees 
ADD CONSTRAINT DF_Employees_IsActive 
DEFAULT 1 FOR IsActive;-- 使用函数作为默认值
ALTER TABLE Orders 
ADD CONSTRAINT DF_Orders_OrderNumber 
DEFAULT (CONCAT('ORD-', FORMAT(GETDATE(), 'yyyyMMdd-HHmmss'))) 
FOR OrderNumber;-- 删除默认约束
ALTER TABLE Employees 
DROP CONSTRAINT DF_Employees_IsActive;

计算列

-- 创建包含计算列的表
CREATE TABLE OrderItems (OrderItemID INT PRIMARY KEY,Quantity INT NOT NULL,UnitPrice DECIMAL(10,2) NOT NULL,Discount DECIMAL(3,2) DEFAULT 0,-- 计算列LineTotal AS (Quantity * UnitPrice * (1 - Discount)) PERSISTED,-- 非持久化计算列DiscountAmount AS (Quantity * UnitPrice * Discount)
);-- 为已存在的表添加计算列
ALTER TABLE Employees 
ADD FullName AS (FirstName + ' ' + LastName);-- 添加持久化计算列并创建索引
ALTER TABLE Employees 
ADD Age AS (DATEDIFF(YEAR, BirthDate, GETDATE())) PERSISTED;CREATE INDEX IX_Employees_Age ON Employees(Age);

---

5. 插入数据

基本插入操作

-- 插入部门数据
INSERT INTO Departments (DepartmentName, Location, Budget) VALUES
('人力资源部', '北京', 500000.00),
('技术部', '上海', 2000000.00),
('销售部', '广州', 1500000.00),
('财务部', '北京', 800000.00),
('市场部', '深圳', 1200000.00);-- 插入员工数据
INSERT INTO Employees (FirstName, LastName, Email, Phone, HireDate, Salary, DepartmentID) VALUES
('张', '三', 'zhang.san@company.com', '13800138001', '2023-01-15', 8000.00, 2),
('李', '四', 'li.si@company.com', '13800138002', '2023-02-20', 12000.00, 2),
('王', '五', 'wang.wu@company.com', '13800138003', '2023-03-10', 7000.00, 3),
('赵', '六', 'zhao.liu@company.com', '13800138004', '2023-04-05', 9000.00, 1),
('陈', '七', 'chen.qi@company.com', '13800138005', '2023-05-12', 11000.00, 4),
('刘', '八', 'liu.ba@company.com', '13800138006', '2023-06-18', 6500.00, 3),
('杨', '九', 'yang.jiu@company.com', '13800138007', '2023-07-22', 10000.00, 5),
('黄', '十', 'huang.shi@company.com', '13800138008', '2023-08-15', 13000.00, 2);-- 使用OUTPUT子句返回插入的数据
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
OUTPUT INSERTED.EmployeeID, INSERTED.FirstName, INSERTED.LastName, INSERTED.Email
VALUES ('新', '员工', 'new.employee@company.com', 8500.00, 1);

批量插入和高级插入

-- 使用SELECT INTO创建新表并插入数据
SELECT FirstName + ' ' + LastName AS FullName,Email,Salary,DepartmentID
INTO TempEmployees
FROM Employees
WHERE Salary > 8000;-- 使用INSERT INTO SELECT批量插入
INSERT INTO Projects (ProjectName, Description, StartDate, EndDate, Budget, Status, DepartmentID)
SELECT 'Project-' + CAST(ROW_NUMBER() OVER (ORDER BY DepartmentID) AS VARCHAR),'Auto-generated project for department',DATEADD(DAY, DepartmentID * 10, GETDATE()),DATEADD(MONTH, 6, DATEADD(DAY, DepartmentID * 10, GETDATE())),Budget * 0.1,'Planning',DepartmentID
FROM Departments
WHERE Budget > 500000;-- 使用VALUES构造器插入多行
INSERT INTO Skills (SkillName, SkillCategory, Description)
VALUES ('SQL Server', '数据库', '微软关系型数据库管理系统'),('Python', '编程语言', '通用高级编程语言'),('项目管理', '管理技能', 'PMP认证项目管理技能'),('数据分析', '分析技能', '数据挖掘和统计分析能力');-- 条件插入（仅插入不存在的记录）
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
SELECT '测试', '用户', 'test.user@company.com', 7500.00, 1
WHERE NOT EXISTS (SELECT 1 FROM Employees WHERE Email = 'test.user@company.com'
);

---

6. 查询数据

基本查询

-- 查询所有员工
SELECT * FROM Employees;-- 查询特定字段
SELECT FirstName, LastName, Email, Salary FROM Employees;-- 使用别名
SELECT e.FirstName + ' ' + e.LastName AS FullName,e.Email AS EmailAddress,e.Salary AS MonthlySalary,e.Salary * 12 AS AnnualSalary
FROM Employees e;-- 条件查询
SELECT * FROM Employees WHERE Salary > 10000;-- 多条件查询
SELECT * FROM Employees 
WHERE Salary BETWEEN 8000 AND 12000 
AND DepartmentID IN (2, 3)
AND IsActive = 1;-- 模糊查询
SELECT * FROM Employees WHERE FirstName LIKE '张%';
SELECT * FROM Employees WHERE Email LIKE '%@company.com';
SELECT * FROM Employees WHERE LastName LIKE '_四';  -- 第二个字是"四"-- 空值查询
SELECT * FROM Employees WHERE Phone IS NULL;
SELECT * FROM Employees WHERE Phone IS NOT NULL;-- 排序查询
SELECT * FROM Employees ORDER BY Salary DESC, HireDate ASC;-- 限制结果数量
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;
SELECT TOP 10 PERCENT * FROM Employees ORDER BY Salary DESC;-- 使用OFFSET FETCH（SQL Server 2012+）
SELECT * FROM Employees 
ORDER BY Salary DESC
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

聚合函数查询

-- 基本聚合函数
SELECT COUNT(*) AS TotalEmployees,COUNT(DISTINCT DepartmentID) AS DepartmentCount,AVG(Salary) AS AverageSalary,MIN(Salary) AS MinSalary,MAX(Salary) AS MaxSalary,SUM(Salary) AS TotalSalaryExpense,STDEV(Salary) AS SalaryStdDev,VAR(Salary) AS SalaryVariance
FROM Employees;-- 按部门统计
SELECT DepartmentID,COUNT(*) AS EmployeeCount,AVG(Salary) AS AvgSalary,MIN(Salary) AS MinSalary,MAX(Salary) AS MaxSalary,STRING_AGG(FirstName + ' ' + LastName, ', ') AS EmployeeNames
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 1;-- 分组集（GROUPING SETS）
SELECT DepartmentID,YEAR(HireDate) AS HireYear,COUNT(*) AS EmployeeCount,AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY GROUPING SETS ((DepartmentID),(YEAR(HireDate)),(DepartmentID, YEAR(HireDate)),()  -- 总计
);-- CUBE和ROLLUP
SELECT DepartmentID,YEAR(HireDate) AS HireYear,COUNT(*) AS EmployeeCount,SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY CUBE(DepartmentID, YEAR(HireDate));SELECT DepartmentID,YEAR(HireDate) AS HireYear,MONTH(HireDate) AS HireMonth,COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY ROLLUP(DepartmentID, YEAR(HireDate), MONTH(HireDate));

---

7. 多表联合查询

UNION查询

-- 联合查询员工和部门经理信息
SELECT FirstName + ' ' + LastName AS Name, 'Employee' AS Type, Salary AS Amount
FROM Employees
UNION
SELECT DepartmentName AS Name, 'Department' AS Type, Budget AS Amount
FROM Departments;-- UNION ALL（包含重复项）
SELECT DepartmentID, 'Employee' AS Source FROM Employees
UNION ALL
SELECT DepartmentID, 'Project' AS Source FROM Projects
ORDER BY DepartmentID, Source;-- EXCEPT（差集）
SELECT EmployeeID FROM Employees
EXCEPT
SELECT DISTINCT EmployeeID FROM EmployeeProjects;-- INTERSECT（交集）
SELECT DepartmentID FROM Employees
INTERSECT
SELECT DepartmentID FROM Projects;

---

8. 连接查询

内连接（INNER JOIN）

-- 查询员工及其部门信息
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,e.Email,e.Salary,d.DepartmentName,d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- 三表内连接
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,d.DepartmentName,p.ProjectName,ep.Role,ep.HoursWorked
FROM Employees e
INNER JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeID
INNER JOIN Projects p ON ep.ProjectID = p.ProjectID
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE ep.HoursWorked > 100;

左连接（LEFT JOIN）

-- 查询所有员工及其部门信息（包括没有分配部门的员工）
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,e.Email,e.Salary,ISNULL(d.DepartmentName, '未分配部门') AS DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- 查询所有部门及其员工数量
SELECT d.DepartmentName,d.Location,d.Budget,COUNT(e.EmployeeID) AS EmployeeCount,ISNULL(AVG(e.Salary), 0) AS AvgSalary
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName, d.Location, d.Budget;-- 查找没有员工的部门
SELECT d.*
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
WHERE e.EmployeeID IS NULL;

右连接（RIGHT JOIN）

-- 查询所有部门及其员工信息
SELECT d.DepartmentName,d.Location,e.FirstName + ' ' + e.LastName AS EmployeeName,e.Salary
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY d.DepartmentName, e.Salary DESC;

全外连接（FULL OUTER JOIN）

-- 查询所有员工和部门的完整信息
SELECT ISNULL(e.FirstName + ' ' + e.LastName, '无员工') AS EmployeeName,ISNULL(d.DepartmentName, '无部门') AS DepartmentName,COALESCE(e.Salary, d.Budget, 0) AS Amount
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

交叉连接（CROSS JOIN）

-- 生成员工和项目的所有可能组合
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,p.ProjectName,'Potential Assignment' AS Status
FROM Employees e
CROSS JOIN Projects p
WHERE e.DepartmentID = p.DepartmentID  -- 限制为同部门
AND NOT EXISTS (SELECT 1 FROM EmployeeProjects ep WHERE ep.EmployeeID = e.EmployeeID AND ep.ProjectID = p.ProjectID
);

自连接（Self JOIN）

-- 查找同一部门的员工配对
SELECT e1.FirstName + ' ' + e1.LastName AS Employee1,e2.FirstName + ' ' + e2.LastName AS Employee2,d.DepartmentName
FROM Employees e1
INNER JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND e1.EmployeeID < e2.EmployeeID
INNER JOIN Departments d ON e1.DepartmentID = d.DepartmentID;-- 查找员工的上级（使用部门经理）
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,m.FirstName + ' ' + m.LastName AS ManagerName,d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Employees m ON d.ManagerID = m.EmployeeID
WHERE e.EmployeeID != d.ManagerID OR d.ManagerID IS NULL;

APPLY运算符

-- CROSS APPLY（类似内连接）
SELECT d.DepartmentName,TopEmps.EmployeeName,TopEmps.Salary
FROM Departments d
CROSS APPLY (SELECT TOP 2 FirstName + ' ' + LastName AS EmployeeName,SalaryFROM Employees eWHERE e.DepartmentID = d.DepartmentIDORDER BY Salary DESC
) AS TopEmps;-- OUTER APPLY（类似左连接）
SELECT d.DepartmentName,RecentProjects.ProjectName,RecentProjects.StartDate
FROM Departments d
OUTER APPLY (SELECT TOP 1 ProjectName, StartDateFROM Projects pWHERE p.DepartmentID = d.DepartmentIDORDER BY StartDate DESC
) AS RecentProjects;

---

9. 高级查询

GROUP BY 和 HAVING

-- 按部门分组统计，只显示平均薪资大于8000的部门
SELECT d.DepartmentName,COUNT(e.EmployeeID) AS EmployeeCount,AVG(e.Salary) AS AverageSalary,MIN(e.Salary) AS MinSalary,MAX(e.Salary) AS MaxSalary,STDEV(e.Salary) AS SalaryStdDev
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName
HAVING AVG(e.Salary) > 8000 AND COUNT(e.EmployeeID) > 1
ORDER BY AverageSalary DESC;

子查询

-- 标量子查询
SELECT FirstName + ' ' + LastName AS EmployeeName,Salary,(SELECT AVG(Salary) FROM Employees) AS CompanyAvgSalary,Salary - (SELECT AVG(Salary) FROM Employees) AS SalaryDifference
FROM Employees;-- 相关子查询
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName,e.Salary,d.DepartmentName,(SELECT COUNT(*) FROM Employees e2 WHERE e2.DepartmentID = e.DepartmentID AND e2.Salary > e.Salary) AS HigherSalaryCount
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;-- EXISTS子查询
SELECT d.DepartmentName,d.Budget
FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID AND e.Salary > 10000
);-- NOT EXISTS子查询
SELECT e.FirstName + ' ' + e.LastName AS EmployeeName
FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM EmployeeProjects ep WHERE ep.EmployeeID = e.EmployeeID
);-- ANY/SOME和ALL子查询
SELECT * FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE DepartmentID = 2
);SELECT * FROM Employees
WHERE Salary > ALL (SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID
);

窗口函数

-- 排名函数
SELECT FirstName + ' ' + LastName AS EmployeeName,Salary,DepartmentID,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryDenseRank,NTILE(4) OVER (ORDER BY Salary DESC) AS SalaryQuartile,PERCENT_RANK() OVER (ORDER BY Salary DESC) AS PercentRank
FROM Employees;-- 分区排名
SELECT FirstName + ' ' + LastName AS EmployeeName,Salary,DepartmentID,ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptSalaryRank
FROM Employees;-- 聚合窗口函数
SELECT FirstName + ' ' + LastName AS EmployeeName,Salary,DepartmentID,SUM(Salary) OVER () AS TotalSalary,SUM(Salary) OVER (PARTITION BY DepartmentID) AS DeptTotalSalary,AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary,COUNT(*) OVER (PARTITION BY DepartmentID) AS DeptEmployeeCount
FROM Employees;-- 累计和移动聚合
SELECT EmployeeID,FirstName + ' ' + LastName AS EmployeeName,HireDate,Salary,-- 累计求和SUM(Salary) OVER (ORDER BY HireDate) AS RunningTotal,-- 移动平均（前2行到当前行）AVG(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3,-- 移动求和（前1行到后1行）SUM(Salary) OVER (ORDER BY HireDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSum3
FROM Employees;-- LEAD和LAG函数
SELECT EmployeeID,FirstName + ' ' + LastName AS EmployeeName,Salary,LAG(Salary, 1, 0) OVER (ORDER BY Salary) AS PreviousSalary,LEAD(Salary, 1, 0) OVER (ORDER BY Salary) AS NextSalary,Salary - LAG(Salary, 1, 0) OVER (ORDER BY Salary) AS SalaryGap
FROM Employees;-- FIRST_VALUE和LAST_VALUE
SELECT FirstName + ' ' + LastName AS EmployeeName,Salary,DepartmentID,FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptMaxSalary,LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DeptMinSalary
FROM Employees;

CTE（公共表表达式）

-- 基本CTE
WITH DepartmentStats AS (SELECT DepartmentID,COUNT(*) AS EmployeeCount,AVG(Salary) AS AvgSalary,SUM(Salary) AS TotalSalaryFROM EmployeesGROUP BY DepartmentID
)
SELECT d.DepartmentName,ds.EmployeeCount,ds.AvgSalary,ds.TotalSalary,d.Budget,d.Budget - ds.TotalSalary AS RemainingBudget
FROM DepartmentStats ds
INNER JOIN Departments d ON ds.DepartmentID = d.DepartmentID
WHERE ds.EmployeeCount > 1;-- 多个CTE
WITH 
DeptEmployees AS (SELECT DepartmentID, COUNT(*) AS EmpCountFROM EmployeesGROUP BY DepartmentID
),
DeptProjects AS (SELECT DepartmentID, COUNT(*) AS ProjCountFROM ProjectsGROUP BY DepartmentID
)
SELECT d.DepartmentName,ISNULL(de.EmpCount, 0) AS EmployeeCount,ISNULL(dp.ProjCount, 0) AS ProjectCount
FROM Departments d
LEFT JOIN DeptEmployees de ON d.DepartmentID = de.DepartmentID
LEFT JOIN DeptProjects dp ON d.DepartmentID = dp.DepartmentID;-- 递归CTE
WITH EmployeeHierarchy AS (-- 锚点成员：顶级经理SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS EmployeeName,e.DepartmentID,d.DepartmentName,0 AS Level,CAST(e.FirstName + ' ' + e.LastName AS NVARCHAR(MAX)) AS HierarchyPathFROM Employees eINNER JOIN Departments d ON e.EmployeeID = d.ManagerIDUNION ALL-- 递归成员：下属员工SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS EmployeeName,e.DepartmentID,eh.DepartmentName,eh.Level + 1,eh.HierarchyPath + ' -> ' + e.FirstName + ' ' + e.LastNameFROM Employees eINNER JOIN EmployeeHierarchy eh ON e.DepartmentID = eh.DepartmentIDWHERE e.EmployeeID NOT IN (SELECT ManagerID FROM Departments WHERE ManagerID IS NOT NULL)AND eh.Level < 3
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

PIVOT和UNPIVOT

-- PIVOT示例：将行转换为列
SELECT *
FROM (SELECT YEAR(HireDate) AS HireYear,DepartmentID,SalaryFROM Employees
) AS SourceTable
PIVOT (AVG(Salary)FOR DepartmentID IN ([1], [2], [3], [4], [5])
) AS PivotTable;-- 动态PIVOT
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DepartmentID) FROM EmployeesFOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');SET @query = 'SELECT HireYear, ' + @cols + '
FROM (SELECT YEAR(HireDate) AS HireYear, DepartmentID, SalaryFROM Employees
) x
PIVOT (AVG(Salary)FOR DepartmentID IN (' + @cols + ')
) p';EXEC sp_executesql @query;-- UNPIVOT示例：将列转换为行
CREATE TABLE SalesData (Year INT,Q1 DECIMAL(10,2),Q2 DECIMAL(10,2),Q3 DECIMAL(10,2),Q4 DECIMAL(10,2)
);INSERT INTO SalesData VALUES 
(2023, 100000, 120000, 115000, 130000),
(2024, 110000, 125000, 120000, 135000);SELECT Year, Quarter, Sales
FROM SalesData
UNPIVOT (Sales FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;

---

10. 更新数据

基本更新操作

-- 更新单个员工的薪资
UPDATE Employees 
SET Salary = 9000.00 
WHERE EmployeeID = 1;-- 更新多个字段
UPDATE Employees 
SET Phone = '13900139001',Email = 'zhang.san.new@company.com',ModifiedDate = GETDATE(),ModifiedBy = SYSTEM_USER
WHERE EmployeeID = 1;-- 条件更新
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2 AND Salary < 10000;-- 使用CASE语句的条件更新
UPDATE Employees
SET Salary = CASE WHEN DepartmentID = 1 THEN Salary * 1.05WHEN DepartmentID = 2 THEN Salary * 1.10WHEN DepartmentID = 3 THEN Salary * 1.08ELSE Salary * 1.03END,ModifiedDate = GETDATE();

高级更新操作

-- 使用JOIN进行更新
UPDATE e
SET e.Salary = e.Salary * 1.05,e.ModifiedDate = GETDATE()
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = '技术部';-- 使用子查询更新
UPDATE Employees 
SET Salary = (SELECT AVG(Salary) * 1.1 FROM Employees e2 WHERE e2.DepartmentID = Employees.DepartmentID
)
WHERE Salary < (SELECT AVG(Salary) FROM Employees e3 WHERE e3.DepartmentID = Employees.DepartmentID
);-- 使用CTE更新
WITH EmployeeRanking AS (SELECT EmployeeID,Salary,ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRankFROM Employees
)
UPDATE e
SET e.Salary = e.Salary * 1.15
FROM Employees e
INNER JOIN EmployeeRanking er ON e.EmployeeID = er.EmployeeID
WHERE er.SalaryRank = 1;-- 使用OUTPUT子句记录更新
UPDATE Employees
SET Salary = Salary * 1.1
OUTPUT INSERTED.EmployeeID,DELETED.Salary AS OldSalary,INSERTED.Salary AS NewSalary,GETDATE() AS UpdateDate
INTO SalaryHistory(EmployeeID, OldSalary, NewSalary, ChangeDate)
WHERE DepartmentID = 2;

使用MERGE语句

-- 创建临时表模拟数据源
CREATE TABLE #TempEmployeeUpdates (EmployeeID INT,NewSalary DECIMAL(10,2),NewDepartmentID INT
);INSERT INTO #TempEmployeeUpdates VALUES 
(1, 8500.00, 2),
(2, 12500.00, 2),
(999, 15000.00, 1);  -- 不存在的员工-- 使用MERGE进行复杂的更新/插入/删除操作
MERGE Employees AS target
USING #TempEmployeeUpdates AS source
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED AND target.Salary != source.NewSalary THENUPDATE SET Salary = source.NewSalary,DepartmentID = source.NewDepartmentID,ModifiedDate = GETDATE()
WHEN NOT MATCHED BY TARGET THENINSERT (FirstName, LastName, Email, Salary, DepartmentID)VALUES ('新', '员工' + CAST(source.EmployeeID AS VARCHAR), 'new' + CAST(source.EmployeeID AS VARCHAR) + '@company.com', source.NewSalary, source.NewDepartmentID)
WHEN NOT MATCHED BY SOURCE AND target.IsActive = 0 THENDELETE
OUTPUT $action AS Action,INSERTED.EmployeeID AS InsertedID,DELETED.EmployeeID AS DeletedID,INSERTED.Salary AS NewSalary,DELETED.Salary AS OldSalary;DROP TABLE #TempEmployeeUpdates;

---

11. 删除数据

基本删除操作

-- 删除单个记录
DELETE FROM Employees WHERE EmployeeID = 100;-- 条件删除
DELETE FROM Employees 
WHERE Salary < 5000 AND IsActive = 0;-- 使用OUTPUT子句记录删除的数据
DELETE FROM Employees
OUTPUT DELETED.*
WHERE EmployeeID = 101;-- 删除并归档数据
DELETE FROM Employees
OUTPUT DELETED.* INTO ArchivedEmployees
WHERE IsActive = 0 AND DATEDIFF(YEAR, ModifiedDate, GETDATE()) > 2;

高级删除操作

-- 使用JOIN删除
DELETE ep
FROM EmployeeProjects ep
INNER JOIN Projects p ON ep.ProjectID = p.ProjectID
WHERE p.Status = 'Cancelled';-- 使用子查询删除
DELETE FROM SalaryHistory 
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE IsActive = 0
);-- 使用CTE删除重复数据
WITH DuplicateEmails AS (SELECT EmployeeID,Email,ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmployeeID) AS rnFROM Employees
)
DELETE FROM DuplicateEmails WHERE rn > 1;-- 批量删除（防止锁定过多记录）
DECLARE @BatchSize INT = 1000;
DECLARE @RowsDeleted INT = 1;WHILE @RowsDeleted > 0
BEGINDELETE TOP (@BatchSize) FROM LargeTableWHERE CreatedDate < DATEADD(YEAR, -5, GETDATE());SET @RowsDeleted = @@ROWCOUNT;WAITFOR DELAY '00:00:01';  -- 暂停1秒
END;

TRUNCATE TABLE

-- 快速删除所有数据（比DELETE更快，但不能回滚）
TRUNCATE TABLE TempEmployees;-- 注意：TRUNCATE不能用于有外键引用的表
-- 需要先删除外键约束
ALTER TABLE EmployeeProjects DROP CONSTRAINT FK_EmployeeProjects_Employees;
TRUNCATE TABLE Employees;
-- 然后重新添加约束

---

12. 视图详解

创建基本视图

-- 创建简单视图
CREATE VIEW vw_EmployeeBasicInfo
AS
SELECT EmployeeID,FirstName + ' ' + LastName AS FullName,Email,Phone,HireDate
FROM Employees
WHERE IsActive = 1;-- 创建带JOIN的视图
CREATE VIEW vw_EmployeeDepartmentInfo
AS
SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS FullName,e.Email,e.Salary,d.DepartmentName,d.Location,m.FirstName + ' ' + m.LastName AS ManagerName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
LEFT JOIN Employees m ON d.ManagerID = m.EmployeeID
WHERE e.IsActive = 1;-- 使用视图
SELECT * FROM vw_EmployeeDepartmentInfo WHERE Salary > 10000;

创建高级视图

-- 带聚合的视图
CREATE VIEW vw_DepartmentStatistics
AS
SELECT d.DepartmentID,d.DepartmentName,d.Location,d.Budget,COUNT(e.EmployeeID) AS EmployeeCount,ISNULL(AVG(e.Salary), 0) AS AverageSalary,ISNULL(SUM(e.Salary), 0) AS TotalSalaryExpense,d.Budget - ISNULL(SUM(e.Salary), 0) AS RemainingBudget
FROM Departments d
LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID AND e.IsActive = 1
GROUP BY d.DepartmentID, d.DepartmentName, d.Location, d.Budget;-- 带CTE的视图
CREATE VIEW vw_EmployeeRanking
AS
WITH SalaryRanking AS (SELECT EmployeeID,FirstName + ' ' + LastName AS FullName,Salary,DepartmentID,ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptSalaryRank,RANK() OVER (ORDER BY Salary DESC) AS CompanySalaryRankFROM EmployeesWHERE IsActive = 1
)
SELECT * FROM SalaryRanking;-- 带UNION的视图
CREATE VIEW vw_AllContacts
AS
SELECT 'Employee' AS ContactType,EmployeeID AS ContactID,FirstName + ' ' + LastName AS Name,Email,Phone
FROM Employees
WHERE IsActive = 1
UNION ALL
SELECT 'Manager' AS ContactType,m.EmployeeID AS ContactID,m.FirstName + ' ' + m.LastName AS Name,m.Email,m.Phone
FROM Departments d
INNER JOIN Employees m ON d.ManagerID = m.EmployeeID;

可更新视图

-- 创建可更新的视图
CREATE VIEW vw_ActiveEmployees
AS
SELECT EmployeeID,FirstName,LastName,Email,Phone,Salary,DepartmentID,IsActive
FROM Employees
WHERE IsActive = 1
WITH CHECK OPTION;  -- 确保通过视图的更新满足WHERE条件-- 通过视图更新数据
UPDATE vw_ActiveEmployees
SET Salary = Salary * 1.05
WHERE DepartmentID = 2;-- 通过视图插入数据
INSERT INTO vw_ActiveEmployees (FirstName, LastName, Email, Salary, DepartmentID, IsActive)
VALUES ('测试', '员工', 'test.view@company.com', 8000, 1, 1);-- 创建INSTEAD OF触发器使复杂视图可更新
CREATE TRIGGER trg_UpdateEmployeeDepartmentInfo
ON vw_EmployeeDepartmentInfo
INSTEAD OF UPDATE
AS
BEGINUPDATE eSET e.Email = i.Email,e.Salary = i.SalaryFROM Employees eINNER JOIN inserted i ON e.EmployeeID = i.EmployeeID;
END;

索引视图（物化视图）

-- 创建索引视图
CREATE VIEW vw_DepartmentSalarySummary
WITH SCHEMABINDING  -- 必需的
AS
SELECT d.DepartmentID,d.DepartmentName,COUNT_BIG(*) AS EmployeeCount,  -- 必须使用COUNT_BIGSUM(ISNULL(e.Salary, 0)) AS TotalSalary,SUM(ISNULL(CAST(e.Salary AS BIGINT), 0)) AS TotalSalaryBig
FROM dbo.Departments d  -- 必须使用架构名
INNER JOIN dbo.Employees e ON d.DepartmentID = e.DepartmentID
WHERE e.IsActive = 1
GROUP BY d.DepartmentID, d.DepartmentName;-- 在视图上创建聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_vw_DepartmentSalarySummary
ON vw_DepartmentSalarySummary (DepartmentID);-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_vw_DepartmentSalarySummary_TotalSalary
ON vw_DepartmentSalarySummary (TotalSalary);

分区视图

-- 创建分区表
CREATE TABLE Employees_2023 (EmployeeID INT PRIMARY KEY CHECK (EmployeeID BETWEEN 1 AND 1000),FirstName NVARCHAR(50),LastName NVARCHAR(50),HireDate DATE CHECK (YEAR(HireDate) = 2023)
);CREATE TABLE Employees_2024 (EmployeeID INT PRIMARY KEY CHECK (EmployeeID BETWEEN 1001 AND 2000),FirstName NVARCHAR(50),LastName NVARCHAR(50),HireDate DATE CHECK (YEAR(HireDate) = 2024)
);-- 创建分区视图
CREATE VIEW vw_AllEmployees
AS
SELECT * FROM Employees_2023
UNION ALL
SELECT * FROM Employees_2024;

视图管理

-- 修改视图
ALTER VIEW vw_EmployeeBasicInfo
AS
SELECT EmployeeID,FirstName + ' ' + LastName AS FullName,Email,Phone,HireDate,DepartmentID  -- 新增字段
FROM Employees
WHERE IsActive = 1;-- 查看视图定义
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_EmployeeBasicInfo'));-- 使用系统视图查看视图信息
SELECT s.name AS SchemaName,v.name AS ViewName,v.create_date,v.modify_date
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
ORDER BY s.name, v.name;-- 查看视图依赖关系
SELECT OBJECT_NAME(referencing_id) AS ViewName,OBJECT_NAME(referenced_id) AS ReferencedObject,referenced_entity_name AS ReferencedColumn
FROM sys.sql_expression_dependencies
WHERE OBJECT_NAME(referencing_id) = 'vw_EmployeeDepartmentInfo';-- 刷新视图元数据
EXEC sp_refreshview 'vw_EmployeeDepartmentInfo';-- 加密视图定义
CREATE VIEW vw_SensitiveData
WITH ENCRYPTION
AS
SELECT * FROM Employees WHERE Salary > 100000;-- 删除视图
DROP VIEW IF EXISTS vw_EmployeeBasicInfo;

---

13. 存储过程详解

创建基本存储过程

-- 创建简单存储过程
CREATE PROCEDURE sp_GetAllEmployees
AS
BEGINSET NOCOUNT ON;SELECT EmployeeID,FirstName + ' ' + LastName AS FullName,Email,Salary,DepartmentIDFROM EmployeesWHERE IsActive = 1ORDER BY LastName, FirstName;
END;-- 执行存储过程
EXEC sp_GetAllEmployees;-- 带参数的存储过程
CREATE PROCEDURE sp_GetEmployeesByDepartment@DepartmentID INT
AS
BEGINSET NOCOUNT ON;SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS FullName,e.Email,e.Salary,d.DepartmentNameFROM Employees eINNER JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE e.DepartmentID = @DepartmentIDAND e.IsActive = 1ORDER BY e.Salary DESC;
END;-- 执行带参数的存储过程
EXEC sp_GetEmployeesByDepartment @DepartmentID = 2;

带多个参数和默认值的存储过程

CREATE PROCEDURE sp_SearchEmployees@FirstName NVARCHAR(50) = NULL,@LastName NVARCHAR(50) = NULL,@DepartmentID INT = NULL,@MinSalary DECIMAL(10,2) = NULL,@MaxSalary DECIMAL(10,2) = NULL,@IsActive BIT = 1
AS
BEGINSET NOCOUNT ON;SELECT e.EmployeeID,e.FirstName,e.LastName,e.Email,e.Salary,d.DepartmentNameFROM Employees eLEFT JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE (@FirstName IS NULL OR e.FirstName LIKE '%' + @FirstName + '%')AND (@LastName IS NULL OR e.LastName LIKE '%' + @LastName + '%')AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)AND (@MinSalary IS NULL OR e.Salary >= @MinSalary)AND (@MaxSalary IS NULL OR e.Salary <= @MaxSalary)AND e.IsActive = @IsActiveORDER BY e.LastName, e.FirstName;
END;-- 多种调用方式
EXEC sp_SearchEmployees;
EXEC sp_SearchEmployees @FirstName = '张';
EXEC sp_SearchEmployees @DepartmentID = 2, @MinSalary = 8000;

带输出参数的存储过程

CREATE PROCEDURE sp_GetDepartmentStatistics@DepartmentID INT,@EmployeeCount INT OUTPUT,@AverageSalary DECIMAL(10,2) OUTPUT,@TotalSalary DECIMAL(15,2) OUTPUT
AS
BEGINSET NOCOUNT ON;SELECT @EmployeeCount = COUNT(*),@AverageSalary = AVG(Salary),@TotalSalary = SUM(Salary)FROM EmployeesWHERE DepartmentID = @DepartmentIDAND IsActive = 1;-- 返回详细信息SELECT d.DepartmentName,d.Location,d.Budget,@EmployeeCount AS EmployeeCount,@AverageSalary AS AverageSalary,@TotalSalary AS TotalSalary,d.Budget - @TotalSalary AS RemainingBudgetFROM Departments dWHERE d.DepartmentID = @DepartmentID;
END;-- 调用带输出参数的存储过程
DECLARE @EmpCount INT, @AvgSalary DECIMAL(10,2), @TotalSal DECIMAL(15,2);EXEC sp_GetDepartmentStatistics @DepartmentID = 2,@EmployeeCount = @EmpCount OUTPUT,@AverageSalary = @AvgSalary OUTPUT,@TotalSalary = @TotalSal OUTPUT;SELECT @EmpCount AS EmployeeCount, @AvgSalary AS AverageSalary, @TotalSal AS TotalSalary;

带返回值的存储过程

CREATE PROCEDURE sp_UpdateEmployeeSalary@EmployeeID INT,@NewSalary DECIMAL(10,2)
AS
BEGINSET NOCOUNT ON;-- 检查员工是否存在IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)BEGINRETURN -1;  -- 员工不存在END-- 检查薪资是否合理IF @NewSalary <= 0BEGINRETURN -2;  -- 薪资无效END-- 记录旧薪资DECLARE @OldSalary DECIMAL(10,2);SELECT @OldSalary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;-- 更新薪资UPDATE EmployeesSET Salary = @NewSalary,ModifiedDate = GETDATE()WHERE EmployeeID = @EmployeeID;-- 记录薪资历史INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, Reason)VALUES (@EmployeeID, @OldSalary, @NewSalary, '定期调薪');RETURN 0;  -- 成功
END;-- 调用并检查返回值
DECLARE @ReturnValue INT;
EXEC @ReturnValue = sp_UpdateEmployeeSalary @EmployeeID = 1, @NewSalary = 9500;IF @ReturnValue = 0PRINT '薪资更新成功';
ELSE IF @ReturnValue = -1PRINT '员工不存在';
ELSE IF @ReturnValue = -2PRINT '薪资金额无效';

带事务处理的存储过程

CREATE PROCEDURE sp_TransferEmployee@EmployeeID INT,@NewDepartmentID INT,@Reason NVARCHAR(200)
AS
BEGINSET NOCOUNT ON;SET XACT_ABORT ON;  -- 出错时自动回滚BEGIN TRYBEGIN TRANSACTION;-- 检查员工是否存在IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)BEGINRAISERROR('员工不存在', 16, 1);END-- 检查部门是否存在IF NOT EXISTS (SELECT 1 FROM Departments WHERE DepartmentID = @NewDepartmentID)BEGINRAISERROR('部门不存在', 16, 1);END-- 记录转部门前的信息DECLARE @OldDepartmentID INT;SELECT @OldDepartmentID = DepartmentID FROM Employees WHERE EmployeeID = @EmployeeID;-- 更新员工部门UPDATE EmployeesSET DepartmentID = @NewDepartmentID,ModifiedDate = GETDATE()WHERE EmployeeID = @EmployeeID;-- 记录转部门历史INSERT INTO TransferHistory (EmployeeID, OldDepartmentID, NewDepartmentID, TransferDate, Reason)VALUES (@EmployeeID, @OldDepartmentID, @NewDepartmentID, GETDATE(), @Reason);-- 更新相关项目UPDATE EmployeeProjectsSET UnassignedDate = GETDATE()WHERE EmployeeID = @EmployeeIDAND ProjectID IN (SELECT ProjectID FROM Projects WHERE DepartmentID = @OldDepartmentID)AND UnassignedDate IS NULL;COMMIT TRANSACTION;SELECT '员工转部门成功' AS Result;END TRYBEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();DECLARE @ErrorSeverity INT = ERROR_SEVERITY();DECLARE @ErrorState INT = ERROR_STATE();RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);END CATCH;
END;

动态SQL存储过程

CREATE PROCEDURE sp_DynamicEmployeeReport@SelectColumns NVARCHAR(MAX) = '*',@WhereClause NVARCHAR(MAX) = NULL,@OrderBy NVARCHAR(200) = 'EmployeeID',@TopN INT = NULL
AS
BEGINSET NOCOUNT ON;DECLARE @SQL NVARCHAR(MAX);-- 构建基本查询SET @SQL = 'SELECT ';-- 添加TOP子句IF @TopN IS NOT NULLSET @SQL = @SQL + 'TOP ' + CAST(@TopN AS NVARCHAR(10)) + ' ';-- 添加列SET @SQL = @SQL + @SelectColumns + ' FROM Employees e INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID ';-- 添加WHERE子句IF @WhereClause IS NOT NULLSET @SQL = @SQL + 'WHERE ' + @WhereClause + ' ';-- 添加ORDER BY子句SET @SQL = @SQL + 'ORDER BY ' + @OrderBy;-- 打印SQL语句（用于调试）PRINT @SQL;-- 执行动态SQLEXEC sp_executesql @SQL;
END;-- 调用示例
EXEC sp_DynamicEmployeeReport @SelectColumns = 'e.FirstName, e.LastName, e.Salary, d.DepartmentName',@WhereClause = 'e.Salary > 8000 AND e.IsActive = 1',@OrderBy = 'e.Salary DESC',@TopN = 10;-- 更安全的动态SQL（使用参数化查询）
CREATE PROCEDURE sp_SafeDynamicQuery@DepartmentID INT = NULL,@MinSalary DECIMAL(10,2) = NULL
AS
BEGINSET NOCOUNT ON;DECLARE @SQL NVARCHAR(MAX);DECLARE @Params NVARCHAR(200);SET @SQL = 'SELECT * FROM Employees WHERE 1=1 ';IF @DepartmentID IS NOT NULLSET @SQL = @SQL + 'AND DepartmentID = @DeptID ';IF @MinSalary IS NOT NULLSET @SQL = @SQL + 'AND Salary >= @MinSal ';SET @Params = '@DeptID INT, @MinSal DECIMAL(10,2)';EXEC sp_executesql @SQL, @Params, @DeptID = @DepartmentID, @MinSal = @MinSalary;
END;

递归存储过程

CREATE PROCEDURE sp_GetEmployeeHierarchy@ManagerID INT,@Level INT = 0
AS
BEGINSET NOCOUNT ON;-- 创建临时表存储结果（如果不存在）IF @Level = 0BEGINCREATE TABLE #Hierarchy (EmployeeID INT,EmployeeName NVARCHAR(100),DepartmentName NVARCHAR(100),Level INT,ManagerID INT);END-- 插入当前级别的员工INSERT INTO #HierarchySELECT e.EmployeeID,REPLICATE('  ', @Level) + e.FirstName + ' ' + e.LastName,d.DepartmentName,@Level,@ManagerIDFROM Employees eINNER JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE (@ManagerID IS NULL AND d.ManagerID = e.EmployeeID)OR (e.DepartmentID IN (SELECT DepartmentID FROM Departments WHERE ManagerID = @ManagerID));-- 递归调用获取下属DECLARE @CurrentEmployeeID INT;DECLARE emp_cursor CURSOR FORSELECT EmployeeID FROM #Hierarchy WHERE Level = @Level;OPEN emp_cursor;FETCH NEXT FROM emp_cursor INTO @CurrentEmployeeID;WHILE @@FETCH_STATUS = 0BEGINEXEC sp_GetEmployeeHierarchy @CurrentEmployeeID, @Level + 1;FETCH NEXT FROM emp_cursor INTO @CurrentEmployeeID;ENDCLOSE emp_cursor;DEALLOCATE emp_cursor;-- 返回结果（仅在顶层）IF @Level = 0BEGINSELECT * FROM #Hierarchy ORDER BY Level, EmployeeName;DROP TABLE #Hierarchy;END
END;

错误处理和日志记录

CREATE PROCEDURE sp_LoggedOperation@Operation NVARCHAR(100),@Parameters NVARCHAR(MAX)
AS
BEGINSET NOCOUNT ON;DECLARE @StartTime DATETIME2 = GETDATE();DECLARE @LogID INT;-- 记录操作开始INSERT INTO OperationLog (Operation, Parameters, StartTime, Status)VALUES (@Operation, @Parameters, @StartTime, 'Running');SET @LogID = SCOPE_IDENTITY();BEGIN TRY-- 执行实际操作IF @Operation = 'UpdateSalaries'BEGINUPDATE Employees SET Salary = Salary * 1.05;ENDELSE IF @Operation = 'DeleteInactive'BEGINDELETE FROM Employees WHERE IsActive = 0;END-- 更新日志：成功UPDATE OperationLogSET EndTime = GETDATE(),Duration = DATEDIFF(MILLISECOND, @StartTime, GETDATE()),Status = 'Success',RowsAffected = @@ROWCOUNTWHERE LogID = @LogID;END TRYBEGIN CATCH-- 更新日志：失败UPDATE OperationLogSET EndTime = GETDATE(),Duration = DATEDIFF(MILLISECOND, @StartTime, GETDATE()),Status = 'Failed',ErrorMessage = ERROR_MESSAGE(),ErrorNumber = ERROR_NUMBER()WHERE LogID = @LogID;-- 重新抛出错误THROW;END CATCH;
END;

系统存储过程扩展

-- 创建扩展存储过程查看表信息
CREATE PROCEDURE sp_TableInfo@TableName NVARCHAR(128)
AS
BEGINSET NOCOUNT ON;-- 表基本信息SELECT t.name AS TableName,s.name AS SchemaName,p.rows AS RowCount,SUM(a.total_pages) * 8 AS TotalSpaceKB,SUM(a.used_pages) * 8 AS UsedSpaceKBFROM sys.tables tINNER JOIN sys.schemas s ON t.schema_id = s.schema_idINNER JOIN sys.indexes i ON t.object_id = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idWHERE t.name = @TableNameGROUP BY t.name, s.name, p.rows;-- 列信息SELECT c.column_id,c.name AS ColumnName,t.name AS DataType,c.max_length,c.precision,c.scale,c.is_nullable,c.is_identity,dc.definition AS DefaultValueFROM sys.columns cINNER JOIN sys.types t ON c.user_type_id = t.user_type_idLEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_idWHERE c.object_id = OBJECT_ID(@TableName)ORDER BY c.column_id;-- 索引信息SELECT i.name AS IndexName,i.type_desc AS IndexType,i.is_unique,i.is_primary_key,STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS ColumnsFROM sys.indexes iINNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_idINNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_idWHERE i.object_id = OBJECT_ID(@TableName)GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key;
END;

存储过程管理

-- 修改存储过程
ALTER PROCEDURE sp_GetAllEmployees
AS
BEGINSET NOCOUNT ON;SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS FullName,e.Email,e.Salary,d.DepartmentName  -- 新增部门名称FROM Employees eLEFT JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE e.IsActive = 1ORDER BY e.LastName, e.FirstName;
END;-- 查看存储过程定义
SELECT OBJECT_DEFINITION(OBJECT_ID('sp_GetAllEmployees'));-- 查看所有存储过程
SELECT s.name AS SchemaName,p.name AS ProcedureName,p.create_date,p.modify_date
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
ORDER BY s.name, p.name;-- 查看存储过程依赖
SELECT OBJECT_NAME(referencing_id) AS ProcedureName,OBJECT_NAME(referenced_id) AS ReferencedObject
FROM sys.sql_expression_dependencies
WHERE OBJECT_NAME(referencing_id) = 'sp_GetEmployeesByDepartment';-- 重新编译存储过程
EXEC sp_recompile 'sp_GetAllEmployees';-- 加密存储过程
CREATE PROCEDURE sp_SensitiveProcedure
WITH ENCRYPTION
AS
BEGIN-- 敏感操作SELECT * FROM SensitiveData;
END;-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_GetAllEmployees;

---

14. 函数详解

标量函数（Scalar Functions）

-- 创建基本标量函数
CREATE FUNCTION fn_GetFullName
(@FirstName NVARCHAR(50),@LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGINRETURN @FirstName + ' ' + @LastName;
END;-- 使用标量函数
SELECT EmployeeID,dbo.fn_GetFullName(FirstName, LastName) AS FullName,Email
FROM Employees;-- 计算年龄的函数
CREATE FUNCTION fn_CalculateAge
(@BirthDate DATE
)
RETURNS INT
AS
BEGINRETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) - CASE WHEN DATEPART(MONTH, @BirthDate) > DATEPART(MONTH, GETDATE()) OR(DATEPART(MONTH, @BirthDate) = DATEPART(MONTH, GETDATE()) AND DATEPART(DAY, @BirthDate) > DATEPART(DAY, GETDATE()))THEN 1ELSE 0END;
END;-- 计算工作年限
CREATE FUNCTION fn_GetWorkYears
(@HireDate DATE
)
RETURNS DECIMAL(5,2)
AS
BEGINDECLARE @Years DECIMAL(5,2);SET @Years = DATEDIFF(DAY, @HireDate, GETDATE()) / 365.25;RETURN @Years;
END;-- 格式化货币
CREATE FUNCTION fn_FormatCurrency
(@Amount DECIMAL(15,2),@CurrencySymbol NVARCHAR(5) = '¥'
)
RETURNS NVARCHAR(20)
AS
BEGINRETURN @CurrencySymbol + FORMAT(@Amount, 'N2');
END;-- 使用多个函数
SELECT dbo.fn_GetFullName(FirstName, LastName) AS FullName,dbo.fn_GetWorkYears(HireDate) AS WorkYears,dbo.fn_FormatCurrency(Salary, '$') AS FormattedSalary
FROM Employees;

内联表值函数（Inline Table-Valued Functions）

-- 创建内联表值函数
CREATE FUNCTION fn_GetEmployeesByDepartment
(@DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName AS FullName,e.Email,e.Salary,e.HireDate,d.DepartmentNameFROM Employees eINNER JOIN Departments d ON e.DepartmentID = d.DepartmentIDWHERE e.DepartmentID = @DepartmentIDAND e.IsActive = 1
);-- 使用内联表值函数
SELECT * FROM dbo.fn_GetEmployeesByDepartment(2);-- 在JOIN中使用
SELECT d.DepartmentName,emp.FullName,emp.Salary
FROM Departments d
CROSS APPLY dbo.fn_GetEmployeesByDepartment(d.DepartmentID) emp
WHERE d.Location = '上海';-- 获取薪资范围内的员工
CREATE FUNCTION fn_GetEmployeesBySalaryRange
(@MinSalary DECIMAL(10,2),@MaxSalary DECIMAL(10,2)
)
RETURNS TABLE
AS
RETURN
(SELECT EmployeeID,FirstName + ' ' + LastName AS FullName,Salary,DepartmentID,CASE WHEN Salary < 8000 THEN '初级'WHEN Salary BETWEEN 8000 AND 12000 THEN '中级'ELSE '高级'END AS LevelFROM EmployeesWHERE Salary BETWEEN @MinSalary AND @MaxSalaryAND IsActive = 1
);-- 获取部门层级
CREATE FUNCTION fn_GetDepartmentHierarchy
(@DepartmentID INT
)
RETURNS TABLE
AS
RETURN
(WITH DeptHierarchy AS (SELECT DepartmentID,DepartmentName,ParentDepartmentID,0 AS LevelFROM DepartmentsWHERE DepartmentID = @DepartmentIDUNION ALLSELECT d.DepartmentID,d.DepartmentName,d.ParentDepartmentID,dh.Level + 1FROM Departments dINNER JOIN DeptHierarchy dh ON d.ParentDepartmentID = dh.DepartmentID)SELECT * FROM DeptHierarchy
);

多语句表值函数（Multi-Statement Table-Valued Functions）

-- 创建多语句表值函数
CREATE FUNCTION fn_GetEmployeeProjectSummary
(@StartDate DATE,@EndDate DATE
)
RETURNS @ProjectSummary TABLE
(EmployeeID INT,EmployeeName NVARCHAR(100),TotalProjects INT,TotalHours DECIMAL(10,2),AverageHoursPerProject DECIMAL(10,2),MostRecentProject NVARCHAR(200)
)
AS
BEGIN-- 插入基本数据INSERT INTO @ProjectSummary (EmployeeID, EmployeeName, TotalProjects, TotalHours)SELECT e.EmployeeID,e.FirstName + ' ' + e.LastName,COUNT(DISTINCT ep.ProjectID),SUM(ep.HoursWorked)FROM Employees eINNER JOIN EmployeeProjects ep ON e.EmployeeID = ep.EmployeeIDINNER JOIN Projects p ON ep.ProjectID = p.ProjectIDWHERE p.StartDate >= @StartDate AND p.StartDate <= @EndDateGROUP BY e.EmployeeID, e.FirstName, e.LastName;-- 更新平均工时UPDATE @ProjectSummarySET AverageHoursPerProject = TotalHours / NULLIF(TotalProjects, 0);-- 更新最近项目UPDATE psSET MostRecentProject = (SELECT TOP 1 p.ProjectNameFROM EmployeeProjects epINNER JOIN Projects p ON ep.ProjectID = p.ProjectIDWHERE ep.EmployeeID = ps.EmployeeIDORDER BY p.StartDate DESC)FROM @ProjectSummary ps;RETURN;
END;-- 使用多语句表值函数
SELECT * FROM dbo.fn_GetEmployeeProjectSummary('2024-01-01', '2024-12-31');-- 员工技能矩阵函数
CREATE FUNCTION fn_GetEmployeeSkillMatrix()
RETURNS @SkillMatrix TABLE
(EmployeeID INT,EmployeeName NVARCHAR(100),Programming INT,Database INT,ProjectManagement INT,Communication INT
)
AS
BEGIN-- 插入员工基本信息INSERT INTO @SkillMatrix (EmployeeID, EmployeeName)SELECT EmployeeID, FirstName + ' ' + LastNameFROM EmployeesWHERE IsActive = 1;-- 更新各项技能分数UPDATE smSET Programming = (SELECT MAX(es.ProficiencyLevel)FROM EmployeeSkills esINNER JOIN Skills s ON es.SkillID = s.SkillIDWHERE es.EmployeeID = sm.EmployeeIDAND s.SkillCategory = '编程语言')FROM @SkillMatrix sm;UPDATE smSET Database = (SELECT MAX(es.ProficiencyLevel)FROM EmployeeSkills esINNER JOIN Skills s ON es.SkillID = s.SkillIDWHERE es.EmployeeID = sm.EmployeeIDAND s.SkillCategory = '数据库')FROM @SkillMatrix sm;-- 设置默认值UPDATE @SkillMatrixSET Programming = ISNULL(Programming, 0),Database = ISNULL(Database, 0),ProjectManagement = ISNULL(ProjectManagement, 0),Communication = ISNULL(Communication, 0);RETURN;
END;

系统函数和高级用法

-- 创建确定性函数（可用于计算列和索引）
CREATE FUNCTION fn_GetQuarter
(@Date DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGINRETURN DATEPART(QUARTER, @Date);
END;-- 在计算列中使用
ALTER TABLE Projects
ADD Quarter AS dbo.fn_GetQuarter(StartDate) PERSISTED;-- 创建索引
CREATE INDEX IX_Projects_Quarter ON Projects(Quarter);-- CLR函数示例（需要先启用CLR）
-- 这是一个正则表达式验证函数的示例
CREATE FUNCTION fn_RegexMatch
(@Input NVARCHAR(MAX),@Pattern NVARCHAR(MAX)
)
RETURNS BIT
AS EXTERNAL NAME RegexAssembly.RegexFunctions.IsMatch;-- 递归函数示例
CREATE FUNCTION fn_Fibonacci
(@n INT
)
RETURNS INT
AS
BEGINIF @n <= 1RETURN @n;RETURN dbo.fn_Fibonacci(@n - 1) + dbo.fn_Fibonacci(@n - 2);
END;-- 日期处理函数集合
CREATE FUNCTION fn_GetWorkingDays
(@StartDate DATE,@EndDate DATE
)
RETURNS INT
AS
BEGINDECLARE @Days INT = 0;DECLARE @CurrentDate DATE = @StartDate;WHILE @CurrentDate <= @EndDateBEGINIF DATEPART(WEEKDAY, @CurrentDate) NOT IN (1, 7)  -- 不是周末SET @Days = @Days + 1;SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);ENDRETURN @Days;
END;-- JSON处理函数
CREATE FUNCTION fn_ParseJSON
(@json NVARCHAR(MAX),@key NVARCHAR(100)
)
RETURNS NVARCHAR(MAX)
AS
BEGINRETURN JSON_VALUE(@json, '$.' + @key);
END;

函数性能优化

-- 优化的内联表值函数（比多语句表值函数性能更好）
CREATE FUNCTION fn_GetDepartmentEmployeesOptimized
(@DepartmentID INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(SELECT e.EmployeeID,e.FirstName,e.LastName,e.SalaryFROM dbo.Employees eWHERE e.DepartmentID = @DepartmentIDAND e.IsActive = 1
);-- 创建索引视图配合函数使用
CREATE VIEW vw_EmployeeSalaryByDepartment
WITH SCHEMABINDING
AS
SELECT DepartmentID,COUNT_BIG(*) AS EmployeeCount,SUM(Salary) AS TotalSalary
FROM dbo.Employees
WHERE IsActive = 1
GROUP BY DepartmentID;CREATE UNIQUE CLUSTERED INDEX IX_vw_EmployeeSalaryByDepartment
ON vw_EmployeeSalaryByDepartment(DepartmentID);-- 避免在WHERE子句中使用标量函数
-- 不好的做法
SELECT * FROM Employees WHERE dbo.fn_GetWorkYears(HireDate) > 5;-- 好的做法
SELECT * FROM Employees WHERE HireDate < DATEADD(YEAR, -5, GETDATE());

函数管理

-- 修改函数
ALTER FUNCTION fn_GetFullName
(@FirstName NVARCHAR(50),@LastName NVARCHAR(50),@Format NVARCHAR(10) = 'FL'  -- 新增格式参数
)
RETURNS NVARCHAR(101)
AS
BEGINIF @Format = 'LF'RETURN @LastName + ', ' + @FirstName;ELSERETURN @FirstName + ' ' + @LastName;
END;-- 查看函数定义
SELECT OBJECT_DEFINITION(OBJECT_ID('fn_GetFullName'));-- 查看所有用户定义函数
SELECT s.name AS SchemaName,o.name AS FunctionName,o.type_desc AS FunctionType,o.create_date,o.modify_date
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type IN ('FN', 'IF', 'TF')  -- FN=标量, IF=内联表值, TF=多语句表值
ORDER BY s.name, o.name;-- 查看函数依赖
SELECT OBJECT_NAME(referencing_id) AS FunctionName,OBJECT_NAME(referenced_id) AS ReferencedObject,referenced_entity_name AS ReferencedColumn
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('fn_GetEmployeesByDepartment');-- 授权函数执行权限
GRANT EXECUTE ON fn_GetFullName TO [UserName];
GRANT SELECT ON fn_GetEmployeesByDepartment TO [UserName];-- 删除函数
DROP FUNCTION IF EXISTS fn_GetFullName;

15. 触发器

DDL触发器

-- 创建数据库级DDL触发器
CREATE TRIGGER trg_Database_DDL
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGINSET NOCOUNT ON;DECLARE @EventData XML = EVENTDATA();INSERT INTO DDLAuditLog (EventType,ObjectName,ObjectType,SQLCommand,LoginName,EventTime)VALUES (@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)'),GETDATE());-- 阻止在生产时间删除表IF @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') = 'DROP_TABLE'AND DATEPART(HOUR, GETDATE()) BETWEEN 9 AND 17  -- 工作时间BEGINRAISERROR('不允许在工作时间删除表！', 16, 1);ROLLBACK;END
END;-- 创建服务器级DDL触发器
CREATE TRIGGER trg_Server_LoginAudit
ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
BEGINSET NOCOUNT ON;DECLARE @EventData XML = EVENTDATA();INSERT INTO master.dbo.ServerAuditLog(EventType,LoginName,ServerPrincipalName,SQLCommand,ClientHost,EventTime)VALUES (@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)'),@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),HOST_NAME(),GETDATE());
END;

LOGON触发器

-- 创建登录触发器
CREATE TRIGGER trg_Monitor_Logins
ON ALL SERVER
FOR LOGON
AS
BEGINSET NOCOUNT ON;DECLARE @LoginName NVARCHAR(100) = ORIGINAL_LOGIN();DECLARE @ClientHost NVARCHAR(100) = HOST_NAME();DECLARE @AppName NVARCHAR(128) = APP_NAME();-- 记录登录信息INSERT INTO master.dbo.LoginAuditLog(LoginName, ClientHost, ApplicationName, LoginTime)VALUES (@LoginName, @ClientHost, @AppName, GETDATE());-- 限制同时连接数DECLARE @ConnectionCount INT;SELECT @ConnectionCount = COUNT(*) FROM sys.dm_exec_sessions WHERE login_name = @LoginNameAND session_id != @@SPID;IF @ConnectionCount > 5BEGINRAISERROR('用户连接数超过限制！', 16, 1);ROLLBACK;END-- 限制工作时间登录IF @LoginName LIKE '%_temp%' AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART(HOUR, GETDATE()) > 17)BEGINRAISERROR('临时账户只能在工作时间使用！', 16, 1);ROLLBACK;END
END;

触发器管理和优化

-- 查看所有触发器
SELECT s.name AS SchemaName,t.name AS TriggerName,OBJECT_NAME(parent_id) AS TableName,t.type_desc,t.is_disabled,t.is_instead_of_trigger,m.definition
FROM sys.triggers t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON t.object_id = m.object_id
ORDER BY s.name, OBJECT_NAME(parent_id), t.name;-- 禁用和启用触发器
ALTER TABLE Employees DISABLE TRIGGER trg_Employees_AfterUpdate;
ALTER TABLE Employees ENABLE TRIGGER trg_Employees_AfterUpdate;-- 禁用表上所有触发器
ALTER TABLE Employees DISABLE TRIGGER ALL;
ALTER TABLE Employees ENABLE TRIGGER ALL;-- 删除触发器
DROP TRIGGER IF EXISTS trg_Employees_AfterInsert;-- 检查触发器性能
SELECT OBJECT_NAME(object_id) AS TriggerName,execution_count,total_worker_time / 1000 AS total_cpu_ms,total_elapsed_time / 1000 AS total_duration_ms,(total_worker_time / execution_count) / 1000 AS avg_cpu_ms,(total_elapsed_time / execution_count) / 1000 AS avg_duration_ms
FROM sys.dm_exec_trigger_stats
ORDER BY total_worker_time DESC;-- 嵌套触发器示例（需谨慎使用）
CREATE TRIGGER trg_Departments_UpdateEmployeeCount
ON Departments
AFTER UPDATE
AS
BEGINSET NOCOUNT ON;IF UPDATE(DepartmentName)BEGIN-- 更新相关员工的缓存信息UPDATE Employees SET ModifiedDate = GETDATE()WHERE DepartmentID IN (SELECT DepartmentID FROM inserted);END
END;

---

16. 事务处理

基本事务操作

-- 显式事务
BEGIN TRANSACTION;UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE DepartmentID = 2;INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, Reason)
SELECT EmployeeID, Salary / 1.1, Salary, GETDATE(), '年度调薪'
FROM Employees 
WHERE DepartmentID = 2;-- 提交事务
COMMIT TRANSACTION;-- 回滚事务示例
BEGIN TRANSACTION;DELETE FROM Employees WHERE EmployeeID = 1;-- 假设发现错误，回滚
ROLLBACK TRANSACTION;

事务隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Employees;  -- 可能读到脏数据SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM Employees;  -- 默认级别，避免脏读SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Employees WHERE DepartmentID = 2;
-- 其他会话无法修改这些行
COMMIT;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Employees WHERE Salary > 10000;
-- 其他会话无法插入符合条件的新行
COMMIT;-- 使用快照隔离
ALTER DATABASE CompanyDB SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Employees;  -- 读取事务开始时的快照
COMMIT;-- 表级别的快照隔离（READ_COMMITTED_SNAPSHOT）
ALTER DATABASE CompanyDB SET READ_COMMITTED_SNAPSHOT ON;

TRY-CATCH错误处理

-- 基本错误处理
BEGIN TRYBEGIN TRANSACTION;UPDATE Employees SET Salary = 'invalid_value'  -- 这会导致错误WHERE EmployeeID = 1;COMMIT TRANSACTION;
END TRY
BEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_LINE() AS ErrorLine,ERROR_PROCEDURE() AS ErrorProcedure;
END CATCH;-- 复杂的事务处理存储过程
CREATE PROCEDURE sp_ProcessPayroll@DepartmentID INT,@BonusPercentage DECIMAL(5,2)
AS
BEGINSET NOCOUNT ON;SET XACT_ABORT ON;  -- 遇到错误自动回滚DECLARE @TranCount INT = @@TRANCOUNT;BEGIN TRYIF @TranCount = 0BEGIN TRANSACTION;ELSESAVE TRANSACTION sp_ProcessPayroll;-- 验证部门是否存在IF NOT EXISTS (SELECT 1 FROM Departments WHERE DepartmentID = @DepartmentID)RAISERROR('部门不存在', 16, 1);-- 验证奖金比例IF @BonusPercentage < 0 OR @BonusPercentage > 100RAISERROR('奖金比例必须在0-100之间', 16, 1);-- 计算并更新薪资DECLARE @UpdatedCount INT;UPDATE EmployeesSET Salary = Salary * (1 + @BonusPercentage / 100),ModifiedDate = GETDATE()WHERE DepartmentID = @DepartmentIDAND IsActive = 1;SET @UpdatedCount = @@ROWCOUNT;-- 记录薪资变更历史INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangeDate, Reason)SELECT EmployeeID,Salary / (1 + @BonusPercentage / 100),Salary,GETDATE(),'部门奖金调整 - ' + CAST(@BonusPercentage AS VARCHAR) + '%'FROM EmployeesWHERE DepartmentID = @DepartmentIDAND IsActive = 1;-- 更新部门预算UPDATE DepartmentsSET Budget = Budget - (SELECT SUM(Salary * @BonusPercentage / 100)FROM EmployeesWHERE DepartmentID = @DepartmentIDAND IsActive = 1)WHERE DepartmentID = @DepartmentID;IF @TranCount = 0COMMIT TRANSACTION;SELECT '处理成功' AS Result,@UpdatedCount AS UpdatedEmployees;END TRYBEGIN CATCHIF XACT_STATE() = -1  -- 事务不可提交BEGINIF @TranCount = 0ROLLBACK TRANSACTION;ELSEROLLBACK TRANSACTION sp_ProcessPayroll;ENDELSE IF XACT_STATE() = 1  -- 事务可提交BEGINIF @TranCount = 0COMMIT TRANSACTION;ENDDECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();DECLARE @ErrorSeverity INT = ERROR_SEVERITY();DECLARE @ErrorState INT = ERROR_STATE();RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);END CATCH;
END;

分布式事务

-- 使用分布式事务（需要MSDTC服务）
BEGIN DISTRIBUTED TRANSACTION;-- 本地操作
UPDATE Employees SET Salary = Salary * 1.05 WHERE DepartmentID = 1;-- 链接服务器操作
UPDATE LinkedServer.RemoteDB.dbo.EmployeeBackup 
SET LastUpdated = GETDATE()
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees WHERE DepartmentID = 1
);-- 提交分布式事务
COMMIT TRANSACTION;-- 检查分布式事务状态
SELECT * FROM sys.dm_tran_distributed_transaction;

保存点（Savepoint）

BEGIN TRANSACTION;-- 第一阶段操作
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1;
SAVE TRANSACTION Stage1;-- 第二阶段操作
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 2;
SAVE TRANSACTION Stage2;-- 第三阶段操作（假设出错）
BEGIN TRYUPDATE Employees SET Salary = 'Invalid';  -- 这会出错
END TRY
BEGIN CATCH-- 只回滚到Stage2，保留Stage1的更改ROLLBACK TRANSACTION Stage2;-- 继续其他操作UPDATE Employees SET Salary = Salary * 1.05 WHERE DepartmentID = 2;
END CATCH;COMMIT TRANSACTION;

死锁处理

-- 创建死锁处理存储过程
CREATE PROCEDURE sp_DeadlockRetry@MaxRetries INT = 3
AS
BEGINSET NOCOUNT ON;DECLARE @RetryCount INT = 0;DECLARE @Success BIT = 0;WHILE @RetryCount < @MaxRetries AND @Success = 0BEGINBEGIN TRYBEGIN TRANSACTION;-- 按照固定顺序访问表以避免死锁UPDATE Employees SET ModifiedDate = GETDATE() WHERE EmployeeID = 1;UPDATE Departments SET ModifiedDate = GETDATE() WHERE DepartmentID = 1;COMMIT TRANSACTION;SET @Success = 1;END TRYBEGIN CATCHIF @@TRANCOUNT > 0ROLLBACK TRANSACTION;-- 检查是否是死锁IF ERROR_NUMBER() = 1205BEGINSET @RetryCount = @RetryCount + 1;WAITFOR DELAY '00:00:01';  -- 等待1秒后重试PRINT '死锁检测到，重试中... (' + CAST(@RetryCount AS VARCHAR) + '/' + CAST(@MaxRetries AS VARCHAR) + ')';ENDELSEBEGIN-- 其他错误，重新抛出THROW;ENDEND CATCH;ENDIF @Success = 0RAISERROR('操作在多次重试后仍然失败', 16, 1);
END;-- 设置死锁优先级
SET DEADLOCK_PRIORITY LOW;  -- 在死锁时优先被终止
-- 或
SET DEADLOCK_PRIORITY HIGH;  -- 在死锁时优先保留

事务日志管理

-- 查看事务日志使用情况
SELECT name,log_reuse_wait_desc,log_reuse_wait,total_log_size_mb = total_log_size_in_bytes / 1024.0 / 1024.0,used_log_space_mb = used_log_space_in_bytes / 1024.0 / 1024.0,used_log_space_percent = used_log_space_in_percent
FROM sys.dm_db_log_space_usage;-- 检查活动事务
SELECT s.session_id,s.login_name,s.program_name,t.transaction_begin_time,DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS duration_seconds,t.transaction_type,t.transaction_state
FROM sys.dm_tran_session_transactions st
INNER JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions t ON st.transaction_id = t.transaction_id
ORDER BY t.transaction_begin_time;-- 备份事务日志
BACKUP LOG CompanyDB TO DISK = 'C:\Backup\CompanyDB_Log.trn';-- 在简单恢复模式下收缩日志
ALTER DATABASE CompanyDB SET RECOVERY SIMPLE;
DBCC SHRINKFILE('CompanyDB_Log', 10);  -- 收缩到10MB
ALTER DATABASE CompanyDB SET RECOVERY FULL;

---

17. 索引优化

聚集索引

-- 创建聚集索引（表只能有一个）
CREATE CLUSTERED INDEX IX_Employees_EmployeeID 
ON Employees (EmployeeID);-- 查看聚集索引信息
SELECT i.name AS IndexName,i.type_desc,OBJECT_NAME(i.object_id) AS TableName,STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.type = 1  -- 聚集索引
GROUP BY i.name, i.type_desc, i.object_id
ORDER BY OBJECT_NAME(i.object_id);-- 重建聚集索引
ALTER INDEX IX_Employees_EmployeeID ON Employees REBUILD;

非聚集索引

-- 创建简单非聚集索引
CREATE NONCLUSTERED INDEX IX_Employees_LastName 
ON Employees (LastName);-- 创建复合索引
CREATE NONCLUSTERED INDEX IX_Employees_Dept_Salary 
ON Employees (DepartmentID, Salary DESC);-- 创建包含列的索引
CREATE NONCLUSTERED INDEX IX_Employees_Email_Includes 
ON Employees (Email)
INCLUDE (FirstName, LastName, Phone);-- 创建条件索引
CREATE NONCLUSTERED INDEX IX_Employees_Active_Salary 
ON Employees (Salary)
WHERE IsActive = 1;-- 创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IX_Employees_Email_Unique 
ON Employees (Email)
WHERE Email IS NOT NULL;-- 查看索引统计信息
SELECT OBJECT_NAME(i.object_id) AS TableName,i.name AS IndexName,i.type_desc,ius.user_seeks,ius.user_scans,ius.user_lookups,ius.user_updates,ius.last_user_seek,ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECT_NAME(i.object_id) = 'Employees'
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC;

索引维护

-- 查看索引碎片
SELECT OBJECT_NAME(ips.object_id) AS TableName,i.name AS IndexName,ips.index_type_desc,ips.avg_fragmentation_in_percent,ips.page_count,CASE WHEN ips.avg_fragmentation_in_percent > 30 THEN 'REBUILD'WHEN ips.avg_fragmentation_in_percent > 10 THEN 'REORGANIZE'ELSE 'NO ACTION'END AS Recommendation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 100  -- 忽略小表
ORDER BY ips.avg_fragmentation_in_percent DESC;-- 重组索引
ALTER INDEX IX_Employees_LastName ON Employees REORGANIZE;-- 重建索引
ALTER INDEX IX_Employees_LastName ON Employees REBUILD;-- 重建表的所有索引
ALTER INDEX ALL ON Employees REBUILD;-- 在线重建索引（企业版功能）
ALTER INDEX IX_Employees_LastName ON Employees REBUILD 
WITH (ONLINE = ON);-- 更新统计信息
UPDATE STATISTICS Employees;
UPDATE STATISTICS Employees IX_Employees_LastName;-- 自动维护索引脚本
DECLARE @SQL NVARCHAR(MAX) = '';SELECT @SQL = @SQL + CASE WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ips.object_id) + '] REBUILD;' + CHAR(13)WHEN avg_fragmentation_in_percent > 10 THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ips.object_id) + '] REORGANIZE;' + CHAR(13)END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 100 
AND ips.avg_fragmentation_in_percent > 10;EXEC sp_executesql @SQL;

特殊索引类型

-- 列存储索引（用于数据仓库场景）
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData ON SalesData;CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_EmployeeAnalytics 
ON Employees (EmployeeID, DepartmentID, Salary, HireDate);-- XML索引
ALTER TABLE Projects ADD ProjectData XML;CREATE PRIMARY XML INDEX IX_Projects_ProjectData 
ON Projects (ProjectData);CREATE XML INDEX IX_Projects_ProjectData_Path 
ON Projects (ProjectData)
USING XML INDEX IX_Projects_ProjectData
FOR PATH;-- 全文索引
CREATE FULLTEXT CATALOG ft_CompanyDB;CREATE FULLTEXT INDEX ON Employees
(FirstName LANGUAGE 2052,  -- 中文LastName LANGUAGE 2052
)
KEY INDEX PK_Employees
ON ft_CompanyDB;-- 全文搜索
SELECT * FROM Employees
WHERE CONTAINS(FirstName, '"张*"');-- 空间索引（地理数据）
ALTER TABLE Departments ADD Location GEOGRAPHY;CREATE SPATIAL INDEX IX_Departments_Location 
ON Departments (Location)
WITH (GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM));-- 内存优化表索引
CREATE TABLE EmployeesMemory
(EmployeeID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),FirstName NVARCHAR(50) NOT NULL,LastName NVARCHAR(50) NOT NULL,Email NVARCHAR(100) NOT NULL,INDEX IX_EmployeesMemory_Name NONCLUSTERED (LastName, FirstName)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

索引设计建议

-- 查找缺失的索引
SELECT mid.statement AS TableName,mid.equality_columns,mid.inequality_columns,mid.included_columns,migs.unique_compiles,migs.user_seeks,migs.avg_total_user_cost,migs.avg_user_impact,'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.dbo.', '_') + '_Missing' +' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NOT NULL THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ',' ELSE '' END + mid.inequality_columns ELSE '' END + ')' +CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS CreateIndexSQL
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_user_impact > 20
ORDER BY migs.avg_user_impact DESC;-- 查找未使用的索引
SELECT OBJECT_NAME(i.object_id) AS TableName,i.name AS IndexName,i.type_desc,ius.user_seeks,ius.user_scans,ius.user_lookups,ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND i.index_id > 0  -- 排除堆
AND (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0
OR ius.user_seeks IS NULL
ORDER BY OBJECT_NAME(i.object_id), i.name;-- 查找重复的索引
WITH IndexColumns AS (SELECT OBJECT_NAME(i.object_id) AS TableName,i.name AS IndexName,i.index_id,i.is_unique,STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumnsFROM sys.indexes iINNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_idINNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_idWHERE i.index_id > 0 AND ic.is_included_column = 0GROUP BY i.object_id, i.name, i.index_id, i.is_unique
)
SELECT ic1.TableName,ic1.IndexName AS Index1,ic2.IndexName AS Index2,ic1.KeyColumns
FROM IndexColumns ic1
INNER JOIN IndexColumns ic2 ON ic1.TableName = ic2.TableName AND ic1.KeyColumns = ic2.KeyColumnsAND ic1.index_id < ic2.index_id;

---

18. 安全性管理

用户和角色管理

-- 创建登录名
CREATE LOGIN CompanyUser WITH PASSWORD = 'StrongP@ssw0rd123!';
CREATE LOGIN CompanyReader WITH PASSWORD = 'ReadOnly@2024!';-- 创建数据库用户
USE CompanyDB;
CREATE USER CompanyUser FOR LOGIN CompanyUser;
CREATE USER CompanyReader FOR LOGIN CompanyReader;-- 创建自定义角色
CREATE ROLE db_hr_manager;
CREATE ROLE db_finance_viewer;
CREATE ROLE db_project_manager;-- 添加用户到角色
ALTER ROLE db_hr_manager ADD MEMBER CompanyUser;
ALTER ROLE db_finance_viewer ADD MEMBER CompanyReader;-- 查看用户和角色
SELECT p.name AS PrincipalName,p.type_desc AS PrincipalType,p.default_schema_name,p.create_date,p.modify_date
FROM sys.database_principals p
WHERE p.type IN ('S', 'U', 'R')  -- S=SQL用户, U=Windows用户, R=角色
ORDER BY p.type_desc, p.name;-- 查看角色成员
SELECT r.name AS RoleName,m.name AS MemberName,m.type_desc AS MemberType
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

权限管理

-- 授予权限
GRANT SELECT ON Employees TO CompanyReader;
GRANT SELECT, INSERT, UPDATE ON Departments TO db_hr_manager;
GRANT EXECUTE ON sp_GetEmployeesByDepartment TO CompanyUser;-- 授予列级权限
GRANT SELECT (FirstName, LastName, Email) ON Employees TO CompanyReader;
GRANT UPDATE (Phone, Email) ON Employees TO CompanyUser;-- 拒绝权限
DENY DELETE ON Employees TO CompanyUser;
DENY SELECT (Salary) ON Employees TO CompanyReader;-- 撤销权限
REVOKE SELECT ON Employees FROM CompanyReader;-- 架构权限
GRANT CREATE TABLE TO db_hr_manager;
GRANT ALTER ANY SCHEMA TO db_project_manager;-- 查看权限
SELECT p.state_desc,p.permission_name,p.class_desc,pr.name AS PrincipalName,OBJECT_NAME(p.major_id) AS ObjectName,c.name AS ColumnName
FROM sys.database_permissions p
LEFT JOIN sys.objects o ON p.major_id = o.object_id
LEFT JOIN sys.columns c ON p.major_id = c.object_id AND p.minor_id = c.column_id
INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id
WHERE pr.name = 'CompanyUser'
ORDER BY p.permission_name, pr.name;-- 有效权限检查
SELECT p.class_desc,p.permission_name,p.state_desc,OBJECT_NAME(p.major_id) AS ObjectName
FROM fn_my_permissions(NULL, 'DATABASE') p
ORDER BY p.class_desc, p.permission_name;-- 检查用户对特定对象的权限
SELECT * FROM fn_my_permissions('Employees', 'OBJECT');

行级安全性（RLS）

-- 创建安全策略函数
CREATE FUNCTION dbo.fn_SecurityPredicate(@DepartmentID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
(SELECT 1 AS fn_SecurityPredicate_resultWHERE @DepartmentID = USER_ID() OR IS_MEMBER('db_datareader') = 1OR IS_MEMBER('db_hr_manager') = 1
);-- 创建安全策略
CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employees,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employees AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employees AFTER UPDATE
WITH (STATE = ON);-- 基于用户的行级安全
CREATE FUNCTION dbo.fn_UserSecurityPredicate(@CreatedBy NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
(SELECT 1 AS fn_SecurityPredicate_resultWHERE @CreatedBy = USER_NAME()OR IS_MEMBER('db_owner') = 1
);-- 动态数据掩码
ALTER TABLE Employees 
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');ALTER TABLE Employees 
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXXX-",4)');ALTER TABLE Employees 
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');-- 授予查看掩码数据的权限
GRANT UNMASK TO db_hr_manager;-- 查看掩码定义
SELECT t.name AS TableName,c.name AS ColumnName,c.is_masked,c.masking_function
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.is_masked = 1;

透明数据加密（TDE）

-- 1. 创建主密钥
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey@2024!';-- 2. 创建证书
CREATE CERTIFICATE TDECert 
WITH SUBJECT = 'TDE Certificate for CompanyDB';-- 3. 创建数据库加密密钥
USE CompanyDB;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;-- 4. 启用透明数据加密
ALTER DATABASE CompanyDB SET ENCRYPTION ON;-- 查看加密状态
SELECT db.name AS DatabaseName,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length
FROM sys.dm_database_encryption_keys dm
INNER JOIN sys.databases db ON dm.database_id = db.database_id;-- 备份证书（重要！）
BACKUP CERTIFICATE TDECert 
TO FILE = 'C:\Backup\TDECert.cer'
WITH PRIVATE KEY 
(FILE = 'C:\Backup\TDECert.pvk',ENCRYPTION BY PASSWORD = 'CertBackup@2024!'
);

Always Encrypted

-- 创建列主密钥
CREATE COLUMN MASTER KEY CMK_Auto1
WITH
(KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',KEY_PATH = 'CurrentUser/My/ThumBprint'
);-- 创建列加密密钥
CREATE COLUMN ENCRYPTION KEY CEK_Auto1
WITH VALUES
(COLUMN_MASTER_KEY = CMK_Auto1,ALGORITHM = 'RSA_OAEP',ENCRYPTED_VALUE = 0x016E000001630075007200720065006E...
);-- 创建加密列的表
CREATE TABLE EncryptedEmployees
(EmployeeID INT NOT NULL,FirstName NVARCHAR(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK_Auto1),Salary DECIMAL(10,2) ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK_Auto1),CONSTRAINT PK_EncryptedEmployees PRIMARY KEY (EmployeeID)
);

审计

-- 创建服务器审计
USE master;
CREATE SERVER AUDIT CompanyAudit
TO FILE 
(FILEPATH = 'C:\Audit\',MAXSIZE = 100 MB,MAX_ROLLOVER_FILES = 10
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);-- 启用服务器审计
ALTER SERVER AUDIT CompanyAudit WITH (STATE = ON);-- 创建数据库审计规范
USE CompanyDB;
CREATE DATABASE AUDIT SPECIFICATION CompanyDB_Audit
FOR SERVER AUDIT CompanyAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Employees BY public),
ADD (EXECUTE ON dbo.sp_UpdateEmployeeSalary BY public);-- 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION CompanyDB_Audit WITH (STATE = ON);-- 查看审计日志
SELECT event_time,action_id,succeeded,session_server_principal_name,database_name,schema_name,object_name,statement
FROM fn_get_audit_file('C:\Audit\CompanyAudit*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;-- SQL Server Audit事件示例
CREATE SERVER AUDIT SPECIFICATION LoginAudit
FOR SERVER AUDIT CompanyAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP);ALTER SERVER AUDIT SPECIFICATION LoginAudit WITH (STATE = ON);

安全最佳实践

-- 1. 定期检查过期登录
SELECT name,create_date,modify_date,DATEDIFF(DAY, modify_date, GETDATE()) AS days_since_last_modify
FROM sys.sql_logins
WHERE is_expiration_checked = 1
AND DATEDIFF(DAY, modify_date, GETDATE()) > 90;-- 2. 检查空密码或弱密码
SELECT name
FROM sys.sql_logins
WHERE PWDCOMPARE('', password_hash) = 1
OR PWDCOMPARE(name, password_hash) = 1
OR PWDCOMPARE('password', password_hash) = 1
OR PWDCOMPARE('123456', password_hash) = 1;-- 3. 查看过度权限的用户
SELECT p.name AS PrincipalName,p.type_desc,pe.permission_name,pe.state_desc,OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_principals p
INNER JOIN sys.database_permissions pe ON p.principal_id = pe.grantee_principal_id
WHERE pe.permission_name IN ('CONTROL', 'ALTER', 'TAKE OWNERSHIP')
AND p.name NOT IN ('dbo', 'sa');-- 4. 安全配置检查
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;-- 检查重要的安全配置
SELECT name,value_in_use,CASE WHEN name = 'remote access' AND value_in_use = 1 THEN '建议禁用远程访问'WHEN name = 'xp_cmdshell' AND value_in_use = 1 THEN '建议禁用xp_cmdshell'WHEN name = 'Ad Hoc Distributed Queries' AND value_in_use = 1 THEN '建议禁用Ad Hoc分布式查询'ELSE '配置正常'END AS SecurityRecommendation
FROM sys.configurations
WHERE name IN ('remote access', 'xp_cmdshell', 'Ad Hoc Distributed Queries');

---

19. 备份和恢复

完整备份

-- 基本完整备份
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Full.bak';-- 带压缩的完整备份
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Full_Compressed.bak'
WITH COMPRESSION, INIT;-- 多文件备份（并行处理）
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Full_1.bak',DISK = 'C:\Backup\CompanyDB_Full_2.bak',DISK = 'C:\Backup\CompanyDB_Full_3.bak'
WITH FORMAT, COMPRESSION;-- 验证备份
RESTORE VERIFYONLY 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak';-- 查看备份信息
RESTORE HEADERONLY 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak';RESTORE FILELISTONLY 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak';

差异备份

-- 差异备份（基于最后的完整备份）
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;-- 查看备份链
SELECT database_name,backup_start_date,backup_finish_date,type,CASE typeWHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Log'END AS backup_type,compressed_backup_size / 1024 / 1024 AS size_mb,first_lsn,last_lsn,differential_base_lsn
FROM msdb.dbo.backupset
WHERE database_name = 'CompanyDB'
ORDER BY backup_start_date DESC;

事务日志备份

-- 前提：数据库必须处于完整恢复模式
ALTER DATABASE CompanyDB SET RECOVERY FULL;-- 事务日志备份
BACKUP LOG CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Log.trn';-- 带压缩的日志备份
BACKUP LOG CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Log_Compressed.trn'
WITH COMPRESSION;-- 截断日志（仅截断，不备份）
BACKUP LOG CompanyDB WITH TRUNCATE_ONLY;  -- 已弃用
-- 新方法：
ALTER DATABASE CompanyDB SET RECOVERY SIMPLE;
ALTER DATABASE CompanyDB SET RECOVERY FULL;

文件和文件组备份

-- 文件组备份
BACKUP DATABASE CompanyDB 
FILEGROUP = 'PRIMARY'
TO DISK = 'C:\Backup\CompanyDB_Primary.bak';-- 文件备份
BACKUP DATABASE CompanyDB 
FILE = 'CompanyDB_Data'
TO DISK = 'C:\Backup\CompanyDB_DataFile.bak';-- 部分备份（主文件组和读写文件组）
BACKUP DATABASE CompanyDB 
READ_WRITE_FILEGROUPS
TO DISK = 'C:\Backup\CompanyDB_Partial.bak';

数据库恢复

-- 完整恢复（覆盖现有数据库）
RESTORE DATABASE CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak'
WITH REPLACE;-- 恢复到不同名称
RESTORE DATABASE CompanyDB_Test 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak'
WITH MOVE 'CompanyDB_Data' TO 'C:\Data\CompanyDB_Test.mdf',MOVE 'CompanyDB_Log' TO 'C:\Data\CompanyDB_Test.ldf';-- 恢复到特定时间点
RESTORE DATABASE CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak'
WITH NORECOVERY;RESTORE DATABASE CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Diff.bak'
WITH NORECOVERY;RESTORE LOG CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Log1.trn'
WITH NORECOVERY;RESTORE LOG CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Log2.trn'
WITH STOPAT = '2024-12-15 14:30:00';-- 在线页面恢复（企业版功能）
RESTORE DATABASE CompanyDB 
PAGE = '1:100,1:101' 
FROM DISK = 'C:\Backup\CompanyDB_Full.bak'
WITH NORECOVERY;RESTORE LOG CompanyDB 
FROM DISK = 'C:\Backup\CompanyDB_Log.trn';

快照备份

-- 创建数据库快照
CREATE DATABASE CompanyDB_Snapshot_20241215 ON
(NAME = 'CompanyDB_Data',FILENAME = 'C:\Snapshots\CompanyDB_Snapshot_20241215.ss'
)
AS SNAPSHOT OF CompanyDB;-- 从快照恢复数据库
RESTORE DATABASE CompanyDB 
FROM DATABASE_SNAPSHOT = 'CompanyDB_Snapshot_20241215';-- 删除快照
DROP DATABASE CompanyDB_Snapshot_20241215;

自动化备份脚本

-- 创建备份策略存储过程
CREATE PROCEDURE sp_BackupStrategy@DatabaseName NVARCHAR(128),@BackupType VARCHAR(10) = 'FULL',  -- FULL, DIFF, LOG@BackupPath NVARCHAR(500) = 'C:\Backup\'
AS
BEGINSET NOCOUNT ON;DECLARE @FileName NVARCHAR(500);DECLARE @SQL NVARCHAR(MAX);DECLARE @Date NVARCHAR(20) = FORMAT(GETDATE(), 'yyyyMMdd_HHmmss');-- 构建文件名SET @FileName = @BackupPath + @DatabaseName + '_' + @BackupType + '_' + @Date;IF @BackupType = 'FULL'BEGINSET @FileName = @FileName + '.bak';SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @FileName + ''' WITH COMPRESSION, CHECKSUM, INIT';ENDELSE IF @BackupType = 'DIFF'BEGINSET @FileName = @FileName + '.bak';SET @SQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @FileName + ''' WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, INIT';ENDELSE IF @BackupType = 'LOG'BEGINSET @FileName = @FileName + '.trn';SET @SQL = 'BACKUP LOG [' + @DatabaseName + '] TO DISK = ''' + @FileName + ''' WITH COMPRESSION, CHECKSUM, INIT';ENDPRINT '执行备份: ' + @SQL;EXEC sp_executesql @SQL;-- 记录备份信息INSERT INTO BackupLog (DatabaseName, BackupType, FileName, BackupDate, Status)VALUES (@DatabaseName, @BackupType, @FileName, GETDATE(), '成功');PRINT '备份完成: ' + @FileName;
END;-- 清理旧备份文件的存储过程
CREATE PROCEDURE sp_CleanupOldBackups@BackupPath NVARCHAR(500) = 'C:\Backup\',@RetentionDays INT = 30
AS
BEGINSET NOCOUNT ON;DECLARE @SQL NVARCHAR(MAX);DECLARE @CutoffDate DATETIME = DATEADD(DAY, -@RetentionDays, GETDATE());-- 使用xp_cmdshell删除旧文件（需要启用）SET @SQL = 'forfiles /p "' + @BackupPath + '" /s /m *.bak /d -' + CAST(@RetentionDays AS VARCHAR) + ' /c "cmd /c del @path"';EXEC xp_cmdshell @SQL;SET @SQL = 'forfiles /p "' + @BackupPath + '" /s /m *.trn /d -' + CAST(@RetentionDays AS VARCHAR) + ' /c "cmd /c del @path"';EXEC xp_cmdshell @SQL;PRINT '已清理 ' + CAST(@RetentionDays AS VARCHAR) + ' 天前的备份文件';
END;

备份压缩和加密

-- 压缩备份
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Compressed.bak'
WITH COMPRESSION,STATS = 10;  -- 每10%显示进度-- 加密备份
CREATE CERTIFICATE BackupCert 
WITH SUBJECT = 'Backup Certificate';BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backup\CompanyDB_Encrypted.bak'
WITH ENCRYPTION 
(ALGORITHM = AES_256,SERVER CERTIFICATE = BackupCert
),
COMPRESSION;-- 恢复加密备份
RESTORE DATABASE CompanyDB_Test 
FROM DISK = 'C:\Backup\CompanyDB_Encrypted.bak'
WITH MOVE 'CompanyDB_Data' TO 'C:\Data\CompanyDB_Test.mdf',MOVE 'CompanyDB_Log' TO 'C:\Data\CompanyDB_Test.ldf',REPLACE;

监控备份状态

-- 查看当前备份进度
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2), r.percent_complete) AS percent_complete,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS estimated_completion_time,CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS elapsed_minutes,CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS estimated_minutes,CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS estimated_hours,s.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command IN ('BACKUP DATABASE', 'RESTORE DATABASE', 'BACKUP LOG');-- 查看备份历史
SELECT TOP 20s.database_name,s.backup_start_date,s.backup_finish_date,DATEDIFF(MINUTE, s.backup_start_date, s.backup_finish_date) AS duration_minutes,CASE s.typeWHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Log'END AS backup_type,s.backup_size / 1024 / 1024 AS backup_size_mb,s.compressed_backup_size / 1024 / 1024 AS compressed_size_mb,m.physical_device_name
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY s.backup_start_date DESC;-- 检查备份完整性
SELECT database_name,MAX(CASE WHEN type = 'D' THEN backup_start_date END) AS last_full_backup,MAX(CASE WHEN type = 'I' THEN backup_start_date END) AS last_diff_backup,MAX(CASE WHEN type = 'L' THEN backup_start_date END) AS last_log_backup,DATEDIFF(DAY, MAX(CASE WHEN type = 'D' THEN backup_start_date END), GETDATE()) AS days_since_full,DATEDIFF(HOUR, MAX(CASE WHEN type = 'L' THEN backup_start_date END), GETDATE()) AS hours_since_log
FROM msdb.dbo.backupset
WHERE database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
GROUP BY database_name
ORDER BY database_name;

---

20. 性能优化

查询性能分析

-- 启用查询计划统计
SET STATISTICS IO ON;
SET STATISTICS TIME ON;-- 执行查询
SELECT e.FirstName + ' ' + e.LastName AS FullName,d.DepartmentName,e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > 10000;-- 关闭统计
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;-- 查看执行计划
SET SHOWPLAN_ALL ON;
-- 执行查询...
SET SHOWPLAN_ALL OFF;-- 实际执行计划
SET STATISTICS PROFILE ON;
-- 执行查询...
SET STATISTICS PROFILE OFF;

性能监控查询

-- 查找资源消耗最高的查询
SELECT TOP 20qs.execution_count,qs.total_logical_reads / qs.execution_count AS avg_logical_reads,qs.total_logical_writes / qs.execution_count AS avg_logical_writes,qs.total_worker_time / 1000 / qs.execution_count AS avg_cpu_time_ms,qs.total_elapsed_time / 1000 / qs.execution_count AS avg_elapsed_time_ms,qs.total_rows / qs.execution_count AS avg_rows,qs.last_execution_time,SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;-- 查找最消耗IO的查询
SELECT TOP 20qs.execution_count,qs.total_logical_reads,qs.total_logical_writes,qs.total_physical_reads,qs.total_logical_reads + qs.total_logical_writes AS total_io,(qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS avg_io,SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_io DESC;-- 查找编译时间最长的查询
SELECT TOP 20qs.execution_count,qs.plan_generation_num,qs.total_elapsed_time / 1000 AS total_elapsed_time_ms,qs.total_worker_time / 1000 AS total_worker_time_ms,SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS query_text,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.plan_generation_num DESC;

等待统计分析

-- 查看等待统计
SELECT TOP 20wait_type,wait_time_ms,max_wait_time_ms,signal_wait_time_ms,waiting_tasks_count,wait_time_ms / waiting_tasks_count AS avg_wait_time_ms,(wait_time_ms - signal_wait_time_ms) / waiting_tasks_count AS avg_resource_wait_ms,signal_wait_time_ms / waiting_tasks_count AS avg_signal_wait_ms,wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS percentage
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK','SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT','BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;-- 清空等待统计
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);-- 当前等待的会话
SELECT s.session_id,r.wait_type,r.wait_time,r.wait_resource,r.blocking_session_id,s.login_name,s.host_name,s.program_name,t.text AS current_query
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type IS NOT NULL
ORDER BY r.wait_time DESC;

阻塞和死锁分析

-- 查看当前阻塞
WITH BlockingHierarchy AS (-- 阻塞链的根SELECT s.session_id,s.login_name,r.wait_type,r.wait_time,r.wait_resource,r.blocking_session_id,t.text AS sql_text,0 AS LevelFROM sys.dm_exec_requests rINNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_idCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) tWHERE r.blocking_session_id = 0AND EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WHERE r2.blocking_session_id = r.session_id)UNION ALL-- 被阻塞的会话SELECT s.session_id,s.login_name,r.wait_type,r.wait_time,r.wait_resource,r.blocking_session_id,t.text AS sql_text,bh.Level + 1FROM sys.dm_exec_requests rINNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_idCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) tINNER JOIN BlockingHierarchy bh ON r.blocking_session_id = bh.session_id
)
SELECT REPLICATE('  ', Level) + CAST(session_id AS VARCHAR(10)) AS blocking_tree,session_id,blocking_session_id,login_name,wait_type,wait_time,wait_resource,sql_text
FROM BlockingHierarchy
ORDER BY Level, session_id;-- 死锁图跟踪（使用扩展事件）
CREATE EVENT SESSION DeadlockTracker ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename='C:\Logs\Deadlocks.xel')
WITH (STARTUP_STATE=ON);ALTER EVENT SESSION DeadlockTracker ON SERVER STATE = START;-- 查看死锁信息
SELECT event_data.value('(/event/@timestamp)[1]', 'datetime2') AS DeadlockTime,event_data.value('(/event/data[@name="xml_report"]/value)[1]', 'xml') AS DeadlockGraph
FROM (SELECT CAST(event_data AS XML) AS event_dataFROM sys.fn_xe_file_target_read_file('C:\Logs\Deadlocks*.xel', NULL, NULL, NULL)
) AS data;

资源监控

-- CPU使用率
SELECT record_id,DATEADD(ms, -1 * (ts_now - timestamp), GETDATE()) AS EventTime,SQLProcessUtilization,SystemIdle,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,timestamp,ms_ticks AS ts_nowFROM (SELECT timestamp, CONVERT(xml, record) AS record, ms_ticksFROM sys.dm_os_ring_buffersWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;-- 内存使用情况
SELECT type,SUM(pages_kb) / 1024 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_mb DESC;-- 缓冲池状态
SELECT CASE WHEN database_id = 32767 THEN 'ResourceDb'ELSE DB_NAME(database_id)END AS database_name,COUNT(*) * 8 / 1024 AS buffer_pool_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;-- 文件IO统计
SELECT DB_NAME(mf.database_id) AS database_name,mf.name AS logical_filename,mf.type_desc,divfs.num_of_reads,divfs.num_of_bytes_read / 1024 / 1024 AS read_mb,divfs.io_stall_read_ms,divfs.num_of_writes,divfs.num_of_bytes_written / 1024 / 1024 AS write_mb,divfs.io_stall_write_ms,divfs.io_stall / (divfs.num_of_reads + divfs.num_of_writes) AS avg_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) divfs
INNER JOIN sys.master_files mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id
ORDER BY avg_io_stall_ms DESC;

性能调优建议

-- 数据库引擎调优顾问脚本
-- 分析表扫描
SELECT OBJECT_NAME(ius.object_id) AS TableName,ius.user_scans,ius.user_seeks,ius.user_lookups,ius.user_updates,ius.user_scans * 100.0 / (ius.user_scans + ius.user_seeks + ius.user_lookups) AS scan_percentage
FROM sys.dm_db_index_usage_stats ius
WHERE ius.database_id = DB_ID()
AND ius.user_scans > 0
AND (ius.user_scans + ius.user_seeks + ius.user_lookups) > 100
ORDER BY scan_percentage DESC;-- 检查统计信息更新情况
SELECT OBJECT_NAME(s.object_id) AS TableName,s.name AS StatisticName,STATS_DATE(s.object_id, s.stats_id) AS last_updated,sp.modification_counter,sp.rows,sp.rows_sampled,CASE WHEN sp.modification_counter > sp.rows * 0.2 THEN '需要更新'WHEN STATS_DATE(s.object_id, s.stats_id) < DATEADD(DAY, -7, GETDATE()) THEN '可能需要更新'ELSE '正常'END AS status
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY sp.modification_counter DESC;-- 参数化查询建议
SELECT TOP 20qs.execution_count,qs.plan_generation_num,SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset)/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;-- 数据库配置优化建议
SELECT name,value_in_use,CASE nameWHEN 'max degree of parallelism' THEN CASE WHEN value_in_use = 0 THEN '建议设置为CPU核心数的一半'WHEN value_in_use > 8 THEN '建议不超过8'ELSE '配置合理'ENDWHEN 'cost threshold for parallelism' THENCASE WHEN value_in_use = 5 THEN '建议提高到25-50'ELSE '配置合理'ENDWHEN 'max server memory (MB)' THENCASE WHEN value_in_use = 2147483647 THEN '建议设置具体值，为操作系统保留内存'ELSE '配置合理'ENDEND AS recommendation
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism', 'max server memory (MB)');

---

21. 删除表和数据库

删除表

-- 删除单个表
DROP TABLE IF EXISTS TempEmployees;-- 删除多个表（需要考虑外键依赖）
DROP TABLE IF EXISTS EmployeeProjects;
DROP TABLE IF EXISTS Projects;-- 安全删除表（检查依赖关系）
DECLARE @TableName NVARCHAR(128) = 'Employees';-- 检查外键依赖
SELECT OBJECT_NAME(f.parent_object_id) AS DependentTable,f.name AS ForeignKeyName,OBJECT_NAME(f.referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys f
WHERE OBJECT_NAME(f.referenced_object_id) = @TableName;-- 如果有依赖，先删除外键
ALTER TABLE EmployeeProjects DROP CONSTRAINT FK_EmployeeProjects_Employees;
ALTER TABLE SalaryHistory DROP CONSTRAINT FK_SalaryHistory_Employees;-- 然后删除表
DROP TABLE Employees;-- 删除表的同时保留数据结构
SELECT * INTO Employees_Backup FROM Employees WHERE 1=0;  -- 只复制结构
DROP TABLE Employees;

清空表数据

-- DELETE删除所有数据（可以回滚，记录日志）
DELETE FROM TempTable;-- TRUNCATE删除所有数据（速度快，不能回滚）
TRUNCATE TABLE TempTable;-- 条件删除
DELETE FROM Employees WHERE IsActive = 0;-- 批量删除（避免长时间锁表）
DECLARE @BatchSize INT = 1000;
DECLARE @RowsDeleted INT = @BatchSize;WHILE @RowsDeleted = @BatchSize
BEGINDELETE TOP (@BatchSize) FROM LargeTable WHERE CreatedDate < DATEADD(YEAR, -5, GETDATE());SET @RowsDeleted = @@ROWCOUNT;-- 避免阻塞其他操作WAITFOR DELAY '00:00:01';
END;

删除数据库对象

-- 删除视图
DROP VIEW IF EXISTS vw_EmployeeDepartmentInfo;-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_GetEmployeesByDepartment;-- 删除函数
DROP FUNCTION IF EXISTS fn_GetFullName;-- 删除触发器
DROP TRIGGER IF EXISTS trg_Employees_AfterUpdate;-- 删除索引
DROP INDEX IF EXISTS IX_Employees_LastName ON Employees;-- 删除约束
ALTER TABLE Employees DROP CONSTRAINT IF EXISTS CK_Employees_Salary;
ALTER TABLE Employees DROP CONSTRAINT IF EXISTS FK_Employees_Departments;-- 删除默认约束
DECLARE @ConstraintName NVARCHAR(128);
SELECT @ConstraintName = d.name
FROM sys.default_constraints d
INNER JOIN sys.columns c ON d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE OBJECT_NAME(d.parent_object_id) = 'Employees' AND c.name = 'IsActive';IF @ConstraintName IS NOT NULLEXEC('ALTER TABLE Employees DROP CONSTRAINT ' + @ConstraintName);

删除数据库

-- 安全删除数据库
-- 1. 设置为单用户模式，断开所有连接
ALTER DATABASE CompanyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;-- 2. 删除数据库
DROP DATABASE CompanyDB;-- 或者使用脚本检查连接
DECLARE @DatabaseName NVARCHAR(128) = 'CompanyDB';-- 检查是否有活动连接
IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE database_id = DB_ID(@DatabaseName)AND session_id != @@SPID
)
BEGINPRINT '数据库仍有活动连接，正在断开...';-- 断开所有连接DECLARE @SQL NVARCHAR(MAX) = 'ALTER DATABASE [' + @DatabaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE';EXEC sp_executesql @SQL;
END-- 删除数据库
SET @SQL = 'DROP DATABASE [' + @DatabaseName + ']';
EXEC sp_executesql @SQL;PRINT '数据库 ' + @DatabaseName + ' 已成功删除';

批量删除脚本

-- 删除所有用户表的脚本
DECLARE @SQL NVARCHAR(MAX) = '';-- 先删除外键约束
SELECT @SQL = @SQL + 'ALTER TABLE [' + s.name + '].[' + t.name + '] DROP CONSTRAINT [' + fk.name + '];' + CHAR(13)
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id;-- 再删除表
SELECT @SQL = @SQL + 'DROP TABLE [' + s.name + '].[' + t.name + '];' + CHAR(13)
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id;PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- 取消注释以执行-- 删除所有存储过程
SET @SQL = '';
SELECT @SQL = @SQL + 'DROP PROCEDURE [' + s.name + '].[' + p.name + '];' + CHAR(13)
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id;PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- 取消注释以执行-- 删除所有视图
SET @SQL = '';
SELECT @SQL = @SQL + 'DROP VIEW [' + s.name + '].[' + v.name + '];' + CHAR(13)
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id;PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- 取消注释以执行-- 删除所有函数
SET @SQL = '';
SELECT @SQL = @SQL + 'DROP FUNCTION [' + s.name + '].[' + o.name + '];' + CHAR(13)
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type IN ('FN', 'IF', 'TF');PRINT @SQL;
-- EXEC sp_executesql @SQL;  -- 取消注释以执行

清理和维护

-- 清理系统数据
-- 清理作业历史
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '2023-01-01';-- 清理邮件日志
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = '2023-01-01';-- 清理错误日志
EXEC xp_readerrorlog 0, 1, N'备份', N'成功';  -- 查看备份相关日志-- 收缩数据库（谨慎使用）
DBCC SHRINKDATABASE(CompanyDB, 10);  -- 收缩到10%空闲空间-- 重新组织数据库文件
-- 1. 备份数据库
BACKUP DATABASE CompanyDB TO DISK = 'C:\Backup\CompanyDB_BeforeShrink.bak';-- 2. 收缩日志文件
BACKUP LOG CompanyDB TO DISK = 'C:\Backup\CompanyDB_Log.trn';
DBCC SHRINKFILE('CompanyDB_Log', 1);-- 3. 重建所有索引
ALTER INDEX ALL ON Employees REBUILD;-- 更新所有统计信息
EXEC sp_updatestats;-- 检查数据库一致性
DBCC CHECKDB(CompanyDB) WITH NO_INFOMSGS;PRINT '数据库清理和维护完成!';

---

总结

本指南涵盖了SQL Server数据库操作的各个方面，从基础的数据库创建到高级的性能优化。主要知识点包括：

核心概念

• 数据库和表结构设计：合理的数据类型选择、约束设计、关系建模
• CRUD操作：数据的增删改查，包括复杂的多表操作
• 索引优化：聚集索引、非聚集索引、索引维护策略

高级特性

• 视图、存储过程、函数：封装业务逻辑，提高代码复用性
• 触发器：实现自动化业务规则和审计
• 事务处理：确保数据一致性和完整性

管理和运维

• 安全性管理：用户权限、行级安全、数据加密
• 备份恢复：数据保护和灾难恢复策略
• 性能监控：识别瓶颈、优化查询性能

最佳实践

1. 设计阶段：规范化设计、合理的数据类型、适当的约束
2. 开发阶段：使用参数化查询、适当的索引、优化的SQL语句
3. 运维阶段：定期备份、监控性能、维护索引、更新统计信息

---