mysql小型超市管理系统
时间: 2023-12-28 10:06:00 浏览: 41
MySQL小型超市管理系统是一个基于MySQL数据库的管理系统,用于管理超市的商品、库存、销售等信息。以下是一个简单的MySQL小型超市管理系统的示例:
1. 创建数据库和表格:
```sql
CREATE DATABASE supermarket;
USE supermarket;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
quantity INT NOT NULL
);
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL,
sale_date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(id)
);
```
2. 插入商品信息:
```sql
INSERT INTO products (name, price, quantity)
VALUES ('Apple', 2.50, 100),
('Banana', 1.50, 200),
('Orange', 3.00, 150);
```
3. 查询商品信息:
```sql
SELECT * FROM products;
```
4. 更新商品库存:
```sql
UPDATE products
SET quantity = quantity - 10
WHERE id = 1;
```
5. 添加销售记录:
```sql
INSERT INTO sales (product_id, quantity, sale_date)
VALUES (1, 5, '2021-01-01'),
(2, 10, '2021-01-02');
```
6. 查询销售记录:
```sql
SELECT sales.id, products.name, sales.quantity, sales.sale_date
FROM sales
JOIN products ON sales.product_id = products.id;
```
7. 计算每个商品的销售总额:
```sql
SELECT products.name, SUM(products.price * sales.quantity) AS total_sales
FROM sales
JOIN products ON sales.product_id = products.id
GROUP BY products.name;
```