多表查询

一.多表数据记录查询

   department表

employee表

1.1 where条件连接

select e.name as 姓名,d.name as 部门 
from employee as e,department as d 
where e.dept_id=d.id

温馨小提示:仔细观察一下这排列顺序,李老师id是2,以此类推,明显默认是asc升序排序

1.2 inner join条件连接

 

-- 内联查询
select e.name as 姓名,d.name as 部门
from employee as e inner join department as d
on e.dept_id=d.id -- 连接条件

 

---

1.3 outer join外连接

 

department表

employee表

1 -- 左外连接查询 
2 -- 以左表为基准所以左表所有数据会被查询出来
3 -- 而右表查询出的结果以匹配结果为标准

select e.name as 姓名,d.name as 部门
from employee as e left outer join department as d
on e.dept_id=d.id

温馨小提示:仔细观察,因为左查询是以左表做基表,所以左表的所有数据会全部出现(陆老师没有dpet_id关联还是出现),右表的财务部没有出现(没有被关联到)

                    连接代码可简写为left join

 

-- 右外连接查询
-- 以右表为基准,所以右表所有数据都将被查询出来
-- 而左表查询出的结果以匹配结果为标准
select e.name as 姓名,d.name as 部门
from employee as e right outer join department as d
on e.dept_id=d.id

1.4 三表查询

-- 三表查询
select e.name as 姓名,d.name as 部门,a.address as 地址
from employee as e left join department as d on e.dept_id=d.id
left join addres as a on a.dept_id=d.id

 

---