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';