仓库管理系统sql语句的设计
时间: 2023-09-29 19:08:40 浏览: 58
仓库管理系统需要设计多个表来存储不同的信息,以下是一些常见的表及其对应的SQL语句设计:
1. 商品信息表
CREATE TABLE goods_info (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL,
PRIMARY KEY (id)
);
2. 供应商信息表
CREATE TABLE supplier_info (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
address VARCHAR(100),
contact VARCHAR(50),
PRIMARY KEY (id)
);
3. 入库信息表
CREATE TABLE in_stock_info (
id INT NOT NULL AUTO_INCREMENT,
goods_id INT NOT NULL,
supplier_id INT NOT NULL,
quantity INT NOT NULL,
in_date DATETIME NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (goods_id) REFERENCES goods_info(id),
FOREIGN KEY (supplier_id) REFERENCES supplier_info(id)
);
4. 出库信息表
CREATE TABLE out_stock_info (
id INT NOT NULL AUTO_INCREMENT,
goods_id INT NOT NULL,
quantity INT NOT NULL,
out_date DATETIME NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (goods_id) REFERENCES goods_info(id)
);
5. 库存信息表
CREATE TABLE stock_info (
id INT NOT NULL AUTO_INCREMENT,
goods_id INT NOT NULL,
stock INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (goods_id) REFERENCES goods_info(id)
);
这些表可以根据需求进行扩展或者修改,但是需要保证数据之间的关联性和一致性。