北邮数据库_sql实验二报告

数据库实验二SQLserver实验二SQLServer数据库设计高级内容一、使用查询分析器实现以下查询(1)统计有学生选修的课程门数。SELECTCOUNT(DISTINCTCourseNo)CourseTotalFROMsc(2)求选课在四门以上的学生所选课程的平均成绩(不统计不及格的课程)。最后按降序列出平均成绩名次名单来。SELECTsno,AVG(score)avg_gradeFROMscWHEREscore=60GROUPBYsnoHAVINGcount(courseno)=4ORDERBYavg_gradeDESC数据库实验二SQLserver(3)统计每门指选课程的学生选修人数(超过10人的课程才统计),要求输出课程号,课程名和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。SELECTcourseno,COUNT(sno)snumFROMscGROUPBYcoursenoHAVINGCOUNT(sno)=10ORDERBYCOUNT(sno)DESC,coursenoASC数据库实验二SQLserver(4)检索所学课程包含了s3所选所有课程的学生姓名。SELECTDISTINCTsnoFROMscsc_1WHERENOTEXISTS(SELECT*FROMscsc_2WHEREsno='200815128'ANDNOTEXISTS(SELECT*FROMscsc_3WHEREsc_3.sno=sc_1.snoANDsc_3.courseno=sc_2.courseno))数据库实验二SQLserver注：上述查询中所用的课程号和学号的值，可以根据自己表中的数据作修改；为了验证查询的正确，可能还需要输入或修改表中的示例数据；二、实现数据完整性（1）定义check约束check约束用来限制用户输入的某一列数据；例如：成绩输入的值应该限制为0-100之间的数值altertablescaddconstraintck_scCHECK(score=0ANDscore=100)删除约束altertablescdropconstraintck_sc数据库实验二SQLserver（2）定义规则（rule）rule也可以用来限制用户输入的数据，但它只定义一次，可以绑定到一列或多列；例如：创建一个规则，保证只允许输入指定的课程类别：“必修”，“任选”，“指选”，然后把此规则绑定到“课程类别”；创建规则createrulecoursetypeas@coursetypein(必修,任选,指选)绑定规则sp_bindrule'coursetype','Course.Category'数据库实验二SQLserver解除绑定sp_unbindrule'Course.Category'删除规则droprulecoursetype数据库实验二SQLserver（3）创建以上约束后，练习修改约束的操作（包括增加，修改和删除以上约束）；三、在企业管理器中利用数据导入，导出向导练习数据的导入导出；把每个表中的数据导出到指定的文本文件中；导出的文件四、在企业管理器中，练习数据库的完整性备份和恢复；备份文件；五、存储过程和触发器的实现1．存储过程（1）使用CREATEPROCEDURE命令创建存储过程例1：定义存储过程，实现学生学号，姓名，课程名和成绩的查询；数据库实验二SQLserverCREATEPROCstudent_queryASSELECTstudent.sno,sname,coursename,scoreFROMstudent,course,scWHEREstudent.sno=sc.snoandcourse.courseno=sc.coursenoEXECstudent_query例2：定义存储过程，实现按某人指定课程的成绩；CREATEPROCstudent_query1@snamevarchar(20),@cnamevarchar(30)ASSELECTstudent.sno,sname,coursename,scoreFROMstudent,course,scWHEREstudent.sno=sc.snoANDcourse.courseno=sc.coursenoANDsname=@snameANDcoursename=@cname数据库实验二SQLserverexecstudent_query1'李刚','数据库技术'例3：定义存储过程，在查询某人所选修的课程和成绩，指定姓名时，可以只给出姓；CREATEPROCstudent_query2@snamevarchar(20)='%'ASSELECTstudent.sno,sname,coursename,scoreFROMstudent,course,scWHEREstudent.sno=sc.snoANDcourse.courseno=sc.coursenoandsnameLIKE@snameEXECstudent_query2'张%'例4：定义存储过程，计算并查看指定学生的总学分altertablecoursealtercolumnCreditsfloatCREATEPROCstudent_query3@snamevarchar(20),@totalintegerOUTPUTASSELECT@total=SUM(Credits)FROMstudent,course,scWHEREstudent.sno=sc.snoANDcourse.courseno=sc.coursenoandsname=@snamedeclare@totalfloat数据库实验二SQLserverexecstudent_query3'李刚',@totalOUTPUTselect@totalTotalCredits（2）使用EXEC命令执行上述存储过程2．触发器（1）使用CREATETRIGGER命令对学生选课信息表创建插入触发器，实现的功能是：当向学生选课信息表中插入一记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程编号是否在课程表中存在，若有一项为否，则提示“违背数据的一致性”错误信息，并且不允许插入。CREATETRIGGERcheck_insON[dbo].[SC]FORINSERTASifnotexists(select*fromstudent,course,insertedwherestudent.sno=inserted.snoandcourse.courseno=inserted.courseno)RAISERROR('违背数据的一致性',16,1)elsebeginPRINT'数据已插入'endinsertintoscvalues('200815128','C4',80)数据库实验二SQLserver数据库实验二SQLserver（2）使用CREATETRIGGER命令对学生信息表创建删除触发器，实现的功能是：当在学生信息表中删除一条记录时，同时删除学生选课信息表中相应的记录。CREATETRIGGER[del_trigger]ON[dbo].[Student]FORDELETEASdeletefromscwheresc.snoin(selectsnofromdeleted)（3）向课程信息表插入数据，在学生信息表删除记录，验证触发器的执行；SC表新增加的记录在Student表中删除2011210041学生的信息后，可见SC表中也删除了其选课信息数据库实验二SQLserver六、在实验老师验收所有的项目后，删除所创建的数据库,把实验中生成的脚本文件，数据导出的文本文件以及数据库的备份文件复制到U盘中，并通过验收。