█ T-SQL编程

之前的所有学习过的SQL语句具有通用性，而T-SQL只运行在sql server

▌ 变量

声明： declare 变量名 类型（变量名要求以@开头）

设置：set/select 变量名 = 值

输出：print/select 变量名

declare @name nvarchar(10)			-- 声明
set @name='Loli'					-- 赋值
print @name							-- 输出

全局变量（系统内置变量）：@@符号，直接print/select

@@version

查看当前数据库版本

@@identity

插入操作后调用，返回最近的标识值

@@servername

服务器名称

@@error

返回执行的上一个Transact-SQL语句的错误号；没有则返回0

@@rowcount

返回受上一语句影响的行数

▌ 选择语句（if）

declare @id int
set @id = 10

if @id > 5			-- 其实当只有一句T-SQL语句时，写begin end没有必要
begin				-- 满足时执行			
	print 'yes'		
end
else begin			-- 不满足时执行
	print 'no'
end

▌ 循环语句（while）

declare @id int
set @id = 0

while @id < 10
begin
	print @id
	set @id = @id+1
end

▌ 异常处理语句（try-catch）

begin try
	insert into StudentInfo 
	values('Loli')
end try
begin catch 
	print @@error
end catch

 
 

█ 事务（Transaction）

事务是并发控制的单位

保证多个操作全部成功时才认为是一次有效的操作，当一个操作失败，全部操作无效，回到做之前什么都没做的状态

也就是说，我们划定了一段代码为一个整体，它们要么全部完成，要么全部无效

只有数据改变（增加、修改、删除）时才会引发事务，查询不会引发事务

分类：显式事务（begin，commit，rollback）；隐式事务（每个语句都是一个事务）

begin try
	begin tran						-- 开启事务("反悔点")
	delete from StudentInfo
	delete from ABCInfo 
	commit tran						-- 提交事务("提交，不反悔")
end try
begin catch
	rollback tran					-- 回滚事务("反悔")
end catch

☀ 事务的目的是什么呢？

保证整个数据和行为的有效性。当一个操作有错，其他的数据和行为可能会变得毫无意义

因此我们干脆直接放弃

☀ 为什么可以实现回滚（rollback）？

先要理解插入、删除、更新的机制。
 
除了待操作的表外，还存在一个Inserted表和Deleted表；
 
• 当插入时，原表和Inserted表都被插入一条数据，确定提交后，Inserted表清空
 
• 当删除时，原表一条数据被删除，Deleted表多出一条数据，确定提交后，Deleted表清空
 
• 当更新时，没有更新表，实际上是执行了一次删除和一次插入，同时使用Deleted表和Inserted表

 
 

█ 锁

通俗地讲，锁的目的就是防止多个人同时操作时造成数据的错误

↓ 下面的锁模型一定要看懂 ！

-- demo : 锁模型

begin tran

update StudentInfo 
set cId = 1 where stuName = 'loli1'		
-- 先执行之前的的所有语句，"1行受到影响"
-- 此时"新建查询",执行update StudentInfo...,会一直处于等待状态
-- 这是因为，begin开启了事务，StudentInfo表被使用并被锁上；此时其他人或其他程序想要使用该表，只能等待

rollback tran
-- 执行该语句结束事务时，"新建查询"中的语句立即完成。这是因为锁被打开。
-- 以上就是锁模型

-- （另外的一个细节是，事务由于回滚，那"1行受到影响"的效果也被无效化了）

 
 

█ 自定义函数

将一段T-SQL脚本进行封装，以完成一个逻辑操作（即类似于"方法"“函数”）

demo :

-- 创建一个函数，计算全体学生某门功课平均成绩

-- 创建函数
create function getAverage(@courseID int) returns int
as
begin
	declare @ave int
	set @ave = (select avg(score) from ScoreInfo where sId = @courseID group by sId)	-- 根据参数进行查询
	return @ave
end

-- 调用函数
declare @ave int
declare @cId int
set @cId = 3
set @ave = dbo.getAverage(@cId)



-- 当returns的是表(table)时，称为内嵌表值函数
-- 使用的场景是：视图是不支持where子句中指定参数的，为了解决这个问题，我们使用内嵌表函数生成视图

