SQL Server Transact-SQL编程/存储过程/触发器

1.T-sql语言
数据定义语言DDL对表或视图的操作
数据操纵语言DML对数据进行操作
数据控制语言
其他常用类型： 
    事务管理语言
    流程控制语言  循环  分支  选择
    附加的语言元素     变量 常量
2.常量不需要声明
全局变量：一般系统生成
局部变量：自己声明的
declare @变量名 数据类型
set @变量名=值
select @变量名=表达式（表达式返回单个值）

declare @name nvachar(50)
set @name='张三'
select @name

--变量赋值方法二
select @name='王文'
select @name

3.
位运算符：&    |  ^ 
比较运算符： = <> != !< !> > >= < <=
逻辑运算符:
all如果一组的比较都为true则结果为true
and
any如果一组的比较中任何一个为true则结果为true
between
exists如果子查询中包含了一些行，那么结果为true
in
like
not
or
some 如果一组比较中，有些比较为true结果为true
赋值运算符=
连接运算符+
一元运算符
+正数
-负数
~返回数字的逻辑非
4.表达式
declare @str nvarchar(50)
select @str='姓名：'+姓名+‘性别：’+性别 from 学生信息 where 学号=‘20050502’
select @str
5.注释：变量说明、代码更改日期、算法描述等
--单行注释
/*多行注释*/

6.
if bool表达式
begin 
语句块
end
else
begin
语句块
end

--语句块包含多个语句时用begin  end 

declare @num int 
select @num=avg(分数) from 成绩信息 where 考试编号='0802' and课程编号='2'
if @num>80
begin
    print '这们课老师教课非常成功'
end
else
    print '这门课老师还需要继续努力'
7.if  else 语句嵌套

declare @num int 
select @num=avg(分数) from 成绩信息 where 考试编号='0802' and课程编号='2'
if @num>=60
begin
    if @num<70
    print '成绩刚刚及格，还要继续努力'
    else if @num<80
    print '成绩中等'
    else if @num<90
    print '成绩良好'
    else
    print '成绩优秀，希望把经验分享给大家'
end
else
    print '这门课老师还需要继续努力'

8.case语句
case 变量表达式
when  值1  then  操作语句1
when  值2  then  操作语句2
.
.
.
else 操作语句  --可以省略不设置
end

select  员工姓名 ,所任职位,员工职称=
case 所任职位
when ‘经理’ then '高级职称’
when ‘主管’ then '中级职称'
when ‘职员’then '初级职称'
else '其他职称'
end 
from 员工信息 

9.while 语句
while boolean表达式
begin
t-sql语句
break 
t-sql语句
continue
t-sql语句
end

declare @i int,@num int
set @i=1
set @num=1
while @i<=10
begin
    set @num=@num*@i
    set @i=@i+1
end
print @num

--FitemID,FParentID,FLevel  ID 父ID 级别

