use MyScool
--全局变量 = 系统变量
--print @@version
--select @@VERSION
--定义局部变量=自定义变量
--declare @age int
--赋值的方式 set select
--declare @age int
--set @age=18
--print @age
--赋值的方式 select
--declare @Sname varchar(50)
--select @Sname=sname from t_Student where SName='王莉莉'
--print @sname
--如果是常量值 用 set 如果是动态值 用select
--declare @Sgrade int
--select @Sgrade=SGrade from t_Student where SName='王莉莉'
--print @Sgrade
--select * from t_Student where SGrade>@Sgrade
select * from t_Student
--declare @Sname varchar(50),
--@scode uniqueidentifier,
--@score int
--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 @scoreAvg float
--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 @count int
--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 @index int,
--@sum int=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 @i int=0,@j int =0 , @z int=0,@g decimal(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 @avg int=0
select @avg=avg(score) from t_Score
print @avg
if exists (select * from t_Score where Score<@avg)
begin
while(1=1)
begin
update t_Score set Score+=1 where Score<97
if 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
