华南理工大学计算机学院数据库试验一报告

1、华南理工大学数据库课程实验报告实验题目：实验一：SQL的使用姓名：学号：班级：计科一班组别：无合作者：指导教师：董守玲实验概述【实验目的及要求】实验目的：通过交互式SQL的使用，掌握数据库的创建、插入、更新、查询等基本操作实验要求：1创建Student数据库(oracle可以不创建新的数据库，直接创建下列表就可以了)包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下划线表示

2、主键，斜体表示外键)，并插入一定数据。2完成如下的查询要求及更新的要求。(1)查询身高大于1.80m的男生的学号和姓名；(2)查询计算机系秋季所开课程的课程号和学分数；(3)查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；(4)查询至少选修一门电机系课程的女生的姓名(假设电机系课程的课程号以EE开头)；(5)查询每位学生已选修课程的门数和总平均成绩；(6)查询每门课程选课的学生人数，最高成绩，最低成绩和平均成绩；(7)查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；(8)查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；(9)查询有一门以上(含一门)三个学

3、分以上课程的成绩低于70分的学生的姓名；(10)查询1984年1986年出生的学生的姓名，总平均成绩及已修学分数。(11)在STUDENT和SC关系中，删去SNO以01'开关的所有记录。(12)在STUDENT关系中增加以下记录：<0409101何平女1987-03-021.62><0408130向阳男1986-12-111.75>(13)将课程CS-221的学分数增为3,讲课时数增为603.补充题：(1)统计各系的男生和女生的人数。(2)列出学习过编译原理，数据库或体系结构课程，且这些课程的成绩之一在90分以上的学生的名字。(3)列出未修选电子技术课程，但选修

