第十一节 sql语句：比较运算符=<>等

--比较运算符:=、!=、<、<=、>、>=子查询结果必须为个

--如果子查询结果大于,请使用in关键字

select*from StuInfo where ID in

(select StuID from StuMark where WrittenExam=60)


--查询参加考试的学生信息

select*from StuInfo where ID in

(select StuID from StuMark)

--查询未参加考试的学生信息

select*from StuInfo where ID notin

(select StuID from StuMark)


--如果exists括号内的子查询有结果为true否则为false

ifexists(select*from StuMark where WrittenExam>80)

begin

  update StuMark set WrittenExam=WrittenExam+2

  where WrittenExam+2<=100

end

else

 begin

  update StuMark set WrittenExam=WrittenExam+5

  where WrittenExam+5<=100

 end

select*from StuMark

go


--not exists

ifnotexists(select*from StuMark

 where WrittenExam>60 and LabExam>60)

begin

  update StuMark set WrittenExam=WrittenExam+3,

  LabExam=LabExam+3

end

else

  begin

     update StuMark set WrittenExam=WrittenExam+1,

     LabExam=LabExam+1

  end

select*from StuMark

go