各类复合查询和笛卡尔积、内外链接的操作

1.基础查询

在之前的MySQL的CURD基本操作博客中，讲述了一部分查询的语法，但是在实际的生产活动中，那种最基础的查询方式不足以满足复杂查询条件时的要求，所以在普通查询的基础上，MySQL还提供了功能更强大的复合查询，本文就让我们一起来学学复合查询的操作吧！

依旧是使用scott提供的这张表，来进行复合查询的操作。

在那之前，先复习一下普通查询的基本操作吧！

查询工资高于500或者岗位为MANAGER的成员，同时他们的姓名首字母为大写J；

1
2
3
4
5
6
-- 使用%来匹配
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
-- 使用substring函数
select * from emp 
where (sal > 500 or job = 'MANAGER') 
and substring(ename,1,1) = 'J';

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

1
2
-- 默认就是升序，可以省略asc
select * from emp order by deptno asc, sal desc;

显示工资高于平均工资的员工信息

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

显示每个部门的平均工资和最高工资

1
select deptno, avg(sal), max(sal) from emp group by deptno;

显示每种岗位的雇员数量和平均工资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select job,count(*),avg(sal) from emp group by job;
-- 默认情况下会保留6位小数，可以用format函数保留到2位
select job,count(*),format(avg(sal),2) from emp group by job;
-- 格式化后的输出结果如下
+-----------+----------+--------------------+
| job       | count(*) | format(avg(sal),2) |
+-----------+----------+--------------------+
| ANALYST   |        2 | 3,000.00           |
| CLERK     |        4 | 1,037.50           |
| MANAGER   |        3 | 2,758.33           |
| PRESIDENT |        1 | 5,000.00           |
| SALESMAN  |        4 | 1,400.00           |
+-----------+----------+--------------------+
5 rows in set (0.000 sec)

使用年薪进行降序排序。年薪等于12个月的月薪+奖金，但是在emp表中，并非所有人都有奖金，一些人的奖金是null，此时直接相加得到的结果就会有null;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [scott]> select ename,12*sal+comm from emp;
+--------+-------------+
| ename  | 12*sal+comm |
+--------+-------------+
| SMITH  |        NULL |
| ALLEN  |    19500.00 |
| WARD   |    15500.00 |
| JONES  |        NULL |
| MARTIN |    16400.00 |
| BLAKE  |        NULL |
| CLARK  |        NULL |
| SCOTT  |        NULL |
| KING   |        NULL |
| TURNER |    18000.00 |
| ADAMS  |        NULL |
| JAMES  |        NULL |
| FORD   |        NULL |
| MILLER |        NULL |
+--------+-------------+
14 rows in set (0.001 sec)

我们需要用ifnull函数来解决这个问题，如果奖金为null则返回0；

1
select ename,12*sal+ifnull(comm,0) from emp;

此时就能得到每个人的年薪了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [scott]> select ename,12*sal+ifnull(comm,0) from emp;
+--------+-----------------------+
| ename  | 12*sal+ifnull(comm,0) |
+--------+-----------------------+
| SMITH  |               9600.00 |
| ALLEN  |              19500.00 |
| WARD   |              15500.00 |
| JONES  |              35700.00 |
| MARTIN |              16400.00 |
| BLAKE  |              34200.00 |
| CLARK  |              29400.00 |
| SCOTT  |              36000.00 |
| KING   |              60000.00 |
| TURNER |              18000.00 |
| ADAMS  |              13200.00 |
| JAMES  |              11400.00 |
| FORD   |              36000.00 |
| MILLER |              15600.00 |
+--------+-----------------------+
14 rows in set (0.001 sec)

现在我们就可以在order by中用年薪进行降序排序了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [scott]> select ename,12*sal+ifnull(comm,0) as 年薪 from emp order by 年薪 desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| KING   | 60000.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| JONES  | 35700.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD   | 15500.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| SMITH  |  9600.00 |
+--------+----------+
14 rows in set (0.001 sec)

2.多表查询

基础查询的操作就复习上面几个就差不多了，接下来我们先看复合查询的第一个大点，多表查询。

所谓多表查询，就是将一个表和另外一个表合并，再在这个合并了之后的表里面进行查询；

  • 比如：显示雇员名、雇员工资以及所在部门的名字;

因为雇员名是在emp表里面，部门名字是在dept表里面，所以我们就需要进行多表的联合查询才能一次取到结果（执行一次SQL查询的效率优于两次独立的SQL查询）。

