超市食品信息管理系统运用SQL 作为后台数据库的具体代码
时间: 2024-01-22 16:19:50 浏览: 24
以下是一个简单的超市食品信息管理系统的 SQL 数据库代码:
创建商品表:
```
CREATE TABLE goods (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL,
expiration_date DATE NOT NULL
);
```
创建订单表:
```
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
total_price DECIMAL(10,2) NOT NULL,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```
创建订单详情表:
```
CREATE TABLE order_details (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (goods_id) REFERENCES goods(id)
);
```
添加商品:
```
INSERT INTO goods (name, category, price, stock, expiration_date)
VALUES ('可乐', '饮料', 2.50, 100, '2022-12-31');
```
更新商品信息:
```
UPDATE goods SET price = 2.80 WHERE id = 1;
```
查询所有商品:
```
SELECT * FROM goods;
```
查询某个类别的商品:
```
SELECT * FROM goods WHERE category = '零食';
```
添加订单:
```
INSERT INTO orders (customer_name, total_price) VALUES ('张三', 28.50);
```
添加订单详情:
```
INSERT INTO order_details (order_id, goods_id, quantity, price)
VALUES (1, 1, 2, 5.00);
```
查询某个订单的详情:
```
SELECT g.name, od.quantity, od.price FROM order_details od
JOIN goods g ON od.goods_id = g.id
WHERE od.order_id = 1;
```
以上是一个简单的超市食品信息管理系统的 SQL 数据库代码。具体实现可能会有所不同,这里提供的代码仅供参考。