#子查询-某些情况下,当进行查询的时候,需要的条件是另外一个select语句的结果,这个时候就要用到子查询。用于子查询的关键字主要包括: in、not in、=、!=、exists、not exists等等。 #从emp表中查询出所有部门在dept表中的所有记录 mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 3 | hr | | 4 | sl | +--------+----------+ 4 rows in set (0.01 sec) mysql> select * from emp; +-------+------------+------------+---------+--------+------+ | ename | birth | hirdate | sal | deptno | age1 | +-------+------------+------------+---------+--------+------+ | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 | | zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 | | ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 | | ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 | +-------+------------+------------+---------+--------+------+ 4 rows in set (0.00 sec) mysql> select * from emp where deptno in(select deptno from dept); +-------+------------+------------+---------+--------+------+ | ename | birth | hirdate | sal | deptno | age1 | +-------+------------+------------+---------+--------+------+ | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 | | ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 | | zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 | | ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 | +-------+------------+------------+---------+--------+------+ 4 rows in set (0.00 sec) #如果子查询记录数唯一,还可以用=代替in mysql> select * from emp where deptno=(select deptno from dept limit 1); +-------+------------+------------+---------+--------+------+ | ename | birth | hirdate | sal | deptno | age1 | +-------+------------+------------+---------+--------+------+ | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 | +-------+------------+------------+---------+--------+------+ 1 row in set (0.00 sec) # 某些情况下,子查询可转化为表连接 mysql> select * from emp where deptno in(select deptno from dept); +-------+------------+------------+---------+--------+------+ | ename | birth | hirdate | sal | deptno | age1 | +-------+------------+------------+---------+--------+------+ | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 | | ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 | | zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 | | ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 | +-------+------------+------------+---------+--------+------+ 4 rows in set (0.00 sec) #转换为表连接后 mysql> select emp.* from emp,dept where emp.deptno=dept.deptno; +-------+------------+------------+---------+--------+------+ | ename | birth | hirdate | sal | deptno | age1 | +-------+------------+------------+---------+--------+------+ | zzx1 | 2000-01-01 | 2000-01-01 | 2000.00 | 1 | 21 | | ssss | 2019-01-01 | 2018-01-01 | 5000.00 | 2 | 24 | | zzx1 | 2002-03-09 | 2009-04-03 | 2001.00 | 3 | 22 | | ttx2 | 2023-04-10 | 2010-03-04 | 4000.00 | 4 | 23 | +-------+------------+------------+---------+--------+------+ 4 rows in set (0.00 sec) #union和union all的主要区别是union all是把结果集直接合并在一起 union是将union all后的结果在进行一次distinct,去除重复记录后的结果。 mysql> select deptno from emp union all select deptno from dept; +--------+ | deptno | +--------+ | 1 | | 3 | | 4 | | 2 | | 1 | | 2 | | 3 | | 4 | +--------+ 8 rows in set (0.00 sec) mysql> select deptno from emp union select deptno from dept; +--------+ | deptno | +--------+ | 1 | | 3 | | 4 | | 2 | +--------+ 4 rows in set (0.00 sec)