其基本语句就是在from后面添加多个表，并使用表名.表中字段名来指定某一个表中的字段；如果这个字段只有一个表中有，那就可以不用指定表名（但依旧建议指定表名避免出错）

1
select * from 表1,表2,...;

2.1 笛卡尔积

先来一个全列查询，看看效果

1
select * from emp,dept;

可以看到，dept表直接拼接在了emp表之后，而且emp表中的每一个值都被根据dept中不同的部门ID重复了4次。这是因为在MySQL中，他并不知道你需要什么数据，所以进行数据拼接的时候，是采用穷举的方式来拼接的！

这个不加任何过滤条件，穷举拼接得到的结果被称为笛卡尔积！笛卡尔积对表的结构并没有要求，任何两张表都可以进行笛卡尔积。

在MySQL中，多表组合过后的结果，我们都可以当作它本来就是一张表来处理。

image-20230908082727650

下图是一个更详细的示意图，我们先从emp表中取出一条记录，将其和dept表中的4条记录进行组合，呈现在最终结果里面。很明显，这是一个穷举的结果，因为每个emp的记录都会和4个dept的记录进行组合，最终dept中的每个记录也和emp中的每个记录有过一次组合了。

交换emp和dept的顺序并不会影响这个排列组合的结果，下图可以看到，dept表和emp的顺序交换只会交换两个表中的列在笛卡尔积中出现的顺序。

image.png

2.2 指定条件

接下来再回到上面提到的问题：显示雇员名、雇员工资以及所在部门的名字；

1
select emp.ename,emp.sal,dept.dname from emp,dept;

虽然我们选中了我们需要的列来显示，但依旧没有解决笛卡尔积中穷举出来的无效数据，一个人名还是出现了四次；

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [scott]> select emp.ename,emp.sal,dept.dname from emp,dept;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | ACCOUNTING |
| SMITH  |  800.00 | RESEARCH   |
| SMITH  |  800.00 | SALES      |
| SMITH  |  800.00 | OPERATIONS |
| ALLEN  | 1600.00 | ACCOUNTING |
| ALLEN  | 1600.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| ALLEN  | 1600.00 | OPERATIONS |

这时候就需要添加上筛选条件，来剔除掉无效的数据。

观察笛卡尔积的结构可以知道，和当前员工的部门编号不同的部门的数据就是无效的，我们添加上员工部门编号和部门表中的部门编号相同的条件，就可以剔除掉无效的数据。

1
2
3
4
select emp.ename,emp.sal,dept.dname 
from emp,dept 
where emp.deptno = dept.deptno; 
-- 通过where保证员工表中的部门编号和部门表中相同

这时候筛选出来的数据，才是正确的每个员工和其对应的部门名字的数据；每个员工也不在会重复4次了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [scott]> select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.004 sec)

2.3 小练习

再来做两个小练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 显示部门号为10的部门名字，员工名字和员工工资；
MariaDB [scott]> select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.001 sec)

-- 显示各个部门员工的姓名，工资，工资级别
MariaDB [scott]> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.001 sec)

3.自链接

所谓自连接，是在同一张表中进行链接和查询。

比如显示员工FORD上级领导的员工编号和姓名，我们可以用子查询，也可以用emp表的自链接来处理。

1
2
3
4
5
6
7
8
9
10
-- 使用子查询
select empno,ename from emp 
where emp.empno = (select mgr from emp where ename='FORD');
-- 使用多表查询（其实是给emp表起两个不同的别名）
-- 如果不起别名，则无法自链接成功，因为没有办法区分两个emp表
-- 员工的领导编号和员工编号一致，即为领导用户的信息
select leader.empno,leader.ename 
from emp leader, emp worker 
where leader.empno = worker.mgr 
		and worker.ename='FORD';

这种在一张表里面进行笛卡儿积的查询操作，就叫做自链接。最终都可以获取到正确结果

1
2
3
4
5
6
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.001 sec)

自链接本质上也是一个多表查询，只不过两个表都是emp表而已。

4.子查询

子查询指的是，将一个select查询语句作为子操作，嵌入到一个sql语句中；

我们可以把子查询的结果也当作是一张表来进行处理，这样一想就能理解子查询是怎么玩的了。

4.1 单行子查询

显示和SMITH同一个部门的员工

1
2
select * from emp 
where deptno = (select deptno from emp where ename='smith');

在这个查询中，子查询语句是select deptno from emp where ename='smith'，这个语句返回的结果只有一行，所以被称为单行子查询。

