SQL语句学习——多表查询、子查询（大数据学习）

一、多表查询

1、笛卡尔集

select * from emp , dept;
# 发现有56条数据，组成了一个集合，称之为笛卡尔集。
# 结论：笛卡尔集是没有意义的，只有中间的一些数据有意义。

# 多表关联查询的本质就是从笛卡尔集中过滤有意义的数据。
select * from emp , dept where emp.deptno = dept.deptno;

 2、多表查询

分为： 内连接
            外连接  （左外连接 和 右外连接 ）

 1）内连接

# 类似于方言
select * from emp , dept where emp.deptno = dept.deptno;
# 对应的是标准sql中的内连接。

# 内连接的语法，普通话写法
select 字段... from 表1 inner join 表2 on 条件
# 此时的inner 可以省略。
select * from emp inner join dept on emp.deptno = dept.deptno;

注：内连接：左表和右边的数据，必须有关联，才能显示出来，否则左右两边的数据都不显示。 

2）外连接：（只要学会左外连接就万事大吉！因为右外连接可以用左外连接来写）

左外连接：

# 左外连接语法：
select 字段... from 表1 left outer join 表2 on 条件

# sql语句编写：
select * from emp left outer join dept on emp.deptno = dept.deptno;
# 总结：
# 左外连接的意思是：以左表为主，左表的数据全部显示，能关联上右表的显示，关联不上的显示为null。
# 左连接和右连接 中的 outer 可以省略。
select * from emp left join dept on emp.deptno = dept.deptno;

# 右外连接语法：
select 字段... from 表1 right outer join 表2 on 条件

# sql语句编写：
select * from emp right outer join dept on emp.deptno = dept.deptno;
# 含义：以右表为主，右表的数据全部显示，能关联左表的显示，关联不上的，显示为null

# 所有的右连接都可以写成左连接.
# eg：
select * from emp right outer join dept on emp.deptno = dept.deptno;
# 对应的效果一样的左连接的写法：
select * from dept left outer join emp on emp.deptno = dept.deptno;

# 如果表名特别⻓，每次写关联关系就特别的费劲，可以使⽤别名代替：
select * from dept left outer join emp on emp.deptno = dept.deptno;
# 添加完别名之后的写法：
select * from dept d left  join emp e on e.deptno = d.deptno;

# 如果是想要两个表中的某些字段，该字段必须非常的明确：比如两个表中都有deptno字段，表明要求的字段
select ename,sal,e.deptno,dname from emp e join  dept  d on e.deptno = d.deptno;

注：多表关联的时候，如果一个字段非常的明确，可以不在前面加表名，如果有多个表都有该字段，必须在字段前面指明是哪个表的字段，否则报错！！！ 

二、子查询（难点和重点）

子查询：当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依赖B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包含多个⼦查询。 

子查询中返回的结果一般都是一个值，可以使用 = >  < 等，否则会报错。

子查询出现的地方：

• 在where子句中: 子查询的结果可⽤作条件筛选时使⽤的值。
• 在from子句中: 子查询的结果可充当⼀张表或视图，需要使⽤表别名。
• 在having子句中（一般在group by后面）: 子查询的结果可⽤作分组查询再次条件过滤时使⽤的值
• 在select子句中: 子查询的结果可充当⼀个字段。仅限子查询返回单⾏单列的情况。 

子查询出现在where子句中 （子查询当作条件筛选时使用的值——一个值）

# 1、查询⼯资⼤于10号部⻔的平均⼯资的⾮10号部⻔的员⼯信息
# 分析：
# 1）10号部⻔的平均⼯资
 select avg(sal) from emp where deptno = 10;
# 2）查询员工信息
 select * from emp where deptno != 10 and sal > (select avg(sal) from emp where deptno = 10);

# 2、查询与7369同部⻔的同事信息
select * from emp where deptno = (select deptno from emp where empno = 7369)
  and empno != 7369;

 子查询出现在from子句中（子查询当作一个虚拟表使用）

# 1、查询员⼯的姓名，⼯资，及其部⻔的平均⼯资
# 分析
# 每一个部门的平均工资是多少？
select deptno,avg(sal) from emp group by deptno;
# 此时：查询出来的平均工资表可以当做一个虚拟的表，和emp表关联起来
select * from ( select deptno,avg(sal) avgMoney from emp group by deptno ) avgTable;
# 现在 avgTable 表  和 emp  通过 deptno 相关联
select  ename,sal,avgMoney from emp , 
   (select deptno,avg(sal) avgMoney from emp group by deptno) avgTable   
   where emp.deptno=avgTable.deptno;

# 虚拟机的表解释：--任何的查询结果都可以当做一个虚拟机的表来使用！

子查询出现在select后面 （子查询的结果可以当作一个字段，仅限子查询返回单行单列的情况）

# 2、查询员⼯的姓名，⼯资，及其部⻔的平均⼯资
# 注：说一个情况：emp表和emp表也是可以自己关联自己的。
select * from emp e1, emp e2 where e1.empno = e2.empno;
# 最终的答案：
select ename,sal, (select avg(sal) from emp e2 where e2.deptno = e1.deptno ) from emp e1;
select ename,sal, ( select avg(sal) from emp e2 where e2.deptno = emp.deptno ) from emp;
# 优化一下：工资 保留小数点后2位
select ename,sal, round(
    ( select avg(sal) from emp e2 where e2.deptno = e1.deptno),2) 部门平均工资 
  from emp e1;

# 查询每个员⼯的信息及其部⻔的平均⼯资，⼯资之和，部⻔⼈数
select *,
  (select avg(sal) from emp e1 where e1.deptno = emp.deptno) 平均工资,
  (select sum(sal) from emp e2 where e2.deptno = emp.deptno) 工资之和,
  (select count(*) from emp e3 where e3.deptno = emp.deptno) 部门人数
from emp;


# 也可以用其他形式来写，并不只是一种写法，不过上面为最优解
select ename,sal,平均工资 from emp,(select deptno,avg(sal) 平均工资,sum(sal) 工资和,count(*) 部门人数  from emp group by deptno) avgd where emp.deptno = avgd.deptno;

子查询可以出现在having字句中 （子查询的结果可用作分组查询再次条件过滤时使用的值）

# 查询平均⼯资⼤于30号部⻔的平均⼯资的部⻔号，和平均⼯资
# 分析：30号部⻔的平均⼯资
select avg(sal) from emp where deptno = 30;
# 查询所有部门的平局工资：
select deptno,avg(sal) from emp group by deptno;
# 整合
select deptno,avg(sal) avgMoney from emp group by deptno 
   having avgMoney > (select avg(sal) from emp where deptno = 30);

三、union 和 union all

将两个表的结果集放在一起：

• union：合并结果的时候，相同的数据可以去重。
• union all ：合并结果的时候，没有去重的功能。

注：合并之后以第一个表的字段为主显示，且左右两边的查询结果的列数必须相同，并且列的数据类型也必须相同，列的名称可以不同。如果两张表中的字段列数不相同，只要查询的字段相同即可。

# eg：
select tname,theight,tweight from teacher
    union 
select sname,sheight,sweight from student;