点餐系统数据库

-- 用户信息表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(255)
);

-- 菜单信息表
CREATE TABLE menu (
    menu_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    description TEXT,
    image_url VARCHAR(255)
);

-- 订单信息表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    total_price DECIMAL(10, 2),
    status VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 订单详情表
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    menu_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (menu_id) REFERENCES menu(menu_id)
);

-- 查询某个用户的订单信息
SELECT o.order_id, o.total_price, o.status, m.name AS menu_name, m.price AS menu_price, od.quantity
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN menu m ON od.menu_id = m.menu_id
WHERE o.user_id = 1;