MySQL，练习

表结构参考：MySQL，等值联结、内部联结、多表连接、自联结、自然联结、外部联结、带聚集函数的联结-CSDN博客

1、找出购买了产品id=1023005的客户信息

# 联结三表，再过滤
SELECT customers.*
FROM orderitems,orders,customers
WHERE orderitems.order_num=orders.order_num AND orders.cust_id=customers.cust_id
	AND orderitems.prod_id=1023005;

2、找出产品id=1023005这一订单内的其它产品

SELECT A1.*
FROM orderitems as A1,orderitems as A2
WHERE A1.order_num=A2.order_num
AND A2.prod_id=1023005;
-- 自联结后过滤产品id

3、找出购买了id=1023005的客户，购买的其它产品（一个客户可能有多个订单）

SELECT o.cust_id,oi.order_num,p.*
FROM orderitems as oi,orders as o,products as p
	WHERE oi.order_num=o.order_num AND oi.prod_id=p.prod_id
	AND o.cust_id=(
			SELECT orders.cust_id
			FROM orderitems,orders
			WHERE orderitems.order_num=orders.order_num
				AND orderitems.prod_id=1023005
				);

4、查询同时购买了产品id=2023200和id=2023101的用户信息

SELECT *
FROM 
(SELECT customers.cust_id,customers.cust_name
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=1023200) as t1 
 
 join
 
(SELECT customers.cust_id,customers.cust_name
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=2023101) as t2
 
ON t1.cust_id=t2.cust_id;
-- 1、找出产品id为1023005的对应客户；
-- 2、找出产品id为2023101的对应客户；
-- 3、取2个结果集都存在的用户即可（内联结2个结果集）

5、查询购买了产品id=1023200，也可能购买了id=2023101的用户信息

SELECT *
FROM 
(SELECT customers.cust_id,customers.cust_name,orderitems.prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=2023200) as t1 
 
 left join
 
(SELECT customers.cust_id,customers.cust_name,orderitems.prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=2023101) as t2
 
ON t1.cust_id=t2.cust_id;
-- 1、找出产品id为2023200的对应客户；
-- 2、找出产品id为2023101的对应客户；
-- 3、（左联结2个结果集后）

6、查询购买了产品id=2023101，也可能购买了id=10232002023101的用户信息

SELECT *
FROM 
(SELECT customers.cust_id,customers.cust_name,orderitems.prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=2023200) as t1 
 
 RIGHT join
 
(SELECT customers.cust_id,customers.cust_name,orderitems.prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num
 AND prod_id=2023101) as t2
 
ON t1.cust_id=t2.cust_id;
-- 1、找出产品id为2023200的对应客户；
-- 2、找出产品id为2023101的对应客户；
-- 3、（右join联结2个结果集后，按照cust_id过滤）

7、查询每人每个订单的总花费

SELECT customers.cust_id,customers.cust_name,orders.order_num,SUM(orderitems.item_price)
FROM orders JOIN orderitems JOIN customers
WHERE orders.order_num=orderitems.order_num
	AND customers.cust_id=orders.cust_id
	GROUP BY orders.order_num;
-- 	联结三个表
-- 	带聚合的分组（按每个订单来分组）

8、查询每个人的总花费

SELECT customers.cust_id,customers.cust_name,SUM(orderitems.item_price)
FROM orders JOIN orderitems JOIN customers
WHERE orders.order_num=orderitems.order_num
	AND customers.cust_id=orders.cust_id
	GROUP BY customers.cust_id;
-- 	联结三个表
-- 	带聚合的分组（按每个用户来分组）

9、查询‘张’姓客户的数量

SELECT COUNT(*)
FROM customers
WHERE cust_name LIKE '张%';

10、查询没有购买产品的客户

SELECT *
FROM customers
WHERE cust_id NOT IN (
	SELECT  DISTINCT customers.cust_id
	FROM customers,orders
	WHERE customers.cust_id = orders.cust_id);
	
-- 	先查询出order表的客户id
-- 	再在客户表中取非集，即获取未购物的客户

11、查询消费最高的前三位客户

SELECT customers.cust_id,customers.cust_name,SUM(orderitems.item_price) as zongjia
FROM orders JOIN orderitems JOIN customers
WHERE orders.order_num=orderitems.order_num
	AND customers.cust_id=orders.cust_id
	GROUP BY customers.cust_id
	ORDER BY zongjia DESC
		LIMIT 3;
-- 	联结三个表
-- 	带聚合的分组（按每个用户来分组）