《进销存系统 sql文件》由会员分享，可在线阅读，更多相关《进销存系统 sql文件（21页珍藏版）》请在金锄头文库上搜索。
1、进销存系统进销存系统 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 Cu
2、stomerInfo(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
3、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), -国
4、家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), -产品名称Product
5、Type 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), -产品编
6、号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 in
7、t, -进库数量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 ke
8、y 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), -
9、订单业务员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 nvarcha
10、r(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 * f
11、rom 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 Co
12、mponyInfo-向产品信息表中添加数据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-向库存信息表中添加数据ins
13、ert 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 val
14、ues(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 va
15、lues(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
