SqlServer 编程 高级查询
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
