【MySQL】复合查询
前面我们解说的 mysql 表的查询都是对一张表进行查询，在实际开发中这远远不敷，接下来我们要学习多表查询，即符合查询。一、根本查询回首
接下来我们回首一下以前学的根本查询，我们继承使用雇员表测试表。
[*] 查询工资高于 500 或岗位为 MANAGER 的雇员，同时还要满足他们的姓名首字母为大写的 J
select * from emp
-> where (sal > 500
-> or job = 'MANAGER')
-> and ename like 'J%';
https://i-blog.csdnimg.cn/blog_migrate/cb194b93eb1c98b6bf860ed972e2482f.png
[*] 按照部门号升序而雇员的工资降序排序
select * from emp order by deptno, sal desc;
https://i-blog.csdnimg.cn/blog_migrate/138549e3e9bb636abb31a204922eb528.png
[*] 使用年薪进行降序排序
select ename, sal*12+ifnull(comm, 0) 年薪 from emp order by 年薪 desc;
https://i-blog.csdnimg.cn/blog_migrate/b969a7556f80c4c68b5397ab6887ed6b.png
[*]表现工资最高的员工的名字和工作岗位
使用查询 select 语句：
select ename, job from emp where sal = (select max(sal) from emp);
https://i-blog.csdnimg.cn/blog_migrate/cc548daa836072adbf1b794deac998c4.png
使用分页筛选：
select ename, job from emp order by sal desc limit 1 offset 0;
https://i-blog.csdnimg.cn/blog_migrate/99b8e70f50518f4b7848840d3e2117ac.png
[*] 表现工资高于均匀工资的员工信息
select ename, sal from emp where sal > (select avg(sal) from emp);
https://i-blog.csdnimg.cn/blog_migrate/f10a8e2a698d58bdf078af47dcfbd0fa.png
[*] 表现每个部门的均匀工资和最高工资
select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;
https://i-blog.csdnimg.cn/blog_migrate/2269ffabb4d054ae7207c07837120229.png
[*] 表现均匀工资低于 2000 的部门号和它的均匀工资
select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资<2000;
https://i-blog.csdnimg.cn/blog_migrate/d0914928bb0096fe56d2501d67f7495d.png
[*] 表现每种岗位的雇员总数，均匀工资
select job, count(*), format(avg(sal), 2) from emp group by job;
https://i-blog.csdnimg.cn/blog_migrate/45ba09b5b94e93b01eb43653d0c3dc28.png
二、多表查询
实际开发中往往数据来自不同的表，所以必要多表查询。我们继承使用一个简单的公司管理系统，有三张表 emp，dept，salgrade 来演示如何进行多表查询。
比方，表现雇员名、雇员工资以及所在部门的名字因为上面的数据来自 emp 和 dept 表，因此要联合查询，我们可以使用以下语句进行联合查询：
select * from emp, dept;
上面语句的含义就是将 emp 表和 dept 表进行联合，那么它是怎样进行联合的呢？原理如下图：
https://i-blog.csdnimg.cn/blog_migrate/9b76bc3225c904cb8c2c1191d16e3815.png
将 emp 表的每一个 deptno 与 dept 表的每一个 deptno 进行组合，形成新的一行，当 emp 表中的全部 deptno 和 dept 表中的 deptno 全部组合完成，说明联合完毕，形成新的一个表。此中这种将数据进行穷举组合的方式，我们称作为笛卡尔积。
但是我们会发现，当 emp 中的 deptno 和 dept 中的 deptno 组合时，会出现 deptno 不对应的环境，这种环境对我们来说没有意义，所以我们可以使用 where 把它筛选开，我们还可以使用 表名.字段 指定表现哪一个字段：
select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno=dept.deptno;
https://i-blog.csdnimg.cn/blog_migrate/1799afac13ecb33d0e0f950b7fad53c0.png
如上图，就把两张表联合完成。
下面我们看一些实例：
[*] 表现部门号为 10 的部门名，员工名和工资
select ename, sal, dname from emp, dept
-> where dept.deptno = emp.deptno
-> and emp.deptno = 10;
https://i-blog.csdnimg.cn/blog_migrate/c261d02053ab0768203c57c22f1c4d8d.png
[*] 表现各个员工的姓名，工资，及工资级别
select ename, sal, grade from emp, salgrade
-> where emp.sal between losal and hisal;
https://i-blog.csdnimg.cn/blog_migrate/16c16a096cf63bae9ea5cc2cc0ce7450.png
三、自连接
自连接是指在同一张表连接查询。
比方，表现员工 WARD 的上级领导的编号和姓名（mgr 是员工领导的编号）
[*] 使用子查询
select empno, ename from emp where emp.empno=(select mgr from emp where ename='WARD');
https://i-blog.csdnimg.cn/blog_migrate/a5d533ed3a363eaccc85dd30c2a526cb.png
[*] 使用多表查询（自查询）
select leader.empno, leader.ename
-> from emp leader, emp worker
-> where leader.empno = worker.mgr
-> and worker.ename = 'WARD';
使用到表的别名 - - from emp leader, emp worker，给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别。
https://i-blog.csdnimg.cn/blog_migrate/c6a38ec491d26dd3b914592fcc202288.png
四、子查询
子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询。
1. 单行子查询
返回一行记录的子查询。
比方：
[*] 表现 JAMES 同一部门的员工
select * from emp where deptno = (select deptno from emp where ename = 'JAMES');
https://i-blog.csdnimg.cn/blog_migrate/c649e0b424c51d41a01800ea0967d230.png
2. 多行子查询
返回多行记录的子查询。
[*] in 关键字；查询和 10 号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含 10 自己的
select ename, job, sal, deptno
-> from emp
-> where job in(
-> select distinct job
-> from emp
-> where deptno = 10)
-> and deptno<>10;
https://i-blog.csdnimg.cn/blog_migrate/29b618614e89180849bf279fdd17e669.png
[*] all 关键字；表现工资比部门 30 的全部员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from emp
-> where sal > all(select sal from emp where deptno=30);
https://i-blog.csdnimg.cn/blog_migrate/7975eab7c9feef4664e10776a12a33bc.png
[*] any 关键字；表现工资比部门 30 的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
select ename, sal, deptno from emp
-> where sal > any(select sal from emp where deptno=30);
https://i-blog.csdnimg.cn/blog_migrate/2d3a7f64f0117466f778bb3d01c23615.png
3. 多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。
比方，查询和 WARD 的部门和岗位完全相同的全部雇员，不含 WARD 本人
select ename from emp
-> where (deptno, job)=(select deptno, job from emp
-> where ename='WARD')
-> and ename<>'WARD';
https://i-blog.csdnimg.cn/blog_migrate/350cdbf94da853ffeed00dbd9a3cba34.png
克制目前，目前全部的子查询，全部都在 where 子句中充当判断条件！任何时刻，查询出来的临时结构，本质在逻辑上也是表结构！
4. 在 from 子句中使用子查询
子查询语句出如今 from 子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
实例：
[*]表现每个高于自己部门均匀工资的员工的姓名、部门、工资、均匀工资
先获取各个部门的均匀工资，将其看作临时表
select deptno dt, avg(sal) 平均工资 from emp group by deptno;
然后将各个部门的均匀工资作为 from 的条件：
select ename, deptno, sal, format(平均工资, 2) from emp,
-> (select deptno dt, avg(sal) 平均工资 from emp group by deptno) tmp
-> where emp.sal> tmp.平均工资
-> and emp.deptno = tmp.dt;
https://i-blog.csdnimg.cn/blog_migrate/a426d260382b0e0743d707aef5dcac36.png
[*]查找每个部门工资最高的人的姓名、工资、部门、最高工资
先获取每个部门最高工资的人的信息：
select deptno, max(sal) 最高工资 from emp group by deptno;
再将上面语句作为 from 的条件代入：
select emp.ename, emp.sal, emp.deptno, 最高工资 from emp,
-> (select deptno, max(sal) 最高工资 from emp group by deptno) tmp
-> where emp.deptno = tmp.deptno
-> and emp.sal = tmp.最高工资;
https://i-blog.csdnimg.cn/blog_migrate/9352604cb497d5c7914450a84a23425a.png
[*]表现每个部门的信息（部门名，编号，地址）和人员数量
[*] 使用多表
select dept.dname, dept.deptno, dept.loc, count(*) '部门人数' from emp,
-> dept
-> where emp.deptno = dept.deptno
-> group by dept.deptno, dept.dname, dept.loc;
https://i-blog.csdnimg.cn/blog_migrate/2d25b2f700413796e6ab3e3f72023bd8.png
[*]使用子查询
先对 emp 表的各部门人数进行统计：
select count(*), deptno from emp group by deptno;
将上面的表看作临时表作为 from 条件：
select dept.deptno, dname, 部门人数, loc from dept,
-> (select count(*) 部门人数, deptno from emp group by deptno) tmp
-> where dept.deptno = tmp.deptno;
https://i-blog.csdnimg.cn/blog_migrate/b85f23ea063737d4a220e5664fbba740.png
总结，解决多表问题的本质：想办法将多表转化为单表，所以 mysql 中，全部 select 的问题全部都可以转成单表问题！这就是多表查询的思想！
5. 归并查询
在实际应用中，为了归并多个 select 的执行结果，可以使用集合操作符 union，union all.
（1）union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果会合的重复行。
实例：
[*] 将工资大于 2500 或职位是 MANAGER 的人找出来
select ename, sal, job from emp where sal > 2500 union
-> select ename, sal, job from emp where job = 'MANAGER';
https://i-blog.csdnimg.cn/blog_migrate/d0707b230b3004f18bb5566a87981ddf.png
自动去掉告终果会合的重复记录。
（2）union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果会合的重复行。
实例：
[*] 将工资大于 2500 或职位是 MANAGER 的人找出来
select ename, sal, job from emp where sal > 2500 union all
-> select ename, sal, job from emp where job = 'MANAGER';
https://i-blog.csdnimg.cn/blog_migrate/3eec50c6f7fa54859579c22bf363890d.png
没有将重复记录去掉。
五、练习
[*] 查找全部员工入职时候的薪水环境
[*] 获取全部非manager的员工emp_no
[*] 获取全部员工当前的manager
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]