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字符串截取
--大小写转换
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