create function [dbo].[GetChild](@ID varchar(10))
return @t table (FitemID varchar(10),FParentID varchar(10),Level int)
as
begin
    declare @i int
    set @i=(select Flevel from department where FitemID=@ID）
    insert into @t select FitemID,FparentID,Flevel from department where FitemID=@ID
 --当前级，本级，如果不要的话可以注释掉  将当前级插入到表变量中
    while @@rowcount<>0  --全局变量 @@rowcount执行sql语句受影响的行数
    begin
        set @i=@i+1
        insert into @t
        select 
            a.FParentID=b.FitemID and b.Level=@i-1
    end
    return
end

create table department(
FitemID varchar(10),
FParentID varchar(10),
Fname varchar(50),
Flevel int
)

insert into department(FitemID,FParentID,FLevel)
values('1','0',1),
    ('2','1',2),
    ('3','1',2),
    ('4','1',2),
    ('5','2',3),
    ('6','2',3),
    ('7','2',3),
    ('8','3',3),
    ('10','3',3),
    ('11,'4',3),
    ('12','4',3),
    ('13','4',3),
    ('14','4',3),
    ('15','5',4),
    ('16','5',4),
    ('17','6',4),
    ('18','7',4)

select fITEMID from GETCHILD('2')

10. while 语句中使用其他语句
 3-100的素数
declare @i int
delcare @j int
set @i=3
while @i<=100
begin 
    declare @bol int
    set @bol=1
    SET @j=2
    begin
        while @j<sqrt(@i)
        begin
        if @i%@j=0
        begin
        set @bol=0
        break
        end
        set @j=@j+1
    end
    if @bol=1 print @i
    set @i=@i+1    
end

11.waitfor 延迟语句
waitfor {
delay time --延迟时间值
|time time --指定具体时间执行语句
}

--调用
waitfor delay '00:00:05'
exec sp_help

waitfor time '00:00:05'
exec sp_helpdb

12.goto 语句--跳转语句

declare @Counter int 
set @Counter=1
while @Counter<10
begin
    print @Counter
    set @Counter=@Counter+1
    if @Counter=4 goto Branch_One
    if @Counter=5 goto Branch_Two
end
Branch_One:
    print 'Jumping To_Branch One'
    goto Branch_Three;
Branch_Two:
    print 'Jumping To Branch Two'
Branch_Three:
    print 'Jumping To Branch Three'

13.try ...catch 错误处理语句

begin try 
sql 语句
end try 
bengin catch 
sql语句
end catch

begin try 
declare @num int 
set @num=1/0
select @num
end try 
begin catch
select error_line() as '错误行数',error_message() '错误消息'
end catch

14.数学函数
abs 绝对值
exp 以E为底的指数
ceiling >=表达式的最小整数
floor <=表达式的最大整数
ln 数值表达式的自然对数
log 数值表达式以10为底的对
power 数值表达式进行幂运算的结果
round 舍入到指定长度或精度的数值表达式
sign 数值表达式的正号、负号、或零
square 数值表达式的平方
sqrt 数值表达式的平方根

declare @i float,@j int,@a decimal(18,2)
set @i=-12.345
set @j=100
print round(@i,2)
print floor(abs(round(@i,2)))
print power(@j,2)
print square(@j)
print power(@j,0.5)
print sqrt(@j)

15.字符串函数
ascII 返回字符串表达式中最左侧的字符的ASCII代码值
char 返回指定ascII代码的字符
left 左子串函数，返回字符串中从左边开始指定个数的字符串
len 返回指定字符串表达式的字符数（不是字节数）,其中不包含尾随空格
lower 转换成小写字符
ltrim删除前导空格字符串，返回删除了前导空格之后的字符表达式
replace 替换函数 用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项
replicate复制函数，以指定的次数重复字符表达
right 返回字符串中从右边开始指定个数的字符
rtrim删除尾随空格函数，删除所有尾随空格后返回一个字符串
space 空格函数，返回由重复的空格组成的字符串
str 数字向字符转换函数，返回由数字数据转换来的字符数据
substring 子串函数，返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分
upper 将小写转换为大写

declare @str nvarchar(50)
set @str='hello word!'
print ascii(@str)
print char(104)
print left(@str,5) --hello
print right(@str,5) --orld!
print substring(@str,7,5) --world
print len(@str) --12长度

常用字符串函数
charindex(父字符串或列名表达式，子字符串)返回字符串中某个指定的子串出现的开始位置，如果没有发现子字符串则返回0,此函数不可用于text 和 image数据类型
patindex('%子串%',列名)返回字符串中某个指定的子串出现的位置，此函数可用于char、varchar和text数据类型

declare @str1 nvarchar(20),
@str2 nvarchar(20),
@str3 nvarchar(20),
@str4 nvarchar(20),
@str5 nvarchar(20),
set @str1='上海分公司经理'
set @str2='北京分公司经理'
set @str3='天津分公司经理'
set @str4='上海分公司财务经理'
set @str5='天津分公司财务经理'

--分公司经理
print charindex('分公司',@str4)
print right(@str1,len(@str1)-charindex('分公司',@str1)+1))
--分公司  经理
print right(@str1,len(@str1)-patinex('分公司%经理',@str1)+1))