4、了数字电路或数字逻辑课程的学生数。(4)按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。(5)列出平均成绩最高的学生名字和成绩。(SELEC啕中不彳#使用TOPn子句)4.选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1)修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2)设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表

5、中。【实验环境】PC机,WINDOWS操作系统，Oracle或MicrosoftSQLServer数据库实验内容【实验过程】一、实验步骤：1 .连接数据库2 .准备数据3 .建立模式（数据库）4 .建立表5 .插入数据6 .开始查询二、实验数据：（如给定文件）三、实验主要过程：1创建Student数据库（oracle可以不创建新的数据库，直接创建下列表就可以了）包括Students,Courses,SC表，表结构如下：Students（SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT）Courses（CNO,CNAME,LHOUR,CREDIT,SEMESTER）SC

6、SNO.CNQGRADE）（注：下划线表示主键，斜体表示外键），并插入一定数据。mysql>uselab;Databasechangedl®ysql>CREATETABLEStudent5(- >SNOVARCHAR(15)PRIHARYKE%- >SNAHEVARCHAk(5)p- >SEXVARCHAR(3,- >BDATEVARCHAR(26)r- >HEIGHTNUMERIC(3,2).- >DEPARTMENTVARCHAR(19)兀QueryOKrerousatfected(£).64sec)my5ql>CR

7、EATETABLECourses(- >CNOVARCHARJ10)PRIMARYKEY,- >CNAHEVARCHAR(ie),- >LHOURVARCHAR(3),- >CREDITNUMERIC(2,1),- >SEMESTERVARCHAR(2)r：QueryOK,6rowsaifccted(£).46sec)mysql>CREATETABLESC(- SNOVARCHAR(15)PRIHARY任匕- >CNOVARCHAR(10)r- >GRADESWALLINT,- >FOREIGNKEY(5NOREFERENCESS

8、tUdentstSNOp- >FOREIGNKEY(SNOREFERENCESCourses(CNOQueryUK.6rowsaffected(9,7Qsec)CREATETABLEStudents(SNOVARCHAR(15)PRIMARYKEY,SNAMEVARCHAR(5),SEXVARCHAR(3),BDATEVARCHAR(20),HEIGHTNUMERIC(3,2),DEPARTMENTVARCHAR(10)；CREATETABLECourses(CNOVARCHAR(10)PRIMARYKEY,CNAMEVARCHAR(10),LHOURVARCHAR(3),CREDITN

9、UMERIC(2,1),SEMESTERVARCHAR(2);CREATETABLESC(SNOVARCHAR(15),CNOVARCHAR(10),GRADESMALLINT,FOREIGNKEY(SNO)REFERENCESStudents(SNO),FOREIGNKEY(CNO)REFERENCESCourses(CNO);2完成如下的查询要求及更新的要求。(1)查询身高大于1.80m的男生的学号和姓名;nysqlaSELECTSNO,SHAME->FRDHstudents->WHEREHEIGHT1.的ANDSEXLIKE5N0期性环耶京就打冷?球.渔宛S北y春堪i上忖堆张

10、军强生程那春玲村姑文青用写WE2015303717632015305412132015305413402015305414S12015305410382O1530541B4520153054222420153054244620153054245320153054257620153696121820159245958412rawsinsetf乩eesec)SELECTSNO,SNAMEFROMStudentsWHEREHEIGHT>1.80ANDSEXLIKE,;(2)查询计算机系秋季所开课程的课程号和学分数;SELECTCNO,CREDITFROMCoursesWHERECNOLIKE&

11、#39;CS%'ANDSEMESTER麻E'|CNO|CREDIT|事噂_4|CS-221|2.0|+-+T1rowinset(6.06sec)(3)查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩;SELECTSNAME,SC.CNO,CREDIT,GRADEFROMStudents,Courses,SCWHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNOANDSC.CNOLIKE'CS%'ANDSEMESTERLKE'Emptyset(6.96sec)(4)查询至少选修一门电机系课程的女生的姓名(假

12、设电机系课程的课程号以EE开头)；SELECTDISTINCTSNAMEFROMStudents,SCWHEREStudents.SNO=SC.SNOANDCNOLIKE'EE%'ANDSEXLIKE'ISNAME平智之墟建青杰)M相湛省盘成浮点咫俗戢用蝠仁第松哲买地襦芯作正山除古何何喉引唐工英件物再造赵手剑(5)查询每位学生已选修课程的门数和总平均成绩;SELECTSNO,COUNT(*),AVG(GRADE)FROMSCGROUPBYSNO;20153G54262G|201530661679|201535542519|201536061248201536541429

13、|Z0153C542334|201592450584|201592456586|201592458587|2015924505883|75,33333I79.6667|378E67375.66673I68.3333|3I56.&057|3|8S.&667|3I74,GOO0|383,66&7370.333375rowsinset(6.60sec)(6)查询每门课程选课的学生人数，最高成绩，最低成绩和平均成绩;SELECTCNO,COUNT(*),MAX(GRADE),MIN(GRADE),AVG(GRADE)FROMSCGROUPBYCNO;4CNO|1T|C0UNT&

14、lt;*|卜1一|MAX(GRADE>|IlJL4|MIN(GRADE>|L_i卜|AVG(GRADE)ci|C2IC3|C4|C5EEIEE2EE3|EE41191123|121|122|2。331"27|133|»4ri|LQ0|52|96i50)197|51|100|51160|5195|56L00j50)|109j50|100|56卜41r77.157973.347875.695276.954571.759973.121272.814873.0667|71,8788k9rowsinset(9.05sec)(7)查询所有课程的成绩都在80分以上的学生的姓名

15、、学号、且按学号升序排列;SELECTDISTINCTSNAME,SC.SNOFROMStudents,SCWHEREStudents.SNO=SC.SNOANDGRADE>80ORDERBYSC.SNOASC;杰WA德虹笛华鸽之畔提杰蒂杰圭总贴之咸锦松伸低廿呢心夹盘常朝寄走厅能毗蚌矗飞姑咻“亚X昊昊班许物标机港甘钱钵锦派赵郑嘉玛也村黄牛制2015305421492Q1533542156|201530542107|201530542231|20153Q5422622615305422S6201530542293201536542347201530542378201530542415201

16、53B5424462015305424532S153Q5424B42H15?日5475"2615305426132815305426262015306610792815355425192015360G124S201524565&42015924505662A15924565&757rowsinset(0.ORsec(8)查询缺成绩的学生的姓名，缺成绩的课程号及其学分数;SELECTSNAME,SC.CNO,CREDITFROMSCLEFTOUTERJOINStudentsONSC.SNO=Students.SNOLEFTOUTERJOINCoursesONSC.CNO

17、=Courses.CNOWHEREGRADEISNULL;(9)查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名;SELECTDISTINCTSNAMEFROMStudents,Courses,SCWHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNOANDCREDIT>3ANDGRADE<70;7何出财升罗美售*曾郢工梁陈£郛费洪郑件阿健文懦现朝程旗R伟照舍越*整烹特38rowsinset(B,06sec(10)查询1984年1986年出生的学生的姓名，总平均成绩及已修学分数SELECTSNAME,AVG(GRAD

18、E),SUM(CREDIT)FROMStudents,Courses,SCWHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNOAND(BDATEBETWEEN1984AND1986)GROUPBYSNAME;理鸿髭平融达峰玮A曲可智嘉康淼浩宇菰博客鼻*港哲M明将告晓林可庄M海洞创南tt凯琳罗百朝r!L舒超邛邱郑郑郑陈陈陈映隙陈新麦黄黄61.3333|10.9|(79,66678.984,66079.072,33338.0|77.606G3.9|74,3333|19.3|75,333319.9|j80.3333|10.0|66,66671】3|76.6667

19、1H,9|75,3333|111与1|69,33339.3|S2.60GQ19.0|j65.606611,&|77.6667|11.9|74.6667|13.9|80.6066|9.053-66679.0|69,3333|8.0|j77,3333|1&。155.66e7|9.3|44rowsinsetf225waruing5(电.。5sec)(11)在STUDENTS口SC关系中，删去SNO以01开关的所有记录。DELETEFROMStudentsWHERESNOLIKE'01%'DELETEFROMSCWHERESNOLIKE'01%'luys

20、qlADELETE- >FROMStudents- >WHERESNOLIKE'01*;QueryOK,9reusl中2ct2d(6.06iec)mysql>DELETE- >FROMSC- >WHERESNOLIKE"61'QueryOK,rowsaflected(G+00sec)(12)在STUDENT关系中增加以下记录：<0409101何平女1987-03-021.62><0408130向阳男1986-12-111.75>INSERTINTOStudents(SNO,SNAME,SEX,BDATE,HEIGH

21、T)VALUES(0409101,何平'，'女',1987-03-02,1.62);INSERTINTOStudents(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES(0408130,向阳'，'男',1986-12-11,1.75);4-+H+HH+|SNO|SNANE|SEX|BDATE|HEIGHT|DEPARTMENT|L75NULL1.62NULL468139而阻界1963409161|何平女1982(13)将课程CS-221的学分数增为3,讲课时数增为60UPDATECoursesSETCREDIT=3WHEREC

22、NOLIKE'CS-221'UPDATECoursesSETLHOUR='60'WHERECNOLIKE'CS-221'4-|CNO|CNAME|LHOUR|CREDIT|SEMESTER|+-f+T|CS-221|数提尼提|60|3,0|tt|+1-k+a+1rowinsetsec)3 .补充题:(1)统计各系的男生和女生的人数。SELECTDEPARTMENT,SUM(CASEWHENSE湘'THEN1ELSE0END)生人数,SUM(CASEWHENSEaTHEN1ELSE0END)生人数FROMStudentsGROUPBYDEP

23、ARTMENT;+一+-+|DEPARTMENT|男上人敢|女生人教|+-'K-十|NULL|1|1|电机系|2317|计葬礼系|2510(2)列出学习过编译原理，数据库或体系结构课程，且这些课程的成绩之一在90分以上的学生的名字。SELECTDISTINCTSNAMEFROMStudents,SC,CoursesWHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNOANDGRADE>90AND（CNAMELIK褊译原理'ORCNAMELIKEB据库'ORCNAMELIKE#系结构,）;SWAME虹研唐畅全尊语晓心做世A琳晚7杞

24、-f刘除黄#部柏(3)列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。SELECTCOUNT(*)FROMCoursesWHERE(CNAMELIK散字电路'ORCNAMELIK嗽字逻辑')ANDNOTEXISTS(SELECT*FROMSCWHERESC.CNO=Courses.CNOANDCNAMBW技术');|COUNTS)|1rowinset(6.05sec(4)按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表不。SELECTDISTINCTSC.CNO,CNAME,SNO,GRADEFROMCourses

25、LEFTOUTERJOINSCONCourses.CNO=SC.CNOGROUPBYSC.CNO,CNAME,SNO,GRADEORDERBYSC.CNO,CNAME,SNO,GRADE;EE4|EE4|EE4|EE4IEE4IIEE4IIEE4I|EE4|EE4EE4IEE4IIEE4IIEE4u曼字4it.mt是£字叁辑违字舐辑迎字教Jfti,£字$弹说字蔻Mlft字装21315髓542149|20153G542187|201536542224|201530542231|2fll5J6542293|201536542347|20153G542378|2015365424

26、46I28153电542453|20153G542576|261535542519|201592459530201592450588696752S7|82I88|59|1905477|55j8Z&3226rowse5n.1.00(5)列出平均成绩最高的学生名字和成绩。(SELEC啕中不彳#使用TOPn子句)SELECTSNAME,AVG_GRADEFROMStudents,(SELECTSNO,AVG(GRADE)AVG_GRADEFROMSCGROUPBYSNO)ASAVG_SCWHEREStudents.SNO=AVGSC.SNOANDAVGGRADE=(SELECTMAX(AVG

27、GRADE2)FROM(SELECTSNO,AVG(GRADE)AVG_GRADE2FROMSCGROUPBYSNO)ASAVG_SC2);|SNAHE|AVGGRADE|+一一T+|修映玄|95.3333|4k-41row工nst(5.95xc)4 .选做题：对每门课增加先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：ALTERTABLECoursesADDPRE1VARCHAR(10),ADDPRE2VARCHAR(10);mysql>ALTERTABLECourses->ADDPRE1VARCHAK(IO),ADDPRE?VARCHA

28、R(10);QueryOK,6rowsaffected(1.93匚Records；0Duplicates；0warnings；e1)修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。ALTERTABLECoursesADDCONSTRAINTTHE_KEYFOREIGNKEY(PRE1)REFERENCESCourses(CNO);UPDATECoursesSETPRE1='C1'WHERECNAME嗷据库,;my&ql>ALTERTABLECourses。ADDC0N5TRAIMTTHEKEY->FOREIGNKEY(PRE1)->REFE

29、RENCES3rs电Query0Kr10rowsaffected(1.Ilsec)Records：1GDuplicates：。Warnings：02）设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。（该学生已经选修过数据库课程的所有先修课，并达到合格成绩。）首先，设置数据库课程的先修课。UPDATECoursesSETPRE1='C1'WHERECNAME嗷据库,;my£ql>UPDATECourses-aSETPRE1='C1'->WHERECN刖E='数据席;QueryOK,1rowaffected(

30、6.12secJRowsmatched:1Changedi1Morning二：0|CNO1CNAME|LHOUR上.i11*CREDIT|SEMESTER|PRE11b*4PRE21+*.干唱F-'1Cl1牧掘结构|6413.0|秋|NULL1NULL1IIC2救据庠|644.8春ClNULLC3人工智施482s*NULLNULL1C4编译原理|644.G|1秋1NULLNULL1C51体系毋构|4813,91教1NULL1NULL1结果:SELECTSNOFROMSC,CoursesWHERESC.CNO=Courses.CNOANDSC.CNO=(SELECTPRE1FROMCou

31、rsesWHERECNO='C2');|201539541654|201536542484|291536542286|201536541159|201536542334|20153054152420153054169219rowsinset(0.00sec)小结通过这次实验，我收获了很多。首先，是课本的理论知识通过实验得到了验证，加深了我对它们的理解。其次，对于数据库实践的操作，通过各个习题的练习，我较为熟练地掌握了它们的用法。因此，对日后使用数据库进行操作也增加了信心。最后，对于具体的题目，通过实际编程这种更为严谨的过程。我也总结了不少的技巧和方法。较为系统的知识在课程学习过程中大致已经掌握