多表查询

一、标量子查询

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 *FROM prod_info AS p ;
SELECT * FROM supplier_info AS s;



SELECT p.*, s.* 
FROM prod_info AS p, supplier_info AS s 
WHERE p.supplier_id= s.supplier_id;

（二）自联结

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';

（三）外部联结

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%';

INNER JOIN 相当于并集

OUTER JOIN 相当于交集

五、组合查询 UNION