工资管理数据库代码.doc

CREATE DATABASE 工资管理库 ON PRIMARY(NAME=工资管理库,FILENAME=D:工资管理库_data.mdf,SIZE=4MB,MAXSIZE=6MB,FILEGROWTH=2MB)LOG ON (NAME =工资管理库_log, FILENAME =D:工资管理库.ldf, SIZE=1MB,MAXSIZE=25MB,FILEGROWTH=10%)create table 部门(部门名称 CHAR(50) NOT NULL primary key,部门负责人 CHAR(50) NOT NULL, 部门人数 CHAR(50);create table 员工(工号 CHAR(50) NOT NULL primary key,姓名 CHAR(50) NOT NULL,性别 CHAR(2) DEFAULT男CHECK(性别 IN(男,女),年龄 CHAR(10) NOT NULL,籍贯 VARCHAR(50) NOT NULL,身份证号 CHAR(50) UNIQUE NOT NULL,婚姻情况 CHAR(50),学历 CHAR(50),部门名称 CHAR(50) NOT NULL,工种 CHAR(50),工龄 CHAR(4) NOT NULL,职务等级 CHAR(50);CREATE TABLE 工资(工号 char(50) not null primary key,工资年月 char(50) not null ,岗位工资 decimal not null,岗位补贴 decimal,出勤天数 decimal not null,加班工时 decimal, 加班工资 decimal, 加班天数 decimal,加班类别 char(50),病假扣款 decimal,事假扣款 decimal, 其他扣款 decimal, 应发工资 decimal, 实发工资 decimal not null,foreign key (工号) references 员工(工号);alter table 工资add constraint CK_gongho check(工号0);create table 系统用户(用户名 char(50) primary key, 用户权限 char(50), 用户密码 char(10),)create table 工龄工资(工龄 char(4) primary key,工龄工资 float(8)not null);create table 员工登陆密码(工号 CHAR(50) primary key,密码 char(6),foreign key(工号) references 员工(工号);alter table 员工登陆密码add constraint CK_gongh check(工号0);insert into 部门 values(财务部,王玲,20)insert into 部门 values(生产部,丽丽,400)insert into 部门 values(董事会,李四,10)insert into 部门 values(后勤部,二子,40)insert into 部门 values(公关部,小丽,15)insert into 部门 values(行政部,琪琪,20)insert into 部门 values(企划部,小新,15)insert into 部门 values(销售部,小海,10)insert into 部门 values(人力资源部,丽子,5)insert into 员工 values(1001,李民,男,25,天津,111000101,已婚,专科 ,销售部,内销,4,职工)insert into 员工 values(1002,李四,男,50,上海,111000102,已婚,本科 ,董事长,null,30,董事长)insert into 员工 values(1003,丽丽,女,25,北京,111000103,未婚,本科 ,生产部,电工,8,生产部部长)insert into 员工 values(1004,王玲,男,35,东京,111000104,已婚,研究生 ,财务部,预算,6,财务部部长)insert into 员工 values(1005,二子,女,30,江西,111000105,未婚,本科 ,后勤部,车工,7,后勤部部长)insert into 员工 values(1006,小丽,女,26,苏州,111000106,已婚,本科 ,公关部,null,3,公关部部长)insert into 员工 values(1007,小新,男,27,扬州,111000107,已婚,研究生 ,企划部,主管,2,企划部部长)insert into 员工 values(1008,琪琪,女,24,安徽,111000108,未婚,本科 ,行政部,null,8,行政部部长)insert into 员工 values(1009,小海,男,23,南京,111000109,已婚,专科 ,销售部,主管,4,销售部部长)insert into 员工 values(1010,丽子,女,22,江西,111000110,未婚,本科 ,人力资源部,null,4,人力资源部部长)insert into 员工 values(1011,小美,女,26,海南,111000111,已婚,本科 ,公关部,前台,5,职工)insert into 员工 values(1012,小韩,男,20,山东,111000112,离异,本科 ,财务部,融资,5,出纳)insert into 工资 values(1001,2013-12,2500,500,25,2,15,4,加急,50,50,100,3500,3400)insert into 工资 values(1002,2013-12,10000,600,100,5,25,2,null,0,0,0,15000,20000)insert into 工资 values(1003,2013-12,4000,500,30,2,30,4,核对,50,50,100,5000,4800)insert into 工资 values(1004,2013-12,3500,500,30,2,30,4,加急,50,50,100,5000,4800)insert into 工资 values(1005,2013-12,4000,500,30,2,30,4,加急,50,50,100,5000,4800)insert into 工资 values(1006,2013-12,4000,500,30,2,30,4,加急,50,50,100,5000,4800)insert into 工资 values(1007,2013-12,3500,500,30,2,30,4,审核,50,50,100,5200,5000)insert into 工资 values(1008,2013-12,3500,500,30,2,30,4,加急,50,50,100,5400,5200)insert into 工资 values(1009,2013-12,3500,500,30,2,30,4,审核,50,50,100,5000,4800)insert into 工资 values(1010,2013-12,3500,500,30,2,30,4,加急,50,50,100,4800,4300)insert into 工资 values(1011,2013-12,2500,500,25,2,15,4,加急,50,50,100,3500,3400)insert into 工资 values(1012,2013-12,2500,500,25,2,15,4,加急,50,50,100,3500,3400)insert into 员工登陆密码 values(1001,1101)insert into 员工登陆密码 values(1002,1102)insert into 员工登陆密码 values(1003,1103)insert into 员工登陆密码 values(1004,1104)insert into 员工登陆密码 values(1005,1105)insert into 员工登陆密码 values(1006,1106)insert into 员工登陆密码 values(1007,1107)insert into 员工登陆密码 values(1008,1108)insert into 员工登陆密码 values(1009,1109)insert into 员工登陆密码 values(1010,1110)insert into 员工登陆密码 values(1011,1111)insert into 员工登陆密码 values(1012,1112)insert into 工龄工资 values(1,3000)insert into 工龄工资 values(2,4000)insert into 工龄工资 values(3,5000)insert into 工龄工资 values(4,6000)insert into 工龄工资 values(5,7000)insert into 工龄工资 values(6,8000)insert into 工龄工资 values(7,10000)insert into 工龄工资 values(8,12000)insert into 工龄工资 values(9,15000)insert into 工龄工资 values(10,20000)insert into 工龄工资 values(11,40000)（三）视图(1)查询员工工资情况建立一个视图，反映员工姓名及工资情况。CREATE VIEW 员工_工资 AS SELECT 员工.姓名, 工资.* FROM 员工,工资 WHERE 员工.工号=工资.工号（四）存储过程(1)指定员工工资情况的查询定义一个名为stsffCost的存储过程，该存储过程以以工资年月(staffgongzi)、出勤天数(staffchuqin)、加班工资(staffjiaban)、应发工资(staffying)、实发工资(staffshi)为输入参数返回指定员工工资信息的存储过程。stsffCost代码如下：CREATE PROCEDURE staffCoststaffgongzi decimal,staffchuqin decimal,staffjiaban decimal,staffying decimal,staffshi decimalASSELECT *FROM 工资WHERE 工资年月=staffgongzi AND 出勤天数=staffchuqin AND 加班工资=staffjiaban AND应发工资=staffying AND实发工资=staffshiGO(2）指定范围的员工的信息查询。以员工工号、部门编号为输入参数返回指定员工基本信息的存储过程，要求输入部分员工信息查询员工全部信息。定义一个名为stsffInfo的存储过程，该存储过程以员工工号(staffNum)、部门编号(bmNum)作为输入参数，存储过程返回查询到的结果集。satffInfo的定义代码如下：CREATE PROCEDURE staffInfostaffNum VARCHAR(50)=%,bmNum VARCHAR(50)=%ASSELECT *FROM 员工,部门WHERE 员工.部门名称=部门.部门名称 AND (员工.工号 LIKE staffNum VARCHAR(50)+%) AND (部门编号 LIKE bmNum VARCHAR(50)+%)GO （3）统计指定年份各部门每个月份的工资总额。以年份为输入参数，以总工资金额为输出参数，产生该年度12个月份的每部门工资统计的存储过程。定义该存储过程名为bmMonhSum，输入参数为ThisYear，实现代码如下：CREATE PROCEDURE bmMonthSumThisYear CHAR(10)ASSELECT DATEPART(MM,工资年月) AS 员工工资月份,SUM(实发工资) AS 部门人数FROM 员工,工资,部门WHERE 员工.工号=工资.工号 AND 员工.部门名称=部门.部门名称 AND DATEPART(YYYY,工资年月)=ThisYearGROUP BY DATEPART(MM,工资年月)GO（五）触发器（1）关键操作，在工资表里添加一条新的记录，实发工资会自动更新新的数据。定义这个触发器名称为insert_工资。代码如下：create trigger insert_工资 on 工资 for insertasdeclare i char(20)declare bb intset i=casewhen bb=0001 then 实发工资when bb=0002 then 实发工资endprint i (2)员工新调入进行员工新调入操作是在员工信息表中添加一条新的记录，可以为员工信息表设计一个Insert触发器，当员工信息表执行Insert操作后自动更改出部门信息表相应记录的数据。定义这个触发器名称为staff_insert。其代码如下：CREATE TRIGGER staff_insert ON 员工FOR INSERTAS IF (SELECT COUNT(*) FROM 部门,inserted,