销售管理系统相关代码.doc

-销售管理系统相关代码 班级:计科学061 学号:061004103 姓名:方爽-创建数据库IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N销售管理系统)DROP DATABASE 销售管理系统GOCREATE DATABASE 销售管理系统 ON (NAME = N销售管理系统_Data, FILENAME = NF:销售管理系统_SQL销售管理系统_Data.MDF , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N销售管理系统_Log, FILENAME = NF:销售管理系统_SQL销售管理系统_Log.LDF , SIZE = 1, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_ASGOexec sp_dboption N销售管理系统, Nautoclose, NfalseGOexec sp_dboption N销售管理系统, Nbulkcopy, NfalseGOexec sp_dboption N销售管理系统, Ntrunc. log, NtrueGOexec sp_dboption N销售管理系统, Ntorn page detection, NfalseGOexec sp_dboption N销售管理系统, Nread only, NfalseGOexec sp_dboption N销售管理系统, Ndbo use, NfalseGOexec sp_dboption N销售管理系统, Nsingle, NfalseGOexec sp_dboption N销售管理系统, Nautoshrink, NtrueGOexec sp_dboption N销售管理系统, NANSI null default, NfalseGOexec sp_dboption N销售管理系统, Nrecursive triggers, NfalseGOexec sp_dboption N销售管理系统, NANSI nulls, NfalseGOexec sp_dboption N销售管理系统, Nconcat null yields null, NfalseGOexec sp_dboption N销售管理系统, Ncursor close on commit, NfalseGOexec sp_dboption N销售管理系统, Ndefault to local cursor, NfalseGOexec sp_dboption N销售管理系统, Nquoted identifier, NfalseGOexec sp_dboption N销售管理系统, NANSI warnings, NfalseGOexec sp_dboption N销售管理系统, Nauto create statistics, NtrueGOexec sp_dboption N销售管理系统, Nauto update statistics, NtrueGOif( (microsoftversion / power(2, 24) = 8) and (microsoftversion & 0xffff = 724) )exec sp_dboption N销售管理系统, Ndb chaining, NfalseGOuse 销售管理系统GO-检查当前数据库是否已存在相应表if exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客户订货表_商品基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客户订货表 DROP CONSTRAINT FK_客户订货表_商品基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_库存表_商品基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.库存表 DROP CONSTRAINT FK_库存表_商品基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_销售表_商品基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.销售表 DROP CONSTRAINT FK_销售表_商品基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_销售退货表_商品基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.销售退货表 DROP CONSTRAINT FK_销售退货表_商品基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客户订货表_客户基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客户订货表 DROP CONSTRAINT FK_客户订货表_客户基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客户结款表_客户基础信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客户结款表 DROP CONSTRAINT FK_客户结款表_客户基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客户订货表_订货票号表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客户订货表 DROP CONSTRAINT FK_客户订货表_订货票号表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_销售表_销售票号表2) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.销售表 DROP CONSTRAINT FK_销售表_销售票号表2GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_销售退货表_销售退单票号表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.销售退货表 DROP CONSTRAINT FK_销售退货表_销售退单票号表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客户结款记录表_客户结款表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客户结款记录表 DROP CONSTRAINT FK_客户结款记录表_客户结款表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.销售管理视图) and OBJECTPROPERTY(id, NIsView) = 1)drop view dbo.销售管理视图GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客户结款记录表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客户结款记录表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客户结款表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客户结款表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客户订货表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客户订货表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.库存表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.库存表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.销售表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.销售表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.销售退货表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.销售退货表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.商品基础信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.商品基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客户基础信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客户基础信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.操作员信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.操作员信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.订货票号表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.订货票号表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.销售票号表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.销售票号表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.销售退单票号表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.销售退单票号表GO-创建用户if not exists (select * from master.dbo.syslogins where loginname = N山)BEGINdeclare logindb nvarchar(132), loginlang nvarchar(132) select logindb = N销售管理系统, loginlang = N简体中文if logindb is null or not exists (select * from master.dbo.sysdatabases where name = logindb)select logindb = Nmasterif loginlang is null or (not exists (select * from master.dbo.syslanguages where name = loginlang) and loginlang Nus_english)select loginlang = languageexec sp_addlogin N山, null, logindb, loginlangENDGOif not exists (select * from dbo.sysusers where name = Nguest and hasdbaccess = 1)EXEC sp_grantdbaccess NguestGOif not exists (select * from dbo.sysusers where name = N山)EXEC sp_grantdbaccess N山, N山GO-创建数据库表CREATE TABLE dbo.商品基础信息表 (商品编号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,简称 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,规格 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,产地 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,单位 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,保质期 char (5) COLLATE Chinese_PRC_CI_AS NULL ,进价 money NULL ,批发价 money NULL ,零售价 money NULL ,备注 varchar (60) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.客户基础信息表 (客户编号 varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客户全称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,简称 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,地址 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,传真 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,联系人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,联系人电话 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,开户银行 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,纳税人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,邮箱网址 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,邮政编码 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,银行账号 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.操作员信息表 (操作员编号 varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,操作员姓名 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作员密码 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作员级别 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,时间 datetime NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.订货票号表 (订单号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,品种数 int NULL ,数量 int NULL ,应付金额 money NULL ,订货时间 datetime NULL ,经手人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作员 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,是否可用 int NULL ) ON PRIMARYGOCREATE TABLE dbo.销售票号表 (销售票号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,品种数 int NULL ,数量 int NULL ,应付金额 money NULL ,付款方式 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,实付金额 money NULL ,销售时间 datetime NULL ,经手人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作员 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.销售退单票号表 (销售退单票号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,品种数 int NULL ,数量 int NULL ,应付金额 money NULL ,付款方式 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,实付金额 money NULL ,退货时间 datetime NOT NULL ,经手人 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,操作员 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.客户结款表 (客户编号 varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客户全称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,金额小计 money NULL ,欠款记账 money NULL ,付款合计 money NULL ,结款时间 datetime NULL ) ON PRIMARYGOCREATE TABLE dbo.客户订货表 (客户编号 varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客户名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,商品编号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,数量 int NULL ,金额 money NULL ,折扣 float NULL ,订货票号 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.库存表 (商品编号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,库存数量 int NULL ,库存金额 money NULL ,盘点 int NULL ) ON PRIMARYGOCREATE TABLE dbo.销售表 (商品编号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客户编号 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客户全称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,数量 int NULL ,金额 money NULL ,折扣 float NULL ,销售票号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.销售退货表 (商品编号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客户编号 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客户全称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,数量 int NULL ,金额 money NULL ,折扣 float NULL ,销售退单票号 varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.客户结款记录表 (客户编号 varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客户全称 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,结款金额 money NULL ,结款时间 datetime NULL ,操作员 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGO-创建主键约束ALTER TABLE dbo.商品基础信息表 WITH NOCHECK ADD CONSTRAINT PK_商品基础信息表 PRIMARY KEY CLUSTERED (商品编号) ON PRIMARY GOALTER TABLE dbo.客户基础信息表 WITH NOCHECK ADD CONSTRAINT PK_客户基础信息表 PRIMARY KEY CLUSTERED (客户编号) ON PRIMARY GOALTER TABLE dbo.操作员信息表 WITH NOCHECK ADD CONSTRAINT PK_操作员信息表 PRIMARY KEY CLUSTERED (操作员编号) ON PRIMARY GOALTER TABLE dbo.订货票号表 WITH NOCHECK ADD CONSTRAINT PK_订货票号表 PRIMARY KEY CLUSTERED (订单号) ON PRIMARY GOALTER TABLE dbo.销售票号表 WITH NOCHECK ADD CONSTRAINT PK_销售票号表 PRIMARY KEY CLUSTERED (销售票号) ON PRIMARY GOALTER TABLE dbo.销售退单票号表 WITH NOCHECK ADD CONSTRAINT PK_销售退单票号表 PRIMARY KEY CLUSTERED (销售退单票号) ON PRIMARY GOALTER TABLE dbo.客户结款表 WITH NOCHECK ADD CONSTRAINT PK_客户结款表 PRIMARY KEY CLUSTERED (客户编号) ON PRIMARY GOALTER TABLE dbo.客户订货表 WITH NOCHECK ADD CONSTRAINT PK_客户订货表 PRIMARY KEY CLUSTERED (客户编号,商品编号) ON PRIMARY GOALTER TABLE dbo.库存表 WITH NOCHECK ADD CONSTRAINT PK_库存表 PRIMARY KEY CLUSTERED (商品编号) ON PRIMARY GOALTER TABLE dbo.销售表 WITH NOCHECK ADD CONSTRAINT PK_销售表 PRIMARY KEY CLUSTERED (销售票号) ON PRIMARY GOALTER TABLE dbo.销售退货表 WITH NOCHECK ADD CONSTRAINT PK_销售退货表 PRIMARY KEY CLUSTERED (销售退单票号) ON PRIMARY GOALTER TABLE dbo.客户结款记录表 WITH NOCHECK ADD CONSTRAINT PK_客户结款记录表 PRIMARY KEY CLUSTERED (客户编号) ON PRIMARY GO-创建默认值约束ALTER TABLE dbo.操作员信息表 ADD CONSTRAINT DF_操作员信息表_时间 DEFAULT (getdate() FOR 时间GOALTER TABLE dbo.销售票号表 ADD CONSTRAINT DF_销售票号表_销售时间 DEFAULT (getdate() FOR 销售时间GOALTER TABLE dbo.销售退单票号表 ADD CONSTRAINT DF_销售退单票号表_退货时间 DEFAULT (getdate() FOR 退货时间GO-创建默认值约束ALTER TABLE dbo.客户结款表 ADD CONSTRAINT DF_客户结款表_结款时间 DEFAULT (getdate() FOR 结款时间,CONSTRAINT IX_客户结款表 UNIQUE NONCLUSTERED (客户编号) ON PRIMARY GO-创建默认值约束ALTER TABLE dbo.库存表 ADD CONSTRAINT DF_库存表_盘点 DEFAULT (0) FOR 盘点GOALTER TABLE dbo.客户结款记录表 ADD CONSTRAINT DF_客户结款记录表_结款时间 DEFAULT (getdate() FOR 结款时间GOALTER TABLE dbo.客户结款表 ADD CONSTRAINT FK_客户结款表_客户基础信息表 FOREIGN KEY (客户编号) REFERENCES dbo.客户基础信息表 (客户编号)GOALTER TABLE dbo.客户订货表 ADD CONSTRAINT FK_客户订货表_订货票号表 FOREIGN KEY (订货票号) REFERENCES dbo.订货票号表 (订单号),CONSTRAINT FK_客户订货表_客户基础信息表 FOREIGN KEY (客户编号) REFERENCES dbo.客户基础信息表 (客户编号),CONSTRAINT FK_客户订货表_商品基础信息表 FOREIGN KEY (商品编号) REFERENCES dbo.商品基础信息表 (商品编号)GOALTER TABLE dbo.库存表 ADD CONSTRAINT FK_库存表_商品基础信息表 FOREIGN KEY (商品编号) REFERENCES dbo.商品基础信息表 (商品编号)GOALTER TABLE dbo.销售表 ADD CONSTRAINT FK_销售表_商品基础信息表 FOREIGN KEY (商品编号) REFERENCES dbo.商品基础信息表 (商品编号),CONSTRAINT FK_销售表_销售票号表2 FOREIGN KEY (销售票号) REFERENCES dbo.销售票号表 (销售票号)GOALTER TABLE dbo.销售退货表 ADD CONSTRAINT FK_销售退货表_商品基础信息表 FOREIGN KEY (商品编号) REFERENCES dbo.商品基础信息表 (商品编号),CONSTRAINT FK_销售退货表_销售退单票号表 FOREIGN KEY (销售退单票号) REFERENCES dbo.销售退单票号表 (销售退单票号)GOALTER TABLE dbo.客户结款记录表 ADD CONSTRAINT FK_客户结款记录表_客户结款表 FOREIGN KEY (客户编号) REFERENCES dbo.客户结款表 (客户编号)GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO-创建视图CREATE VIEW dbo.销售管理视图ASSELECT dbo.订货票号表.订单号, dbo.库存表.商品编号, dbo.销售退货表.销售退单票号, dbo.商品基础信息表.商品编号 AS Expr1, dbo.客户订货表.客户编号, dbo.客户订货表.商品编号 AS Expr2, dbo.客户基础信息表.客户编号 AS Expr3, dbo.销售表.销售票号, dbo.销售票号表.销售票号 AS Expr4, dbo.客户结款表.客户编号 AS Expr5, dbo.客户结款记录表.客户编号 AS Expr6, dbo.销售退单票号表.销售退单票号 AS Expr7FROM dbo.订货票号表 INNER JOIN dbo.客户订货表 ON dbo.订货票号表.订单号 = dbo.客户订货表.订货票号 INNER JOIN dbo.客户基础信息表 ON dbo.客户订货表.客户编号 = dbo.客户基础信息表.客户编号 INNER JOIN dbo.客户结款表 ON dbo.客户基础信息表.客户编号 = dbo.客户结款表.客户编号 INNER JOIN dbo.客户结款记录表 ON dbo.客户结款表.客户编号 = dbo.客户结款记录表.客户编号 INNER JOIN dbo.库存表 ON dbo.客户订货表.商品编号 = dbo.库存表.商品编号 INNER JOIN dbo.商品基础信息表 ON dbo.客户订货表.商品编号 = dbo.商品基础信息表.商品编号 AND dbo.库存表.商品编号 = dbo.商品基础信息表.商品编号 INNER JOIN dbo.销售表 ON dbo.商品基础信息表.商品编号 = dbo.销售表.商品编号 INNER JOIN dbo.销售票号表 ON dbo.销售表.销售票号 = dbo.销售票号表.销售票号 INNER JOIN dbo.销售退货表 ON dbo.商品基础信息表.商品编号 = dbo.销售退货表.商品编号 INNER JOIN dbo.销售退单票号表 ON dbo.销售退货表.销售退单票号 = dbo.销售退单票号表.销售退单票号GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO-创建触发器create trigger tri11on 库存表for insert,update,deleteas print 数据修改成功！goinsert into 库存表values(S20081228003,饼干,50,500,0)gocreate trigger tri2on 操作员信息表for insert,update,deleteas print 数据修改成功！gocreate trigger tri3on 订货票号表for inser