015 数据库学习笔记--变量 + if else + 循环 + 算术运算符 + substring + len + go + try catch + 求百分比

变量赋值 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字符串截取<br>--大小写转换<br>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