【MySQL】多表查询

Luojj_2020

已于 2023-04-27 15:30:19 修改

阅读量535

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-07-09 05:24:09 首次发布

本文链接：https://blog.csdn.net/m0_51064412/article/details/125643609

版权

MySQL 专栏收录该内容

9 篇文章 13 订阅

订阅专栏

文章目录

补充：来自oracle 9i的经典测试表（举例使用）
一. 笛卡尔积
- 1. 介绍
- 2. 举例
二. 自连接
- 1. 介绍
- 2. 举例
三. 子查询
四. 合并查询
- 1. union
- 2. union all

补充：来自oracle 9i的经典测试表（举例使用）

表1：emp员工表

员工表创建语句如下：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

接下来向员工表中插入一些数据：

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

结果显示：
在这里插入图片描述

表2：dept部门表

部门表创建语句如下：

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);

部门表数据插入：

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

结果显示：
在这里插入图片描述

表3：salgrade工资等级表

工资等级表创建语句如下：

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);

数据插入：

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

结果显示：
在这里插入图片描述

一. 笛卡尔积

1. 介绍

实际开发中往往数据来自不同的表，所以需要用到多表查询，下面我们把emp员工表和dept 部门表联合起来一起查询，看看是什么效果：

mysql> select * from emp, dept;

结果显示：
在这里插入图片描述

发现MySQL从其中一张表中选出一条记录去和另外一张表的所有记录进行组合，重复这个过程直到一开始那张表的所有记录全部被组合完成，这样子不加筛选条件的直接组合在一起显示出来的结果都叫做笛卡尔积。

另外，emp员工表中有一个字段叫做deptno表示该员工所在部门的部门号，这个字段是一个外键，其主表是dept部门表：
在这里插入图片描述

我们在笛卡尔积全显示时，两张表中的deptno字段也参与组合了，我们可以使用where字句进行筛选只要emp表中的deptno = dept表中的deptno字段的记录：
在这里插入图片描述

2. 举例

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

首先部门号这个字段的话emp表和dept表中都存在，而部门名存在于dept表中，员工号和工资存在于emp表中，我们先对两张表做笛卡尔积然后从中进行条件显示和筛选。

mysql> select dname, ename, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| ACCOUNTING | CLARK  | 2450.00 |
| ACCOUNTING | KING   | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
+------------+--------+---------+
3 rows in set (0.00 sec)

2、显示各个员工的姓名，工资，及工资级别

员工姓名和工资属于emp表中的两个字段，而工资级别是属于salgrade表中的一个字段，下面是salgrade表的内容：

grade字段：表示工资定级。
losal字段：表示某个等级的最低工资。
hisal字段：表示某个等级的最高工资。

在对笛卡尔积后的结果进行筛选时我们要注意让emp表中sal字段的值落在salgrade.losal和salgrade.hisal之间：

mysql> select ename, sal, grade from emp, salgrade where 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.00 sec)

二. 自连接

1. 介绍

一张表可以和其他表进行笛卡尔积，也可以和自己进行笛卡尔积（不过要重命名来防止重名），我们把同一张表连接查询的情况称为自连接：

// 1、查看dept部门表的数据
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

// 2、尝试直接用dept表自连接，提示我们两张表不能重名
mysql> select * from dept, dept;
ERROR 1066 (42000): Not unique table/alias: 'dept'

// 3、修改其中一张表的名字为dept_bak，自连接成功
mysql> select * from dept, dept as dept_bak;
+--------+------------+----------+--------+------------+----------+
| deptno | dname      | loc      | deptno | dname      | loc      |
+--------+------------+----------+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |     10 | ACCOUNTING | NEW YORK |
|     30 | SALES      | CHICAGO  |     10 | ACCOUNTING | NEW YORK |
|     40 | OPERATIONS | BOSTON   |     10 | ACCOUNTING | NEW YORK |
|     10 | ACCOUNTING | NEW YORK |     20 | RESEARCH   | DALLAS   |
|     20 | RESEARCH   | DALLAS   |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |     30 | SALES      | CHICAGO  |
|     30 | SALES      | CHICAGO  |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |     30 | SALES      | CHICAGO  |
|     10 | ACCOUNTING | NEW YORK |     40 | OPERATIONS | BOSTON   |
|     20 | RESEARCH   | DALLAS   |     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |     40 | OPERATIONS | BOSTON   |
|     40 | OPERATIONS | BOSTON   |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)

