多表连接

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

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

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

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

1、内连接：把两张表有对应关系的记录连接成一张虚拟表,(在硬盘上是两张表,在内存上建了一张虚拟的表)
select * from emp inner join dep on emp.dep_id = dep.id;

#应用：
    1):select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活 尽量用2

    2):select * from emp inner join dep on emp.dep_id = dep.id
        where dep.name = "技术"
    ;#where过滤的作用

2、左连接：在内连接的基础上，保留左边没有对应关系的记录
select * from emp left join dep on emp.dep_id = dep.id;


3、右连接：在内连接的基础上，保留右边没有对应关系的记录
select * from emp right join dep on emp.dep_id = dep.id;


4、全连接：在内连接的基础上，保留左、右边没有对应关系的记录
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;

#补充：多表连接可以不断地与虚拟表连接

查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;

 多表连接查询

#补充：多表连接可以不断地与虚拟表连接

查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;