第三章上机

打印直角三角形

declare @i int
			set @i=0
			declare @j int 
			set @j=0
			declare @str nvarchar(8)
			set @str=' '
			while(@i<=5)			
			begin		
			set @str+='*'	
			set @i+=1		
			while(@j<@i)
			begin			
			set @j+=1
			print @str
			end
			end
			

使用变量保存查询数据
DECLARE @NO int                                          -- 学号
SET @NO = 3
DECLARE @date datetime                             -- 出生日期
DECLARE @year int                                       -- 出生年份
-- 获得学号是20011的学生姓名和年龄
SELECT StudentName 姓名,
               FLOOR(DATEDIFF(DY, BornDate, GETDATE())/365) 年龄
   FROM student  WHERE StudentNo=@NO

-- 查询输出比学号是20011的学生大1岁和小1岁的学生信息
SELECT  @date=BornDate FROM Student   -- 使用SELECT赋值
  WHERE StudentNo=@NO
SET @year = DATEPART(YY, @date)             
SELECT *  FROM Student 
   WHERE DATEPART(YY,BornDate) = @year + 1
                 OR DATEPART(YY,BornDate) = @year - 1
GO

使用if  else 语句
DECLARE @name nvarchar(50)      --姓名
DECLARE @score decimal(5,2)       --分数
SELECT TOP 1  @score=StudentResult,@name=stu.StudentName 
   FROM Result r	
   INNER JOIN Student stu ON r.StudentNo=stu.StudentNo
   INNER JOIN Subject sub ON r.SubjectNo=sub.SubjectNo
   WHERE r.StudentNo='20012' AND sub.SubjectName='Java Logic'
   ORDER BY ExamDate DESC
PRINT '学生姓名:' + @name
IF (@score > 85)
  PRINT '考试等级:' + '优秀'
ELSE IF (@score > 70)
  PRINT '考试等级:' + '良好'
ELSE IF (@score > 60)
  PRINT '考试等级:' + '中等'
ELSE
  PRINT '考试等级:' + '差'

使用逻辑控制语句
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

使用批处理
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