?? 人力資源管理實例程序.sql
字號:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_工資發(fā)放歷史表_職員基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[工資發(fā)放歷史表] DROP CONSTRAINT FK_工資發(fā)放歷史表_職員基本信息表
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_月工資統(tǒng)計表_職員基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[月工資統(tǒng)計表] DROP CONSTRAINT FK_月工資統(tǒng)計表_職員基本信息表
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_職員懲罰表_職員基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[職員懲罰表] DROP CONSTRAINT FK_職員懲罰表_職員基本信息表
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_職員獎勵表_職員基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[職員獎勵表] DROP CONSTRAINT FK_職員獎勵表_職員基本信息表
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用戶清單]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用戶清單]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[組織機構編碼表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[組織機構編碼表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[職員基本信息表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[職員基本信息表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[月工資統(tǒng)計表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[月工資統(tǒng)計表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[個人所得稅表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[個人所得稅表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[工資發(fā)放歷史表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[工資發(fā)放歷史表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[職員獎勵表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[職員獎勵表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[職員懲罰表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[職員懲罰表]
GO
CREATE TABLE [dbo].[用戶清單] (
[用戶編號] [char] (6) NOT NULL ,
[部門] [char] (20) NOT NULL ,
[姓名] [char] (10) NOT NULL ,
[性別] [char] (2) NOT NULL ,
[密碼] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[組織機構編碼表] (
[內部編號] [int] IDENTITY (1, 1) NOT NULL ,
[類別] [varchar] (100) NULL ,
[AbsIndex] [int] NULL ,
[ItemIndex] [int] NULL ,
[ItemLevel] [int] NULL ,
[ParentIndex] [int] NULL ,
[類別號] [char] (10) NULL ,
[單位編號] [varchar] (20) NOT NULL PRIMARY KEY,
[單位名稱] [varchar] (100) NULL ,
[拼音編碼] [varchar] (50) NULL ,
[單位地址] [varchar] (100) NULL ,
[單位電話號碼] [varchar] (50) NULL ,
[開戶銀行] [varchar] (100) NULL ,
[帳號] [varchar] (50) NULL ,
[開戶全稱] [varchar] (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[職員基本信息表] (
[內部編號] [int] IDENTITY (1, 1) NOT NULL ,
[職員編號] [varchar] (30) NOT NULL PRIMARY KEY,
[姓名] [varchar] (20) NOT NULL ,
[姓名簡碼] [varchar] (10) NULL ,
[性別] [varchar] (2) NULL ,
[出生日期] [int] NULL ,
[年齡] [int] NULL ,
[籍貫] [varchar] (50) NULL ,
[民族] [varchar] (20) NULL ,
[文化程度] [char] (50) NULL ,
[畢業(yè)學校] [char] (100) NULL ,
[健康狀況] [varchar] (50) NULL ,
[婚姻狀況] [varchar] (10) NULL ,
[身份證號碼] [varchar] (18) NULL ,
[家庭電話] [varchar] (50) NULL ,
[辦公電話] [varchar] (50) NULL ,
[手機] [varchar] (50) NULL ,
[電子郵件地址] [varchar] (50) NULL ,
[職工賬號] [varchar] (20) NULL ,
[單位編號] [varchar] (20) NULL,
[備注] [varchar] (100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[月工資統(tǒng)計表] (
[日期] [int] NULL ,
[職員編號] [varchar] (30) Not NULL PRIMARY KEY,
[基本工資] [float] NULL DEFAULT (0) ,
[浮動工資] [decimal](26, 2) NULL DEFAULT (0) ,
[合同補] [decimal](26, 2) NULL DEFAULT (0) ,
[糧副補] [decimal](26, 2) NULL DEFAULT (0) ,
[房補] [decimal](26, 2) NULL DEFAULT (0) ,
[臨時補] [decimal](26, 2) NULL DEFAULT (0) ,
[職務工資] [decimal](26, 2) NULL DEFAULT (0) ,
[工齡工資] [decimal](26, 2) NULL DEFAULT (0) ,
[考核工資] [decimal](26, 2) NULL DEFAULT (0) ,
[獎金] [decimal](26, 2) NULL DEFAULT (0) ,
[應發(fā)金額合計] [decimal](26, 2) NULL DEFAULT (0) ,
[房租] [decimal](26, 2) NULL DEFAULT (0) ,
[水電費] [decimal](26, 2) NULL DEFAULT (0) ,
[請假扣除] [decimal](26, 2) NULL DEFAULT (0) ,
[考勤扣除] [decimal](26, 2) NULL DEFAULT (0) ,
[罰款] [decimal](26, 2) NULL DEFAULT (0) ,
[住房公積金] [decimal](26, 2) NULL DEFAULT (0) ,
[醫(yī)療保險] [decimal](26, 2) NULL DEFAULT (0) ,
[養(yǎng)老保險] [decimal](26, 2) NULL DEFAULT (0) ,
[失業(yè)保險] [decimal](26, 2) NULL DEFAULT (0) ,
[生育保險] [decimal](26, 2) NULL DEFAULT (0) ,
[工傷保險] [decimal](26, 2) NULL DEFAULT (0) ,
[應扣金額合計] [decimal](26, 2) NULL DEFAULT (0) ,
[工資合計] [decimal](26, 2) NULL DEFAULT (0) ,
[個人所得稅] [decimal](26, 2) NULL DEFAULT (0) ,
[實發(fā)金額] [decimal](26, 2) NULL DEFAULT (0) ,
[發(fā)放否] [char] (10) NULL ,
[月份] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[個人所得稅表] (
[編號] [int] NOT NULL PRIMARY KEY,
[級數(shù)] [char] (2) NOT NULL ,
[不計稅工資] [decimal](26, 2) NOT NULL DEFAULT (800),
[工資下限] [decimal](26, 2) NOT NULL DEFAULT (0),
[工資上限] [decimal](26, 2) NOT NULL DEFAULT (0),
[個人所得稅率] [decimal](26, 2) NOT NULL DEFAULT (0),
[速算扣除數(shù)] [decimal](26, 2) NOT NULL DEFAULT (0),
[備注] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[工資發(fā)放歷史表] (
[日期] [int] NULL ,
[職員編號] [varchar] (30) NULL ,
[基本工資] [float] NULL ,
[浮動工資] [decimal](26, 2) NULL ,
[合同補] [decimal](26, 2) NULL ,
[糧副補] [decimal](26, 2) NULL ,
[房補] [decimal](26, 2) NULL ,
[臨時補] [decimal](26, 2) NULL ,
[職務工資] [decimal](26, 2) NULL ,
[工齡工資] [decimal](26, 2) NULL ,
[考核工資] [decimal](26, 2) NULL ,
[獎金] [decimal](26, 2) NULL ,
[應發(fā)金額合計] [decimal](26, 2) NULL ,
[房租] [decimal](26, 2) NULL ,
[水電費] [decimal](26, 2) NULL ,
[請假扣除] [decimal](26, 2) NULL ,
[考勤扣除] [decimal](26, 2) NULL ,
[罰款] [decimal](26, 2) NULL ,
[住房公積金] [decimal](26, 2) NULL ,
[醫(yī)療保險] [decimal](26, 2) NULL ,
[養(yǎng)老保險] [decimal](26, 2) NULL ,
[失業(yè)保險] [decimal](26, 2) NULL ,
[生育保險] [decimal](26, 2) NULL ,
[工傷保險] [decimal](26, 2) NULL ,
[應扣金額合計] [decimal](26, 2) NULL ,
[工資合計] [decimal](26, 2) NULL ,
[個人所得稅] [decimal](26, 2) NULL ,
[實發(fā)金額] [decimal](26, 2) NULL ,
[發(fā)放否] [char] (10) NULL ,
[月份] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[職員獎勵表] (
[序號] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[職員編號] [varchar] (30) NOT NULL ,
[獎勵類型] [varchar] (20) NULL ,
[獎勵金額] [float] NULL DEFAULT (0),
[是否計入工資] [char] (10) NULL DEFAULT ('否'),
[獎勵原因] [varchar] (50) NULL ,
[部門意見] [varchar] (100) NULL ,
[獎勵日期] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[職員懲罰表] (
[序號] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[職員編號] [varchar] (30) NOT NULL ,
[懲罰類型] [varchar] (20) NULL ,
[懲罰金額] [float] NULL DEFAULT (0) ,
[是否計入工資] [char] (10) NULL DEFAULT ('否'),
[懲罰原因] [varchar] (50) NULL ,
[部門意見] [varchar] (100) NULL ,
[懲罰日期] [int] NULL
) ON [PRIMARY]
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -