【MySQL 08】复合查询
目录
1.准备工作
2.多表查询
笛卡尔积
多表查询案例
3. 自连接
4.子查询
1.单行子查询
2.多行子查询
3.多列子查询
4.在from子句中使用子查询
5.合并查询
1.union
2.union all
1.准备工作
如下三个表，将作为示例，理解复合查询
- EMP员工表
- DEPT部门表
- SALGRADE工资等级表
2.多表查询
实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。进行多表查询时，表与表就会进行笛卡尔积。
笛卡尔积
什么是笛卡尔积：
数学上，有两个集合A={a,b},B={1,2,3},则两个集合的笛卡尔积={{a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3}} 列出所有情况,一共是2*3=6条记录；在数据库中，笛卡尔积是多表查询没有连接条件时返回的表结果。
笛卡尔积的元素是元组，关系A和关系B的笛卡尔积可以记为（AXB），如果A表a条，B表为b条，那么A和B的笛卡尔积为（a+b）列数，有（a*b）行的元素集合。检索出来的条目是将第一个表中的行数乘以第二个表中的行数。
避免全笛卡尔积 ：在 where 加入有效的连接条件；
消除笛卡尔积：使用等值连接和非等值连接；
例子：
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询
对部分结果截取，由于两张表进行了笛卡尔积，任意一种可能都是存在的，我们可以看到SMITH时20部门的，但是给他拼接了其它部门的信息，这显然是没有意义的，所以我们在进行笛卡尔积的时候是要加过滤条件的。
我们需要通过员工的部门号与对应的部门做关联，这才是正确的。这其实就有点像员工表中的外键，与部门表中的主键做关联。现在两张表就有效的合成了一张表，这张表的信息肯定是准确无误的，我们可以对这张大表多增删查改。
多表查询案例
1.显示雇员名、雇员工资以及所在部门的名字
雇员名、雇员工资是存在于emp表中的，二所在部门存在于dept表中的，因此我们要同时对emp表和dept进行查询。
select emp.ename, emp.sal, dept.dname: 这部分指定了查询的结果应该包含哪些列。from emp, dept: 这部分指定了查询将要使用的表。在这个例子中，它指定了两个表：emp和dept。注意，这里使用的是表的直接连接（也称为笛卡尔积），但实际的连接条件在WHERE子句中给出。where emp.deptno = dept.deptno: 这是查询的关键部分，它指定了两个表之间的连接条件。这里，它要求emp表中的deptno字段（雇员所属的部门编号）必须与dept表中的deptno字段（部门编号）相匹配。
2.显示部门号为10的部门名，员工名和工资
在上一题的基础上多给个条件就行了。
3.显示各个员工的姓名，工资，及工资级别
姓名和薪资属于emp表，而工资级别属于salgrade表，所以要对两表做笛卡尔积，但薪资要在薪资对应等级的范围内，不然就是错误关系。
3. 自连接
自连接是指在同一张表连接查询，即自己与自己做笛卡尔积。在自连接中要，要取别名才可以。
自连接在处理需要比较表中记录之间的关系时非常有用。例如，你可能有一个包含员工信息的表，并希望找到每个员工的直接上级或下级。例子：
显示员工 FORD 的上级领导的编号和姓名
第一步先筛选出，在自连接表中与FORD有关的信息。
select * from emp e1,emp e2 where e1.ename='FORD';
第二步，我们知道要的不是FORD的信息，而是需要看到它领导的信息。因此我们可以用FORD领导的编号找到它领导的信息。
select * from emp e1,emp e2 where e1.ename='FORD' and e1.mgr=e2.empno;
第三步，我们只需要领导的编号和姓名，那么我们在第二步的基础上，选出我们需要的信息就好了
4.子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
1.单行子查询
该子查询返回的是单行单列的数据，即一个格子
例子：显示SMITH同一部门的员工
那么首先我们就要找出SMITH的部门号，返回的是一个格子。
然后就用SMITH返回的部门号查找同一部门的员工
2.多行子查询
该子查询返回的是多行但是单列的数据
例子：
1.查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
先找出10号部门的工作岗位，这里对工作岗位一般会用到去重操作
然后我们将此作为子查询条件，这里会用到in关键字，只要是上面三种岗位其中一个的就符合筛选条件，当然除10号部门以外的员工。
2.显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
这里我们子查询条件就是30号部门所有的工资，可以对该结果去重。
然后，我们要用到all关键字，工资大于30号部门所有工资的员工，就筛选出来。（当然也可以用大于30号最高工资的方法进行筛选，而不是用all关键字）
3.显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门
的员工）
这里把all关键字换成any关键字就可以了（当然也可以用大于30号最低工资的方法进行筛选，而不是用any关键字）
3.多列子查询
该子查询返回的是多列但是单行的数据
例子：
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
那么首先就要查询SMITH的部门和岗位
然后，只要筛选出deptno和job，与SMITH完全一样的就可以了。
4.在from子句中使用子查询
你可以在
FROM子句中使用子查询来创建一个临时表或派生表。这个临时表在查询的执行期间存在，并且你可以像对待普通表一样对它进行操作，包括选择列、应用过滤条件以及与其他表进行笛卡尔积。这也就是MySQL中一切皆表的思想，只不过在from中使用子查询，所得的临时表要取别名使用。
例子：
1.显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
首先获取各个部门的平均工资，将其看作临时表我们需要部门的平均工资和部门号，我们不存在这种表，因此只能构建临时表。然后通过此表，与员工表做笛卡尔积，筛选出我们需要的数据就可以了。
2.显示每个部门的信息（部门名，编号，地址）和人员数量
使用多表查询方法：统计每个部门的人数，并同时返回部门的名称（
dname）、部门编号（deptno）和位置（loc）。这个查询使用了EMP（员工）表和DEPT（部门）表。并通过
EMP.deptno=DEPT.deptno条件将它们连接起来。然后，它按部门编号、部门名称和部门位置进行分组，并使用count(*)函数来计算每个组中的记录数（即每个部门的人数）。
使用子查询方法：
先查询出每个部门人员数量、部门编号。
一上面查询结果作为临时表，与部门表做笛卡尔积，筛选出符合条件的情况。
5.合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all
union操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。union all操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。1.union
案例：将工资大于25000或职位是MANAGER的人找出来
这里一共有七行数据
使用union进行合并（这里对结果做了去重）
2.union all
接上面例子，发现是没有对结果去重的
