MySchool第三章

使用批处理

USE MySchool  
GO
IF  EXISTS(SELECT * FROM  sysobjects  WHERE  name ='Admin')
	DROP TABLE Admin
GO
CREATE TABLE Admin(  --创建表
	[LoginId]  [nvarchar](50) NOT NULL,
	[LoginPwd] [nvarchar](50) NOT NULL
)

ALTER TABLE Admin    --添加主健约束
ADD CONSTRAINT PK_Admin PRIMARY KEY (LoginId)
GO
INSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST1','123')    --插入数据
INSERT INTO Admin([LoginId],[LoginPwd]) VALUES('TEST2','123456') --插入数据
GO
UPDATE Admin SET [LoginPwd]='1234567' WHERE [LoginId]='TEST2' --更新数据
GO

逻辑控制语句

DECLARE @date datetime  --考试时间
DECLARE @subNO int      --课程编号
SELECT @subNO=SubjectNo FROM Subject
WHERE SubjectName='C# OOP'
SELECT  @date=max(ExamDate) FROM Result 
WHERE SubjectNo=@subNO

PRINT '加分前学生的考试成绩如下：'
SELECT 学号=StudentNo,成绩等级=CASE  
								 WHEN StudentResult BETWEEN 0 AND 59 THEN '你要努力了！！！'
								 WHEN StudentResult BETWEEN 60 AND 69 THEN '★'
								 WHEN StudentResult BETWEEN 70 AND 79 THEN '★★'
								 WHEN StudentResult BETWEEN 80 AND 89 THEN '★★★'
								 ElSE '★★★★'
                                END  
       FROM Result 
       WHERE SubjectNo=@subNO AND ExamDate=@date 

DECLARE @n int
WHILE(1=1) --条件永远成立
   BEGIN
    SELECT @n=COUNT(*) FROM Result 
    WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<60 --统计不及格人数
    IF (@n>0)
       UPDATE Result SET StudentResult=StudentResult+2 FROM Result
       WHERE SubjectNo=@subNO AND ExamDate=@date AND StudentResult<=98
  --每人加2分
    ELSE
       BREAK  --退出循环
  END