【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

接上面例子，发现是没有对结果去重的