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)
因为没有得到数据,只能先建好数据库,等得到数据后,再慢慢完善吧