二、SQL学习之多表查询

内连接包括等值连接和自连接

自身连接：一个表与其自己进行连接

需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀

等值连接就是连接运算符为=

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组（属性名必须相同,），并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

连接操作的执行过程

---

一、标量子查询：只返回一行一列（相当于一个常数）

--查询价格大于奶茶的商品
1.查询奶茶的价格-------------15 
SELECT m.sale_price FROM milk_tea AS m WHERE m.prod_name='奶茶';
2.查询大于奶茶的价格  >15
SELECT m.prod_name,m.sale_price FROM milk_tea AS m WHERE m.sale_price>15;

合成一个语句--多表查询 把1的语句用（）包起来放在>后面  ps:1语句必须只返回一个单元格 即SELECT后面只能有一个列
SELECT m.prod_name,m.sale_price 
FROM milk_tea AS m 
WHERE m.sale_price>(
		SELECT m.sale_price 
		FROM milk_tea AS m 
		WHERE m.prod_name='奶茶');

二、关联子查询（组内比较）：返回一列-----为表起两个别名，在子查询中加入where p.class=p2.class

1.求出每种类别的均价
SELECT AVG(p.sale_price) FROM prod_info AS p  WHERE p.class='日用品' GROUP BY p.class;
2.每种类别大于均价的商品
SELECT *
FROM prod_info AS p2 
WHERE p2.sale_price>(
                   SELECT AVG(p.sale_price) 
                   FROM prod_info AS p  
                   WHERE p.class=p2.class
                   GROUP BY p.class
                   ); 

 

三、普通子查询-----子查询返回一列数据：

（1）将子查询的结果列，作为主查询的取值范围IN

（2）将子查询的结果作为table表，放在FROM之后

（1）将子查询的结果列，作为子查询的取值范围IN

1.查询各种种类的薯片名称
SELECT p.prod_name FROM prod_info AS p WHERE p.prod_name LIKE '%薯片';

2.查询每种薯片的订单--例如原味薯片
SELECT o.prodname,SUM(o.quantity) FROM order_list AS o WHERE o.prodname='原味薯片';

合二为一：查询每种味道的薯条的订单
SELECT o.prodname,o.quantity FROM order_list AS o WHERE o.prodname IN('原味薯片','黄瓜味薯片','番茄味薯片')

发现存在多个订单，使用groupby对每个种类进行SUM聚合
SELECT o.prodname,SUM(o.quantity) FROM order_list AS o WHERE o.prodname IN('原味薯片','黄瓜味薯片','番茄味薯片') GROUP BY o.prodname;


SELECT o.prodname,SUM(o.quantity) 
FROM order_list AS o 
WHERE o.prodname IN(
		SELECT p.prod_name 
		FROM prod_info AS p 
		WHERE p.prod_name LIKE '%薯片') 
                    GROUP BY o.prodname;

将上述语句结合

（2）将子查询的结果作为table表，放在FROM之后 

SELECT * FROM prod_info AS p WHERE p.prod_name='抽纸';
把上一个语句的查询结果作为一个表b
SELECT * FROM (SELECT * FROM prod_info AS p WHERE p.prod_name='抽纸') AS b WHERE b.cost<20;

 四、内部联结------所有参与的表必须有共同的列（返回结果是共同列重复的行）

内部连接（等值联接）

这是最常用到的一种联接，但实现这种功能需要借助外键的帮助。

此外，内联接的标准写法为

select *

from SC inner join Student

on SC.StuID = Student.StuID;

将SC和Student两张表进行内部联接，实际上，它是以筛选的形式达到此效果的，即将笛卡尔积的结果，添加筛选条件，即保留笛卡尔积结果中满足SC.StuID = Student.StuID的行得到的结果即为内部联接的结果。

通过内部联接，可以将对一张表中的信息进行扩展，从而达到我们想要的查询目的。

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

定义：自然连接是关系R和S在所有公共属性（common attribute）上的等接（Equijoin）。但在得到的结果中公共属性只保留一次，其余删除。

下面我们看一下内联结的优势

 

 五、自联结----必须是同一张表

自连接

有些情况下，需要联接自己才能完成相关的查询操作，此时的联接被称为自连接，此时联接的两张表为，自己和自己，为了避免混淆，需要使用别名进行区分。比如：

select *

from products as p1,products as p2

where p1.ID = p2.ID

六、外部联结 ------所有参与的表必须有共同的列（返回结果包括无关联的行）

left join 是 left outer join 的简写

SELECT c.cust_id,o.quantity
FROM cust_info AS c LEFT JOIN order_list AS o
ON c.cust_id=o.cust_id
AND o.order_id LIKE '20190401%';

七、表联结---再联结中使用聚集函数

1.
SELECT l.cust_id,COUNT(l.prod_id)
FROM (SELECT c.cust_id,o.prod_id
FROM cust_info AS c LEFT JOIN order_list AS o
ON c.cust_id=o.cust_id
AND o.order_id LIKE '20190401%') AS l
GROUP BY l.cust_id;
2.   1.和2.的查询结果相同
SELECT c.cust_id,COUNT(o.prod_id)
FROM cust_info AS c LEFT JOIN order_list AS o
ON c.cust_id=o.cust_id
AND o.order_id LIKE '20190401%'
GROUP BY c.cust_id;

八、组合查询 ------UNION------连接的两个表要有相同的字段名，即每个Select子句查询的列名相同

UNION 去重
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'

UNION ALL不去重
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION ALL
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'