数据库课程设计源代码.docx

use kjq111007307/*创建一个部门信息表包含“部门号，部门名，部门经理，人数”属性列*/createtable department(depart_no char(2)primarykey, depart_name char(30)notnull, depart_manage char(6)notnull, depart_people intnotnull)/*创建一个职位信息表包含“职位，基本薪资,福利,失业保险,住房公积金”属性列*/createtable position(pos char(30)primarykey, basesalary floatnotnull, benefits floatnotnull, insurances floatnotnull, housing_funds floatnotnull)/*创建一个职工信息表包含职工号，职工名，性别，年龄，学历，部门号，职位属性列*/createtable staff_message(staff_no char(4)primarykey, staff_name char(10)notnull, staff_sex char(2)check(staff_sex in(男,女)), staff_age intnotnull, staff_edu char(10)notnull, staff_dep char(2)notnull, staff_job char(30)notnull,foreignkey(staff_dep)references department(depart_no),foreignkey(staff_job)references position(pos))/*创建一个员工考勤表包含“职工号，年月，迟到，缺勤，加班”属性列*/createtable staff_days(staff_no char(4), month_date char(6), staff_late intnotnull, staff_absent intnotnull, workoverdays intnotnull,primarykey(staff_no,month_date))/*创建一个薪资表包含“职工号，年月，奖金，罚金，真实薪资”属性列*/createtable salary(staff_no char(4), month_date char(6), addsalary floatnotnull, subsalary floatnotnull, relsalary floatnotnull,primarykey(staff_no,month_date))createindex salary_index on salary(month_date asc,staff_no asc);/*部门表信息的录入*//*经理室*/insertinto departmentvalues(01,manage_department,王栋,1);/*财务科*/insertinto departmentvalues(02,financial_department,张鹏,3);/*技术科*/insertinto departmentvalues(03,plan_department,代淑英,5);/*销售科*/insertinto departmentvalues(04,market_department,金加容,6);/*职位表信息的录入*//*经理*/insertinto positionvalues(manager,4500,1125,-45,-36);/*副经理*/insertinto positionvalues(assistant_manager,4000,1000,-40,-32);/*办事*/insertinto positionvalues(clerk,3500,875,-35,-28);/*助理*/insertinto positionvalues(assistant,2000,500,-20,-16);/*职工信息表信息的录入*/insertinto staff_messagevalues(0101,王栋,男,38,硕士,01,manager);insertinto staff_messagevalues(0201,张鹏,男,35,硕士,02,manager);insertinto staff_messagevalues(0202,程雷,男,30,本科,02,clerk);insertinto staff_messagevalues(02