2. 举例

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

相关信息都能在emp表中找到：
在这里插入图片描述

方法一：使用子查询

// 先找出FORD员工的领导的编号，再根据这个编号找到领导的姓名。
mysql> select empno, ename from emp where empno=(select mgr from emp where ename='FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

方法二：使用多表查询（自连接）

// 1、先查询所有领导的工号和姓名
mysql> select leader.empno, leader.ename from emp as worker, emp as leader where worker.mgr=leader.empno;
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
| 007566 | JONES |
| 007698 | BLAKE |
| 007698 | BLAKE |
| 007698 | BLAKE |
| 007698 | BLAKE |
| 007698 | BLAKE |
| 007782 | CLARK |
| 007788 | SCOTT |
| 007839 | KING  |
| 007839 | KING  |
| 007839 | KING  |
| 007902 | FORD  |
+--------+-------+
13 rows in set (0.00 sec)

// 2、筛选员工FORD的领导的工号和姓名
mysql> select leader.empno, leader.ename from emp as worker, emp as leader where worker.mgr=leader.empno and worker.ename='FORD';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

三. 子查询

1. 单行单列子查询

介绍：单行子查询是指返回一行记录的子查询，但我们通常只会使用到其中一个字段的内容做为外部查询的条件。

举例：显示和SMITH同一部门的员工姓名和部门号

// 第一步：查询出SMITH员工的工号是多少
mysql> select deptno from emp where ename='SMITH';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

// 第二步：把上一步得到的查询结果做为筛选条件开始新的查询
mysql> select ename, deptno from emp where deptno=(select deptno from emp where ename='SMITH');
+-------+--------+
| ename | deptno |
+-------+--------+
| SMITH |     20 |
| JONES |     20 |
| SCOTT |     20 |
| ADAMS |     20 |
| FORD  |     20 |
+-------+--------+
5 rows in set (0.00 sec)

2. 多行单列子查询

介绍：多行子查询是指返回多行记录的子查询，这些多行记录都要使用到，通常搭配in关键字、all关键字、any关键字的其中之一做为筛选条件使用。

any和all关键字通常要配合比较符号使用而in关键字就不需要。
any和all关键字的区别在于前者只需满足其中一个就可以，而后者必须全满足。

举例

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

最终需要查询的雇员的名字，岗位，工资，部门号这些都在emp表中有。
每个部门下都有很多个员工，他们每个人只有一个工作岗位（彼此之间可能相同，也可能不同）。

// 第一步：先看看10号部门下的员工他们的工作岗位都有些什么
mysql> select ename, job, deptno from emp where deptno=10;
+--------+-----------+--------+
| ename  | job       | deptno |
+--------+-----------+--------+
| CLARK  | MANAGER   |     10 |
| KING   | PRESIDENT |     10 |
| MILLER | CLERK     |     10 |
+--------+-----------+--------+
3 rows in set (0.00 sec)

// 2、查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号
mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10);
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| JONES  | MANAGER   | 2975.00 |     20 |
| BLAKE  | MANAGER   | 2850.00 |     30 |
| CLARK  | MANAGER   | 2450.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| SMITH  | CLERK     |  800.00 |     20 |
| ADAMS  | CLERK     | 1100.00 |     20 |
| JAMES  | CLERK     |  950.00 |     30 |
| MILLER | CLERK     | 1300.00 |     10 |
+--------+-----------+---------+--------+
8 rows in set (0.00 sec)

// 3、在第二步的基础上排除10号部门自己的员工
mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| SMITH | CLERK   |  800.00 |     20 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)

2、all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

最终需要查询的员工的姓名、工资、部门号都保存在emp表中。
需要先子查询出30号部门的所有员工的工资。

// 第一步：先查询出30号部门的所有员工的工资
mysql> select sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
6 rows in set (0.00 sec)

