变量赋值 set、 select
--变量赋值 set、 select declare @variable varchar(20) select @variable='变量赋值一,通过select赋值' print @variable set @variable='变量赋值二,通过set赋值' print @variable go
自定义函数中使用 条件判断语句 if else
--自定义函数中使用 条件判断语句 if else --自定义函数中不能存在 print 打印操作 create function GetReturnValue ( @num1 int, --入参信息 @num2 int --入参信息 ) returns varchar(max) -- 返回值类型 as begin declare @variable varchar(100) --if 条件语句 if @num1 >@num1 begin --print('num1 > num2') --自定义函数中不能存在 print 打印操作 set @variable ='num1 > num2' end if @num1 = @num2 begin --print('num1 = num2') set @variable ='num1 = num2' end else begin --print('num1 < num2') set @variable ='num1 < num2' end return @variable --返回值 end go --调用函数 select [dbo].[GetReturnValue](6,8) as 函数调用返回值
循环语句
--循环语句 declare @num int set @num=1 while @num <=10 begin print('num=' + convert(varchar(2),@num)) insert into [dbo].[student] values('num' + convert(varchar(2),@num),@num,'男') set @num=@num + 1 end go select * from [dbo].[student]
--循环语句 break declare @num int set @num=1 while @num <=10 begin print('num=' + convert(varchar(2),@num)) if @num=5 begin break end set @num=@num + 1 end go --循环语句 continue declare @num int set @num=1 while @num <=10 begin set @num=@num + 1 if @num=5 begin continue end print('num=' + convert(varchar(2),@num)) end go
算术运算符:
--sql 数字 算术运算符 + - * / % declare @num1 int,@num2 int,@num3 varchar(10) set @num1 =4 set @num2=2 set @num3=6 --数字操作 select @num1 + @num2 as 加法 select @num1 - @num2 as 减法 select @num1 * @num2 as 乘法 select @num1 / @num2 as 除法 select @num1 % @num2 as 求余 select @num1 + convert(varchar(10),@num3) -- 字符串本身为数字,按数字处理返回结果 go --字符串 + 数字 :运算 declare @num3 int,@num4 varchar(20),@num5 varchar(20) set @num3=11 set @num4='9' set @num5='abc' --字符串 + 数字 拼接 retrun 119 select convert(varchar(20),@num3) + convert(varchar(20),@num4) as 字符串 --字符串拼接 select @num4 + @num5 --数字拼接--即相加 return 20 select @num3 + @num4 as 数字
substring、len:
--substring字符串截取 参数解释如下: --参数1:字符串内容 --参数2:从字符串的第几位开始截取,索引从1开始 --参数3:截取到第几位 --len('字符串'):用于获取字符串长度 select len('helloWpf') as 获取字符串长度,substring('helloWpf',2,len('helloWpf')) as字符串截取
--大小写转换
select lower('ABC') as 转小写,upper('abc') as 转大写
go批处理
go用于一条或多条SQL语句的集合,一个go批作为一个字符串交给服务器去执行;
使用go表示批,一个批出现错误,则批中国的语句将会被回滚。
try catch:
--try catch declare @num int set @num=1 begin try while @num <=10 begin print('num=' + convert(varchar(2),@num)) --insert into [dbo].[student] values('num' + convert(varchar(2),@num),@num,'男') set @num=@num + 1 end end try begin catch print('出现异常,进行异常处理') end catch go
示例二:进入异常
--try catch declare @num int set @num=1 begin try while @num <=10 begin --insert into [dbo].[student] values('num' + convert(varchar(2),@num),@num,'男') --缺少类型转换,进入catch异常捕获 insert into [dbo].[student] values('num' + @num,@num,'男') print('num=' + convert(varchar(2),@num)) set @num=@num + 1 end end try begin catch print('出现异常,进行异常处理') end catch go
求百分比
select * from ( select * from ( select count(*) as five from TableImageQuality where ImageScore = 5) five, (select count(*) as four from TableImageQuality where ImageScore = 4) four, (select count(*) as three from TableImageQuality where ImageScore = 3) three, (select count(*) as two from TableImageQuality where ImageScore = 2) two, (select count(*) as one from TableImageQuality where ImageScore = 1) one union all select (select count(*) as five from TableImageQuality where ImageScore = 5) * 100 / (select count(*) as five from TableImageQuality) five, (select count(*) as five from TableImageQuality where ImageScore = 4) * 100 / (select count(*) as five from TableImageQuality) four, (select count(*) as five from TableImageQuality where ImageScore = 3) * 100 / (select count(*) as five from TableImageQuality) three, (select count(*) as five from TableImageQuality where ImageScore = 2) * 100 / (select count(*) as five from TableImageQuality) two, (select count(*) as five from TableImageQuality where ImageScore = 1) * 100 / (select count(*) as five from TableImageQuality) one )retrunResult
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!