16.聚合函数
AVG
COUNT
SUM
MAX
MIN

17.日期和时间函数
dateadd返回给指定日期加上一个时间间隔后的新datetime值  dateadd(间隔单位，间隔值，指定日期)
datediff返回跨两个指定日期的日期边界数和时间边界数  datediff(单位，日期1，日期2）
datename返回指定日期的指定日期部分的字符串
datepart返回表示指定日期的指定日期部分的整数 datepart(单位,指定日期)
day返回一个整数，表示指定日期的天DATEPART部分
getdate返回当前系统日期和时间
getutcdate返回当前的UTC时间
month放回表示指定日期的月部分的整数
year返回指定日期的年份

print getdate()
print getutcdate()
print year(getdate())
print month(getdate())
print day(getdate())
print datepart(year,getdate())
print datepart(day,getdate())
print datepart(yy,getdate())
print datepart(mm,getdate())
print datepart(dd,getdate())
print dateadd(mm,1,getdate())
print datediff(dd,'2018-01-01','2018-08-01')

18.标量值函数
create function 函数名
(@参数名  类型)
returns 返回值类型
[with encryption]加--密
begin
sql函数体
return  变量表达式
end

create function avgsum(@id varchar(10))
returns int
as 
begin
declare @sum int
select @sum=avg(分数) from 成绩信息
where 课程编号=@id
return @sum
end 
go

--调用函数
select dbo.avgsum('2');

19.表值函数
create function(@参数名 类型)
returns table
[with encryption]
[as]
return(sql语句返回数据表)

create function findcourse(@id varchar(10)) 
returns table
as
return
(    
select 课程名称,姓名,分数,from(
    select b.课程名称,c.姓名,avg(a.分数) 分数
    from 成绩信息 a join 课程信息 b
    on a.课程编号=b.课程编号
    join 教师信息 c on b.任课教师=c.教师编号
    where a.考试编号=@id 
    group by b.课程名称,c.姓名
)a
where 分数>=85
)
go

--调用函数
select * from findcourse('0801')

20.游标Cursor:是一种数据访问机制，允许用户访问单独的数据行，而不是对整个行集进行操作。用户可以通过单独处理每一行逐条收集信息并对数据逐行进行操作，可以降低系统开销和潜
在的阻隔情况，可以使用这些数据生成SQL代码并立即执行或输出。
游标主要包括两部分：
    1）游标结果集   由定义游标的SELECT语句返回的行的集合
    2）游标位置    指向这个结果集中的某一行的指针

特点：  返回一个完整的结果集，但允许程序设计语言只调用集合中的一行；
    允许定位在结果集的特定行；
    从结果集的当前位置检索一行或多行；
    支持对结果集中当前位置的行进行数据修改；
    可以为其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持；
    提供脚本、存储过程和触发器中使用的访问结果集中的数据的Q-SQL语句。
declare 游标名称 [insensitive][scroll] cursor --insensitive将结果集放到临时表中游标操作的是临时表中的数据，当数据库中的数据更新时，游标操作不受影响；scroll，没有此关键
字游标只能访问结果集中下一行数据；当添加上此关键字时，可以访问结果集中的第一行最后一行，上一行，下一行等操作,随机指定游标的位置
for select语句
[for {read only|update[of 列名1,列名2...列名n]}]  --read only只读的游标；update 可修改的游标，当后面跟列名则指定只有那些列可以修改，若不写列名则所有列都可以修改

declare student_cursor scroll cursor
for
select * from 学生信息
for read only

deallocate student_cursor --释放游标

declare student_cursor scroll cursor
for
select * from 学生信息
for update

--打开游标
open 游标名称
--检索游标
fetch [[next|prior|first|last|absolute{n |@nvar}|relative{n|@nvar}] --absolute指定检索当前结果集中的第几行，relative指定检索在当前行向后几行即间隔几行去读取
from]
游标名
[into @variable_name]--将某一个数据行的列的值放到一个定义的变量中  
--关闭游标
close 游标名
--释放游标
deallocate 游标名

--声明游标
declare stu_cursor scroll cursor for
select 姓名 from 学生信息 

--打开使用关闭释放游标
declare @NAME varchar(20)
open stu_cursor
--fetch next from stu_cursor 
--fetch first  from stu_cursor
fetch absolute(10)  from stu_cursor
into @NAME
print @name
close stu_cursor
deallocate stu_cursor

21.判断游标提取状态
@@fetch_status --全局变量获取提取状态
返回值：
0  fetch 语句成功
-1 fetch 语句失败或行不在结果集中
-2 提取的行不存在

declare stu_cursor scroll cursor for
select 姓名 from 学生信息

open stu_cursor
declare @name varchar(20)
--fetch last from stu_cursor
fetch next from stu_cursor
into @name
if @@fetch_status=0
print @name
else
print '提取数据错误'
close  stu_cursor

deallocate stu_cursor

22.游标应用示例
--声明一个游标stu_cursor
declare stu_cursor scroll cursor for
select 学号 from 学生信息

--创建一个临时表#t
create table #t(name nvarchar(20),sorce int)
--打开游标
open stu_cursor
--声明变量
delare @name varchar(20)
--检索游标
fetch next from stu_cursor
into @name
--while循环判断检索结果是否为0，检索的游标位置有没有超出结果集的行
while @@fetch_status=0
begin
--如果fetch提前成功将当前行列的值放到临时表#t中
insert into #t
select b.姓名，avg(分数) as 分数
from 成绩信息 a join 学生信息 b
on a.学生编号=b.学号
where a.考试编号='0801' and b.学号=@name
group by b.姓名
/*
.
.此处可以查询检索很多信息放到临时表中，通过学号=@name检索将不相关的信息放到同一个临时表中
.
*/
--检索游标   下一行如果@@fetch_status为0则继续循环
fetch next from stu_cursor
into @name
end

--关闭游标
close stu_cursor
--释放游标
deallocate stu_cursor

--查询临时表#t中的信息
select name 姓名,sorce 平均分 from #t
--删除临时表
drop table #t

23.事务
begin transaction --开启事务
commit transaction --提交事务
rollback transaction --回滚事务
save transaction--保存事务，可以保存部分事务，回滚的时候只有save transaction 以后的事务进行回滚

例子：
begin transaction
declare @nation varchar(20)
set @nation='满族'
insert into 学生信息 values('2009010201','李明','男','1984-08-08',@nation,'20090102','北京市')
--save transacton
--其他操作语句
if @nation='汉族'
begin
 commit transaction
end
else
rollback transcation

24.锁
锁：封锁，就是一个事务可以向系统提出请求，对被操作的数据枷锁Lock。其他事务必须等到此事务解锁Unlock之后才能访问该数据。从而，在多个用户并发访问数据库时，确保不互相干扰
。可锁定的单位是：行，页，表，盘区和数据库。

分类：
共享锁S：读操作。
    多个事务可封锁一个共享单位的数据。
    任何事务都不能修改加S锁的数据。
    通常是加S锁的数据被读取完毕，S锁立即被释放。
独占锁X：用于写操作
    仅允许一个事务封锁此共享数据。
    其他任何事务必须等到X锁被释放才能对数据进行访问。
    X锁一直到事务结束才能被释放。
更新锁U：用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁。
    当被读取数据页将要被更新时，则升级为X锁。
    U锁一直到事务结束时才能被释放。

25.系统与元数据函数
convert()  --将一种数据类型的数据转换为另一种数据类型的数据
current_user  --返回当前用户的名称
isdate()  --判断它的输入是不是一个有效日期
isnull() --用一个指定替换值替换任何空值
isnumeric()  --判断它的输入是不是一个数值

declare @str varchar(10),@i int,@j int
set @str='12'
set @i=10

if isnumeric(@str)=0 --=0不是数值 1是数值
begin
    print convert(int,@str)*@i
end
print isnull(@j,10)*@i --如果为空则赋默认值10
 

26.begin ... end

begin 
{
sql语句
}
end

declare @sex varchar(5)
if @sex is not null
begin
select * from 学生信息 where 性别=@sex
print '查询成功'
end
else
begin
print '查询信息失败，参数为空'
end

27.事务模式
自动提交事务：每条单独的语句都是一个事务
显式事务：每个事务均以begin transaction 语句显式开始，以commit或rollback 语句显式结束
隐式事务：在前一个事务完成时新事物饮食启动，但每个事务仍以commit或rollback语句显式完成
批处理级事务：只能应用于多个活动结果集MARS,在MARS会话中启动的transact-SQL显式或饮食事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动又SQL Server进行
回滚
28.XACT_ABORT选项
用于指定当SQL语句出现运行时错误时，SQL Server是否自动回滚到当前事务。其语法格式：
set xact_abort{on|off}
当set xact_abort 为on 时，如果执行SQL语句产生运行时错误，则整个事务将终止并回滚。当set xact_abort为off时，
有时只回滚产生错误的SQL语句，而事务将继续进行处理。如果错误很严重，那么即使set xact_abort为off也可能回滚整个事务。
set xact_abort的设置是在执行或运行时设置，而不是在分析时设置。对于大多数OLE DB提供程序，必须将隐式或显示事务中的数据修改语句中的xact_abort设置为on 。唯一不需要该选项设
置的情况是在提供程序支持嵌套事务时。

28.死锁
两个或两个以上的进程在执行过程中，因争夺资源而造成的一种相互等待的现象，若无外力作用，他们将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的
进程成为死锁进程。
由于占用资源是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。
一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源。例如，如果线程A说住了记录1并等待记录2，而线程B锁住了记录2并等
待记录1，这样两个线程就发生了死锁现象。

必要条件：
互斥条件：一个资源每次只能被一个进程使用
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件：进程已获得的资源，在未使用完之前，不能强行剥夺。
循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。
以上四个条件是锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生锁。

死锁的解除和预防
按同一顺序访问对象
避免事务中的用户交互
保持事务简短并在一个批处理中
使用低隔离级别
使用绑定连接

死锁的建议：
对频繁使用的表使用集蔟化的索引
设法避免一次性影响大量记录的T-SQL语句，特别是insert和update语句
设法让update和delete语句使用索引
使用嵌套事务时，避免提交和回退冲突
对一些数据不需要及时更新值得表在写SQL的时候在表后台加nolock如：select * from tableA(nolock)

29.嵌套事务

begin transaction T1
update 班级信息 set 班级名='金融管理系1班' where 班级编号='20050301'
if exists(select * from 班级信息 where 班级名='金融管理系1班')
begin
    begin transaction t2
    update 班级信息 set 辅导员='李铭' where 班级名='金融管理系1班'
    if exists(select * from 辅导员信息 where 姓名='李铭')
    commit transacton t2
    else
    begin
    rollback transaction t2
    end
    commit transaction t1
end
else
rollback transaction t1

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

1.存储过程
create proc p_employee
@departmentId varchar(10),
@zhiwei varchar(20)='职员'
as 
select A.员工姓名,A.性别，A.所在职位，A.联系电话，B.部门名称
from 员工信息 A,部门信息 B
where a.所在部门编号=B.部门编号 and B.部门编号=@departmentId and A.所任职位=@zhiwei

exec p_employee @departmentid='10001',@zhiwei='经理'

3.create  proc p_sorce
@name varchar(10),
@sorce int output
as
select b.姓名,@sorce=avg(a.分数)
from 成绩信息 a,学生信息 b
where a.学生编号=b.学号 and b.学号=@anme

---执行存储过程
declare @sorce1 int
exec p_sorce @name='张苗苗',@sorce1=@sorce1 output
-- 或者exec p_sorce '张苗苗',@sorce1 output
print @sorce1

4.
create proc sorceforupdate
@id varchar(10),
@sorce int
as
update 成绩信息 set 分数=@sorce where 成绩编号=@id

--调用
exec sorceforupdate @id='3',@sorce=100
5.删除存储过程
drop procedure 存储过程名称
-- drop proc proc_student
6.设计存储过程的规则
    可以在存储过程中引用临时表
    可以访问被调用
    远程存储过程不能回滚更改，不参与事务处理
不能包括的T-SQL语句
create  aggregate 
create rule
create default
create schema  架构
create 或者alter function 函数
create 或alter trigger 触发器
create 或alter procedure 子存储过程
create 或alter view 视图
set parseonly
set showplan_all
set showplan_text
set showplan_xml
use Database_name 切换数据库

7.系统存储过程
exec sp_who sa  --登录用户
exec sp_helpdb 学生成绩管理系统    --数据库的信息
exec sp_monitor  --数据库的统计信息
8.临时存储过程 
#局部临时存储过程  ##全局临时存储过程

create procedure #get_info
as
select a.分数,b.姓名  from 成绩 a,学生信息 b where a.学生编号=b.学号 
order by b.学号

--调用临时存储过程
exec #get_info

drop proc #get_info
 

create procedure ##get_info
as
select a.分数,b.姓名  from 成绩 a,学生信息 b where a.学生编号=b.学号 
order by b.学号

--调用临时存储过程
exec ##get_info

9.嵌套存储过程

create proc get_classinfo
as
select * from 班级信息
exec get_student ‘男’
print @@NESTLEVEL --打印存储过程级别（嵌套级别）

--调用
exec get_classinfo

10.查看存储过程
exec sp_helptext get_student

1.存储过程包含：程序流、逻辑及对数据库的查询，接受参数、输出参数、返回单个或多个结果集及返回值
用户自定义存储过程
系统存储过程
扩展存储过程
create proc 名称
@参数名称 参数类型，@参数名称 参数类型=默认值,@参数名称 参数类型 output
with {recompile|encryption|recompile,encryption}
as
sql语句

-- with recompile当前引擎不缓存存储过程的执行计划

create procedure proc_student
@sex varchar(10)
as
select *  from 学生信息 where 性别=@sex

exec proc_student @sex='男'

---------------------------------------------------------------------------------------------
1.创建触发器，当学生信息表插入数据时，班级表的班级人数+1
create trigger T_addnum
on 学生信息 
for insert
as 
update 班级信息 set 班级人数=班级人数+1
where 班级编号=(select 所属班级 from inserted)

2.
create trigger T_DELETETEACHER
on 教师信息
for delete
as
select 姓名 as 被删除的教师，性别，年龄，联系电话 from deleted

3.
create trigger t_update
on 教师信息
for update
as
if (update(姓名) or update(性别))
begin
    print '事务不能被处理，基础数据不能被修改'
    rollback transaction
end
else
    print '数据修改成功'

4.DDL触发器 数据定义触发器
create trigger 触发器名
on {all server|database}
with encryption
{for|after|event_type}
as
SQL语句

 create trigger T_ontdelete
on detabase
for Drop_table,alter_table
as
print '事务不能被修改，基础数据表不能被修改和删除'
rollback

5. 管理触发器
alter table 员工信息
disable trigger t_add
alter table 员工信息
enable trigger t_add
alter table 员工信息
drop trigger t_add

6.嵌套触发器
create trigger t_del
on 新员工信息
for delete
as
insert into 员工信息表 (员工编号,员工姓名,所在部门编号,所任职位,性别) 
select 员工编号,员工姓名,所在部门编号,所任职位,性别
from deleted

create trigger t_add
on 员工信息
for insert
as 
update 部门信息 set 员工人数=员工人数+1
where 部门编号=(select 所在部门编号 from inserted)

delete from 新员工信息 where 员工编号=14 
select 员工人数 from 部门信息 where 部门编号='10001'

触发器最多嵌套32层

7.递归触发器  最多出发16层