dbcarsys1.sql

汽车销售管理系统,进销存系统典范,值得初学者多多学习

/*-----------------------------------------------------------*/
/*			创建数据库			     */
/*-----------------------------------------------------------*/
use master
go
if exists(select * from sysdatabases where name='dbCarsys')
	drop database dbCarsys
go
create database dbCarsys
go
use dbCarsys
go

/*-----------------------------------------------------------*/
/*			客户信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblClient')
	drop table tblClient
go
create table tblClient
(
	cliCode		Varchar(10) primary key,
	cliName		Varchar(10) not null,
	cliSex		Bit not null,
	cliPid		Varchar(18) not null,
	cliBirthday	Datetime,
	cliIncName	Varchar(50),
	cliIncCharacter	Varchar(20),
	cliIncCalling	Varchar(20),
	cliHeadship	Varchar(20),
	cliTel		Varchar(13) not null,
	cliPoss		Varchar(6) not null,
	cliAdd		Varchar(100) not null
)
go

/*-----------------------------------------------------------*/
/*			代办项目表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCommissionItem')
	drop table tblCommissionItem
go
create table tblCommissionItem
(
	comCode		Varchar(10) primary key,
	comName		Varchar(20) not null,
	comMoney	Money not null,
	comRemark	varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			厂商信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblFactory')
	drop table tblFactory
go
create table tblFactory
(
	facCode		Varchar(10) primary key,           --厂商编号
	facName		Varchar(100) not null,             --厂商名称
	facLinkMan	Varchar(15) not null,              --联系人
	facTel		Varchar(13) not null,              --联系电话
	facAdd		Varchar(50) not null,              --地址
	facPos		Varchar(6),                        --邮编
	facState	bit,                               --状态（0代表暂停，1代表未暂停）
	facRemark	Varchar(500)                       --备注
)
go

/*-----------------------------------------------------------*/
/*			车辆信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCarType')
	drop table tblCarType
go
create table tblCarType
(
	ctCode		Varchar(20) primary key,            --车型代码
	ctName		Varchar(20) not null,               --车辆名称
	ctType		Varchar(30) not null,               --车辆类型
	ctPlace		Varchar(100) not null,              --车辆产地
	ctCarFac	Varchar(100) not null,              --厂牌名称
	facCode		Varchar(10) foreign key references tblFactory(facCode),--厂商编号
	ctRePrice	Money not null,                     --采购参考价
	ctSellPrice	Money not null                      --销售参考价
)
go
select *from tblCarType
/*-----------------------------------------------------------*/
/*			仓库信息表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblDepotInfo')
	drop table tblDepotInfo
go
create table tblDepotInfo
(
	diCode		Varchar(10) primary key,
	diName		Varchar(10) UNIQUE not null,
	diUser		Varchar(10) not null,
	diAdd		varchar(100) not null,
	diTel		Varchar(13) not null
)
go

/*-----------------------------------------------------------*/
/*			员工表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblUser')
	drop table tblUser
go
create table tblUser
(
	uCode		Varchar(10) primary key,
	uPwd		Varchar(10) not null,
	uName		Varchar(10) not null,
	uPinYin		Varchar(50) not null,
	uSex		Bit not null,
	uPid		Varchar(18) not null,
	uBranch		Varchar(20) not null,
	uJob		Varchar(20) not null,
	uIncumbency 	datetime not null,
	uDimission 	datetime,
	uState		Tinyint not null,
	uTel		Varchar(13) not null,
	uAdd		Varchar(100)
)
go

/*-----------------------------------------------------------*/
/*			权限规则表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblAccess')
	drop table tblAccess
go
create table tblAccess
(
	uCode		Varchar(10) foreign key references tblUser(uCode),
	formName	Varchar(20),
	controlName	Varchar(20) constraint pk_tblAccess_uCode_formName_controlName primary key(ucode,formName,controlName),
	aaction		Bit,
	accInfo		varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			系统初始表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblOsInit')
	drop table tblOsInit
go
create table tblOsInit
(
	osiCode 	varchar(10) primary key,
	osiName		varchar(100) not null,
	osiAdd		varchar(100) not null,
	osiTel		varchar(100) not null,
	osiWeb		varchar(100) not null,
	osiInDepotCode	varchar(10) not null,
	osiMoveCode	varchar(10) not null,
	osiSellCode	varchar(10) not null,
	osiGatheringCode	varchar(10) not null,
	osiCommissionCode	varchar(10) not null,
	osiPayCode	varchar(10) not null,
	osiPreCode	varchar(10) not null,
	osiStoCode	varchar(10) not null
)
go

/*-----------------------------------------------------------*/
/*			采购单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblStock')
	drop table tblStock
go

create table tblStock
(
	stoCode		varchar(10) primary key,                                            --采购编号
	stoDate		datetime not null,					            --采购日期						
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,     --车型代码
	stoState	Tinyint default 0,                                                  --是否审核
	stoColor	varchar(20) not null,                                               --车辆颜色
	stoAdvance	Money default 0,                                                    --预付款
	stoPrice	Money not null,                                                     --车辆价格
	sotRemark	varchar(500),                                                       --备注
	optCode		varchar(10) foreign key references tblUser(uCode) not null,         --采购人员编
	checkCode	varchar(10) foreign key references tblUser(uCode) null,             --审核人编号
	stoIdea		varchar(200)                                                        --审核意见
)
go
select *from tblStock
/*--------------------7.29修改采购单表------------------------*/
/*--------------------修改内容为审核人员编号和审核人意见可以为空*/


/*-----------------------------------------------------------*/
/*			入库单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblInDepot')
	drop table tblInDepot
go
create table tblInDepot
(
	indCode		Varchar(10) primary key,
	indDate		Varchar(20) not null,
	diCode		Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	stoCode		Varchar(10) foreign key references tblStock(stoCode) not null,
	indColor	varchar(10) not null,
	indButtom	Varchar(20) not null,
	indEngine	Varchar(20) not null,
	indEli		Varchar(20) not null,
	indImport	Varchar(20) not null,
	indVerify	Varchar(20) not null,
	indSelf		Varchar(20) not null,
	indKey		Varchar(20) not null,
	indKilm		Varchar(20) not null,
	indOutDate	Varchar(20) not null,
	indPrice	money	    not null,
	indTake		Varchar(20) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode),
	indState	Tinyint     not null,
	indOutStore	datetime,
	indOutMan	varchar(10) foreign key references tblUser(uCode),
	indInfo		varchar(100),
	indRemark	varchar(100),
)
go

/*-----------------------------------------------------------*/