进销存系统 sql文件.doc

进销存系统进销存系统 sqlsql 文件文件use mastergoif exists (select * from sysdatabases where name = 'SellManageSystem')drop database SellManageSystemgocreate database SellManageSystemgouse SellManageSystemgo--创建客户信息表if exists (select 1 from sysobjects where name = 'CustomerInfo')drop table CustomerInfogocreate table CustomerInfo(CustomerID int primary key identity(1000,1), --客户编号CustomerName nvarchar(20) not null, --客户名称CustomerAddress nvarchar(50) , --客户地址CustomerPhone nvarchar(11), --客户Taxno nvarchar(10) , --税号Repute nvarchar(50), --信誉度Country nvarchar(20), --国家Province nvarchar(20) --省份)go--创建供应商信息表if exists (select 1 from sysobjects where name = 'ComponyInfo')drop table ComponyInfogocreate table ComponyInfo(ComponyID int primary key identity(1000,1), --供应商编号ComponyName nvarchar(20) not null, --供应商名称ComponyAddress nvarchar(50) , --供应商地址ComponyPhone nvarchar(11), --供应商Taxno nvarchar(10) , --税号Country nvarchar(20), --国家Province nvarchar(20) --省份)go--创建产品信息表if exists (select 1 from sysobjects where name = 'ProductInfo')drop table ProductInfogocreate table ProductInfo(ProductID int primary key identity(001,1), --产品编号ComponyID int foreign key references ComponyInfo(ComponyID), --供应商编号ProductName nvarchar(20), --产品名称ProductType nvarchar(20), --产品类别NumberPerMonth int , --月生产能力Price money --单价)go--创建库存信息表if exists (select 1 from sysobjects where name = 'StorageInfo')drop table StorageInfogocreate table StorageInfo(StorageID int primary key identity(1,1), --库存信息编号ProductID int foreign key references ProductInfo(ProductID), --产品编号StorageAddress nvarchar(50), --库存地址StorageNumber int --库存数量)go--创建产品进库信息表if exists (select 1 from sysobjects where name = 'StockInfo')drop table StockInfogocreate table StockInfo(StockID int primary key identity(001,1), --进库号ProductID int foreign key references ProductInfo(ProductID), --产品编号StockNumber int, --进库数量Stock money, --进价Stocksum money, --总价ComponyID int foreign key references ComponyInfo(ComponyID), --供应商编号StockTime datetime , --进库时间Principal nvarchar(20) --经手人)go--创建订单信息表if exists (select 1 from sysobjects where name = 'OrderInfo')drop table OrderInfogocreate table OrderInfo(OrderID int primary key identity(1,1), --订单号CustomerID int foreign key references CustomerInfo(CustomerID), --客户编号ProductID int foreign key references ProductInfo(ProductID), --产品编号OrderTime datetime , --订单时间OrderNumber int , --订单数量Price money , --单价Ordersum money, --总价RequireDate datetime , --产品需要时间OrderEmployee nvarchar(20), --订单业务员IsConsignment nvarchar(2) --是否发货)go--创建发货信息表if exists (select 1 from sysobjects where name = 'SendInfo')drop table SendInfogocreate table SendInfo(SendID int primary key identity(10,1), --发货信息编号OrderID int foreign key references OrderInfo(OrderID), --订单编号SendDate datetime , --发货时间SendPrincipal nvarchar(20) --发货负责人)go--向客户信息表中添加数据insert into CustomerInfo values('三毛','武昌南湖','32322323','012450','良好','中国','湖北省')insert into CustomerInfo values('张伟','黄石火车站','32322323','451245','还行','中国','湖北省')insert into CustomerInfo values('黄江','杭州西湖','32322323','012475','不错','中国','江苏省')insert into CustomerInfo values('张伟','成都','32322323','320145','很好','中国','四川省')select * from CustomerInfo--向供应商信息表中添加数据insert into ComponyInfo values('DELL','武昌南湖','32322323','012450','中国','湖北省')insert into ComponyInfo values('Acer','黄石火车站','32322323','451245','中国','湖北省')insert into ComponyInfo values('Lenove','杭州西湖','32322323','012475','中国','江苏省')insert into ComponyInfo values('HP','成都','32322323','320145','中国','四川省')select * from ComponyInfo--向产品信息表中添加数据insert into ProductInfo values(NULL,'MP3','电子产品',20000,150)insert into ProductInfo values(NULL,'Nokia 5220','电子产品',3000,1150)insert into ProductInfo values(NULL,'Acer 4741g','电子产品',800,4150)insert into ProductInfo values(NULL,'Kingston 4G','电子产品',5000,60)select * from ProductInfo--向库存信息表中添加数据insert into StorageInfo values(1,'北区仓库',20000)insert into StorageInfo values(2,'长江 2 号仓库',3000)insert into StorageInfo values(3,'东北仓库',1200)insert into StorageInfo values(4,'盐城仓库',8000)select * from StorageInfo--向产品进库信息表添加数据insert into StockInfo values(1,300,NULL,NULL,NULL,getdate(),'张三')insert into StockInfo values(2,2000,NULL,NULL,NULL,getdate(),'李四')insert into StockInfo values(3,500,NULL,NULL,NULL,getdate(),'张三')insert into StockInfo values(4,6000,NULL,NULL,NULL,getdate(),'李四')select * from StockInfo--向订单信息表中添加数据insert into OrderInfo values(1000,1,getdate(),100,NULL,NULL,getdate(),'晓风','是')insert into OrderInfo values(1001,2,getdate(),2000,NULL,NULL,'20100910','李宇春','否')insert into OrderInfo values(1002,3,getdate(),300,NULL,NULL,'20101004','张娟','是')insert into OrderInfo values(1003,4,getdate(),600,NULL,NULL,'20101010','金凤','否')select * from OrderInfo--向发货信息表中添加数据insert into SendInfo values(2,'20060412','李四')insert into SendInfo values(1,'20101001','张三')insert into SendInfo values(3,'20101011','张三')insert into SendInfo values(4,'20101015','李四')select * from SendInfogo--创建库存视图if exists (select 1 from sysobjects where name = 'view_st。