3.SqlServer-深入T-SQL高级编程

1.局部变量的使用

--声明学号变量
declare @stuId int,@stuName varchar(20)

--查询小明的信息
set @stuName='小明'
select * from Students where StudentName=@stuName;

--查询与小明学号相邻的学员
select * from Students where StudentId=(@stuId+1) or StudentId=(@stuId-1);

2.set 与 select 的比较

declare @stuAddress nvarchar(100), @stuName nvarchar(100)

--set @stuAddress='合肥',@stuName='小红'      --已经报错了，不允许这样赋值
--select @stuAddress='合肥蜀山',@stuName='大明'  --允许这样赋值

--set @stuAddress=StudentAddress from Students;--不允许
--set @stuAddress=(select StudentAddress from Students);--不允许，因为得到的结果不止一个
--select @stuAddress= StudentAddress from Students;--可以

--set @stuAddress=(select StudentAddress from Students where 1<0);--null值
select @stuAddress= StudentAddress from Students where 1<0;--和原来一样

3.全局变量

-------------------------示例1--------------------------------
print '服务器的名称：'+@@servername;
print 'SqlServer的版本'+@@version;

select @@SERVERNAME AS '服务器名称';
select @@VERSION AS 'SqlServer 的版本';

----------------------------示例2---------------------------------
use StudentManageDB
go

--插入学员信息
insert into Students(StudentName,Gender,Age,Birthday,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
values('袁崇焕','男',20,'1990-05-01',342222199005014677,'15756008899','合肥市包河区',10)

--获取最后一条SQL语句的执行错误号
print @@error

4.数据类型转换

convert(类型,变量)

use StudentManageDB
go
--定义变量并查询
declare @sumScore int
select @sumScore=(CSharp+SQLServerDB) from ScoreList
where StudentId=100003

print '学号=100003总成绩：'+convert(varchar(20),@sumScore)

--------------------两种不同类型转换比较------------------------------
--使用CAST转换
select StudentName + '的出生日期是：'+CAST(Birthday as varchar(50)) AS '学生信息' 
from Students where StudentId =100005;
--使用 CONVERT
select StudentName +'的出生日期是：'+CONVERT(varchar(50),Birthday,120) AS '学生信息'
from Students where StudentId=100005;-- 120 : 样式号，这里是为一种日期格式

5.T-SQL中的其他函数

use StudentManageDB
go
--定义变量
declare @birthday datetime,@days int, @age int
--查询出生日期
select @birthday=Birthday from Students where StudentId=100002;
--计算出生天数
set @days = datediff(DAYOFYEAR,@birthday,GETDATE());
--计算年龄
set @age=FLOOR(@days/365);

print '年龄为：'+convert(varchar(20),@age);

--直接查询
select floor(datediff(dy,Birthday,getdate())/365) 年龄 
from Students where StudentId=100002

6.if-else 语句

use StudentManageDB
go
--查询成绩
declare @cAvg int
select @cAvg=avg(CSharp) from ScoreList s1
inner join Students s2 on  s1.StudentId=s2.StudentId 
where ClassId=1;
print 'C#平均成绩：' + convert(varchar(20),@cAvg);

if(@cAvg >= 80)
   print '成绩优秀'
else
   print '成绩一般'

7.while语句

use StudentManageDB
go
print '加分之前的C#成绩：'
select StudentId,CSharp from ScoreList

declare @CSharp int, @Stuid int
while(1=1)  --一定要对齐，良好的代码规范
    begin
        select top 1 @CSharp=CSharp,@Stuid=StudentId  --找出第一个小于60分
                from ScoreList where CSharp<60
        if(@CSharp<60)
            update ScoreList set CSharp=CSharp+1     --小于60就加1
                where StudentId=@Stuid
        if((select count(*) from ScoreList where CSharp<60)=0) --全部大于60  就跳出
            break
    end
print '加分之后的C#成绩'
select StudentId,CSharp from ScoreList

8.case-end语句示例

use StudentManageDB
go
select 学号=StudentId,
    总评=CASE    
                when (CSharp+SQLServerDB)/2 >= 90 then 'A'
                when (CSharp+SQLServerDB)/2 between 80 and 89 then 'B'
                when (CSharp+SQLServerDB)/2 between 70 and 79 then 'C'
                when (CSharp+SQLServerDB)/2 between 60 and 69 then  'D'
                else '不及格'
         end
from ScoreList;