《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;