SQLServer 编程总结
case的用途
case语句有四个关键字,缺一不可,分别是:case when then end，另外还有一个else。case 后面跟字段名（当在when后面出现字段名时，case里不能写出来），when后面跟判断语句，then后面跟显示结果， end表示case语句结束。
--第一种用法 用于简单的是非判断(字段名在case后面) select sname,case ssex when 1 then '男' else '女' end as sex from student --第二种 进行稍微复杂一些的判断（字段名在when后面） select sname,case when ssex = 1 then '男' when ssex =0 then '女' end as sex from student
for xml path
for xml path是将结果集以XML的格式返回。用的比较多的地方，就是group by之后，拼接字符串返回。
示例：
--创建一张表subjects，有三个人，分别选修了不同的科目 create table subjects(username varchar(20),subjects_name varchar(20)) insert into subjects (username,subjects_name) values ( '谭' ,'高数' ), ( '谭','音乐' ), ( '谭谭','体育' ), ( '谭谭','武术' ), ( '谭谭','化学' ), ( '谭谭谭','生物' )
根据上面的内容，我们就可以查询每个人学习的科目，并把这些科目放在一起（用逗号分隔）。
--使用stuff去除首位分隔符
select s.username,stuff(( select ','+b.subjects_name from subjects as b where b.username=s.username for xml path('')),1,1,'') as subjects
from subjects as s
group by username
除了上面的用法，也可是 FOR XML PATH可以用来拼接JSON字符串或字符串。例如：
-- STUFF结合FOR XML PATH可以用来拼接JSON字符串
select '['+ stuff((select ',{"name": "' + username + '","subjects": "' + subjects_name + '"}'
from subjects for xml path('')),1,1,'') +']'
while循环与游标
while语句使用示例
declare @i int
set @i = 1
while @i<5
begin
--insert into student (sname,sage,ssex) values ('tanyongjun',12,1)
print @i
set @i = @i +1
end
游标示例
--1.声明游标 declare test_cursor cursor scroll for select sname,sage from student where ssex = 1 --2.打开游标 open test_cursor --3.声明游标提取数据所要存放的变量 declare @sname varchar(50), @sage int --4.定位游标到哪一行 fetch first from test_cursor into @sname,@sage --into的变量数量必须与游标查询结果集的列数相同 while @@fetch_status=0 --提取成功，进行下一条数据的提取操作 begin --进来业务处理 --例如：把 sname为 tanyongjun 的数据中 sage 字段 加+1，进行修改 if @sname='tanyongjun' begin update student set sage = sage+1 where current of test_cursor -- 如果是修改当前行，就可以用上面这中写法 end -- 当然也可以根据提取的数据作为条件进行处理 fetch next from test_cursor into @sname,@sage --移动游标 end --5.关闭游标 close test_cursor --6.删除游标，释放资源 deallocate test_cursor
总结：
1. while循环和游标都可以实现循环的目的，while用法比较简洁明了；
2. 游标是在while的基础上实现循环的功能，但是语法较复杂，且效率没有while高，所以一般不推荐使用；
3. while循环体内支持再嵌套一个循环，但是一定要注意不能出现死循环，否则sql服务就会卡死、崩溃。
exists
exists是用来判断是否存在的，当exists查询中的查询存在结果时则返回真，否则返回假。not exists则相反。
exists做为where 条件时，是先对where 前的主查询询进行查询，然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。
查询时，一般情况下，子查询会分成两种情况：
1.子查询与外表的字段有关系时
例如： select col1 , col2 from t1 where exists (select col1 , col2 from t2 where t2.col1 = t1.col1)
上面的sql语句相当于一个关联查询。它先执行t1的查询，然后把t1中的每一条记录放到t2的条件中去查询，如果存在，则显示此条记录。
2.子查询与外表的字段没有任何关联
例如： select col1 , col2 from t1 where exists ( select * from t2 where t2.col1 = '条件')
在这种情况下，只要子查询的条件成立，就会查询出t1中的所有记录，反之，如果子查询中没有查询到记录，则t1不会查询出任何的记录。
当子查询与主表不存在关联关系时，简单认为只要exists为一个条件判断，如果为true,就输出所有记录。如果为false则不输出任何的记录。
json操作
主要介绍5个函数：
1. openJson:打开Json字符串
2. IsJson:判断一个字符串是不是合法的Json格式。是返回1，否返回0，null返回null。
3. Json_Value:从Json字符串中提取值。
4. Json_Query:Json字符串中提取对象或数组。
5. Json_Modify:更新Json字符串中的属性值，并返回更新的Json字符串。
--IsJson:判断一个字符串是不是合法的Json格式。是返回1，否返回0，null返回null。
select isjson('{"id":"1","name":"tanyongjun","sex":1}')
select isjson('tan')
--从Json字段中提取值
select json_value('{"id":"1","name":"tanyongjun","sex":1}','$.name')
函数和存储过程
一、自定义函数
自定义函数分为：标量值函数或表值函数两种。
标量值函数：如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。（返回字段）
表值函数：如果 RETURNS 子句指定 TABLE，则函数为表值函数。（返回table）
表值函数又分为两种：内嵌表值函数（行内函数）或多语句函数
如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数
--标量值函数
create function fun_add_num(@age int)
returns int
as
begin
declare @num int
set @num = 2
return @age + @num
end
go
-- 调用 *注：必须加上dbo.否则会报错
select dbo.fun_add_num(11)
go
--内嵌表值函数
create function fun_test(@name varchar(50))
returns table
as
return (select sname,sage,ssex from student where sname = @name)
go
--调用 在调用 表值函数 时 dbo. 可以省略
select * from fun_test('tanyongjun')
go
-- 多语句表值函数 ****分割字符串，然后返回
create function fun_split_str
(
@str_val varchar(2000), --要分割的字符串
@split varchar(10) --分隔符号
)
returns @temp table (result varchar(10))
as
begin
declare @result as varchar(10); --定义变量用于接收单个结果
set @str_val = @str_val + @split ;
while (@str_val <> '')
begin
set @result = left(@str_val, charindex(@split, @str_val) -1) ;
insert @temp values(@result) ;
--STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
set @str_val = stuff(@str_val, 1, charindex(@split, @str_val), '');
end
return
end
go
--调用
select * from fun_split_str('tan,tanyong,tanyongjun',',')
注意事项：
标量函数：
1. 所有的入参前都必须加@
2. create后的返回，单词是returns，而不是return
3. returns后面的跟的不是变量，而是返回值的类型，如：int，char等。
4. 在begin/end语句块中，是return。
内嵌表值函数：
1. 只能返回table，所以returns后面一定是TABLE
2. AS后没有begin/end，只有一个return语句来返回特定的记录。
多语句表值函数：
1. returns后面直接定义返回的表类型，首先是定义表名，表明前面要加@，然后是关键字TABLE，最后是表的结构。
2. 在begin/end语句块中，直接将需要返回的结果insert到returns定义的表中就可以了，在最后return时，会将结果返回。
3. 最后只需要return，return后面不跟任何变量。
二、存储过程
-- 无参存储过程 create proc proc_test1 as -- as 不能省略 begin select * from tb_test end go exec proc_test1 go --有参存储过程 create proc proc_test2 @username varchar(50) as begin select * from tb_test where username = @username end exec proc_test2 'tanyongjun' --有参 有返回值的存储过程 create proc proc_test3 @username varchar(50), @row_number int output as begin select @row_number = count(*) from tb_test where username = @username end go declare @row_num int exec proc_test3 'tanyongjun11', @row_num output -- 不写output ，@row_num 不输出任何信息 print @row_num
存储过程和函数的使用：
1. 存储过程和函数都可以将一段SQL语句进行封装，这样大大的方便了实际使用时候的调用步骤。
2. 存储过程里面可以嵌套存储过程，也经常会调用各种函数。
3. 函数里面可以调用其他函数，但是一般不嵌套存储过程。
4. 上面例子中的函数，也可以改写成存储过程，但是函数使用起来要方便的多，因为函数可以直接用select联用，但是存储过程只能用exec执行。
常用函数（字符串、时间）
一、字符串
--计算字符串长度
select len('tanyongjun')
--字符串转换为大、小写
select lower('TAN') --将字符串转换为小写
select upper('tan') --将字符串转换为大写
--去字符串前后空格
select trim( ' tanyongjun ')
--删除指定长度的字符，并在指定的起点处插入另一组字符
--stuff(character_expression , start , length ,character_expression)
--start 一个整数值，指定删除和插入的开始位置。
--length 一个整数，指定要删除的字符数。
select stuff('abcdefg',1,6,'Hello ')
--从左侧开始取子字符串
select left('tanyongjun',3)
--从右侧开始取子字符串
select right('tanyongjun',7)
--字符串替换
select replace('tanyongjun','yong','YONG')
--字符串拼接
select 'tan'+'yogjun' --用+号实现字符串拼接
select concat('tan','yongjun') --用concat()内置函数实现字符串拼接
二、时间
select convert(varchar(50),getdate(),23) -- 获取当前日期 select convert(varchar(50),getdate(),108) -- 获取当前时间
1. DATEADD (datepart , number, date )函数
用于进行日期时间的加法运算。其中datepart参数是表示日期部分的参数（比如是以日期还是以月份相加等），number参数是具体的加数，正数代表向未来日期方向加，负数代表向过去日期方向减，date参数为待计算的日期时间类型数据。
datepart参数可以取的单位类型如下：
例如：当前时间加上 3 天 ： select dateadd(dd,3,getdate())
2. DATEDIFF ( datepart , startdate , enddate )函数
用于计算两个日期时间之间的差额。其中datepart参数表示日期部分的参数（比如只比较年还是只比较月等），startdate参数为起始日期时间类型数据；enddate参数为结束日期时间类型数据。
示例： select datediff(dd, '2023/1/17 ', '2023/1/19')
3. DATENAME(datepart,date)函数
用来获取一个日期的特定部分，比方只获取年份或者是只获取月份等。其中datepart参数是表示要返回的日期部分的参数(即如果是Year的话，则返回的是具体的年份)，date参数为待计算日期。
示例： select datename(dd,getdate()) -- 获取当前时间是 “几号”
三、其他
-- cast 类型转换 declare @temp1 int = 1 select cast(@temp1 as varchar(50) ) -- isnull 如果表达式为 NULL，则返回指定值，否则返回表达式 declare @temp2 varchar(50) select isnull(@temp2,'没有内容') set @temp2 = 'tanyongjun' select isnull(@temp2,'没有内容')
