MySQL 小超市数据库设计 - 代码示例

以下是一个简单的 MySQL 小超市数据库的设计代码：

-- 创建商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, quantity INT NOT NULL );

-- 创建顾客表 CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL );

-- 创建订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(id), FOREIGN KEY (product_id) REFERENCES products(id) );

-- 示例数据 INSERT INTO products (name, price, quantity) VALUES ('商品1', 10.99, 100); INSERT INTO products (name, price, quantity) VALUES ('商品2', 5.99, 50); INSERT INTO products (name, price, quantity) VALUES ('商品3', 8.99, 75);

INSERT INTO customers (name, email) VALUES ('顾客1', 'customer1@example.com'); INSERT INTO customers (name, email) VALUES ('顾客2', 'customer2@example.com');

INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 1, 2); INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 2, 1); INSERT INTO orders (customer_id, product_id, quantity) VALUES (2, 1, 3);

-- 查询顾客的订单和商品信息 SELECT c.name AS customer_name, o.quantity, p.name AS product_name, p.price FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id WHERE c.id = 1;