一、子查询

当我们需要执行某条SQL语句时，需要某些查询的结果作为依据时，先期执行的这条语句就是子查询

1
SELECT name,sal FROM WHERE sal > （SELECT sal FROM emp WHERE name = 'CLERK')

除了在SELECT中使用子查询外，也可以在DDL、DML中使用子查询

1
2
3
CREATE TABLE myemp AS (SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc 
		       FROM mep e,dept d 
		       WHERE e.deptno=d.deptno)

1
DELETE FROM amp WHERE deptno=(SELECT deptno FROM WHERE ename='CLERK')

子查询根据查询的结果不同可分为：
1.单行单列子查询：查询结果为1个值
2.多行单列子查询：查询结果为一个字段，但是有多条记录

1
2
3
SELECT name,job 
FROM emp 
WHERE job IN(SELECT job FROM dept WHERE deptno=10)

1
2
3
4
SELECT MIN(sal),deptno 
FROM emp 
GROUP BY deptno 
HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno=30)

3.多行多列子查询：查询结果集像一张表，也常当做表来用

1
2
3
4
5
6
SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,deptno
            FROM emp
            GROUP BY deptno) s
WHERE e.deptno=s.deptno
AND e,sal>s.avg_sal

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字，EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE ,判断子查询不返回数据用NOT EXISTS

1
2
3
SELECT deptno,name 
FROM dept d 
WHERE EXISTS (SELECT * FROM amp e WHERE d.deptno = e.deptno)

在SELECT子句中使用子查询相当于外连接

1
2
SELECT e.ename,(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) 
FROM emp e;

二、分页查询

当表中数据量很大时，一次性查询表中全部数据会导致数据全部加载到内存中，对系统资源占用有很大影响，响应速度也慢，但往往用户也不需要一次性将这么多数据全部查看，这时我们可以分批查询表中数据。
由于标准SQL没有对分页进行语法定义，所以不同的数据库中分页的语法不一致。

ORACLE中有ROWNUM字段，称为伪列
该字段并不存在于任何一张表中，但是可以从任何一张表去查询该字段，当查询该字段时，ORACLE只要可以从表中查询出一条记录，就会为该条记录中该字段添加行号。行号从1开始，会自动增长。

1
SELECT ROWNUM,name FROM amp where ROWNUM <= 5

由于ROWNUM只有查询出一条数据后，才会对该记录编号，然后ROWNUM自增，所以我们不能在第一次查询表中数据时使用ROWNUM>1以上的数字进行过滤，否则将得不到任何数据。为此，需要进行两次查询，第一次仅使用ROWNUM为结果集编号，再基于这个结果集进行二次查询，根据ROWNUM的编号取需要的范围内的记录。

1
SELECT * FROM (SELECT ROWNUM rn,ename FROM FROM emp) WHERE rn >= 5 AND rn <= 10

当分页查询有排序需求时，要先排序再编号，原因在于：使用ROWNUM对结果集编号是在查询过程中进行的，而ORDER BY排序是在查询出结果后进行的。所以，当排序过后，原本的编号顺序就打乱了。为此，我们要先排序，再将排序后的结果集进行编号，才能得到正确的结果。

1
2
3
4
SELECT * 
FROM（SELECT ROWNUM rn,t.* 
      FROM (SELECT * FROM emp ORDER BY sal DESC)  t）
WHERE rn > 5 AND rn <= 10

pageSize：每页显示的条目数
page：页数
start = （page - 1） pageSize + 1
end = pagepageSize

三、DECODE 函数

DECODE

1
DECODE(expr,search1,result1[,search2,result2…][,default])

  • DECODE用于比较参数expo的值，如果匹配到哪一个search条件，就返回对应的result结果
  • 可以有多组search和result的对应关系，如果任何一个都没有匹配到，则返回最后default的值
  • default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL
1
2
3
4
5
6
7
8
SELECT name,job
DECODE(job,
       'MANAGER',sal * 1.2,
       'ANLYST',sal * 1.1,
       'SALESMAN',sal * 1.05,
       sal
) bonus
FROM emp;

