ms sql 学习应用

使用T-sql的一般查询应用

go
declare @age int
  select @age=stuAge from  stuInfo where stuName='小白'
  select * from stuInfo where stuAge >@age
go

使用t-sql的子查询

select * from stuInfo where stuAge >( select stuAge from stuInfo where stuName='小白')
select examNo,stuNo,writtenexam ,labexam ,identity(int ,1,1) as id into stuMark
  from stuMarks

 

go
select * from stuMark

 

使用内连接查询

go

select s.stuNo,s.stuName,s.stuSex,s.stuAge,s.stuseat, s.stuaddress,c.examno ,c.writtenexam ,
   c.labexam from stuinfo as s inner join stumark as c
   on (s.stuNo=c.stuNo)

go

use studb

go
 if exists(select * from stumarks where writtenexam>80)
   begin
           print('本班的成绩高80分每人只加2分加分后成绩如下：');
           update stumarks set writtenexam=writtenexam+2
   end
 else
   begin
                print'本班的成绩小于80分每人添加5分添加后成绩为：'
              update stumarks set writtenexam=writtenexam+5
   end
 select * from stumarks;

select ((select count(*) from stuinfo) -(select count(*) from stumarks))

 

 

一个复杂的查询应用

declare @avgwritten int,
        @avglab     int,
        @maxlab     int
 select @avgwritten=avg(writtenexam) from stumarks
 select @avglab=avg(labexam) from stumarks
if @avgwritten>@avglab
begin
   while(1=1)
   begin
     update stumarks set labexam=labexam+1
     select @maxlab=max(labexam) from stumarks
       if @maxlab>90    
          break;
   end
  
end
else
begin
    while(1=1)
    begin
           update stumarks set writtenexam=writtenexam+1
           if ((select max(writtenexam) from stumarks)>90)
            break
    end
end

select * from stumarks