1. SQL(高级查询)
1.1. 子查询
1.1.1. 子查询在WHERE子句中
在SELECT查询中,在WHERE查询条件中的限制条件不是一个确定的值,而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。
子查询:嵌入在其它SQL语句中的SELECT语句,大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句,也可以是其它类型的语句比如DML或DDL语句。
根据返回结果的不同,子查询可分为单行子查询、多行子查询及多列子查询。
![](http://pdf7.tarena.com.cn/tts8_source/ttsPage/JAVA/JSD_V05/ORACLE/DAY04/SUPERDOC/01/index.files/image001.png)
图-1子查询
例如查找和SCOTT同职位的员工:
- SELECT e.ename, e.job
- FROM emp e
- WHERE e.job =
- (SELECT job FROM emp WHERE ename = 'SCOTT');
查找薪水比整个机构平均薪水高的员工:
- SELECT deptno, ename, sal
- FROM emp e
- WHERE sal> (SELECT AVG(sal) FROM emp);
如果子查询返回多行,主查询中要使用多行比较操作符,包括IN、ALL、ANY。其中ALL和ANY不能单独使用,需要配合单行比较操作符>、>=、<、<=一起使用。例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息:
- SELECT empno, ename, job, sal, deptno
- FROM emp
- WHERE deptno IN
- (SELECT deptno FROM emp WHERE job = 'SALESMAN')
- AND job <> 'SALESMAN';
在子查询中需要引用到主查询的字段数据,使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据,则整个条件返回TRUE。如果子查询没有结果,则返回FALSE。例如列出来那些有员工的部门信息:
- SELECT deptno, dname FROM dept d
- WHERE EXISTS
- (SELECT * FROM emp e
- WHERE d.deptno = e.deptno);
1.1.2. 子查询在HAVING部分
子查询不仅可以出现在WHERE子句中,还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息:
- SELECT deptno, MIN(sal) min_sal
- FROM emp
- GROUP BY deptno
- HAVING MIN(sal) >
- (SELECT MIN(sal) FROM emp WHERE deptno = 30);
1.1.3. 子查询在FROM部分
在查询语句中,FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询,则子查询出现在FROM 子句中,这个子查询也称作行内视图或者匿名视图。这时,把子查询当作视图对待,但视图没有名字,只能在当前的SQL语句中有效。
查询出薪水比本部门平均薪水高的员工信息:
- SELECT e.deptno, e.ename, e.sal
- FROM emp e,
- (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
- WHERE e.deptno = x.deptno
- ande.sal>x.avg_sal
- ORDER BY e.deptno;
1.1.4. 子查询在SELECT部分
把子查询放在SELECT子句部分,可以认为是外连接的另一种表现形式,使用更灵活:
- SELECT e.ename, e.sal, e.deptno,
- (SELECT d.deptno FROM dept d
- WHERE d.deptno = e.deptno) deptno
- FROM emp e;
1.2. 分页查询
1.2.1. ROWNUM
ROWNUM被称作伪列,用于返回标识行数据顺序的数字。例如:
- SELECT ROWNUM, empno, ename, sal
- FROM emp;
ROWNUM只能从1计数,不能从结果集中直接截取。下面的查询语句将没有结果:
- SELECT ROWNUM, empno, ename, sal
- FROM emp
- WHERE rownum> 3;
如果利用ROWNUM截取结果集中的部分数据,需要用到行内视图:
- SELECT * FROM
- (SELECT ROWNUMrn , e.* FROM emp e