MySQL 多表查询与子查询

理解内外连接、自连接和不同形式的子查询。

1.简单连接（笛卡尔积）

select * from 表A join 表B;
select * from 表A，表B;

## 对数据进行单纯的拼接 会产生很多垃圾数据 不会使用

2.有条件连接

2.1内连接

select * from 表A join 表B ON 条件；
## 返回两张表都满足连接条件的部分(交集)

2.2左外连接

select * from 表A left join 表B ON 条件；
## 返回两张表都满足连接条件的部分(交集) + 左表中不能与右表关联的数据 
## 未关联到的数据用<null>填充

2.3 右外连接

select * from 表A right join 表B ON 条件；
## 返回两张表都满足连接条件的部分(交集) + 右表中不能与左表关联的数据 
## 未关联到的数据用<null>填充
## 左表和右表实现的功能基本一样（结果放的前后顺序不一样）

2.4 全连接

union (去重)， union all（不去重）

select * from 表A left join 表B ON 条件；
union
select * from 表A right join 表B ON 条件；
## 返回两张表都满足的条件+左表不满足的+右表不满足的
## 未匹配到的值 用<null> 填充

## union 是用来连接数据的 不是只能用在连接左外连接和右外连接

3.自连接

只有一张表，对这张表进行多次连接。常用场景：树状结构（省市区/组织架构等）

# 自查询
#先筛选出所有的省
select *from area.areas where pid is null;
#给省匹配市
select * from area.areas sheng left join area.areas shi on sheng.id = shi.pid where sheng.pid is null;
#给市匹配区
select * from area.areas sheng left join area.areas shi on sheng.id = shi.pid
                               left join area.areas  qu on shi.id=qu.pid where sheng.pid is null;
#整理显示列
select sheng.id '省ID',sheng.title '省',shi.id '市ID',shi.title '市',qu.id '区ID',qu.title '区' from area.areas sheng left join area.areas shi on sheng.id = shi.pid
                               left join area.areas  qu on shi.id=qu.pid where sheng.pid is null;

#优化思想  不需要在排除垃圾数据 用内连接就可以直接筛掉匹配不上的数据
select* from area.areas sheng join area.areas shi on sheng.id=shi.pid; #sheng的区没了 shi 的省没了

select * from area.areas sheng join area.areas shi on sheng.id = shi.pid
                               join area.areas  qu on shi.id=qu.pid; # shi的区没了 qu的省市都没了

4. 子查询

#将已查询的结果再在查询里作为数据使用（嵌套）
# 三种类型
#1. 查询结果作为新表用于查询---写在from后面
#2. 查询结果作为条件进行筛选---写在where后面
#3. 查询结果作为查询字段---写在select与from中间

# 1.先查询原表，查询出名称与价格，再筛选出价格大于2000
select pname,price from day05_mutiple_tab.table_product;
select * from (select pname,price from day05_mutiple_tab.table_product) as new where new.price>2000;
# 2.查询平均价，筛选出大于平均价格的商品
select avg(price) from day05_mutiple_tab.table_product;
select *from table_product where (select avg(price) from day05_mutiple_tab.table_product) < price;
# 3.查询平均价，展示每个商品与平均价的差价
select avg(price) from day05_mutiple_tab.table_product;
select price -(select avg(price) from day05_mutiple_tab.table_product)as '差值' from table_product;