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