// 第二步：上一步的结果做为子查询配合all关键字完成最终查询
mysql> 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.00 sec)

3、any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

any关键字的选择要求是你能打得过其中一个。
all关键字的选择要求是你必须全部能打过。

// 第一步：先查询出30号部门的所有员工的工资（注意去重）
mysql> select distinct sal from emp where deptno=30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
5 rows in set (0.01 sec)

// 第二步：上一步的结果做为子查询配合any关键字完成最终查询
mysql> select ename, sal, deptno from emp where sal > any(select distinct sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

3. 多列单行子查询

介绍：多列子查询则是指查询返回多个列数据的子查询语句，需要筛选的字段在圆括号中给出并以逗号分隔。

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

// 1、查询出SMITH的部门号和工作岗位
mysql> select deptno, job from emp where ename='SMITH';
+--------+-------+
| deptno | job   |
+--------+-------+
|     20 | CLERK |
+--------+-------+
1 row in set (0.00 sec)

// 2、查询和SMITH的部门和岗位完全相同的所有雇员，但不含SMITH本人
mysql> 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.00 sec)

4. 在from子句中使用子查询

介绍：子查询语句出现在from子句中时，通常把该子查询当做一个临时表使用去和其它表进行笛卡尔积。

举例

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

// 1、子查询得到每个部门的平均工资
mysql> select deptno, avg(sal) as avg from emp group by deptno;
+--------+-------------+
| deptno | avg         |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

// 2、用上一步子查询得到结果去和emp表做笛卡尔积，完成部门平均工资和员工信息的关联
mysql> select ename, emp.deptno, sal, avg from emp, (select deptno, avg(sal) as avg from emp group by deptno) as avgTable limit 3;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg         |
+-------+--------+---------+-------------+
| SMITH |     20 |  800.00 | 2916.666667 |
| ALLEN |     30 | 1600.00 | 2916.666667 |
| WARD  |     30 | 1250.00 | 2916.666667 |
+-------+--------+---------+-------------+
3 rows in set (0.00 sec)

// 3、在第二步得到的结果中筛选出工资大于自己所在部门平均工资的员工
mysql> select ename, emp.deptno, sal, avg from emp, (select deptno, avg(sal) as avg from emp group by deptno) as avgTable where emp.deptno=avgTable.deptno and emp.sal>avg;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg         |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

2、查找每个部门工资最高的人的姓名、工资、部门、最高工资

// 1、查询出每个部门的最高工资是多少
mysql> select deptno, max(sal) as max from emp group by deptno;
+--------+---------+
| deptno | max     |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
3 rows in set (0.00 sec)

// 2、用上一步查询出来的结果去和emp表做笛卡尔积，把员工信息和每个部门的最高工资关联在一张表里
mysql> select ename, sal, job, max from emp, (select deptno, max(sal) as max from emp group by deptno) as maxTable limit 3;
+-------+---------+----------+---------+
| ename | sal     | job      | max     |
+-------+---------+----------+---------+
| SMITH |  800.00 | CLERK    | 5000.00 |
| ALLEN | 1600.00 | SALESMAN | 5000.00 |
| WARD  | 1250.00 | SALESMAN | 5000.00 |
+-------+---------+----------+---------+
3 rows in set (0.00 sec)

// 3、在上一步的结果中进行一系列筛选得到最终结果
mysql> select ename, sal, job, max from emp, (select deptno, max(sal) as max from emp group by deptno) as maxTable where emp.deptno=maxTable.deptno and sal=max;
+-------+---------+-----------+---------+
| ename | sal     | job       | max     |
+-------+---------+-----------+---------+
| BLAKE | 2850.00 | MANAGER   | 2850.00 |
| SCOTT | 3000.00 | ANALYST   | 3000.00 |
| KING  | 5000.00 | PRESIDENT | 5000.00 |
| FORD  | 3000.00 | ANALYST   | 3000.00 |
+-------+---------+-----------+---------+
4 rows in set (0.00 sec)

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

部门名，编号，地址这三个字段都存在于dept表中。
人员数量需要到emp表中根据部门先分组然后再去统计。

