MYSQL人事工资管理系统--设计视图（四）

蓝晶灵

　　2) 建立一个名为V_CX_GZ的视图，功能是从工资档案表中查询岗位工资和补贴都高于500元的记录，要求显示出部门名称、姓名、性别、职称、基本工资、岗位工资、补贴和奖金，并按部门和基本工资的降序排列。(该视图用于“固定条件查询”功能)MYSQL>CREATE OR REPLACE VIEW V_CX_GZ AS SELECT A.DEPARTMENT_NAME,A.EMPLOYEE_NAME,A.GENDER,A.TITLE,B.BASIC_WAGE,B.POST_WAGE,B.SUBSIDIES,B.FUND FROM EMPLOYEE A LEFT JOIN WAGE B ON (A.EMPLOYEE_ID=B.EMPLOYEE_ID)  WHERE (B.POST_WAGE >500) AND (B.SUBSIDIES >500);MYSQL>SELECT * FROM V_CX_GZ; 3) 建立一个所有职工工资统计视图V_ZGGZTJ，计算所有人员的基本工资的总和、平均值、最大值和最小值。(该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_ZGGZTJ AS SELECT SUM(BASIC_WAGE),AVG(BASIC_WAGE),MAX(BASIC_WAGE),MIN(BASIC_WAGE) FROM WAGE;MYSQL>SELECT * FROM V_ZGGZTJ;
　　4) 建立一个各部门人数统计的视图V_BMTJ，功能是从职工信息表中统计各部门的人数。(该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_BMTJ AS SELECT DEPARTMENT_NAME,COUNT(DEPARTMENT_ANEM) FROM EMPLOYEE GROUP BY DEPARTMENT_NAME;MYSQL>SELECT * FROM V_BMTJ;
　　5) 建立一个各类职称人数统计的视图V_ZCTJ，功能是从职工信息表中统计各类职称的人数。(该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_ZCTJ AS SELECT TITLE,COUNT(TITLE) FROM EMPLOYEE GROUP BY TITLE;MYSQL>SELECT * FROM V_ZCTJ;

　　6) 建立一个各民族人数统计的视图V_MZTJ，功能是从职工信息表中统计各民族的人数。(该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_MZTJ AS SELECT NATIONAL,COUNT(NATIONAL) FROM EMPLOYEE GROUP BY NATIONAL;MYSQL>SELECT * FROM V_MZTJ;

　　7）建立一个职工工资视图V_ZGGZ，功能是从职工信息、工资档案和工资发放三个表中查询所有职工的工资情况，其中包括部门名、姓名、性别、职称、基本工资、岗位工资、补贴、奖金、应得工资、其他加、其他扣、实发工资、发放日期。（其中：应得工资=基本工资+岗位工资+补贴+奖金+其他加；实发工资=基本工资+岗位工资+补贴+奖金+其他加-其他扣。）(该视图用于“选择条件查询”功能)内部查询MYSQL>CREATE OR REPLACE VIEW V_ZGGZ AS SELECT EMPLOYEE.DEPARTMENT_NAME,EMPLOYEE.EMPLOYEE_NAME,EMPLOYEE.GENDER,EMPLOYEE.TITLE,WAGE.BASIC_WAGE,WAGE.POST_WAGE,WAGE.SUBSICDIES,WAGE.FUND,WAGE.BASIC_WAGE+WAGE.POST_WAGE+WAGE.SUBSIDIES+WAGE.FUND+PWAGE.BAISE_WAGE AS TOTAL_WAGE,PWAGE.RAISE_WAGE,PWAGE.CUT_WAGE,WAGE.BASIC_WAGE+WAGE.POST_WAGE+WAGE.SUBSIDIES+WAGE.FUND+PWAGE.RAISE_WAGE-PWAGE.CUT_WAGE AS WAGES FROM EMPLOYEE INNER JOIN WAGE ON EMPLOYEE.EMPLOYEE_ID=WAGE.EMPLOYEE_ID INNER JOIN PWAGE ON  WAGE.EMPLOYEE_ID=PWAGE.EMPLOYEE_ID;MYSQL>SELECT * FROM V_ZGGZ;

　　8) 建立一个月工资汇总视图V_YGZHZ，功能是从视图V_ZGGZ中统计每个月发放的基本工资、岗位工资、补贴、奖金、其他加、应得工资、其他扣、实发工资的总和。 (该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_YGZHZ AS SELECT SUM(BASIC_WAGE),SUM(POST_WAGE),SUM(SUBSIDIES),SUM(FUND),SUM(RAISE_WAGE),SUM(TOTAL_WAGE),SUM(WAGES) FROM V_ZGGZ;MYSQL>SELECT * FROM V_YGZHZ;

　　9) 建立一个部门工资汇总视图V_BMGZHZ，功能是从视图V_ZGGZ中统计各个部门的基本工资、岗位工资、补贴、奖金、其他加、应得工资、其他扣、实发工资的总和。(该视图用于“数据统计”功能)MYSQL>CREATE OR REPLACE VIEW V_BMGZHZ AS SELECT SUM(BASIC_WAGE),SUM(POST_WAGE),SUM(SUBSIDIES),SUM(FUND),SUM(RAISE_WAGE),SUM(TOTAL_WAGES),SUM(CUT_WAGE),SUM(WAGES) FROM V_ZGGZ GROUP BY DEPARTMENT_NAME;MYSQL>SELECT * FROM V_BMGZHZ;

　　哈！ 。。写完了。如有什么不足之处，请指正。顺便给点意见和建议。