数据库上机实验报告

1、数据库实验报告（所有实验院系：信息科学与工程学院专业：网络工程电子商务班级：姓名：学号：指导老师：年月日一、实验目的熟练掌握SQL语句的插入、修改、删除、查询等相关语法。能够使用MicrosoftSQLServer和MySQL软件进行相关的数据库操作。二、实验内容1数据库的创建和使用；2表的创建和使用；3数据的插入、删除和修改；4数据的查询；5.视图的创建和使用。实验一、创建学生成绩管理数据库在学生成绩管理数据库中，包括基本的三个关系：studentcoursesc。(1) student(sno,sname，ssexsage，sdept),表示学号，姓名，性别，年龄,所在系。主键学号，姓名唯

2、一，系默认cs_(2) Course(cn,ocname，cpno，ccredit)表示课程号，课程名，先行课程号，学分。主键课程号(3) SC(snoeno,grade)表示学号，课程号，成绩。外键，学号和课程号。创建该数据库代码及其最后结果图如下：createtablestudent(snochar(20)primarykey,snamechar(10)unique,ssexchar(2),sagesmallint,sdeptchar(12)defaultcs)胆表SdACnoQnd9500119295MI2時弟MM3BB95002295W23socreatetablecourse(cno

3、char(10)primarykey,cnamechar(20),cpnochar(10),ccreditchar(6)FIRST龍代叫辔携）OaCtHittCprtoCrni154223佰凤第廃L4463S抵错蜡沟74617PASCAL语書6斗createtablesc(snochar(20),cnochar(10),gradesmallint,primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno)Student衰SsoSnawSdqt95001李翩男20OS

4、95W2刘震-女19WW3z女1B夕5W4Ki,19实验二、在实验一的结果上进行各种操作练习及其代码一修改表结构，对学生表增加，电话号码一列，删除年龄一列。altertablestudentaddpnumberchar(22)altertablestudentdropcolumnsage二简单查询例1.查询全体学生详细记录select*fromstudent例2.查询信息系所有男生的学号、姓名、出生年份selectsno,sname,sagefromstudentwheressex=男例3.查询选修过课的学生的学号selectsnofromsc例4.查询年龄在25-30之间的学生姓名及性别。s

5、electsname,ssexfromstudentwheresagebetween25and30例5.查询姓“欧阳”的学生。selectsnamefromstudentwheresnamelik欧阳例6.查询信息系IS数学系MA和计算机系CS的学生。selectsnamefromstudentwheresdept=isorsdept=maorsdept=cs例7查询所有学生的成绩及姓名selectgrade,snamefromstudent,scwheresc.sno=student.sno例8查询95001学生的所选的课程，成绩和专业selectcname,grade,sdeptfroms

6、tudent,sc,coursewherestudent.sno=o=oandstudent.sno=95001实验三、四关系模式如下：EMPLOYEEFNAMEMINITLNAMESSNBDATEADDRESSSEXSALARYSUPERSSNDNODEPARTMENTDNAMEDNUMBERMGRSSNMGRSTARTDATEDEPT_LOCATIONSDNUMBERDLOCATIONPROJECTPNAMEPNUMBERPLOCATIONDNUMWORKS_ONESSNPNOHOURSDEPENDENTESSNDEPENDENTNAMESEXBDATERELATIONSHIP数据内容如下

7、：EMPLOYEEFNAMEMINITLNAMESSNBDATEADDRESSSEXSALARYSUPERSSNDNOJohnBSmith1234567891965-01-09731Fondren,Houston,TXM300003334455555FranklinTWong3334455551955-12-08638Voss,Houston,TXM400008886655555AliciaJZelaya9998877771968-07-193321Castle,Spring,TXF250009876543214JenniferSWallace9876543211941-06-20291Ber

8、ry,Bellaire,TXF430008886655554RameshKNarayan6668844441962-09-15975FireOak,Humble,TXM380003334455555JoyceAEnglish4534534531972-07-315631Rice,Houston,TXF250003334455555AhmadVJabbar9879879871969-03-29980Dallas,Houston,TXM250009876543214JamesEBorg8886655551937-11-10450Stone,Houston,TXM55000null1DEPTLOCA

9、TIONSDEPARTMENTDNAMENUMBERMGRSSNMGRSTARTDATEResearch53334455551988-05-22Administration49876543211995-01-01Headquarters18886655551981-0&19DNUMBERLOCATION1Houston4Stafford5Bellaire5Sugariand5HoustonWORKSONESSNPNOHOURS123456789132512345678927.5666884444340.0453453453120.0453453453220.0333445555210.0333

10、445555310.03334455551010.03334455552010.09998877773030.09998877771010.09879879871035.0987987987305.09876543213020,09876543212015.088866555520nullPROJECTPNAMEPNUMBERPLOCATIONNUMProductX1Bellaire5ProductY2Suqarland5ProductZ3Houston5Computerization10Stafford4Reorganization20Houston1Newbenefits30Staffor

11、d4DEPENDENTESSNDEPENDENTNAMESEXBDATERELATIONSHIP333445555AliceF1986-04-05DAUGHTER333445555TheodoreM1983-10-25SON333445555JoyF1958-05-03SPOUSE987654321AbnerM1942-02-28SPOUSE123456789MichaelM1988-01-04SON123456789AliceF1988-12-30DAUGHTER123456789ElizabethF1967-05-05SPOUSE创建数据库并使用数据库createdatabaseEnter

12、priseuseEnterprise;创建表EMPLOYEE并插入数据createtableEMPLOYEE(FNAMEchar(20),MINITchar(1),LNAMEchar(20),SSNchar(9)primarykeyBDATEdatetimeADDRESSchar(50),SEXchar1)check(SEX=MorSEX=F),SALARYintSUPERSSNchar(9),DNOsmallintforeignkey(SUPERSSN)referencesEMPLOYEE(SSN);insertintoEMPLOYEEvalues(James,E,Bong，88866555

13、5,1937-11-10,450Stone,Houston,TX,M,55000,null,1);insertintoEMPLOYEEvalues(Franklin,T,Wong,333445555,1955-12-08,638Voss,Houston,Tx,M,40000,888665555,5);insertintoEMPLOYEEvalues(Jennifer,S,Wallace,987654321,1941-06-20,291Berry,Bellaire,TX,F,43000,888665555,4);insertintoEMPLOYEEvalues(Ramesh,K,Narayan,

14、666884444,1962-09-15,975FireOak,Humble,TX,M,38000,333445555,5);insertintoEMPLOYEEvalues(Joyce,A,English,453453453,1972-07-31,5631Rice,Houston,TX,F,25000,333445555,5);insertintoEMPLOYEEvalues(John,B,Smith,123456789,1965-01-09,731Fondren,Houston,TX,M,30000,333445555,5);insertintoEMPLOYEE,1968-07-19,33

15、21,4);,1969-03-29,980,4);values(Alicia,J,Zelaya,999887777Castle,Spring,TX,F,25000,987654321insertintoEMPLOYEEvalues(Ahmad,V,Jabbar,987987987Dallas,Houston,TX,M,25000,987654321创建表DEPARTMENT并插入数据createtableDEPARTMENT(DNAMEchar(20),DNUMBERsmallintprimarykeyMGRSSNchar(9),MGRSTARTDATEdatetime);insertinto

16、DEPARTMENTvalues(Research,5,333445555,1988-05-22)insertintoDEPARTMENTvalues(Administration,4,987654321,1995-01-01);insertintoDEPARTMENTvalues(Headquarters,1,888665555,1981-06-19);创建表project并插入数据createtablePROJECT(PNAMEchar(20),PNUMBERsmallintprimarykeyPLOCATIONchar(20),DNUMsmallintforeignkey(DNUM)re

17、ferencesDEPARTMENT(DNUMBER);insertintoPROJECTvalues(ProductX,1,Bellaire,5);insertintoPROJECTvalues(ProductY,2,Sugarland,5);insertintoPROJECTvalues(ProductZ,3,Houston,5);insertintoPROJECTvalues(Computerization,10,Stafford,4insertintoPROJECTvalues(Reorganization,20,Houston,1);insertintoPROJECTvalues(N

18、ewbenefits,30,Stafford,4);创建表WORKS_ON并插入数据createtableWORKS_ONESSNchar(9),PNOsmallintHOURSfloat(1),primarykey(ESSN,PNO),foreignkey(ESSN)referencesEMPLOYEE(SSN),foreignkey(PNO)referencesPROJECT(PNUMBER);insertintoWORKS_ONvalues(123456789,1,32.5);insertintoWORKS_ONvalues(123456789,2,7.5);insertintoWORK

19、S_ONvalues(666884444,3,40.0);insertintoWORKS_ONvalues(453453453,1,20.0);insertintoWORKS_ONvalues(453453453,2,20.0);insertintoWORKS_ONvalues(333445555,2,10.0);insertintoWORKS_ONvalues(333445555,3,10.0);insertintoWORKS_ONvalues(333445555,10,10.0)insertintoWORKS_ONvalues(333445555,20,10.0)insertintoWOR

20、KS_ONvalues(999887777,30,30.0)insertintoWORKS_ONvalues(999887777,10,10.0)insertintoWORKS_ONvalues(987987987,10,35.0)insertintoWORKS_ONvalues(987987987,30,5.0);insertintoWORKS_ONvalues(987654321,30,20.0)insertintoWORKS_ONvalues(987654321,20,15.0)insertintoWORKSONvalues(888665555,20,null);创建表DEPT_LOAC

21、TION并插入数据createtableDEPT_LOCATIONDNUMBERsmallintDLOCATIONchar(20),primarykey(DNUMBER,DLOCATION),foreignkey(DNUMBER)referencesDEPARTMENT(DNUMBER);insertintoDEPT_.LOCATIONvalues(1Houston);insertintoDEPT_.LOCATIONvalues(4Stafford);insertintoDEPT_.LOCATIONvalues(5Bellaire);insertintoDEPT_.LOCATIONvalues

22、(5Sugarland)insertintoDEPT.LOCATIONvalues(5Houston);创建表DEPENDENT并插入数据createtableDEPENDENT(ESSNchar(9),DEPENDENT_NAMESEXchar(1),char(20),BDATEdatetimeRELATIONSHIPchar(10),primarykey(ESSN,DEPENDENT_NAME),foreignkey(ESSN)referencesEMPLOYEE(SSN);insertintoDEPENDENTvalues(333445555,Alice，F,1986-04-05,DAU

23、GHTER);insertintoDEPENDENTvalues(333445555,Theodore,M,1983-10-25,SON);insertintoDEPENDENTvalues(333445555,Joy,F,1958-05-03,SPOUSE);insertintoDEPENDENTvalues(987654321,Abner,M,1942-02-28,SPOUSE);insertintoDEPENDENTvalues(123456789,Michael,M,1988-01-04,SON);insertintoDEPENDENTvalues(123456789,Alice,F,

24、1988-12-30,DAUGHTER);DEPENDENTinsertintovalues(123456789,Elizabeth,F,1967-05-05,SPOUSE);查询操作RetrievethebirthdateandaddressoftheemployeewhosenameisJohnB.SmithselectBDATE,ADDRESSfromEMPLOYEEwhereFNAME=JohnandMINIT=BandLNAME=Smith;tment.RetrievethenameandaddressofallemployeeswhoworkfortheResearchdepais

25、electFNAME,ADDRESSfromEMPLOYEE,DEPARTMENTwhereDEPARTMENT.MGRSSN=EMPLOYEE.SSNandDEPARTMENT.DNAME=Research;ForeveryprojectlocatedinStaffordlisttheprojectnumber,thecontrolingepartmentnumber,andthedepartmentmanagerslastname,address,andbirthdate.selectPNUMBER,DNAME,LNAME,BDATE,ADDRESSfromPROJECT,DEPARTME

26、NT,DEPT_LOCATION,EMPLOYEEwhereDEPT_LOCATIONDLOCATION=StaffordandDEPT_LOCATIONDNUMBER=PROJECTDNUMandDEPT_LOCATIONDNUMBER=DEPARTMENTDNUMBERandDEPARTMENT.MGRSSN=EMPLOYEE.SSN;Foreachemployee,retrievtheemployeesname,andthenameofhisorherimmediatesupervisor.selectEP.FNAME,EP.MINIT,EP.LNAME,ES.FNAME,ES.MINI

27、T,ES.LNAMEfromEMPLOYEEEP,EMPLOYEEESwhereEP.SUPERSSN=ES.SSN;RetrievetheSSNvaluesforallemployees.selectSSNfromEMPLOYEERetrievethenamesofemployeeswhohavenodependents.selectSSNfromEMPLOYEEwherenotexists(selectESSNfromDEPENDENTwhereEMPLOYEESSN=ESSN);Retrievethenameofeachemployeewhoworksonalltheprojectcon

28、trollebydepartmentnumber5.selectdistinctFNAME,LNAMEfromEMPLOYEE,PROJECT,DEPARTMENT,WORKS_ONwhereDEPARTMENTDNUMBER=5andPROJECT.DNUM=DEPARTMENT.DNUMBERandPROJECTPNUMBER=WORKS_ONPNOandEMPLOYEESSN=WORKSONESSNMakealist)fallprojectnumbersforprojectthatinvolveanemployeewhoselastnameisSmithasaworkerorasaman

29、agerofthedepartmentthatcontrolstheproject.selectdistinctWORKSONPNOfromEMPLOYEE,WORKS_ON,PROJECT,DEPARTMENTwhereEMPLOYEELNAME=SmithandEMPLOYEESSN=WORKS_ONESSNorPROJECT.DNUM=DEPARTMENT.DNUMBERandDEPARTMENT.MGRSSN=EMPLOYEE.SSN);Retrievethesocialsecuritynumbersofallemployeeswhoworkonprojectnumber1,2,or3

30、.selectdistinctESSNfromWORKS_ON,PROJECTwhereWORKS_ONPNO=PROJECTPNUMBERand(PROJECTPNUMBER=1orPROJECTPNUMBER=2orPROJECTPNUMBER=3)RetrievethenamesofallemployeeswhodonothavesupervisorsselectFNAMEfromEMPLOYEEwhereSUPERSSNisnullFindthemaximumsalary,theminimumsalary,andtheaveragesalaryamongallen.ployees.se

31、lectmax(SALARY)maximumsalary,minSALARY)minimumsalary,avgSALARY)averagesalaryfromEMPLOYEEFindthemaximumsalarytheminimumsalaryandtheaveragesalaryimongemployeeswhoworkfortheResearchdepartment.selectmax(SALARY)maximumsalarymin(SALARY)minimumsalaryavg(SALARY)averagesalaryfromEMPLOYEE,DEPARTMENTwhereEMPLO

32、YEE.DNO=DEPARTMENT.DNUMBERandDEPARTMENT.DNAME=Research;Retrievethetotalnumberofemployeesinthecompanyselectcount(FNAME)totalnumberfromEMPLOYEERetrievethenumberofemployeesintheResearchdepartmentselectcount(FNAME)totalnumberfromEMPLOYEE,DEPARTMENTwhereEMPLOYEE.DNO=DEPARTMENT.DNUMBERandDEPARTMENT.DNAME=

33、Research;Foreachdepartment,retrievethedepartmentnumber,thenumberofemployeesinthedepartment,andtheiraveragesalary.selectDEPARTMENT.DNUMBER,count(EMPLOYEE.DNO)count,avg(EMPLOYEE.SALARY)averagesalaryfromDEPARTMENT,EMPLOYEEwhereDEPARTMENT.DNUMBER=EMPLOYEE.DNOgroupbyDEPARTMENT.DNUMBERForeachproject,retri

34、evetheprojectnumber,projectname,andthenumberofemployeeswhoworkonthatproject.selectPROJECT.PNAME,count(PROJECT.PNUMBER)countfromPROJECT,WORKS_ONwherePROJECTPNUMBER=WORKS_ONPNOgroupbyPROJECT.PNAMEForeachprojectonwhichmorethantwoemployeeswork,retrievetheprojectnqmber,projectname,andthenumberofemployees

35、whoworkonthatproject.selectPROJECT.PNAME,fromPROJECT,WORKS_ONwherePROJECT.PNUMBERgroupbyPROJECT.PNAMEcount(PROJECT.PNUMBER)countWORKSONPNORetrieveallemployeeswhoseaddressisinHouston,Texas.Here,thevalueoftheADDRESSattributemustcontainthesubstringHouston,TX.selectFNAME,MINIT,LNAME,ADDRESSfromEMPLOYEEw

36、hereEMPLOYEE.ADDRESSlike%Houston,TXRetrieveallemployeeswhowerebornduringthe1950s.Here,5mustbethe9thcharacterofthestring(according;oourformatfordate),sotheBDATEvalueis5_witheachunderscoreasaplaceholderforasinglearbitrarycharacter.selectFNAME,MINIT,LNAME,BDATEfromEMPLOYEEwhereconvert(varchar,BDATE,110

37、)like5_ShowtheeffectofgivingallemployeeswhoworkontheProductXprojecta10%raise.Retrievealist)femployeesandtheprojectseachworksin,orderedbytheemployeesdepartment,andwithineachdepartmentorderedalphabeticallybyemployeelastname.实验五、附加MSSQL2000的示例数据库pubs数据库1使用演示脚本创建表、插入数据，查找以X%开头的数据。-建表createtabletest(colv

38、archar(10)-插入数据insertintotestvalues(X_yz)insertintotestvalues(Xyz)insertintotestvalues(X%yz)insertintotestvalues(Xyz)练习及其代码如下：-练习1-找出pubs数据库titl(表中计算机类图书中价格最高的图书的价格。USEpubsSELECTmaX(price)FROMtitleswheretype=popular_compH结果占消息max_price1|27.54-练习2-查询titles中有几类图书。USEpubsSELECTcount(distincttype)FROMti

39、tles二结果消息count1|6-练习3-按照州进行分类，查找每个州有几名作者USEpubsSELECTstate,count(*)FROMauthorsgroupbystateorderby1-练习4-要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和(ytd_sale)s。USEpubsSELECTpub_id,sum(ytd_sales)FROMtitlesgroupbypub_idorderby1二结果消息pub_idsum107362S2&62DS77442193133324&41-练习5-在pubs数据库的title表中，找出平均价格大于18美元的书的种类。US

40、EpubsSELECTpub_id,avg(pri平均价格FROMtitlesGROUPBYpub_idHAVINGavg(price)18-练习6-在pubs数据库的title表中，找出最高价大于20美元的书的种类。USEpubsSELECTtype,max(pric平均价格FROMtitlesGROUPBYtypeHAVINGmax(price)20二结果匕消息type最高怕1business23.9SB2mad_cook23.9SB3popular_comp27.544psychology25.90B5trad_cook25.14-练习7-找出title_和pub_name的对应关系。U

41、sepubsSelecttitles.title_id,publishers.pub_nameFromtitlesJOINpublishersONtitles.pub_id=publishers.pub_id二结果消息titledpub_name1BU1D32AlgodataInfosystems2BU1111AlgodataInfosystems3BU2075NewMoonBooks4BU7B32AlgodataInfosystems5MC2222BinnetAHandley6MC3021Binnet&Handley7MC3026Binnet&HandleyBPC1035AlgodataIn

42、fosystems9PCSS8BAlgodataInfosystems1DPC9999AlgodataInfosystems11PS1372Binnet&Handley-练习8-找出title_id,t和tpub_name的对应关系。UsepubsSelecttitles.title_id,titles.title,publishers.pub_nameFromtitlesJOINpublishersONtitles.pub_id=publishers.pub_id二结果消息titledtitlepub_name1BU1032TheBusyBoecutivesDatabaseGuideAlgo

43、dataInfosystems2BU1111CookingwithComputers:SurreptitiousBalanceSheetsAlgodataInfosystems3BU2075YouCanCambatComputerStress!NewMoonBooks4BU7B32StraightTalkAboutComputerAlgodataInfosystems5MC.777?SiliconValleyGastnonomicTreatsBinnet&HandleyGMC3O21TheGoumnetMicrowaveBinnet&Handley7MC3026ThePsychologyofC

44、omputerCookingBinnet&HandleySPC1D35ButIsItUserFriendly?AlgodataInfosystema9pcsmSecretsofSiliconValleyAlgodataInfosystems10PC9999NetEtiquetteAlgodataInfosystems11PS1372ComputerPhobicANDNon-PhobicIndividuals:Beha.Binnet&Handley12PS2OS1IsAngertheEnemy?NewMoonBooks13PSZ1OGLifeWithoutFearNewMoonBooks14PS

45、3333ProlongedDataDeprivation:FourCaseStudiesNewMoonBook吕15PS7777EmotionalSecurity:ANewAlgorithmNewMoonBooks16TC321BOnions,Leeks,andGarlic:CaokingSecretsoftheM.Binnet&Handley练习9-查询每个作者的编号，姓名，所出的书的编号，并对结果排序。Usepubs_Selectauthors.au_id,authors.au_fname+.+authors.au_lnamename,titleauthor.title_idFromaut

46、horsJOINtitleauthorONauthors.au_id=titleauthor.au_idorderbyauthors.au_id二结果消息sujdnametitled1172-32-1176Johnson.WhitePS3333221345315Marjorie.GreenBU1032321345315Marjorie.GreenBU20754Gheryt.CaraonPC1Q35526741-2334Michael.0LearyBU1111626741-2334Michael.0LearyTC7777727+-9331Dean.StraightBU7S32B碍甩BAbraha

47、m.Ben.BU1D329427-17-2319Ann.DullPCBBBS1D472-27-2349Burt.GringlesbyTC7777114M-29-17MCharlene.Lac.PC9999124M-29-17MCharlene.Lac.PS77771364S-92-1B72Reginald.Blot.TC420314672-71-3249Akiko.Yokom.TC777715712-4167Innes.delCast.MC222216722-51-5454Michel.DeFra.MC30211772+-9331Stearns.MacF.BU11111S72+-9331Ste

48、ams.MacF.PS13721975-30-7331Livia.KaraenPS137220W-91-&654Sylvia.PanteleyTC321B21碍92-71開Sheryl.HunterPCBBBSn-nnir-im匚ft_n：一-=10.从authors表中选择state,ci列从publishe-表中选择state,ci列y,并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。usepubsselectstate,cityfrompublishersunionselectstate,cityfromauthorsorderby1,2二结果消息sta

49、tedty1NULLMnchen2NULLParis3CABefkel.4CACavela5CAMenlo.CCAOakland7CAPaloA.SCASanFr.9CASanJ.10CAVacav.11CAWalnu.12DCWadi.13ILChicago14INGary15KSLawns.2r.4aOl.cm11.对上面的查询语句作修改，保留所有重复的记录Selectauthors.city,authors.state,publishers.city,publishers.statefrompublishers,authorsdtystatedtystate1MenloParkCABostonMA2OaklandCABostonMA3BerkeleyCABostonMA4SanJoseCABostonMA5OaklandCABostonMA6LawrenceKSBostonMA7BerkeleyCABostonMAaPalo判toCABostonMA9CoveloCABostonMA1DSanFranciscoCABostonMA11NashvilleTNBostonMA12CorvallisORBostonMA13WalnutCreekCABostonMA14AnnArborMlElostonMA15GaryINElostonMA16Oakl