数据库（九）：多表查询②数据库——多表查询

进击のpython

---

数据库——多表查询

---

那接着上一节说，其实在真正的操作中，不光是要把数据联合，还需要进行筛选数据，比如：

打印员工平均年龄大于三十的部门

拿到一个需求教你怎么写！

需要员工员工年龄和部门

员工年龄存放在employee中，部门存放在deparement中

应该是什么连接呢？根据需求应该是内连接~（这个，你自己理解一下就可以了嘛）

复制代码select * from employee inner join department on employee.dep_id=department.id;

1
select * from employee inner join department on employee.dep_id=department.id;

复制代码mysql> select * from employee inner join department on employee.dep_id=department.id; +----+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | +----+-----------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
mysql> select * from employee inner join department on employee.dep_id=department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name
| sex
| age
| dep_id | id
| name
|
+----+-----------+--------+------+--------+------+--------------+
|
1 | egon
| male
|
18 |
200 |
200 | 技术
|
|
2 | alex
| female |
48 |
201 |
201 | 人力资源
|
|
3 | wupeiqi
| male
|
38 |
201 |
201 | 人力资源
|
|
4 | yuanhao
| female |
28 |
202 |
202 | 销售
|
|
5 | liwenzhou | male
|
18 |
200 |
200 | 技术
|
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

拿到一个虚拟表，怎么叫虚拟表呢？因为这个表不是硬盘尚存的，而是临时拼在内存里的

但！他是一张表！

根据题意应该是部门之间的员工的年龄进行比较，所以就应该分组，按照什么分组呢？？？

如果按照id（dep_id），那我select后面拿到的只能是id（dep_id）【参考分组时说的知识点】

所以就应该用部门的名字name字段，但是发现了吗？有两个name字段，所以应该强制指定：

复制代码select department.name from employee inner join department on employee.dep_id=department.id group by department.name;

1
2
select department.name from employee inner join department on employee.dep_id=department.id
group by department.name;

接着我们要员工年龄的平均值大于三十，是不是应该用having过滤一下：

复制代码select department.name from employee inner join department on employee.dep_id=department.id group by department.name having avg(age)>30;

1
2
3
select department.name from employee inner join department on employee.dep_id=department.id
group by department.name
having avg(age)>30;

为了清晰一点，加个字符串拼接：

复制代码select concat('平均年龄超过三十的部门：',department.name) from employee inner join department on employee.dep_id=department.id group by department.name having avg(age)>30;

1
2
3
select concat('平均年龄超过三十的部门：',department.name) from employee inner join department on employee.dep_id=department.id
group by department.name
having avg(age)>30;

复制代码mysql> select concat('平均年龄超过三十的部门：',department.name) from employee inner join department on employee.dep_id=department.id -> group by department.name -> having avg(age)>30; +----------------------------------------------------------------+ | concat('平均年龄超过三十的部门：',department.name) | +----------------------------------------------------------------+ | 平均年龄超过三十的部门：人力资源 | +----------------------------------------------------------------+ 1 row in set (2.76 sec)

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select concat('平均年龄超过三十的部门：',department.name) from employee inner join department on employee.dep_id=department.id
-> group by department.name
-> having avg(age)>30;
+----------------------------------------------------------------+
| concat('平均年龄超过三十的部门：',department.name)
|
+----------------------------------------------------------------+
| 平均年龄超过三十的部门：人力资源
|
+----------------------------------------------------------------+
1 row in set (2.76 sec)

表头不好看，重命名一下：

复制代码select concat('平均年龄超过三十的部门：',department.name) info from employee inner join department on employee.dep_id=department.id group by department.name having avg(age)>30;

1
2
3
select concat('平均年龄超过三十的部门：',department.name) info from employee inner join department on employee.dep_id=department.id
group by department.name
having avg(age)>30;

复制代码mysql> select concat('平均年龄超过三十的部门：',department.name) info from employee inner join department on employee.dep_id=department.id -> group by department.name -> having avg(age)>30; +--------------------------------------------------+ | info | +--------------------------------------------------+ | 平均年龄超过三十的部门：人力资源 | +--------------------------------------------------+ 1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select concat('平均年龄超过三十的部门：',department.name) info from employee inner join department on employee.dep_id=department.id
-> group by department.name
-> having avg(age)>30;
+--------------------------------------------------+
| info
|
+--------------------------------------------------+
| 平均年龄超过三十的部门：人力资源
|
+--------------------------------------------------+
1 row in set (0.00 sec)

那这个需求就做出来了

而至此！select的终极版语法才算是真正的水落石出：

定义顺序：

复制代码SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>

1
2
3
4
5
6
7
8
9
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行顺序：

