MySQL数据库技术（第3版） 周德伟 实训参考答案.docx

《MySQL数据库技术（第3版） 周德伟 实训参考答案.docx》由会员分享，可在线阅读，更多相关《MySQL数据库技术（第3版） 周德伟 实训参考答案.docx（9页珍藏版）》请在课桌文档上搜索。

1、实训参考答案实训1:1教学管理系统数据库设计答案（1）e-r图：（2）系（,系名、系主任）教师（教师号、教师名、职称、系号）课程（课程号、课程名、学分、教昨号）学生（生号、姓名、年龄、性别、系号）项目（项目号、名称.负责人）选修（学曼、课程号、分数）负责（数匝号、项目号、排名）2.员工工资管理系统数据库设计答案：U）e-rS:（2）员工（员工编号、姓名、学历、出生日期、性别、工作年限、地址、电话、部门编号）部门（部门编号、名称、备注）薪水(员工编号、收入、支出)实训2:(略)实训3:1.CREATEDATABASEYGG1.;CREATEDATABASETest：(SUSEYGG1.;A1.T

2、ERDATABASETestDEFAU1.TCHARACTERSETgb2312DEFAU1.TCO1.1.ATEgb2312Chineseci;SHoWDATABASES;DROPDATABASETest;CREATETAB1.EEmployees(员工编号char(6)NOTNU1.1.,姓名Char(IO)NOTNU1.1.,学历Char(4)NOTNU1.1.,出生日期dateNOTNU1.1.,性别char(2)NOTNU1.1.,工作年限tinyintdefaultNU1.1.,tvarchar(20)defaultNU1.1.,电话号码Char(12)defaultNU1.1.,

3、员工部门号char(3)NOTNU1.1.PRIMARYKEYC员工编号,)；CREATETAB1.EDepartments(部门编号char(3)NOTNU1.1.,部门名称Char(20)NOTNU1.1.备注text,PRIMARYKEY(部门编号)；CREATETAB1.Esalary(,员工编号Char(6)NOTNU1.1.,收入floatNOTNU1.1.,支出floatNOTNU1.1.,PRIMARYKEYC员工编号,)实训4lINSERTINTOemployeesVA1.UESfOOOOOr.三H,.,1966-01-23,.-1,.&,中山路32-l-5081.83355

4、5681,2,);INSERTINTOemployeesVA1.UES(010008.,I4,.,1976-03-28,.-1,.3,北京东路100-2.183321321,T):INSERTINTO,employeesVA1.UES(020010丁王向容,硕士二1982-12-09,T.2四牌槎10-0-1081.83792361,.,):INSERTINTOemployeesVA1.UES(,020018.,1960-07-30.0.6.中山东路102-2.,83413301,1)：INSERTINTOemployeesVA1.UE$(102201/刘明J本科71972-10-18；1,3

5、.虎距路1-2:83606608丁5)INSERTINTOemployeesVA1.UES1022081.朱俊丁硕士71965-09-28.1.2,牌楼巷5-3-106,.84708817.5);INSERTINTOemployeesVA1.UES(,10899,i,.,1979-08-10,.-0,4,中山路10-3-105,83346722,3,);INSERTINTOemployeesVA1.UES(,1110067张石兵:本科丁1974-1001.1.1.解放路34-l-203,.,84563418,5,);INSERTINTO-employeesVA1.UES(210678,j,1,

6、1977-04-02,2,中山北路24-351.,83467336,3,):INSERTINTOemployeesVA1.UES(302566.李玉琨.本科1968-09-20.T,3,.热和路209-31.,58765M1,4)：INSERTINTOemployees,VA1.UES(308759丁叶凡丁本科197-8,T,2J北京西路3-7-52l,8330890r,4);INSERTINTOemployeesVA1.UES504209陈林琳大专,19690903.iO*5J汉中路120-4-12,.,84468158.,4);INSERTINTOdepartmentsVAUJES(,财务

7、部,NU1.1.）：INSERTINTOdepartmentsVA1.UESC2,人力资源部,NU1.1.）：INSERTINTOdepartmentsVA1.UES(,3,经理办公室，NU1.1.）;INSERTINTOdepartmentsVAUJESC4,研发部，NU1.1.）：INSERTINTOdepartmentsVA1.UES(5,市场部,NU1.1.）：3INSERTINTOsalary-VA1.UESCOOOOOl,2100.8,123.09);INSERTINTOsalaryVA1.UES(,010008,1582.62,88.03);INSERTINTOsalaryVA1

8、.UES(,020010,2860,198)：INSERTINTOsalaryVAI-URS(020018,2347.68,180);INSERTINTOsalary-VA1.UESC10220,2569.88,185.65)：INSERTINTOsalaryVA1.UESC102208,1980,100):INSERTINTOsalaryVA1.UES(,108991,.3259.98.281.52);INSERTINTOsalaryVA1.UES(,111006,1987.01,79.58);INSERTINTOsalary-VA1.UESC210678,2240,121);INSERTI

