MSSQL 逻辑控制语句

--逻辑控制语句


--分支结构
--(1)IF-ELSE语句


--案例1：统计并显示2011-01-01的Java考试平均分
--如果平均分在70以上，显示“考试成绩优秀”，并显示前三名学生的考试信息
--如果在70以下，显示“考试成绩较差”，并显示后三名学生的考试信息


DECLARE @sid INT,@avg INT  
--1)根据课程名称找到课程编号
SELECT @sid=SubjectId FROM dbo.Subject WHERE SubjectName='java';
--PRINT @sid;
--2)统计并显示2011-01-01的Java考试平均分
SELECT @avg=AVG(StudentResult) FROM dbo.Result 
WHERE ExamDate='2011-01-01' AND SubjectId=@sid;
IF(@avg>=70)
BEGIN
PRINT '考试成绩优秀'
SELECT TOP 3 *FROM dbo.Result WHERE ExamDate='2011-01-01' AND SubjectId=@sid
ORDER BY StudentResult DESC;
END
ELSE
   BEGIN
PRINT '考试成绩较差'
SELECT TOP 3 *FROM dbo.Result WHERE ExamDate='2011-01-01' AND SubjectId=@sid
 ORDER BY StudentResult ASC;
END




--(2)CASE-END语句
--CASE 
--  WHEN 条件1 THEN  结果1
--  WHEN 条件2 THEN  结果2
--  ……
--  ELSE 其他结果
--END


--注意：ELSE：表示CASE中所有WHEN条件均不为 TRUE 时返回的结果 
--如果省略ELSE且WHEN条件都为FALSE时，CASE语句返回NULL


--案例2：采用美国ABCDE五级打分制显示学生Java课最近一次考试成绩
--A级:   90分以上
--B级： 80－89分
--C级:   70－79分
--D级： 60－69分
--E级： 60分以下


DECLARE @sid INT ,@date DATETIME
--1)根据课程名称找到课程编号
SELECT @sid=SubjectId FROM dbo.Subject WHERE SubjectName='java';
PRINT @sid;




--2）Java课最近一次的日期
SELECT @date=MAX(examdate) FROM dbo.Result WHERE SubjectId=@sid;


--3)学生Java课最近一次考试成绩
SELECT StudentNo 学号,StudentResult,成绩=
  CASE
    WHEN studentResult>=90  THEN 'A'
    WHEN studentResult>=80  THEN 'B'
    WHEN studentResult>=70  THEN 'C'
    WHEN studentResult>=60  THEN 'D'
    ELSE  'E'
  END 
 FROM dbo.Result WHERE SubjectId=@sid AND ExamDate=@date;




--学生表
SELECT *FROM dbo.Student;
--性别 0:男 1:女
SELECT studentName,Gender,性别=
  CASE 
    WHEN gender=0  THEN '男'
    ELSE  '女'
  end
 FROM dbo.Student;
 
 --分别统计男生女生是多少？
 SELECT *FROM student;
 
 --SELECT COUNT(1) FROM student WHERE Gender=0;
 --SELECT COUNT(1) FROM student WHERE Gender=1;
 
 SELECT gender 性别,COUNT(1) 个数 FROM student GROUP BY Gender;
 
 --case...end....分别统计男生女生是多少？








--循环结构
--WHILE语句
-- SQL中的WHILE语句
--WHILE （条件）
--  BEGIN
--    语句1
--    语句2
--    ……
--    BREAK
--  END


--1-10之间的数
  
  --1)初始值
  -- DECLARE @i INT=1;
  ----2)条件
  --WHILE(@i<=10)
  --BEGIN
  --  PRINT @i;
  ----3)改变初值(给变量赋值必须使用set or select)
  -- set @i=@i+1;  --?
  --END
  
--1-10之间的偶数和


 --1)初始值
   DECLARE @i INT=1,@sum INT=0;
  --2)条件
  WHILE(@i<=10)
  BEGIN
    --判断
    IF(@i%2=0) --(逻辑判断是=)
     set @sum=@sum+@i
  --3)改变初值(给变量赋值必须使用set or select)
   set @i=@i+1;  
  END
 PRINT @sum;


--1-10之间数,遇到5结束
  --1)初始值
   DECLARE @k INT=1;
  --2)条件
  WHILE(@k<=10)
  BEGIN
     IF(@k=5)
     BREAK;
    PRINT @k;
  --3)改变初值(给变量赋值必须使用set or select)
   set @k=@k+1;  --?
  END


--案例3：检查学生“C#”课最近一次考试是否有不及格（60分及格）的学生。
--如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格


--修改数据
--复制新表
--SELECT  * INTO myResult FROM dbo.Result


SELECT * FROM Result WHERE SubjectId=3; 
SELECT * FROM myResult ;
SELECT * FROM dbo.Subject;


DECLARE @sid INT ,@date DATETIME,@n INT;
--1)根据课程名称找到课程编号
SELECT @sid=SubjectId FROM dbo.Subject WHERE SubjectName='C#';
PRINT @sid;


--2）Java课最近一次的日期
SELECT @date=MAX(examdate) FROM dbo.myResult WHERE SubjectId=@sid;
PRINT @date;


WHILE(1=1)
begin
--3)“C#”课最近一次考试是否有不及格（60分及格）的学生的人数。
SELECT @n=COUNT(1) FROM myResult WHERE SubjectId=@sid AND ExamDate=@date AND StudentResult<60;
  IF (@n>0)
    UPDATE myresult SET StudentResult=StudentResult+2 
    WHERE SubjectId=@sid AND ExamDate=@date AND StudentResult<=95;
  ELSE
   BREAK;
END


--SELECT * FROM dbo.myResult;


--批处理是包含一个或多个 SQL 语句的组，从应用程序一次性地发送到SQL Server执行
--SQL Server 将每批SQL语句编译成一个执行单元，此单元称为执行计划




--案例4：为了督促学生学习，学校对多次考试不及格的学生给予处理。
--凡是一次考试不及格者，给予警告；
--三次以下（含）不及格者，肄业处理；
--三次以上不及格者，开除学籍




SELECT * FROM dbo.Result;


--第一步：创建新表Punish，表结构是：
--处罚记录（学号、不及格次数、处理意见）创建新表时处理意见为空
 IF EXISTS (SELECT * FROM sysobjects WHERE name='punish')
  DROP TABLE punish;
 CREATE TABLE punish
 (
   SID INT NOT NULL,
   scount INT NOT NULL,
   smsg VARCHAR(100)
 )
go
--第二步：查询所有不及格的成绩插入Punish表中
--表存在
INSERT INTO punish 
SELECT StudentNo,COUNT(1),'' FROM dbo.Result WHERE StudentResult<60
GROUP BY StudentNo


go
--第三步：根据每个学生不及格次数批量更新处理意见
UPDATE punish SET smsg='警告' WHERE scount=1
UPDATE punish SET smsg='肄业' WHERE scount=2
UPDATE punish SET smsg='开除' WHERE scount=3


go
SELECT * FROM dbo.punish;