SQL进阶-(1)

--CASE函数用法--

--语法：

--1.Case...end会生成一个新列，作用是判断你指定字段的值或者范围，然后得到一个用户自定义的对

 

应的值

--2.它并不会修改原始的表数据，而只是修改结果集的显示

--3.case可以做等值判断，也可以做范围判断。

--4.做等值判断的语法：

--case 字段或者表达式

--  when 具体的值 then '用户自定义值'

--  else 上面的when都不满足就满足else

--end

--5.做等值判断的时候不能判断null值

select StudentNo,StudentName,

case ClassId+1 --当case后面接有字段或者表达式说这个case只能做行等值判断

 when 1 then '一期班'

 when 2 then '二期班'

  else '我不知道'

end,--case会生成一个新列，所以如果Case..end后面还有其它列，就必须先使用，进行分割

case email

 when  NULL  then '没有填写'

 else email

end as 你要的列名

 from Student

--case ..end做范围判断

--语法：

--case --没有接任何的列名或者表达式

-- when 表达式 then 值

-- 。。。。

-- else  值  

--end

--then后面的值的类型需要可以互换

--when后面的表达式使用的字段可以是任意的，并不要求是同一个

select StudentName,

case

  when BornDate>'2000-1-1' then '小屁孩' --如果不满足这个when相当于隐藏一个条件转到下一个

 

when

  when BornDate>'1990-1-1' then '小青年'

  when BornDate>'1980-1-1' then '小中年'

  when sex='男' then '他是男的'

end,

case

 when Email is null then '没有填写'

 else Email  

end

 from Student

 

--百分制转换为素质教育

select StudentNo,

case

 when StudentResult>=90 then 'A'

 when StudentResult>=80 then 'B'

 when StudentResult>=70 then 'C'

 when StudentResult>=60 then 'D'

 when StudentResult is  null then '没有考试'

 else  'E'

end AS 成绩

 from Result

 

 --IF ELSE
--1.也有多重和嵌套
--2.没有｛｝，用begin..end替代
--3.if结构里面必须有语句做处理
--4.没有所谓的true/false
--5.()可以省略
--if(1<>1)
-- begin
--  print 'aa'
--  print 'bb'
-- end
go
--计算office平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @subjectName nvarchar(50)='office'--科目名称
declare @subjectId int=(select SubjectId from Subject where SubjectName=@subjectName)--科目ID
declare @avg int--平均分
select @avg=(select AVG(StudentResult) from Result where SubjectId=@subjectId and studentresult is not null)
if @avg>=60
 begin
 print '成绩不错。输入前三名：'
 select top 3 * from Result where SubjectId=@subjectId order by StudentResult desc
 end
else
 begin
 print '成绩不好。输入后三名：'
 select top 3 * from Result where SubjectId=@subjectId order by StudentResult asc 
 end  

 

--WHILE循环--

--1.不有写true/false

--2.没有｛｝，只有begin..and

--3.可以嵌套

--4.也可以在循环体写continue/break,continue可以中止当前这一次，继续下一次，break是跳出当前这一层循环

 

---计算1-100之间所有奇数的和--

declare @i int=1

declare @sum int=0

while(@i<=100)

begin

 if(@i%2!=0)

begin

set @sum+=@i

end

 set @i+=1

end

print '和='+cast(@sum as char(4))

 

go

--如果office不及格的人超过半数(考试题出难了)，则给每个人增加2分,循环加，直到不及格的人数少于一半。

declare @subjectName nvarchar(50)='office'--科目名称

declare @subejctId int=(select subjectId from Subject where SubjectName=@subjectName)--科目ID

declare @totalNum int --总人数

set @totalNum=(select COUNT(*) from Result where SubjectId=@subejctId)

declare @unpassNum int---没有及格人数

select @unpassNum =COUNT(*) from Result where SubjectId=@subejctId and StudentResult<70

print @totalnum

print @unpassnum

while(@totalNum/2<@unpassNum)

begin

update Result set StudentResult+=5 where SubjectId=@subejctId and StudentResult <=95

--再一次计算不及格人数

select @unpassNum =COUNT(*) from Result where SubjectId=@subejctId and StudentResult<70

end

 

go

declare @subjectName nvarchar(50)='office'--科目名称

declare @subejctId int=(select subjectId from Subject where SubjectName=@subjectName)--科目ID

declare @totalNum int --总人数

set @totalNum=(select COUNT(*) from Result where SubjectId=@subejctId)

--declare @unpassNum int---没有及格人数

--select @unpassNum =COUNT(*) from Result where SubjectId=@subejctId and StudentResult<70

--print @totalnum

--print @unpassnum

while(1=1)

begin

if((select COUNT(*) from Result where SubjectId=@subejctId and StudentResult<70)>@totalNum/2)

update Result set StudentResult+=4 where SubjectId=@subejctId and StudentResult<=96

else

break

end

 

 

---变量赋值

---局部变量--

--语法

--declare @名称 类型=初始值

declare @name varchar='aaaa' --如果字符串没有指定长度，那么长度就是1

print @name

--两种赋值方式:在sql中为变量赋值必须使用set/select,而不能直接使用  变量=值

--set:侧重于直接给一个具体的值

--select:侧重于通过查询赋值

go

--查询比林思大的学员信息

select * from Student where BornDate<(select BornDate from Student where StudentName='林思')

go

declare @time datetime

--set后面如果是sql语句，那么必须接完整的 独立子查询

--set @time=(select  BornDate from Student where StudentName='林思')

--使用select赋值，如果后面是sql语句，可以是独立子查询，也可以省略select关键字

select @time=  BornDate from Student where StudentName='林思'

print @time

--使用set/select赋值的共同点

--1.都能直接给值

--2.如果后面的sql语句是一个完整Sql语句，那么两者没有任何的区别

go

--区别：

declare @age int=100,@name varchar(50)='aaa'

--1.set一次只能为一个变量赋值,而select可以一次为多个变量赋值

--set @age=20 ,@name='张三'

--select @age=20 ,@name='张三'

--2.set进行赋值的时候如果sql语句返回多行一列值，那么：子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。但是如果是select赋值且sql语句省略了select.那么会得到最后一个值

--set @name=(select StudentName from Student)

--select @name= StudentName from Student

--3.如果sql语句返回null值，那么set会成为null值而select会保留原始值

--set @name=(select StudentName from Student where StudentNo=100)

select @name= StudentName from Student where StudentNo=100

print @name

print @age

 

 

--两种输出方式：

--select:以结果集的方式输出

--print :以文本形式输出,每一个print单独占据一行,且永远只能输出一个值,它是在服务器端输出的

select * from Student

print 'aa' +'bb'

 

--查询参加最近一次“office”考试成绩最高分和最低分

select MAX(StudentResult),MIN(StudentResult) from Result

where  SubjectId=(select subjectid from subject where subjectname='office') and ExamDate=(select max(examdate) from result where subjectid=(select subjectid from subject where subjectname='office'))

declare @subjectname nvarchar(50)='office' --科目名称

go

declare @subjectname nvarchar(50)='office' --科目名称

declare @subjectId int --科目 ID

set @subjectId=(select subjectid from subject where subjectname=@subjectname)

declare @time datetime --最近一次考试日期

select @time=MAX(examdate) from Result where SubjectId=@subjectId

--查询

select MAX(StudentResult),MIN(StudentResult) from Result where SubjectId=@subjectId and ExamDate=@time