MySQL 多表查询

前期准备

#建表
create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;

按照之前的思路，如果我们想要查表是不是应该这样写，那我们看下结果。

select * from emp,dep; 

mysql> select * from emp,dep;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  1 | jason | male   |   18 |    200 |  201 | 人力资源     |
|  1 | jason | male   |   18 |    200 |  202 | 销售         |
|  1 | jason | male   |   18 |    200 |  203 | 运营         |
|  2 | egon  | female |   48 |    201 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |

左表一条记录与右表所有记录都对应一遍叫笛卡尔积，将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据。

内连接

内连接：只取两张表有对应关系的记录：只取两张表有对应关系的记录 关键字inner join

查询员工及所在部门的信息

select * from emp inner join dep on emp.dep_id = dep.id;

查询部门为技术部的员工及部门信息

select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技术";

左连接

在内连接的基础上保留左表没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;

+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | nick  | female |   28 |    202 |  202 | 销售         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+

右连接

在内连接的基础上保留右表没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | nick  | female |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+

全连接

在内连接的基础上保留左、右面表没有对应关系的的记录

select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;


+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | nick  | female |   28 |    202 |  202 | 销售         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+

子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

查询部门是技术或者人力资源的员工信息

思路：先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息

select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");

查询每个部门最新入职的员工

思路：先查每个部门最新入职的员工，再按部门对应上联表查询

select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;

记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询