【MySQL系列】MySQL复合查询的学习 _ 多表查询 | 自毗连 | 子查询 | 合并查 ...

「媒介」文章内容大抵是对MySQL复合查询的学习。
  「归属专栏」MySQL
  「主页链接」个人主页
  「笔者」枫叶老师(fy)
  

  
一、基本查询回顾

前面篇章讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够，下面将讲解复合查询，首先回顾一下基本的查询。
利用的数据库是之前篇章的雇员信息表，员工表（emp）、部门表（dept）和工资等级表（salgrade）

   查询工资高于500或岗位为MANAGER的雇员，同时还要满意他们的姓名首字母为大写的J

1. mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

复制代码

   按照部门号升序而雇员的工资降序排序

1. mysql> select * from emp order by deptno asc, sal desc;

复制代码

   利用年薪进行降序排序

1. mysql> select ename, sal*12+ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;

复制代码

注：

• 由于NULL与任何值做计算得到的结果都是NULL，因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金，这样可能导致得到的年薪为NULL值。
  
• 在计算每个员工的年薪时，应该通过ifnull函数判断员工的奖金是否为NULL，假如不为NULL则ifnull函数返回员工的奖金，假如为NULL则ifnull函数返回0，避免让NULL值到场计算
  

   显示工资最高的员工的名字和工作岗位
  办理该题目需要进行两次查询

别的，这种题目还可以利用子查询，将两句查询语句合并起来，需要将第一次查询的SQL语句用括号括起来。

1. mysql> select ename, job from emp where sal = (select max(sal) from emp);

复制代码

   显示工资高于均匀工资的员工信息
  也是利用子查询办理

1. mysql> select * from emp where sal > (select avg(sal) from emp);

复制代码

   显示每个部门的均匀工资和最高工资
  在group by子句中指明按照部门号进行分组，在select语句中利用avg函数和max函数，分别查询每个部门的均匀工资和最高工资

1. mysql> select deptno, format(avg(sal), 2) 平均, max(sal) 最高 from emp group by deptno;

复制代码

   显示均匀工资低于2000的部门号和它的均匀工资
  在group by子句中指明按照部门号进行分组，在select语句中利用avg函数查询每个部门的均匀工资，在having子句中指明筛选条件为均匀工资小于2000

1. mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;

复制代码

   显示每种岗位的雇员总数，均匀工资

1. mysql> select job, count(*) 人数, format(avg(sal), 2) 平均工资 from emp group by job;

复制代码

二、多表查询

上面的底子查询都是在一张表的底子上进行的查询，实际开发中往往数据来自差别的表，所以需要多表查询。

• 在进行多表查询时，只需要将多张表的表名依次放到from子句之后，用逗号隔开即可，这时MySQL将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据源
  
• 多表查询的本质，就是对给定的多张表取笛卡尔积，然后在产生的新表进行查询
  

笛卡尔积是指给定两个集合A和B，此中A中的每个元素和B中的每个元素都可以组成一个有序对，这些有序对的集合就是A和B的笛卡尔积。
例如，员工表和部门表进行笛卡尔积
员工表：

部门表：

两张表进行笛卡尔积

1. mysql> select * from emp, dept;

复制代码

员工表和部门表的笛卡尔积由两部门组成，前半部门是员工表的列信息，后半部门是部门表的列信息

对员工表和部门表取笛卡尔积时，会先从员工表中选出一条记录与部门表中的全部记录进行组合，然后再从员工表中选出一条记录与部门表中的全部记录进行组合，以此类推，终极得到一张新表

对多张表取笛卡尔积后得到的数据并不都是有意义的。
比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要从笛卡尔积产生的新表筛选出员工的部门号和部门的编号相称记录。

留意：进行笛卡尔积的多张表中可能会存在相同的列名，这时在选中列名时需要通过表名.列名的方式进行指明，假如有重复的不指明白切一列，就会报错。

   显示雇员名、雇员工资以及所在部门的名字
  从题意可以看出，部门名只有dept表中才有，其他数据泉源于emp表，即数据来自EMP和DEPT表，因此要联合查询，即多表查询

1. mysql> select emp.ename, emp.sal, dept.deptno from emp, dept where emp.deptno = dept.deptno;

复制代码

   显示部门号为10的部门名，员工名和工资
  部门名只有部门表中才有，员工名和员工工资只有员工表中才有，因此需要同时利用员工表和部门表进行多表查询，在where子句中指明筛选条件为员工的部门号即是部门编号（筛选符合条件的信息）

1. mysql> select ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;

复制代码

留意：在筛选部门号即是10的部门时，可以利用员工表中的部门号，也可以利用部门表中的部门编号，因为两列都是一样的。
   显示各个员工的姓名，工资，及工资级别
  员工名和工资只有员工表中才有，而工资级别只有工资等级表中才有，因此需要同时利用员工表和工资等级表进行多表查询，在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录

1. mysql> select ename, sal, grade from emp, salgrade where sal between losal and hisal;

复制代码

三、自毗连

自毗连是指在同一张表进行毗连查询，也就是说我们不仅可以对差别表进行取笛卡尔积，也可以对同一张表取笛卡尔积。
   显示员工FORD的上级向导的编号和姓名
  可以利用子查询，先对员工表进行查询得到FORD的向导的编号，然后再根据向导的编号对员工表进行查询得到FORD向导的姓名

1. mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');

复制代码

也可以利用多表查询（自查询），因为员工表中的mgr字段可以或许将表中员工的信息和员工向导的信息关联起来。

1. mysql> select leader.empno, leader.ename from emp leader, emp worder where leader.empno = worder.mgr and worder.ename = 'FORD';

复制代码

