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

实训参考答案实训1:1教学管理系统数据库设计答案（1）e-r图：（2）系（¾,系名、系主任）教师（教师号、教师名、职称、系号）课程（课程号、课程名、学分、教昨号）学生（生号、姓名、年龄、性别、系号）项目（项目号、名称.负责人）选修（学曼、课程号、分数）负责（数匝号、项目号、排名）2.员工工资管理系统数据库设计答案：U）e-rS:（2）员工（员工编号、姓名、学历、出生日期、性别、工作年限、地址、电话、部门编号）部门（部门编号、名称、备注）薪水(员工编号、收入、支出)实训2:(略)实训3:1.CREATEDATABASEYGG1.;CREATEDATABASETest：(SUSEYGG1.;A1.TERDATABASETestDEFAU1.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.,'t'varchar(20)defaultNU1.1.,电话号码Char(12)defaultNU1.1.,'员工部门号'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员工编号,)实训4lINSERTINTOemployees'VA1.UESfOOOOOr.三H,.,1966-01-23,.-1,.&,中山路32-l-5081.833555681,2,);INSERTINTOemployees'VA1.UES('010008'.,I4,.,1976-03-28,.-1,.3,'北京东路100-2'.183321321,T):INSERTINTO,employees'VA1.UES('020010丁王向容,硕士二1982-12-09,T.2四牌槎10-0-1081.83792361,.,):INSERTINTOemployees'VA1.UES(,020018'.,','1960-07-30'.'0'.6.中山东路102-2'.,83413301','1')：INSERTINTOemployees'VA1.UE$(102201/刘明'J本科71972-10-18；1,3.'虎距路1-2:83606608丁5)INSERTINTO'employeesVA1.UES'1022081.朱俊丁硕士71965-09-28.1.2,牌楼巷5-3-106,.84708817'.'5');INSERTINTOemployees'VA1.UES(,10899,i,.±,'1979-08-10,.-0,4,'中山路10-3-105,83346722,3,);INSERTINTOemployees'VA1.UES(,1110067张石兵:本科丁1974-1001'.1.1.'解放路34-l-203,.,84563418,5,);INSERTINTO-employees'VA1.UES('210678,¼j,1,'1977-04-02',2,中山北路24-351.,83467336,3,):INSERTINTOemployees'VA1.UES('302566.李玉琨.本科'1968-09-20'.T,3,.热和路209-31.,58765M1,'4')：INSERTINTO'employees,VA1.UES(308759丁叶凡丁本科197-8,T,2J北京西路3-7-52'l,8330890r,'4');INSERTINTOemployees'VA1.UES¢504209陈林琳大专,'19690903'.iO*5J汉中路120-4-12,.,84468158'.,4');INSERTINTOdepartmentsVAUJES(,'财务部',NU1.1.）：INSERTINTOdepartmentsVA1.UESC2",人力资源部',NU1.1.）：INSERTINTOdepartmentsVA1.UES(,3','经理办公室'，NU1.1.）;INSERTINTOdepartments'VAUJESC4','研发部'，NU1.1.）：INSERTINTOdepartmentsVA1.UES('5',市场部',NU1.1.）：3INSERTINTO'salary-VA1.UESCOOOOOl",2100.8,123.09);INSERTINTO'salary'VA1.UES(,010008,1582.62,88.03);INSERTINTO'salary'VA1.UES(,020010,2860,198)：INSERTINTO"salary'VAI-URS('020018',2347.68,180);INSERTINTO'salary-VA1.UESC10220,2569.88,185.65)：INSERTINTO'salary'VA1.UESC102208,1980,100):INSERTINTO'salary'VA1.UES(,108991,.3259.98.281.52);INSERTINTO'salary'VA1.UES(,111006,1987.01,79.58);INSERTINTO'salary-VA1.UESC210678,2240,121);INSERTINTO'salary'VA1.UESC302566,2980.7,210.2)：INSERTINTO'salary'VA1.UES(,308759,.2531.98,199.08)：INSERTINTOsalary'VA1.UES（'504209',2066.15,108）：2.1 INSERTINTOdepartments'VA1.UES（*6分销售部丁。建'）;2 INSERTINTOemployees,VA1.UES（'600001/张松丁本科:19B8-01-30男12解放路23号丁8323456T.6）：INSERTINTO'employees'VA1.UES（,600002,.,付理大专,.1979-12-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）as实际收入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（收入）fromsalary;2.SeIeet员工编号，收入fromsalarywhere收入>2000;select姓名,地址fromemployeeswhere出生日期>=<1970-01-01"SeleCt»fromemployeeswhere工作年限>=3and性别=Tand学历in（"本科,硕士,）；SeIeCt姓名,地址,学历fromemployeeswhere员工编号like、0_'select员工编号,收入fromsalarywhere收入BETWEEN2000AND3000;3.CDseIectemployees.*,部门名称fromemployeesjoindepartmentsonemployees.员工部门号=departments.部门编号where姓名='王林'；SeIeCtemployees.*,部门名称fromemployeesjoindepartmentsonemployees.员工部门号=departments.部门编号where部门名称in（'财务部研发部市场部,）；SeIeCtemployees*,收入,支出fromemployeesjoinsalaryUSing（员工嫔号）；SeIeCt姓名,收入,支出fromemployeesjoinsalaryonemployees.员工编号=Salary.员工编号joindepartmentsonemployees.员工部门号=departments.部门编号where部门名称='研发部'and出生日期'19700101;©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人数fromemployeesjoindepartmentsonemployees.w工部门号:dopartments,部门编号groupby部门名称havingunt(*)=2;SeIeCt学历,count()as人数fromemployeesgroupby学历；©select*fromemployeesorderby出生日期desc;©select*fromsalaryorderby收入；SeleCt工作年FR.count(*)as人数fromemployeesgroupby工作年限Orderbycount(*);实训6 createviewEmP一VieWIasselect员工编号,姓名,工作年限,学历fromemployeeswhere性别='1' selectfromEmp,viewlwhere工作年限=2; createviewEmpview2asselectemployees.员工编号,姓名,部门名称,收入fromemployeesjoinsalaryonemployees.员工编号:SaIary.员工编号joindepartmentsonemployees.员工部门号=departments.部门编号；select员工编号,姓名,收入fromEmp_view2where部门名称二'研发部'； createviewEmp.view3asselect员工编号,姓名,学历,出生日期,性别,工作年限,员工部门号fromemployeeswhere工作年限2withcheckoption; insertintoempview3VaIUeS(,041110',钟晓玲'，博士',19731201'；男',3.4); updateempview2set收入=收入+200Where姓名='李丽'； deletefromemp_view3where学历二本科；alterviewemp_viewl(员工编号,姓名,实际收入)asselectemployees员工编号,姓名.round(收入支出.2)fromemployeesjoinsalaryUsing(员工编号)； dropviewempview2,empview3;实训71.(1)CDcreateindexdepartJndonCmPlOyeeS(员工部门号)；CreateindexAdjndonemployees(姓名,地址)；Createuniqueindexmc_indondepartment"部门名称)；(2)AITERTAB1.EemployeesADDINDEXdate_ind(出生日期)，addindexna_ind(姓名,性别)；AlTERTAB1.EdepartmentsADDprimarykey(部门嫔号)；(3) createtablecpk(产品编号char(10)notnullprimarykey,产品名称char(20)notnull,单价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(男丁女CREATETAB1.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.IMITERSSCREATEPROCEDUREc<INdlchar(6),IN>d2char(6),OUTresultnt)BEGINDEC1.AREsl.s2INT：Select收入-支出intosifromsalarywhere员工编号=idl:Select收入-支出intos2fromsalarywhere员工编,号=id2:IFsl>s2THENSETresult=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)RETURNSchar(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.ETEONemployeesFOREACHROWBEGINDE1.ETEFROMsalaryWHERE员工编号=O1.D员工编号：END$DE1.IMITER：DE1.IMITERSSCREATETRIGGERempinsAFTERINSERTONemployeesFOREACHROWBEGINinsertintosalaryVaIUeS(new.员工编号,0.0);END$DE1.IMITER;DE1.IMITERSSCREATETRIGGERempupdateAfterUPDATEONemployeesFOREACHROWBEGINUPDATEsalarySET收入=收入+(new.工作年限-OId.工作年限)500Where员工编号=NEW.员工编号；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,user2localhostIDENTIFIEDBY,1234,;(2>RENAMEUSERuser2localhostTOuser3localhost;SETPASSWORDFORuser3<glocalhost='123456,;DRoPUSERuser3<glocalhost;GRANTSE1.ECTONYGG1.empIoyeesTOuserllocalhost：GRANTinsert,update,deleteONYGG1.employeesTOuserllocalhost;GRANTA1.1.ONYGG1.TOuserllocalhost;©GRANTSE1.ECTONYGG1.saIaryTOuserl©localhostWITHGRANTOPTION;©REVOKESE1.ECTONYGG1.empIoyeesFROMUSerl1。CaIhOSt;2.SE1.ECT*FROMYGG1.dePanmentSINTOOUTFI1.E,D7myfile.t×fFIE1.DSTERMINATEDBY,1,OPTIONA1.1.YENC1.OSEDBY""1.INESTERMINATEDBY?;CREATETAB1.Ebk_depart1.IKEYGG1.departments;1.OADDATAINFI1.E,Drmyfile.t×fINTOTAB1.Ebk.departFIE1.DSTERMINATEDBYVOPTIONA1.1.YENC1.OSEDBY'",1.inesterminatedbyet；