// 1、可以看到dept表中包含了部门名，编号，地址这三个字段的信息
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

// 2、从emp表中计算出每个部门的员工总数
mysql> select deptno, count(*) as sum from emp group by deptno;
+--------+-----+
| deptno | sum |
+--------+-----+
|     10 |   3 |
|     20 |   5 |
|     30 |   6 |
+--------+-----+
3 rows in set (0.00 sec)

// 3、让第二步查询出来的结果和dept表做笛卡尔积，把部门信息和每个部门的人员总数关联起来
mysql> select dname, dept.deptno, loc, sum from dept, (select deptno, count(*) as sum from emp group by deptno) as sumTable;
+------------+--------+----------+-----+
| dname      | deptno | loc      | sum |
+------------+--------+----------+-----+
| ACCOUNTING |     10 | NEW YORK |   3 |
| RESEARCH   |     20 | DALLAS   |   3 |
| SALES      |     30 | CHICAGO  |   3 |
| OPERATIONS |     40 | BOSTON   |   3 |
| ACCOUNTING |     10 | NEW YORK |   5 |
| RESEARCH   |     20 | DALLAS   |   5 |
| SALES      |     30 | CHICAGO  |   5 |
| OPERATIONS |     40 | BOSTON   |   5 |
| ACCOUNTING |     10 | NEW YORK |   6 |
| RESEARCH   |     20 | DALLAS   |   6 |
| SALES      |     30 | CHICAGO  |   6 |
| OPERATIONS |     40 | BOSTON   |   6 |
+------------+--------+----------+-----+
12 rows in set (0.00 sec)

// 4、最后在上一步基础上进行where筛选即可
mysql> select dname, dept.deptno, loc, sum from dept, (select deptno, count(*) as sum from emp group by deptno) as sumTable where dept.deptno=sumTable.deptno;
+------------+--------+----------+-----+
| dname      | deptno | loc      | sum |
+------------+--------+----------+-----+
| ACCOUNTING |     10 | NEW YORK |   3 |
| RESEARCH   |     20 | DALLAS   |   5 |
| SALES      |     30 | CHICAGO  |   6 |
+------------+--------+----------+-----+
3 rows in set (0.00 sec)

5. 子查询使用场景总结

子查询得到的结果可以在下面两个场景中使用：

用在where字句中作为筛选条件去使用。
用在from后去和其他的表做笛卡尔积。

四. 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union和union all去进行合并查询。

1. union

介绍：该操作符用于取得两个结果集的并集并完成去重。

举例：将工资大于2500或职位是MANAGER的人找出来

// 工资和职位都在emp表中存在，所以可以直接在该表中进行where筛选
mysql> 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.00 sec)

// 说明1：如果两个select查询出来的结果字段数量不同，会报错并终止SQL语句
mysql> select ename from emp where sal>2500 
    -> union 
    -> select ename, sal from emp where job='MANAGER';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

// 说明2：如果两个select查询出来的结果字段数量相同，但是字段不同会导致结果错误，但不会报错
mysql> select ename, sal from emp where sal>2500 
    -> union 
    -> select ename, job from emp where job='MANAGER';
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+
8 rows in set (0.00 sec)

2. union all

介绍：该操作符用于取得两个结果集的并集并且不会去重。

举例：将工资大于25000或职位是MANAGER的人找出来

// 1、union的去重版本（共有6行记录）
mysql> 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.00 sec)

// 2、union all的不去重版本（共有8行记录）
mysql> select ename, sal, job from emp where sal>2500 union all 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.00 sec)

Luojj_2020

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【MySQL】多表查询

结果显示：发现MySQL从其中一张表中选出一条记录去和另外一张表的所有记录进行组合，重复这个过程直到一开始那张表的所有记录全部被组合完成，这样子不加筛选条件的直接组合在一起显示出来的结果都叫做笛卡尔积。另外，emp员工表中有一个字段叫做deptno表示该员工所在部门的部门号，这个字段是一个外键，其主表是dept部门表：// 1、查看dept部门表的数据 mysql > select * from dept;
复制链接

扫一扫