oracle学习笔记４: SQL与面向集合

列出定单之间的平均天数

drop table job_history; 
create table job_history(
employee_id number primary key not null,
 start_date date,
 end_date date,
 ename varchar2(30),
 job  varchar2(30)
); 

delete from job_history; 
insert into job_history values(1,to_date('2014-07-07','yyyy-mm-dd'),to_date('2015-07-07','yyyy-mm-dd'),'polly','sales');
insert into job_history values(8,to_date('2015-07-07','yyyy-mm-dd'),to_date('2018-07-07','yyyy-mm-dd'),'polly','finance');
insert into job_history values(2,to_date('2014-06-07','yyyy-mm-dd'),to_date('2015-06-07','yyyy-mm-dd'),'hanmeimei','secrectory');
insert into job_history values(9,to_date('2015-06-07','yyyy-mm-dd'),to_date('2017-06-07','yyyy-mm-dd'),'hanmeimei','finance');
insert into job_history values(3,to_date('1985-06-07','yyyy-mm-dd'),to_date('1987-02-07','yyyy-mm-dd'),'litao','engineer');
insert into job_history values(10,to_date('1987-06-07','yyyy-mm-dd'),to_date('2000-02-07','yyyy-mm-dd'),'litao','operations person');
insert into job_history values(4,to_date('2003-06-07','yyyy-mm-dd'),to_date('2006-04-07','yyyy-mm-dd'),'lilei','manager');
insert into job_history values(11,to_date('2006-08-07','yyyy-mm-dd'),to_date('2009-04-07','yyyy-mm-dd'),'lilei','ceo');
insert into job_history values(5,to_date('1999-01-07','yyyy-mm-dd'),to_date('1999-08-07','yyyy-mm-dd'),'uncle wang','cto');
insert into job_history values(12,to_date('2009-05-07','yyyy-mm-dd'),to_date('2010-08-07','yyyy-mm-dd'),'uncle wang','sales');
insert into job_history values(6,to_date('2001-07-07','yyyy-mm-dd'),to_date('2005-06-07','yyyy-mm-dd'),'jim','police');
insert into job_history values(13,to_date('2005-07-07','yyyy-mm-dd'),to_date('2009-10-07','yyyy-mm-dd'),'jim','sales');
insert into job_history values(7,to_date('2004-09-07','yyyy-mm-dd'),to_date('2008-06-07','yyyy-mm-dd'),'green','waiter');
insert into job_history values(14,to_date('2008-09-07','yyyy-mm-dd'),to_date('2015-06-07','yyyy-mm-dd'),'green','waiter');
select distinct employee_id, ename
  from job_history j1
 where not exists
 (select null
          from job_history j2
         where j2.employee_id = j1.employee_id
           and round(months_between(j2.start_date, j2.end_date) / 12, 0) <>
               round(months_between(j1.start_date, j1.end_date) / 12, 0));

select employee_id
  from job_history
 group by employee_id
having min(round(months_between(start_date, end_date) / 12, 0)) = max(round(months_between(start_date, end_date) / 12, 0));

步骤处理流程图与嵌套处理流程图

SQL是基于集合理论的，集合中的行没有预先确定顺序，排序就必须在符合查询条件的数据行都从集合中抽取出来之后再单独进行。

准备测试数据

