在数据管理领域，T-SQL（Transact-SQL）是一种强大的数据库查询语言，它不仅支持SQL的核心功能，还增加了许多高级特性。以下将为您解析30个实用的T-SQL数据库案例，帮助您轻松掌握数据管理技巧。
案例一：创建数据库和表
CREATE DATABASE MyDatabase;
GO
USE MyDatabase;
GO
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(100)
);
GO
案例二：插入数据
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');
GO
案例三：查询数据
SELECT * FROM Employees;
GO
案例四：条件查询
SELECT * FROM Employees WHERE FirstName = 'John';
GO
案例五：使用别名
SELECT e.EmployeeID, e.FirstName, e.LastName, e.Email
FROM Employees AS e;
GO
案例六：聚合函数
SELECT COUNT(*) AS TotalEmployees FROM Employees;
GO
案例七：分组查询
SELECT COUNT(*) AS TotalEmployees, FirstName
FROM Employees
GROUP BY FirstName;
GO
案例八：HAVING子句
SELECT COUNT(*) AS TotalEmployees, FirstName
FROM Employees
GROUP BY FirstName
HAVING COUNT(*) > 1;
GO
案例九：连接查询
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees AS e
JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
GO
案例十：子查询
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE EmployeeID IN (SELECT ManagerID FROM Employees);
GO
案例十一：联合查询
SELECT EmployeeID, FirstName, LastName
FROM Employees
UNION
SELECT ManagerID, 'Manager', 'N/A'
FROM Employees;
GO
案例十二：交叉连接
SELECT e.EmployeeID, e.FirstName, d.DepartmentName
FROM Employees AS e
CROSS JOIN Departments AS d;
GO
案例十三：更新数据
UPDATE Employees
SET Email = 'new.email@example.com'
WHERE EmployeeID = 1;
GO
案例十四：删除数据
DELETE FROM Employees
WHERE EmployeeID = 1;
GO
案例十五：事务处理
BEGIN TRANSACTION;
UPDATE Employees
SET Email = 'new.email@example.com'
WHERE EmployeeID = 1;
UPDATE Departments
SET DepartmentName = 'New Department'
WHERE DepartmentID = 1;
COMMIT TRANSACTION;
GO
案例十六：触发器
CREATE TRIGGER trg_BeforeDeleteEmployee
ON Employees
INSTEAD OF DELETE
AS
BEGIN
RAISERROR ('Cannot delete employee', 16, 1);
END;
GO
案例十七：存储过程
CREATE PROCEDURE sp_GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees
WHERE EmployeeID = @EmployeeID;
END;
GO
案例十八：用户定义函数
CREATE FUNCTION fn_GetEmployeeName
(@EmployeeID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Name NVARCHAR(100);
SELECT @Name = FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID = @EmployeeID;
RETURN @Name;
END;
GO
案例十九：索引
CREATE INDEX idx_EmployeeID ON Employees (EmployeeID);
GO
案例二十：视图
CREATE VIEW vw_EmployeeDetails
AS
SELECT EmployeeID, FirstName, LastName, Email
FROM Employees;
GO
案例二十一：备份和还原
BACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase.bak';
GO
RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backup\MyDatabase.bak';
GO
案例二十二：分区表
CREATE PARTITION FUNCTION PartitionFunctionByEmployeeID (INT) AS RANGE LEFT FOR VALUES (100, 200, 300);
GO
CREATE PARTITION SCHEME PartitionSchemeByEmployeeID
AS PARTITION PartitionFunctionByEmployeeID
ALL TO ([PRIMARY]);
GO
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
...
) ON PartitionSchemeByEmployeeID (EmployeeID);
GO
案例二十三：全文搜索
CREATE FULLTEXT INDEX ON Employees (Email)
KEY INDEX PK_Employees;
GO
SELECT * FROM Employees
WHERE CONTAINS(Email, 'example.com');
GO
案例二十四：高可用性和灾难恢复
-- 创建数据库镜像
CREATE DATABASE MyDatabase_Mirror ON PRIMARY (
NAME = 'MyDatabase_Mirror_Data',
FILENAME = 'C:\Database\MyDatabase_Mirror_Data.mdf'
)
LOG ON (
NAME = 'MyDatabase_Mirror_Log',
FILENAME = 'C:\Database\MyDatabase_Mirror_Log.ldf'
);
-- 配置数据库镜像
ALTER DATABASE MyDatabase_Mirror SET PARTNER = 'C:\Database\MyDatabase_Mirror_Data.mdf';
GO
案例二十五：数据迁移
-- 使用SSIS包进行数据迁移
BULK INSERT Employees
FROM 'C:\Data\Employee.csv'
WITH (
CODEPAGE = 'ACP',
DATAFILETYPE = 'native',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
GO
案例二十六：性能监控
-- 使用SQL Server Profiler进行性能监控
案例二十七：数据加密
-- 使用透明数据加密（TDE）进行数据加密
ALTER DATABASE MyDatabase SET ENCRYPTION ON;
GO
案例二十八：数据压缩
-- 使用数据压缩进行性能优化
ALTER INDEX ALL ON Employees REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
案例二十九：分区视图
-- 使用分区视图进行查询优化
案例三十：使用动态SQL
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM Employees WHERE EmployeeID = ' + CAST(1 AS NVARCHAR(10));
EXEC sp_executesql @SQL;
GO
通过以上30个案例，您将能够更好地理解和掌握T-SQL数据库管理技巧。希望这些案例能够帮助您在实际工作中更加高效地处理数据。