复制代码(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(7)
SELECT
(8)
DISTINCT <select_list>
(1)
FROM <left_table>
(3)
<join_type> JOIN <right_table>
(2)
ON <join_condition>
(4)
WHERE <where_condition>
(5)
GROUP BY <group_by_list>
(6)
HAVING <having_condition>
(9)
ORDER BY <order_by_condition>
(10)
LIMIT <limit_number>

---

子查询

除了这种查询方法，还有子查询，子查询就是把查询的结果，当做另一个表的查询条件

那刚才的例子，也可以这么搞，我先拿到部门平均年龄过30的id，然后再去employee去找对应的部门名

带in的子查询

先拿到超过30的id：

复制代码select dep_id from employee inner join department on employee.dep_id=department.id group by dep_id having avg(age)>30;

1
2
3
select dep_id from employee inner join department on employee.dep_id=department.id
group by dep_id
having avg(age)>30;

复制代码mysql> select dep_id from employee inner join department on employee.dep_id=department.id -> group by dep_id -> having avg(age)>30; +--------+ | dep_id | +--------+ | 201 | +--------+ 1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
mysql> select dep_id from employee inner join department on employee.dep_id=department.id
-> group by dep_id
-> having avg(age)>30;
+--------+
| dep_id |
+--------+
|
201 |
+--------+
1 row in set (0.00 sec)

拿到了之后进行传值：

复制代码select name from department where id in ( select dep_id from employee inner join department on employee.dep_id=department.id group by dep_id having avg(age)>30);

1
2
3
4
select name from department where id in (
select dep_id from employee inner join department on employee.dep_id=department.id
group by dep_id
having avg(age)>30);

复制代码mysql> select name from department where id in ( -> -> select dep_id from employee inner join department on employee.dep_id=department.id -> -> group by dep_id -> -> having avg(age)>30); +--------------+ | name | +--------------+ | 人力资源 | +--------------+ 1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select name from department where id in (
->
-> select dep_id from employee inner join department on employee.dep_id=department.id
->
-> group by dep_id
->
-> having avg(age)>30);
+--------------+
| name
|
+--------------+
| 人力资源
|
+--------------+
1 row in set (0.00 sec)

是不是也达到了需求~

查询技术部员工的姓名怎么写？

复制代码select name from employee where dep_id in ( select dep_id from employee inner join department on employee.dep_id=department.id where department.name = "技术" group by dep_id);

1
2
3
4
select name from employee where dep_id in (
select dep_id from employee inner join department on employee.dep_id=department.id
where department.name = "技术"
group by dep_id);

复制代码mysql> select name from employee where dep_id in ( -> select dep_id from employee inner join department on employee.dep_id=department.id -> where department.name = "技术" -> group by dep_id); +-----------+ | name | +-----------+ | egon | | liwenzhou | +-----------+ 2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select name from employee where dep_id in (
-> select dep_id from employee inner join department on employee.dep_id=department.id
-> where department.name = "技术"
-> group by dep_id);
+-----------+
| name
|
+-----------+
| egon
|
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)

查看不足1人的部门名这个怎么做？

这个其实就用到了数学的思想~总数里去掉一人及以上的，是不是就是不足一人的了

而只要是在用户表里面的用户拥有dep_id的，是不是都是有部门的，去掉这一部分是不是就满足了题意

select name from department where id not in (select distinct dep_id from employee);

(子查询得到的是有人的部门id)

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

1
2
3
4
5
6
7
8
9
10
mysql> select name from department where id not in (select distinct dep_id from employee);
+--------+
| name
|
+--------+
| 运营
|
+--------+
1 row in set (0.47 sec)

带比较运算符的子查询

提一句吧，既然拿到的是数据，就像1 12 3 一样的，你就把它看作个数字，当然是可以比较的

查询大于所有人平均年龄的员工名与年龄

select avg(age) from employee;查询所有员工的年龄 # 28.000

那在你心里，此时这一串乱七八糟的东西就是28.000

此时题意就变成，所有年龄大于28.000的员工名与年龄

select name,age from employee where age > 28.000;然后将28.000进行“等量代换”

select name,age from employee where age > (select avg(age) from employee);

复制代码mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name
| age
|
+---------+------+
| alex
|
48 |
| wupeiqi |
38 |
+---------+------+
2 rows in set (0.00 sec)

查询大于部门内平均年龄的员工名、年龄

部门内，一看就是分组，而且应该是以部门进行分组

复制代码select employee.name,employee.age from employee inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t2 on employee.dep_id = t2.dep_id where employee.age > t2.avg_age;

1
2
3
4
5
select employee.name,employee.age from employee
inner join
(select dep_id,avg(age) avg_age from employee group by dep_id) t2
on employee.dep_id = t2.dep_id
where employee.age > t2.avg_age; 

