数据库实验及数据库实验二

实验三建表、修改表、删除表1.建立数据库jxgl在jxgl数据库中建立学生表student,课程表course,选修表sc，建表过程有如下方式1）以图形界面操作2）以SQL操作（以下脚本可直接在查询分析器中执行）CreateTableStudent(SnoCHAR(5)NOTNULLPRIMARYKEY(Sno),SnameVARCHAR(20),SageSMALLINTCHECK(Sage>=15ANDSage<=45),SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女'),SdeptCHAR(2));CreateTableCourse(CnoCHAR(2)NOTNULLPRIMARYKEY(Cno),CnameVARCHAR(20),CpnoCHAR(2),CcreditSMALLINT);CreateTableSC(SnoCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESStudent(Sno),CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARYKEY(Sno,Cno),CONSTRAINTC_FFOREIGNKEY(Cno)REFERENCESCourse(Cno));INSERTINTOStudentVALUES('98001','钱横',18,'男','CS');INSERTINTOStudentVALUES('98002','王林',19,'女','CS');INSERTINTOStudentVALUES('98003','李民',20,'男','IS');INSERTINTOStudentVALUES('98004','赵三',16,'女','MA');INSERTINTOCourseVALUES('1','数据库系统','5',4);INSERTINTOCourseVALUES('2','数学分析',null,2);INSERTINTOCourseVALUES('3','信息系统导论','1',3);INSERTINTOCourseVALUES('4','操作系统原理','6',3);INSERTINTOCourseVALUES('5','数据结构','7',4);INSERTINTOCourseVALUES('6','数据处理基础',null,4);INSERTINTOCourseVALUES('7','C语言','6',3);INSERTINTOSCVALUES('98001','1',87);INSERTINTOSCVALUES('98001','2',67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSCVALUES('98002','2',95);INSERTINTOSCVALUES('98002','3',88);课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit）学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept）学生选课表SC（学号Sno,课程号Cno,成绩Grade)

实验四Select查询操作练习（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询：①检索年龄大于23岁的男学生的学号和姓名；selectSno,SnamefromStudentwhereSage>'23'ANDSsex='男';②检索至少选修一门课程的女学生的姓名；selectSnamefromStudent,SCwhereSsex='女'ANDStudent.Sno=SC.SnogroupbyStudent.Snamehavingcount(*)>=1;或者SelectSnameFromStudentWhereSsex='女'ANDSnoin(selectsnofromSCgroupbysnohavingcount(*)>=1);③检索王同学不学的课程的课程号；selectCnofromCoursewhereCourse.Cnonotin(selectCnofromSC,StudentwhereSC.Sno=Student.SnoANDSnameLIKE'王%');④检索至少选修两门课程的学生学号；selectDISTINCTStudent.SnofromStudent,SCWHEREStudent.Sno=SC.SnoGROUPBYStudent.SnoHAVINGCOUNT(*)>=2;⑤检索全部学生都选修的课程的课程号与课程名；SELECTCno,CnamefromCoursewherenotexists(select*fromstudentwherenotexists(select*fromSCwhereSC.sno=Student.SnoANDSC.Cno=Course.Cno))或者假设所有学生只有两人SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=(selectcount(*)fromStudent));SELECTCno,CnamefromCourseWHERECourse.Cnoin(selectCnofromSCgroupbySC.Cnohavingcount(Sno)=2);⑥检索选修了所有3学分课程的学生学号。selectdistinctStudent.SnofromStudent,SCwhereexists(select*fromCoursewhereCcredit='3'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno);基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询：①统计有学生选修的课程门数；selectcount(distinctSC.Cno)FROMSC;②求选修4号课程的学生的平均年龄；SELECTavg(Student.Sage)fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='4';SELECTavg(Student.Sage)as平均年龄fromStudent,SCwhereStudent.Sno=SC.SnoANDCno='3';③求学分为3的每门课程的学生平均成绩；SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'groupbySC.Cno用groupby语句以课程号分组注意，如果程序是这样的：SELECTavg(SC.Grade)fromCourse,SC,StudentwhereStudent.Sno=SC.SnoANDCourse.Ccredit='3'ANDCourse.Cno=SC.Cno;只显示一门课程的成绩！④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECTCno,count(Sno)fromSCGROUPBYCnoHAVINGCount(Sno)>3orderbycount(sno)DESC,Cno;SELECTCno,count(Sno)as选修人数fromSCGROUPBYCnoHAVINGCount(Sno)>1orderbycount(sno)DESC,Cno;⑤检索学号比“王林”同学大而年龄比他小的学生姓名；SELECTSnameFROMStudentwhereSno>(selectSnofromStudentwhereSname='王林')ANDSage<(selectSagefromStudentwhereSname='王林');或者也可以selectX.snamefromStudentasX,StudentasYwhereX.Sno>Y.SnoANDX.Sage<Y.SageANDY.Sname='王林';⑥检索姓名以“王”开头的所有学生的姓名和年龄；selectSname,SagefromStudentwhereStudent.Snamelike'王%';注意：like那里不能用等号“=”⑦在SC中检索成绩为空值的学生学号和课程号；SELECTSno,CnofromSCwhereGradeisnull注意：isnull那里不能用等号“=”⑧求年龄大于女学生平均年龄的男学生姓名和年龄；SELECTSname,SagefromStudentwhereSage>(selectavg(Sage)fromstudentwhereSsex='女')ANDSsex='男';⑨求年龄大于所有女学生年龄的男学生姓名和年龄；SELECTSname,SagefromStudentwhereSage>(SELECTMAX(Sage)fromStudentwhereSsex='女')ANDSsex='男';⑩检索所有比“王林”年龄大的学生姓名、年龄和性别；SELECTSname,Sage,SsexfromStudentwhereSage>(selectSagefromStudentwhereSname='王林')⑾检索选修“2”课程的学生中成绩最高的学生的学号；SELECTSno,GradefromSCwhereGrade=(selectMAX(Grade)fromSCwhereCno='2');注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,avg也是⑿检索学生姓名以及所选修课程的课程号和成绩；selectSname,Cno,GradefromSC,StudentwhereStudent.Sno=SC.Sno;⒀检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。选择4门以上selectsno,sum(Grade)sumfromSCwheregrade>=60ANDSnoin(selectSnofromSCgroupbySnohavingcount(Sno)>4)groupbysnoorderbysum(grade)desc注意：选择2门课（包括两门）selectsno,sum(Grade)fromSCwheregrade>=60ANDSnoin(selectSnofromSCgroupbySnohavingcount(Sno)>=2)groupbysnoorderbysum(grade)desc再注意：Selectsno,sum(grade)FromSCWheregrade>=60Groupbysnohavingcount(sno)>1Orderbysum(grade)desc因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。任课教师：《数据库原理及应用》实验报告学号：姓名：班级：

