--建表
CREATE TABLE tb_medicine---4药品表
(MNo
CHAR(10)
NOT NULL
primary key
,MName
varchar(50)
NOT NULL
,Norm
varchar(50)
NOT NULL
,MPrice
money
NOT NULL
,ValidDate
date
notnull,MNumintnotnull,SupplyNOchar(10)
FOREIGN KEY(SupplyNO)
REFERENCES tb_Supply(SupplyNO)
,StockNOchar(10)
FOREIGN KEY(StockNO)
REFERENCES tb_Stock(StockNO)
,Instuction
xml
);
create table tb_Supply---1供应商表
(SupplyNO
CHAR(10)
NOT NULL
primary key
,SupplyName
varchar(50)
NOT NULL
,Address
varchar(50)
NOT NULL
,SupplyTel
varchar(20)
notnull);
create table tb_Stock----2药库表
(StockNO
CHAR(10)
NOT NULL
primary key
,StockName
varchar(50)
NOT NULL
,ANOchar(10)
CONSTRAINT fk_Stock_ANO
FOREIGN KEY(ANO)
REFERENCES tb_Admin(ANO)
);
create table tb_Admin---3管理员表
(ANO
CHAR(10)
NOT NULL
primary key
,AName
varchar(50)
NOT NULL
,pwd
varbinary
NOT NULL
);
ALTER TABLE tb_Admin
ALTER COLUMN
pwd
varbinary(128);
create table tb_Client---5客户表
(CNO
CHAR(10)
NOT NULL
primary key
,CName
varchar(100)
NOT NULL
,CTel
varchar(40)
NOT NULL
,CAddress
varchar(100)
);
ALTER TABLE tb_Client
ALTER COLUMN
CAddress
varchar(100);
create table tb_caigou---6采购表
(caigouNO
CHAR(10)
NOT NULL
primary key
,caigouDate
date
NOT NULL
,ANOchar(10)
NOT NULL
CONSTRAINT fk_caigou_ANO
FOREIGN KEY(ANO)
REFERENCES tb_Admin(ANO)
,SupplyNOchar(10)
NOT NULL
CONSTRAINT fk_caigou_SupplyNO
FOREIGN KEY(SupplyNO)
REFERENCES tb_Supply(SupplyNO)
);
create table tb_caigouDetail---7采购明细表
(caigouDetailNO
CHAR(10)
NOT NULL
primary key
,caigouNOchar(10)
NOT NULL
CONSTRAINT fk_caigouDetail_caigouNO
FOREIGN KEY(caigouNO)
REFERENCES tb_caigou(caigouNO)
,MNochar(10)
NOT NULL
CONSTRAINT fk_caigouDetail_MNo
FOREIGN KEY(MNo)
REFERENCES tb_medicine(MNo)
,Numintnotnull,Price
money--AS (num*MPrice)
);
create table tb_chushou---8出售表
(chushouNO
CHAR(10)
NOT NULL
primary key
,chushouDate
date
NOT NULL
,ANOchar(10)
NOT NULL
CONSTRAINT fk_chushou_ANO
FOREIGN KEY(ANO)
REFERENCES tb_Admin(ANO)
,CNOchar(10)
NOT NULL
CONSTRAINT fk_chushou_CNO
FOREIGN KEY(CNO)
REFERENCES tb_Client(CNO)
);
create table tb_chushouDetail----9出售明细表
(chushouDetailNO
CHAR(10)
NOT NULL
primary key
,chushouNOchar(10)
NOT NULL
CONSTRAINT fk_chushouDetail_chushouNO
FOREIGN KEY(chushouNO)
REFERENCES tb_chushou(chushouNO)
,MNochar(10)
NOT NULL
CONSTRAINT fk_chushouDetail_MNo
FOREIGN KEY(MNo)
REFERENCES tb_medicine(MNo)
,Numintnotnull,Price
money--AS (num*MPrice)
);
如果觉得《药品信息管理系统mysql_药品信息管理系统数据库部分代码》对你有帮助，请点赞、收藏，并留下你的观点哦！
