08 -- 复合查询
1. 多表查询
例:显示雇员名、雇员工资和所在部门的名字需要emp和dept表,所以需要联合查询
本质就是数据的穷举,将多表转为一张表
select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno;
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10;// 显示部门号为10的部门名,员工名和工资
select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;// 显示各个员工的姓名,工资,及工资级别
2. 自连接
是指在同一张表连接查询
例子:显示员工FORD的上级领导的编号和姓名
select empno.ename from emp where emp.empno=(select mgr from emp where ename = 'FORD');// 使用子查询
// 使用多表查询
select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='FORD';// 给自己的表起别名,因为要先做笛卡尔积,所以别名可以先识别
3. 子查询
是指嵌入在其他sql语句中的select语句,也叫做嵌套查询
3.1 单行子查询
select * from emp where deptno=(select deptno from emp where ename = 'smith');// 查询和smith同一部门的员工
3.2 多行子查询
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno<>10;
// in关键字;查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);
// all关键字;显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno = 30);
// any关键字;显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
3.3 多列子查询
单列子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句。
select ename from emp where (deptno,job)=(select deptno,job from emp where ename='smith') and ename <> 'smith';
// 查询和smith的部门和岗位完全相同的所有雇员,不含smith本人
3.4 在from子句中使用子查询
把一个子查询当作一个临时表使用
// 显示每个高于自己部门平均工资的员工的姓名、部门、工资和平均工资。获取各个部门的平均工资,将其看作一个临时表
select ename,deptno,sal,format(asal,2) from emp,(select avg(sal) asal,deptno from emp group by deptno) tmp where emp.sal > tmp.sal and emp.deptno = tmp.deptno;
// 查找每个部门工资最高的人的姓名、工资、部门、最高工资
select emp.name,emp.sal,emp.deptno,ms from emp,(select max(sal),deptno from emp group by deptno) tmp where emp.deptno=emp.deptno and emp.sal=tmp.ms;
// 显示每个部门的信息(部门名、编号、地址)和人员数量
// 使用多表
select dept.dname,dept.deptno,dept.loc,count(*) '部门人员' from emp,dept where emp.deptno = dept.deptno group by dept.deptno,dept.dname,dept.loc;
// 使用子查询
select count(*),deptno from emp group by deptno;
select dept.deptno,dname,mycnt,loc from dept,(select count(*) mycnt,deptno from emp group by deptno) tmp where dept.deptno = tmp.deptno;
3.5 合并查询
为了合并多个select的执行结果
3.5.1 union
用于取到两个结果集的并集,当使用该操作符时,会自动去掉结果集中的重复行
// 将工资大于2500或职位时MANAGER的人找出来
select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'manager';
3.5.2 union all
用于取到两个结果集的并集,当使用该操作符时,不会去掉结果集中的重复行
// 将工资大于2500或职位时MANAGER的人找出来
select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'manager';