mysql 数据操作 多表查询 子查询 带IN关键字的子查询

 

 

1 带IN关键字的子查询

#查询平均年龄在25岁以上的部门名
关键点部门名

以查询员工表的dep_id的结果 当作另外一条sql语句查询条件使用 

in （sql语句）

mysql> select dep_id from employee group by dep_id having avg(age) >25 ;
+--------+
| dep_id |
+--------+
|    201 |
|    202 |
+--------+
2 rows in set (0.00 sec)

 

mysql> select name from department where id in (select dep_id from employee group by dep_id having avg(age) >25) ;
+--------------+
| name         |
+--------------+
| 人力资源     |
| 销售         |
+--------------+
2 rows in set (0.00 sec)

 

 

#查看技术部员工姓名

mysql> select name from employee where dep_id in (select id from department where name = "技术")  ;
+-----------+
| name      |
+-----------+
| mike      |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)

 

#查看不足1人的部门名

查询部门id 这里的部门人数至少有1人以上
一个id 对应一个员工 有得id对应多个员工

mysql> select dep_id from employee ;
+--------+
| dep_id |
+--------+
|    200 |
|    201 |
|    201 |
|    202 |
|    200 |
|    204 |
+--------+
6 rows in set (0.00 sec)

 

 

拿到是至少有一个人部门id  取反就是不足1人的部门id

mysql> select distinct dep_id from employee ;
+--------+
| dep_id |
+--------+
|    200 |
|    201 |
|    202 |
|    204 |
+--------+
4 rows in set (0.00 sec)

 

取反

mysql> select name from department where id not in(select distinct dep_id from employee) ;
+--------+
| name   |
+--------+
| 运营   |
+--------+
1 row in set (0.00 sec)