sql语句写一个超市管理系统
时间: 2023-11-21 10:00:37 浏览: 55
以下是一个简单的超市管理系统的 SQL 数据库架构,包括顾客、商品、订单和库存:
```sql
-- 创建顾客表
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(100) NOT NULL
);
-- 创建商品表
CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
description VARCHAR(500) NOT NULL,
quantity INT NOT NULL
);
-- 创建订单表
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL,
total DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- 创建订单详情表
CREATE TABLE order_details (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 创建库存表
CREATE TABLE inventory (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(id)
);
```
然后,您可以使用 SQL 查询来执行各种操作,例如:
- 添加新客户:
```sql
INSERT INTO customers (name, email, phone, address) VALUES ('John Doe', 'john.doe@example.com', '123-456-7890', '123 Main Street');
```
- 添加新产品:
```sql
INSERT INTO products (name, price, description, quantity) VALUES ('Milk', 2.99, 'Fresh whole milk', 100);
```
- 创建新订单:
```sql
INSERT INTO orders (customer_id, order_date, total) VALUES (1, NOW(), 12.99);
```
- 添加订单详情:
```sql
INSERT INTO order_details (order_id, product_id, quantity, price) VALUES (1, 1, 2, 5.98);
```
- 更新库存数量:
```sql
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 1;
```
- 显示所有客户:
```sql
SELECT * FROM customers;
```
- 显示所有订单:
```sql
SELECT * FROM orders;
```
- 显示某个客户的所有订单:
```sql
SELECT * FROM orders WHERE customer_id = 1;
```
- 显示某个产品的库存数量:
```sql
SELECT quantity FROM inventory WHERE product_id = 1;
```