子查询是指嵌入在其他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(*)
函数来计算每个组中的记录数(即每个部门的人数)。
使用子查询方法:
先查询出每个部门人员数量、部门编号。
一上面查询结果作为临时表,与部门表做笛卡尔积,筛选出符合条件的情况。