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