⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 dbcarsys.sql

📁 汽车销售管理系统,进销存系统典范,值得初学者多多学习
💻 SQL
📖 第 1 页 / 共 3 页
字号:
🔍
123 
/*-----------------------------------------------------------*/
/*			代办表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCommission')
	drop table tblCommission
go
create table tblCommission
(
	CommissionCode	Varchar(10) primary key,
	CommissionDate	Datetime default getDate() not null,
	selCode		Varchar(10) foreign key references tblSell(selCode) not null,
	comCode		Varchar(10) foreign key references tblCommissionItem(comCode) not null,
	comMoney	Money not null,
)
go

/*-----------------------------------------------------------*/
/*			回访规则表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblVisitInfo')
	drop table tblVisitInfo
go
create table tblVisitInfo
(
	viCode		Varchar(10) primary key,
	viName		Varchar(50) not null,
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,
	viDay		int not null,
)
go

/*-----------------------------------------------------------*/
/*			客户回访表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblVisit')
	drop table tblVisit
go
create table tblVisit
(
	vCode		Varchar(10) primary key,
	vDate		Datetime not null,
	vPDate		datetime,
	vFashion	Varchar(10),
	viCode		Varchar(10) foreign key references tblVisitInfo(viCode) not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	vNote		Varchar(500),
	optCode		Varchar(10) foreign key references tblUser(uCode),
	checkCode	Varchar(10) foreign key references tblUser(uCode),
	vState		Tinyint default 0 not null,
	vIdea		Varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			客户跟踪表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblTail')
	drop table tblTail
go
create table tblTail
(
	taiCode		Varchar(10) primary key,
	taiDate		Datetime default getDate() not null,
	taiFashion	Varchar(20) not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,
	taiNote		Varchar(500) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	taiState	Tinyint default 0 not null,
	taiIdea		Varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			应付款表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblShouldPayment')
	drop table tblShouldPayment
go
create table tblShouldPayment
(
	spCode		Varchar(10) primary key,
	indCode		Varchar(10) foreign key references tblInDepot(indCode) not null,
	spPMoney	Money default 0,
	spMoney		Money not null,
	spState		bit default 0 not null,
	spRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			付款单明细表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblPayment')
	drop table tblPayment
go
create table tblPayment
(
	payBillCode	Varchar(10) primary key,
	payCode		Varchar(10) foreign key references tblShouldPayment(spCode) not null,
	payDate		Datetime not null,
	paySummary	Varchar(200),
	payMoney	Money default 0,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	payState	bit default 0,
	payRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			应收款单表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblShouldGathering')
	drop table tblShouldGathering
go
create table tblShouldGathering
(
	sgCode		Varchar(10) primary key,
	sgMoney		Money not null,
	selCode		Varchar(10) foreign key references tblSell(selCode) not null,
	sgState		bit default 0,
	sgRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			收款单明细表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblGathering')
	drop table tblGathering
go
create table tblGathering
(
	gatCode		Varchar(10) primary key,
	sgCode		Varchar(10) foreign key references tblShouldGathering(sgCode) not null,
	gatDate		Datetime not null,
	gatMoney	Money default 0,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	gatState	bit default 0,
	gatRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			添加测试数据			     */
/*	    (完整的流程数据测试,除了权限表与财务模块)	     */
/*-----------------------------------------------------------*/
--//用户信息(1位管理员，2位职员)
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1000','123456','null','null',1,'null','null','null','2006-01-01',1,'null')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1001','123456','管理员','xiaobai',1,'350203198210114020','安全部','管理员','2006-01-01',1,'0592-5581188')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1002','123456','小黑','xiaohei',1,'350203198210104020','信息部','操作员','2006-06-01',1,'0592-5582288')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1003','123456','小兰','xiaolan',0,'350203198212014020','信息部','操作员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1004','123456','老大','laoda',0,'350203198212033333','采购部','采购经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1005','123456','老二','laoer',0,'350203198212034444','采购部','经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1006','123456','小王','laoer',0,'350203198212034444','采购部','采购员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1007','123456','小张','laoer',0,'350203198212034444','采购部','采购员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1008','123456','小张','laoer',0,'350203198212034444','采购部','仓库管理员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1009','123456','预售员','laoer',0,'350203198212034444','预售部','预售员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp10010','123456','预售经理','laoer',0,'350203198212034444','预售部','预售经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp10011','123456','销售员','laoer',0,'350203198212034444','销售部','销售员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp10012','123456','销售经理','laoer',0,'350203198212034444','销售部','销售经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp10013','123456','客户员','laoer',0,'350203198212034444','客户部','客户员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp10014','123456','客户经理','laoer',0,'350203198212034444','客户部','客户经理','2006-07-01',1,'0592-5583388')

select * from tblUser
go
--//权限信息(注：项目最后在处理)

