|
|
-- 比较条件
|
|
|
-- 找出所有价格大于500元的产品。
|
|
|
SELECT product_name, price FROM products WHERE price > 500;
|
|
|
-- 列出所有总销售额大于1000元的销售订单。
|
|
|
SELECT order_id, total_amount FROM sales_orders WHERE total_amount > 1000;
|
|
|
-- 集合比较
|
|
|
-- 获取所有来自“科技公司”的产品列表。
|
|
|
SELECT p.product_name
|
|
|
FROM products p
|
|
|
JOIN product_suppliers ps ON p.product_id = ps.product_id
|
|
|
JOIN suppliers s ON ps.supplier_id = s.supplier_id
|
|
|
WHERE s.company_name = '科技公司';
|
|
|
-- 查找由“王明”或“李华”负责的所有销售订单。
|
|
|
SELECT so.order_id, so.customer_id, so.total_amount
|
|
|
FROM sales_orders so
|
|
|
JOIN sales_people sp ON so.salesperson_id = sp.salesperson_id
|
|
|
WHERE sp.saleperson_name IN ('王明', '李华');
|
|
|
-- 范围比较
|
|
|
-- 找到2024年1月1日至2024年1月31日之间的所有销售订单。
|
|
|
SELECT * FROM sales_orders
|
|
|
WHERE order_date BETWEEN '2024-10-01' AND '2024-10-31';
|
|
|
-- 列出库存数量在100到200之间的所有产品。
|
|
|
SELECT product_name, stock_quantity
|
|
|
FROM products
|
|
|
WHERE stock_quantity BETWEEN 100 AND 200;
|
|
|
-- 字符串相似比较
|
|
|
-- 搜索名字中包含“华”的所有客户。
|
|
|
SELECT customer_name, phone
|
|
|
FROM customers
|
|
|
WHERE customer_name LIKE '%华%';
|
|
|
-- 找出公司名称以“电子”开头的所有供应商。
|
|
|
SELECT company_name, phone
|
|
|
FROM suppliers
|
|
|
WHERE company_name LIKE '电子%';
|
|
|
-- 多表连接
|
|
|
-- 显示每个销售订单的详细信息，包括订单ID、客户名、销售人员名和总金额。
|
|
|
SELECT so.order_id, c.customer_name, sp.saleperson_name, so.total_amount
|
|
|
FROM sales_orders so
|
|
|
JOIN customers c ON so.customer_id = c.customer_id
|
|
|
JOIN sales_people sp ON so.salesperson_id = sp.salesperson_id;
|
|
|
-- 列出每种产品的供应商及其仓库位置。
|
|
|
SELECT p.product_name, s.company_name, w.location
|
|
|
FROM products p
|
|
|
JOIN product_suppliers ps ON p.product_id = ps.product_id
|
|
|
JOIN suppliers s ON ps.supplier_id = s.supplier_id
|
|
|
JOIN product_warehouses pw ON p.product_id = pw.product_id
|
|
|
JOIN warehouses w ON pw.warehouse_id = w.warehouse_id;
|
|
|
-- 嵌套查询
|
|
|
-- 找到所有购买了“笔记本电脑”的客户。
|
|
|
SELECT DISTINCT c.customer_name
|
|
|
FROM customers c
|
|
|
JOIN sales_orders so ON c.customer_id = so.customer_id
|
|
|
JOIN order_details od ON so.order_id = od.order_id
|
|
|
JOIN products p ON od.product_id = p.product_id
|
|
|
WHERE p.product_name = '笔记本电脑';
|
|
|
-- 列出有退货记录的销售订单。
|
|
|
SELECT so.order_id, so.order_date, so.total_amount
|
|
|
FROM sales_orders so
|
|
|
WHERE so.order_id IN (SELECT r.order_id FROM returns r);
|
|
|
-- EXISTS 查询
|
|
|
-- 检查是否有任何产品没有关联的供应商。
|
|
|
SELECT p.product_name
|
|
|
FROM products p
|
|
|
WHERE NOT EXISTS (
|
|
|
SELECT 1 FROM product_suppliers ps WHERE ps.product_id = p.product_id
|
|
|
);
|
|
|
-- 找出至少有一条退货记录的客户。
|
|
|
SELECT c.customer_name
|
|
|
FROM customers c
|
|
|
WHERE EXISTS (
|
|
|
SELECT 1 FROM returns r
|
|
|
JOIN sales_orders so ON r.order_id = so.order_id
|
|
|
WHERE so.customer_id = c.customer_id
|
|
|
);
|
|
|
-- 进阶查询
|
|
|
-- 计算每个销售人员的总销售额。
|
|
|
SELECT sp.saleperson_name, SUM(so.total_amount) AS total_sales
|
|
|
FROM sales_people sp
|
|
|
JOIN sales_orders so ON sp.salesperson_id = so.salesperson_id
|
|
|
GROUP BY sp.saleperson_name;
|
|
|
-- 统计每个仓库的产品总数。
|
|
|
SELECT w.location, SUM(pw.quantity_in_warehouse) AS total_products
|
|
|
FROM warehouses w
|
|
|
JOIN product_warehouses pw ON w.warehouse_id = pw.warehouse_id
|
|
|
GROUP BY w.location;
|
|
|
-- 找出每个客户的最近一次购买日期。
|
|
|
SELECT c.customer_name, MAX(so.order_date) AS last_purchase_date
|
|
|
FROM customers c
|
|
|
JOIN sales_orders so ON c.customer_id = so.customer_id
|
|
|
GROUP BY c.customer_name;
|
|
|
-- 列出所有销售活动及其对应的总销售额。
|
|
|
SELECT sa.activity_name, SUM(so.total_amount) AS total_sales
|
|
|
FROM sales_activities sa
|
|
|
JOIN sales_orders so ON so.order_date BETWEEN sa.start_date AND sa.end_date
|
|
|
GROUP BY sa.activity_name;
|
|
|
-- 找到最贵的三个产品及其供应商。
|
|
|
SELECT p.product_name, s.company_name, p.price
|
|
|
FROM products p
|
|
|
JOIN product_suppliers ps ON p.product_id = ps.product_id
|
|
|
JOIN suppliers s ON ps.supplier_id = s.supplier_id
|
|
|
ORDER BY p.price DESC
|
|
|
LIMIT 3;
|
|
|
-- 计算每个运输方式的平均单位成本。
|
|
|
SELECT sm.methodname, AVG(sm.cost_per_unit) AS avg_cost
|
|
|
FROM shipping_methods sm
|
|
|
GROUP BY sm.methodname; |