-- 创建函数
create function getView(@specialty nvarchar(20)) returns table
as 
begin
	return(select sId,sName from StudentInfo where specialty = @specialty)
end

-- 调用函数
select * from getView("网络工程")

-- 其实，这个返回的"视图"和真正的视图是有本质的不同的，这里的返回的视图(table)是一个实实在在存在的、拥有记录的表；而真正的视图(view)不含有记录

 
 

█ 存储过程

将一段T-SQL脚本进行封装，以完成一个逻辑操作（即类似于"方法"“函数”）

create proc 名称
	--参数列表(形参)
as
begin
	-- 方法体 
end


exec 名称 参数(实参)

demo：

create proc func
	@temp1 varchar(10),					-- 形参@temp1
	@temp2 varchar(10) output			-- 形参@temp2(一个proc只能返回一个select中的值；声明output后，这个形参的值也能别被"带出来")
as
begin
	set nocount on						-- 让"n行受影响"这句话不显示
	select ltrim(rtrim(@temp1))			-- func的作用是去掉一个字符串的左右空格
	set @temp2 = 'Hello'					-- 改变形参@temp2的值；这个效果是可以被"带出proc"的
end


declare @str1 varchar(10)
declare @str2 varchar(10)
set @str1 = '   123  '
set @str2 = 'Hi'
exec fun @str1, @str2 output			-- 注意这里的@str2后要加上output，这样@str2的值被改变的效果，就被"带出"fun内部了
print @str2								-- 验证@str2是否真的被改变（打印Hello）




-- 创建一个过程，比较两个员工(用员工ID表示)的平均工资，若前者比后者高就输出1,否则输出0

create procedure compare @clerkId1 int, @clerkId2 int, @res int output
as
begin
	declare @ave1 float
	declare @ave2 float
	set @ave1 = (select AVG(salary) from salaryInfo where clerkId = @clerkId1)
	set @ave2 = (select AVG(salary) from salaryInfo where clerkId = @clerkId2)
	if @ave1 > @ave2
		set @res = 1
	else
		set @res = 0
end

 
 

█ 索引器（indexer）

▶ 是什么？

  相当于书的目录，实现了数据的快速查找

▶ 作用？

  数据量很大，where用到的列加索引后，查询效率大大提高

  但索引不是越多越好，因为物理开销越大——典型的"空间换时间"

▶ 分类

  1. 聚集索引：与实际存储数据位置一样；一个表中只能有一个聚集索引（主键）
  2. 非聚集索引：索引顺序与存储顺序不一致（我们常用的）

▶ 建立索引

界面：右击表 → 设计 → 右键→ 索引/键 → 选择列（是唯一的?）

代码：

create (unique) nonclustered index ix_名称 on 表名(列名)			-- 非聚集索引

 
 

█ 触发器（trigger）

当对某个表进行某个操作时，会触发某个触发器，从而执行一个段程序

触发器是一个特殊的存储过程
 
分类：

  1. DML触发器：当进行insert、delete、update操作时触发（没有select）
          分为after（for）触发器、instead of触发器
  2. DDL触发器 : 在create、alter、drop…table、database…时触发

语法：

create trigger 触发器名称 on 表名
after(for)/instead of  insert/delete/update
as
begin
	...
end

 
一个典例：在另一表中备份所有被删除的数据

-- 创建一个结构相同的备份表
select top 0 * into StudentBackup from StudentInfo

-- 创建一个触发器
create trigger tri_delete_student on StudentInfo
after delete
as
begin										-- 就算一次删除50个数据，触发器也只被触发1次，这段代码执行50次
	insert into StudentBackup(sId, sName)
	select sId, sName from deleted			-- 把deleted表中的数据插入到备份表中
end

 
另一个典例：当StudentInfo修改学号时，ScoreInfo中的学号也同时修改 (学号的类型为char(10))

create trigger tri_update on StudentInfo
after update
as 
begin
	declare @old_num char(10), @new_num char(10)
	set @old_num = (select sId from deleted)
	set @new_num = (select sId from inserted)
	update ScoreInfo
	set sId = @new_num where sId = @old_num
end

 

 

  
> _ <