SQL server 工资管理系统数据库及其文档

use 工资管理系统 go CREATE TABLE [dbo].[惩罚]( [ID] [int] IDENTITY(1,1) NOT NULL, [条] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL, [款] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL, [员工编号] [varchar](10) COLLATE Chinese_PRC_CI_AS primary key not NULL, [姓名] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [部门] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [岗位] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [性别] [varchar](50) COLLATE Chinese_PRC_CI_AS check(性别='男'or 性别='女') NULL, [出生日期] [datetime] NULL, [惩罚时间] [datetime] NULL, [惩罚项目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [说明] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL, [罚款金额] [money] NULL ) ON [PRIMARY] go CREATE TABLE [dbo].[工资信息]( [员工编号] [nvarchar](10) COLLATE Chinese_PRC_CI_AS primary key NOT NULL, [员工姓名] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL, [性别] [varchar](50) COLLATE Chinese_PRC_CI_AS check(性别='男'or 性别='女') NULL, [基本工资] [money] not NULL, [岗位工资] [money] NULL, [住房补贴] [money] NULL, [津贴] [money] NULL, [工会会费] [money] NULL, [水电费] [money] NULL, [住房公积金] [money] NULL, [养老保险] [money] NULL, [罚款] [money] null, [奖励] [money] null, [总计] [money] null ) ON [PRIMARY] go CREATE TABLE [dbo].[奖励]( [ID] [int] IDENTITY(1,1) NOT NULL, [条] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL, [款] [varchar](30) COLLATE Chinese_PRC_CI_AS NULL, [员工编号] [varchar](10) COLLATE Chinese_PRC_CI_AS primary key not NULL, [姓名] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [部门] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [岗位] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [性别] [varchar](10) COLLATE Chinese_PRC_CI_AS check(性别='男'or 性别='女') NULL, [出生日期] [datetime] NULL, [奖励项目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [具体内容] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL, [奖励时间] [datetime] NULL, [说明] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL, [奖励款额] [money] NULL ) ON [PRIMARY] go CREATE TABLE [dbo].[员工表]( [员工编号] [nvarchar](10) COLLATE Chinese_PRC_CI_AS primary key NOT NULL, [员工姓名] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL, [岗位] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [口令] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL, [一级部门] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [二级部门] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] go CREATE TABLE [dbo].[员工基本信息]( [员工编号] [nvarchar](10) COLLATE Chinese_PRC_CI_AS primary key NOT NULL, [员工姓名] [nvarchar](30) COLLATE Chinese_PRC_CI_AS unique NULL, [性别] [nvarchar](2) COLLATE Chinese_PRC_CI_AS check(性别='男'or 性别='女') NULL, [出生日期] [smalldatetime] NULL, [民族] [nvarchar](30) default'汉' COLLATE Chinese_PRC_CI_AS NULL, [学历] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [进入公司时间] [smalldatetime] NULL, [转正时间] [smalldatetime] NULL, [部门] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [岗位] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [血型] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL, [身高] [real] NULL, [体重] [real] NULL, [籍贯] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [户口所在地] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [身份证号] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL, [所学专业] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [毕业学校] [nvarchar](60) COLLATE Chinese_PRC_CI_AS NULL, [家庭通讯地址] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [所在街道办事处] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL, [邮编] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL, [电话] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [传呼机] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL, [移动电话] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NULL, [特长] [nvarchar](39) COLLATE Chinese_PRC_CI_AS NULL, [健康情况] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, ) ON [PRIMARY] --插入信息 go use 工资管理系统 go --奖励 insert into 奖励 values ('','','0801102101','aa','asf','sdf','男','','jf','ert','','dfd','100') go insert into 奖励 values ('','','0801102102','bb','asdf','dkff','男','','df','dfk','','kf','100') go --惩罚 use 工资管理系统 go insert into 惩罚 values ('d','d','0801102103','dk','ds','as','女','','','扼杀','sd','100') go insert into 惩罚 values ('tr','4er','0801102105','er','re','re','女','','','色的','se','200') go insert into 惩罚 values ('r','w','0801102109','s','se','we','男','','','谔谔','ee','50') go --工资信息 insert into 工资信息 values ('0801102101','aa','男','2000','32','43','123','23','23','13','23','','100','2403') go insert into 工资信息 values ('0801102102','bb','男','2050','34','134','234','45','36','12','75','','100','2680') go insert into 工资信息 values ('0801102103','dk','女','2069','45','343','234','56','65','76','378','100','','3166') go insert into 工资信息 values ('0801102104','we','女','2023','65','36','54','78','89','78','234','','','2657') go insert into 工资信息 values ('0801102105','er','女','2067','34','54','65','56','567','77','67','','200','2787') go insert into 工资信息 values ('0801102106','fg','女','2067','345','34','34','34','34','35','53','','','2636') go insert into 工资信息 values ('0801102107','fte','女','2127','345','54','456','45','56','76','346','','','3505') go insert into 工资信息 values ('0801102108','ter','女','2321','42','24','45','56','56','55','35','','','2634') go insert into 工资信息 values ('0801102109','s','女','3433','54','45','567','8','97','99','86','','50','4229') go insert into 工资信息 values ('0801102110','ss','女','3434','34','35','656','75','67','67','76','','','4444') go --员工表 insert into 员工表 values ('0801102101','aa','sdf','rte','er','4rt') go insert into 员工表 values ('0801102102','bb','dkff','er','rty','5rt') go insert into 员工表 values ('0801102103','dk','as','dfg','ert','6rt') go insert into 员工表 values ('0801102104','we','re','dsv','rt3','2rt') go insert into 员工表 values ('0801102105','er','re','wrer','erf','2rt') go insert into 员工表 values ('0801102106','fg','ssdil','wer','whido','2rt') go insert into 员工表 values ('0801102107','fte','rjk','hj','njkh','3rt') go insert into 员工表 values ('0801102108','ter','hbkl','njl','j','4rt') go insert into 员工表 values ('0801102109','s','rtfd','fgh','gf','5rt') go insert into 员工表 values ('0801102110','ss','gh','gfh','jg','6rt') go --员工基本信息表 insert into 员工基本信息 values ('0801102101','aa','男','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102102','bb','男','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102103','dk','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102104','we','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102105','er','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102106','fg','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102107','fte','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102108','ter','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102109','s','女','','','','','','','','','','','','','','','','','','','','','','','') go insert into 员工基本信息 values ('0801102110','ss','女','','','','','','','','','','','','','','','','','','','','','','','') go --视图 --奖励 create view v_najiangli1 as select 工资信息.员工编号,工资信息.员工姓