本节我们用一个简单的公司管理系统有三张表 EMP员工信息表, DEPT 部门信息表, SALGRADE 薪资等级表来演示子查询、多表查询、自连接目录子查询单列子查询单行子查询多行子查询 - in,all,any多列子查询在 from 子句中使用子查询合并查询多表查询语法笛卡尔积综合案例自连接子查询即在 select 中嵌套 select 查询嵌套的 select 称为子查询单列子查询单行子查询单行子查询即子查询的最终结果只有一行的子查询案例显示与 SMITH 同一部门的所有员工select * from EMP WHERE deptno (select deptno from EMP where enamesmith);多行子查询 - in,all,any多行子查询即子查询的最终结果有两行及以上的子查询in 关键字语法select ... from table where coulmn in (select 子查询的结果有两行及以上);作用如果 coulmn 列的数据在子查询结果中那么条件为真否则为假案例查询和 10 号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10号部门的雇员select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno10) and deptno ! 10;all 关键字语法select ... from table where coulmn all (select 子查询的结果有两行及以上); select ... from table where coulmn all (select 子查询的结果有两行及以上);作用如果 coulmn 列的数据比所有的子查询结果都要大(小)即大(小)于子查询结果的最大(小)值那么条件为真否则为假案例显示工资比 30 号部门的所有员工的工资都要高的员工的姓名、工资和部门select ename, sal, deptno from EMP where sal all(select sal from EMP where deptno 30);any 关键字语法select ... from table where coulmn any (select 子查询的结果有两行及以上); select ... from table where coulmn any (select 子查询的结果有两行及以上);作用只要 coulmn 列的数据 有 比子查询结果大(小)的那么条件为真否则为假案例显示工资只要比 30 号部门的任意员工的工资高的员工的姓名、工资和部门号包含 30 号部门的员工select ename, sal, deptno from EMP where sal any(select sal from EMP where deptno30);多列子查询单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言 的而多列子查询则是指查询返回多个列数据的子查询语句案例查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人mysql select ename from EMP where (deptno, job) (select deptno, job from EMP where enameSMITH) and ename ! SMITH; 也可以换成 ‘ in ’即把 (deptno, job) 看成一个整体如果子查询有多列则匹配所有与 (deptno, job) 相同的列。在 from 子句中使用子查询在 MySQL 中一切皆表包括 select 查询过程的临时表我们可以利用这些临时表与现有的表或其他临时表做笛卡尔积再从结果做筛选得到一些有用的信息。案例显示每个高于自己部门平均工资的员工的姓名、部门、工资、部门平均工资// 步骤1先显示每个部门的平均工资 mysql select DEPTNO,avg(sal) from EMP group by DEPTNO; --------------------- | DEPTNO | avg(sal) | --------------------- | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | --------------------- 3 rows in set (0.00 sec) // 步骤二将上面的临时表与 EMP 表做笛卡尔积 mysql select * from EMP,(select DEPTNO,avg(sal) mysql from EMP group by DEPTNO) as tmp; ------------------------------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | avg(sal) | ------------------------------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | 2916.666667 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 10 | 2916.666667 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 10 | 2916.666667 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 10 | 2916.666667 | //...(部分) // 步骤三过滤掉上面的临时表的无意义的行即 EMP.DEPTNO ! tmp.DEPTNO 的行 mysql select * from EMP,(select DEPTNO,avg(sal) mysql from EMP group by DEPTNO) as tmp where EMP.DEPTNO tmp.DEPTNO; ------------------------------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | avg(sal) | ------------------------------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | 2175.000000 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | 1566.666667 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | 1566.666667 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | 2175.000000 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | 1566.666667 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | 2916.666667 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | 2916.666667 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | 1566.666667 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | 2175.000000 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | 1566.666667 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | 2175.000000 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | 2916.666667 | ------------------------------------------------------------------------------------------- 14 rows in set (0.00 sec) // 步骤四利用上面的临时表筛选出高于自己部门平均工资的员工的姓名、部门、工资、部门平均工资 mysql select ENAME,SAL,EMP.DEPTNO,avg mysql from EMP,(select DEPTNO,avg(sal) as avg from EMP group by DEPTNO) as tmp mysql where EMP.DEPTNO tmp.DEPTNO and EMP.SAL avg; ------------------------------------- | ENAME | SAL | DEPTNO | avg | ------------------------------------- | ALLEN | 1600.00 | 30 | 1566.666667 | | JONES | 2975.00 | 20 | 2175.000000 | | BLAKE | 2850.00 | 30 | 1566.666667 | | SCOTT | 3000.00 | 20 | 2175.000000 | | KING | 5000.00 | 10 | 2916.666667 | | FORD | 3000.00 | 20 | 2175.000000 | ------------------------------------- 6 rows in set (0.00 sec)上面的最终结果的表还可以作为临时表与其他表做笛卡尔积......合并查询合并多个 select 的执行结果可以使用集合操作符 unionunion all语法select ... union select ...; select ... union all select ...;unionunion all 操作符用于取得两个结果集的并集。当使用union时会自动去掉结果集中的重复行。当使用union all时,不会去掉结果集中的重复行。案例将工资大于2500或职位是MANAGER的人找出来mysql select ename, sal, job from EMP where sal2500 - union select ename, sal, job from EMP where jobMANAGER; --------------------------- | 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 | --------------------------- mysql select ename, sal, job from EMP where sal2500 union all - select ename, sal, job from EMP where jobMANAGER; --------------------------- | 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 | ---------------------------多表查询实际开发中往往数据来自不同的表所以需要多表查询。本节我们用一个简单的公司管理系统有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。语法select ... from table_name1,table_name2,...;笛卡尔积案例显示雇员名、雇员工资以及所在部门的名字。雇员名、雇员工资来自 EMP 表部门的名字来自 DEPT 表。mysql show tables; ----------------- | Tables_in_scott | ----------------- | BONUS | | DEPT | | EMP | | SALGRADE | ----------------- 4 rows in set (0.00 sec) mysql select * from EMP; ---------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ---------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ---------------------------------------------------------------------- 14 rows in set (0.00 sec) 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) mysql select * from EMP,DEPT; ---------------------------------------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC | ---------------------------------------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 20 | RESEARCH | DALLAS | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 40 | OPERATIONS | BOSTON | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 20 | RESEARCH | DALLAS | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 40 | OPERATIONS | BOSTON | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 20 | RESEARCH | DALLAS | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 40 | OPERATIONS | BOSTON | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 20 | RESEARCH | DALLAS | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 40 | OPERATIONS | BOSTON | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 10 | ACCOUNTING | NEW YORK | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 20 | RESEARCH | DALLAS | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 40 | OPERATIONS | BOSTON | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 10 | ACCOUNTING | NEW YORK | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 20 | RESEARCH | DALLAS | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 40 | OPERATIONS | BOSTON | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 40 | OPERATIONS | BOSTON | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 20 | RESEARCH | DALLAS | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 30 | SALES | CHICAGO | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 40 | OPERATIONS | BOSTON | ---------------------------------------------------------------------------------------------------- 56 rows in set (0.00 sec)观察发现同时查询两张表EMP 和 DEPT时是把 EMP 的每一行与 DEPT的每一行都组合起来然后显示。这种穷举组合的方式称为两张表的笛卡尔积。但这种组合方式组合后的一些行是没有意义的比如---------------------------------------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC | ---------------------------------------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 10 | ACCOUNTING | NEW YORK | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 30 | SALES | CHICAGO | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 40 | OPERATIONS | BOSTON |史密斯在表中出现了四次而上表的第二行才是我们想要的所以我们要对 EMP 和 DEPT 合成的一张表进行条件筛选mysql select ENAME,SAL,DNAME from EMP,DEPT where EMP.DEPTNO DEPT.DEPTNO; ----------------------------- | ENAME | SAL | DNAME | ----------------------------- | CLARK | 2450.00 | ACCOUNTING | | KING | 5000.00 | ACCOUNTING | | MILLER | 1300.00 | ACCOUNTING | | SMITH | 800.00 | RESEARCH | | JONES | 2975.00 | RESEARCH | | SCOTT | 3000.00 | RESEARCH | | ADAMS | 1100.00 | RESEARCH | | FORD | 3000.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | TURNER | 1500.00 | SALES | | JAMES | 950.00 | SALES | ----------------------------- 14 rows in set (0.00 sec)综合案例显示部门号为10的部门名员工名和工资select ename, sal,dname from EMP, DEPT where EMP.deptnoDEPT.deptno and DEPT.deptno 10;显示各个员工的姓名工资及工资级别select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;自连接对同一张表做笛卡尔积叫做表的自连接语法select ... from table_name as 重命名1,table_name as 重命名2;案例显示员工FORD的上级领导的编号和姓名mysql select * from EMP; ---------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ---------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ---------------------------------------------------------------------- 14 rows in set (0.00 sec)方法一使用子查询select empno,ename from emp where emp.empno(select mgr from emp where enameFORD);方法二使用多表查询自连接select leader.empno,leader.ename from emp as leader, emp as worker where leader.empno worker.mgr and worker.enameFORD;