1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 企业级正规建库脚本
CREATE DATABASE CorpERP
ON PRIMARY 
(
    NAME = 'CorpERP_Data',               -- 逻辑名
    FILENAME = 'D:\SQLData\CorpERP.mdf', -- 物理文件全路径
    SIZE = 100MB,                        -- 初始大小
    MAXSIZE = 500GB,                     -- 最大限制（防止写满整盘）
    FILEGROWTH = 10%                     -- 自动扩容步长参数
)
LOG ON 
(
    NAME = 'CorpERP_Log',
    FILENAME = 'E:\ SQLLogs\CorpERP_Log.ldf', -- 建议日志单独放到高速读写的纯净磁盘（如 SSD）
    SIZE = 50MB,
    MAXSIZE = 200GB,
    FILEGROWTH = 50MB                    -- 日志暴涨时建议不要按百分比算，按固定兆数扩张减少碎片
);
GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
USE CorpERP;
GO

-- 1. 创建部门主表
CREATE TABLE Department (
    -- [1] 主键约束 (PRIMARY KEY): 唯一 + 绝不为空 + 默认生成聚集物理索引树
    -- [2] 标识/自增属性 (IDENTITY(起始值, 步长)): 让引擎自己接管分配独立ID
    DeptId INT IDENTITY(1,1) PRIMARY KEY, 
    -- [3] 唯一键约束 (UNIQUE): 保证全表部门名不可重复，且自带一个唯一的非聚集索引
    DeptName NVARCHAR(50) NOT NULL UNIQUE,
    ManagerName NVARCHAR(50) NULL
);

-- 2. 创建高度约束的员工表
CREATE TABLE Employee (
    EmpId BIGINT IDENTITY(1000, 1) PRIMARY KEY, -- 大企业建议用 BIGINT 防止越界
    EmpName NVARCHAR(50) NOT NULL,
    
    -- [4] 检查约束 (CHECK): 由引擎在插入/修改时把控取值范围逻辑，杜绝脏数据
    Age TINYINT CHECK (Age >= 18 AND Age <= 65),  
    
    -- [5] 默认值约束 (DEFAULT): 插入时如果不给此列赋值，则启用默认配置
    Gender CHAR(2) DEFAULT '男', 
    Salary DECIMAL(18, 2) NOT NULL, -- 金额：总长度18位，包含小数点后2位精确
    
    JoinDate DATETIME2 DEFAULT CURRENT_TIMESTAMP, -- DATETIME2 精度比 DATETIME 要更高
    IsDeleted BIT DEFAULT 0, -- 【软删除标记】BIT = 相当于布尔值 True/False
    
    DeptId INT NOT NULL, 

    -- [6] 外键约束 (FOREIGN KEY): 数据血统关系最强硬的防线！
    -- 如果有员工还挂着这个 DeptId，那么在部门表是绝对无法直接 Delete 删掉那个部门的。
    CONSTRAINT FK_Employee_Department 
        FOREIGN KEY (DeptId) 
        REFERENCES Department(DeptId)
        -- ON DELETE CASCADE -- 可选级联操作：如果填了这个，部门一旦被删，旗下所有关联员工被系统瞬间全部同时除名。
);
GO

1
2
3
4
5
6
7
8
-- 比如：忘了加某列，现在在线无感增加
ALTER TABLE Employee ADD Email VARCHAR(100) NULL;

-- 比如：给某列加上 CHECK 约束
ALTER TABLE Employee ADD CONSTRAINT CK_Employee_Salary CHECK (Salary >= 3000.00);

-- 清理外键关联后，强制删除全表结构
-- DROP TABLE Employee;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 形态A：单条 / 极简多条批量插入 (SQL 2008 之后支持)
INSERT INTO Department (DeptName, ManagerName)
VALUES 
('研发核心部', 'Tony'),
('财务风控部', 'Amy'),
('市场部', 'Bob');

-- 形态B：不指定特定列，按表字段顺序强行插入。如果表加列该写法必报错 (极不推荐！)
-- INSERT INTO Department VALUES ('行政部', 'Lily');

-- 形态C：查出某个表，并塞进一个已存在的结构同样的表 (INSERT INTO ... SELECT)
INSERT INTO EmployeeBackup (EmpName, Salary, DeptId)
SELECT EmpName, Salary, DeptId FROM Employee WHERE Age > 50;

