【15】子查询

1. 子查询

在查询语句中，需要有一部分语句先执行，这部分语句的执行结果，作为外层查询语句的一部分（结果集、条件），
这部分先行执行的查询，就叫做子查询。

2. 子查询使用方式

2.1 子查询的结果作为外层查询的条件（where）

• 查询 和 WARD 部门相同的员工信息

	select * from Emp where deptNo = (select deptNo from Emp where ename = 'WARD');

2.2 子查询的结果作为外层查询的表（from后）

• 子查询的结果，作为外层查询的一部分数据
• 查询员工编号，员工姓名，经理编号，经理姓名

    select e.empNo,e.ename,e.mgr,m.ename from Emp e, (select * from Emp) m 
   		where e.mgr = m.empNo
   			or (e.mgr is null and e.ename = m.ename);
   			
   	select e.*,d.dname from Dept d,(select * from Emp where ename like '%A%') e
   		where d.deptNo = e.deptNo;
   	
   	select * from Student s,(select * from Grade) g
   		where s.grade_Id = g.grade_Id
   			and s.sex = '女';

2.3 子查询的结果作为外层查询的列（from前）

• 子查询的结果放在 from 之前，子查询中可以直接使用from后面的表中的列

	select d.*,(select count(empNo) from Emp e where e.deptNo = d.deptNo) from Dept d;

3. 子查询和表连接的关系

子查询可以替代表连接，不是所有的表连接都能替换子查询

4. 子查询的结果，作为外层查询的条件（where）

4.1. 等值子查询

当子查询的结果明确是一个值的时候，使用 = 、> 、<、>=、<=、<>、!= 连接子查询

• 查询 部门名称是SALES的员工信息

 	select * from Emp where deptNo = (select deptNo from Dept where dname = 'SALES')

4.2. 范围子查询

当子查询的结果返回多个值的时候，使用 in 、not in、any 、all 连接子查询

• in ： 表示范围
• any ： 表示任何一个，如果 " > any “, 则取最小值；如果” < any "，则取最大值；
• all： 表示所有的，如果 " > all “, 则取最大值；如果” < all "，则取最小值；

	select * from Emp where deptNo = (select deptNo from Dept where dname like '%S%')
	> ORA-01427: 单行子查询返回多个行
	select * from Emp where deptNo in(select deptNo from Dept where dname like '%S%')
	
	select * from Emp where deptNo = any(select deptNo from Dept where dname like '%S%')

	--查询比姓名中含有a的员工sal还高的员工信息
	select empNo,ename,sal from Emp where sal > all(select sal from Emp where ename like '%A%')
 
	--列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称
 	select ename,sal,dname from Emp e , Dept d
		where e.deptNo = d.deptNo 
			and	sal > all(select sal from Emp where deptNo = 30)

	select ename,sal,(select dname from Dept where deptNo = e.deptNo) from Emp e
		where	sal > all(select sal from Emp where deptNo = 30)

5. Oracle 伪列

伪列的使用可以简化对数据库的操作

• sysdate ： 表示当前系统时间
• sequence.nextval：序列的下一个值
• sequence.currval：序列的当前值
• rowid ： 表示数据行确切的存储位置
• rownum ： 检索的数据行号（从1开始，不能间断）
• uid : 用户标识
• user : 当前的用户名

	select uid from dual; -- 48
	select user from dual; -- SCOTT
	
	select rowid, d.* from Dept d;
	
		ROWID                  		 DEPTNO 		DNAME			LOC
		--------------------------
		AAAE5hAABAAALCxAAA         20 				RESEARCH		DALLAS
		AAAE5hAABAAALCxAAB         30 				SALES			CHICAGO
		AAAE5hAABAAALCxAAC         40 				OPERATIONS		BOSTON
		AAAE5hAABAAALCxAAD         10 				ACCOUNTING		NEW YORK
	
	select rownum, d.* from Dept d;     

6. 标准的 Oracle 三层分页语句

	select * from -- 最外层，添加rownum的起始边界（别名）
	(
		select rownum r , e.* from -- 中间层，添加rownum的结束边界
		(
			select * from Emp where 1=1 ORDER BY empNo asc -- 最内层：基础的查询(where、group by、order by)
		) e
		where rownum <= 10
	) 
	where r >= 6

7. 带有分页参数的分页语句

• pageIndex ：当前页码（从1开始）
• pageSize：每页的总条数

	select * from -- 最外层，添加rownum的起始边界（别名）
	(
		select rownum r , e.* from -- 中间层，添加rownum的结束边界
		(
			select * from Emp where 1=1 ORDER BY empNo asc -- 最内层：基础的查询(where、group by、order by)
		) e
		where rownum <= (pageIndex * pageSize)
	) 
	where r > (pageIndex-1) * pageSize

8. 总页数计算

• totalPage ： 总页数
• totalCount： 总记录数 [count(*) where]
• pageSize：每页的总条数

	totalPage = (totalCount % pageSize == 0) ?
		 totalCount / pageSize : totalCount / pageSzie + 1;