数据库实验——SQL高级应用

SQL高级应用 

一、实验目的

1、了解T-SQL变量的使用方法

2、掌握T-SQL各种运算符、控制语句的功能及使用方法

3、掌握常用系统函数的调用方法

4、掌握用户自定义函数的使用

5、掌握存储过程的定义和执行方法

6、了解触发器的定义和执行方法

二、实验内容与步骤

自学教材第6章中6.2.3、6.2.4和6.3小节，完成本次实验内容。

在StudentCourse数据库上，用SQL语句实现下面操作：

1、局部变量的使用

（1）声明一个局部变量avggrade，并将StuCourse表中成绩的平均分赋值给变量avggrade（分别用set和select两种赋值方式）。（语法可参考教材例6.1）

（2）声明一个局部变量i并赋值为70，查询成绩大于i的学生学号和选修的课程号。

2、控制语句

（1）if语句：如果StuCourse表中所有学生的平均成绩大于80，显示'成绩优异'，以及成绩大于80的选课记录；否则显示'成绩一般'。（if语句的使用可参考教材例6.5）

（2）while语句：使用while循环，对course表中的学分总和进行检查，若学分总和小于50，则对每门课程学分加1，直到学分总和不小于50为止。（while语句的使用可参考教材例6.7）

3、系统函数

（1）返回当前日期的年月日

执行： select getdate() as 当前时间

（2）在update语句中使用@@rowcount全局变量来检测是否存在发生更改的记录

执行：update  student   

set 总学分=90   

where总学分=70

if @@rowcount=0

print  '警告：没有发生记录更新'

（2）完成教材实验7实验内容3（4）~（6）

4、用户自定义函数

（1）标量函数：编写用户自定义函数，要求根据输入学号，返回某学生的总分数（语法可参考教材例6.9），并使用SELECT和EXEC两种方式调用执行该函数（语法可参考教材例6.10）。

（2）内嵌表值函数：编写用户自定义函数，查询出选修某课程的选课信息，并调用执行该函数。（语法可参考教材例6.11）

（3）多语句表值函数：编写用户自定义函数，查询出给定某个系的学生平均成绩，并调用执行该函数。（语法可参考教材例6.12）

5、存储过程

（1）（无参数）用命令方式创建并执行一个存储过程，查询成绩不及格的学生的学号姓名专业、课号课名及相应分数。（语法格式可参考教材例6.13）

（2）（带输入参数）用命令方式创建并执行一个的存储过程，该存储过程带有输入参数@stu_no，从选课表中查询学号是@stu_no的学生的选课信息。（语法格式可参考教材例6.14）

（3）（带输入参数和输出参数）用命令方式创建并执行一个的存储过程，该存储过程带有输入参数@stu_no和输出参数@stu_avg，从选课表中查询学号是@stu_no的学生的成绩平均分并赋值给输出参数@stu_avg。（语法格式可参考教材例6.16）

6、触发器

（1）完成教材实验8内容3（注：rollback表示回滚，表示取消刚刚进行的操作）；

验证触发器：分别执行

INSERT INTO StuCourse VALUES（'070208','1001',70）

INSERT INTO StuCourse VALUES（'070110','2001',70）

插入是否能够成功？为什么？

（2）教材实验8思考与练习3定义触发器Ctrig 实现约束：课程的学分不能小于2，如果小于则自动改为2。自行对触发器进行验证。

实验内容

在StudentCourse数据库上，用SQL语句实现下面操作：

1、局部变量的使用

（1）声明一个局部变量avggrade，并将StuCourse表中成绩的平均分赋值给变量avggrade（分别用set和select两种赋值方式）。（语法可参考教材例6.1）

语句：

DECLARE @avggrade REAL
SET @avggrade = (SELECT AVG(成绩) FROM StuCourse)
PRINT @avggrade

DECLARE @avggrade REAL
SELECT @avggrade = AVG(成绩) FROM StuCourse
PRINT @avggrade

结果：

 

（2）声明一个局部变量i并赋值为70，查询成绩大于i的学生学号和选修的课程号。

语句：

DECLARE @i INT
SET @i = 70
SELECT 学号, 课程号
FROM StuCourse
WHERE 成绩 > @i

结果：

 

2、控制语句

（1）if语句：如果StuCourse表中所有学生的平均成绩大于80，显示'成绩优异'，以及成绩大于80的选课记录；否则显示'成绩一般'。（if语句的使用可参考教材例6.5）

语句：

IF (
	SELECT MIN(a.平均成绩)
	FROM(
		SELECT AVG(成绩) AS '平均成绩'
		FROM StuCourse
		GROUP BY 学号)a ) > 80
    BEGIN
         PRINT '成绩优异'
         SELECT *
             FROM StuCourse
             WHERE 成绩 > 80
    END
ELSE
    BEGIN
         PRINT '成绩一般'
    END

结果：

 

（2）while语句：使用while循环，对course表中的学分总和进行检查，若学分总和小于50，则对每门课程学分加1，直到学分总和不小于50为止。（while语句的使用可参考教材例6.7）

语句：

WHILE (SELECT SUM(学分) FROM Course) < 50
	BEGIN
		UPDATE Course
			SET 学分 = 学分 + 1
	END

结果：

 

3、系统函数

（1）返回当前日期的年月日

执行： select getdate() as 当前时间

语句：

SELECT getdate() AS 当前时间

结果：

 

（2）在update语句中使用@@rowcount全局变量来检测是否存在发生更改的记录

执行：update  student   

set 总学分=90   

where总学分=70

if @@rowcount=0

