MySQL高频基本面试问题整理

好的，以下是一些MySQL多表查询的练习题： 1. 查询订单表中所有订单的详细信息，包括订单号、下单时间、客户名、客户电话、商品名、商品单价、商品数量、商品总价，并按照订单号升序排列。 ``` SELECT o.order_id, o.order_time, c.customer_name, c.customer_phone, p.product_name, p.product_price, od.quantity, od.total_price FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_details od ON o.order_id = od.order_id JOIN products p ON p.product_id = od.product_id ORDER BY o.order_id ASC; ``` 2. 查询所有客户的订单总数和订单总金额，并按照订单总金额降序排列。 ``` SELECT c.customer_name, COUNT(o.order_id) AS order_count, SUM(od.total_price) AS total_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN order_details od ON o.order_id = od.order_id GROUP BY c.customer_name ORDER BY total_amount DESC; ``` 3. 查询订单表中所有订单的商品种类数和商品总数量，并按照订单总数量降序排列。 ``` SELECT o.order_id, COUNT(DISTINCT od.product_id) AS product_count, SUM(od.quantity) AS total_quantity FROM orders o JOIN order_details od ON o.order_id = od.order_id GROUP BY o.order_id ORDER BY total_quantity DESC; ``` 希望这些练习题对你有所帮助！