9、NTOsalaryVA1.UESC302566,2980.7,210.2)：INSERTINTOsalaryVA1.UES(,308759,.2531.98,199.08)：INSERTINTOsalaryVA1.UES（504209,2066.15,108）：2.1 INSERTINTOdepartmentsVA1.UES（*6分销售部丁。建）;2 INSERTINTOemployees,VA1.UES（600001/张松丁本科:19B8-01-30男12解放路23号丁8323456T.6）：INSERTINTOemployeesVA1.UES（,600002,.,付理大专,.1979-12

10、-01,，女,10,瑚景路45-.,83455689,61）;3 UPDATEsalary.employeesSETSaIary.收入=收入+1000.employees.员工部门号=6WHERESaIary.员工编号=employees.员工编号andemployees.姓名=王向容4 DE1.ETEsalary.employeesFROMSaIary,employeesWHERESaIary.员工镇号=employees.员工编号andemployees.姓名=王林；实训51.selectdistinct员工部门号,性别fromemployees;SelectroUnd（收入-支出,2）a

11、s实际收入fromsalary;SElECT姓名，CASEWHEN性别=TTHEN男,WHEN性别=OTHEN女ENDAS性别FROMemployees;select姓名,地址asaddressl电话号码astelephphonefromemployees;SeleCtround（avg（收入）,2）as平均月收入fromsalary;SeIeetround（sum（支出）2）as总支出fromsalary;SeIeCt姓名,地址,电话号码fromemployeeswhere性别=0;（gelectcount（*）as员工数fromemployees;selectmax（收入），min（收入）

12、fromsalary;2.SeIeet员工编号，收入fromsalarywhere收入2000;select姓名,地址fromemployeeswhere出生日期=3and性别=Tand学历in（本科,硕士,）；SeIeCt姓名,地址,学历fromemployeeswhere员工编号like、0_;select员工编号,收入fromsalarywhere收入BETWEEN2000AND3000;3.CDseIectemployees.*,部门名称fromemployeesjoindepartmentsonemployees.员工部门号=departments.部门编号where姓名=王林；Se

13、IeCtemployees.*,部门名称fromemployeesjoindepartmentsonemployees.员工部门号=departments.部门编号where部门名称in（财务部研发部市场部,）；SeIeCtemployees*,收入,支出fromemployeesjoinsalaryUSing（员工嫔号）；SeIeCt姓名,收入,支出fromemployeesjoinsalaryonemployees.员工编号=Salary.员工编号joindepartmentsonemployees.员工部门号=departments.部门编号where部门名称=研发部and出生日期197

14、00101;SE1.ECT姓名,地址，CASEWHEN收入v2000THEN低收入WHEN=2000and收入=3000THEN中等收入WHEN收入3OTHEN高收入，ENDAS收入水平FROMemployeesjoinsalaryUSing(员工编号)；4.select部门名称,count()as人数fromemployeesjoindepartmentsonemployees.工部门号=departments.部门编号groupby部门名称；Seleet性别，count(*)as人数fromemployeesgroupby性别；select部门名称,count(*)as人数fromempl

15、oyeesjoindepartmentsonemployees.w工部门号:dopartments,部门编号groupby部门名称havingunt(*)=2;SeIeCt学历,count()as人数fromemployeesgroupby学历；select*fromemployeesorderby出生日期desc;select*fromsalaryorderby收入；SeleCt工作年FR.count(*)as人数fromemployeesgroupby工作年限Orderbycount(*);实训6 createviewEmP一VieWIasselect员工编号,姓名,工作年限,学历from

16、employeeswhere性别=1; selectfromEmp,viewlwhere工作年限=2; createviewEmpview2asselectemployees.员工编号,姓名,部门名称,收入fromemployeesjoinsalaryonemployees.员工编号:SaIary.员工编号joindepartmentsonemployees.员工部门号=departments.部门编号；select员工编号,姓名,收入fromEmp_view2where部门名称二研发部； createviewEmp.view3asselect员工编号,姓名,学历,出生日期,性别,工作年限,员

17、工部门号fromemployeeswhere工作年限2withcheckoption; insertintoempview3VaIUeS(,041110,钟晓玲，博士,19731201；男,3.4); updateempview2set收入=收入+200Where姓名=李丽； deletefromemp_view3where学历二本科；alterviewemp_viewl(员工编号,姓名,实际收入)asselectemployees员工编号,姓名.round(收入支出.2)fromemployeesjoinsalaryUsing(员工编号)； dropviewempview2,empview3

