SQL零基础学习笔记（五）

多表查询

一、标量子查询（查询条件设置只与一张表有关）

SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶';

SELECT *
FROM milk_tea AS m1
 WHERE 	m1.sale_price> (
												SELECT m.sale_price 
												FROM milk_tea AS m 
												WHERE m.prod_name='奶茶');
												
												
SELECT m1.*, (SELECT m.sale_price 
							FROM milk_tea AS m 
							WHERE m.prod_name='奶茶')
FROM milk_tea AS m1;																	
									

SELECT p.class,AVG(p.sale_price) 
FROM prod_info AS p 
GROUP BY class
HAVING AVG(p.sale_price) > 15;


SELECT p.class,AVG(p.sale_price) 
FROM prod_info AS p 
GROUP BY class
HAVING AVG(p.sale_price) > (SELECT m.sale_price 
														FROM milk_tea AS m 
														WHERE m.prod_name='奶茶') ;
														
														
														
SELECT AVG(p.sale_price)
FROM prod_info AS p	
WHERE p.class='日用品'; ---34.96
												
												
SELECT *
FROM prod_info AS P1
WHERE p1.class='日用品'
AND p1.sale_price >( SELECT AVG(p.sale_price)
										FROM prod_info AS p	
										WHERE p.class='日用品');
										
										

二、关联子查询（多张表有关）

***子查询的结果与主查询的目标列存在一定关联***

										
SELECT *
FROM prod_info AS P1
WHERE p1.sale_price >(#关联子查询
										SELECT AVG(p.sale_price)
										FROM prod_info AS p	
										WHERE p.class=p1.class);

三、普通子查询

******将子查询的结果列作为主查询的取值范围*****

                             *****或作为主查询新的检索表*****

SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price= 15;

SELECT * FROM milk_tea AS m WHERE m.prod_name IN('奶茶','薯片','薯条');



SELECT * 
FROM milk_tea AS m 
WHERE m.prod_name IN(
											SELECT m1.prod_name 
											FROM milk_tea AS m1 
											WHERE m1.sale_price= 15);

											
SELECT p.prod_name, p.type, p.sale_price FROM prod_info AS p WHERE p.prod_name='抽纸';

SELECT b.type
FROM (		#将选出的三列作为新的目标表
			SELECT p.prod_name, p.type, p.sale_price FROM prod_info AS p
			 WHERE p.prod_name='抽纸') AS b 
WHERE b.sale_price > 26;

四、表联结

     *****  在一条SELECT语句中实现关联多个表，在一组中输出 *****

（一）内部联结 INNER JOIN——必须有共同列

基本上只用于联结两个表,不太适合多个表联结

SELECT p.*
FROM prod_info AS p, order_list AS l 
WHERE p.prod_id=l.prod_id
AND l.order_id='20190403001';

SELECT p.*
FROM prod_info AS p INNER JOIN order_list AS l 
ON p.prod_id=l.prod_id
AND l.order_id='20190403001';

（三）外部联结——必须有共同列

输出结果包含了在相关表中没有关联行的行的联结方式就是外联结

LEFT OUTER JOIN 还是  RIGHT OUTER JOIN  就是设定展示哪边的表的所有行

FULL OUTER JION

SELECT * FROM cust_info AS c;
SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%';
 
SELECT c.*,l.*
FROM cust_info AS c LEFT JOIN order_list AS l
ON c.cust_id=l.cust_id
AND l.order_id LIKE '20190407%';

四、在联结中使用聚合函数

ORDER BY

SELECT c2.cust_id,COUNT(c2.prod_id)
FROM(SELECT c.cust_id,l.prod_id,l.prodname,l.order_id
		FROM cust_info AS c 
		LEFT OUTER JOIN order_list AS l 
		ON c.cust_id=l.cust_id
		AND l.order_id LIKE '20190401%'
) AS c2
GROUP BY c2.cust_id;


SELECT c.cust_id,COUNT(l.prod_id)
FROM cust_info AS c 
LEFT OUTER JOIN order_list AS l 
ON c.cust_id=l.cust_id
AND l.order_id LIKE '20190401%'
GROUP BY c.cust_id;

五、组合查询 UNION

自动去重/不去重：        UNION ALL

没两条SELECT之间 UNION 一次

相同的列/表达式/聚合函数

SELECT *
FROM order_list AS l
WHERE l.order_id LIKE '20190407%';
UNION
SELECT *
FROM order_list AS l
WHERE l.order_id LIKE '20190409%';