仓库管理数据库设计
网站编辑2023-05-17 16:54:31731
```sql
CREATE TABLE warehouse (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
capacity INT NOT NULL
);
```
```sql
CREATE TABLE item (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
description VARCHAR(200),
quantity INT NOT NULL,
warehouse_id INT NOT NULL,
FOREIGN KEY (warehouse_id) REFERENCES warehouse(id)
);
```
```sql
CREATE TABLE in_record (
id INT PRIMARY KEY,
item_id INT NOT NULL,
quantity INT NOT NULL,
date DATETIME NOT NULL,
FOREIGN KEY (item_id) REFERENCES item(id)
);
```
```sql
CREATE TABLE out_record (
id INT PRIMARY KEY,
item_id INT NOT NULL,
quantity INT NOT NULL,
date DATETIME NOT NULL,
FOREIGN KEY (item_id) REFERENCES item(id)
);
```
```sql
INSERT INTO warehouse (id, name, address, capacity) VALUES (1, '仓库1', '地址1', 1000);
```
```sql
INSERT INTO item (id, name, description, quantity, warehouse_id) VALUES (1, '物品1', '描述1', 100, 1);
```
```sql
INSERT INTO in_record (id, item_id, quantity, date) VALUES (1, 1, 50, '2021-01-01 10:00:00');
UPDATE item SET quantity = quantity + 50 WHERE id = 1;
```
```sql
INSERT INTO out_record (id, item_id, quantity, date) VALUES (1, 1, 20, '2021-01-02 10:00:00');
UPDATE item SET quantity = quantity - 20 WHERE id = 1;
```
```sql
SELECT item.name, item.quantity, warehouse.name AS warehouse_name FROM item JOIN warehouse ON item.warehouse_id = warehouse.id WHERE item.id = 1;
```
以上是一个简单的仓库管理数据库设计和操作示例。通过这个数据库，我们可以方便地管理仓库和物品的信息，并进行入库和出库操作。当然，这只是一个基础的示例，实际应用中可能需要更复杂的设计和操作。