-- 形态D：凭空创造新表并塞满数据 (SELECT ... INTO)。
-- 这种写法超级快，常用于制作一次性的数据报表镜像或跨库拉取日志：
SELECT * INTO Employee_Mirror_2025 
FROM Employee 
WHERE IsDeleted = 0;

1
2
3
4
5
6
7
8
9
-- 条件删除 (慢，按日志逐行打标记伪删除然后物理覆盖)
DELETE FROM Employee WHERE EmpId = 1005 AND IsDeleted = 1;

-- 【面试常考】TRUNCATE 与 DELETE 对于全表清除的区别：
-- 1. Truncate 极其暴力，不写单独回滚事务日志，只释放数据页，瞬间清空几亿条数据。
-- 2. Truncate 不需要加上 WHERE。
-- 3. Truncate 会将 IDENTITY 自增标识的种子无情重置为 1 ! 
-- 4. 如果这张表被别人用来做外键参考了，由于引擎为了保护引用完整性，直接禁止你执行 Truncate。
-- TRUNCATE TABLE Employee_Mirror_2025; 

1
2
3
4
5
6
7
8
9
10
11
-- 普通的定向涨薪
UPDATE Employee 
SET Salary = Salary * 1.05
WHERE DeptId = 1 AND Age > 30;

-- 【高阶联表更新】: 只通过另一张发来的核算表，去更新本体的薪资
UPDATE e
SET e.Salary = temp.NewSalary
FROM Employee AS e
INNER JOIN TempPayRaiseTable AS temp ON e.EmpId = temp.TargetEmpId
WHERE e.IsDeleted = 0;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- AS 取别名：方便前端接收。如果不想要原名，可以用 '' 包裹。
SELECT EmpName AS '姓名', Salary AS '月薪' FROM Employee;

-- 强大的条件匹配与运算
SELECT * FROM Employee 
WHERE Age BETWEEN 25 AND 35             -- 【范围】等同于 >=25 AND <=35
  AND DeptId IN (1, 3, 5)               -- 【集合】只取这个数组里面的
  AND EmpName LIKE '张_%'                 -- 【模糊】姓张，且名字至少两个字（_代表必然有一个单字符），%代表后面的都无所谓。
  AND ManagerName IS NOT NULL;          -- 【NULL处理】SQL里比对 NULL 绝对不能用 = 号！只能用 IS

-- 排序组合分页与顶尖截取
-- 获取薪水排名前 10 的活人，如果在平薪的情况下再按照更年轻的进行排序
SELECT TOP 10 * FROM Employee 
WHERE IsDeleted = 0
ORDER BY Salary DESC, Age ASC;

-- 【SQL 2012+ 分页新主宰：OFFSET FETCH】
-- 查询出第 3 页的数据（假设每页 20 条），它完全替代了以前丑陋的 ROW_NUMBER() 分页法
SELECT * FROM Employee
ORDER BY EmpId 
OFFSET 40 ROWS           -- 跳过前 40 行 (即抛弃第 1 到 2 页)
FETCH NEXT 20 ROWS ONLY; -- 再拿出紧接着的 20 行数据 (即这才是第 3 页)

1
2
3
4
5
6
7
8
9
10
11
12
-- 统计各个部门各种性别在职的人头数、平均薪水、以及该组合下挑出的最高的那笔工资
SELECT 
    DeptId, 
    Gender,
    COUNT(1) AS 'TotalWorkers',   -- COUNT(1) 和 COUNT(*) 性能在现代编译器下基本无异
    AVG(Salary) AS 'AvgSalary',
    MAX(Salary) AS 'TopSalaryOutThere'
FROM Employee
WHERE IsDeleted = 0               -- 【第一层过滤】WHERE，它是发生在 GROUP BY 【聚类之前】 剔除没用的散落死数据的
GROUP BY DeptId, Gender
HAVING COUNT(1) >= 5              -- 【第二层过滤】HAVING，它必须写在【聚类之后】，用于对刚刚压缩算好的总数进行进一步的高级筛选！
ORDER BY TotalWorkers DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- A. 内连接 (INNER JOIN)：门当户对。只会挑选在两张表中靠 ON 都能双花匹配的那些完美的数据带出来。如果某员工没有部门，他就被剔除丢弃。
SELECT e.EmpName, d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptId = d.DeptId;