--//客户信息(3个客户)
insert into tblClient values('KH100000','null',0,'000000000000000000','1980-01-01','null','null','null','null','0592-1111111','null','null')
insert into tblClient values('KH100001','张三',1,'350203198010104020','1980-01-01','厦门三五互联信息技术有限公司','私营','IT行业','业务经理','0592-1111111','361000','福建厦门')
insert into tblClient values('KH100002','李四',0,'350203198010104020','1980-10-10','厦门三五互联信息技术有限公司','私营','IT行业','业务经理','0592-2222222','361000','福建厦门')
insert into tblClient values('KH100003','王五',1,'350203198011114020','1980-11-11','厦门三五互联信息技术有限公司','私营','IT行业','公司职员','0592-3333333','361000','福建厦门')
insert into tblClient values('KH100004','张逢君',1,'350203198209154016','1982-09-15','北大青鸟厦门邦初培训中心','私营','教育机构','学员','13696906040','361000','福建厦门')
select * from tblClient
go
--//厂商信息(2家厂商)
insert into tblFactory values('CS1001','上海福特汽车公司','Mr.Ford','021-12345678','上海埔东新区','200000',1,'上海福特汽车公司')
insert into tblFactory values('CS1002','上海大众汽车公司','Mr.Csvw','021-11223344','上海埔东新区','200000',1,'上海大众汽车公司')
select * from tblFactory
go
--//车型信息(4部汽车资料)
insert into tblCarType values('MONEDO 2.0Chia','蒙迪欧','小轿车','上海福特','上海福特(四速自排)真皮','CS1001',179800,189800)
insert into tblCarType values('MONEDO 2.5 V6 GLX','蒙迪欧','小轿车','上海福特','上海福特(手排/自动)真皮','CS1001',180000,190000)
insert into tblCarType values('PASSAT 1.8T','帕萨特','小轿车','上海大众','上海帕萨特(手排/自动)真皮','CS1002',248000,255000)
insert into tblCarType values('PASSAT 2.0','帕萨特','小轿车','上海大众','上海帕萨特(手排)真皮','CS1002',350000,360000)
select * from tblCarType
go
--//仓库信息(2间仓库)
insert into tblDepotInfo values('CK1001','1号仓库','仓库员1','厦门湖里区','0592-5051111','否')
insert into tblDepotInfo values('CK1002','2号仓库','仓库员2','厦门思明区','0592-5052222','否')
select * from tblDepotInfo
go
--//代办项目(3个代办项)
insert into tblCommissionItem values('DBS1000','null',0,'null')
insert into tblCommissionItem values('DBS1001','上牌',500,'仅限厦门地区')
insert into tblCommissionItem values('DBS1002','VCD',800,'仅限厦门地区')
insert into tblCommissionItem values('DBS1003','车底灯',1000,'仅限厦门地区')
select * from tblCommissionItem
go
--//回访规则(根据目前现有汽车资料，每种汽车使用15天与60天的访问方式)
insert into tblVisitInfo values('HF1001','拜访','MONEDO 2.0Chia',15)
insert into tblVisitInfo values('HF1002','拜访','MONEDO 2.0Chia',60)
insert into tblVisitInfo values('HF1003','拜访','MONEDO 2.5 V6 GLX',15)
insert into tblVisitInfo values('HF1004','拜访','MONEDO 2.5 V6 GLX',60)
insert into tblVisitInfo values('HF1005','拜访','PASSAT 1.8T',15)
insert into tblVisitInfo values('HF1006','拜访','PASSAT 1.8T',60)
insert into tblVisitInfo values('HF1007','拜访','PASSAT 2.0',15)
insert into tblVisitInfo values('HF1008','拜访','PASSAT 2.0',60)
select * from tblVisitInfo
go
--//系统初始(所有单据编号使用中文拼音大写开头,例如'采购单'——CG开头；起始编号统一为100001,其它编号同理)
insert into tblOsInit values('SYS1001','厦门T77汽车销售公司','福建厦门','0592-8888888','www.xmt77.com','RK100001','YK100001','XS100001','SK100001','DB100001','FK100001','YS100001','CG100001')
select * from tblOsInit
go
--//采购信息(2部车未审核，2部车已经完成,采购时不产生付款单)
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100001','2006-07-01','MONEDO 2.0Chia',0,'蓝色',179800,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100002','2006-07-01','MONEDO 2.5 V6 GLX',0,'紫色',180000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100003','2006-07-01','PASSAT 1.8T',3,'银白色',248000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100004','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1002','emp1001')
select * from tblStock
go
--//入库信息(2部车分别在2个不同仓库中,并且2部车状态:已审核已通过,生成付款单)
insert into tblInDepot(indCode,indDate,diCode,stoCode,indColor,indButtom,indEngine,indEli,indImport,indVerify,indSelf,indKey,indKilm,indOutDate,indPrice,indTake,optCode,indState)
		values('RK100001','2006-07-05','CK1001','CG100003','银白色','DP123','FDJ123','HG123','JK123','SJ123','ZB123','YS123','100','2006-07-15',248000,'TD123','emp1002',1)