复制代码mysql> select employee.name,employee.age from employee -> inner join -> (select dep_id,avg(age) avg_age from employee group by dep_id) t2 -> on employee.dep_id = t2.dep_id -> where employee.age > t2.avg_age; +------+------+ | name | age | +------+------+ | alex | 48 | +------+------+ 1 row in set (0.34 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select employee.name,employee.age from employee
-> inner join
-> (select dep_id,avg(age) avg_age from employee group by dep_id) t2
-> on employee.dep_id = t2.dep_id
-> where employee.age > t2.avg_age;
+------+------+
| name | age
|
+------+------+
| alex |
48 |
+------+------+
1 row in set (0.34 sec)

但是这个employee太长了，所以，给他重新命名比较好（知道为什么是t2了吧）

复制代码select employee.name,employee.age from employee t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

1
2
3
4
5
select employee.name,employee.age from employee t1
inner join
(select dep_id,avg(age) avg_age from employee group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 

带exists关键字的子查询

理解成if

select * from employee where exists (select id from department where id=200);

括号里是不是不为空，那就是真，就相当于if True：于是就有打印结果
select * from employee where exists (select id from department where id=204);
括号里是不是为空，那就是假，就相当于if False：于是没有打印结果

---

接下来借着这个例子给你讲讲一种思想：我连我自己

每个职位最新入职的员工

自己创建表：

复制代码create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int );

1
2
3
4
5
6
7
8
9
10
11
12
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

插入数据：

复制代码insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

select post,max(hire_date) from employee group by post;

复制代码mysql> select post,max(hire_date) from employee -> -> group by post; +--------------------------------+----------------+ | post | max(hire_date) | +--------------------------------+----------------+ | operation | 2016-03-11 | | sale | 2017-01-27 | | teacher | 2015-03-02 | | 驻沙河办事处外交大使 | 2017-03-01 | +--------------------------------+----------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select post,max(hire_date) from employee
->
-> group by post;
+--------------------------------+----------------+
| post
| max(hire_date) |
+--------------------------------+----------------+
| operation
| 2016-03-11
|
| sale
| 2017-01-27
|
| teacher
| 2015-03-02
|
| 驻沙河办事处外交大使
| 2017-03-01
|
+--------------------------------+----------------+

这是不是每个部门最新入职的员工，但是，你虽然看到了这张表，但事实上是虚拟的，是不存在的

所以我们为了让它存在的“合法化”，我们应该将其整体重新命名：

(select post,max(hire_date) from employee group by post) as t2;

我们要的是那名员工！

我们新建的表是不是有职位和日期！employee是不是也有部门和日期，这是不是就联系上了？

那我们就可以通过这个联系来找到这些员工

复制代码select * from employee as t1 inner join (select post,max(hire_date) hire_date from employee group by post) as t2 on t1.post = t2.post and t1.hire_date=t2.hire_date;

1
2
3
4
select * from employee
as t1 inner join
(select post,max(hire_date) hire_date from employee group by post) as t2 on
t1.post = t2.post and t1.hire_date=t2.hire_date;

复制代码mysql> select * from employee as t1 inner join -> -> (select post,max(hire_date) hire_date from employee group by post) as t2 on -> -> t1.post = t2.post and t1.hire_date=t2.hire_date; +----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | post | hire_date | +----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+ | 1 | egon | male | 18 | 2017-03-01 | 驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 驻沙河办事处外交大使 | 2017-03-01 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | teacher | 2015-03-02 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | sale | 2017-01-27 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | operation | 2016-03-11 | +----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+ 4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
mysql> select * from employee
as t1 inner join
->
-> (select post,max(hire_date) hire_date from employee group by post) as t2 on
->
-> t1.post = t2.post and t1.hire_date=t2.hire_date;
+----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+
| id | name
| sex
| age | hire_date
| post
| post_comment | salary
| office | depart_id | post
| hire_date
|
+----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+
|
1 | egon
| male
|
18 | 2017-03-01 | 驻沙河办事处外交大使
| NULL
|
7300.33 |
401 |
1 | 驻沙河办事处外交大使
| 2017-03-01 |
|
2 | alex
| male
|
78 | 2015-03-02 | teacher
| NULL
| 1000000.31 |
401 |
1 | teacher
| 2015-03-02 |
| 13 | 格格
| female |
28 | 2017-01-27 | sale
| NULL
|
4000.33 |
402 |
2 | sale
| 2017-01-27 |
| 14 | 张野
| male
|
28 | 2016-03-11 | operation
| NULL
|
10000.13 |
403 |
3 | operation
| 2016-03-11 |
+----+--------+--------+-----+------------+--------------------------------+--------------+------------+--------+-----------+--------------------------------+------------+
4 rows in set (0.00 sec)

当然也可以用where来做，你自己尝试做一下

复制代码select * from employee as t1 inner join (select post,max(hire_date) hire_date from employee group by post) as t2 on t1.post = t2.post where t1.hire_date=t2.hire_date;

1
2
3
4
5
select * from employee
as t1 inner join
(select post,max(hire_date) hire_date from employee group by post) as t2 on
t1.post = t2.post
where t1.hire_date=t2.hire_date;

---

***** *****