第十节 case....end

--case...end

--ABCDE显示成绩

select学号=StuID,成绩=case

  when WrittenExam<60 then'E'

  when WrittenExam between 60 and 69 then'D'

  when WrittenExam between 70 and 79 then'C'

  when WrittenExam between 80 and 89 then'B'

  else

     'A'

end

from StuMark


select学号=StuID,笔试成绩=WrittenExam,

机试成绩=LabExam,平均分=(WrittenExam+LabExam)/2,

成绩=case

 when(WrittenExam+LabExam)/2<60 then'不及格'

 when(WrittenExam+LabExam)/2 between 60 and 69 then'差'

 when(WrittenExam+LabExam)/2 between 70 and 79 then'中'

 when(WrittenExam+LabExam)/2 between 80 and 89 then'良'

 else'优'

end

   from StuMark


--传统写法

declare @age int

select @age=age from StuInfo where StuName='张三'

select*from StuInfo where Age>@age


--子查询

--子查询先执行"()"里面的查询再去执行父查询

--子查询中如果使用的是比较运算符请确保它的返回值是一个

select*from StuInfo where age>     

(select Age from StuInfo where StuName='张三')

--常见的错误：

--子查询和结果集比较

select*from StuInfo where age>     

(select*from StuInfo where StuName='张三')

--子查询返回多个值,无法比较

select*from StuInfo where age>     

(select age from StuInfo)

--内联接

select s.StuName from StuInfo s

innerjoin stuMark m on s.ID=m.stuID

where m.WrittenExam=60


--使用子查询替换内联接

select StuName from StuInfo where ID=

(select StuID from StuMark where WrittenExam=60)


--子查询用于删除

deletefrom StuInfo where age>

(select age from StuInfo where StuName='王武')

--子查询用于更新

update StuInfo set Address='浙江金华'

where Age<(select age from StuInfo where StuName='王武')

--子查询用于插入

insertinto StuInfo(StuName,Age)

select'李',

(select age from StuInfo where StuName='王武')