use kjq111007307/*创建一个部门信息表包含“部门号，部门名，部门经理，人数”属性列*/create table department(depart_no char(2)primary key,depart_name char(30)not null,depart_manage char(6)not null,depart_people int not null)/*创建一个职位信息表包含“职位，基本薪资,福利,失业保险,住房公积金”属性列*/ create table position(pos char(30)primary key,basesalary float not null,benefits float not null,insurances float not null,housing_funds float not null)/*创建一个职工信息表包含"职工号，职工名，性别，年龄，学历，部门号，职位"属性列*/ create table staff_message(staff_no char(4)primary key,staff_name char(10)not null,staff_sex char(2)check(staff_sex in('男','女')),staff_age int not null,staff_edu char(10)not null,staff_dep char(2)not null,staff_job char(30)not null,foreign key(staff_dep)references department(depart_no), foreign key(staff_job)references position(pos))/*创建一个员工考勤表包含“职工号，年月，迟到，缺勤，加班”属性列*/create table staff_days(staff_no char(4),month_date char(6),staff_late int not null,staff_absent int not null,workoverdays int not null,primary key(staff_no,month_date))/*创建一个薪资表包含“职工号，年月，奖金，罚金，真实薪资”属性列*/create table salary(staff_no char(4),month_date char(6),addsalary float not null,subsalary float not null,relsalary float not null,primary key(staff_no,month_date))create index 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('0203','王晶晶','女','29','本科','02','assistant');insertinto staff_messagevalues('0301','代淑英','女','35','硕士','03','manager'); insertinto staff_messagevalues('0302','刘燕','女','30','本科','03','assistant_manager'); insertinto staff_messagevalues('0303','杨浩','男','27','本科','03','clerk');insertinto staff_messagevalues('0304','程伟','男','31','本科','03','clerk');insertinto staff_messagevalues('0305','唐琦','女','25','本科','03','assistant');insertinto staff_messagevalues('0401','金加容','女','34','本科','04','manager'); insertinto staff_messagevalues('0402','吴辉','男','35','本科','04','assistant_manager'); insertinto staff_messagevalues('0403','陈睿','男','32','本科','04','clerk');insertinto staff_messagevalues('0404','万莉','女','36','本科','04','clerk');insertinto staff_messagevalues('0405','方冬雨','女','31','本科','04','clerk'); insertinto staff_messagevalues('0406','陈晨','女','27','专科','04','assistant'); insertinto staff_daysvalues('0101','201205','0','0','0');insertinto staff_daysvalues('0201','201205','0','0','0');insertinto staff_daysvalues('0202','201205','0','0','0');insertinto staff_daysvalues('0203','201205','0','0','0');insertinto staff_daysvalues('0301','201205','0','0','0');insertinto staff_daysvalues('0302','201205','0','0','0');insertinto staff_daysvalues('0303','201205','0','0','0');insertinto staff_daysvalues('0304','201205','0','0','0');insertinto staff_daysvalues('0305','201205','0','0','0');insertinto staff_daysvalues('0401','201205','0','0','0');insertinto staff_daysvalues('0402','201205','0','0','0');insertinto staff_daysvalues('0403','201205','0','0','0');insertinto staff_daysvalues('0404','201205','0','0','0');insertinto staff_daysvalues('0405','201205','0','0','0'); insertinto staff_daysvalues('0406','201205','0','0','0'); insertinto salaryvalues('0101','201205','0','0','5544'); insertinto salaryvalues('0201','201205','0','0','5544'); insertinto salaryvalues('0202','201205','0','0','4312'); insertinto salaryvalues('0203','201205','0','0','2464'); insertinto salaryvalues('0301','201205','0','0','5544'); insertinto salaryvalues('0302','201205','0','0','4928'); insertinto salaryvalues('0303','201205','0','0','4312'); insertinto salaryvalues('0304','201205','0','0','4312'); insertinto salaryvalues('0305','201205','0','0','2464'); insertinto salaryvalues('0401','201205','0','0','5544'); insertinto salaryvalues('0402','201205','0','0','4928'); insertinto salaryvalues('0403','201205','0','0','4312'); insertinto salaryvalues('0404','201205','0','0','4312'); insertinto salaryvalues('0405','201205','0','0','4312');insertinto salaryvalues('0406','201205','0','0','2464');/*******************触发器***********************//*创建一个触发器当修改考勤表中的加班或迟到或缺勤时，薪资表中的数据有自动更新的功能*/ create trigger xinzi1on staff_daysfor updateas/*定义三个变量late,absent,overdays当考勤表有更新时用来记录新的数据*/declare @late int,@absent int,@overdays int/*给变量赋值*/select @late=staff_late from insertedselect @absent=staff_absent from insertedselect @overdays=workoverdays from inserted/*定义变量来记录组成最终真实薪资的各项薪资*/declare @kkbasesalary float,@kkaddsalary float,@kksubsalary float,@kkbenefits float,@kkinsurances float,@kkhousing_funds float,@kkrelsalarys float/*给组成最终真实薪资的各项薪资赋值*/select @kkbasesalary=(select basesalaryfrom positionwhere pos=(select staff_jobfrom staff_messagewhere staff_no=(select staff_no from inserted)))select @kkaddsalary=(select workoverdays from inserted)*30select @kksubsalary=(select staff_late from inserted)*(-10)+(select staff_absent from inserted)*(-30)select @kkbenefits=@kkbasesalary*(0.25)select @kkinsurances=@kkbasesalary*(-0.01)select @kkhousing_funds=@kkbasesalary*(-0.008)/*用组成真实薪资的各项薪资来给真实薪资赋值*/select @kkrelsalarys=@kkbasesalary+@kkaddsalary+@kksubsalary+@kkbenefits+@kkinsurances+@kkhousing_funds/*利用变量对薪资表中的'加班费，迟到缺勤扣除费'进行更新*/update salaryset addsalary=@kkaddsalarywhere staff_no=(select staff_no from inserted)and month_date=(select month_date from inserted)update salaryset subsalary=@kksubsalarywhere staff_no=(select staff_no from inserted)and month_date=(select month_date from inserted)update salaryset relsalary=@kkrelsalaryswhere staff_no=(select staff_no from inserted)and month_date=(select month_date from inserted)/*创建一个触发器当对考勤表插入新数据时，薪资表有自动对应插入新数据的功能*/ create trigger xinzi2on staff_daysfor insertasbegin/*定义变量来记录薪资表中应插入的各项数据*/declare @jjstaff_no char(4),@jjmonth_date char(6)declare @jjbasesalary float,@jjaddsalary float,@jjsubsalary float,@jjbenefits float,@jjinsurances float,@jjhousing_funds float,@jjrelsalarys float/*给各个变量赋值*/select @jjstaff_no=(select staff_nofrom inserted)select @jjmonth_date=(select month_datefrom inserted)select @jjbasesalary=(select basesalaryfrom positionwhere pos=(select staff_jobfrom staff_messagewhere staff_no=(select staff_no from inserted)))select @jjaddsalary=0select @jjsubsalary=0select @jjbenefits=@jjbasesalary*(0.25)select @jjinsurances=@jjbasesalary*(-0.01)select @jjhousing_funds=@jjbasesalary*(-0.008)select @jjrelsalarys=@jjbasesalary+@jjbenefits+@jjinsurances+@jjhousing_funds/*将新值插入到薪资表中*/insertinto salaryvalues(@jjstaff_no,@jjmonth_date,@jjaddsalary,@jjsubsalary,@jjrelsalarys)end/*对员工信息表创建一个触发器实现当登记新来员工信息时,薪资表和考勤表中自动对应增加新记录的功能*/create trigger staffmessage1on staff_messagefor insertasbegin/*定义变量来记录待插入薪资表和考勤表的数据*/declare @hhstaff_no char(4)select @hhstaff_no=(select staff_no from inserted)/*向考勤表中自动插入新员工的薪资数据,同时薪资表也会自动插入新数据*/insertinto staff_daysvalues(@hhstaff_no,'201206',0,0,0)end/*对员工信息表创建一个触发器来实现当某员工信息从信息表中删除时，薪资表和考勤表能自动删除关于该员工的信息的功能*/create trigger staffmessage2on staff_messagefor deleteasbegin/*删除辞职员工在薪资表中的记录*/deletefrom salarywhere staff_no=(select staff_no from deleted)/*删除辞职员工在考勤表中的记录*/deletefrom staff_dayswhere staff_no=(select staff_no from deleted)end/******************存储过程********************//*创建一个存储过程实现向部门表录入新数据的功能*/create procedure department_insert@aadepart_no char(2),@aadepart_name char(30),@aadepart_manage char(6),@aadepart_people intasbegininsertinto departmentvalues(@aadepart_no,@aadepart_name,@aadepart_manage,@aadepart_people) end/*创建一个存储过程实现向职位表录入新数据的功能*/create procedure position_insert@bbpos char(30),@bbbasesalary float,@bbbenefits float,@bbinsurances float,@bbhousing_funds floatasbegininsertinto positionvalues(@bbpos,@bbbasesalary,@bbbenefits,@bbinsurances,@bbhousing_funds) end/*创建一个存储过程实现向职工信息表录入新数据的功能*/create procedure staffmessage_insert@ccstaff_no char(4),@ccstaff_name char(10),@ccstaff_sex char(2),@ccstaff_age int,@ccstaff_edu char(10),@ccstaff_dep char(2),@ccstaff_job char(30)asbegininsertinto staff_messagevalues(@ccstaff_no,@ccstaff_name,@ccstaff_sex,@ccstaff_age,@ccstaff_edu,@ccstaff_dep, @ccstaff_job)end/*创建一个存储过程实现在新的一月时向考勤表中插入新数据的功能*/create procedure kaoqin_insert@qqstaff_no char(4),@qqmonth_date char(6),@qqstaff_late int,@qqstaff_absent int,@qqworkoverdays intasbegininsertinto staff_daysvalues(@qqstaff_no,@qqmonth_date,@qqstaff_late,@qqstaff_absent,@qqworkoverdays) end/*创建一个存储过程实现只能查看自己的基本信息的功能*/create proc select_staffmessage@staff_number char(4)asselect*from staff_messagewhere staff_no=@staff_number;/*创建一个存储过程实现只能查看自己的工资情况的功能*/create proc select_salary@staff_num char(4)as/*查看自己某年某月的福利，失业保险，住房公积金，奖金，罚金，最终真实工资*/select salary.staff_no,salary.month_date,position.benefits,position.insurances, position.housing_funds,salary.addsalary,salary.subsalary,salary.relsalaryfrom salary,staff_message,positionwhere salary.staff_no=@staff_numand salary.staff_no=staff_message.staff_noand staff_message.staff_job=position.pos;/*创建一个存储过程实现只能查看自己的考勤信息的功能*/create proc select_staffdays@staff_no char(4)asselect*from staff_dayswhere staff_no=@staff_no;/*****************实现功能的代码********************//*查看五个基本表*/select*from department;select*from position;select*from staff_message;select*from staff_daysselect*from salary;/*查看某员工自己的信息*/exec select_staffmessage'0202';/*查看某员工自己的考勤记录*/exec select_staffdays'0202';/*查看某员工自己的薪资*/exec select_salary'0202';/*更新考勤表薪资表会自动对应更新，*/update staff_daysset staff_late=3where staff_no='0101'and month_date='201205';select*from staff_days;select*from salary;/*向考勤表插入信息，薪资表也会自动对应插入信息*/exec kaoqin_insert'0101','201206','0','0','0';select*from staff_days;select*from salary;/*向员工信息表插入信息，考勤表和薪资表也会自动对应插入新信息*/ exec staffmessage_insert'0204','陈珍','女','29','本科','02','clerk';select*from staff_message;select*from staff_days;select*from salary;/*删除员工信息表的信息，考勤表和信息表也会自动删除对应信息*/ deletefrom staff_messagewhere staff_no='0204';select*from staff_message;select*from staff_days;select*from salary;/*按部门查询员工的基本信息*/select*from staff_messagewhere staff_dep='03';/*按年月查看员工的考勤记录*/select*from staff_dayswhere month_date='201205';/*按部门查看所有员工薪资总数*/select sum(relsalary)from staff_message,salarywhere salary.staff_no=staff_message.staff_noand staff_dep='04';/*按职位查看所有员工薪资总数*/select sum(relsalary)from staff_message,salarywhere salary.staff_no=staff_message.staff_noand staff_job='clerk';。