05. 基础篇 - 多表查询

多表关系

一对多（多对一）

多对多

一对一

多表查询概述

内连接

-- 查询每一个员工的姓名，及关联部门名称(隐式内连接)
-- 表结构：name，dept
-- 连接条件：emp.dept_id = dept_id
select emp.name ,dept.name from emp, dept
                where emp.dept_id = dept_id;

-- 查询每一个员工的姓名，及关联部门名称(显式内连接)
-- 表结构：name，dept
-- 连接条件：emp.dept_id = dept_id
select e.name ,d.name from emp e inner join dept d on
                e.dept_id = d.id;

外连接

-- 查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp dept
-- 连接条件：emp.dept_id = dept.id
select e.* ,d.name from emp e left outer join dept d on e.dept_id = d.id;

-- 查询emp表的所有数据，和对应的部门信息（右外连接）
-- 表结构：emp dept
-- 连接条件：emp.dept_id = dept.id
select d.* ,e.* from emp e right outer join dept d on d.id = e.dept_id;

自连接

-- 查询员工及其所属领导的名字
-- 表结构：emp.a emp.b
-- 连接条件：emp.managerid = emp.id
select a.name '员工' ,b.name '领导' from emp a ,emp b where a.managerid = b.id;

-- 查询所有员工emp及其领导的名字emp如果员工没有领导，也需要查询出来
-- 表结构：emp.a emp.b
-- 连接条件：emp.managerid = emp.id
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出来(不去重)
select *from emp where salary < 5000
union all
select *from emp where age > 50;

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出来(去重)
select *from emp where salary < 5000
union
select *from emp where age > 50;

子查询

标量子查询

-- 查询 “销售部” 的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 查询在 “方东白” 入职之后的员工信息
select *from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

-- 查询销售部和市场部的所有员工信息
select *from emp where dept_id in ( select id from dept where name = '销售部' or name = '市场部');


-- 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部'));


-- 查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select *from emp where 
(salary ,managerid) = (select salary ,managerid from emp where name = '张无忌');

表子查询

-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select *from emp where (job ,salary) in
(select job ,salary from emp where name = '鹿杖客' or name = '宋远桥');


-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.* ,d.name from (select * from emp where entrydate > '2006-01-01') e
left join dept d on e.dept_id = d.id;

多表查询案例

-- 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, e.age, e.job, d.name
from emp e,
     dept d
where e.dept_id = d.id;


-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select e.name, e.age, e.job, d.name
from emp e
         inner join dept d on e.dept_id = d.id and e.age < 30;


-- 3. 查询拥有员工的部门ID、部门名称
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
select distinct d.id, d.name
from emp e,
     dept d
where e.dept_id = d.id;


-- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
-- 表: emp , dept
-- 连接条件: emp.dept_id = dept.id
-- 外连接
select e.*, d.name
from emp e
         left join dept d on e.dept_id = d.id
where (e.age > 40);


-- 5. 查询所有员工的工资等级
-- 表: emp , salgrade
-- 连接条件 : emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.*, s.grade
from emp e,
     salgrade s
where e.salary between s.losal and s.hisal;


-- 6. 查询 "研发部" 所有员工的信息及 工资等级
-- 表: emp , salgrade , dept
-- 连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id = dept.id
-- 查询条件 : dept.name = '研发部'
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where (e.dept_id = d.id)
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';


-- 7. 查询 "研发部" 员工的平均工资
-- 表: emp , dept
-- 连接条件 :  emp.dept_id = dept.id
select avg(e.salary)
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';


-- 8. 查询工资比 "灭绝" 高的员工信息。
select *
from emp
where salary > (select salary from emp where emp.name = '灭绝');


-- 9. 查询比平均薪资高的员工信息
select *
from emp
where salary > (select avg(salary) from emp);


-- 10. 查询低于本部门平均工资的员工信息
select *
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);


-- 11. 查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '部门人数'
from dept d;


-- 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
-- 表: student , course , student_course
-- 连接条件: student.id = student_course.studentid , course.id = student_course.courseid
select s.name, s.no, c.name
from student s,
     student_course sc,
     course c
where s.id = sc.studentid
  and sc.courseid = c.id;

总结