子查询

子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等

带IN关键字的子查询

 1#查询平均年龄在25岁以上的部门名
 2select id,name from department
 3    where id in 
 4        (select dep_id from employee group by dep_id having avg(age) > 25);
 5#查看技术部员工姓名
 6select name from employee
 7    where dep_id in 
 8        (select id from department where name='技术');
 9#查看不足1人的部门名(子查询得到的是有人的部门id)
10select name from department where id not in (select distinct dep_id from employee);

** 带比较运算符的子查询**

 1#比较运算符：=、!=、>、>=、<、<=、<>
 2#查询大于所有人平均年龄的员工名与年龄
 3mysql> select name,age from emp where age > (select avg(age) from emp);
 4+---------+------+
 5| name | age |
 6+---------+------+
 7| alex | 48 |
 8| wupeiqi | 38 |
 9+---------+------+
102 rows in set (0.00 sec)
11#查询大于部门内平均年龄的员工名、年龄
12select t1.name,t1.age from emp t1
13inner join 
14(select dep_id,avg(age) avg_age from emp group by dep_id) t2
15on t1.dep_id = t2.dep_id
16where t1.age > t2.avg_age; 

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

 1#department表中存在dept_id=203，Ture
 2mysql> select * from employee
 3    ->     where exists
 4    ->         (select id from department where id=200);
 5+----+------------+--------+------+--------+
 6| id | name       | sex    | age  | dep_id |
 7+----+------------+--------+------+--------+
 8|  1 | egon       | male   |   18 |    200 |
 9|  2 | alex       | female |   48 |    201 |
10|  3 | wupeiqi    | male   |   38 |    201 |
11|  4 | yuanhao    | female |   28 |    202 |
12|  5 | liwenzhou  | male   |   18 |    200 |
13|  6 | jingliyang | female |   18 |    204 |
14+----+------------+--------+------+--------+
15#department表中存在dept_id=205，False
16mysql> select * from employee
17    ->     where exists
18    ->         (select id from department where id=204);
19Empty set (0.00 sec)