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;
