1、内连接:把两张表有对应关系的记录连接成一张虚拟表
select * from emp inner join dep on emp.dep_id = dep.id;
#应用:
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活
select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术"
;
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
;
create table tec(id int,name char(10));
insert into tec value(1,"egon");
insert into tec value(2,"yyh");
create table stu(id int,name char(10));
insert into stu value(1,"大傻");
insert into stu value(2,"中傻");
insert into stu value(3,"小傻");
create table s_t(s_id int,t_id int);
insert into s_t value(1,2);
insert into s_t value(2,2);
insert into s_t value(3,1);
需求 找出 yyh 这个老师 教过的学生信息
思路:
第一步 到关系表中 去查询 哪些老师教过哪些学生(学生的id) 形成了一个临时表
第二步 将上一步得到临时表 与 学生表进行连接
第三步 加上额外的筛选条件 老师的name 是 yyh
select tec.name teacher,stu.name student from
tec inner join s_t on tec.id = s_t.t_id
inner join stu on s_t.s_id = stu.id
where tec.name = "egon" ;