多表查询练习
练习题:
创建两张，一个是商品表 products，另一个是订单表 orders。请按照以下要求进行操作：
1.创建商品表(products)和订单表(orders)，其中商品表包含字段：id（自动增长主键）、name（商品名称）、price（商品价格）；
2.订单表包含字段：id（自动增长主键）、product_id（关联商品表的商品ID）、quantity（商品数量）、total_price（订单总价）。
3.将订单表中的 product_id 与商品表的 id 建立外键关联。
参考答案:
创建商品表和订单表:
-- 创建商品表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
-- 创建订单表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
quantity INT,
total_price DECIMAL(10, 2),
FOREIGN KEY (product_id) REFERENCES products(id)
);
为 products 表和 orders 表添加一些模拟数据。
-- 添加产品数据到 products 表
INSERT INTO products (name, price) VALUES
('冰箱', 999.99),
('电视', 1499.99),
('电饭锅', 99.99),
('洗衣机', 599.99);
-- 添加订单数据到 orders 表
INSERT INTO orders (product_id, quantity, total_price) VALUES
(1, 2, 1999.98),
(2, 1, 1499.99),
(3, 3, 299.97),
(1, 3, 2999.97),
(4, 2, 1199.98);
多表查询的练习题：
1.查询所有商品的信息。
SELECT * FROM products;
2.查询订单编号为1的订单的详细信息（包括商品信息）。
SELECT * FROM orders
JOIN products ON orders.product_id = products.id
WHERE orders.id = 1;
3.查询订单总价超过100的订单的信息。
SELECT * FROM orders
WHERE total_price > 100;
4.查询商品表中价格最高的商品信息。
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);
5.查询订单表中购买数量最多的商品信息。
SELECT * FROM products
WHERE id = (SELECT product_id FROM orders
GROUP BY product_id
ORDER BY SUM(quantity) DESC
LIMIT 1);
6.查询每个商品的销售总量。
SELECT products.name, SUM(orders.quantity) AS total_sales
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name;
7.查询每个商品的销售总额（销售总量 * 商品价格）。
SELECT products.name, SUM(orders.quantity * products.price) AS total_revenue
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name;
8.查询没有任何订单的商品信息。
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM orders);
9.查询每个订单的商品数量和总价。
SELECT orders.id, SUM(orders.quantity) AS total_quantity, orders.total_price
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY orders.id;
10.查询每个订单的商品名称和价格。
SELECT orders.id, products.name, products.price
FROM orders
JOIN products ON orders.product_id = products.id;
11.查询订单总数和总销售额。
SELECT COUNT(*) AS total_orders, SUM(total_price) AS total_revenue
FROM orders;
12.查询每个商品的订单数和总销售额。
SELECT products.name, COUNT(*) AS total_orders, SUM(total_price) AS total_revenue
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name;
13.查询每个商品的平均价格。
SELECT products.name, AVG(products.price) AS avg_price
FROM products
GROUP BY products.name;
14.查询每个商品的销售额占总销售额的百分比。
SELECT products.name, (SUM(orders.total_price) / (SELECT SUM(total_price) FROM orders)) * 100 AS sales_percentage
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name;
15.创建users添加用户和orders关联，orders添加是否发货字段。
-- 创建users
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(64) not null
);
INSERT INTO users(name) values("张三"),("李四");
ALTER TABLE `orders`
ADD COLUMN `user_id` int NULL AFTER `id`,
ADD COLUMN `shipped` tinyint NULL DEFAULT 0 AFTER `total_price`,
ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
update orders set user_id = 1 where id in (1,2,5);
update orders set user_id = 2 where id in (3,4);
update orders set shipped = 1 where id in (2,4);
16.查询已下单并且已发货的订单信息。
SELECT * FROM orders
WHERE shipped = 1;
17.查询购买商品数量最多的前5位用户信息。
SELECT users.name, SUM(orders.quantity) AS total_quantity
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY users.name
ORDER BY total_quantity DESC
LIMIT 5;
18.查询购买了特定商品的用户数量。
SELECT COUNT(DISTINCT orders.user_id) AS total_users
FROM orders
WHERE orders.product_id = 1;
19.查询购买同一商品的用户数量最多的商品信息。
SELECT products.name, COUNT(DISTINCT orders.user_id) AS total_users
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name
ORDER BY total_users DESC
LIMIT 1;
20.查询每个用户的购买商品数量和金额。
SELECT users.name, SUM(orders.quantity) AS total_quantity, SUM(orders.total_price) AS total_amount
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY users.name;