北邮数据库sql实验二报告.docx
《北邮数据库sql实验二报告.docx》由会员分享,可在线阅读,更多相关《北邮数据库sql实验二报告.docx(14页珍藏版)》请在冰豆网上搜索。
北邮数据库sql实验二报告
实验二SQLServer数据库设计高级内容
一、使用查询分析器实现以下查询
(1)统计有学生选修的课程门数。
SELECTCOUNT(DISTINCTCourseNo)CourseTotalFROMsc
(2)求选课在四门以上的学生所选课程的平均成绩(不统计不及格的课程)。
最后按降序列出平均成绩名次名单来。
SELECTsno,AVG(score)avg_grade
FROMsc
WHEREscore>=60
GROUPBYsnoHAVINGcount(courseno)>=4
ORDERBYavg_gradeDESC
(3)统计每门指选课程的学生选修人数(超过10人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
SELECTcourseno,COUNT(sno)snum
FROMsc
GROUPBYcoursenoHAVINGCOUNT(sno)>=10
ORDERBYCOUNT(sno)DESC,coursenoASC
(4)检索所学课程包含了s3所选所有课程的学生姓名。
SELECTDISTINCTsnoFROMscsc_1WHERE
NOTEXISTS(
SELECT*FROMscsc_2WHEREsno='200815128'AND
NOTEXISTS(
SELECT*FROMscsc_3
WHEREsc_3.sno=sc_1.snoANDsc_3.courseno=sc_2.courseno
)
)
注:
上述查询中所用的课程号和学号的值,可以根据自己表中的数据作修改;为了验证查询的正确,可能还需要输入或修改表中的示例数据;
二、实现数据完整性
(1)定义check约束
check约束用来限制用户输入的某一列数据;
例如:
成绩输入的值应该限制为0-100之间的数值
altertablescaddconstraintck_scCHECK(score>=0ANDscore<=100)
删除约束
altertablescdropconstraintck_sc
(2)定义规则(rule)
rule也可以用来限制用户输入的数据,但它只定义一次,可以绑定到一列或多列;
例如:
创建一个规则,保证只允许输入指定的课程类别:
“必修”,“任选”,“指选”,然后把此规则绑定到“课程类别”;
创建规则
createrulecoursetype
as@coursetypein("必修","任选","指选")
绑定规则
sp_bindrule'coursetype','Course.Category'
解除绑定
sp_unbindrule'Course.Category'
删除规则
droprulecoursetype
(3)创建以上约束后,练习修改约束的操作(包括增加,修改和删除以上约束);
三、在企业管理器中利用数据导入,导出向导练习数据的导入导出;把每个表中的数据导出到指定的文本文件中;
导出的文件
四、在企业管理器中,练习数据库的完整性备份和恢复;
备份文件;
五、存储过程和触发器的实现
1.存储过程
(1)使用CREATEPROCEDURE命令创建存储过程
例1:
定义存储过程,实现学生学号,姓名,课程名和成绩的查询;
CREATEPROCstudent_query
AS
SELECTstudent.sno,sname,coursename,scoreFROMstudent,course,sc
WHEREstudent.sno=sc.snoandcourse.courseno=sc.courseno
EXECstudent_query
例2:
定义存储过程,实现按某人指定课程的成绩;
CREATEPROCstudent_query1
@snamevarchar(20),
@cnamevarchar(30)
AS
SELECTstudent.sno,sname,coursename,scoreFROMstudent,course,sc
WHEREstudent.sno=sc.snoANDcourse.courseno=sc.courseno
ANDsname=@snameANDcoursename=@cname
execstudent_query1'李刚','数据库技术'
例3:
定义存储过程,在查询某人所选修的课程和成绩,指定姓名时,可以只给出姓;
CREATEPROCstudent_query2
@snamevarchar(20)='%'
AS
SELECTstudent.sno,sname,coursename,scoreFROMstudent,course,sc
WHEREstudent.sno=sc.snoANDcourse.courseno=sc.courseno
andsnameLIKE@sname
EXECstudent_query2'张%'
例4:
定义存储过程,计算并查看指定学生的总学分
altertablecoursealtercolumnCreditsfloat
CREATEPROCstudent_query3
@snamevarchar(20),
@totalintegerOUTPUT
AS
SELECT@total=SUM(Credits)FROMstudent,course,sc
WHEREstudent.sno=sc.snoANDcourse.courseno=sc.courseno
andsname=@sname
declare@totalfloat
execstudent_query3'李刚',@totalOUTPUT
select@totalTotalCredits
(2)使用EXEC命令执行上述存储过程
2.触发器
(1)使用CREATETRIGGER命令对学生选课信息表创建插入触发器,实现的功能是:
当向学生选课信息表中插入一记录时,检查该记录的学号在学生表中是否存在,检查该记录的课程编号是否在课程表中存在,若有一项为否,则提示“违背数据的一致性”错误信息,并且不允许插入。
CREATETRIGGERcheck_insON[dbo].[SC]
FORINSERT
AS
ifnotexists(
select*fromstudent,course,inserted
wherestudent.sno=inserted.snoandcourse.courseno=inserted.courseno
)
RAISERROR('违背数据的一致性',16,1)
else
begin
PRINT'数据已插入'
end
insertintoscvalues('200815128','C4',80)
(2)使用CREATETRIGGER命令对学生信息表创建删除触发器,实现的功能是:
当在学生信息表中删除一条记录时,同时删除学生选课信息表中相应的记录。
CREATETRIGGER[del_trigger]ON[dbo].[Student]
FORDELETE
AS
deletefromscwheresc.snoin(selectsnofromdeleted)
(3)向课程信息表插入数据,在学生信息表删除记录,验证触发器的执行;
SC表新增加的记录
在Student表中删除2011210041学生的信息后,可见SC表中也删除了其选课信息
六、在实验老师验收所有的项目后,删除所创建的数据库,把实验中生成的脚本文件,数据导出的文本文件以及数据库的备份文件复制到U盘中,并通过验收。