4.2 多行子查询

有单行查询，自然也有多行查询。但是如果一个语句返回了多条结果（多行单列），将其直接作为判据肯定是不行的，这会导致判据不唯一，MySQL不知道你到底想要比较的是那一部分的数据。这时候就需要用下面几个关键字来帮忙了

  • in：查询存在于某某结果里面的数据
  • all：查询比这个结果中所有值都大/小的数据
  • any/some：查询比这个结果中某一个值大/小的数据

以下是几个示例：

查询和10号部门工作岗位相同的员工名字、岗位、工资、部门号，但是不包含10号部门自己的员工

1
2
3
4
5
6
7
8
9
10
11
select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| SMITH | CLERK   |  800.00 |     20 |
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+
5 rows in set (0.001 sec)

显示部门工资比30号部门所有员工工资都高的员工姓名、工资、部门号

1
2
3
4
5
6
7
8
9
10
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.001 sec)

some和any的作用相同，用于查询某个条件比子查询结果集中任意一个值都大/小的结果。

下面是一个示例，查询emp员工表中，薪水比工作是SALESMAN的任何人大的员工。查询之前，先看看emp全表的数据，在这里能得出工作是SALESMAN的员工的最低工资是1250。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [scott]> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.000 sec)

使用如下语句查询薪水比SALESMAN任意员工工资高的员工，即过滤掉工资小于等于1250的员工。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [scott]> select * from emp where sal > some(select sal from emp where job = 'SALESMAN');
+--------+--------+-----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+-----------+------+---------------------+---------+--------+--------+
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |   NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |   NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |   NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |   0.00 |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |   NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+--------+--------+
9 rows in set (0.000 sec)

4.3 多列子查询

前两个子查询的结果都是单列的，如果是多列的结果，我们要怎么处理呢？

举例：查询和SMITH部门和岗位完全相同的所有雇员，不包括SMITH本人。

1
2
3
select ename from emp where (deptno,job)=
	(select deptno,job from emp where ename = 'SMITH')
	and ename != 'SMITH';

这里面我们在where条件中直接指定了两列，用于和select子查询的结果的两列进行比较。注意要保证两个列和select指定列的顺序一样。

1
2
3
4
5
6
7
8
9
MariaDB [scott]> select ename from emp where (deptno,job)=
    -> (select deptno,job from emp where ename = 'SMITH')
    -> and ename != 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.026 sec)

在多列子查询中，in/any/all关键字中只有any关键字可以使用。

比如我们将上面的例子改成“查询名字为S开头的员工的部门和岗位完全相同的所有雇员”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
MariaDB [scott]> (select deptno,job from emp where ename like 'S%');
+--------+---------+
| deptno | job     |
+--------+---------+
|     20 | CLERK   |
|     20 | ANALYST |
+--------+---------+
2 rows in set (0.002 sec)

MariaDB [scott]> select ename from emp where (deptno,job) = any(select deptno,job from emp where ename like 'S%');
+-------+
| ename |
+-------+
| SMITH |
| SCOTT |
| ADAMS |
| FORD  |
+-------+
4 rows in set (0.001 sec)

MariaDB [scott]> select ename from emp where (deptno,job) = all(select deptno,job from emp where ename like 'S%');
ERROR 1241 (21000): Operand should contain 1 column(s)
MariaDB [scott]> select ename from emp where (deptno,job) in (select deptno,job from emp where ename like 'S%');
+-------+
| ename |
+-------+
| SMITH |
| SCOTT |
| ADAMS |
| FORD  |
+-------+
4 rows in set (0.002 sec)

在测试中可以看到，子查询的结果是2行2列，此时我们只能用any关键字来指定结果和这两个员工任意一个的部门、工作一样的人；in关键字指定部门、工作在这个结果中的数据。

all关键字会报错，因为all关键字自能用于一列数据。

4.4 在from中使用子查询

这个就更好理解了，我们将select的结果视作另外一张表，放到from后面就行了，此时就结合了子查询和多表查询。

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

要想解决这个问题，我们首先要获得各个部门的平均工资，并将其视作一个新表。再将员工表和其集合，筛选出最终结果

1
2
3
4
5
-- format函数用于限制小数点个数
select ename,deptno,sal,format(avgsal,2)
from emp,(select avg(sal) as avgsal,deptno as dt from
         emp group by deptno) as temp
where emp.sal > temp.avgsal and emp.deptno = temp.dt;

