sql数据库第八课

--传统写法
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
inner join stuMark m on s.ID=m.stuID
where m.WrittenExam=60

--使用子查询替换内联接
select StuName from StuInfo where ID=
(select StuID from StuMark where WrittenExam=60)

--子查询用于删除
delete from StuInfo where age>
(select age from StuInfo where StuName='王武')
--子查询用于更新
update StuInfo set Address='浙江金华'
where Age<(select age from StuInfo where StuName='王武')
--子查询用于插入
insert into StuInfo(StuName,Age)
select '李',
(select age from StuInfo where StuName='王武')
--比较运算符:=、!=、<、<=、>、>=子查询结果必须为个
--如果子查询结果大于,请使用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 not in
(select StuID from StuMark)

 

if exists (子查询)
if exists (select * from stumark where writtenexam>90)
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
if not exists (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

select 应到人数=(select count(*)from stuInfo),
实到人数=(select count(*)from stumark),
缺考人数=(select count(*)from stuInfo)-(select count(*)from stumark)

if exists (select * from sysobjects where name='newtable')
drop table newTable

select s.stuName,m.writtenexam,m.labexam,s.ID,
ispass=case
when writtenexam>=60 and labexam>=60 then 1
else 0
end
into newTable from stuInfo s left join
stumark m on s.ID=m.stuID
go
select * from newTable

declare @writtenAvg int
declare @labAvg int

select @writtenAvg=avg(writtenexam),
@labAvg=avg(labexam) from newTable
where writtenexam is not null
or labexam is not null

if @writtenAvg<@labAvg
begin
 while(1=1)
 begin
 update newTable set writtenexam=writtenexam+1
 if (select max(writtenexam) from newTable)>=97
 break
end
end
else
 begin
  while(1=1)
  begin
  update newTable set labexam=labexam+1
  if (select max(labexam) from newTable)>=97
  break
 end
end
go
select * from newTable

update newTable set ispass=case
 when writtenexam>=60 and labexam>=60 then 1
 else 0
end
select * from newTable
go

select 姓名=stuName,笔试成绩=case
when writtenexam is null then '缺考'
else convert(varchar(5),writtenexam)
end,
机试成绩=case
when labexam is null then '缺考'
else convert(varchar(5),labexam)
end,
是否通过=case
when ispass=1 then'通过'
else '不通过'
end
from newTable

select 总人数=count(*),通过人数=sum(ispass),
通过率=convert(varchar(50),avg(ispass*100))+'%'
from newTable

转载于:https://www.cnblogs.com/xyb1117/archive/2012/03/07/2383710.html