數據庫上機實驗報告

1、數據庫實驗報告（所有實驗院系：信息科學與工程學院專業(yè)：網絡工程電子商務班級：姓名：學號：指導老師：年月日一、實驗目的熟練掌握SQL語句的插入、修改、刪除、查詢等相關語法。能夠使用MicrosoftSQLServer和MySQL軟件進行相關的數據庫操作。二、實驗內容1數據庫的創(chuàng)建和使用；2表的創(chuàng)建和使用；3數據的插入、刪除和修改；4數據的查詢；5.視圖的創(chuàng)建和使用。實驗一、創(chuàng)建學生成績管理數據庫在學生成績管理數據庫中，包括基本的三個關系：studentcoursesc。(1) student(sno,sname，ssexsage，sdept),表示學號，姓名，性別，年齡,所在系。主鍵學號，姓名唯

2、一，系默認cs_(2) Course(cn,ocname，cpno，ccredit)表示課程號，課程名，先行課程號，學分。主鍵課程號(3) SC(snoeno,grade)表示學號，課程號，成績。外鍵，學號和課程號。創(chuàng)建該數據庫代碼及其最后結果圖如下：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學生的所選的課程，成績和專業(yè)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創(chuàng)建數據庫并使用數據庫createdatabaseEnter

12、priseuseEnterprise;創(chuàng)建表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創(chuàng)建表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);創(chuàng)建表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);創(chuàng)建表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);創(chuàng)建表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);創(chuàng)建表DEPENDENT并插入數據createtableDEPENDENT(ESSNchar(9),DEPENDENT_NAMESEXchar(1),char(20),BDATEdatetimeRELATIONSHIPchar(10),primarykey(ESSN,DEPENDENT_NAME),foreignkey(ESSN)referencesEMPLOYEE(SSN);insertintoDEPENDENTvalues(333445555,Alice，F(xiàn),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使用演示腳本創(chuàng)建表、插入數據，查找以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