结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [scott]> select ename,deptno,sal,format(avgsal,2)
    -> from emp,(select avg(sal) as avgsal,deptno as dt from
    ->          emp group by deptno) as temp
    -> where emp.sal > temp.avgsal and emp.deptno = temp.dt;
+-------+--------+---------+------------------+
| ename | deptno | sal     | format(avgsal,2) |
+-------+--------+---------+------------------+
| ALLEN |     30 | 1600.00 | 1,566.67         |
| JONES |     20 | 2975.00 | 2,175.00         |
| BLAKE |     30 | 2850.00 | 1,566.67         |
| SCOTT |     20 | 3000.00 | 2,175.00         |
| KING  |     10 | 5000.00 | 2,916.67         |
| FORD  |     20 | 3000.00 | 2,175.00         |
+-------+--------+---------+------------------+
6 rows in set (0.001 sec)

例题进阶

进阶：在“每个工资高于自己部门平均工资的员工的姓名、部门、工资和部门平均工资”要求的基础上，在结果中加上员工的薪资等级。

1
2
3
4
select ename,deptno,sal,format(avgsal,2)
from emp,(select avg(sal) as avgsal,deptno as dt from
         emp group by deptno) as temp
where emp.sal > temp.avgsal and emp.deptno = temp.dt;

首先将原有结果和薪资等级表进行笛卡尔积，然后再去掉无效的数据

1
2
3
4
5
-- 备注：这个SQL有错误！
select * from salgrade,(select ename,deptno,sal,format(avgsal,2)
from emp,(select avg(sal) as avgsal,deptno as dt from
         emp group by deptno) as temp
where emp.sal > temp.avgsal and emp.deptno = temp.dt);

直接执行上面这个SQL是不行的，因为这里我们指定了一个子查询的结果，却没有给这个子查询上一个表的别名，此时MySQL会拒绝执行笛卡尔积，并且报告SQL语法错误。

1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

正确的写法是给子查询加上一个表的别名，即可查询到笛卡尔积的结果

1
2
3
4
5
-- 更正：用于笛卡尔积的子查询需要有别名
select * from salgrade,(select ename,deptno,sal,format(avgsal,2)
    from emp,(select avg(sal) as avgsal,deptno as dt from
             emp group by deptno) as temp
    where emp.sal > temp.avgsal and emp.deptno = temp.dt) as subquery;

为了方便在博客中展示，我在末尾添加了一个limit 1，这样能快速知道sql是否有错误，又不会被大量查询结果填满屏幕。

1
2
3
4
5
6
7
8
9
10
MariaDB [scott]> select * from salgrade,(select ename,deptno,sal,format(avgsal,2)
    -> from emp,(select avg(sal) as avgsal,deptno as dt from
    ->          emp group by deptno) as temp
    -> where emp.sal > temp.avgsal and emp.deptno = temp.dt) as subquery limit 1;
+-------+-------+-------+-------+--------+---------+------------------+
| grade | losal | hisal | ename | deptno | sal     | format(avgsal,2) |
+-------+-------+-------+-------+--------+---------+------------------+
|     1 |   700 |  1200 | ALLEN |     30 | 1600.00 | 1,566.67         |
+-------+-------+-------+-------+--------+---------+------------------+
1 row in set (0.001 sec)

确认我们的sql正确后，再通过where条件去掉无效的数据，并在select中写清楚我们需要的最终结果。

1
2
3
4
5
select grade,ename,deptno,sal,asal from salgrade,(select ename,deptno,sal,format(avgsal,2) as asal
    from emp,(select avg(sal) as avgsal,deptno as dt from
             emp group by deptno) as temp
    where emp.sal > temp.avgsal and emp.deptno = temp.dt) as subquery
where subquery.sal between losal and hisal;

这时候我们就成功得到了包含员工薪资等级的结果！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [scott]> select grade,ename,deptno,sal,asal from salgrade,(select ename,deptno,sal,format(avgsal,2) as asal
    ->     from emp,(select avg(sal) as avgsal,deptno as dt from
    ->              emp group by deptno) as temp
    ->     where emp.sal > temp.avgsal and emp.deptno = temp.dt) as subquery
    -> where subquery.sal between losal and hisal;
