【SQL开源代码栏目提醒】:网学会员为需要SQL开源代码的朋友们搜集整理了first.sql相关资料,希望对各位网友有所帮助!
-- =============================================
-- Basic Create Database Template
-- =============================================
IF EXISTS (SELECT *
FROM sysdatabases
WHERE name = 'wage')
DROP DATABASE wage
GO
CREATE DATABASE wage
GO
use wage
go
if exists (select 1
from sysobjects
where id = object_id('departwage')
and type = 'V')
drop view departwage
go
if exists (select 1
from sysobjects
where id = object_id('staffwage')
and type = 'V')
drop view staffwage
go
if exists (select 1
from sysobjects
where id = object_id('Vwagepay')
and type = 'V')
drop view Vwagepay
go
if exists (select 1
from sysobjects
where id = object_id('absence')
and type = 'U')
drop table absence
go
if exists (select 1
from sysobjects
where id = object_id('depart')
and type = 'U')
drop table depart
go
if exists (select 1
from sysobjects
where id = object_id('leave')
and type = 'U')
drop table leave
go
if exists (select 1
from sysobjects
where id = object_id('overwork')
and type = 'U')
drop table overwork
go
if exists (select 1
from sysobjects
where id = object_id('staff')
and type = 'U')
drop table staff
go
if exists (select 1
from sysobjects
where id = object_id('transfer')
and type = 'U')
drop table transfer
go
if exists (select 1
from sysobjects
where id = object_id('type')
and type = 'U')
drop table type
go
if exists (select 1
from sysobjects
where id = object_id('user_load')
and type = 'U')
drop table user_load
go
if exists (select 1
from sysobjects
where id = object_id('wage')
and type = 'U')
drop table wage
go
if exists (select 1
from sysobjects
where id = object_id('wagepay')
and type = 'U')
drop table wagepay
go
if exists (select 1
from sysobjects
where id = object_id('welfare')
and type = 'U')
drop table welfare
go
if exists (select 1
from sysobjects
where id = object_id('workrate')
and type = 'U')
drop table workrate
go
/*==============================================================*/
/* Table : absence */
/*==============================================================*/
create table absence (
absence_id int not null,
staff_id char(10) null,
type_name char(10) null,
absence_time float null,
constraint PK_ABSENCE primary key (absence_id)
)
go
/*==============================================================*/
/* Table : depart */
/*==============================================================*/
create table depart (
depart_id char(4) not null,
depart_name char(20) null,
constraint PK_DEPART primary key (depart_id)
)
go
/*==============================================================*/
/* Table : leave */
/*==============================================================*/
create table leave (
leave_id int not null,
staff_id char(10) null,
staff_name char(15) null,
leave_sdate datetime null,
leave_edate datetime null,
leave_days float null,
type_name char(10) null,
leave_memory text null,
constraint PK_LEAVE primary key (leave_id)
)
go
/*==============================================================*/
/* Table : overwork */
/*==============================================================*/
create table overwork (
overwork_id int not null,
staff_id char(10) null,
overwork_date datetime null,
overwork_day float null,
workrate_id int null,
overwork_wage money null,
constraint PK_OVERWORK primary key (overwork_id)
)
go
/*==============================================================*/
/* Table : staff */
/*==============================================================*/
create table staff (
staff_id char(10) not null,
staff_name char(15) null,
staff_depid char(4) null,
staff_sex char(2) null,
staff_birthday datetime null,
staff_addr char(30) null,
staff_tel varchar(20) null,
staff_birthplace char(10) null,
staff_degree char(20) null,
staff_major char(20) null,
staff_incomptime datetime null,
staff_bwage money null,
staff_position char(15) null,
staff_idcard char(18) null,
constraint PK_STAFF primary key (staff_id)
)
go
/*==============================================================*/
/* Table : transfer */
/*==============================================================*/
create table transfer (
transfer_id int not null,
staff_name char(10) null,
depart_name char(10) null,
newdepart_name char(10) null,
transfer_time datetime null,
transfer_reason char(30) null,
constraint PK_TRANSFER primary key (transfer_id)
)
go
/*==================================