数据库局部变量，全局变量，流程控制

前言知识点什么时候用局部变量调试脚本、存储过程参数、临时存值全局变量获取执行状态、错误处理IF/WHILE条件判断、批量数据处理视图简化复杂查询、统一查询逻辑索引加快查询速度函数封装可复用的计算逻辑存储过程封装复杂业务、批量操作一SQL局部变量变量1.是什么以开头是你自己在 SQL 脚本里定义的 “临时小盒子”只能在当前这一段 SQL 里用GO之后就失效了作用存一个值后面反复用避免到处写死数字 / 字符串2.三步使用语法-- ① 定义变量DECLARE 变量名 数据类型 DECLARE stuId INT; -- 存数字 DECLARE stuName NVARCHAR(20); -- 存中文/字符串 -- ② 给变量赋值两种方式 SET stuId 1; -- 方式1SET赋值推荐 SELECT stuName 张三; -- 方式2SELECT赋值 -- ③ 使用变量直接写在SQL里 SELECT * FROM Student WHERE id stuId AND name stuName;3.常用场景调试时把条件统一改成变量改一次全生效存储过程里接收参数把查询结果临时存起来给后面的语句用二SQL全局变量1.是什么以开头系统自带的变量不用你定义用来获取数据库的状态、执行信息直接就能用2.最常用的4个-- ① IDENTITY获取刚插入数据的自增ID INSERT INTO Student(name) VALUES(李四); DECLARE newId INT IDENTITY; -- 拿到刚插入的ID -- ② ROWCOUNT上一条SQL影响了多少行 UPDATE Student SET age20 WHERE id1; PRINT 更新了 CAST(ROWCOUNT AS NVARCHAR) 行; -- ③ ERROR上一条SQL的错误码0没出错 DELETE FROM Student WHERE id999; IF ERROR 0 PRINT 删除出错了; -- ④ VERSION查看SQL Server版本 SELECT VERSION;三流程控制IF/While1.是什么让SQL脚本 会思考会循环 像C#一样写逻辑2.IF条件判断IF (条件) BEGIN -- 条件成立时执行的代码多条语句必须加BEGIN...END PRINT 条件成立; END ELSE BEGIN PRINT 条件不成立; END示例根据分数判断是否及格DECLARE score INT 85; IF score 60 BEGIN PRINT 及格了; SELECT * FROM Student WHERE score score; END ELSE BEGIN PRINT 不及格需要补考; END3.While循环WHILE (循环条件) BEGIN -- 循环体每次执行的代码 -- 注意一定要有跳出条件不然会死循环 IF 条件满足 BREAK; -- 跳出循环 END示例批量删除旧日志分批删不锁表DECLARE batchSize INT 1000; -- 每次删1000条 WHILE 11 -- 死循环靠里面的BREAK退出 BEGIN -- 每次删1000条 DELETE TOP (batchSize) FROM Logs WHERE CreateTime 2023-01-01; -- 如果这次没删到数据说明删完了退出循环 IF ROWCOUNT 0 BREAK; END PRINT 删除完成;四视图1.是什么一张 “虚拟表”本质是一条存起来的SELECT语句每次查询视图就会自动执行这条 SQL索引视图是它的特殊形式会把结果物理存起来2.基础创建语法-- 普通视图 CREATE VIEW v_student_score AS SELECT s.id, s.name, sc.score FROM Student s JOIN Score sc ON s.id sc.stuId; -- 使用视图和普通表一样查 SELECT * FROM v_student_score WHERE score 90;3.索引视图-- 必须加WITH SCHEMABINDING且只能用INNER JOIN CREATE VIEW v_student_score_index WITH SCHEMABINDING AS SELECT s.id, s.name, sc.score FROM dbo.Student s INNER JOIN dbo.Score sc ON s.id sc.stuId; GO -- 创建聚集索引视图数据会物理存储 CREATE UNIQUE CLUSTERED INDEX idx_v ON v_student_score_index(id);五索引1.是什么给表加的 “目录”加快查询速度就像书本的目录不用逐页翻直接定位到内容2.最常用的索引语法1. 普通非聚集索引CREATE INDEX idx_stu_name ON Student(name);目的让按name查询变快适用场景SELECT * FROM Student WHERE name张三2. 唯一索引CREATE UNIQUE INDEX idx_stu_phone ON Student(phone);目的既让phone查询变快又强制phone字段的值不能重复适用场景手机号、身份证号这种不能重复的字段3. 复合索引CREATE INDEX idx_stu_age_name ON Student(age, name);目的按age先排序再按name排序优化多条件查询适用场景WHERE age22 AND name张三这种查询走复合索引比两个单字段索引快得多注意顺序很重要(age, name)只能优化age开头的条件反过来(name, age)就不行。六函数1.是什么封装好的一段 SQL 逻辑输入参数返回结果分为标量函数返回单个值和表值函数返回一张表2.标量函数示例返回单个值-- 创建一个函数根据分数返回等级 CREATE FUNCTION GetScoreLevel(score INT) RETURNS NVARCHAR(10) AS BEGIN DECLARE level NVARCHAR(10); IF score 90 SET level 优秀; ELSE IF score 60 SET level 及格; ELSE SET level 不及格; RETURN level; END; -- 使用函数 SELECT name, dbo.GetScoreLevel(score) AS level FROM Student;七存储过程1.是什么把多条 SQL 语句封装成一个 “方法”支持参数、流程控制调用一次就执行里面所有逻辑适合复杂业务、批量操作2.基础创建语法-- 创建存储过程根据学号查学生成绩 CREATE PROCEDURE GetStudentInfo stuId INT -- 输入参数 AS BEGIN -- 里面可以写多条SQL支持变量、IF、WHILE SELECT * FROM Student WHERE id stuId; SELECT * FROM Score WHERE stuId stuId; END; -- 调用存储过程 EXEC GetStudentInfo stuId 1;八触发器和游标1. 触发器Trigger核心特点触发时机BEFORE/AFTER操作前 / 操作后触发事件INSERT、UPDATE、DELETE自动执行无参数无返回值实战示例MySQL需求给用户表加一个自动记录修改日志的触发器-- 日志表 CREATE TABLE user_log( id INT PRIMARY KEY AUTO_INCREMENT, info VARCHAR(200), create_time DATETIME ); -- 用户表 CREATE TABLE user( id INT PRIMARY KEY, name VARCHAR(50) ); -- 创建触发器当user表新增数据时自动写日志 DELIMITER // -- 临时修改语句结束符 CREATE TRIGGER trg_user_insert AFTER INSERT ON user -- 插入后触发 FOR EACH ROW -- 每一行都触发 BEGIN INSERT INTO user_log(info, create_time) VALUES(CONCAT(新增用户, NEW.name), NOW()); END // DELIMITER ; -- 测试插入用户触发器自动执行 INSERT INTO user(id, name) VALUES(1, 张三);✅ 效果你只插了用户表日志表自动多了一条记录全程不用手动调用触发器。2. 游标Cursor核心特点用于循环遍历查询结果集逐行处理四步标准流程声明 → 打开 → 取数据 → 关闭缺点性能比整批 SQL 差尽量少用实战示例MySQL 存储过程 游标需求逐行读取用户表打印每个用户名DELIMITER // CREATE PROCEDURE proc_cursor_test() BEGIN -- 1. 声明变量 DECLARE done INT DEFAULT 0; DECLARE userName VARCHAR(50); -- 2. 声明游标 DECLARE user_cursor CURSOR FOR SELECT name FROM user; -- 3. 游标结束标记 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; -- 4. 打开游标 OPEN user_cursor; -- 5. 循环逐行读取 read_loop: LOOP FETCH user_cursor INTO userName; -- 取一行数据 IF done 1 THEN LEAVE read_loop; END IF; SELECT userName; -- 输出当前行逐行处理 END LOOP; -- 6. 关闭游标 CLOSE user_cursor; END // DELIMITER ; -- 调用 CALL proc_cursor_test();九事务1.是什么事务是数据库中的一个操作序列这些操作要么全部完成要么全部不做。换句话说事务是保证数据库数据一致性和完整性的单位。生活类比转账操作你从账户 A 转 100 元到账户 B包含两个操作A 减 100B 加 100事务保证两步要么都成功要么都不做不会出现 A 减了钱但 B 没收到的情况。2. 事务的四大特性ACID事务保证数据库安全性和可靠性核心有ACID四个特性特性英文含义原子性Atomicity事务是最小操作单位要么全做要么全不做一致性Consistency事务完成后数据库从一个合法状态变到另一个合法状态隔离性Isolation多个事务并发执行时互不干扰持久性Durability事务完成后结果永久保存在数据库即使系统崩溃也不丢失3.事务操作语法--1.开始事务 begin transaction --2.提交事务 commit --3.回滚事务 rollback4.事务操作实例假设有一个账户表 AccountCREATE TABLE Account( AccountID INT PRIMARY KEY, Balance DECIMAL(10,2) ); INSERT INTO Account VALUES (1, 1000), (2, 500);转账示例BEGIN TRANSACTION; -- 从账户1扣钱 UPDATE Account SET Balance Balance - 200 WHERE AccountID 1; -- 给账户2加钱 UPDATE Account SET Balance Balance 200 WHERE AccountID 2; -- 检查是否有异常 IF ERROR 0 ROLLBACK; -- 出错就撤销 ELSE COMMIT; -- 没问题就提交特点如果更新账户2时出错比如表锁住或数据异常账户1的钱也不会减少。事务保证了原子性。5.事务隔离级别当多个事务同时执行时需要控制“互相看到的数据状态”。SQL Server 提供 4 种隔离级别隔离级别特点常见问题Read Uncommitted可读未提交的数据脏读看到未提交的数据Read Committed只能读已提交的数据防止脏读但可发生不可重复读Repeatable Read锁定读取的行防止不可重复读但可能有幻读Serializable完全锁表严格顺序防止幻读但性能最低