+-------+-------+--------+---------+----------+
| grade | ename | deptno | sal     | asal     |
+-------+-------+--------+---------+----------+
|     3 | ALLEN |     30 | 1600.00 | 1,566.67 |
|     4 | JONES |     20 | 2975.00 | 2,175.00 |
|     4 | BLAKE |     30 | 2850.00 | 1,566.67 |
|     4 | SCOTT |     20 | 3000.00 | 2,175.00 |
|     5 | KING  |     10 | 5000.00 | 2,916.67 |
|     4 | FORD  |     20 | 3000.00 | 2,175.00 |
+-------+-------+--------+---------+----------+
6 rows in set (0.001 sec)

小练习1

查询每个部门工资最高的人的姓名、工资、部门、最高工资。

1
2
3
4
select emp.ename, emp.sal, emp.deptno, ms 
from emp,(select max(sal) ms, deptno 
          from emp group by deptno) temp
where emp.deptno=temp.deptno and emp.sal=temp.ms;

结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [scott]> select emp.ename, emp.sal, emp.deptno, ms
    -> from emp,(select max(sal) ms, deptno
    ->       fr          from emp group by deptno) temp
    -> where emp.deptno=temp.deptno and emp.sal=temp.ms;
+-------+---------+--------+---------+
| ename | sal     | deptno | ms      |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.001 sec)

小练习2

显示每个部门的信息（包括部门名字、编号、地址）和人员数量。

这个问题可以用多表查询或用子查询解决。

1
2
3
4
5
-- 使用多表
select dept.dname,dept.deptno,dept.loc,count(*) as mycnt
from emp,dept
where emp.deptno = dept.deptno
group by dept.deptno,dept.dname,dept.loc;

使用子查询的方式如下

1
2
3
4
select dept.deptno,dname,mycnt,loc 
from dept,(select count(*) as mycnt,deptno from emp
          	group by deptno) temp
where dept.deptno=temp.deptno;

运行结果相同（列的位置不一样）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
MariaDB [scott]> select dept.dname,dept.deptno,dept.loc,count(*) as mycnt
    ->  emp,depfrom emp,dept
    -> where emp.deptno = dept.deptno
    -> group by dept.deptno,dept.dname,dept.loc;
+------------+--------+----------+-------+
| dname      | deptno | loc      | mycnt |
+------------+--------+----------+-------+
| ACCOUNTING |     10 | NEW YORK |     3 |
| RESEARCH   |     20 | DALLAS   |     5 |
| SALES      |     30 | CHICAGO  |     6 |
+------------+--------+----------+-------+
3 rows in set (0.002 sec)

MariaDB [scott]> select dept.deptno,dname,mycnt,loc
 dept,(    -> from dept,(select count(*) as mycnt,deptno from emp
    ->           group by deptno) temp
    -> tno;where dept.deptno=temp.deptno;
+--------+------------+-------+----------+
| deptno | dname      | mycnt | loc      |
+--------+------------+-------+----------+
|     10 | ACCOUNTING |     3 | NEW YORK |
|     20 | RESEARCH   |     5 | DALLAS   |
|     30 | SALES      |     6 | CHICAGO  |
+--------+------------+-------+----------+
3 rows in set (0.001 sec)

4.5 合并查询

如果想合并多个select的执行结果，需要使用union或union all操作符来处理。

4.5.1 union

该操作符用于获取两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行。

注意，union在处理的时候不会检查两个子查询中列的关系，所以使用时需要保证列的数量一致，且字段顺序一致，否则集合出来的结果没有意义。

举例：将工资大于2500或者职位是MANAGER的人筛选出来。

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

单独查询这两个表时，有一个重复数据JONES

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [scott]> select ename,sal,job from emp where sal>2500;
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
+-------+---------+-----------+
5 rows in set (0.001 sec)

MariaDB [scott]> select ename,sal,job from emp where job='MANAGER';
+-------+---------+---------+
| ename | sal     | job     |
+-------+---------+---------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+---------+
3 rows in set (0.001 sec)

使用union后，会去掉这个重复数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [scott]> select ename,sal,job from emp where sal>2500 union
	-> select ename,sal,job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
6 rows in set (0.001 sec)

4.5.2 union all

和union功能相同，但是不会去重

1
2
select ename,sal,job from emp where sal>2500 union all
select ename,sal,job from emp where job='MANAGER';

使用这个操作符来合并两个表，可以看到JONES出现了两次，没有被去重。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [scott]> select ename,sal,job from emp where sal>2500 union all
select e    -> select ename,sal,job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
8 rows in set (0.001 sec)

5.内外链接

5.1 内连接

内连接其实就是在笛卡尔积的基础上通过条件对数据进行筛选，和上文多表查询中使用的where语句来筛选数据本质上没有区别

1
2
3
select ... from table1 
[inner] join table2
on ... [and ...];

比如显示雇员名、雇员工资以及所在部门的名字，在之前我们是用where做条件，现在可以直接join了（二者等价）

1
2
3
4
5
-- 内链接的时候inner可以不写，因为默认就是内连接
select emp.ename,emp.sal,dept.dname 
from emp 
join dept
on emp.deptno = dept.deptno;

5.2 左外连接

外连接分为两种，一个是左外连接，一个是右外连接。分别对应左侧还是右侧完全显示。

1
2
3
select ... from table1 
left join table2
on ... [and ...];

比如我们有两个表，一个是学生表，一个是成绩表。使用如下sql对两个表进行初始化

1
2
3
4
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);

