数据库-多表查询

 外连接与内连接

 

-- 查询部门及所属部门名称，隐式内连接
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

-- 起别名
select e.name,q.name from tb_emp e,tb_dept q where e.dept_id=q.id;

-- 外连接
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id=tb_dept.id;


-- 左外连接
select e.name,q.name from tb_emp e left join tb_dept q on e.dept_id=q.id;


-- 右外连接
select e.name,q.name from tb_emp e right join tb_dept q on e.dept_id=q.id;

 子查询

-- 查询教研部所有员工信息
select * from tb_emp where dept_id=(select id from tb_dept where name='教研部');

-- 查询方东白入职后的信息
select * from tb_emp where update_time>=(select create_time from tb_emp where name='方东白');

-- 查询教研部和学工部员工信息
select *from tb_emp where tb_emp.dept_id in(select id from tb_dept where name='教研部'or name='学工部');

-- 查询教研部所有员工信息
select * from tb_emp where dept_id=(select id from tb_dept where name='教研部');

-- 查询方东白入职后的信息
select * from tb_emp where update_time>=(select create_time from tb_emp where name='方东白');

-- 查询教研部和学工部员工信息
select *from tb_emp where tb_emp.dept_id in(select id from tb_dept where name='教研部'or name='学工部');

-- 查询与韦一笑入职日器和职位相同的员工信息
select * from tb_emp where (create_time,dept_id)=(select create_time,dept_id from tb_emp where name ='韦一笑');

-- 查询入职时间2006-01-01之后的员工信息，及其部门信息
select e.*,q.name from (select * from tb_emp where entrydate>'2006-01-01') e ,tb_dept q where e.dept_id=-q.id;