201421314431-张和杰-数据库实验三报告

第1页共10页仲恺农业工程学院实验报告纸计算科学（院、系）信息与计算科学专业144班数据库原理与应用课学号201421314431姓名张和杰实验日期2016-3-21教师评定实验三实验项目：数据库多表查询与视图的创建和使用。实验目的：通过实验，要求学生掌握多表查询和使用视图。实验要求：成功使用多表查询和视图。实验内容：在SM数据库中进行以下操作：1.使用EXISTS查询所有选修了001号课程的学生的姓名。2.使用NOTEXISTS查询未选修了001号课程的学生的姓名。第2页共10页3.使用NOTEXISTS查询选修了课程学分为４的全部课程的学生的姓名。4.统计教职工的总人数、最高工资、最少工资、平均工资。第3页共10页5.查询选修两门课程以上的学生学号。6.查询选修了三门课程的学生学号。7.使用分组查询选修了所有课程的学生学号、姓名。8.在选修课001中，使用ALL查询比学号00001和学号00004的成绩都低的学生学号；第4页共10页9.在选修课001中，使用ANY查询比学号00001或学号00004的成绩低的学生学号；10.在选修课001中，查询比平均成绩低的学生的学号；11.按总平均值降序给出所有课程都及格但不包括课程001的所有学生的总平均成绩；第5页共10页12.更新表student中的年龄，年龄为当前年份减去出生年份（提示，使用year()）；13.更新表SC中的成绩，把选修了“数据库”的成绩加1；14.在表student中删除学号为00001的学生信息；15.在表course中删除“数据库”课程的信息；第6页共10页16.在表SC中删除男同学的信息。17.建立一个系号为003的学生表（学号，姓名），要求把student的所有学号和姓名插入该表。18.使用SQL语句写出课本P70页第6题的查询。(1)求供应工程J1零件的供应商号码SNO:SELECTDISTSNOFROMSPJWHEREJNO=’J1’第7页共10页(2)求供应工程J1零件P1的供应商号码SNO:SELECTDISTSNOFROMSPJWHEREJNO='J1'ANDPNO='P1'(3)求供应工程J1零件为红色的供应商号码SNO:SELECTSNOFROMSPJ,PWHEREJNO='J1'ANDSPJ.PNO=P.PNOANDCOLOR='红'(4)求没有使用天津供应商生产的红色零件的工程号JNO:SELECTDISTJNOFROMSPJWHEREJNONOTIN(SELEJNOFROMSPJ,P,SWHERES.CITY='天津'ANDCOLOR='红'ANDS.SNO=SPJ.SNOANDP.PNO=SPJ.PNO)。(5)求至少用了供应商S1所供应的全部零件的工程号JNO。将查询分为两步1、查询S1供应商供应的零件号SELECTDISTPNOFROMSPJWHERESNO='S1'结果是（P1，P2）2、查询哪一个工程既使用P1零件又使用P2零件。SELECTJNOFROMSPJWHEREPNO='P1'ANDJNOIN(SELECTJNOFROMSPJWHEREPNO='P2')源代码：selectsnamefromstudentwhereexists(select*fromscwheresc.SNo=student.snoandsc.CNo='001')selectsnamefromstudentwherenotexists(select*fromscwheresc.SNo=student.snoandsc.CNo='001')selectsnamefromstudentwherenotexists(select*fromcoursewhereCCredits='4'andnotexists(select*第8页共10页fromscwheresc.SNo=student.SNoandsc.CNo=course.CNo))selectcount(*)总人数,max(distinctsal)最高工资,min(distinctsal)最少工资,avg(sal)平均工资fromteacherselectsnofromscgroupbysnohavingcount(*)=2selectsnofromscgroupbysnohavingcount(*)=3selectsno,snamefromstudentwheresnoin(selectsnofromscgroupbysnohavingcount(*)=(selectcount(*)fromcourse))selectsnofromscwhereCNo='001'andScoreall(selectScorefromscwhere(sno='00001'orsno='00004')andCNo='001')selectsnofromscwhereCNo='001'andScoreany(selectScorefromscwhere(sno='00001'orsno='00004')andCNo='001')selectsnofromscwhereCNo='001'andScore(selectAVG(Score)fromsc)groupbySNoselectsno,AVG(score)fromsc第9页共10页whereCNo'001'groupbySNohavingMIN(score)=60orderbyAVG(score)descupdateStudentsetSAge=2016-YEAR(SBir)updatescsetScore=Score+1wherecno=(selectcnofromcoursewhereCName='数据库')EXECSP_HELPCONSTRAINTscaltertablescdropconstraintFK_sc_Studentaltertablescaddforeignkey(sno)referencesstudent(sno)ondeletecascadedeletefromStudentwherestudent.SNo='00001'EXECSP_HELPCONSTRAINTscaltertablescdropconstraintFK__sc__CNo__22AA2996altertablescaddforeignkey(CNo)referencescourse(CNo)ondeletecascadedeletefromcoursewhereCName='数据库'deletefromscwhereSNoin(selectSNofromStudentwhereSSex='男')createtable学生表(sdeptchar(15)default'003',学号char(5)notnull,姓名varchar(8)notnull第10页共10页)insertinto学生表(学号,姓名)selectsno,SNamefromStudent;实验结果：图见上面，由于后面一些更新删除操作使得表内容有很多变化且更新删除插入等操作不能直观看出来结果所以没截图，只截图代码部分，全都是自己做的实验体会：这次实验题目比较多且有些有难度，用noexists表示全称量词，还有分组那部分尚且还用不太熟练，其中最麻烦的还要算删除外键数据，这里我采用了复杂的操作这要先查看约束名，删除约束名再设置成级联外键就可以一一对应删除，还有过程中曾错误地想在=后面直接接avg（....）,之后才发现不能这样，比较的符号后面要加这些函数要用（selectavg（....））才可以，总的来说，这次实验收获比较大，不过还是要在多做一些题，加强对数据库增删查改的操作