insert into tblInDepot(indCode,indDate,diCode,stoCode,indColor,indButtom,indEngine,indEli,indImport,indVerify,indSelf,indKey,indKilm,indOutDate,indPrice,indTake,optCode,indState)
		values('RK100002','2006-07-05','CK1002','CG100004','黑色','DP321','FDJ321','HG321','JK321','SJ321','ZB321','YS321','100','2006-07-15',350000,'TD321','emp1003',1)
select * from tblInDepot
go
--//移库信息(注：简单的业务不添加数据了)

insert into tblMove values('YK10001','2006-7-20','RK100001','CK1001','CK1002','emp1002','没办法');

insert into tblMove values('YK10002','2006-7-23','RK100002','CK1002','CK1001','emp1001','没出息');
go

--//预售信息(2个客户订购仓库内不同的车,并且2张预售单状态都是'已审核已通过',不产生收款单,只开出定金收据)
insert into tblPresell values('YD100000','2006-07-20','KH100000','PASSAT 1.8T','null',0,0,'emp1000','null',0,'emp1000','null')
insert into tblPresell values('YD100001','2006-07-20','KH100001','PASSAT 1.8T','银白色',10000,250000,'emp1002','已支付定金10000 RMB',2,'emp1001','无意见')
insert into tblPresell values('YD100002','2006-07-20','KH100002','PASSAT 2.0','黑色',10000,350000,'emp1003','已支付定金10000 RMB',2,'emp1001','无意见')
select * from tblPresell
go
--//销售信息(根据2个客户的预售单信息产生销售单,并且2张销售单状态都是'销售已完成',生成收款单,生成客户回访)
insert into tblSell values('XS100001','YD100001','2006-07-25','KH100001','RK100001','本地','家用',255000,'无','emp1002',3,'emp1001','无意见')
insert into tblSell values('XS100002','YD100002','2006-07-25','KH100002','RK100002','本地','商用',360000,'无','emp1002',3,'emp1001','无意见')
select * from tblSell
go
--//代办信息(根据销售信息中的2个客户分别代办不同的项目,并修改收款单中的应付款金额)
insert into tblCommission values('DB100001','2006-07-25','XS100001','DBS1001',500)
insert into tblCommission values('DB100002','2006-07-25','XS100002','DBS1002',800)
select * from tblCommission
go
--//客户跟踪(跟踪1个没有销售记录的客户,状态——属实)
insert into tblTail values('GZ100001','2006-07-20','电话跟踪','KH100003','MONEDO 2.0Chia','果然是好车啊！可惜没钱买','emp1002','emp1001',1,'无意见')
insert into tblTail values('GZ100002','2006-07-25','电话跟踪','KH100003','MONEDO 2.0Chia','明年打算买','emp1002','emp1001',1,'无意见')
insert into tblTail values('GZ100003','2006-07-25','电话跟踪','KH100004','MONEDO 2.0Chia','暂时没钱','emp1003','emp1001',1,'无意见')
select * from tblTail
go
--//客户回访(回访已购买车的2个客户,类型属于购车后15天回访,状态——属实)
insert into tblVisit values('HF100001','2006-08-10','2006-08-10','拜访','HF1005','KH100001','贵公司服务质量一流','emp1002','emp1001',1,'无意见')
insert into tblVisit(vCode,vDate,viCode,cliCode) values('HF100002','2006-10-10','HF1006','KH100001')
insert into tblVisit values('HF100003','2006-08-10','2006-08-10','拜访','HF1007','KH100002','贵公司服务质量一般','emp1002','emp1001',1,'无意见')
insert into tblVisit(vCode,vDate,viCode,cliCode) values('HF100004','2006-10-10','HF1008','KH100002')
select * from tblVisit
go
--//付款信息
insert into tblShouldPayment values('FK100001','RK100001',0,248000,1,'aaa')
insert into tblShouldPayment values('FK100002','RK100002',0,350000,1,'aaa')
--//付款明细信息
insert into tblPayment values('FKM100001','FK100001','2006-10-10','aaa',248000,'emp1002','emp1001',1,'bbb')
insert into tblPayment values('FKM100002','FK100002','2006-10-10','aaa',350000,'emp1002','emp1001',1,'bbb')
--//收款信息



/*-----------------------------------------------------------*/
/*			添加存储过程			     */
/*	(需要存储过程请写在此处,存储过程名以proc_开头)	     */
/*-----------------------------------------------------------*/


--//客户视图(显示未购买过车的客户信息)
if exists(select * from sysobjects where name='view_TailCli')
	drop view view_TailCli
go
create view view_TailCli
as
	select * from tblClient where cliCode not in(select cliCode from tblPresell)
go
123

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -