sql T-Sql

--------------------------------
---------T-SQL------------------
--------------------------------
use stuDB
--局部变量(查找里斯的信息)
declare @name varchar(8)
set @name='里斯'
select * from stuInfo where stuName=@name
--局部变量(查找里斯同桌)
declare @name varchar(8)
set @name='里斯'
declare @stuSeat smallint
select @stuSeat=stuSeat from stuInfo where stuName=@name
select * from stuInfo where stuSeat=@stuSeat-2 or stuSeat=@stuSeat+2
--(if-else)
declare @avg float
select @avg=avg(writtenExam) from stuMarks
print '班级平均成绩:'+convert(varchar(5),@avg);
if(@avg>60)
 begin
  print '班级成绩优秀,前三名:'
  select top 3 * from stuMarks order by writtenExam desc
 end
else
 begin
  print '班级成绩较差,后三名'
  select top 3 * from stuMarks order by writtenExam asc
 end
--(while)
select * from stuMarks
declare @num int
while(1=1)--一直运行直到所有的writtenExam都大于等于60
 begin
  select @num=count(*) from stuMarks where writtenExam<60
  if(@num>0)
   begin
    update stuMarks set writtenExam=writtenExam+2
   end
  else
   begin
    break
   end
 end
--(case)
select * from stuMarks
print 'ABCDE五级显示成绩如下:'
select stuNo,笔试成绩=case
  when writtenExam<60 then 'e'
  when writtenExam>=60 and writtenExam<=69 then 'd'
  when writtenExam between 70 and 79 then 'c'
  when writtenExam between 80 and 89 then 'b'
  else 'a'
 end
from stuMarks
select * from stuInfo
--获取年龄比里斯大的学员
--T-Sql
declare @age int
select @age=stuAge from stuInfo where stuName='里斯'
select * from stuInfo where stuAge>@age
go
--子查询
select * from stuInfo where stuAge>(select stuAge from stuInfo where stuName='里斯')
go
--获取笔试、机试都通过的学员
--表连接
select stuInfo.stuNo,stuInfo.stuName,stuMarks.writtenExam,stuMarks.labExam from stuInfo 
inner join stuMarks 
on stuInfo.stuNo=stuMarks.stuNo
where stuMarks.writtenExam>=60 and stuMarks.labExam>=60
go
--子查询
select * from stuInfo where stuNo in 
(select stuNo from stuMarks where writtenExam>=60 and labExam>=60)
go
select * from stuInfo where stuNo not in
(select stuNo from stuMarks where writtenExam<60 or labExam<60)
go