-- B. 左外连接 (LEFT JOIN / LEFT OUTER JOIN)：以和为贵，以左为尊。
-- 强制保留左表(Employee)里的每一行！哪怕右表(Department)里的那个ID刚好被删了，他左边的名字也照样出来，只是这行关于右边 DeptName 的格子会显示 NULL 补齐。
SELECT e.EmpName, ISNULL(d.DeptName, '暂无安排') AS '所属部门' 
FROM Employee e
LEFT JOIN Department d ON e.DeptId = d.DeptId;

-- C. 右外联 (RIGHT JOIN)：与 LEFT JOIN 同理，反向保右不保左。

-- D. 全外连接 (FULL OUTER JOIN)：极其罕见。不管你俩有没有配对上，所有的行孤儿和寡母全在这一张表里合体集结，没有的数据相互铺 NULL。

-- E. 交叉连接 (CROSS JOIN)：更罕见。笛卡尔积乘法。不要带 ON。如果表 A 有 1000 人，表 B 有 10 个部门，那最后由于连出来的结果是 1000*10 = 10000 行纯组排列的数据爆炸。

1
2
3
4
5
6
7
-- 需要知道哪些人赚的钱超过了财务部的平均工资
SELECT EmpName, Salary 
FROM Employee 
WHERE Salary > (
    SELECT AVG(Salary) FROM Employee 
    WHERE DeptId = (SELECT DeptId FROM Department WHERE DeptName = '财务部')
);

1
2
3
4
5
6
7
8
9
10
11
WITH FinanceInfo AS (
    SELECT DeptId FROM Department WHERE DeptName = '财务部'
),
AvgSalTable AS (
    SELECT AVG(Salary) AS FinanceAvg FROM Employee 
    WHERE DeptId = (SELECT DeptId FROM FinanceInfo)
)
SELECT e.EmpName, e.Salary, a.FinanceAvg
FROM Employee e
CROSS JOIN AvgSalTable a
WHERE e.Salary > a.FinanceAvg;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查询所有人原本的薪水，并额外在每行的末尾，算出一个该行所处在本部门群体内部的前后大榜排名：
-- 这如果用老语法做可能要成百上千行的极其变态的临时表。
SELECT 
    EmpId, 
    EmpName, 
    DeptId, 
    Salary,
    -- ROW_NUMBER()：排名函数。
    -- PARTITION BY DeptId 说的是针对部门切分开来算，ORDER BY 说的是根据薪水降序。
    ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY Salary DESC) AS '本部门内薪资排名',
    
    -- 额外在这附赠一个该组的所有人平均值，当做一个普通的列平铺在旁边作为对比！
    AVG(Salary) OVER(PARTITION BY DeptId) AS '部门平均标杆'
FROM Employee
WHERE IsDeleted = 0;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE @TargetSalary INT =ROUND(RAND() * 20000, 0); -- 产生个随机薪资用来玩
PRINT '生成的随即目标是：' + CAST(@TargetSalary AS VARCHAR);

IF @TargetSalary < 5000
    PRINT '这个数很小不影响大局。';
ELSE IF @TargetSalary BETWEEN 5000 AND 12000
BEGIN
    PRINT '落入中等核算区间。';
    -- 这里可以写很长的多行 UPDATE 语句等批量核算操作
END
ELSE
    PRINT '超额阈值预警！';

-- ========== CASE WHEN ... THEN ... END : 分流数据改造王者 ==========
-- 它属于查询改造级的高手。用于在一行内部，直接对输出呈现的东西做条件翻转翻译变形。
SELECT EmpName, Salary,
    CASE 
        WHEN Salary >= 20000 THEN '企业高管'
        WHEN Salary >= 10000 AND Salary < 20000 THEN '中层骨干'
        WHEN Salary < 10000 THEN '基层建设者'
        ELSE '统计异常' -- 如果连上面的条件都没拦得住的最终安全垫
    END AS '薪资地位等级'
FROM Employee;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN TRY
    -- 声明并插入一个已经存在的名字导致主键约束大崩溃...
    INSERT INTO Department (DeptId, DeptName) VALUES (1, '违规植入');
END TRY
BEGIN CATCH
    -- 所有关于这次引擎报错的核心参数都在这这这
    PRINT '出现预期外的极度严重操作异常...';
    PRINT '错误级别 (Severity): ' + CAST(ERROR_SEVERITY() AS VARCHAR);
    PRINT '错误代号 (Number): '   + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT '错误所在的行数 (Line): ' + CAST(ERROR_LINE() AS VARCHAR);
    PRINT '原汁原味的引擎错误红字文本: ' + ERROR_MESSAGE();
    
    -- 下一步，你通常可以将这些详情强行偷偷再 INSERT 入你们公司的专用日志查错临时表
END CATCH

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 定义几个临时承载内存数据的杯子
DECLARE @CurrentEmpId BIGINT;
DECLARE @CurrentSalary DECIMAL(18,2);

-- 1. 【宣告游标】告诉数据库：给我划出一片内存专门给我的遍历使用
DECLARE emp_cursor CURSOR FOR 
    SELECT EmpId, Salary FROM Employee WHERE IsDeleted = 0; 

-- 2. 【开启游标】正式在内存生成指向第一行头顶的一个虚拟只读指针
OPEN emp_cursor;

-- 3. 【提线推进】拉伸第一次：取出实际数据倾倒放进我们刚才声明好的杯子里准备计算
FETCH NEXT FROM emp_cursor INTO @CurrentEmpId, @CurrentSalary;

-- 4. 【开启死亡大循环】
-- 全局的状态变量 @@FETCH_STATUS 只要返回 0，意思就是：不仅刚才那句 FETCH 取到了货而且这底下还有活儿没干完
WHILE @@FETCH_STATUS = 0 
BEGIN
    -- 【在这写你的单行究极复杂的业务】
    PRINT '当前扫描到 ID = ' + CAST(@CurrentEmpId AS VARCHAR) + ' 该兄弟工资有：' + CAST(@CurrentSalary AS VARCHAR);
    
    -- ... 可以去别的什么遥远的系统拉取什么特殊的发票配置信息
    -- ... IF 一下
    -- 修改这单行特定的数据
    
    -- 【最重要的：提线向前再走一格】！如果忘了这句，你就是死循环爆服！
    FETCH NEXT FROM emp_cursor INTO @CurrentEmpId, @CurrentSalary;
END

-- 5. 【极其严重警告：必须扫尾关闭及释放】如果不执行这句不仅内存爆光整个锁全不放
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- CREATE VIEW：给这个虚假的表定制造型组合
CREATE VIEW v_SeniorEmployeesWithDept
AS
SELECT 
    e.EmpId, e.EmpName, e.Salary,
    d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptId = d.DeptId
WHERE e.Age > 40 AND e.IsDeleted = 0;
GO

-- 前端或是其他开发人员使用时，极其整洁。
-- 绝大多数情况下，不要去试图对视图底层执行 INSERT、UPDATE，哪怕有些特殊配置允许这样做。它只是一种用来查询的数据隔离防盗门。
SELECT * FROM v_SeniorEmployeesWithDept;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 标量函数范例：输入入职日期，给你吐出现实算出的“工龄”数字
CREATE FUNCTION fn_CalculateWorkYears (@JoinDate DATETIME)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @JoinDate, GETDATE());
END;
GO

-- 外部愉快调用: (在调用标量时引擎有个死规矩: 一般都得加上系统所属名 dbo.)
SELECT EmpName, dbo.fn_CalculateWorkYears(JoinDate) AS 'WorkYears' FROM Employee;

-------------------------------------------------------------------------------------------------

-- 内联表值函数 (TVF) 范例：接收一个薪资要求，只找出超过薪资的完整多列集合结构返回
CREATE FUNCTION fn_GetHighPaidWorkers (@Threshold DECIMAL(18,2))
RETURNS TABLE
AS
RETURN 
(
    SELECT EmpId, EmpName, Salary 
    FROM Employee 
    WHERE Salary >= @Threshold AND IsDeleted = 0
);
GO

-- 外部把它当做一个需要带进去门票的高端虚表来用:
SELECT * FROM fn_GetHighPaidWorkers(20000.00); 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 我们来写一个标准的带全套操作特性的高阶存储过程
CREATE PROCEDURE sp_HandleEmployeeBonus
    @InDeptId INT,                        -- 接收普通的传递进来的参数
    @InBaseBonus DECIMAL(18,2),
    @OutTotalGrantedBonus DECIMAL(18,2) OUTPUT -- 输出型特殊参数（做完事之后带出给调用方读取的）
