一些常用SQL语句大全-下

----------------while循环语句-------------
declare @n int
while(1=1)--条件永远成立
begin
select @n=count(*) from stuMarks where writtenExam<60 --统计不及格的人数
if(@n>0)
update stuMarks set writtenExam=writtenExam+2 --每人加2分
else
break --退出循环
end
print '加分后的成绩如下：'
select * from stuMarks
-----------------case多分支语句-------------
select * from stuMarks --原始数据
print 'ABCDE五级显示成绩如下：'
select stuNo,成绩=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 stuMarks
-----------------go批处理语句------------------
use Master
go
create table stuInfo
(
id int not null,
name varchar(20)
)
go
-----------------in和not in子查询--------------------
in查询：
select stuName from stuInfo
where stuNo in (select stuNo from stuMarks where writtenExam=60)
not in查询：
select stuName from stuInfo
where stuNo not in (select stuNo from stuMarks)
go
------------------exists和not exists子查询------------
exists子查询:
1、if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
go
2、if exists(select * from stuMarks where writtenExam>80)
begin
print '本班有人笔试成绩高于80分，每人只加2分，加分后的成绩如下：'
update stuMarks set writtenExam=writtenExam+2
select * from stuMarks
end
else
begin
print '本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩如下：'
update stuMarks set writtenExam=writtenExam+5
select * from stuMarks
end
go
not exists子查询:
if not exists(select * from stuMarks where writtenExam>60 and labExam>60)
begin
print '本班无人通过考试，试题偏难，每人加3分，加分后的成绩如下：'
update stuMarks set writtenExam=writtenExam+3，labExam=labExam+3
select * from stuMarks
end
else
begin
print '本班考试成绩一般，每人只加1分，加分后的成绩如下：'
update stuMarks set writtenExam=writtenExam+1，labExam=labExam+1
select * from stuMarks
end