18、;实训71.(1)CDcreateindexdepartJndonCmPlOyeeS(员工部门号)；CreateindexAdjndonemployees(姓名,地址)；Createuniqueindexmc_indondepartment部门名称)；(2)AITERTAB1.EemployeesADDINDEXdate_ind(出生日期)，addindexna_ind(姓名,性别)；AlTERTAB1.EdepartmentsADDprimarykey(部门嫔号)；(3) createtablecpk(产品编号char(10)notnullprimarykey,产品名称char(20)not

19、null,单价float(5,2),库存量int,indexCPkJh(库存量,单价)；2. SHOWINDEXFROMemployees;3.CREATETAB1.Ejj(employeelDchar(6)NotNU1.1.primarykey,jefloat(5,2)NU1.1.,FOREIGNKEY(employeelD)REFERENCESemployees(员工嫔号)ONupdateCASCADEondeleteCASCADE);2CREATETAB1.EEMP(IDchar(6)NotNU1.1.primarykey.性别Char(2)NU1.1.C2ECK(性别IN(男丁女CRE

20、ATETAB1.EEMP_1(IDchar(6)NotNU1.1.primarykey,出生日期dateNU1.1.CHECK(出生日期1980-01-01);4CREATETAB1.EEMP_2(IDchar(6)NotNU1.1.primarykey.工资floai(5.2)notnull.扣款float(5.2)n3l.CHECK(工资扣款)；实训81.DE1.IMITERSSCREATEPROCEDUREcd2char(6),OUTresultnt)BEGINDEC1.AREsl.s2INT：Select收入-支出intosifromsalarywhere员工编号=idl:Select

21、收入-支出intos2fromsalarywhere员工编,号=id2:IFsls2THENSETresult=0;E1.SESETresult=1;ENDIF;ENDSSDE1.IMITER;：2CallcpC000,10899.(gr);3selectr；2.DelimiterssCREATEFUNCTIONnum.p()RETURNSINTEGERDETERMINISTICBEGINRETURN(SE1.ECTCOUNT()FROMemployees);END$DE1.IMITER;DE1.IMITERSSCREATEFUNCTIONPe1.Work(idchar(6)RETURNSch

22、ar(20)DETERMINISTICBEGINDEC1.AREbmChar(20);SE1.ECT部门名称intobmfromemPIoyeeSioindePanmentSOnernPlOyeeS.员工部门号departments.部门编号v/here员工编号=id;IFbm=，研发部，THENRETURN(SE1.ECT学历FROMemployeesWHERE员工编号=id):E1.SERETURN,NO,;ENDIF;END$DE1.IMITER；3.DE1.IMITERSSCREATETRIGGERsalary.delAFTERDE1.ETEONemployeesFOREACHROWB

23、EGINDE1.ETEFROMsalaryWHERE员工编号=O1.D员工编号：END$DE1.IMITER：DE1.IMITERSSCREATETRIGGERempinsAFTERINSERTONemployeesFOREACHROWBEGINinsertintosalaryVaIUeS(new.员工编号,0.0);END$DE1.IMITER;DE1.IMITERSSCREATETRIGGERempupdateAfterUPDATEONemployeesFOREACHROWBEGINUPDATEsalarySET收入=收入+(new.工作年限-OId.工作年限)500Where员工编号=N

24、EW.员工编号；END$DE1.IMITER：4.1DE1.IMITERS$CREATEEVENTeve111.empONSCHEDU1.EEVERY1MINUTESTARTSCURDATE()+INTERVA1.1MINUTEDOBEGINUPDATEemployeesset工作年限=工作年限+1Where姓名=王林：END$DE1.IMITER;2SETG1.OBA1.EVEN1.SCHEDU1.ER=1;3A1.TEREVENTeve1.empDISAB1.E;实训91.CREATEUSERuserllocalhostIDENTIFIEDBY,1234,user2localhostIDE

25、NTIFIEDBY,1234,;(2RENAMEUSERuser2localhostTOuser3localhost;SETPASSWORDFORuser3glocalhost=123456,;DRoPUSERuser3glocalhost;GRANTSE1.ECTONYGG1.empIoyeesTOuserllocalhost：GRANTinsert,update,deleteONYGG1.employeesTOuserllocalhost;GRANTA1.1.ONYGG1.TOuserllocalhost;GRANTSE1.ECTONYGG1.saIaryTOuserllocalhostW

26、ITHGRANTOPTION;REVOKESE1.ECTONYGG1.empIoyeesFROMUSerl1。CaIhOSt;2.SE1.ECT*FROMYGG1.dePanmentSINTOOUTFI1.E,D7myfile.tfFIE1.DSTERMINATEDBY,1,OPTIONA1.1.YENC1.OSEDBY1.INESTERMINATEDBY?;CREATETAB1.Ebk_depart1.IKEYGG1.departments;1.OADDATAINFI1.E,Drmyfile.tfINTOTAB1.Ebk.departFIE1.DSTERMINATEDBYVOPTIONA1.1.YENC1.OSEDBY,1.inesterminatedbyet；