查询所有学生的成绩，如果这个学生没成绩，也要显示学生的个人信息。

这里就涉及到了两个表进行多表查询了，而且给出一个条件：如果学生没有成绩，也需要显示学生个人信息。

先来试试笛卡尔积的结果，发现只有两个学生有成绩，kity/nono同学是没有成绩的，也没有显示在结果集中。

1
2
3
4
5
6
7
8
MariaDB [stu_test]> select * from stu,exam where stu.id = exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
+------+------+------+-------+
2 rows in set (0.000 sec)

由此可知，直接使用笛卡尔积无法满足我们的要求。此时就可以用外连接了。

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

执行可以看到，没有成绩的两个同学也显示出来了，只不过他们的成绩都是NULL。此时左外连接的作用就是显示链接左侧的表中，不存在于右侧的信息（右侧表显示NULL）

这里面kity和nono就是存在于左侧表stu中，但不存在于右侧表exam里面的数据。

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [stu_test]> select * from stu
    -> left join exam
    -> on stu.id = exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | NULL |  NULL |
|    4 | nono | NULL |  NULL |
+------+------+------+-------+
4 rows in set (0.001 sec)

如果我们希望显示exam表中不存在于stu表里面的信息，就需要用右外连接。

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

此时能查询到11号同学的成绩，因为这个同学不存在于stu表中，所以stu表的键显示为NULL。

1
2
3
4
5
6
7
8
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
| NULL | NULL |   11 |     8 |
+------+------+------+-------+
3 rows in set (0.001 sec)

如果stu和exam表存在外键约束，那么这种存在于exam中但不存在于stu中的数据被称为悬浮元组。

5.3 右外连接

右外连接和左外连接相反，链接的时候如果右侧表中没有数据，也显示出来；

1
2
3
select ... from table1 
left join table2
on ... [and ...];

先把上面使用的sql语句中的left改成right，可以看到，最终结果里面将没有信息的11号同学的成绩也显示出来了。

1
2
3
4
5
6
7
8
9
MariaDB [stu_test]> select * from stu right join exam on stu.id = exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
| NULL | NULL |   11 |     8 |
+------+------+------+-------+
3 rows in set (0.000 sec)

如果我们将sql中两个表的位置颠倒，则可以得到和左连接一样的结果（只不过列的位置不一样）

1
2
3
4
5
6
7
8
9
10
MariaDB [stu_test]> select * from exam right join stu on stu.id = exam.id;
+------+-------+------+------+
| id   | grade | id   | name |
+------+-------+------+------+
|    1 |    56 |    1 | jack |
|    2 |    76 |    2 | tom  |
| NULL |  NULL |    3 | kity |
| NULL |  NULL |    4 | nono |
+------+-------+------+------+
4 rows in set (0.000 sec)

回到scott的表，尝试用外连接来解决这个问题：列出部门名称和这些部门的员工信息，同时列出没有员工的部门；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [scott]> select d.dname, e.* from dept d left join emp e on d.deptno = e.deptno;
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| dname      | empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
| RESEARCH   | 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| SALES      | 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| SALES      | 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| RESEARCH   | 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| SALES      | 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| SALES      | 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| ACCOUNTING | 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| RESEARCH   | 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| SALES      | 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| RESEARCH   | 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| SALES      | 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| RESEARCH   | 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| ACCOUNTING | 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
| OPERATIONS |   NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |
+------------+--------+--------+-----------+------+---------------------+---------+---------+--------+
15 rows in set (0.002 sec)

The end

关于复合查询以及内外链接的博客就这么多了。知道基础的概念后，我们后续处理更加复杂的SQL问题也能抽丝剥茧而得到最终结果了。