create.sql

课程设计了

create database csmag
on
(
name=csmag,
filename='h:\csmag\data\csmag.mdb',
size=10MB,
maxsize=100MB,
filegrowth=5MB
)
log on
(
name=chatroom_log,
filename='h:\csmag\data\csmag.ldf',
size=10MB,
MaxSize=50MB,
filegrowth=5MB
)
use csmag
go
create table Cklist
(
CkId int primary key,
CkName varchar(100),
CkManager varchar(50),
CkPhone varchar(20),
CkAddress varchar(50),
CkMoreInfo varchar(200)
)

create table SupplyCompany
(
SupplyId int primary key,
SupplyCompanyName varchar(100),
SupplyManager varchar(50),
SupplyAddress varchar(50),
SupplyInfo varchar(200),
QiChuYingFu varchar(20)
)


create table ProducesKind
(
ProducesKindId int primary key,
ProducesKinds varchar(50)
)

create table ProducesJiliang
(
ProducesJiliangId int primary key,
ProducesJiliang varchar(20)
)

create table Produces
(
ProducesId varchar(50) primary key,


ProducesKindId int foreign key references ProducesKind,
ProducesName varchar(50),
ProducesGG varchar(50),
ProducesJiLiangId int foreign key references ProducesJiliang,
ProducesJinJia varchar(20),
ProducesPrices varchar(20),
ProducesCompany varchar(100) ,
ProducesMoreInfo varchar(200),
CkNum int

)
create table SupplyList
(
ListId varchar(50) primary key,
CkId int foreign key references CkList,
SupplyId int foreign key references SupplyCompany,
ProducesId varchar(50) foreign key references Produces,
NeedNum int, 
ListDateTime varchar(20),
AffordMoney varchar(50),
TruelyAfford varchar(50),
HandleManager varchar(20),
ListMoreInfo varchar(200)
)

create table  ManagerQx
(
ManagerQxId int primary key,
ManagerPosition varchar(50),

)

create table CsManager
(
CsManagerId int primary key,
ManagerName varchar(50),
ManagerQx varchar(50),
Salary varchar(50),
ManagerPhone varchar(20),
ManagerAddress varchar(50),
ManagerMoreInfo varchar(200),
)

create table CustomsKind
(
CustomsKindId int primary key,
CustomsKinds varchar(50),
CustomsDiscount varchar(10)
)


create table BigCustoms
(
BigCustomsId int primary key,
CustomsKindId int foreign key references CustomsKind,
BigCustomsName varchar(50),
BigCustomsManager varchar(50),
BigCustomsPhone varchar(20),
BigCustomsAddress varchar(50),
ResiveMoney varchar(20),
BigCustomsMoreInfo varchar(200)
)


create table Member
(
MemberId varchar(50) primary key,
CustomsKindId int foreign key references CustomsKind,

MemberName varchar(50),
MemberJiFen int,
MemberXFMoney varchar(20),
XfNum int,
MemberStillMoney varchar(20),
MemberPhone varchar(20),
MemberZT varchar(10),
AddDateTime varchar(20),
TimeOutDate varchar(20),
MemMoreInfo varchar(200),

)
create table ProducesDiscount
(
DiscountId varchar(20) primary key,
ProducesId varchar(50) foreign key references Produces,
Discount varchar(20)
)
create table SellList
(
SellListId varchar(20),
ProducesId varchar(50) foreign key references Produces,
DiscountId varchar(20) foreign key references ProducesDiscount,
SellNum int,
SellDateTime varchar(20),
ManagerQxId int foreign key references ManagerQx,
)
create table FeiKind
(
FeiKindId int primary key,
FeiLei varchar(10),
FeiDetails varchar(50)
)


create table MoneyKind
(
MoneyKindId int primary key,
MoneyKindS varchar(20)
)

create table MoneyMag
(
MoneyMagId varchar(20) primary key,
FeiKindId int foreign key references FeiKind,
MoneyChang varchar(20),
CsManagerId int foreign key references CsManager,
MoneyKindId int foreign key references MoneyKind,
MoneyMagMoreInfo varchar(200),
MoneyMagDate varchar(20)
)

use csmag
go

create table WorkersList
(



WorkersListId int primary key,
WorkKindListId int foreign key references WorkKindList,
WorkerName varchar(50),
WorkerPwd varchar(50) default null,
Salary varchar(50),
WorkerPhone varchar(20),
WorkerAddress varchar(50),
WorkerMoreInfo varchar(200),


)

create table WorkKindList
(
WorkKindListId int primary key,
WorkKinds varchar(50),
WorkKindInfo varchar(200),
)

create table CkMager
(
CkMagerId int primary key,
WorkersListId int foreign key references WorkersList,
CkId int foreign key references Cklist,
)

create table CsOperateWorker
(
CsOperateWorkerId int primary key,
WorkKindListId int foreign key references WorkKindLis,
WorkersListId int foreign key references WorkersList,

)
use csmag
go

SELECT WorkersList.WorkersListId,
 Cklist.CkName, WorkersList.WorkerName,
 WorkersList.WorkerPwd, WorkersList.Salary, 
WorkersList.WorkerPhone, WorkersList.WorkerAddress, 
WorkersList.WorkerMoreInfo 
FROM Cklist INNER JOIN CkMager ON Cklist.CkId = CkMager.CkId 
INNER JOIN WorkersList ON CkMager.WorkersListId = WorkersList.WorkersListId


insert into