多表查询
笛卡尔积
-- 查询
select * from emp ,dept where emp.dept_id = dept.id;
-- 查询
select * from emp ,dept where emp.dept_id = dept.id;-- ============================= 内连接 ==========================
-- A. 查询所有员工的ID, 姓名 , 及所属的部门名称 (隐式、显式内连接实现)
-- 隐式
select emp.id, emp.name,dept.name from emp ,dept where emp.dept_id = dept.id;-- 显式 inner 可以省略
select emp.id, emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id ;-- B. 查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称 (隐式、显式内连接实现)
-- 隐式
select emp.id, emp.name,dept.name from emp ,dept where emp.dept_id = dept.id and emp.gender = 1 and emp.salary>8000;-- 显式
select emp.id, emp.name,dept.name from emp join dept on emp.dept_id = dept.id where emp.gender = 1 and emp.salary>8000;-- 为表起别名 as 可以省略
select e.id, e.name,dept.name from emp as e join dept on e.dept_id = dept.id where e.gender = 1 and e.salary>8000;
-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)out 可以省略
select e.name,d.name from emp e left outer join dept d on d.id = e.dept_id;-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)out 可以省略
select e.name,d.name from emp e right outer join dept d on d.id = e.dept_id;-- C. 查询工资 高于8000 的 所有员工的姓名, 和对应的部门名称 (左外连接)
select e.name,d.name from emp e left join dept d on d.id = e.dept_id where salary >8000;select e.name,d.name from dept d right join emp e on d.id = e.dept_id where salary >8000;
-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 最早入职 的员工信息
-- a.先去获取最早入职时间
select min(entry_date) from emp;-- b.查询 最早入职 的员工信息
select * from emp where entry_date = '2000-01-01';select * from emp where entry_date = (select min(entry_date) from emp);-- B. 查询在 "阮小五" 入职之后入职的员工信息
-- a.先去获取阮小五入职时间
select entry_date from emp where name = '阮小五';-- b.查询 阮小五入职之后 的员工信息
select * from emp where entry_date > '2015-01-01';select * from emp where entry_date > (select entry_date from emp where name = '阮小五');-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
-- a.先需要去查询"教研部" 和 "咨询部" 的部门ID
select id from dept where name = '教研部' or name = '咨询部';-- b.查询指定部门ID的员工信息
select * from emp where dept_id in (2,3);select * from emp where dept_id in (select id from dept where name = '教研部' or name = '咨询部');-- 行子查询
-- A. 查询与 "李忠" 的薪资 及 职位都相同的员工信息 ;
-- a.查询与 "李忠" 的薪资 及 职位
select salary,job from emp where name = '李忠';-- b.查询指定薪资和职位的员工信息
select * from emp where salary = 5000 and job = 5;select * from emp where salary = (select salary from emp where name = '李忠') and job = (select job from emp where name = '李忠');-- 优化：
select * from emp where (salary,job) = (5000,5);select * from emp where (salary,job) = (select salary,job from emp where name = '李忠');-- 表子查询
-- A. 获取每个部门中薪资最高的员工信息
-- a.获取每个部门中的最高薪资
select dept_id,max(salary) from emp group by dept_id;-- b.查询每个部门中薪资最高的员工信息
select * from emp e,(select dept_id,max(salary) max_sal from emp group by dept_id) awhere e.dept_id = a.dept_id and e.salary = a.max_sal;
-- 需求:
-- 1. 查询 "教研部" 性别为 男，且在 "2011-05-01" 之后入职的员工信息 。
-- 表：dept ,emp
select * from emp where dept_id = (select id from dept where dept.name = '教研部') and gender = 1 and entry_date > '2011-05-01';select * from emp e,dept d where e.dept_id=d.id and d.name = '教研部' and e.gender = 1 and entry_date > '2011-05-01';-- 2. 查询工资 低于公司平均工资的 且 性别为男 的员工信息 。
-- 表：emp
-- 2.1 查询 公司的平均工资
select avg(salary) from emp;-- 2.2 查询工资 低于公司平均工资的 且 性别为男 的员工信息 。
select * from emp where salary < (select avg(salary) from emp) and gender = 1;-- 3. 查询部门人数超过 10 人的部门名称 。
-- 表：emp dept
select d.name,count(*) from emp e,dept d where e.dept_id=d.id group by d.name having count(*)>10;select name from dept where id = (select dept_id from (select count(*) c,dept_id from emp group by dept_id) a where a.c >10);-- 4. 查询在 "2010-05-01" 后入职，且薪资高于 10000 的 "教研部" 员工信息，并根据薪资倒序排序。
-- 表：emp dept
select e.* from emp e,dept d where e.dept_id=d.id and entry_date>'2010.05.01' and salary > 10000 and d.name= '教研部' order by salary desc ;select * from emp where entry_date>'2010-05-01' and salary>10000 and dept_id=(select id from dept where dept.name = '教研部') order by salary desc ;-- 5. 查询工资 低于本部门平均工资的员工信息 。
-- 表：emp dept
-- 5.1 查询每个部门的平均薪资
select dept_id,avg(salary) avg_sal from emp group by dept_id;-- 5.2 查询工资 低于本部门平均工资的员工信息 。
select * from emp e,(select dept_id,avg(salary) avg_sal from emp group by dept_id) a where e.dept_id = a.dept_id and e.salary<a.avg_sal;select * from (select emp.*,avg from emp ,(select avg(salary) avg,dept_id from emp group by dept_id) a where a.dept_id=emp.dept_id) ans where salary<avg;