【MySQL】复合查询 | 内外连接

---

一、MySQL复合查询

基本查询回顾

准备测试表

下面给出三张表，员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表(emp) 包含如下字段：

• 雇员编号（empno）
• 雇员姓名（ename）
• 雇员职位（job）
• 雇员领导编号（mgr）
• 雇佣时间（hiredate）
• 工资月薪（sal）
• 奖金（comm）
• 部门编号（deptno）

部门表(dept) 中包含如下字段：

• 部门编号（deptno）
• 部门名称（dname）
• 部门所在地点（loc）

工资等级表(salgrade) 中包含如下字段：

• 等级（grade）
• 此等级最低工资（losal）
• 此等级最高工资（hisal）

---

查询工资高于500或岗位为MANAGER的员工，同时要求员工姓名的首字母为大写的J

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

查询员工信息，按照部门号升序而雇员的工资降序排序

select ename,deptno,sal from emp order by deptno asc,sal desc;

查询员工信息，按年薪降序显示

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

注意：

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

查询工资最高的员工的姓名和岗位

select ename,job from emp where sal=(select max(sal) from emp);

查询工资高于平均工资的员工信息

select ename,sal from emp where sal > (select avg(sal) from emp);

查询每个部门的平均工资和最高工资

select deptno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;

查询平均工资低于2000的部门和它的平均工资

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

查询每种岗位的雇员总数和平均工资

select job,count(*) 雇员总数,avg(sal) 平均工资 from emp group by job;

---

多表查询

上面的基础查询都是在一张表的基础上进行的查询，而实际开发中往往需要将多张表关联起来进行查询，这就叫做 多表查询。

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

所谓的对多张表取笛卡尔积，就是得到这多张表的记录的所有可能有序对组成的集合，比如下面对员工表和部门表进行多表查询，由于查询语句中没有指明筛选条件，因此最终得到的结果便是员工表和部门表的笛卡尔积。

对部门表和工资等级表取笛卡尔积时，会先从部门表中选出一条记录与工资等级表中的所有记录进行组合，然后再从部门表中选出一条记录与工资等级表中的所有记录进行组合，以此类推，最终得到的就是这两张表的笛卡尔积。

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

这里说明一下，当进行笛卡尔积的多张表中可能会存在相同的列名，这时在选中列名时需要通过 表明.列名 的方式进行指明。

---

显示部门号为10的部门名、员工名和员工工资

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

这里我们需要注意的是，第一个筛选条件已经筛选出员工的部门号和部门编号相同的的记录，因此在筛选部门号等于10的部门时，可以使用员工表中的部门号，也可以使用部门表中的部门编号。

select ename,sal,grade from emp,salgrade where sal between losal and hisal;

说明一下，员工表和工资等级表的笛卡尔积中，将每一个员工的信息和每一个工资等级的信息都进行了组合，而实际一个员工只有和自己的工资对应的工资等级信息进行组合才是有意义的。因此需要根据各个工资等级的最低工资和最高工资判断一个员工是否属于该工资等级，进而筛选出有意义的记录。

---

自连接

自连接 是指在同一张表进行连接查询，也就是说我们不仅可以取不同表的笛卡尔积，也可以对同一张表取笛卡尔积。

如果一张表中的某个字段能够将表中的多条记录关联起来，那么就可以通过自连接将表中通过该字段关联的记录组合起来。

显示员工FORD的上级领导的编号和姓名

我们可以使用子查询，先对员工表进行查询得到FORD的领导的编号，然后再根据领导的编号进对员工表进行查询得到FORD领导的姓名。

select empno,ename from emp where empno=(select mgr from emp where ename='FORD');

当然，解决该问题，也可以使用自连接，因为员工表中的mgr字段可以将表中员工的信息和员工领导的信息关联起来。

对员工表进行自连接后，在where子句中指明筛选条件为员工的领导编号等于领导的编号，这时就能筛选出每个员工信息与其领导信息组合形成的记录，进一步指明筛选条件为员工的姓名为FORD，这时便能筛选出员工FORD的信息和他的领导的信息组成的记录。

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

这里说明一下，由于自连接是对同一张表取笛卡尔积，因此在自连接时至少需要给一张表取别名，否则无法区分这两张表中的列。

---

子查询

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

单行子查询

单行子查询，是指返回单行单列数据的子查询。

显示SMITH同一部门的员工

在子查询中查询SMITH所在的部门号，在where子句中指明筛选条件为员工部门号等于子查询返回的部门号，并且员工的姓名不为SMITH。

select * from emp where deptno=(select deptno from emp where ename='SMITH') and ename<>'SMITH';

此问题当然也可以使用自连接来解决，如下：

select t2.* from emp t1,emp t2 where t1.deptno=t2.deptno and t1.ename='SMITH' and t2.ename<>'SMITH';

---

多行子查询

多行子查询 ，是指返回多行单列数据的子查询。

显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号，但是不包含10号部门的员工。这里主要用到了 IN 关键字

先查询10号部门有哪些工作岗位，然后将上述查询作为子查询，在查询员工表时在where子句中使用in关键字，判断员工的工作岗位是子查询得到的若干岗位中的一个，如果是则符合筛选条件，由于要求筛选出来的员工不包含10号部门的，因此还需要在where子句中指明筛选条件为部门号不等于10。

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

显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号，这里主要用到了all关键字

先查询30号部门员工的工资，这里最好对结果进行去重，然后将上述查询作为子查询，在查询员工表时在where子句中使用all关键字，判断员工的工资是否高于子查询得到的所有工资，如果是则符合筛选条件。

