createtable.txt

信息管理系统

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USER_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[USER_TABLE];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DRIVER_DATA]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DRIVER_DATA];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VEHICLE_DATA]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[VEHICLE_DATA];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WORK_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[WORK_INFO];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REPAIR_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[REPAIR_INFO];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ACCIDENT_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ACCIDENT_INFO];
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WAGE_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[WAGE_INFO];


CREATE TABLE [dbo].[USER_TABLE] (
	[USER_NAME] [nvarchar] (30) not NULL ,
	[USER_PWD] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [POWER_LEVEL] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];

CREATE TABLE [dbo].[DRIVER_DATA](
        [DRIVER_ID] [nvarchar] (15) NOT NULL,
        [ARCHIVES_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [WORKER_NAME] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [SEX] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
        [BIRTHDAY] [datetime] NULL,
        [NATIVE_PLACE] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [ID_CARD] [nvarchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
        [DRIVER_CARD_TYPE] [nvarchar] (1) COLLATE Chinese_PRC_CI_AS NULL ,
        [DRIVER_CARD_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [WORK_TIME] [datetime] NULL,
        [LAST_TIME] [datetime] NULL,
        [LINK_PHONE] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [ADDRESS] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [PHOTO] [image] NULL ,
        [REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 

)ON [PRIMARY];
CREATE TABLE [dbo].[VEHICLE_DATA](
        [VEHICLE_NO] [nvarchar] (15)   NOT  NULL ,
        [VEHICLE_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [VEHICLE_TYPE] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
        [POWER] [int]    NULL ,
        [BUYDAY] [datetime] NULL,
        [USEDAY] [datetime] NULL,
        [DEADDAY] [datetime] NULL,
        [WORK_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [ARCHIVES_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 

)ON [PRIMARY];

CREATE TABLE [dbo].[REPAIR_INFO](
        [VEHICLE_NO]	[nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
        [ARCHIVES_ID]	[nvarchar] (15)NOT  NULL ,
        [VEHICLE_ID]	[nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [DRIVER_ID]	[nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [REPAIR_TYPE]	[nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
        [REPAIR_FACTORY]	[nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [PART]	[nvarchar] (30)COLLATE Chinese_PRC_CI_AS NULL ,
        [REPAIR_CONTENT]	[nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
        [ENTRY_TIME]	[datetime]  NULL,
        [EXIT_TIME]	[datetime]   NULL,
       


)ON [PRIMARY];

CREATE TABLE [ACCIDENT_INFO](
        [VEHICLE_NO] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
        [ARCHIVES_ID] [nvarchar](15) NOT  NULL ,
        [VEHICLE_ID] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
        [DRIVER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
        [ACCIDENT_TYPE] [nvarchar] (2)  COLLATE Chinese_PRC_CI_AS NULL ,
        [ACCIDENT_DAY]  [datetime]  NULL,
        [ACCIDENT_CONTENT] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS  NULL ,
        [MONEY] [float]  NULL,
        [BAOXIAN] [nvarchar] (30)  COLLATE Chinese_PRC_CI_AS NULL ,
        [REMARK] [nvarchar] (255)  COLLATE Chinese_PRC_CI_AS NULL 

)ON [PRIMARY];

CREATE TABLE [WORK_INFO](
[PAICHE_ID] [nvarchar] (30)  NOT NULL ,
[DATETIME] [datetime] NOT NULL ,
[USER_COMPANY] [nvarchar] (50)  NOT NULL ,
[USER] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
[PURPOSE] [nvarchar] (30)  COLLATE Chinese_PRC_CI_AS NULL ,
[PLACE_FROM] [nvarchar] (50)  COLLATE Chinese_PRC_CI_AS NULL ,
[PLACE_TO]  [nvarchar] (50)  COLLATE Chinese_PRC_CI_AS NULL ,
[DAY_BOOKED] [datetime]  NULL ,
[VEHICLE_NO] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
[VEHICLE_TYPE] [nvarchar] (2)  COLLATE Chinese_PRC_CI_AS NULL ,
[DRIVER_ID] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
[TIME_FROM] [datetime] NULL ,
[TIME_TO] [datetime] NULL ,
[NUM_FROM] [float] NULL,
[NUM_TO] [float] NULL,
[NUM] [float] NULL,
[OPERTATOR] [nvarchar] (15)  COLLATE Chinese_PRC_CI_AS NULL ,
[REMARK] [nvarchar](255)  COLLATE Chinese_PRC_CI_AS NULL 


)ON [PRIMARY];

CREATE TABLE [dbo].[WAGE_INFO] (
	[RECORD_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[WORKER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[BASE_WAGE] [float] NULL ,
    [ALLOWANCE1] [float] NULL ,
    [ALLOWANCE2] [float] NULL ,
    [REWARD] [float] NULL ,
    [OVERTIME_MONEY] [float] NULL ,
    [DEDUCT_CHECK] [float] NULL ,
    [AGED_MONEY] [float] NULL ,
    [MEDICAL_MONEY] [float] NULL ,
    [COMPO_MONEY] [float] NULL ,
    [UNEMPLOY_MONEY] [float] NULL ,
    [HOUSING_MONEY] [float] NULL ,
    [INCOME_TAX] [float] NULL ,
    [REAL_WAGE] [float] NULL ,
    [GIVE_DATE] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL        
) ON [PRIMARY];


INSERT INTO  USER_TABLE(USER_NAME,USER_PWD,POWER_LEVEL) VALUES 
('system','system','系统管理员');