sql语句超市管理系统
时间: 2023-10-26 15:08:20 浏览: 50
以下是一个简单的超市管理系统的SQL语句示例,包括客户信息、产品信息和订单信息:
创建客户信息表:
```
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
address VARCHAR(100),
phone VARCHAR(20)
);
```
创建产品信息表:
```
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(10, 2),
stock INT
);
```
创建订单信息表:
```
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
```
插入客户信息数据:
```
INSERT INTO customers (customer_id, name, address, phone)
VALUES
(1, '张三', '北京市海淀区', '13888888888'),
(2, '李四', '上海市浦东新区', '13999999999'),
(3, '王五', '广州市天河区', '13666666666');
```
插入产品信息数据:
```
INSERT INTO products (product_id, name, price, stock)
VALUES
(1, '苹果', 5.00, 100),
(2, '香蕉', 3.00, 200),
(3, '饼干', 10.00, 50);
```
插入订单信息数据:
```
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date)
VALUES
(1, 1, 1, 10, '2021-01-01'),
(2, 2, 2, 20, '2021-01-02'),
(3, 3, 3, 5, '2021-01-03');
```
查询客户信息:
```
SELECT * FROM customers;
```
查询产品信息:
```
SELECT * FROM products;
```
查询订单信息:
```
SELECT orders.order_id, customers.name AS customer_name, products.name AS product_name, orders.quantity, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN products ON orders.product_id = products.product_id;
```
更新产品信息:
```
UPDATE products
SET stock = stock - 10
WHERE product_id = 1;
```
以上是一个简单的超市管理系统的SQL语句示例,实际应用中还需要根据具体情况进行调整和优化。