--创建orders表
drop table orders;
create table orders(
  order_id number primary key,
  customer_id number，
  sales_rep_id number,
  order_total number,
  order_date date
);
drop sequence seq;
create sequence seq;
--准备测试数据
delete from orders;
insert into orders VALUES(seq.nextval,1,4,10000,to_date('2017-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,2,4,10000,to_date('2004-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,2,4,10000,to_date('2017-08-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,1,4,10000,to_date('2010-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,1,4,10000,to_date('2012-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,2,4,10000,to_date('1999-08-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,2,4,10000,to_date('1998-09-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,1,4,10000,to_date('1995-07-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,1,4,10000,to_date('2008-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,2,4,10000,to_date('2009-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2017-08-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2016-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2015-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2018-02-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2017-01-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,3,4,10000,to_date('2014-03-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,4,4,10000,to_date('2014-02-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,4,4,10000,to_date('2014-06-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,4,10000,to_date('2014-08-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,4,10000,to_date('2014-09-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,5,10000,to_date('2014-02-06 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,4,100,to_date('2011-02-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,7,10800,to_date('2013-02-05 18:26','yyyy-mm-dd HH24:MI'));
insert into orders VALUES(seq.nextval,5,8,1000,to_date('2016-02-05 18:26','yyyy-mm-dd HH24:MI'));
SELECT * from orders;
commit;

面向过程的思维方式

--列出客户的定单数
select customer_id, order_date from orders where customer_id = 5;
--根据当前行的日期决定之前的日期
select customer_id,
       order_date,
       lag(order_date, 1, order_date) over(partition by customer_id order by order_date) as pre_order_date
  from orders
 where customer_id = 5;
--决定定单之间的天数
select trunc(order_date)-trunc(pre_order_date) days_between from (
select customer_id,
       order_date,
       lag(order_date, 1, order_date) over(partition by customer_id order by order_date) as pre_order_date
  from orders
 where customer_id = 5
);
--放在一起做平均数，得到最终答案
select avg(trunc(order_date)-trunc(pre_order_date)) avg_days_between from (
select customer_id,
       order_date,
       lag(order_date, 1, order_date) over(partition by customer_id order by order_date) as pre_order_date
  from orders
 where customer_id = 5
);

基于集合的思维方式

select (max(trunc(order_date)) - min(trunc(order_date))) / count(*) as avg_days_between
  from orders
 where customer_id = 5;

计算定单之间的平均天数，只需计算第一笔和最后一笔的定单天数以及总的定单数即可。不需要一个个计算定单，一个个汇总。

集合运算

集合运算符将两个或更多select语句的结果合并形成一个结果集。
联结是用来将不同表中的列组合起来形成一行。

集合运算比较所输入查询的所有行并返回一个包含重复值的行集。

所有的集合运算都必须符合下面的条件：

所有的输入查询必须返回相同数目的列
每一列的数据类型必须与对应的其它输入查询一致
order by子句不能在某个单独的查询中应用，只能用在整个查询的最后，用来排序整个集合运算的结果集
列名源自第一个输入查询

oracle支持４种集合运算符： union all,union, minus,intersect

union all: 返回两个集合中的所有行，包含重复。

union all结果集的恩维图

--例１
SQL> select 'tomluo' as name from dual
  2  union all
  3  select 'tomluo' as name from dual;

NAME
------
tomluo
tomluo


--例２
create table table1(
id_pk integer not null primary key,
color varchar(10) not null
);

create table table2(
id_pk integer not null primary key,
color varchar(10) not null
);
create table table3(
color varchar(10) not null
);
insert into table1 values(1,'red');
insert into table1 values(2,'red');
insert into table1 values(3,'orange');
insert into table1 values(4,'orange');
insert into table1 values(5,'orange');
insert into table1 values(6,'yellow');
insert into table1 values(7,'green');
insert into table1 values(8,'blue');
insert into table1 values(9,'blue');
insert into table1 values(10,'violet');
insert into table2 values(1,'red');
insert into table2 values(2,'red');
insert into table2 values(3,'blue');
insert into table2 values(4,'blue');
insert into table2 values(5,'blue');
insert into table2 values(6,'green');
commit;


select color from table1 
union all
select color from table2;
select color from table1;

union: 返回来自所有输入查询的不包含重复的结果集

union结果集的恩维图

SQL> select 'tomluo' as name from dual
  2  union
  3  select 'tomluo' as name from dual;

NAME
------
tomluo

select color from table1 
union
select color from table2;
select color from table3;
select color from table1 
union 
select color from table3;
--下面的有错，列不一样
select * from table1 
union 
select color from table2;

minus：返回在第一个输入查询中存在但接下来的查询中不存在的非重复数据行
将第一个查询的结果集作为基础数据集减去另一个查询结果集。minus通用来替代not exists（反联结）查询。

minus结果集的恩维图

select color from table1 
minus
select color from table2;

select color from table1 
minus
select color from table3;

intersect: 返回在所有输入查询中都存在的重复行
intersect用来返回在所有输入查询中都存在的唯一行集。intersect通常用来代替exists（半联结）。

intersect结果集的恩维图

select color from table1 
intersect
select color from table2;

集合与空值

空值: 空值并不是一个值，最多是一个标记，可以理解为‘我不知道’。

--列出所有的行从tem表中
select * from emp;
--列出部门号为1,2,3,4的用户
select * from emp where deptno in (1, 2, 3, 4);
--列出部门号不为1,2,3,4的用户
select * from emp where deptno not in (1, 2, 3, 4);
--列出部门号不为1,2,3,4的用户或部门为空值的用户
select * from emp where deptno not in (1, 2, 3, 4);

除非显式声明，它们不会被包含在结果集中。
空值不能用来比较，空值不能与任何东西进行加，减，乖，除运算。

select * from emp where deptno is null;

select * from emp where deptno = null;

insert into emp
  (empno, ename, deptno, job, mgr, hiredate, sal)
values
  (10, 'jiantao', 4, 'engineer', 2, sysdate, 5800);

select sal,comm,sal+comm as tot_cmp from emp where deptno=4;

关系模型是基于两个值(真，假）的逻辑关系，但是SQL语言支持３个值(真，假和未知)的逻辑。空值代表未知，所以很可能返回错误的值。

空值与集合运算

--空值与集合运算
select null from dual
union 
select null from dual;

select null from dual
union all
select null from dual;

select null from dual
intersect
select null from dual;

select null from dual
minus
select null from dual;

select 1 from dual
union
select null from dual;

select 1 from dual
union all
select null from dual;

select 1 from dual
intersect
select null from dual;

select 1 from dual
minus
select null from dual;

当具有空值的两行进行联合时，你只会得到一行。这就表明这两行是相等等的，因此在进行联合时重复行就可以被去掉了。
集合运算将所有的空值作为相等的值来对待。

空值与group by 、order by

group by和order by子句也将空值作为可以用等式进行比较的值来对等。空值总是会像其他己知值那样被放在一起。

select comm, count(*) str from scott.emp group by comm;
select comm, count(*) str from scott.emp group by comm order by comm;
select comm, count(*) str
  from scott.emp
 group by comm
 order by comm nulls first;

select ename, sal, comm from scott.emp order by comm, ename;

默认的排序规则是把空值排到最后。

空值与聚合函数

当在聚合函数如sum,count,avg,min以及max等包含的列中出现空值时，它们将会被从聚合中去掉。如果去掉后集合变成空的，聚合则会返回空值。

select count(*) row_ct,
       count(comm) comm_ct,
       avg(comm) avg_comm,
       min(comm) min_comm,
       max(comm) max_comm,
       sum(comm) sum_comm
  from scott.emp;

count()和count(comm)的区别：　使用得到所有的行数。而使用comm是comm值为非空的行数。如果在计算avg,min,max及sum值时，如果有空值，所有的这些函数最后只返回一个结果。如果空值没有被去掉，这些函数将会返回空值。

要想写出简单易懂且性能会过程化方法更好的ＳＱＬ语句，需要掌握的核心技心就是以集合的方式来思考。如果按照过程化的方式思考时，就会尝试强制让非过程化的ＳＱＬ语言按照不必要的方式来实现其功能。

最后编辑于：2017.12.03 07:52:54

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,332评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,508评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,812评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,607评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,728评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,919评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,071评论 3赞 410
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,802评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,256评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,576评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,712评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,389评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,032评论 3赞 316
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,798评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,026评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,473评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,606评论 2赞 350