CREATE DATABASE IF NOT EXISTS Test CHARACTER SET UTF8;
USE Test;
CREATE TABLE `ManagerInfo`(
`Mno` CHAR(3) COMMENT '管理员编号',
`Mname` VARCHAR(10) NOT NULL COMMENT '管理员姓名',
`Mgender` CHAR(1) COMMENT '管理员性别' DEFAULT '男',
`Mbirhdate` DATE COMMENT '管理员出生日期',
`Mtelephone` CHAR(11) NOT NULL COMMENT '管理员电话',
`Mhiredate` DATE COMMENT '入职日期',
`Wno` CHAR(3) COMMENT '仓库管理编号',
PRIMARY KEY (`Mno`)
);
USE Test;
CREATE TABLE `WarehouseInfo`(
`Wno` CHAR(3) COMMENT '仓库编号',
`Wname` VARCHAR(10) NOT NULL COMMENT '仓库名称',
`Wtype` VARCHAR(7) COMMENT '仓库类别',
`Waddress` VARCHAR(30) COMMENT '仓库地址',
`Wcharge` CHAR(3) COMMENT '仓库主管',
PRIMARY KEY (`Wno`)
);
USE Test;
CREATE TABLE `ProductInfo`(
`Pno` CHAR(8) COMMENT '货品编号',
`Pname` VARCHAR(30) NOT NULL COMMENT '货品名称',
`Ptype` VARCHAR(8) COMMENT '货品类别',
`Pprice` FLOAT COMMENT '货品售价',
`Punit` VARCHAR(10) COMMENT '货品单位',
`Pweight` VARCHAR(10) COMMENT '货品重量',
`Pmanufacture` VARCHAR(30) COMMENT '货品生产厂商',
`Pguaranteedate` VARCHAR(8) COMMENT '货品保质期',
`Wno` CHAR(3) COMMENT '存放货品编号',
`Pstock` INT COMMENT '货品库存量',
PRIMARY KEY (`Pno`)
);
USE Test;
CREATE TABLE `InList`(
`Iid` INT AUTO_INCREMENT COMMENT '入库序号' PRIMARY KEY,
`Pno` CHAR(8) COMMENT '货品编号',
`Wno` CHAR(3) COMMENT '仓库编号',
`Mno` CHAR(3) COMMENT '管理员编号',
`Idate` DATE COMMENT '入库日期',
`Isupplier` VARCHAR(30) COMMENT '供应商名称',
`Iprice` FLOAT COMMENT '入库单价',
`Inum` INT COMMENT '入库数量'
);
USE Test;
CREATE TABLE `OutList`(
`Oid` INT AUTO_INCREMENT COMMENT '出库序号' PRIMARY KEY,
`Pno` CHAR(8) COMMENT '货品编号',
`Wno` CHAR(3) COMMENT '仓库编号',
`Mno` CHAR(3) COMMENT '管理员编号',
`Odate` DATE COMMENT '出库日期',
`Onum` INT COMMENT '出库数量',
`Odest` VARCHAR(30) COMMENT '出库去向'
);
ALTER TABLE ManagerInfo ADD CONSTRAINT fk_Wno FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);
ALTER TABLE InList ADD CONSTRAINT fk_InL_Pno FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);
ALTER TABLE InList ADD CONSTRAINT fk_InL_Wno FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);
ALTER TABLE InList ADD CONSTRAINT fk_InL_Mno FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);
ALTER TABLE OutList ADD CONSTRAINT fk_Out_Pno FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);
ALTER TABLE OutList ADD CONSTRAINT fk_Out_Wno FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);
ALTER TABLE OutList ADD CONSTRAINT fk_Out_Mno FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);