mysql> select ename,sal,deptno from emp 
    -> where sal > all(select distinct sal from emp where deptno=30);

当然这道题也可以使用单行子查询得到30号部门的最高工资，然后判断员工工资是否高于子查询得到的最高工资即可。

显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号，包含30号部门的员工。这里主要用到了 ANY 关键字

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

---

多列子查询

多列子查询，是指返回多列数据的子查询。

显示和SMITH的部门和岗位完全相同的员工，不包含SMITH本人

先查询SMITH所在的部门号和他的岗位，然后将上述查询作为子查询，在查询员工表时在where子句中，指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位，并且员工的姓名不为SMITH即可。

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

说明一下：

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

---

在from子句中使用子查询

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

显示每个高于自己部门平均工资的员工的姓名、部门、工资和部门的平均工资

先查询每个部门的平均工资，由于显示信息中包含部门的平均工资，需要同时使用员工表和上述的查询结果进行多表查询，这时可以将上述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资大于临时表中的平均工资。

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

这里我们需要注意的是，在from子句中使用子查询时，必须给子查询得到的临时表取一个别名，否则将会出错。

显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

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

显示每个部门的部门名、部门编号、所在地址和人员数量

在group by子句中指明按照部门号进行分组，分别查询每个部门的人员数量。

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

---

合并查询

合并查询 是指多个查询结果进行合并，可使用的操作符有 union 和 union all。

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

显示工资大于2500或职位是MANAGER的员工

在合并查询这里，可以使用union操作符将上述的两条查询SQL连接起来，这时将会得到两次查询结果的并集，并且会对合并后的结果进行去重。

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

此外，也可以使用 union all 操作符将上述的两条查询SQL连接起来，这是也会得到两次查询结果的并集，但不会对合并后的结果进行去重。如下：

说明一下：

• 待合并的两个查询结果的列的数量必须一致，否则无法合并。
• 待合并的两个查询结果对应的列属性可以不一样，但不建议这样做。

---

二、MySQL内外连接

在 MySQL 中，表的连接（Table Join） 是指通过共享一个或多个共同的列将两个或多个表中的数据联合起来的操作。这样可以根据关联列的值将数据进行关联，以便在查询结果中获得更全面的信息。表的连接是 SQL 查询中的重要操作之一，它允许我们从多个表中检索相关的数据。表的连接分为内连接和外连接。

内连接

内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

内连接的SQL如下：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;
select 字段 from 表1 inner join 表2 on 连接条件 where 其他条件;

显示SMITH的名字和部门名称

给出一张员工表和一张部门表，员工表中的ename表示员工的姓名，deptno代表的是员工所在部门的部门号。如下：

部门表中的dname代表的是部门名，depno代表的是部门的部门号。如下：

这里按照复合查询的做法就是，取员工表和部门表的笛卡尔积，在where子句中指明筛选条件为员工的部门号等于部门的部门号，筛选出每个员工匹配的的部门信息，并指明员工姓名为SMITH，筛选出SMITH的信息和其所在的部门的信息。

select ename,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

实际上述多表查询的方式本质就是内连接，用标准的内连接SQL编写：

• 将员工表和部门号放在from子句中并通过inner join关键字隔开。
• 在on子句后指明内连接的条件为员工的部门号等于部门的部门号，保证筛选出来的数据是有意义的。
• 在and之后指明筛选条件为员工的姓名为SMITH。

这样筛选出来的结果和多表查询筛选出来的结果是一样的，只不过是写法有一些不同而已。

---

外连接

左外连接

左外连接 返回左表中的所有行以及右表中与左表匹配的行。如果没有匹配的行，右表的列将显示为 NULL。

select 字段名 from 表名1 left join 表名2 on 连接条件;

先创建两张表，学生表和成绩表。学生表中的name代表的是学生的姓名，id代表的是学生的学号。

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | tom  |
|    3 | kity |
|    4 | nono |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from exam;
+------+-------+
| id   | grade |
+------+-------+
|    1 |    56 |
|    2 |    76 |
|   11 |     8 |
+------+-------+
3 rows in set (0.00 sec)

查询所有学生的成绩，就算这个学生没有成绩，也要将学生的个人信息显示出来

如果直接使用内连接将学生表和成绩表连接起来，然后筛选出学生学号等于考试学生学号的记录，那么只能筛选出来有考试成绩的学生信息。

这时我们可以在连接学生表和成绩表时将学生表放在左侧，那么就可以使用左外连接，这时如果左侧表中的某条记录根据连接条件没有找到匹配的右侧表中的记录，就会直接显示左侧表中的记录信息，而其对应的右侧表中的列信息将会用NULL值进行填充。如下：

select * from stu left join exam on stu.id=exam.id;

---

右外连接

右外连接 与左外连接相反，返回右表中的所有行以及左表中与右表匹配的行。如果没有匹配的行，左表的列将显示为 NULL。

select 字段 from 表名1 right join 表名2 on 连接条件;

查询所有的成绩，就算这个成绩没有学生与它对应，也要将学生成绩信息显示出来

select * from stu right join exam on stu.id=exam.id;

简单案例

列出部门名称和这些部门的员工信息，同时列出没有员工的部门

题目要求同时列出没有员工的部门，也就是部门表当中的内容需要完全被显示出来，如果在连接部门表和员工表时将部门表放在左侧，那么就可以使用左外连接。如下：

select dname,emp.* from dept left join emp on dept.deptno=emp.deptno;

当然，如果要使用右外连接，那么可以在连接部门表和员工表时将部门表放在右侧。如下：

select dname,emp.* from emp right join dept on dept.deptno=emp.deptno;

---