use MyScool
--全局变量 = 系统变量
--print @@version--select @@VERSION--定义局部变量=自定义变量
--declare @ageint--赋值的方式 set select
--declare @ageint--set @age=18--print @age--赋值的方式 select
--declare @Snamevarchar(50)--select @Sname=sname from t_Student where SName='王莉莉'--print @sname--如果是常量值 用 set 如果是动态值 用select
--declare @Sgradeint--select @Sgrade=SGrade from t_Student where SName='王莉莉'--print @Sgrade--select * from t_Student where SGrade>@Sgrade
select * from t_Student
--declare @Snamevarchar(50),--@scode uniqueidentifier,--@scoreint--set @Sname='金蝶'--select @scode= scode from t_Student where SName=@Sname--select @score=Score from t_Score where StudentID=@scode--print 'Code '+convert(varchar(50),@scode)--print 'Score '+cast(@score as varchar(20))--declare @scoreAvgfloat--select @scoreAvg=AVG(score) from t_Score
--print @scoreavg--if(@scoreAvg>70)--begin
-- print '考试成绩优秀'-- select top 3* from t_Score order by Score desc
--end
--else--begin
-- print '考试成绩较差'-- select top 3* from t_Score order by Score asc
--end
--declare @countint--while(1=1)--begin
--select @count=COUNT(studentID) from t_Score where Score<61--print @count--if(@count>0)--begin
-- update t_Score set Score+=2 where Score<61--end
--else--begin
--break--end
--end
--declare @indexint,--@sumint=0--set @index=0--while(@index<101)--begin
-- set @sum=@index+@sum-- set @index+=1--end
--print 'SuM '+Cast(@sum as varchar(50))
select * from t_Score
--select score ,case--when Score=100 then '优秀'--when Score<100 and Score>90 then '良好'--when Score<91 and Score>80 then '及格'--else'菜鸡' end '中式打分'--from t_Score
--go
--where 条件列 就是子查询的 查询列
--如果子查询用的是 比较运算符 那么一定在子查询的语句中 要加上where
--select * from t_Student
--where SGrade<(select SGrade from t_Student where SName='金蝶 ')--select * from t_Student
--where SGrade<2,1,2,2,231,23--子查询替换表连接 inner join
--select * from t_Student
--inner join t_Score on t_Student.Scode=t_Score.StudentID
--select * from t_Student where Scode in (select StudentID from t_Score)--select * from t_Student where Scode not in (select StudentID from t_Score)--if exists (select * from t_Score where Score>80 and Score<100)--begin
-- update t_Score set Score+=2 where Score>80 and Score<100--end
--else--begin
-- update t_Score set Score+=5 where Score<80--end
--if not exists (select * from t_Score where Score>80 and Score<100)--begin
-- update t_Score set Score+=2 where Score>80 and Score<100--end
--else--begin
-- update t_Score set Score+=5 where Score<80--end
declare @iint=0,@jint=0,@zint=0,@gdecimal(10,4)=0
select @i=COUNT(scode) from t_Student
select @j=COUNT(scode) from t_Student where scode in(select StudentID from t_Score)
set @z=@i-@j
set @g=cast(@j as decimal(10,4))/@i
select @i'实到人数',@j'应到人数 ',@z'缺考人数',CAST(CAST((@g*100)as int) as varchar(50))+'%'
declare @avgint=0
select @avg=avg(score) from t_Score
print @avgif exists (select * from t_Score where Score<@avg)
begin
while(1=1)
begin
update t_Score set Score+=1 where Score<97if not exists(select * from t_Score where Score<97)
begin
break
end
end
end
else
begin
print '没有低于平均分的人'
end
select SName,Score,case
when Score>90 and Score<101 then '牛逼'else'未参加考试' end
from t_Student s
left join t_Score c on s.Scode=c.StudentID
select * from t_Score