数据库实验七——T-SQL编程

实验任务

• 流程控制语句
  • 查询学号为200102的学生的各科成绩，如果这个学生没选课，就显示“此学生没选课”；如果没有这个学生，就显示“没有此学生”
  • 查询所有学生的考试等级，包括学生号，课程号和成绩等级（A、B、C、D、E）。（[90,100]—A, [80,90)—B,[70,80)—C, [70,80)—C, [60,70)—D, [0,60)—E）
• 自定以函数
  • 定义一个函数，求x学年y门课程的选修学生人数，然后，调用此函数。
  • 定义一个函数，查询某个班各个学生的平均成绩。然后，调用此函数。
• 游标：声明一个游标，查询student表中所有男生的信息，并读取数据。要求：
  • 读取最后一条记录
  • 读取第一条记录
  • 读取第3条记录
  • 读取当前记录指针位置后第2条记录

实验过程

Step1：查询学号为200102的学生的各科成绩，如果这个学生没有选课，就显示“此学生没有选课”；如果没有这个学生，就显示“没有此学生”

IF EXISTS (SELECT * FROM stu_choose_lesson WHERE studentNum = '200102')
	SELECT * FROM stu_choose_lesson WHERE studentNum = '200102'
ELSE 
	IF EXISTS (SELECT * FROM student WHERE studentNum = '200102')
		PRINT '此学生没有选课'
	ELSE
		PRINT '没有此学生'

Step2：查询所有学生的考试等级，包括学生号，课程号和成绩等级（A、B、C、D、E）

SELECT studentNum, lessonNum, score_level = 
	CASE 
		WHEN score >= 90 THEN 'A'
		WHEN score >= 80 THEN 'B'
		WHEN score >= 70 THEN 'C'
		WHEN score >= 60 THEN 'D'
		WHEN score < 60  THEN 'E'
	END
FROM stu_choose_lesson

Step3：定义一个函数，求x学年y门课程的选修学生人数，然后，调用此函数

GO
CREATE FUNCTION chooseNum (@x date, @y varchar(8)) 
	RETURNS int AS BEGIN
	DECLARE @cnt int 
	SET @cnt = (SELECT COUNT(*) FROM lesson le, stu_choose_lesson sc
		WHERE sc.lessonNum= @y AND le.lessonNum = sc.lessonNum AND le.year = @x)
	RETURN @cnt
	END
GO

DECLARE @y date
DECLARE @x varchar(8)
SET @y = '2013'
SET @x = 1
SELECT dbo.chooseNum(@y, @x) AS 'y学年x门课被选次数'

Step4：定义一个函数，查询某个班各个学生的平均成绩。然后，调用此函数

GO 
CREATE FUNCTION stu_avg_score(@classNum tinyint)
	RETURNS table AS
	RETURN (SELECT st.studentNum, AVG(score) AS 平均成绩 FROM stu_choose_lesson sc, student st
		WHERE st.class = @classNum AND st.studentNum = sc.studentNum
		GROUP BY st.studentNum) 
GO

SELECT * FROM student

DECLARE @classNum  tinyint
SET @classNum = 1
SELECT * FROM dbo.stu_avg_score(@classNum)

Step5：声明一个游标，查询student表中所有男生的信息，并读取数据。

DECLARE diy_cursor CURSOR STATIC FOR
	SELECT * FROM student WHERE sex = '男'
OPEN diy_cursor

Step6：读取最后一条记录

FETCH LAST FROM diy_cursor 

Step7：读取第一条记录

FETCH FIRST FROM diy_cursor  

Step8：读取第三条记录

Step9：读取当前记录指针位置后两条记录

第二次读取游标的时候，结果为空，因为游标中仅有四条记录，游标再往后移动时会指向一个空的集合

实验七Over