注 由于自毗连是对同一张表取笛卡尔积，因此在自毗连时至少需要给一张表取别名，否则无法区分这两张表中的列。
四、子查询

• 子查询是指嵌入在其他SQL语句中的查询语句，也叫嵌套查询
  
• 子查询可分为单行子查询、多行子查询、多列子查询，以及在from子句中利用的子查询
  

4.1 单行子查询

单行子查询，是指返回单行单列数据的子查询
   显示SMITH同一部门的员工
  在子查询中查询SMITH所在的部门号，在where子句中指明筛选条件为员工部门号即是子查询返回的部门号

1. mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

复制代码

别的，办理该题目也可以利用自毗连
4.2 多行子查询

多行子查询，是指返回多行单列数据的子查询
   利用in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包罗10自己的
  先查询10号部门有哪些工作岗位，在查询时要对结果进行去重，因为10号部门的某些员工的工作岗位可能是相同的

然后将上述查询作为子查询，在查询员工表时在where子句中利用in关键字，in关键字用于判断员工的工作岗位是子查询得到的若干岗位中的一个

2. mysql> select ename, job, deptno from emp
3.     -> where job in (select distinct job from emp where deptno=10) and deptno<>10;

复制代码

   实用all关键字；显示工资比部门30的全部员工的工资高的员工的姓名、工资和部门号
  先查询30号部门员工的工资，进行去重

将上述查询作为子查询，在查询员工表时在where子句中利用all关键字，all关键字用于判断员工的工资是否高于子查询得到的全部工资

1. mysql> select ename, sal, deptno from emp where sal > all(select distinct sal from emp where deptno=20);

复制代码

   利用any关键字；显示工资比部门30的恣意员工的工资高的员工的姓名、工资和部门号（包罗自己部门的员工）
  先查询30号部门员工的工资，然后在查询员工表时在where子句中利用any关键字，判断员工的工资是否高于子查询的得到的工资中的某一个

1. mysql> select ename, sal, deptno from emp where sal > any(select distinct sal from emp where deptno=30);

复制代码

4.3 多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
   查询和SMITH的部门和岗位完全相同的全部雇员，不含SMITH本人
  先查询SMITH所在部门的部门号和他的岗位，然后将上述查询作为子查询

1. mysql> select * from emp where (deptno,job) = (select deptno, job from emp where ename = 'SMITH') and ename <> 'SMITH';

复制代码

注：

• 多列子查询得到的结果是多列数据，在比较多列数据时需要将待比较的多个列用圆括号括起来
  
• 多列子查询返回的假如是多行数据，在筛选数据时也可以利用in、all和any关键字
  

4.4 在from子句中利用子查询

• 子查询语句不仅可以出现在where子句中，也可以出现在from子句中
  
• 子查询语句出现from子句中，其查询结果将会被当作一个临时表利用
  

   显示每个高于自己部门均匀工资的员工的姓名、部门、工资、均匀工资
  先查询每个部门的均匀工资，这张表当做临时表利用

然后对员工表和上述的查询结果进行多表查询，在where子句中指明筛选条件为员工的部门号即是临时表中的部门号，并且员工的工资大于临时表中的均匀工资

1. mysql> select ename, emp.deptno, sal, 平均工资 from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) tmp
2.     -> where emp.deptno=tmp.deptno and sal > 平均工资;

复制代码

留意：在from子句中利用子查询时，必须给子查询得到的临时表取一个别名，否则查询将会出错
   查找每个部门工资最高的人的姓名、工资、部门、最高工资
  先查询每个部门的最高工资

然后对员工表和上述的查询结果进行取笛卡尔积，在where子句中指明筛选条件为员工的部门号即是临时表中的部门号，并且员工的工资即是临时表中的最高工资

1. mysql> select ename, sal, emp.deptno, 最高工资 from emp, (select max(sal) 最高工资, deptno from emp group by deptno) tmp
2.     ->  where emp.deptno=tmp.deptno and sal=最高工资;

复制代码

   显示每个部门的信息（部门名，编号，地址）和职员数量
  按照部门号进行分组，分别查询每个部门的职员数量

述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号即是临时表中的部门号即可

1. mysql> select dname, dept.deptno, loc, 部门人数 from dept, (select deptno, count(*) 部门人数 from emp group by deptno)
2.     -> tmp where dept.deptno = tmp.deptno;

复制代码

上述也可以只利用多表查询办理

1. mysql> select dname, dept.deptno, loc, count(*) 人数 from emp, dept
2.     -> where emp.deptno = dept.deptno
3.     -> group by dept.deptno, dname, loc;

复制代码

五、合并查询

合并查询，是指将多个查询结果进行合并，关键字union和union all

• union用于取得两个查询结果的并集，union会自动去掉结果集中的重复行
  
• union all也用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行
  

   将工资大于2500或职位是MANAGER的人找出来
  查询工资大于2500的员工，查询职位是MANAGER的员工

可以利用or操作符将where子句中的两个条件关联起来

也可以利用union将上述的两条查询SQL毗连起来，这时将会得到两次查询结果的并集，并且会对合并后的结果进行去重

1. mysql> select ename, job, sal from emp where sal > 2500 union
2.     -> select ename, job, sal from emp where sal > 2500 or job = 'MANAGER';

复制代码

可以利用union all，结果是不去重

留意：待合并的两个查询结果的列的数量必须一致，否则无法合并
--------------------- END ----------------------

1. 「 作者 」 枫叶先生
2. 「 更新 」 2023.8.25
3. 「 声明 」 余之才疏学浅，故所撰文疏漏难免，
4.           或有谬误或不准确之处，敬请读者批评指正。

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。