复杂查询—子查询

基本概念

• 子查询：指嵌入在其他sql语句中的select语句，也叫嵌套查询
• 单行子查询：指只返回一行数据的子查询语句
• 多行子查询：指返回多行数据的子查询，使用关键词 in 或者 all

单行子查询

显示与smith同一部门的所有员工

//子查询
mysql> select ename,deptno from emp where deptno = (select deptno from emp where ename = 'smith');
+-------+--------+
| ename | deptno |
+-------+--------+
| smith |     20 |
| jones |     20 |
| scott |     20 |
| ford  |     20 |
+-------+--------+
4 rows in set (0.00 sec)

//再次过滤 不显示smith
mysql> select ename,deptno from emp where deptno = (select deptno from emp where ename = 'smith') and ename <> 'smith';
+-------+--------+
| ename | deptno |
+-------+--------+
| jones |     20 |
| scott |     20 |
| ford  |     20 |
+-------+--------+
3 rows in set (0.00 sec)

多行子查询 in

查询和部门10的工作相同的雇员的信息, 但是不含10自己的

mysql> select * from emp where job in (select distinct job from emp where deptno = 10) and deptno <> 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | smith | clerk   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | jones | manager | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7900 | james | clerk   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+-------+---------+------+------------+---------+------+--------+
4 rows in set (0.00 sec)

多行子查询 all

显示工资比部门30的所有员工的工资都高的员工的信息（显示工资比部门30最高工资高的员工信息）

//方式一：使用all  大于所有
mysql> select * from emp where sal > all(select sal from emp where deptno = 30);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | king  | president | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+