数据库系统原理与设计第3版 教学全套课件PPT课件 源代码.docx 37页

'《数据库系统原理与设计(第3版)》源代码第3章[例3.1]查询所有班级的班级编号、班级名称和所属学院。SELECTclassNo,className,instituteFROMClass[例3.2]查询所有学院的名称。SELECTinstituteFROMClass如果需要消除重复元组，则可使用如下查询：SELECTDISTINCTinstituteFROMClass[例3.3]查询所有班级的全部信息。SELECTclassNo,className,classNum,grade,instituteFROMClass或SELECT*FROMClass[例3.4]查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名。SELECTinstitute所属学院,classNo班级编号,className班级名称FROMClass该查询也可以使用AS关键字来取别名，如：SELECTinstituteAS所属学院,classNoAS班级编号,classNameAS班级名称FROMClass[例3.5]查询每门课程的课程号、课程名以及周课时(周课时为课时数除以16)，并将课程名中大写字母改为小写字母输出。SELECTcourseNo课程号,lower(courseName)课程名,courseHour/16AS周课时FROMCourse[例3.6]查询2015级的班级编号、班级名称和所属学院。SELECTclassNo,className,instituteFROMClassWHEREgrade=2015[例3.7]在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(getdate())-year(birthday)>=19[例3.8]在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,score FROMScoreWHEREscoreBETWEEN80AND90[例3.9]在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscoreNOTBETWEEN80AND90[例3.10]在选课Score表中查询选修了001、005或003课程的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNoIN("001","005","003")[例3.11]在学生Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnativeNOTIN("南昌","上海")[例3.12]在课程Course表中查询先修课程为空值的课程信息。SELECT*FROMCourseWHEREpriorCourseISnull[例3.13]在课程Course表中查询有先修课程的课程信息。SELECT*FROMCourseWHEREpriorCourseISNOTnull[例3.14]在班级Class表中查询班级名称中含有会计的班级信息。SELECT*FROMClassWHEREclassNameLIKE"%会计%"[例3.15]在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameLIKE"王__"[例3.16]在学生Student表中查询名字中不含有“福”的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREstudentNameNOTLIKE"%福%"[例3.17]在学生Student表中查询蒙古族的同学学号和姓名。SELECTstudentNo,studentNameFROMStudentWHEREnationLIKE"蒙古族" 该查询等价于下面的查询：SELECTstudentNo,studentNameFROMStudentWHEREnation="蒙古族"[例3.18]在班级Class表中查询班级名称中含有“16_”符号的班级名称。SELECTclassNameFROMClassWHEREclassNameLIKE"%16_%"ESCAPE""如果将#字符作为换码字符，则该查询可改写为：SELECTclassNameFROMClassWHEREclassNameLIKE"%16#_%"ESCAPE"#"[例3.19]在选课Score表中查询选修了001、005或003课程的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREcourseNo="001"ORcourseNo="005"ORcourseNo="003"如果要在选课Score表中查询同时选修了001和002课程的同学的选课信息，如下查询是错误的，得不到结果：SELECT*FROMScoreWHEREcourseNo="001"ANDcourseNo="002"[例3.20]在Student表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期。SELECTstudentNo,studentName,birthdayFROMStudentWHEREyear(birthday)=1998ANDnation="汉族"[例3.21]在Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号。SELECTstudentName,native,classNoFROMStudentWHEREnative!="南昌"ANDnative!="上海"[例3.22]在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>=80ANDscore<=90[例3.23]在选课Score表中查询成绩不在80～90分之间的同学学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore<80ORscore>90[例3.24]在学生Student表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。SELECTstudentName,native,classNo FROMStudentWHEREnative!="南昌"ANDnative!="上海"ORDERBYnativeDESC[例3.25]在学生Student表中查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的月份降序排序输出。SELECTstudentNo,studentName,classNo,birthdayFROMStudentWHEREsex="女"ORDERBYclassNo,month(birthday)DESC[例3.26]查询1999年出生的“女”同学基本信息。SELECTstudentNo,studentName,birthdayFROM(SELECT*FROMStudentWHEREsex="女")ASaWHEREyear(birthday)=1999该查询等价于下面的查询：SELECTstudentNo,studentName,birthdayFROMstudentWHEREyear(birthday)=1999ANDsex="女"[例3.27]查询学生总人数。SELECTcount(*)FROMStudent为了便于理解，可以对计算列取一个列名，上述查询可修改为如下：SELECTcount(*)学生人数FROMStudent[例3.28]查询所有选课学生的人数。SELECTcount(studentNo)学生人数FROMScore为了消除重复的元组，必须使用DISTINCT短语，可将查询修改为：SELECTcount(DISTINCTstudentNo)学生人数FROMScore[例3.29]查询学号为1500003同学所选课程的平均分。SELECTavg(score)平均分FROMScoreWHEREstudentNo="1500003"[例3.30]查询每个同学的选课门数、平均分和最高分。SELECTstudentNo,count(*)门数,avg(score)平均分,max(score)最高分FROMScoreGROUPBYstudentNo[例3.31]查询平均分在80分以上的每个同学的选课门数、平均分和最高分。SELECTstudentNo,count(*)门数,avg(score)平均分,max(score)最高分FROMScoreGROUPBYstudentNoHAVINGavg(score)>=80 [例3.32]查找会计学院全体同学的学号、姓名、籍贯、班级编号和所在班级名称。SELECTstudentNo,studentName,native,Student.classNo,classNameFROMStudent,ClassWHEREStudent.classNo=Class.classNoANDinstitute="会计学院"为了简化，可为参与连接的表取别名(称为元组变量)，这样可以改写为：SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudentASa,ClassASbWHEREa.classNo=b.classNoANDinstitute="会计学院"或者SELECTstudentNo,studentName,native,b.classNo,classNameFROMStudenta,ClassbWHEREa.classNo=b.classNoANDinstitute="会计学院"[例3.33]查找选修了课程名称为“计算机原理”的同学学号、姓名。SELECTa.studentNo,studentNameFROMStudenta,Courseb,ScorecWHEREb.courseNo=c.courseNoANDc.studentNo=a.studentNoANDb.courseName="计算机原理"[例3.34]查找同时选修了编号为001和002课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,(SELECT*FROMScoreWHEREcourseNo="002")cWHEREb.courseNo="001"ANDa.studentNo=b.studentNoANDa.studentNo=c.studentNoORDERBYa.studentNo该查询也可以表示为：SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,(SELECT*FROMScoreWHEREcourseNo="001")b,(SELECT*FROMScoreWHEREcourseNo="002")cWHEREa.studentNo=b.studentNoANDa.studentNo=c.studentNoORDERBYa.studentNo[例3.35]查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分，并按学号排序输出。SELECTa.studentNo,studentName,sum(creditHour)FROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDc.courseNo=b.courseNoANDscore>=60GROUPBYa.studentNo,studentNameHAVINGsum(creditHour)>=28ORDERBYa.studentNo[例3.36]实现成绩表Score和课程表Course的自然连接。SELECTstudentNo,a.courseNo,score,courseName,creditHour,courseHour,priorCourseFROMScorea,CoursebWHEREa.courseNo=b.courseNo [例3.37]查找同时选修了编号为001和002课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。SELECTa.studentNo,studentName,b.courseNo,b.score,c.courseNo,c.scoreFROMStudenta,Scoreb,ScorecWHEREb.courseNo="001"ANDc.courseNo="002"ANDb.studentNo=c.studentNoANDa.studentNo=b.studentNoORDERBYa.studentNo[例3.38]在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。SELECTa.studentName,a.classNo,a.birthdayFROMStudenta,StudentbWHEREb.studentName="李宏冰"ANDa.classNo=b.classNo[例3.39]查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassa,StudentbWHEREa.classNo=b.classNoANDgrade=2015ORDERBYclassName[例3.40]使用左外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaLEFTOUTERJOINStudentbONa.classNo=b.classNoWHEREgrade=2015ORDERBYclassName,studentNo[例3.41]使用右外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaRIGHTOUTERJOINStudentbONa.classNo=b.classNoWHEREgrade=2015ORDERBYclassName,studentNo[例3.42]使用全外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。SELECTclassName,institute,studentNo,studentNameFROMClassaFULLOUTERJOINStudentbONa.classNo=b.classNoWHEREgrade=2015ORDERBYclassName,studentNo[例3.43]查询选修过课程的学生姓名。SELECTstudentNameFROMStudentWHEREStudent.studentNoIN(SELECTScore.studentNoFROMScore) [例3.44]查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。SELECTstudentNo,studentName,classNoFROMStudentWHEREstudentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseNameLIKE"%系统%"))[例3.45]查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="计算机原理"))ANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="高等数学"))ORDERBYa.studentNo,scoreDESC该查询也可以表示为如下形式：SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")ANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="高等数学")ORDERBYa.studentNo,scoreDESC[例3.46]查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及所选修的这两门课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")ANDa.studentNoIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="高等数学")AND(courseName="高等数学"ORcourseName="计算机原理")ORDERBYa.studentNo,scoreDESC[例3.47]查询所选修课程的成绩大于所有002号课程成绩的同学学号及相应课程的课程号和成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore>ALL (SELECTscoreFROMScoreWHEREcourseNo="002")[例3.48]查询成绩最高分的学生的学号、课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreWHEREscore=(SELECTmax(score)FROMScore)聚合函数可以直接使用在HAVING子句中(如例3.35)，也可以用于子查询中(如例3.48)，但在WHERE子句中不可以直接使用聚合函数。如下语句是不正确的：SELECT*FROMScoreWHEREscore=max(score)[例3.49]查询年龄小于“计算机科学与技术16-01班”某个同学年龄的所有同学的学号、姓名和年龄。SELECTstudentNo,studentName,year(getdate())-year(birthday)ASageFROMStudentWHEREbirthday>ANY(SELECTbirthdayFROMStudenta,ClassbWHEREclassName="计算机科学与技术16-01班"ANDa.classNo=b.classNo)[例3.50]查询选修了“计算机原理”课程的同学姓名、所在班级编号。SELECTstudentName,classNoFROMStudentxWHEREEXISTS(SELECT*FROMScorea,CoursebWHEREa.courseNo=b.courseNoANDa.studentNo=x.studentNoANDcourseName="计算机原理")[例3.51]查询选修了所有课程的学生姓名。SELECTstudentNameFROMStudentxWHERENOTEXISTS(SELECT*FROMCoursecWHERENOTEXISTS--判断学生x.studentNo没有选修课程c.courseNo(SELECT*FROMScoreWHEREstudentNo=x.studentNoANDcourseNo=c.courseNo))[例3.52]查询至少选修了学号为1600002学生所选修的所有课程的学生姓名。SELECTstudentNameFROMStudentxWHERENOTEXISTS(SELECT*FROMScoreyWHEREstudentNo="1600002"--查询学生"1600002"所选修课程的情况ANDNOTEXISTS--判断学生x.studentNo没有选修课程y.courseNo(SELECT*FROMScore WHEREstudentNo=x.studentNoANDcourseNo=y.courseNo))[例3.53]查询至少选修了学号为1600002学生所选修的所有课程的学生学号、姓名以及该学生所选修课程的课程名和成绩。SELECTx.studentNo,studentName,courseName,scoreFROMStudentx,Coursey,ScorezWHEREx.studentNo=z.studentNoANDy.courseNo=z.courseNoANDNOTEXISTS(SELECT*FROMScorebWHEREstudentNo="1600002"--查询学生"1600002"所选修课程的情况ANDNOTEXISTS--判断学生x.studentNo没有选修课程b.courseNo(SELECT*FROMScoreWHEREstudentNo=x.studentNoANDcourseNo=b.courseNo))[例3.54]查询至少获得了28个学分的同学的学号、姓名以及所选修各门课程的课程名、成绩和学分，并按学号排序输出。要求如下：①对于所选修的课程，如果成绩不及格则不能获得该课程的学分；②如果一个学生选修同一门课程多次，则选取最高成绩输出。SELECTa.studentNo,studentName,courseName,score,creditHourFROMStudenta,Courseb,(SELECTstudentNo,courseNo,max(score)scoreFROMScoreWHILEscore>=60--仅列示已经获得学分(即及格了)的课程GROUPBYstudentNo,courseNo)AScWHEREa.studentNo=c.studentNoANDc.courseNo=b.courseNoANDa.studentNoIN(SELECTstudentNo--子查询QFROMCoursex,(SELECTstudentNo,courseNo,max(score)scoreFROMScoreWHILEscore>=60--只有及格才能获得学分GROUPBYstudentNo,courseNo)ASyWHEREy.courseNo=x.courseNoGROUPBYstudentNoHAVINGsum(creditHour)>=28)ORDERBYa.studentNo[例3.55]查询至少选修了5门课程且课程平均分最高的同学的学号和课程平均分。如果一个学生选修同一门课程多次，则选取最高成绩。SELECTstudentNo,avg(score)avgScoreFROM(SELECTstudentNo,courseNo,max(score)scoreFROMScoreGROUPBYstudentNo,courseNo)aGROUPBYstudentNoHAVINGcount(*)>=5ANDavg(score)=(SELECTmax(avgScore)--子查询Q2FROM(SELECTstudentNo,avg(score)avgScore--子查询Q1，结果作为查询表xFROM(SELECTstudentNo,courseNo,max(score)scoreFROMScore GROUPBYstudentNo,courseNo)bGROUPBYstudentNoHAVINGcount(*)>=5)ASx)[例3.56]查询选修了所有4学分课程(即学分为4的课程)的同学的学号、姓名以及所选修4学分课程的课程名和成绩。SELECTa.studentNo,studentName,courseName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDNOTEXISTS(SELECT*FROMCoursexWHEREcreditHour=4--查询4学分课程的情况ANDNOTEXISTS--判断学生a.studentNo没有选修课程x.courseNo(SELECT*FROMScoreWHEREstudentNo=a.studentNoANDcourseNo=x.courseNo))ANDcreditHour=4--只显示满足上述要求的学生所选修4学分课程的课程名和成绩[例3.57]查询“信息管理学院”1999年出生的同学的学号、出生日期、班级名称和所属学院以及“会计学院”1998年出生的同学的学号、出生日期、班级名称和所属学院。SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDyear(birthday)=1999ANDinstitute="信息管理学院"UNIONSELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoANDyear(birthday)=1998ANDinstitute="会计学院"上述SQL语句可以改写为：SELECTstudentNo,birthday,className,instituteFROMStudenta,ClassbWHEREa.classNo=b.classNoAND(year(birthday)=1999ANDinstitute="信息管理学院"ORyear(birthday)=1998ANDinstitute="会计学院")ORDERBYinstitute[例3.58]查询同时选修了001号和005号课程的同学的学号和姓名。SELECTa.studentNo,studentNameFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDcourseNo="001"INTERSECTSELECTa.studentNo,studentNameFROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDcourseNo="005"本例也可以用下面的SQL语句实现：SELECTa.studentNo,studentNameFROMStudenta,Scoreb WHEREa.studentNo=b.studentNoANDcourseNo="001"ANDa.studentNoIN(SELECTstudentNoFROMScoreWHEREcourseNo="005")[例3.59]查询没有选修“计算机原理”课程的同学的学号和姓名。SELECTstudentNo,studentNameFROMStudentEXCEPTSELECTDISTINCTa.studentNo,studentNameFROMStudenta,Scoreb,CoursecWHEREa.studentNo=b.studentNoANDb.courseNo=c.courseNoANDcourseName="计算机原理"本例也可以用下面的SQL语句实现：SELECTstudentNo,studentNameFROMStudentWHEREstudentNoNOTIN(SELECTstudentNoFROMScorex,CourseyWHEREx.courseNo=y.courseNoANDcourseName="计算机原理")[例3.60]查询每一个同学的学号以及该同学所修课程中成绩最高的课程的课程号和相应成绩。SELECTstudentNo,courseNo,scoreFROMScoreaWHEREscore=(SELECTmax(score)FROMScoreWHEREstudentNo=a.studentNo)[例3.61]查询学生人数不低于500的学院的学院名称及学生人数。SELECTinstitute,count(*)人数FROMStudenta,ClassbWHEREa.classNo=b.classNoGROUPBYinstituteHAVINGcount(*)>=500[例3.62]查询平均分最高的课程的课程号、课程名和平均分。如果一个学生选修同一门课程多次，则选取最高成绩。SELECTa.courseNo,courseName,avg(score)最高平均分FROMCoursea,(SELECTstudentNo,courseNo,max(score)scoreFROMScoreGROUPBYstudentNo,courseNo)ASbWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseNameHAVINGavg(score)=(SELECTmax(avgScore)FROM(SELECTavg(score)avgScoreFROM(SELECTstudentNo,courseNo,max(score)scoreFROMScoreGROUPBYstudentNo,courseNo)AScGROUPBYcourseNo)ASx) 第7章[例7.1]建立学生成绩数据库ScoreDB。CREATEDATABASEScoreDBON--创建数据文件(NAME=ScoreDB,--逻辑文件名，也称为数据文件的别名FILENAME="e:SQLDatabaseScoreDB.mdf",--数据文件的物理文件名SIZE=5,MAXSIZE=10,FILEGROWTH=1)LOGON--创建日志文件(NAME=ScoreLog,--逻辑文件名，也称为日志文件的别名FILENAME="e:SQLDatabaseScoreLog.ldf",--日志文件的物理文件名SIZE=2,MAXSIZE=5,FILEGROWTH=1)[例7.2]建立一个复杂的数据库MyTempDB。CREATEDATABASEMyTempDBON--创建数据文件PRIMARY--创建主逻辑设备中的数据文件，共有1个(NAME=TempDev,--逻辑文件名，也称为数据文件的别名FILENAME="d:TempDataTempDev.mdf",--数据文件的物理文件名SIZE=5,FILEGROWTH=2),FILEGROUPTempHisDev--创建第1个用户逻辑设备(组)中的数据文件，共有1个(NAME=TempHisDev1,--用户逻辑设备(组)TempHisDev中的数据文件别名FILENAME="d:TempDataTempHisDev1.mdf",--TempHisDev中的物理文件名SIZE=10,FILEGROWTH=5),FILEGROUPTempBakDev--创建第2个用户逻辑设备(组)中的数据文件，共有3个(NAME=TempBakDev1,--用户逻辑设备(组)TempBakDev中第1个数据文件别名FILENAME="d:TempDataTempBakDev1.mdf",--TempBakDev中第1个物理文件名SIZE=5,FILEGROWTH=2),(NAME=TempBakDev2,--用户逻辑设备(组)TempBakDev中第2个数据文件别名FILENAME="d:TempDataTempBakDev2.mdf",--TempBakDev中第2个物理文件名SIZE=5,FILEGROWTH=2),(NAME=TempBakDev3,--用户逻辑设备(组)TempBakDev中第3个数据文件别名FILENAME="d:TempDataTempBakDev3.mdf",--TempBakDev中第3个物理文件名SIZE=5,FILEGROWTH=2)LOGON--创建日志文件，共有2个(NAME="TempLogDev1",--第1个日志文件别名FILENAME="d:TempDataTempLogDev1.ldf",--第1个日志文件的物理文件名SIZE=5MB,FILEGROWTH=2MB),(NAME="TempLogDev2",--第2个日志文件别名FILENAME="d:TempDataTempLogDev2.ldf",--第2个日志文件的物理文件名SIZE=5MB, FILEGROWTH=2MB)[例7.3]修改MyTempDB数据库。ALTERDATABASEMyTempDBMODIFYFILE(NAME=TempHisDev1,SIZE=20MB) [例7.4]建立学生成绩管理数据库中的5个基本表。CREATETABLECourse(courseNochar(3)NOTNULL,--课程号courseNamevarchar(30)UNIQUENOTNULL,--课程名creditHournumeric(1)DEFAULT0NOTNULL,--学分courseHourtinyintDEFAULT0NOTNULL,--课时数priorCoursechar(3)NULL,--先修课程/*建立命名的主码约束和匿名的外码约束*/CONSTRAINTCoursePKPRIMARYKEY(courseNo),FOREIGNKEY(priorCourse)REFERENCESCourse(courseNo))CREATETABLEClass(classNochar(6)NOTNULL,--班级号classNamevarchar(30)UNIQUENOTNULL,--班级名institutevarchar(30)NOTNULL,--所属学院gradesmallintDEFAULT0NOTNULL,--年级classNumtinyintNULL,--班级人数CONSTRAINTClassPKPRIMARYKEY(classNo))CREATETABLEStudent(studentNochar(7)NOTNULLCHECK(studentNoLIKE"[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"),--学号studentNamevarchar(20)NOTNULL,--姓名sexchar(2)NULL,--性别birthdaydatetimeNULL,--出生日期nativevarchar(20)NULL,--籍贯nationvarchar(30)DEFAULT"汉族"NULL,--民族classNochar(6)NULL,--所属班级CONSTRAINTStudentPKPRIMARYKEY(studentNo),CONSTRAINTStudentFKFOREIGNKEY(classNo)REFERENCESClass(classNo))CREATETABLETerm(termNochar(3)NOTNULL,--学期号termNamevarchar(30)NOTNULL,--学期描述remarksvarchar(10)NULL,--备注CONSTRAINTTermPKPRIMARYKEY(termNo))CREATETABLEScore(studentNochar(7)NOTNULL,--学号courseNochar(3)NOTNULL,--课程号termNochar(3)NOTNULL,--学期号scorenumeric(5,1)DEFAULT0NOTNULLCHECK(scoreBETWEEN0.0AND100.0),--成绩 /*建立1个由3个属性构成的命名主码约束以及3个命名外码约束*/CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo,termNo),CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESStudent(studentNo),CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCESCourse(courseNo),CONSTRAINTScoreFK3FOREIGNKEY(termNo)REFERENCESTerm(termNo))[例7.5]在MyTempDB数据库中建立TempTable表，放在TempBakDev逻辑设备(组)上。CREATETABLETempTable(xnochar(3)NOTNULL,xnamevarchar(2)NOTNULL,PRIMARYKEY(xno))ONTempBakDev [例7.6]在MyTempDB数据库中为TempTable表增加一列。ALTERTABLETempTableADDxsexintDEFAULT0[例7.7]在MyTempDB数据库中为TempTable表的xname列修改数据类型。ALTERTABLETempTableALTERCOLUMNxnamechar(10)[例7.8]在MyTempDB数据库中为TempTable表的xname列增加唯一约束ALTERTABLETempTableADDCONSTRAINTUniqueXnameUNIQUE(xname)[例7.9]删除TempTable表DROPTABLETempTable[例7.10]在班级表中按所属学院建立一个非聚集索引InstituteIdx。CREATENONCLUSTEREDINDEXInstituteIdxONClass(institute)[例7.11]在学生表中，首先按班级编号的升序，然后按出生日期的降序建立一个非聚集索引ClassBirthIdx。CREATEINDEXClassBirthIdxONStudent(classNo,birthdayDESC)[例7.12]删除班级表Class中建立的InstituteIdx索引。DROPINDEXInstituteIdxONClass[例7.13]将一个新学生元组("1500006","李相东","男","1998-10-2100:00","云南","撒呢族","CS1502")插入到学生表Student中。INSERTINTOStudentVALUES("1500006","李相东","男","1998-10-2100:00","云南","撒呢族","CS1502")[例7.14]将一个新学生元组(姓名：章李立，出生日期：1999-10-1200:00，学号：1500007)插入到学生表Student中。 INSERTINTOStudent(studentName,birthday,studentNo)VALUES("章李立","1999-10-1200:00","1500007")[例7.15]将少数民族同学的选课信息插入到StudentNation表中。首先创建一个基本表StudentNation。CREATETABLEStudentNation(studentNochar(7)NOTNULL,--学号courseNochar(3)NOTNULL,--课程号termNochar(3)NOTNULL,--学期号scorenumeric(5,1)DEFAULT0NOTNULL--成绩CHECK(scoreBETWEEN0.0AND100.0),CONSTRAINTStudentNationPKPRIMARYKEY(studentNo,courseNo,termNo))然后执行如下插入语句：INSERTINTOStudentNationSELECT*FROMScoreWHEREstudentNoIN(SELECTstudentNoFROMStudentWHEREnation<>"汉族")[例7.16]将汉族同学的选课信息插入到StudentNation表中。INSERTINTOStudentNation(studentNo,courseNo,termNo)SELECTstudentNo,courseNo,termNoFROMScoreWHEREstudentNoIN(SELECTstudentNoFROMStudentWHEREnation="汉族")[例7.17]删除学号为1600001同学的选课记录。DELETEFROMScoreWHEREstudentNo="1600001"[例7.18]删除选修了“高等数学”课程的选课记录。DELETEFROMScoreWHEREcourseNoIN(SELECTcourseNoFROMCourseWHEREcourseName="高等数学")[例7.19]删除平均分在60～70分之间的同学的选课记录。DELETEFROMScoreWHEREstudentNoIN(SELECTstudentNoFROMScoreGROUPBYstudentNoHAVINGavg(score)BETWEEN60AND70)[例7.20]将王红敏同学在151学期选修的002课程的成绩改为88分。UPDATEScoreSETscore=88WHEREcourseNo="002"ANDtermNo="151"ANDstudentNoIN(SELECTstudentNoFROMStudentWHEREstudentName="王红敏") 也可以写成：UPDATEScoreSETscore=88FROMScorea,StudentbWHEREa.studentNo=b.studentNoANDcourseNo="002"ANDtermNo="151"ANDstudentName="王红敏"[例7.21]将注册会计16_02班的男同学的成绩都增加5分。UPDATEScoreSETscore=score+5FROMScorea,Studentb,ClasscWHEREa.studentNo=b.studentNoANDb.classNo=c.classNoANDclassName="注册会计16_02班"ANDsex="男"[例7.22]将学号为1600001同学的出生日期修改为1999年5月6日出生，籍贯修改为福州。UPDATEStudentSETbirthday="1999-5-600:00",native="福州"WHEREstudentNo="1600001"[例7.23]将每个班级的学生人数填入到班级表的ClassNum列中。UPDATEClassSETclassNum=sCountFROMClassa,(SELECTclassNo,count(*)sCountFROMStudentGROUPBYclassNo)bWHEREa.classNo=b.classNo[例7.24]创建仅包含1999年出生的学生视图StudentView1999。CREATEVIEWStudentView1999ASSELECT*FROMStudentWHEREyear(birthday)=1999由于本例没有使用WITHCHECKOPTION选项，因此下面的插入语句可以执行：INSERTINTOStudentView1999VALUES("1500008","李相东","男","1998-10-2100:00","云南","哈尼族","CS1501")[例7.25]创建仅包含1999年出生的学生视图StudentView1999Chk，并要求在对该视图进行更新操作时，进行合法性检查(即保证更新操作要满足创建视图中的谓词条件)。CREATEVIEWStudentView1999ChkASSELECT*FROMStudentWHEREyear(birthday)=1999WITHCHECKOPTION 由于本例使用了WITHCHECKOPTION选项，因此下面的插入语句可以执行：INSERTINTOStudentView1999ChkVALUES("1500009","李相西","男","1999-10-2100:00","云南","哈尼族","CS1501")而下面的插入语句不可以执行：INSERTINTOStudentView1999ChkVALUES("1500010","李相南","男","1998-10-2100:00","云南","哈尼族","CS1502")原因是插入的出生日期违反了year(birthday)=1999条件。[例7.26]创建一个包含学生学号、姓名、课程名、获得的学分和相应成绩的视图ScoreView。CREATEVIEWScoreViewASSELECTa.studentNo,studentName,courseName,creditHour,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDscore>=60--成绩必须大于等于60分才能获得学分[例7.27]创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView。CREATEVIEWSourceView(courseNo,courseName,courseCount,courseAvg)ASSELECTa.courseNo,courseName,count(*),avg(score)FROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseName本例也可以用下面的语句替代：CREATEVIEWSourceView1ASSELECTa.courseNo,courseName,count(*)courseCount,avg(Score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseName[例7.28]创建一个包含每门课程的课程编号、课程名称、选课人数和选课平均成绩的视图SourceView2，要求该视图选课人数必须在5人以上。CREATEVIEWSourceView2ASSELECT*FROMSourceView--基于视图SourceViewWHEREcourseCount>=5[例7.29]创建一个包含学生学号、姓名和年龄的视图StudentAgeView。CREATEVIEWStudentAgeViewASSELECTstudentNo,studentName,year(getdate())-year(birthday)ageFROMStudent[例7.30]在StudentView1999中查询CS1601班同学的信息。 SELECT*FROMStudentView1999--基于视图StudentView1999的查询WHEREclassNo="CS1601"对于该查询，系统首先进行有效性检查，判断视图StudentView1999是否存在。如果存在，则从数据库系统表中取出该创建视图的语句，将创建视图中的子查询与用户的查询结合起来，转换为基于基本表的查询。转换后的查询如下：SELECT*FROMStudentWHEREyear(birthday)=1999ANDclassNo="CS1601"[例7.31]在视图SourceView中查询平均成绩在80分以上的课程信息。SELECT*FROMSourceViewWHEREcourseAvg>=80视图SourceView是一个基于聚合运算的视图，列是courseAvg，它是经过聚合函数运算的值。由于在WHERE子句中，不允许对聚合函数进行运算，因此不可能转换为如下查询：SELECTa.courseNo,courseName,count(*)courseCount,avg(score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoANDcourseAvg>=80GROUPBYa.courseNo,courseNameHAVING子句可以对聚合函数直接作用，因此系统会将该查询转换为如下形式：SELECTa.courseNo,courseName,count(*)courseCount,avg(score)courseAvgFROMCoursea,ScorebWHEREa.courseNo=b.courseNoGROUPBYa.courseNo,courseNameHAVINGavg(score)>=80[例7.32]在视图SourceView和课程Course表中查询课程平均成绩在75分以上的课程编号、课程名称、课程平均成绩和学分。SELECTa.courseNo,a.courseName,courseAvg,creditHourFROMCoursea,SourceVIEWbWHEREa.courseNo=b.courseNoANDcourseAvg>=75[例7.33]在StudentView1999中将学号为1600004同学的姓名修改为张小立。UPDATEStudentView1999SETstudentName="张小立"WHEREstudentNo="1600004"对于该操作，系统首先进行有效性检查，判断视图StudentView1999是否存在。如果存在，则从数据库系统表中取出该创建视图的语句，将创建视图中的子查询与用户的查询结合起来，转换为基于基本表的修改。转换后的查询如下：UPDATEStudentSETstudentName="张小立"WHEREyear(birthday)=1999ANDstudentNo="1600004"[例7.34]在视图StudentView1999中将学号为1600004同学的出生年份由1999修改为2000。UPDATEStudentView1999 SETbirthday="2000-05-2000:00:00.000"WHEREstudentNo="1600004"[例7.35]在视图StudentView1999中将学号为1600006同学的记录删除。DELETEFROMStudentView1999WHEREstudentNo="1600006"系统将该操作转化为如下的操作：DELETEFROMStudentWHEREyear(birthday)=1999ANDstudentNo="1600006"[例7.36]在视图SourceView中删除平均成绩大于80分的课程记录。DELETEFROMSourceViewWHEREcourseAvg>=80[例7.37]删除视图及级联视图。(1)删除视图StudentView1999DROPVIEWStudentView1999(2)级联删除视图SourceViewDROPVIEWSourceViewCASCADE[例7.38]在ScoreDB数据库中，查询Score表中的最高成绩，如果最高成绩大于95分，则显示“verygood!”。USEScoreDBGODECLARE@scorenumericSELECT@score=(SELECTmax(score)FROMScore)IF@score>95PRINT"verygood!"[例7.39]声明两个局部变量@sno和@score，用于接受SELECT语句查询返回的结果，并显示其结果。DECLARE@snochar(7),@scorenumericSELECT@sno=a.studentNo,@score=scoreFROMScorea,StudentbWHEREcourseNo="005"ANDa.studentNo=b.studentNoANDstudentName="刘方晨"IF@@ROWCOUNT=0PRINT"Warning:Norowswereselected"ELSESELECT@sno,@score[例7.40]将当前系统的时间按104格式输出。SELECTconvert(char(20),getdate(),104)[例7.41]将当前系统的时间按120格式输出。SELECTconvert(char(20),getdate(),120)[例7.42]获取当前登录的用户名和主机名。 SELECTuser_name(),host_name()[例7.43]在ScoreDB数据库中，查询Score表中学号1500002学生的平均成绩，如果成绩score列为空则用60分替换。USEScoreDBGOSELECTavg(isnull(score,60))FROMScoreWHEREstudentNo="1500002"[例7.44]在图书借阅数据库BookDB中查找读者“张小娟”所借图书的图书名，借阅日期、归还日期，如果没有归还，显示未还书。SELECTbookName,borrowDate,isnull(convert(char(10),returnDate,120),"未还书")FROMReadera,Borrowb,BookcWHEREa.readerNo=b.readerNoANDb.bookNo=c.bookNoANDreaderName="张小娟"[例7.45]在学生表Student中，如果有蒙古族学生，则显示“存在蒙古族的学生”。IFEXISTS(SELECT*FROMStudentWHEREnation="蒙古族")PRINT"存在蒙古族的学生"[例7.46]列示成绩表Score中的所有选课记录，要求根据学期号termNo的不同取值分别显示开课时间为xx年下半年、xx年上半年、xx年暑期小学期，根据成绩score的不同取值分别显示等级为优良(80分及以上)、合格和不及格(小于60分)。如"152"显示为“16年上半年”。SELECTstudentNo学号,courseNo课程号,CASEright(termNo,1)WHEN"1"THENleft(termNo,2)+"年下半年"WHEN"2"THENstr(convert(tinyint,left(termNo,2))+1,2)+"年上半年"ELSEstr(convert(tinyint,left(termNo,2))+1,2)+"年暑期小学期"END开课时间,CASEWHENscore>=80THEN"优良"WHENscore>=60THEN"合格"ELSE"不及格"END等级FROMScore[例7.47]显示100～200之间的素数。/*对于一个正整数n，如果除了1和自身之外它没有其它因子，则该数就称为素数，也称为质数，**由于因子是成对出现的，因此，如果在2～sqrt(n)之间找不到因子，则n就是素质。*/DECLARE@kint,@nintSET@n=100WHILE@n<=200--寻找100～200之间的素数BEGINSET@k=2WHILE@k<=sqrt(@n)--在2～sqrt(@n)之间找@n的因子BEGINIF@n%@k=0--表示@k是@n的一个因子，因此可以判断@n不是素数BREAK--退出当前循环SET@k=@k+1END IF@k>sqrt(@n)--表示@n是素数，输出它PRINT@nSET@n=@n+1END[例7.48]创建一个游标，逐行显示选修了“计算机原理”课程的学生姓名、相应成绩和选课学期，最后显示该课程的平均分。/*声明变量及赋初值*/DECLARE@sNamevarchar(20),@scoretinyint,@termNochar(3)DECLARE@sumScoreint,@countScoresmallintSET@sumScore=0SET@countScore=0--定义游标DECLAREmyCurCURSORFORSELECTstudentName,score,termNoFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDcourseName="计算机原理"ORDERBYstudentNameOPENmyCur--打开游标--获取当前游标的值放到变量@sName、@score和@termNo中FETCHmyCurINTO@sName,@score,@termNoPRINTconvert(char(10),"学生姓名")+convert(char(10),"课程成绩")+convert(char(10),"选课学期")PRINTreplicate("-",30)WHILE(@@FETCH_STATUS=0)BEGIN--显示变量@sName、@score和@termNo中的值PRINTconvert(char(10),@sName)+convert(char(10),@score)+convert(char(10),@termNo)SET@sumScore=@sumScore+@score--计算总分SET@countScore=@countScore+1--计算选课人数FETCHmyCurINTO@sName,@score,@termNo--获取下一个游标值ENDPRINTreplicate("-",30)PRINT"课程平均分"IF@countScore>0PRINT@sumScore/@countScoreELSEPRINT0.00CLOSEmyCur--关闭游标DEALLOCATEmyCur--释放游标[例7.49]将选修了《高等数学》课程且成绩不及格的学生选课记录显示出来，并从数据库中删除该选课记录。/*声明变量及赋初值*/DECLARE@sNamevarchar(20),@scoretinyint--定义游标DECLAREmyCurCURSORFORSELECTstudentName,scoreFROMStudenta,Courseb,ScorecWHEREa.studentNo=c.studentNoANDb.courseNo=c.courseNoANDcourseName="高等数学"ANDscore<60 OPENmyCur--打开游标--获取当前游标的值放到变量@sName和@score中FETCHmyCurINTO@sName,@scoreWHILE(@@FETCH_STATUS=0)BEGIN--显示变量@sName和@score中的值SELECT@sName学生姓名,@score课程成绩--删除当前游标所指的选课记录DELETEFROMScoreWHERECURRENTOFmyCurFETCHmyCurINTO@sName,@score--获取下一个游标值ENDCLOSEmyCur--关闭游标DEALLOCATEmyCur--释放游标[例7.50]输入某个同学的学号，统计该同学的平均分CREATEPROCEDUREproStudentByNo1(@sNochar(7))ASSELECTa.studentNo,studentName,avg(score)FROMStudenta,ScorebWHEREa.studentNo=b.studentNoANDa.studentNo=@sNoGROUPBYa.studentNo,studentName[例7.51]输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行显示该同学的姓名、选课名称和选课成绩。CREATEPROCEDUREproStudentByNo2(@sNochar(7),@avgnumeric(6,2)OUTPUT)ASBEGINDECLARE@sNamevarchar(20),@cNamevarchar(20)DECLARE@scoretinyint,@sumint,@counttinyintSELECT@sum=0,@count=0--定义、打开、获取游标DECLAREcurScoreCURSORFORSELECTstudentName,courseName,scoreFROMScorea,Studentb,CoursecWHEREb.studentNo=@sNoANDa.studentNo=b.studentNoANDa.courseNo=c.courseNoOPENcurScoreFETCHcurScoreINTO@sName,@cName,@scoreWHILE(@@FETCH_STATUS=0)BEGIN--业务处理SELECT@sName,@cName,@score--逐行显示该同学的姓名、选课名称和成绩SET@sum=@sum+@scoreSET@count=@count+1FETCHcurScoreINTO@sName,@cName,@scoreENDCLOSEcurScoreDEALLOCATEcurScoreIF@count=0SELECT@avg=0 ELSESELECT@avg=@sum/@countEND[例7.52]输入某学院名称，统计该学院每个班级同学的选课信息，返回班级编号、班级名称、课程名称、课程选课人数、课程平均分。CREATEPROCEDUREproInstitute(@institutevarchar(30))ASBEGINDECLARE@classNamevarchar(30),@courseNamevarchar(30)DECLARE@classNochar(6),@counttinyint,@avgnumeric(5,1)--创建临时表，存放每个班级的班级编号、班级名称、课程名称、课程选课人数、课程平均分CREATETABLE#myTemp(classNochar(6),classNamevarchar(30),courseNamevarchar(30),classCounttinyint,classAvgnumeric(5,1))--定义游标curClass，依据输入参数@institute，查找班级编号和名称DECLAREcurClassCURSORFORSELECTclassNo,classNameFROMClassWHEREinstitute=@instituteOPENcurClassFETCHcurClassINTO@classNo,@classNameWHILE(@@FETCH_STATUS=0)BEGIN--定义游标curCourse，查找班级编号为@classNo班所选课的课程名称--课程选课人数、课程平均分DECLAREcurCourseCURSORFORSELECTcourseName,count(*),avg(score)FROMStudenta,Scoreb,CoursecWHEREa.studentNo=b.studentNoANDb.courseNo=c.courseNoANDclassNo=@classNoGROUPBYcourseNameOPENcurCourseFETCHcurCourseINTO@courseName,@count,@avgWHILE(@@FETCH_STATUS=0)BEGIN--将班级编号、班级名称、课程名称、课程选课人数、课程平均分--插入到临时表#myTemp中INSERTINTO#myTempVALUES(@classNo,@className,@courseName,@count,@avg)--获取下一游标值，取该班下一门课程的课程名、选课人数和平均分FETCHcurCourseINTO@courseName,@count,@avgENDCLOSEcurCourseDEALLOCATEcurCourse--获取游标curClass的下一个值，即取下一个班级FETCHcurClassINTO@classNo,@classNameEND CLOSEcurClassDEALLOCATEcurClass--显示临时表的内容，同时将临时表的内容返回给调用者SELECT*FROM#myTempEND[例7.53]执行存储过程proStudentByNo1。EXECUTEproStudentByNo1"1600001"[例7.54]执行存储过程proStudentByNo2。DECLARE@avgnumeric(5,1)EXECUTEproStudentByNo2"1600001",@avgOUTPUTSELECT@avg[例7.55]执行过程proInstitute。EXECUTEproInstitute"信息管理学院"也可以使用命令：DECLARE@institutevarchar(30)SET@institute="信息管理学院"EXECUTEproInstitute@institute[例7.56]修改例7.51的存储过程proStudentByNo2，将显示结果的语句删除。ALTERPROCEDUREproStudentByNo2(@sNochar(7),@avgnumeric(6,2)OUTPUT)ASBEGIN……--省略的程序代码见例7.51WHILE(@@FETCH_STATUS=0)BEGIN--业务处理，删除了原来的显示结果的语句：SELECT@sName,@cName,@scoreSET@sum=@sum+@scoreSET@count=@count+1FETCHcurScoreINTO@sName,@cName,@scoreEND……--省略的程序代码见例7.51END[例7.57]删除存储过程proStudentByNo1。DROPPROCEDUREproStudentByNo1[例7.58]创建触发器，保证学生表中的性别仅能取男或女。CREATETRIGGERsexIns--创建插入触发器ONStudent--触发器作用的表FORINSERT--触发器的类型，即触发该触发器被自动执行的事件ASIFEXISTS(SELECT*FROMinsertedWHEREsexNOTIN("男","女"))ROLLBACK--事务的回滚操作，即终止触发该触发器的插入操作CREATETRIGGERsexUpt--创建修改触发器ONStudentFORUPDATE ASIFEXISTS(SELECT*FROMinsertedWHEREsexNOTIN("男","女"))ROLLBACK该例也可以合并为一个触发器，如下所示：CREATETRIGGERsexUptInsONStudentFORINSERT,UPDATEASIFEXISTS(SELECT*FROMinsertedWHEREsexNOTIN("男","女"))ROLLBACK[例7.59]创建触发器，如果对学生表进行了更新(插入、删除和修改)操作，则自动修改班级表中的班级人数。假设一次仅允许更新一个学生记录，否则当作违反约束规则。CREATETRIGGERClassIns--创建插入触发器，inserted表结构与Student表结构相同ONStudentFORINSERTASBEGINDECLARE@classNochar(6)--变量@classNo用于接受插入学生所属的班级编号IF(SELECTcount(*)FROMinserted)>1ROLLBACK--不允许一次插入多个学生记录ELSEBEGINSELECT@classNo=classNo--找出插入学生的班级编号赋给变量@classNoFROMinsertedUPDATEClassSETclassNum=classNum+1WHEREclassNo=@classNo--修改班级表中班级编号为@classNo的班级人数ENDENDCREATETRIGGERClassDel--创建删除触发器，deleted表结构与Student表结构相同ONStudentFORDELETEASBEGINDECLARE@classNochar(6)--变量@classNo用于接受删除学生所属的班级编号IF(SELECTcount(*)FROMdeleted)>1ROLLBACK--不允许一次删除多个学生记录ELSEBEGINSELECT@classNo=classNo--找出删除学生的班级编号赋给变量@classNoFROMdeletedUPDATEClassSETclassNum=classNum-1WHEREclassNo=@classNo--修改班级表中班级编号为@classNo的班级人数ENDENDCREATETRIGGERClassUpt--创建修改触发器，deleted和inserted表结构同StudentONStudentFORUPDATEASBEGIN/*声明两个变量@oldClassNo和@newClassNo，分别接受学生修改前、后的班级编号*/DECLARE@oldClassNochar(6),@newClassNochar(6)IF(SELECTcount(*)FROMdeleted)>1ROLLBACK--不允许一次修改多个学生记录ELSEBEGINSELECT@oldClassNo=classNo--找出修改前学生的班级编号赋给@oldClassNo FROMdeletedSELECT@newClassNo=classNo--找出修改后学生的班级编号赋给@newClassNoFROMinsertedUPDATEClassSETclassNum=classNum-1WHEREclassNo=@oldClassNo--修改班级编号为@oldClassNo的班级人数UPDATEClassSETclassNum=classNum+1WHEREclassNo=@newClassNo--修改班级编号为@newClassNo的班级人数ENDEND本例在更新触发器中要同时使用两张触发器表。如果一次允许更新多个学生记录，则实现自动修改班级表中班级人数的插入触发器如下，请读者写出相应的删除和修改触发器。CREATETRIGGERClassInsMany--创建允许一次插入多条学生记录的插入触发器ONStudentFORINSERTASBEGINDECLARE@classNochar(6)--变量@classNo用于接受所插入的学生所属的班级编号DECLAREcurStudentCURSORFOR--定义一个游标对多个插入的学生进行逐个处理SELECTclassNoFROMinsertedOPENcurStudentFETCHcurStudentINTO@classNoWHILE(@@FETCH_STATUS=0)BEGINUPDATEClassSETclassNum=classNum+1WHEREclassNo=@classNo--更新班级表中班级编号为@classNo的班级人数FETCHcurStudentINTO@classNoENDCLOSEcurStudentDEALLOCATEcurStudentEND也可以不使用游标，直接通过一条SQL语句完成班级人数的修改，插入触发器如下：CREATETRIGGERClassInsMany1ONStudentFORINSERTASUPDATEClassClaSETCla.classNum=Cla.classNum+InsCnt.cntFROM(SELECTIns.classNo,count(*)cnt--统计每一个班级插入学生的人数cntFROMinsertedInsGROUPBYIns.classNo)InsCntWHEREInsCnt.classNo=Cla.classNo[例7.60]创建触发器，只有数据库拥有者(dbo)才可以修改成绩表中的成绩，其它用户对成绩表的插入和删除操作必须记录下来。(1)为了记录用户的操作轨迹，首先创建一张审计表，表结构如下：CREATETABLETraceEmployee(useridvarchar(20)NOTNULL,--用户标识numberintNOTNULL,--操作次数operateDatedatetimeNOTNULL,--操作时间operateTypechar(6)NOTNULL,--操作类型：插入/删除/修改studentNochar(7)NOTNULL,courseNochar(3)NOTNULL,termNochar(3)NOTNULL,scorenumeric(5,1)NOTNULL,CONSTRAINTTraceEmployeePKPRIMARYKEY(userid,number)) (2)分别建立3个触发器，阻止非dbo用户对成绩表的修改，并将非dbo用户对成绩表的插入和删除操作的轨迹添加到审计表TraceEmployee中。CREATETRIGGERScoreTracIns--创建插入类型的触发器ONScoreFORINSERTASBEGINDECLARE@studNochar(7),@courNochar(3),@termNochar(3),@scorenumeric(5,1)DECLARE@numintIFuser<>"dbo"ANDEXISTS(SELECT*FROMinserted)--非dbo对成绩表插入BEGINSELECT@num=max(number)--获取该用户以前的操作次数FROMTraceEmployeeWHEREuserid=userIF@numISNULLSELECT@num=0DECLAREcurTranceCURSORFORSELECT*FROMinserted--inserted表结构与触发器作用的Score表相同OPENcurTranceFETCHcurTranceINTO@studNo,@courNo,@termNo,@scoreWHILE(@@FETCH_STATUS=0)BEGIN--通过游标将非dbo用户对成绩表进行的所有插入操作记录下来SET@num=@num+1--该用户的操作次数自动加1INSERTINTOTraceEmployeeVALUES--在审计表中添加用户的插入操作轨迹(user,@num,getdate(),"insert",@studNo,@courNo,@termNo,@score)FETCHcurTranceINTO@studNo,@courNo,@termNo,@scoreENDCLOSEcurTranceDEALLOCATEcurTranceENDENDCREATETRIGGERScoreTracDel--创建删除类型的触发器ONScoreFORDELETEASBEGINDECLARE@studNochar(7),@courNochar(3),@termNochar(3),@scorenumeric(5,1)DECLARE@numintIFuser<>"dbo"ANDEXISTS(SELECT*FROMdeleted)--非dbo对成绩表进行删除BEGIN……--省略的程序代码见插入类型的触发器ScoreTracInsDECLAREcurTranceCURSORFORSELECT*FROMdeleted--deleted表结构与触发器作用的Score表相同OPENcurTranceFETCHcurTranceINTO@studNo,@courNo,@termNo,@scoreWHILE(@@FETCH_STATUS=0)BEGIN--通过游标将非dbo用户对成绩表进行的所有删除操作记录下来SET@num=@num+1--该用户的操作次数自动加1INSERTINTOTraceEmployeeVALUES--在审计表中添加用户的删除操作轨迹(user,@num,getdate(),"delete",@studNo,@courNo,@termNo,@score)FETCHcurTranceINTO@studNo,@courNo,@termNo,@scoreENDCLOSEcurTranceDEALLOCATEcurTranceENDEND CREATETRIGGERScoreTracUpt--创建修改类型的触发器ONScoreFORUPDATEASIFuser!="dbo"ANDEXISTS(SELECT*FROMdeleted)--非dbo不允许修改成绩ROLLBACK[例7.61]修改例7.60中的修改类型的触发器，允许非dbo用户修改Score表的成绩数据，但是必须将修改操作的轨迹记录在审计表TraceEmployee中。ALTERTRIGGERScoreTracUptONScoreFORUPDATEASBEGIN/*声明两个变量@oldScore和@newScore，分别接受修改前、后的成绩*/DECLARE@oldScorenumeric(5,1),@newScorenumeric(5,1)DECLARE@studNochar(7),@courNochar(3),@termNochar(3),@numintIFuser<>"dbo"BEGINIFupdate(studentNo)ORupdate(courseNo)ORupdate(termNo)ROLLBACK--如果更新了学号、课程号或学期号属性，则回滚ELSEIFUPDATE(score)BEGINSELECT@num=max(number)--获取该用户以前的操作次数FROMTraceEmployeeWHEREuserid=userIF@numISNULLSELECT@num=0--定义游标uptCur，找出给定学号、课程号和学期号选课记录的修改前后的成绩DECLAREuptCurCURSORFORSELECTa.score,b.score,a.studentNo,a.courseNo,a.termNoFROMinserteda,deletedbWHEREa.studentNo=b.studentNoANDa.courseNo=b.courseNoANDa.termNo=b.termNoOPENuptCur--打开游标--获取当前游标值FETCHuptCurINTO@newScore,@oldScore,@studNo,@courNo,@termNoWHILE(@@FETCH_STATUS=0)BEGIN--对每次成绩修改，在审计表中添加两条记录，分别反映修改前后的成绩SET@num=@num+1--该用户的操作次数自动加1INSERTINTOTraceEmployeeVALUES(user,@num,getdate(),"oldUpt",@studNo,@courNo,@termNo,@oldScore)SET@num=@num+1--该用户的操作次数自动加1INSERTINTOTraceEmployeeVALUES(user,@num,getdate(),"newUpt",@studNo,@courNo,@termNo,@newScore)FETCHuptCurINTO@newScore,@oldScore,@studNo,@courNo,@termNoENDCLOSEuptCur--关闭游标DEALLOCATEuptCur--释放游标ENDENDEND[例7.62]删除触发器ClassInsMany。DROPTRIGGERClassInsMany 第9章[例9.1]创建登录账号为login1、密码为p666666的登录账号；创建登录账号为login2、密码为p888888的登录账号。sp_addlogin"login1","p666666"sp_addlogin"login2","p888888"[例9.2]创建登录账号login3，密码为p123456，默认的数据库为ScoreDB。sp_addloginlogin3,"p123456","ScoreDB"[例9.3]将login3的密码修改为p654321。sp_password"p123456","p654321","login3"[例9.4]将login3访问的数据库修改为BookDB。sp_defaultdb"login3","BookDB"[例9.5]删除登录账号login3。sp_droplogin"login3"[例9.6]将登录账号login1添加到当前数据库OrderDB中，且用户名为u1。sp_adduserlogin1,u1[例9.7]将登录账号login2添加到当前数据库OrderDB中，且用户名为u2。sp_adduserlogin2,u2[例9.8]从当前数据库中删除用户u1。sp_dropuseru1[例9.9]将创建表和视图的权限授予用户u1和u2。GRANTcreatetable,createviewTOu1,u2[例9.10]从用户u2收回创建视图的权限。REVOKEcreateviewFROMu2[例9.11]将存储过程proSearchBySno的执行权限授予用户u1、u2和u3。GRANTexcuteONproSearchBySnoTOu1,u2,u3[例9.12]将对班级表Class的查询、插入权限授予用户u1，且用户u1可以转授其所获得的权限给其它用户。GRANTselect,insertONClassTOu1WITHGRANTOPTION[例9.13]将对学生表的性别、出生日期的查询和修改权限授予用户u3、u4和u5，且不可以转授权限。 GRANTselect,updateONStudent(sex,birthday)TOu3,u4,u5[例9.14]将表Score的若干权限分别授予用户u1、u2、u3、u4、u5和u6。(1)将表Score的所有权限授予用户u1，且可以转授权限。GRANTallONScoreTOu1WITHGRANTOPTION(2)用户u1将表Score的所有权限授予用户u2，且可以转授权限。GRANTallONScoreTOu2WITHGRANTOPTION(3)用户u2将表Score的查询和插入权限授予用户u5，且不可以转授权限。GRANTselect,insertONScoreTOu5(4)用户u2将表Score的所有权限授予用户u4，且可以转授权限。GRANTallONScoreTOu4WITHGRANTOPTION(5)用户u4将表Score的查询和删除权限授予用户u6，且可以转授权限。GRANTselect,deleteONScoreTOu6WITHGRANTOPTION[例9.15]用户u2将转授给用户u4的对表Score的修改和查询权限收回。REVOKEselect,updateONScoreFROMu4CASCADE[例9.16]用户u4将转授给用户u6的对表Score的查询权限收回。REVOKEselectONScoreFROMu6[例9.17]建立角色r1和r2。sp_addrole"r1"sp_addrole"r2"[例9.18]删除数据库角色r2。sp_droprole"r2"[例9.19]将用户u2添加到数据库角色r1中。sp_addrolemember"r1","u2"[例9.20]在数据库角色r1中删除用户u2。sp_droprolemember"r1","u2"[例9.21]通过角色实现将一组权限授予一个用户。(1)创建一个角色Role1。sp_addrole"Role1"(2)使用GRANT语句，使角色Role1拥有Student表的select、update、insert权限。GRANTselect,update,insertONStudentTORole1(3)将角色Role1授予用户u1、u2和u3，使他们具有角色Role1所包含的全部权限。sp_addrolemember"Role1","u1"sp_addrolemember"Role1","u2"sp_addrolemember"Role1","u3"或(以下语句是SQL标准语法，但SQLServer不支持)GRANTRole1TOu1,u2,u3 (4)通过角色Role1可以一次性地收回已授予用户u1的这3个权限。sp_droprolemember"Role1","u1"或(以下语句是SQL标准语法，但SQLServer不支持)REVOKERole1FROMu1[例9.22]将对表Student的删除权限授予角色Role1，并收回查询权限。GRANTdeleteONStudentTORole1REVOKEselectONStudentFROMRole1[例9.23]在班级表Class中将classNo定义为主码。CREATETABLEClass(classNochar(6)NOTNULL,--班级号，列约束classNamevarchar(30)UNIQUENOTNULL,--班级名，列约束institutevarchar(30)NOTNULL,--所属学院，列约束gradesmallintDEFAULT0NOTNULL,--年级，列约束classNumtinyintNULL,--班级人数，列约束CONSTRAINTClassPKPRIMARYKEY(classNo)--元组约束)[例9.24]在学生成绩表Score中将studentNo、courseNo、termNo定义为主码。CREATETABLEScore(studentNochar(7)NOTNULL,--学号courseNochar(3)NOTNULL,--课程号termNochar(3)NOTNULL,--学期号scorenumeric(5,1)DEFAULT0NOTNULL,--成绩/*主码由3个属性构成，必须作为元组约束进行定义*/CONSTRAINTScorePKPRIMARYKEY(studentNo,courseNo,termNo))[例9.25]在学生成绩表Score中分别将studentNo、courseNo、termNo定义为外码。CREATETABLEScore(…--省略的内容包括属性定义和主码约束定义，参见例9.24/*外码约束只能定义为表约束，studentNo是外码，被参照表是Student*/CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESStudent(studentNo),/*外码约束只能定义为表约束，courseNo是外码，被参照表是Course*/CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCESCourse(courseNo),/*外码约束只能定义为表约束，termNo是外码，被参照表是Term*/CONSTRAINTScoreFK3FOREIGNKEY(termNo)REFERENCESTerm(termNo))[例9.26]在学生成绩表Score中分别将studentNo、courseNo和termNo定义为外码，且studentNo外码定义为级联删除和修改操作，courseNo外码定义为级联修改操作。CREATETABLEScore(…--省略的内容包括属性定义和主码约束定义，参见例9.24/*外码约束只能定义为表约束，studentNo是外码，被参照表是Student*/ CONSTRAINTScoreFK1FOREIGNKEY(studentNo)REFERENCESStudent(studentNo)ONDELETECASCADE--学生表删除元组时，级联删除成绩表中相应元组ONUPDATECASCADE,--学生表修改学号时，级联修改成绩表中相应元组/*外码约束只能定义为表约束，courseNo是外码，被参照表是Course*/CONSTRAINTScoreFK2FOREIGNKEY(courseNo)REFERENCESCourse(courseNo)ONDELETENOACTION--该定义为默认值，可以不定义ONUPDATECASCADE,--课程表修改课程号时，级联修改成绩表中相应元组/*外码约束只能定义为表约束，termNo是外码，被参照表是Term*/CONSTRAINTScoreFK3FOREIGNKEY(termNo)REFERENCESTerm(termNo))[例9.27]创建学生表Stud，属性及要求为：学号studNo为5位字符，且第1位为字母D、M或U，其他4位为数字，主码，不允许为空值；姓名studName为12位字符，不允许为空值，且值必须唯一；性别sex为2位字符，允许为空值，但值只能取"男"或"女"；年龄age为整型，允许为空值，缺省值为16，取值范围(0,60)；民族nation为变长20位字符，允许为空值，缺省值为"汉族"。CREATETABLEStud(--学号，列约束：不允许为空值；第1位为字母D、M或U，其他4位为数字，约束名为sNoCKstudNochar(5)NOTNULLCONSTRAINTsNoCKCHECK(studNoLIKE"[D,M,U][0-9][0-9][0-9][0-9]"),--姓名，列约束：不允许为空值；取值必须唯一studNamechar(12)UNIQUENOTNULL,--性别，列约束：允许为空值，仅取男或女两个值sexchar(2)NULLCHECK(sexIN("男","女")),--年龄，列约束：允许为空值，默认值为16；取值范围(0,60)，约束名为ageCKagetinyintDEFAULT16NULLCONSTRAINTageCKCHECK(age>0ANDage<60),nationvarchar(20)DEFAULT"汉族"NULL,--民族，允许空，默认汉族CONSTRAINTStudPKPRIMARYKEY(studNo)--元组约束)[例9.28]在学生表Stud中定义：如果是男同学，则其姓名不能以刘开头。  CREATETABLEStud(…--省略的属性定义参见例9.27CONSTRAINTSexCKCHECK(sex="女"ORstudNameNOTLIKE"刘%"),--元组约束CONSTRAINTStudPKPRIMARYKEY(studNo)--元组约束)性别sex与姓名studName属性列之间的元组约束也可以表达为：CONSTRAINTSexCKCHECK(NOT(sex="男"ANDstudNameLIKE"刘%")),--元组约束[例9.29]对于例9.27，将学号studNo、性别sex、年龄age的取值约束不作为列约束来定义，而是放在定义属性之后再单独定义，即作为元组约束来定义，其它要求相同。CREATETABLEStud(studNochar(5)NOTNULL,--学号，不允许空值studNamechar(12)UNIQUENOTNULL,--姓名，不允许空值，取值唯一sexchar(2)NULL,--性别，允许空值 agetinyintDEFAULT16NULL,--年龄，允许空值，默认值为16nationvarchar(20)DEFAULT"汉族"NULL,--民族，允许空值，默认值为汉族CONSTRAINTsNoCKCHECK(studNoLIKE"[D,M,U][0-9][0-9][0-9][0-9]"),--元组约束CONSTRAINTsexValueCHECK(sexIN("男","女")),--元组约束CONSTRAINTageCKCHECK(age>0ANDage<60),--元组约束CONSTRAINTStudPKPRIMARYKEY(studNo)--元组约束)[例9.30]在例9.27的基础上，修改表Stud中的约束条件，要求学号改为在15001～25999之间，年龄由(0,60)之间修改为[15,50]之间。首先，删除已经存在的约束：ALTERTABLEStudDROPCONSTRAINTsNoCKALTERTABLEStudDROPCONSTRAINTageCK然后，添加修改后的约束：ALTERTABLEStudADDCONSTRAINTsNoCKCHECK(studNoBETWEEN"15001"AND"25999")ALTERTABLEStudADDCONSTRAINTageCKCHECK(ageBETWEEN15AND50)[例9.31]当插入学生选课记录时，必须保证该学生已经选修了本次选修课程的先修课程，且同一学期选课不能超出30个学分。CREATETRIGGERinsScoreLimitONScoreFORINSERTASBEGINDECLARE@stuNochar(7),@terNochar(3),@pCouNochar(3)DECLARE@sumCreHournumeric(5,1)DECLAREmyCurCURSORFORSELECTIns.studentNo,Ins.termNo,Cou.priorCourseFROMinsertedIns,CourseCouWHEREIns.courseNo=Cou.courseNoOPENmyCurFETCHmyCurINTO@stuNo,@terNo,@pCorNoWHILE(@@FETCH_STATUS=0)BEGINIF(@pCouNoISNOTNULL)ANDNOTEXISTS(SELECT*FROMScoreWHEREcourseNo=@pCouNoANDstudentNo=@stuNo)ROLLBACK--学生@stuNo没有选修过本次选修课程的先修课程@pCouNo，回滚ELSEBEGIN--统计@stuNo学生在@terNo学期所选修课程的总学分SELECT@sumCreHour=isnull(sum(creditHour),0)FROMScorex,CourseyWHEREx.courseNo=y.courseNoANDstudentNo=@stuNoANDtermNo=@terNoGROUPBYstudentNo,termNo--可以删除该GROUPBY子句IF@sumCreHour>30ROLLBACK--@stuNo学生在@terNo学期选修课程超出30学分，回滚 ENDFETCHmyCurINTO@stuNo,@terNo,@pCorNoENDCLOSEmyCurDEALLOCATEmyCurEND[例9.32]编写一个触发器，判断会员设置的配送方案是否正确(即一个订单设置的多个配送单是否正好将该订单所订购的所有图书全部安排配送了)。本例编写的触发器就是实现6.1.5节的第(9)条业务规则和完整性约束要求。CREATETRIGGERShipBookInsONShipBookFORINSERTASBEGIN/*如果配送明细中存在订单明细中没有订购的图书，或者订单明细中存在配送明细中未被配送的图书，则这次配送设置全部作废*/IF(EXISTS(SELECT*--表示配送明细中存在订单明细中没有订购的图书FROMinsertedinsLEFTOUTERJOINOrderBookordBONordB.orderNo=ins.orderNoANDordB.ISBN=ins.ISBNWHEREordB.ISBNISNULL)OREXISTS(SELECT*--表示订单明细中存在配送明细中未被配送的图书FROMinsertedinsRIGHTOUTERJOIN(SELECTa.orderNo,a.ISBN--查找本次配送的订单明细FROMOrderBooka,insertedbWHEREa.orderNo=b.orderNo)ASordBONordB.orderNo=ins.orderNoANDordB.ISBN=ins.ISBNWHEREins.ISBNISNULL))BEGIN//配送方案不正确，取消本次配送设置DELETEFROMShipBook--删除本次插入的配送明细WHEREorderNoIN(SELECTorderNoFROMinserted)DELETEFROMShipSheet--删除本次插入的配送单WHEREorderNoIN(SELECTorderNoFROMinserted)ENDELSEBEGIN/*计算本次配送设置中每种图书在所有配送单的配送明细中的总配送数量*/DECLARE@orderNochar(15),@isbnchar(17),@sumShipQtyintDECLAREmyCurCURSORFORSELECTorderNo,ISBN,sum(shipQuantity)sumShipQtyFROMinsertedGROUPBYorderNo,ISBNOPENmyCurFETCHmyCurINTO@orderNo,@isbn,@sumShipQty--SELECT@orderNo,@isbn,@sumShipQtyWHERE(@@FETCH_STATUS=0)BEGIN/*判断某订单设置的所有配送单中每一种图书的配送数量是否与该订单的订单明细中该图书的订购数量一致*/IFEXISTS(SELECT*FROMOrderBookWHEREorderNo=@orderNoANDISBN=@isbnANDquantity<>@sumShipQty)BEGIN /*@isbn图书的配送数量与订购数量不一致，取消本次配送设置*/DELETEFROMShipBook--删除本次插入的配送明细WHEREorderNoIN(SELECTorderNoFROMinserted)DELETEFROMShipSheet--删除本次插入的配送单WHEREorderNoIN(SELECTorderNoFROMinserted)BREAK--结束循环ENDFETCHmyCurINTO@orderNo,@isbn,@sumShipQtyENDCLOSEmyCurDEALLOCATEmyCurENDEND上述触发器程序中的第一个IF语句也可以改写为：IF(EXISTS(SELECT*FROMinsertedinsWHEREins.ISBNNOTIN--表示配送明细中存在订单明细中没有订购的图书(SELECTordB.ISBNFROMOrderBookordBWHEREordB.orderNo=ins.orderNo))OREXISTS(SELECT*FROMOrderBookordB,insertedinsWHEREordB.orderNo=ins.orderNoANDordB.ISBNNOTIN--表示订单明细中存在配送明细中未被配送的图书(SELECTinserted.ISBNFROMinsertedWHEREinserted.orderNo=ordB.orderNo)))[例9.33]编写一个触发器，自动修改订单中的订单状态。本例编写的触发器就是实现6.1.5节的第(15)条业务规则和完整性约束要求。CREATETRIGGERorderBookUptONOrderBookFORUPDATEASBEGINDECLARE@orderNochar(15)IFupdate(shipState)--如果订单明细表中的配送状态shipState进行了修改BEGINDECLAREmyCurCURSORFORSELECTDISTINCTorderNoFROMinsertedOPENmyCurFETCHmyCurINTO@orderNoWHILE(@@FETCH_STATUS=0)BEGIN/*对于一个订单，如果订单明细表中不存在配送状态不是"已全部送到"的图书(即所有图书的配送状态均为"已全部送到")，则该订单的订单状态为"已处理结束"*/IFNOTEXISTS(SELECT*FROMOrderBookWHEREorderNo=@orderNoANDshipState!="E")UPDATEOrderSheetSETorderState="F"WHEREorderNo=@orderNoELSE/*否则，如果订单明细表中不存在配送状态为"未配送"或"已部分配送"的图书，则该订单的订单状态为"已全部配送"*/IFNOTEXISTS(SELECT*FROMOrderBookWHEREorderNo=@orderNoANDshipState<"C")UPDATEOrderSheetSETorderState="E" WHEREorderNo=@orderNoELSE/*否则，如果订单明细表中存在配送状态为"已部分配送"的图书，则该订单的订单状态为"已部分配送"*/IFEXISTS(SELECT*FROMOrderBookWHEREorderNo=@orderNoANDshipState="B")UPDATEOrderSheetSETorderState="D"WHEREorderNo=@orderNoFETCHmyCurINTO@orderNoENDCLOSEmycurDEALLOCATEmycurENDEND[例9.34]编写一个存储过程，统计某会员在给定期间购买图书的详细情况，并按如下表格形式显示统计结果。购买图书汇总表会员编号：xxxxxxxxxx姓名：xxxxxxxxxxxxxxxxxxxxxxxx～xxxx年=================================================================================图书编号图书名称购买年份数量应收金额实收金额-----------------------------------------------------------------------------------------------------------------------------------------xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxx.xx…………xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.xxxxxxx.xx…………………………-----------------------------------------------------------------------------------------------------------------------------------------会员等级：xxxx累计购买金额：xxxxxxx.xx合计：xxxxxxxxxxx.xxxxxxx.xx=================================================================================CREATEPROCEDUREqryMemPur(@mNochar(10),@beginYearint,@endYearint)ASBEGINDECLARE@mNamevarchar(20),@mLevelchar(1),@mTolAmtnumeric(18,2)DECLARE@isbnchar(17),@bookTitlevarchar(30),@yearintDECLARE@sumQtyint,@sumRecnumeric(18,2),@sumPaidnumeric(18,2)DECLARE@tolQtyint,@tolRecnumeric(18,2),@tolPaidnumeric(18,2)SELECT@tolQty=0,@tolRec=0,@tolPaid=0/*查找会员基本信息*/SELECT@mName=memName,@mTolAmt=totalAmount,@mLevel=memLevelFROMMemberWHEREmemberNo=@mNoIF@@ROWCOUNT=1--如果找到会员，输出会员基本信息及订单信息BEGIN/*输出表头*/PRINTspace(40)+"购买图书汇总表"PRINT"会员编号："+@mNo+space(10)+"会员姓名："+@mName+space(20)+convert(char(4),@beginYear)+"～"+convert(char(4),@endYear)PRINTreplicate("=",100)PRINTconvert(char(20),"图书编号")+convert(char(32),"图书名称")+convert(char(10),"购买年份")+convert(char(10),"数量")+convert(char(18),"应收金额")+convert(char(18),"实收金额") PRINTreplicate("-",100)DECLAREmyCurCURSORFORSELECTb.ISBN,bookTitle,year(orderDate)year,sum(quantity)sumQty,sum(amtReceivable)sumAmtRec,sum(paidAmt)sumPaidAmtFROMOrderSheeta,OrderBookb,BookcWHEREa.orderNo=b.orderNoANDb.ISBN=c.ISBNANDmemberNo=@mNoANDyear(orderDate)BETWEEN@beginYearAND@endYearGROUPBYb.ISBN,bookTitle,year(orderDate)OPENmyCurFETCHmyCurINTO@isbn,@bookTitle,@year,@sumQty,@sumRec,@sumPaidWHILE(@@FETCH_STATUS=0)BEGIN/*逐行输出数据*/PRINTconvert(char(18),@isbn)+convert(char(32),@bookTitle)+convert(char(10),@year)+convert(char(10),@sumQty)+convert(char(18),@sumRec)+convert(char(18),@sumPaid)/*数据汇总*/SET@tolQty=@tolQty+@sumQtySET@tolRec=@tolRec+@sumRecSET@tolPaid=@tolPaid+@sumPaidFETCHmyCurINTO@isbn,@bookTitle,@year,@sumQty,@sumRec,@sumPaidENDCLOSEmyCurDEALLOCATEmyCur/*输出汇总数据*/PRINTreplicate("-",100)PRINT"会员等级："+@mLevel+space(10)+"累计购买金额："+convert(char(18),@mTolAmt)+convert(char(10),"合计：")+convert(char(10),@tolQty)+convert(char(18),@tolRec)+convert(char(18),@tolPaid)PRINTreplicate("=",100)ENDELSEPRINT"数据库中不存在编号为"+@mNo+"的会员！"END'