print  '警告：没有发生记录更新'

语句：

UPDATE Student
SET 总学分 = 90
WHERE 总学分 = 70
IF @@rowcount=0
PRINT '告警：没有发生记录更新'

结果：

 

（3）完成教材实验7实验内容3（4）~（6）

语句：

SELECT RAND()

SELECT 学号,姓名,REPLACE(专业名,'计算机科学与技术','计算机')
FROM Student

SELECT 学号,姓名,ISNULL(总学分,0) AS 总学分
FROM Student

4、用户自定义函数

（1）标量函数：编写用户自定义函数，要求根据输入学号，返回某学生的总分数（语法可参考教材例6.9），并使用SELECT和EXEC两种方式调用执行该函数（语法可参考教材例6.10）。

语句：

CREATE FUNCTION Total_Grade
(@cnum char(6))
RETURNS REAL
AS
BEGIN
    DECLARE @tot REAL
    SELECT @tot =
         (
             SELECT 总学分
             FROM Student
             WHERE Student.学号 = @cnum
         )
    RETURN @tot
END

DECLARE @num char(6),@tot1 REAL
SET @num = '070101'
SELECT @tot1 = dbo.Total_Grade(@num)
SELECT @tot1

DECLARE @tot REAl
EXEC @tot = dbo.Total_Grade @cnum ='070101'
SELECT @tot AS '总成绩'

（2）内嵌表值函数：编写用户自定义函数，查询出选修某课程的选课信息，并调用执行该函数。（语法可参考教材例6.11）

语句：

REATE FUNCTION GetCourseInfo (@courseName VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM Course
    WHERE Course.课程名 = @courseName
);

SELECT *
FROM dbo.GetCourseInfo('高等数学1');

（3）多语句表值函数：编写用户自定义函数，查询出给定某个系的学生平均成绩，并调用执行该函数。（语法可参考教材例6.12）

语句：

CREATE FUNCTION GetDepartmentAverageGrade (@departmentName VARCHAR(50))
RETURNS @result TABLE
(
    StudentID INT,
    AverageGrade DECIMAL(5, 2)
)
AS
BEGIN
    INSERT INTO @result (StudentID, AverageGrade)
    SELECT Student.学号, AVG(StuCourse.成绩) AS AverageGrade
    FROM StuCourse JOIN Student
		ON Student.学号 = StuCourse.学号
    WHERE Student.专业名 = @departmentName
    GROUP BY Student.学号;
    
    RETURN;
END;

SELECT *
FROM dbo.GetDepartmentAverageGrade('计算机科学与技术');

5、存储过程

（1）（无参数）用命令方式创建并执行一个存储过程，查询成绩不及格的学生的学号姓名专业、课号课名及相应分数。（语法格式可参考教材例6.13）

语句：

CREATE PROCEDURE Failed_info
AS
SELECT a.学号, a.姓名, a.专业名, b.课程号, c.课程名, b.成绩
	FROM Student a JOIN StuCourse b 
		ON a.学号  = b.学号
		JOIN Course c
		ON b.课程号 = c.课程号
	WHERE b.成绩 < 60

EXEC Failed_info

（2）（带输入参数）用命令方式创建并执行一个的存储过程，该存储过程带有输入参数@stu_no，从选课表中查询学号是@stu_no的学生的选课信息。（语法格式可参考教材例6.14）

语句：

CREATE PROCEDURE info
	@stu_no VARCHAR(20)
AS	
SELECT a.学号, b.课程号, c.课程名
FROM Student a JOIN StuCourse b 
		ON a.学号  = b.学号
		JOIN Course c
		ON b.课程号 = c.课程号
	WHERE a.学号 = @stu_no

EXEC info '070101'

（3）（带输入参数和输出参数）用命令方式创建并执行一个的存储过程，该存储过程带有输入参数@stu_no和输出参数@stu_avg，从选课表中查询学号是@stu_no的学生的成绩平均分并赋值给输出参数@stu_avg。（语法格式可参考教材例6.16）

语句：

CREATE PROCEDURE Info
	@stu_no VARCHAR(20),
	@stu_avg INT OUTPUT
AS	
SELECT @stu_avg = AVG(b.成绩)
FROM Student a JOIN StuCourse b 
		ON a.学号  = b.学号
		JOIN Course c
		ON b.课程号 = c.课程号
	WHERE a.学号 = @stu_no

EXEC info '070101'

6、触发器

（1）完成教材实验8内容3（注：rollback表示回滚，表示取消刚刚进行的操作）；

验证触发器：分别执行

INSERT INTO StuCourse VALUES（'070208','1001',70）

INSERT INTO StuCourse VALUES（'070110','2001',70）

插入是否能够成功？为什么？

语句：

CREATE TRIGGER SC_trig ON StuCourse
FOR INSERT
AS 
IF(SELECT a.学号 FROM INSERTED a) NOT IN (SELECT b.学号 FROM Student b)
BEGIN
	RAISERROR('插入操作违背数据的一致性', 16, 1)
	ROLLBACK TRANSACTION
END

INSERT INTO StuCourse VALUES('070208','1001',70)
INSERT INTO StuCourse VALUES('070110','2001',70)

（2）教材实验8思考与练习3定义触发器Ctrig 实现约束：课程的学分不能小于2，如果小于则自动改为2。自行对触发器进行验证。

语句：

CREATE TRIGGER C_trig ON StuCourse
FOR UPDATE
AS
IF(SELECT 学分 FROM Course) < 2
BEGIN
	UPDATE Course
		SET 学分 = 2
END