数据库实验报告

数据库实验报告班级：计科161姓名：蒋东旗指导老师：杜献峰实验一基本表的定义、删除与修改定义基本表1建立学生表Stu，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。这里要求Sno和Sname不能为空值，且取值唯一。Sno为主码。createtablestu(snochar(20)primarykey,snamechar(20)unique,ssexchar(2),sagesmallint,sdeptchar(20));2建立课程表Cose，其属性名意义分别为Cno-课程号,Cname-课程名,Cpno-先修课程号,Credit-学分。Cno为主码。createtablecose(cnochar(4)primarykey,cnamechar(40)notnull,cpnochar(4),ccreditsmallint,foreignkey(cpno)referencescose(cno));3建立成绩表StuSC。其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。Sno和Cno为主码，Sno和Cno分别为外码。createtablestusc(snochar(9),cnochar(4),gradesmallint,primarykey(cno,sno),foreignkey(sno)referencesstu(sno),foreignkey(cno)referencescose(cno));修改基本表(Alter)1向基本表Stu中增加“入学时间”属性列，其属性名为RegisterDate，数据类型为DATE型。AltertableTuring.stuAdd(RegisterDatedate);2删除Student表的属性列RegisterDate。AltertablestuDropcolumnRegisterDatecascadeconstraints;说明：为了保证后面例子能够顺利运行，请大家一定将属性列RegisterDate从Stu表中删除。3将Sage(年龄)的数据类型改为SMALLINT型。AltertablestuModifysagesmallint;4将Stu表的属性列RegisterDate名修改为RegDate，其它不变。Altertableturing.stuRenamecolumnRegisterDatetoRegDate;5增加Sname(姓名)必须取唯一值的约束。altertableturing.stuaddconstraintcons_snameunique(sname);6删除Sname(姓名)必须取唯一值的约束。Altertableturing.stuDropconstraintcons_sname;7表中添加PRIMARYKEY约束Altertableturing.stuscAddconstraintPK_SCPRIMARYKEY(sno,cno);8StuSC表中添加FORENGNKEY约束altertableturing.stuscaddconstraintfk_scforeignkey(sno)referencesturing.stu(sno)foreignkey(cno)referencesturing.cose(cno);9定义SC表中grade默认值为0；altertableturing.Stuscmodify(gradedefault0)10定义SC表中grade最小值为0，最大值为100；altertableturing.stuscaddconstraintchk_grade1check((0=grade)and(grade=100));删除基本表1删除Stu表(注：能直接删除student表吗？为什么？)。不能，因为stusc表中的sno依赖stu表中的sno2删除cose表(注：能直接删除course表吗？为什么？)。不能，因为stusc表中的cno依赖cose表中的cno3删除StuSC表。实验二SQL语言应用A、单表查询无条件查询1查询全体学生的详细记录。select*fromstudent2查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。selectsname,sno,sdeptfromstudent;3查询全体学生的姓名(Sname)、出生年份及学号(Sno)。selectsname,sno,(2018-sage)asbirthdayfromstudent;4查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。selectsname,(2018-sage)asbirthday,lower(sno)fromstudent;5查询选修了课程的学生学号。selectdistinctsnofromsc;条件查询6查询数学系全体学生的学号(Sno)和姓名(Sname)。selectsno,snamefromstudentwheresdept='数理学院';7查询所有年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。selectsname,sagefromstudentwheresagebetween18and22;8查询年龄在18~22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。selectsname,sagefromstudentwheresagenotbetween18and22;9查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。selectsname,sagefromstudentwheresagenotbetween18and22;10查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。selectsno,sname,ssexfromstudentwheresdeptin('数理学院','自动化系','计科系');11查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。selectsno,sname,ssexfromstudentwheresdeptnotin('数理学院','自动化系','计科系');12查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。selectsname,sno,ssexfromstudentwheresnamelike'刘%';13查询姓“刘”且全名为4个汉字的学生的姓名(Sname)和所在系(Sdept)。selectsname,sdeptfromstudentwheresnamelike'刘____';14查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。selectsname,sagefromstudentwheresnamenotlike'刘%';15查询课程名为“DB_设计”的课程号(Cno)和学分(Credit)。selectcno,ccreditfromcoursewherecname='DB_设计';16查询以DB_开头，且倒数第2个汉字字符为“设”的课程的详细情况。selectcno,cpno,cname,ccreditfromcoursewherecnamelike'DB\_%设_';17假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。selectsno,cnofromscwheregradeisnull;18查询所有有成绩的学生学号(Sno)和课程号(Cno)。selectsno,cnofromscwheregradeisnotnull;查询结果排序19查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。selectsno,gradefromscwherecno='c03'andgradeisnotnullORDERbygradedesc;20查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。select*fromstudentorderbysdept,snodesc;集函数的使用21查询学生总人数。selectcount(sno)fromstudent;22查询选修了课程的学生人数。selectcount(distinctsno)fromsc;23计算选修C01号课程的学生平均成绩。selectavg(grade)fromscwherecno='c01';24查询选修C01号课程的学生最高分数。selectMAX(grade)fromscwherecno='c01';查询结果分组25求各个课程号(Cno)及相应的选课人数。selectcno,count(sno)countfromscgroupbycno;26查询选修了3门或3门以上课程的学生学号(Sno)selectsnofromstudentwheresnoin(selectsc.snofromscgroupbysc.snohavingcount(*)3);B链接查询不同表之间的连接查询27查询每个学生及其选修课程的情况。selectstudent.*,sc.*fromstudent,scwherestudent.sno=sc.sno;28查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。selectstudent.sno,sname,cname,gradefromstudent,sc,coursewherestudent.sno=sc.snoandsc.cno=course.cno;自身连接29查`询每一门课的间接先修课(即先修课的先修课)。selectA.cname,B.cnamefromcourseA,courseBwhereA.cpno=B.cno;外连接30把例3.37中的等值连接改为左连接。C嵌套查询带谓词IN的嵌套查询31查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。selectsno,snamefromstudentwheresnoin(selectsnofromscwherecno='c02');32查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。selectsno,sname,sdeptfromstudentwheresdeptin(selectsdeptfromstudentwheresname='李伟');33查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。selectsno,snamefromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname='数据结构'));带谓词ANY或ALL的嵌套查询34查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。selectsname,sagefromstudentwheresage=all(selectsagefromstudentwheresdept='自动化')andsdept!='自动化';带谓词EXISTS的嵌套查询35查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。selectsname,sdeptfromstudentwhereexists(selectsnofromscwherestudent.sno=sc.snoandcno='c01');36查询选修了所有课程的学生姓名(Sname)和所在系。selectsname,sdeptfromstudentwhereexists(select*fromscwherestudent.sno=sc.sno);D集合查询37查询计算机科学系的学生