为方便演示示例：首先做如下初始化数据操作。
CREATE TABLE TEST_BANK(
BANKID varchar2(100),
BANKNAME varchar2(100)
);
INSERT INTO TEST_BANK(BANKID,BANKNAME) VALUES('1','银行1号');
INSERT INTO TEST_BANK(BANKID,BANKNAME) VALUES('2','银行2号');
CREATE TABLE TEST_CUSTOMER(
CUSTOMERID varchar2(100),
CUSTOMERNAME varchar2(100)
);
INSERT INTO TEST_CUSTOMER(CUSTOMERID,CUSTOMERNAME) VALUES('003','张三');
INSERT INTO TEST_CUSTOMER(CUSTOMERID,CUSTOMERNAME) VALUES('004','李四');
CREATE TABLE TEST_CUSTOMER2(
CUSTOMERID varchar2(100),
CUSTOMERNAME varchar2(100)
);
INSERT INTO TEST_CUSTOMER2(CUSTOMERID,CUSTOMERNAME) VALUES('003','张三');
INSERT INTO TEST_CUSTOMER2(CUSTOMERID,CUSTOMERNAME) VALUES('005','王五');
COMMIT;
执行完数据初始化后，查询数据如下所示：
注：以下每一个知识点都是以此数据为基础进行演示，部分演示示例会对表中数据进行修改。但是再结束后会手动将数据恢复至当前内容。即每一个演示示例彼此之间都没有任何数据联系。可单独拿出根据初始数据执行并查看结果。
MINUS（差集）
示例代码：SELECT * FROM TEST_CUSTOMER MINUS SELECT * FROM TEST_CUSTOMER2
查询结果：
INTERSECT（交集）示例代码:SELECT * FROM TEST_CUSTOMER INTERSECT SELECT * FROM TEST_CUSTOMER2
查询结果：
UNION/UNION ALL /Union：去重。/Union all ：不去重示例代码：SELECT * FROM TEST_CUSTOMER UNION SELECT * FROM TEST_CUSTOMER2;
查询结果：
示例代码：SELECT * FROM TEST_CUSTOMER UNION ALL SELECT * FROM TEST_CUSTOMER2;
查询结果：
LISTAGG() WITHIN GROUP()（类比group_concat）将多行合并成一行示例代码：SELECT INN.CUSTOMERID,INN.CUSTOMERNAME,LISTAGG(INN.CUSTOMERID2,'。我是分割标志呀。') WITHIN GROUP (ORDER BY INN.CUSTOMERID2) AS MERGES
FROM (SELECT C1.*,C2.CUSTOMERID AS CUSTOMERID2,C2.CUSTOMERNAME AS CUSTOMERNAME2 FROM TEST_CUSTOMER C1, TEST_CUSTOMER2 C2) INN
GROUP BY INN.CUSTOMERID,INN.CUSTOMERNAME;
查询结果：
MERGE INTO（类比insert on duplicate key）Merge into 在我现在接触的系统中用的比较多。
基本语法：
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
演示示例目标：演示从TEST_CUSTOMER2根据CUSTOMERID 向 TEST_CUSTOMER表中merge数据，若重复了，则更新CUSTOMERNAME,若不重复，则插入数据。
5.1、制造数据从而能看明白张三三覆盖张三:update TEST_CUSTOMER2 SET CUSTOMERNAME = '张三三' WHERE CUSTOMERID='003';
组织好的数据如下：
执行代码:
MERGE INTO TEST_CUSTOMER C1 USING TEST_CUSTOMER2 C2
ON(C1.CUSTOMERID = C2.CUSTOMERID)
WHEN MATCHED THEN
UPDATE SET CUSTOMERNAME = C2.CUSTOMERNAME
WHEN NOT MATCHED THEN
INSERT (CUSTOMERID,CUSTOMERNAME) VALUES(C2.CUSTOMERID,C2.CUSTOMERNAME)
执行结果:
ROWNUM
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类 推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。
rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。
演示示例：
ROWIDoracle数据库的表中的每一行数据都有一个唯一的标识符，或者称为rowid，在oracle内部通常就是使用它来访问数据的。
演示示例：
CONNECT BY START WITH（递归查询）select * from table [start with condition1]
connect by [prior] id=parentid
一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。
start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
创建数据库支持：
CREATE TABLE TEST_MENU(
MANUID varchar2(100),
MANUNAME varchar2(100),
MANULEVEL NUMBER(4),
PARENTID varchar2(100)
);
INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES
('1111','一级菜单',1,'0000');
INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES
('2222','二级菜单',2,'1111');
INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES
('3331','三级菜单1',3,'2222');
INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES
('3332','三级菜单2',3,'2222');
INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES
('3333','三级菜单3',3,'2222');
COMMIT;
创建后结果：
执行代码：SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)
FROM TEST_MENU TM
START WITH TM.MANUID = '1111'
CONNECT BY PRIOR TM.MANUID = TM.PARENTID
查询结果：
执行代码：
SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)
FROM TEST_MENU TM
START WITH TM.MANUID = '2222'
CONNECT BY PRIOR TM.MANUID = TM.PARENTID
查询结果：
注意比较两者的start with 条件。查询结果的差别。
上面一直演示的是从根节点向叶节点递归查询。下面演示从叶子节点向根节点递归查询的示例。
执行代码：
SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)
FROM TEST_MENU TM
START WITH TM.MANUID = '3333'
CONNECT BY TM.MANUID = PRIOR TM.PARENTID查询结果：
NULL值Null值不计数count。count()，遇到null值时，这条记录不会计算在内；CONCAT(a,null)结果为null等。
影响索引
给java开发带来空指针隐患。
Null值相比not null所占空间更大。
使用！=, NOT IN
自己在对于可空字段进行判断时，要判断not null and 字段!=”” ，更繁琐。
演示环境搭建：
INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME) VALUES('007',null);
COMMIT;
先查询一遍演示表中内容
执行脚本：select COUNT(*),COUNT(CUSTOMERID),COUNT(CUSTOMERNAME),MAX(CUSTOMERNAME),MIN(CUSTOMERNAME),CONCAT('A','B'),CONCAT(null,null) FROM TEST_CUSTOMER;查询结果：
1、ROW_NUMBER OVER()（同分不同排名）搭建演示环境：
create table TEST_ROW_NUMBER_OVER(
id varchar(10) not null,
name varchar(10) null,
age varchar(10) null,
salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
COMMIT;
搭建后结果：
10.1、一次排序：对查询结果进行排序（无分组）
演示脚本：
select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t;
查询结果:
10.2、根据id分组排序
演示脚本：
select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t;
查询结果：
RANK()同分排名搭建演示环境：
create table TEST_ROW_NUMBER_OVER(
id varchar(10) not null,
name varchar(10) null,
age varchar(10) null,
salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
COMMIT;
演示脚本：select id,name,age,salary,rank()over(order by id) rn
from TEST_ROW_NUMBER_OVER t ;
查询结果：
LAG/LEAD
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）
准备演示数据：
INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('100','100name');
INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('101','101name');
INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('102','102name');
INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('103','103name');
COMMIT;搭建好的演示数据：
演示向前脚本：SELECT CUSTOMERID,CUSTOMERNAME,lag(CUSTOMERNAME,1,0) over ( order by CUSTOMERID ) FROM TEST_CUSTOMER;
查询结果：
演示向后脚本：
SELECT CUSTOMERID,CUSTOMERNAME,lead(CUSTOMERNAME,1,0) over ( order by CUSTOMERID ) FROM TEST_CUSTOMER;查询结果：
SUM()OVER()累计排序。搭建演示环境：
create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);
insert into test values('11','smith','hangzhou','市场',1000);
insert into test values('12','smith','wenzhou','市场',2000);
insert into test values('13','allen','wenzhou','渠道',3000);
insert into test values('14','allen','wenzhou','渠道',4000);
insert into test values('15','jekch','shanghai','渠道',2500);
insert into test values('11','smith','hangzhou','市场',1000);
insert into test values('12','smith','wenzhou','市场',2000);
commit;
搭建完毕后：
演示一：统计全部。
测试脚本：
select sales_id,sales,dest,dept, revenue,sum(revenue) over() as 总销售额 from test
查询结果：
演示二：根据sales递归统计
测试脚本：
select sales_id,sales,dest,dept,revenue,sum(revenue)over(order by sales)递加销售总额 from test;
查询结果：
演示三：按人统计 测试脚本：
select sales_id,sales,dest,dept,revenue,sum(revenue)over(partition by sales_id) from test查询结果：
