ms sql 学习应用

最新推荐文章于 2024-09-19 21:52:11 发布

最新推荐文章于 2024-09-19 21:52:11 发布 · 51 阅读

文章标签：

#SQL #Go #C #C++ #C#

本文介绍了T-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

使用内连接查询

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)

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