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人数fromemployeesjoindepartmentsonemployees.w工部门号:dopartments,部门编号groupby部门名称havingunt(*)=2;SeIeCt学历,count()as人数fromemployeesgroupby学历；select*fromemployeesorderby出生日期desc;