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