MSSQL变量与赋值

----定义一个变量 @stuNumber,用来存储学生的数量
--方法01 :使用 Set
Declare @stuNumber int 
Set @stuNumber=(Select Count(*) from LibraryDB.dbo.student)
Select @stuNumber As '学生数量'

go  ---一个批处理的结束和另一个批处理的开始， 在批处理中变量名称不能重复

--方法02 :使用 Select
Declare @stuNumber int 
Select @stuNumber=Count(*) from LibraryDb.dbo.Student
Select @stuNumber As '学生数量'

go
--方法03 
Declare @stuNumber int = (Select Count(*) from LibraryDB.dbo.student)
Print '学生数量:'+ CAST(@stuNumber AS char(5))

---声明多个变量
Declare @num01 int,@num02 int,@num03 int
Select @num01

--任何值和Null运算结果都是Null

--------打印年龄最大值，最小值、平均值 
--Set 
Declare @max int,@min int,@avg int
Set @max =(Select Max(Sage) from LibraryDB.dbo.Student)
Set @min =(Select Min(Sage) from LibraryDB.dbo.Student)
Set @avg =(Select AVG(Sage) from LibraryDB.dbo.Student)

Print '最大值：'+ Cast(@max As char(4)) + char(13) +
      '最小值：'+ Cast(@min As char(4)) + char(13) +
      '平均值：'+ Cast(@avg As char(4)) 
go
--Select 
Declare @max int,@min int,@avg int 
Select @max=max(sage), @min=min(sage),@avg=avg(sage)
from LibraryDB.dbo.Student
Print '最大值：'+ Cast(@max As char(4)) + char(13) +
      '最小值：'+ Cast(@min As char(4)) + char(13) +
      '平均值：'+ Cast(@avg As char(4)) 
go

--------Select 和 Set区别 
Declare @num02 int 
Select @num02 =IsNull(sage,0)  from LibraryDB.dbo.Student Order by SAGE 

Print @num02
Select @num02

go

--打印出年龄介于20-30岁之间的所有学生姓名
Declare @stuName varchar(200) ='',@stuNumber int 
Select @stuName=@stuName+SName+'  ' from LibraryDB.dbo.Student Where Sage Between 20 And 30
Set @stuNumber=(Select count(*) from LibraryDb.dbo.Student Where Sage Between 20 And 30)
Print '年龄介于20-30岁之间的所有学生姓名：'+char(13)+@stuName+char(13)+ '共计:'+CAST(@stuNumber As varchar(3))+'人'
go

----Set /Select 
Declare  @num01 int=100 ,@num02 int = 200
Set @num01=(Select SAge from LibraryDB.dbo.Student Where 1=0)
Select @num02=Sage from LibraryDB.dbo.Student Where 1=0
Select @num01
Select @num02
----如果变量有初始值，如果使用Set赋值，无论赋值的结果时什么，都会把这个结果强行给这个变量；如果用Select进行赋值，只有值不等于null才能完成赋值
go

Declare @Outer int =100
Begin 
    Declare @inner int =200
    Select @outer,@inner
End
Select @outer,@inner
----变量的有效期时通过 go来分割的
go

------Varchar  / char
declare @str01 char(100)
set @str01 = 'Steven'
Set @str01 +='wang'
print @str01

declare @str02 varchar(100)
set @str02 = 'Steven'
Set @str02 +='wang'
print @str02
--varchar支持字符串的拼接，而char不可以