和DECODE函数功能相似的有CASE语句

1
2
3
4
5
6
7
SELECT ename,job,sal
CASE WHEN 'MANAGER' THEN sal * 1.2
     WHEN 'ANLYST' THEN sal * 1.1
     WHEN 'SALSMAN' THEN sal * 1.05
     ELSE sal END
     bonus
FROM emp;

按字段内容分组

1
2
3
4
5
6
7
8
9
10
SELECT DECODE (job,
               'ANALYST','VIP',
               'MANAGER','VIP',
               'OPERATION'),
               job,COUNT(*) job_cnt
FROM emp
GROUP BY DECODE(job,
                'ANALYST','VIP',
                'MANAGER','VIP'
                'OPERATION');

按字段内容排序

1
2
3
4
5
6
SELECT deptno,name,loc
FROM dept
ORDER BY DECODE(dname,
                'OPERATIONS',1,
                'ACCOUNTING',2,
                'SALES',3);

四、排序函数

可以根据指定的字段分组，再根据一个指定的字段排序，然后生成一个组内的编号。

ROW_NUMBER

1
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)

表示根据col1分组，然后在分组内根据col2排序，生成组内连续且唯一的数字，排序也可以指定多个字段

1
2
3
4
5
SELECT name,sal,deptno,ROW_NUMBER() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
)
FROM emp

RANK 相同的数据返回相同排名，生成不连续也不唯一的数字
跳跃排序，如果有相同数据，则排名相同，但下一个排名不变

1
2
3
4
5
SELECT name,sal,deptno,ROW_NUMBER() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
)
FROM emp

DENSE_RANK 相同的数据返回相同排名，生成连续但不唯一的数字
如果有相同数据，则排名相同，但下一个排名连续

1
2
3
4
5
SELECT name,sal,deptno,DENSE_RANK() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
)
FROM emp

五、集合操作

UNION、UNION ALL 并集
UNION 不包含重复元素，UNION ALL 包含重复元素
UNION操作符对查询结果排序，UNION ALL不排序

1
2
3
SELECT ename,job FROM emp WHERE job = 'MANAGER'
UNION
SELECT ename,job FROM emp WHERE sal > 2500

INTERSECT 交集

1
2
3
SELECT ename,job FROM emp WHERE job = 'MANAGER'
INTERSECT
SELECT ename,job FROM emp WHERE sal > 2500

MINUS 差集,只有在第一个集合中存在，第二个集合中不存在的才被显示

1
2
3
SELECT ename,job FROM emp WHERE job = 'MANAGER'
MINUS
SELECT ename,job FROM emp WHERE sal > 2500

使用并、交、差集时，必须保证结果集的字段相同

六、高级分组函数

ROLLUP、CUBE和GROUPING SETS运算符是GROUP BY子句的扩展，可以生成与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询

ROLLUP
GROUP BY ROLLUP（a,b,c） 的话，那么分组分别会按照：
GROUP BY a,b,c
GROUP BY a,b
GROUP BY a
最后将全表看做一组统一计算一次
然后再将上述的分组结果并在一个结果集，n个参数的ROLLUP有n+1次分组

1
SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c)

等价于

1
2
3
4
5
6
7
SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,null,null,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT null,null,null,sun(d) FROM test

CUBE
CUBE是将给定的参数字段的每种组合都进行一次分组，然后将这些组合统计的结果集并在一起显示，n个参数的CUBE有2^n次分组
GROUP BY CUBE（a,b,c） 的话，那么分组分别会按照：
GROUP BY a,b,c
GROUP BY a,b
GROUP BY a,c
GROUP BY a
GROUP BY b,c
GROUP BY b
GROUP BY c
最后对全表进行GROUP BY操作，一共是2^3=8次分组

GROUPING SETS
可以根据自定义的组合方式进行分组，然后将这些分组的结果并在一个结果集中显示,n个参数的GROUPING SETS有n次分组
GROUP BY GROUPING SETS（(a,b,c),(a,b)） 的话，那么分组分别会按照：
GROUP BY a,b,c
GROUP BY a,b
GROUPING SETS参数允许重复