MySQL复合查询:
- 基本查询回顾
- 多表查询
- 自连接
- 子查询
-
- 单行子查询
- 多行子查询
- 多列子查询
- 在from子句中使用子查询
- 合并查询
-
- union
- union all
基本查询回顾
准备测试表:Oracle 9i经典测试表 中的group by 子句的使用 这个目录下。
其中包含三张表,分别是员工表(emp)、部门表(dept)、工资登记表(salgrade)。
三张表中包含的字段分别如下:
查询工资高于500或岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J
使用 where 子句筛选满足工资或岗位的人员,并且通过模糊匹配指明员工的姓名首字母为大写的 ‘J’,如下:
按照部门号升序而雇员的工资降序排序
在 order by 子句中按照顺序指明按部门号升序,工资降序进行排列。即不同部门员工按照部门号升序排,同一部门员工按照工资降序排:
使用年薪进行降序排序
显示工资最高的员工的名字和工作岗位
子查询(select max(sal) form emp)返回 “emp” 表中的最高薪资,而外部查询选择具有该薪资值的员工的姓名和工作岗位:
注意:若有多个员工具有最高薪资,则此查询将返回这些员工的姓名和工作岗位。
显示工资高于平均工资的员工信息
显示每个部门的平均工资和最高工资
使用 group by 子句时,查询结果将按照指定的列进行分组,而聚合桉树(avg ,max)将对每个分组计算一次结果:
显示平均工资低于2000的部门号和它的平均工资
having 子句是在 group by 子句之后执行筛选,它用于筛选分组后的结果集,其中的条件是基于聚合函数的计算结果进行的筛选:
显示每种岗位的雇员总数,平均工资
多表查询
单表查询是在一张表的基础上进行的查询。在实际开发中,需要进行表关联查询,将多张表进行关联之后再进行查询。在进行多表查询时,只需将多张表放在 from 子句之后,用逗号进行分隔,这时 MySQL 将会对给定的表进行笛卡尔积运算,然后在笛卡尔积中进行查询。
多表取笛卡尔积案例:
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表,因此要联合查询
说明:
- 员工表和部门表的笛卡尔积由两部分组成,前半部分是员工表的列信息,后半部分是部门表的列信息。
- 其中第一个表中的每一行都会与第二个表中的每一行进行匹配,将它们合并成一个结果集。
使用笛卡尔积对多张表进行连接之后的数据并不是都有意义的,比如员工表和部门表取笛卡尔积时,员工表中的每一条记录都会与部门表中的每一条记录进行组合,但是实际上每个员工与自己对应的部门组合才是有意义的,因此需要使用条件筛选出合理的记录。如下:
说明:在进行笛卡尔积的多张表中可能有相同的列名,因此在指定列名时需要通过 表名.列名
的方式进服务器托管网行指明。
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表,因此要联合查询:
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
显示部门号为10的部门名,员工名和工资
select dname,ename,sal from emp,dept where emp.deptno=dept.deptno and emp.deptno=10;
在这个查询中,使用了两个表 emp 和 dept,它们通过共同的字段 deptno 进行联接。在 where 条件中筛选符合的数据,并限制查询出的信息部门号为10:
显示各个员工的姓名,工资,及工资级别
select ename,sal,grade from emp,salgrade where sal between losal and hisal;
自连接
MySQL 中的自连接是指在同一张表中进行联接操作,即将同一张表的不同行进行连接。自连接可以实现许多复杂查询。
语法:
SELECT ...
FROM table1 t1, table1 t2
WHERE t1.column = t2.column;
说明:table1 是需要进行自连接的表名,t1 和 t2 是该表的别名,可以在查询时使用它们来区分不同的表。column 是需要进行连接的字段,可以是表中的任意字段。
示例:显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号–empno)。
方法一:使用子查询
select empno,ename from emp where empno=(select mgr from emp where ename='FORD');
方法二:使用多表查询(自查询)
select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='FORD';
from emp leader,emp worker,给自己的表起别名,因为要先做笛卡尔积,所以别名可以进行识别。筛选时在 where 子句后写明条件:
子查询
MySQL 中的子查询是嵌套在另一个查询语句中的查询语句。它可以将内层查询的结果作为外层查询的条件或数据源,从而实现更复杂的查询功能。
子查询可分为单行子查询、多行子查询、多列子查询,以及在 from 子句中使用的子查询。
单行子查询
单行子查询,它返回一行数据,通常用于作为外层查询中的一个值或条件。
显示SMITH同一部门的员工
select *from emp where deptno=(select deptno from emp where ename='SMITH') and ename'SMITH';
筛选员工的条件是与 SMITH 同一部门,因此在 where 子句中嵌套一个子查询,该子查询返回 SMITH 所在的部门,然后外层查询按照该条件查询所有符合条件的员工。题中查询与 SMITH 同一部门的员工,因此,这里不用显示 SMITH 的信息了。
此题,我们也可以使用自连接完成,因为与 SMITH 同一部门的员工信息都在该表中,因此将该表进行自连接,在 where 子句后指明条件就可以完成此题。注意:同一个表进行自连接之后需要指明每一个字段使用哪个表,然后用对应表的别名进行指定。
select t2.*from emp t1,emp t2 where t1.ename='SMITH' and t1.deptno=t2.deptno and t2.ename'SMITH';
多行子查询
多行子查询是指一个查询语句中嵌套了另一个查询语句,并且内部查询语句返回多个行。
in关键字;查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno10;
在该查询中,我们需要先找出 10 号部门包含哪些工作(distinct 进行去重),然后将该查询语句作为内层查询条件嵌套进外层查询中,在外层查询中,使用 in 关键字,表示查询在内层查询结果中出现过的 job。然后使用 除去 10 号部门。
all关键字;显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);
外层 select 之后列出所有需要显示的列,where 条件后嵌套 select 查询子句,内层查询子句查出部门号为 30 的所有员工的工资集合,然后对该条件的查询结果使用 all 关键字,如下所示,表示外层查询出的 sal 大于内层查询集合中的每一个数据。
这里不使用 all 关键字也可以,如下:
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);
any关键字;显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);
如下:sal > any(select sal from emp where deptno=30服务器托管网) 表示薪资大于 select 筛选出来的结果集合中的任何一个数据即可。
也可以不使用 any 关键字,大于任意一个数据,即大于最小的那个数据即可。
select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);
多列子查询
单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句。
查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人
select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename'SMITH';
注意,where 后的筛选条件需要与 select 筛选出来的列的顺序一致。例如:若写为 where (deptno,job)=(select job,deptno from emp where ename=‘SMITH’) 则查询不到相应的结果。
说明:
- 多列子查询得到的结果是多列数据,在比较多列数据时使用 () 将条件阔起来。
- 多列子查询返回的是多行数据,筛选数据时也可以使用 in、all、any 关键字。
在from子句中使用子查询
在 MySQL 中,我们可以在 from 子句中使用子查询来生成虚拟表格,以便于在查询中引用它并进行进一步的操作。
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename,deptno,sal,avg_sal from emp,(select avg(sal) avg_sal,deptno dt from emp group by deptno) tmp where emp.sal>tmp.avg_sal and emp.dep.deptno=tmp.dt;
首先,子查询 (select avg(sal) avg_sal,deptno dt from emp group by deptno) tmp
用于计算每个部门的平均工资,它返回一个临时表 tmp ,包含两个列,一个列是平均工资 avg_sal ,一列是部门号 dt。然后,在主查询中,将 emp 表和 tmp 表进行内连接查询,它们的关联条件是 emp 表的 deptno 列和 tmp 表的 dt 列相等。最后,通过 where 子句筛选出 emp 表中的工资高于本部门平均工资的员工,筛选条件为 emp.sal>tmp.avg_sal
,并且在 select 子句中返回指定的列即可。
注意:在 from 子句中使用子查询时,需要为子查询得到的临时表指定别名,否则查询将会失败。
查找每个部门工资最高的人的姓名、工资、部门、最高工资
select ename,sal,deptno,max_sal from emp,(select deptno dt,max(sal) max_sal from emp group by deptno) tmp where emp.sal=tmp.max_sal and emp.deptno=tmp.dt;
首先,子查询 select deptno dt,max(sal) max_sal from emp group by deptno
用于计算每个部门的最高工资,它返回一个临时表 tmp,包含两个列,一个列是部门 dt,一个列是最高工资 max_sal 。然后,在主查询中将 emp 表和 tmp 表进行内连接查询,关联条件 emp.deptno=tmp.dt
。最后通过 where 子句筛选出 emp 表中每个部门的最高工资的员工,筛选条件 emp.sal=tmp.max_sal
,并且在 select 子句中返回指定的列即可。
显示每个部门的信息(部门名,编号,地址)和人员数量
方法一:使用多表
select dept.dname,dept.deptno,dept.loc,count(*) '部门人数' from emp,dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;
需要显示的目标信息出现在两个表中,可以将两个表连接起来,使用 deptno 对两个表产生关联。然后在 where 子句后使用相应条件进行查询。
说明:
-
在 select 语句中新增了要显示部门名和所在地址,因此需要在 group by 中也添加这两个字段,表示当部门号相同时按照部门名进行分组,部门名也相同时按照所在地进行分组。
-
在上述题目中部门号相同的记录,它们的部门名和所在地址也一定相同,因此在 group by 后面添加这两个字段没有什么意义,但是 MySQL 语句要求必须添加。
方法二:使用子查询
select dname,dept.deptno,loc from dept,(select count(*) mycnt,deptno dt from emp group by deptno) tmp where dept.deptno=tmp.dt;
子查询 select count(*) mycnt,deptno dt from emp group by deptno
统计了每个部门的员工数,并将结果保存到临时表 tmp 中。该查询使用 count(*)
对 emp 表进行分组统计,分组依据为 deptno。主查询对部门表 dept 和临时表 tmp 使用 deptno 进行关联。在主查询 select 之后依次列出需要查询的列即可。
合并查询
在实际应用中,为了合并多个 select 的执行结果,可以使用集合操作符 union,union all。
union
union 操作用于将两个或多个 select 语句的执行结果组合成一个结果集,并自动去除结果集中重复行。
将工资大于2500或职位是MANAGER的人找出来
方法一:
直接使用 or
对两个条件进行筛选。
select ename,sal,job from emp where sal>2500 or job='MANAGER';
方法二:union
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';
该 SQL 使用了 union 操作符,将两个子查询的结果合并为一个结果集。第一个子查询选出工资大于 2500 的员工,第二个子查询选出职位为 MANAGER 的员工。由于使用了 union 操作符,因此结果集中不会出现重复行。
union all
union all 用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
将工资大于2500或职位是MANAGER的人找出来
使用 union 和 union all 时需要注意以下几点:
- 数据类型必须匹配:union 和 union all 连接的 select 语句中选的列的数据类型必须匹配,若不匹配,需使用 cast() 函数将其转换为相同的数据类型。
- 列数必须相同:连接的 select 语句必须有相同的列数,且列的顺序和数据类型必须匹配。
- 性能影响:由于 union 操作需要将两个或多个结果集合并在一起,所以会增加查询的开销。在处理大量数据时,应该谨慎使用 union 和 union all。
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
高春辉、王春生、朱峰:关于开源创业的15件小事 阅读原文/Zadig 在 Github/Zadig 在 Gitee 推荐阅读:是时候和 Jenkins 说再见了/Zadig vs. Jenkins 详细比对:时代的选择与开发者之选/平台工程和 AI 时代的新 …