sql server商品进销存管理系统设计及实现的数据库源代码

约定不等于承诺〃 2023-01-17 10:40 91阅读 0赞

use master
    if exists(select * from sysdatabases where name='NorthwindSQL')  --判断NorthwindSQL库是否存在，若存在则先删除
       drop database NorthwindSQL
    
    create database NorthwindSQL                          --创建数据库NorthwindSQL
    on primary
    (
    name='NorthwindSQL',
    filename='D:\sqldata\NorthwindSQL.mdf',
    size=5,
    filegrowth=10%
    )
    log on
    (
    name='NorthwindSQL_log',
    filename='D:\sqldata\NorthwindSQL_log.ldf',
    size=3,
    filegrowth=10%
    )
     
    use NorthwindSQL
    create table Staff(St_ID int identity not null,            --创建员工表
    St_number char(10) not null,
    Name varchar(10) null,
    sex bit not null,
    St_address varchar(42) null,
    St_zipcode char(5) null,
    St_telephone char(10) null,
    St_birthday datetime null,
    St_marriage bit not null,
    Employ_date datetime null,
    Start_money money null,
    Now_money money null,
    Addmoney_date datetime null,
    Department varchar(10) null,
    Exe_director varchar(10) null
    )
    
    
    create table Customer(Cu_ID nvarchar(5) not null,          --创建客户表
    corporate_name nvarchar(40) not null,
    Contact_person nvarchar(30) null,
    Contact_n nvarchar(30) null,
    address nvarchar(60) null,
    City nvarchar(15) null,
    Ad_region nvarchar(15) null,
    Ad_number nvarchar(10) null,
    Country nvarchar(15) null,
    Telephone nvarchar(24) null,
    Fax nvarchar(24) null
     )
     
    create table Order_file(Order_number int identity not null,   --创建订货主档表
    Cu_ID nvarchar(5) null,
    St_ID int null,
    Order_date datetime null,
    Require_date datetime null,
    Delivery_date datetime null,
    Delivery_mode int null,
    Trans_money money null,
    Get_people nvarchar(40) null,
    Delivery_address nvarchar(60) null,
    Delivery_city nvarchar(15) null,
    Delivery_Ad nvarchar(15) null,
    Delivery_Adnumber nvarchar(10) null,
    Delivery_country nvarchar(15) null
     )
    
     
     create table Order_schedule(Order_ID int not null,     --创建订货明细表
     Product_ID int not null,
     O_money money not null,
     Counts smallint not null,
     Discount real not null,
     )
     
     create table Product_info(Product_ID int identity not null,  --创建产品资料表
     Product nvarchar(40) not null,
     Supplier_number int null,
     Category_ID int null,
     DW_count nvarchar(20) null,
     O_money money not null,
     Inventory smallint null,
     YD_count smallint null,
     Safety_stock smallint null,
     No_sale bit not null
     )
    
     
     create table Product_category( Category_ID int identity not null, --创建产品类别表
     Category_name nvarchar(15) not null,
     Instruc ntext null,
     Picture image null
     )
     
     --创建约束
     
     alter table Staff add constraint pK_St_ID primary key (St_ID)  --添加主键约束
     alter table Staff add constraint uq_St_number unique (St_number) --添加唯一约束
     
     alter table Customer add constraint pk_Cu_ID primary key(Cu_ID)
     
     alter table Order_file add constraint pk_Order_number primary key(Order_number)
     alter table Order_file add constraint df_Trans_money default(0) for Trans_money  --添加默认值为0
     
     alter table Order_schedule add constraint pk_Order_ID primary key (Order_ID)
     alter table Order_schedule add constraint df_O_money default(0) for O_money
     alter table Order_schedule add constraint df_Counts default(1) for Counts
     alter table Order_schedule add constraint df_Discount default(0) for Discount
      
     alter table Product_info add constraint pk_Product_ID primary key (Product_ID)
     alter table Product_info add constraint df_O_money default(0) for O_money
     alter table Product_info add constraint df_Inventory default(0) for Inventory
     alter table Product_info add constraint df_YD_count default(0) for YD_count
     alter table Product_info add constraint df_Safety_stock default(0) for Safety_stock
     alter table Product_info add constraint df_No_sale default(0) for No_sale
     
     
     alter table Product_category add constraint pk_Category_ID primary key(Category_ID)
     
    
     alter table Order_file add constraint fk_St_ID foreign key(St_ID) references Staff(St_ID)--添加主外键约束
     alter table Order_file add constraint fk_Cu_ID foreign key(Cu_ID) references Customer(Cu_ID)
     alter table Order_file add constraint fk_Order_number foreign key(Order_number) references Order_schedule(Order_ID)
     alter table Order_schedule add constraint fk_Product_ID foreign key(Product_ID) references Product_info(Product_ID)
     alter table Order_schedule add constraint fk_O_money foreign key(O_money) references Product_info(O_money)
     alter table Product_info add constraint fk_Category_ID foreign key(Category_ID)references Product_category(Category_ID)

因为没有得到数据，只能先建好数据库，等得到数据后，再慢慢完善吧