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,'没有内容')
标签:总结,end,函数,--,编程,SQLServer,查询,字符串,select From: https://www.cnblogs.com/tanyongjun/p/17056324.html