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

--销售管理系统相关代码 班级:计科学061 学号: 姓名:方爽

--创建数据库

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'销售管理系统')
DROP DATABASE [销售管理系统]
GO

CREATE DATABASE [销售管理系统] ON (NAME = N'销售管理系统_Data', FILENAME = N'F:\销售管理系统_SQL\销售管理系统_Data.MDF' ,
SIZE = 2, FILEGROWTH = 10%)
LOG ON (NAME = N'销售管理系统_Log', FILENAME = N'F:\销售管理系统_SQL\销售管理系统_Log.LDF' ,
SIZE = 1, FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'销售管理系统', N'autoclose', N'false'
GO

exec sp_dboption N'销售管理系统', N'bulkcopy', N'false'
GO

exec sp_dboption N'销售管理系统', N'trunc. log', N'true'
GO

exec sp_dboption N'销售管理系统', N'torn page detection', N'false'
GO

exec sp_dboption N'销售管理系统', N'read only', N'false'
GO

exec sp_dboption N'销售管理系统', N'dbo use', N'false'
GO

exec sp_dboption N'销售管理系统', N'single', N'false'
GO

exec sp_dboption N'销售管理系统', N'autoshrink', N'true'
GO

exec sp_dboption N'销售管理系统', N'ANSI null default', N'false'
GO

exec sp_dboption N'销售管理系统', N'recursive triggers', N'false'
GO

exec sp_dboption N'销售管理系统', N'ANSI nulls', N'false'
GO

exec sp_dboption N'销售管理系统', N'concat null yields null', N'false'
GO

exec sp_dboption N'销售管理系统', N'cursor close on commit', N'false'
GO

exec sp_dboption N'销售管理系统', N'default to local cursor', N'false'
GO

exec sp_dboption N'销售管理系统', N'quoted identifier', N'false'
GO

exec sp_dboption N'销售管理系统', N'ANSI warnings', N'false'
GO

exec sp_dboption N'销售管理系统', N'auto create statistics', N'true'
GO

exec sp_dboption N'销售管理系统', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'销售管理系统', N'db chaining', N'false'
GO

use [销售管理系统]
GO

--检验目前数据库是否已存在对应表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户订货表_商品基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[用户订货表] DROP CONSTRAINT FK_用户订货表_商品基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_库存表_商品基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[库存表] DROP CONSTRAINT FK_库存表_商品基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_销售表_商品基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[销售表] DROP CONSTRAINT FK_销售表_商品基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_销售退货表_商品基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[销售退货表] DROP CONSTRAINT FK_销售退货表_商品基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户订货表_用户基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[用户订货表] DROP CONSTRAINT FK_用户订货表_用户基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户结款表_用户基础信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[用户结款表] DROP CONSTRAINT FK_用户结款表_用户基础信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户订货表_订货票号表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[用户订货表] DROP CONSTRAINT FK_用户订货表_订货票号表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_销售表_销售票号表2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[销售表] DROP CONSTRAINT FK_销售表_销售票号表2
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_销售退货表_销售退单票号表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[销售退货表] DROP CONSTRAINT FK_销售退货表_销售退单票号表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户结款统计表_用户结款表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[用户结款统计表] DROP CONSTRAINT FK_用户结款统计表_用户结款表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[销售管理视图]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[销售管理视图]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户结款统计表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用户结款统计表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户结款表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用户结款表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户订货表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用户订货表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[库存表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[库存表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[销售表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[销售表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[销售退货表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[销售退货表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[商品基础信息表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[商品基础信息表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户基础信息表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用户基础信息表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[操作员信息表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[操作员信息表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订货票号表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[订货票号表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[销售票号表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[销售票号表]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[销售退单票号表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[销售退单票号表]
GO

--创建用户

if not exists (select * from master.dbo.syslogins where loginname = N'山')
BEGIN
declare @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 = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'山', null, @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

if 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

CREATE 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 [PRIMARY]
GO

--创建主键约束

ALTER TABLE [dbo].[商品基础信息表] WITH NOCHECK ADD
CONSTRAINT [PK_商品基础信息表] PRIMARY KEY CLUSTERED
(
[商品编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[用户基础信息表] WITH NOCHECK ADD
CONSTRAINT [PK_用户基础信息表] PRIMARY KEY CLUSTERED
(
[用户编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[操作员信息表] WITH NOCHECK ADD
CONSTRAINT [PK_操作员信息表] PRIMARY KEY CLUSTERED
(
[操作员编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[订货票号表] WITH NOCHECK ADD
CONSTRAINT [PK_订货票号表] PRIMARY KEY CLUSTERED
(
[订单号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[销售票号表] WITH NOCHECK ADD
CONSTRAINT [PK_销售票号表] PRIMARY KEY CLUSTERED
(
[销售票号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[销售退单票号表] WITH NOCHECK ADD
CONSTRAINT [PK_销售退单票号表] PRIMARY KEY CLUSTERED
(
[销售退单票号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[用户结款表] WITH NOCHECK ADD
CONSTRAINT [PK_用户结款表] PRIMARY KEY CLUSTERED
(
[用户编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[用户订货表] WITH NOCHECK ADD
CONSTRAINT [PK_用户订货表] PRIMARY KEY CLUSTERED
(
[用户编号],
[商品编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[库存表] WITH NOCHECK ADD
CONSTRAINT [PK_库存表] PRIMARY KEY CLUSTERED
(
[商品编号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[销售表] WITH NOCHECK ADD
CONSTRAINT [PK_销售表] PRIMARY KEY CLUSTERED
(
[销售票号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[销售退货表] WITH NOCHECK ADD
CONSTRAINT [PK_销售退货表] PRIMARY KEY CLUSTERED
(
[销售退单票号]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[用户结款统计表] WITH NOCHECK ADD
CONSTRAINT [PK_用户结款统计表] PRIMARY KEY CLUSTERED
(
[用户编号]
) ON [PRIMARY]
GO

--创建默认值约束

ALTER TABLE [dbo].[操作员信息表] ADD
CONSTRAINT [DF_操作员信息表_时间] DEFAULT (getdate()) FOR [时间]
GO

ALTER TABLE [dbo].[销售票号表] ADD
CONSTRAINT [DF_销售票号表_销售时间] DEFAULT (getdate()) FOR [销售时间]
GO

ALTER 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 [盘点]
GO

ALTER TABLE [dbo].[用户结款统计表] ADD
CONSTRAINT [DF_用户结款统计表_结款时间] DEFAULT (getdate()) FOR [结款时间]
GO

ALTER TABLE [dbo].[用户结款表] ADD
CONSTRAINT [FK_用户结款表_用户基础信息表] FOREIGN KEY
(
[用户编号]
) REFERENCES [dbo].[用户基础信息表] (
[用户编号]
)
GO

ALTER TABLE [dbo].[用户订货表] ADD
CONSTRAINT [FK_用户订货表_订货票号表] FOREIGN KEY
(
[订货票号]
) REFERENCES [dbo].[订货票号表] (
[订单号]
),
CONSTRAINT [FK_用户订货表_用户基础信息表] FOREIGN KEY
(
[用户编号]
) REFERENCES [dbo].[用户基础信息表] (
[用户编号]
),
CONSTRAINT [FK_用户订货表_商品基础信息表] FOREIGN KEY
(
[商品编号]
) REFERENCES [dbo].[商品基础信息表] (
[商品编号]
)
GO

ALTER TABLE [dbo].[库存表] ADD
CONSTRAINT [FK_库存表_商品基础信息表] FOREIGN KEY
(
[商品编号]
) REFERENCES [dbo].[商品基础信息表] (
[商品编号]
)
GO

ALTER TABLE [dbo].[销售表] ADD
CONSTRAINT [FK_销售表_商品基础信息表] FOREIGN KEY
(
[商品编号]
) REFERENCES [dbo].[商品基础信息表] (
[商品编号]
),
CONSTRAINT [FK_销售表_销售票号表2] FOREIGN KEY
(
[销售票号]
) REFERENCES [dbo].[销售票号表] (
[销售票号]
)
GO

ALTER TABLE [dbo].[销售退货表] ADD
CONSTRAINT [FK_销售退货表_商品基础信息表] FOREIGN KEY
(
[商品编号]
) REFERENCES [dbo].[商品基础信息表] (
[商品编号]
),
CONSTRAINT [FK_销售退货表_销售退单票号表] FOREIGN KEY
(
[销售退单票号]
) REFERENCES [dbo].[销售退单票号表] (
[销售退单票号]
)
GO

ALTER TABLE [dbo].[用户结款统计表] ADD
CONSTRAINT [FK_用户结款统计表_用户结款表] FOREIGN KEY
(
[用户编号]
) REFERENCES [dbo].[用户结款表] (
[用户编号]
)
GO