AS
BEGIN
    SET NOCOUNT ON; -- 切忌别漏了！让引擎别在那浪费网速在那傻发"(影响 1 行)" 这种极其占用 IO 流量返回无用信息包了。

    -- 初始化输出总金额计数
    SET @OutTotalGrantedBonus = 0;

    BEGIN TRY
        -- 进行逻辑筛选，没有必要进入修改循环了就快速阻断。
        IF NOT EXISTS(SELECT 1 FROM Employee WHERE DeptId = @InDeptId AND IsDeleted = 0)
        BEGIN
            PRINT '该部门一个活人都没有，终止发奖！';
            RETURN;
        END
        
        -- 计算总奖金额给外围
        SELECT @OutTotalGrantedBonus = SUM(Salary * 0.1M + @InBaseBonus) 
        FROM Employee WHERE DeptId = @InDeptId;

        -- 执行批量的真切物理修改！
        UPDATE Employee 
        SET Salary = Salary + (Salary * 0.1M + @InBaseBonus)
        WHERE DeptId = @InDeptId AND IsDeleted = 0;
        
        PRINT '存储过程执行完成发薪完毕';
    END TRY
    BEGIN CATCH
        -- 【此处本应写更高级别的回滚报错收集代码，请参考后续事务篇章】
        PRINT '执行严重失败! ' + ERROR_MESSAGE();
        SET @OutTotalGrantedBonus = -1;
    END CATCH
END;
GO

-- ================= C# 或客户端调用它的模拟过程 ==================
DECLARE @ResultTotalBonus DECIMAL(18,2); -- 建一个参数准备接收

-- EXEC 指令挂接大名然后传入匹配字典名称
EXEC sp_HandleEmployeeBonus 
    @InDeptId = 1, 
    @InBaseBonus = 500.00, 
    @OutTotalGrantedBonus = @ResultTotalBonus OUTPUT; -- 这里这个特性的对应不能忘写 OUTPUT

SELECT '本次发放到账总数计算报告为：' + CAST(@ResultTotalBonus AS VARCHAR);

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 核心使用自带专杀拼接的系统子过程： sp_executesql （千万别用直接的 EXEC(@string) 因为没有执行防注入化）
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(50) = 'Employee'; -- 甚至连表名都是用户界面传进来的字符串
DECLARE @SearchName NVARCHAR(50) = 'Tony';

-- 极度惊心动魄地利用变量凑出了这段纯文本 T-SQL 字符串
SET @DynamicSQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE EmpName = @NameParam';

-- 直接通过系统底层命令运行这行字符串，并且安全地把内嵌参数的引用抛过去建立防注入防弹衣！
EXEC sp_executesql 
    @stmt = @DynamicSQL, 
    @params = N'@NameParam NVARCHAR(50)', 
    @NameParam = @SearchName;  -- 给那个抛过去的参数填充真的子弹内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE PROCEDURE sp_SecureMoneyTransfer
    @FromAcc INT,
    @ToAcc INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 显性宣告：锁控护卫系统启动 (从这一秒开始执行的修改在没有走到底下提交时都是悬在锁里锁死的，其他人想读这些行的内容统统被阻挡冻结在门外排队！)
    BEGIN TRANSACTION;

    BEGIN TRY
        -- 判断 A 的钱够不够
        IF (SELECT Balance FROM Accounts WHERE Id = @FromAcc) < @Amount
            THROW 50001, '余额绝对不够啦', 1; -- THROW 是现代 T-SQL 版直接把程序砸懵强制把代码跳转扔进下方 CATCH 的抛砖神技
            
        -- A 扣款
        UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromAcc;
        -- B 入账 (此时突然 B 账户由于某人手残加的外键约束问题直接爆掉了红色异常崩溃报错了！！)
        UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToAcc;

        -- 如果天下太平没出什么幺蛾子，那么恭喜全部放行写入物理介质真正提交完本！
        COMMIT TRANSACTION;
        PRINT '转账全链路成功结束。';
    END TRY
    BEGIN CATCH
        -- 【这是事务中最核心保护的一课】刚才如果 B 账户代码由于引擎级错误直接崩溃到了这里被你兜住，之前那一步被扣了钱的 A 的可怜的钱怎么救回来？
        
        -- @@TRANCOUNT 这个全局变量如果大于 0 意味着 “当前身上还挂着个已经开始但没收摊的锁包”
        IF @@TRANCOUNT > 0
        BEGIN
            -- 无情撤回：时间倒流，数据库所有之前被锁死的这批修改全部完美复原回它什么事都没发生之前的最初的样子。并将加锁阻塞池清空释放其他人。
            ROLLBACK TRANSACTION; 
        END
        
        PRINT '极度危机！事务已经熔断回滚。具体错误报告：' + ERROR_MESSAGE();
    END CATCH
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TRIGGER trg_EmployeeDeleteAudit
ON Employee
AFTER DELETE -- 表明只有 Delete 指令发出确认并干掉行后，我狗子立刻就会激活扑出来
AS
BEGIN
    -- 【魔法天书系统表】：inserted (存放修改后的新数据或者刚进来的新数据)， deleted (存放已被覆盖的老残数据或者刚被删去的数据)。这两个表是在执行当瞬内存中极其短暂存在的！！
    
    -- 本触发器监视的明明就是把几十个离职人员强制从数据库干除这个命令！所以引擎给我的魔法表 deleted 中这会装满了那整整被杀的五十个人员信息。
    
    -- 趁还没来得及消失的零点几秒，在后门利用这个删留下的亡语把所有的尸体信息悄悄塞给归档备忘录表
    INSERT INTO Employee_DismissedArchive (EmpId, Name, SalaryWas, DeleteTime)
    SELECT
        EmpId,
        EmpName,
        Salary,
        GETDATE()     -- 利用这种关联式神速复制技巧进行暗中的记录
    FROM deleted;
    
    PRINT '离职人员清理监控：底层数据库核心档案移交已通过无形触发器执行完成。';
    -- 请注意如果内部这种日志移交业务本身报错了，刚才的那批在界面发生的前端删除动作也会因发生全连锁雪崩全盘倒退被强制撤回。这就是同属一条底层锁血命的好处和拖累坏处所在。
END;

1
2
3
4
5
6
7
8
9
10
-- 建树！将极大提高所有带 WHERE Email = '?' 及 ORDER BY Email 结尾相关操作的十万倍性能
CREATE NONCLUSTERED INDEX IX_Employee_EmailSearchLookup 
ON Employee (Email ASC);

-- 再建树！！如果既搜邮箱又搜部门
CREATE NONCLUSTERED INDEX IX_Employee_MailWithDeptCombiner
ON Employee (Email ASC, DeptId DESC);

-- 这句话查询立马变得顺滑：
-- SELECT * FROM Employee WHERE Email = 'jack@exp.net' AND DeptId = 5;

1
2
3
4
5
6
7
8
9
-- 把这招砸向上面刚才建小树的操作进行覆写 (WITH DROP_EXISTING 高级重建)
CREATE NONCLUSTERED INDEX IX_Employee_EmailSearchLookup
ON Employee (Email ASC)
INCLUDE (Phone, Status) -- 注意！不要包含几百个字段的大肉块。这非常增加平时插入数据的拖慢开销（因为增删需要时间更新你的这本超重的日记）
WITH (DROP_EXISTING = ON);

-- 从此以后如下这行极为常见高发频的接口底层查询，因为他不仅通过 Seek 闪电找到了邮件定位区间，所有顺带要求的这俩参数也就在它挂件上一起存在直接带回：
-- 这次他这查询再也不会发生任何一次 Key Lookup 原表提取物理抽拉拖累：性能达到了全网理论封顶极限水平！
-- SELECT Phone, Status FROM Employee WHERE Email = 'jack@exp.net';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 测试用：创建一个自己引用自己的员工上下级树表
-- CREATE TABLE OrgChart (EmpId INT, EmpName NVARCHAR(50), BossId INT NULL);
-- INSERT INTO OrgChart VALUES (1, '大老板', NULL), (2, '销售总监', 1), (3, '研发总监', 1), (4, '销售经理A', 2), (5, '前端程序员', 3);

-- 使用递归 CTE 拉出包含树层级和全部从属路线的高级结果集
WITH RecursiveOrgCTE AS 
(
    -- 1. [锚点成员] 定位起点：也就是找到所有根本没有上司的顶级大老板作为第 1 级
    SELECT EmpId, EmpName, BossId, 1 AS TierLevel, CAST(EmpName AS NVARCHAR(MAX)) AS 'Chain'
    FROM OrgChart 
    WHERE BossId IS NULL

    UNION ALL 

    -- 2. [递归成员] 不断自我循环：拿着上面的结果 CTE 的表名 (RecursiveOrgCTE) 去内部连表！
    SELECT o.EmpId, o.EmpName, o.BossId, 
           cte.TierLevel + 1 AS TierLevel, -- 层级逐级递增
           cte.Chain + ' -> ' + o.EmpName AS 'Chain' -- 把名字像珠子一样顺势串起来
    FROM OrgChart o
    INNER JOIN RecursiveOrgCTE cte ON o.BossId = cte.EmpId -- 将员工的 BossId 指向上一步拿出来的老大的 EmpId
)
SELECT * FROM RecursiveOrgCTE
ORDER BY TierLevel ASC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 原始表大概长这样： 销售员(Name) | 月份(Month) | 业绩(Amount) [是竖向无穷多行的]
-- 希望输出长这样：  | Name | 1月 | 2月 | 3月 | 4月 |

SELECT * FROM 
(
    SELECT Name, Month, Amount 
    FROM SalesRecord 
    WHERE Year = 2025
) AS SourceTable -- 原片基础提取集
PIVOT 
(
    -- PIVOT 魔术引擎启动：把所有相同的 Month 下的 Amount 进行挤压求和
    SUM(Amount) 
    -- 强行把原本作为内容存在的 [1月, 2月] 数据值变成了真正的列名字母！
    FOR Month IN ([1月], [2月], [3月], [4月], [5月], [6月]) 
) AS PivotTable;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 获取每个部门下，工资排名稳居前两名（Top 2）的绝顶高手的资料，并拼接在一起。
-- 这个用单纯的 JOIN 是极其难写的，有了 APPLY 以及传递参数就变得极为简单：

SELECT d.DeptName, TopEmp.EmpName, TopEmp.Salary
FROM Department d
CROSS APPLY 
(
    -- 每从外部读取到一条 Department 部门信息，执行器都会携带它的 d.DeptId 钻进这个子盒子里执行一遍这个拿 Top 2 的独立运算！
    SELECT TOP 2 EmpName, Salary 
    FROM Employee e 
    WHERE e.DeptId = d.DeptId AND e.IsDeleted = 0
    ORDER BY Salary DESC
) AS TopEmp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 常用于定时同步任务，将昨天的销售新流水增量集合（Source）合并贴死到主库汇总表（Target）里
MERGE INTO MainSummaryTable AS target
USING DailyImportTable AS source 
    ON target.UniqueId = source.UniqueId
WHEN MATCHED THEN
    -- 如果找到了两边共有的身份证 ID，就做定向刷新
    UPDATE SET target.TotalSales = target.TotalSales + source.LastSales,
               target.LastUpdate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN 
    -- 目标主机上根本没这个人，判定为纯全新增加新员工
    INSERT (UniqueId, TotalSales, LastUpdate) 
    VALUES (source.UniqueId, source.LastSales, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    -- 甚至支持反向处理：如果是主库里有但新进表里反而消失了的人员（可能离职了），在此地强行打上死亡标记
    UPDATE SET target.IsActive = 0; 
    -- (注意：最后必须跟上一个英文半角分号)

1
2
3
4
5
6
7
8
9
10
-- 1. 表变量：轻盈小巧 (不用 DROP)
DECLARE @LocalIdList TABLE (Id INT PRIMARY KEY, Val NVARCHAR(20));
INSERT INTO @LocalIdList VALUES (1, 'A'), (2, 'B');

-- 2. 临时物理表：能抗下几十万数据大旗 (用完最佳习惯还是手工 DROP TABLE #HeavyDataTable 释放硬盘)
CREATE TABLE #HeavyDataTable (
    OrderNo VARCHAR(50) NOT NULL PRIMARY KEY INDEX IX_OrderTree, -- 直建树状索引！
    Amt DECIMAL(18,2)
);
INSERT INTO #HeavyDataTable SELECT OrderNo, Amount FROM OldHistory WHERE CreateY = 2025;

1
2
3
4
5
6
-- 强行穿刺读取那些正在被执行 UPDATE 锁着的数据项 (极其不安全，但极快无阻塞)
SELECT COUNT(1) FROM HeavyLogs WITH (NOLOCK) WHERE CreateDate > '2025-01-01';

-- 当天重新强制分析新传入的参数 @DeptId，废除曾经该存储过程缓存的全部老式查询路线规划
SELECT * FROM Employee WHERE DeptId = @DeptId 
OPTION (RECOMPILE);