实验二 数据库的简单查询和高级查询实验目的：本实验的目的是使学生掌握SQLServer查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法，加深SQL语言的嵌套查询语句的理解，熟练掌握数据查询中的分组、统计、计算和集合的操作方法。二、实验内容（一）简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。求数学系学生的学号和姓名求选修了课程的学生学号求选修课程号为‘1’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。求选修课程号为‘1’且成绩在80~90之间的学生学号和成绩，并将成绩乘以0.8输出。求数学系或计算机系姓张的学生的信息。求缺少了成绩的学生的学号和课程号（二）连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。查询每个学生的情况以及他所选修的课程求学生的学号、姓名、选修的课程及成绩求选修课程号为‘1’且成绩在90以上的学生学号、姓名和成绩查询每一门课程的间接先行课（即先行课的先行课）（三）嵌套查询操作：在SQLServer查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。具体完成以下各题。将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。求选修了高等数学的学号和姓名求‘1’课程的成绩高于刘晨的学生学号和成绩求其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）求其他系中比计算机系学生年龄都小的学生求选修了‘2’课程的学生姓名求没有选修‘2’课程的学生姓名查询选修了全部课程的学生姓名求至少选修了学号为“95002”（四）集合查询和统计查询：分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。集合查询实验。该实验并操作UNION、交操作INTERSECT和差操作MINUS的实现方法。具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。求学生的总人数求选修了课程的学生人数求课程和选修了该课程的学生人数求选修超过3门课的学生学号查询计算机科学系的学生及年龄不大于19岁的学生查询计算机科学系的学生与年龄不大于19岁的学生的交集查询计算机科学系的学生与年龄不大于19岁的学生的差集查询选修课程‘1’的学生集合与选修课程‘2’的学生集合的交集查询选修课程‘1’的学生集合与选修课程‘2’的学生集合的差集三、实验方法将查询需求用SQL语言表示；在SQLServer查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute(标准执行)或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。所使用的学生管理库中的三张表为：STUDENT（学生信息表）SNO（学号）SNAME（姓名）SEX（性别）SAGE（年龄）SDEPT（所在系）95001李勇男20CS95002刘晨女19IS95003王名女18MA95004张立男19IS95005李明男22CS95006张小梅女23IS95007封晓文女20MACOURSE（课程表）CNO（课程号）CNAME（课程名）CPNO（先行课）CCREDIT（学分）1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64SC（选修表）SNO（学号）CNO（课程号）Grade(成绩)95001192950012859500138895002290950023809500317895003280950041909500446095005180950053899500638095007465四、实验结果与分析（SQL代码及查询结果截图）源代码：1.1SELECTSNO,SNAMEFROMSTUDENTWHERESDEPT='MA';1.2SELECTDISTINCTSNOFROMSC;1.3SELECTSNO,GradeFROMSCWHERECNO='1'ORDERBYGradeDESC,SNO;1.4SELECTSNO,Grade*0.8FROMSCWHERECNO='1'ANDGradeBETWEEN80AND901.5SELECT*FROMSTUDENTWHERESDEPTIN('CS','MA')ANDSNAMELIKE'张%';1.6SELECTSNO,CNOFROMSCWHEREGradeISNULL;2.1SELECTSTUDENT.*,SC.*FROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;2.2SELECTSTUDENT.SNO,SNAME,SC.CNO,GradeFROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO;2.3SELECTSTUDENT.SNO,SNAME,GradeFROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNOANDSC.CNO='1'ANDS