请免费分享！严禁谋利！ Oracle学习笔记
常彦博
where id not in (select recommender_id from account where recommender_id is not null); 11.5多列子查询
where子句后面可以跟多列条件表达式。
eg1：哪些os帐号的开通时间是所在unix服务器上最早的？（每台unix服务器上最早开通的os帐号） select unix_host,os_username,create_date from service where (unix_host,create_date) in (select unix_host,min(create_date) from service group by unix_host); eg2：哪些os帐号的开通时间比所在unix服务器上最早开通时间晚九天 select unix_host,os_username,create_date from service where (unix_host,to_char(create_date,'yyyymmdd')) in (select unix_host,to_char(min(create_date) + 9,'yyyymmdd') from service group by unix_host); 20
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
十二、关联子查询
关联子查询采用的是循环（loop）的方式。
12.1语法
select column1,?from table1 o where column1 operator
(select column1,column2 from table2 i where i.expr1=o.expr2);
12.2执行过程
1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表查询。 2）内部查询基于传入的值执行。 3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成处理，若符合条件，outer表中得到的那条记录就放入结果集中，否则放弃。 4）重复执行1－3，直到把outer表中的所有记录判断一遍。子查询执行n遍。 eg:哪些os帐号的开通天数比同一台unix服务器上的平均开通天数长。 select unix_host,os_username,create_date,round(sysdate - create_date)open_age from service o where round(sysdate - create_date) > (selectavg(round(sysdate - create_date)) from service i where o.unix_host = i.unix_host); 12.3 exists exists采用的是循环（loop）的方式，判断outer表中是否存在在inner表中找到的一条匹配的记录。 12.4 exists执行过程 1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。 2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回true，将outer表中的记录放入结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回false，outer表中的该记录被过滤掉，不能出现在结果集中。 3）重复执行1－2，直到把outer表中的所有记录判断一遍。 eg1：哪些客户是推荐人 select real_name from account o where exists (select 1 from account i where o.id = i.recommender_id); //1可随便写，不关心结果什么样，只关心是否有满足的条件返回 eg2：哪些客户申请了远程登录业务
非关联子查询：
select real_name from account where id in (select account_id from service); 关联子查询：
select real_name from account o where exists (select 1 from service i where o.id = i.account_id); 21
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
12.5 not exists
采用的是循环（loop）的方式，判断outer表中是否不存在记录（它能在inner表中找到匹配的记录）。
12.6 not exists执行过程
1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。
2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回false，将outer表中的记录过滤掉，不能出现在结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回true，outer表中的该记录放入结果集中。 3）重复执行1－2，直到把outer表中的所有记录判断一遍。 eg1：哪些客户不是推荐人 select real_name from account o where not exists (select 1 from account i where o.id = i.recommender_id); eg2：哪些客户没有申请远程登录业务 非关联子查询： select real_name from account where id not in (select account_id from service); 关联子查询： select real_name from account o where not exists (select 1 from service i where o.id = i.account_id); 12.7 in和exists比较 1）exists是用循环（loop）的方式，有outer表的记录数决定循环次数，对于exists影响最大，所以，外表的记录数要少。 2）in先执行子查询，子查询的结果去重之后，再执行主查询，所以，子查询的返回结果越少，越适合用该方式。 22
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
十三、多表查询
结果集中的记录保存在多张表中。
13.1按范式要求设计表结构
第二范式：每个非主属性必须完全依赖于主属性（主键pk列）（避免多对多合表造成数据冗余）。
第三范式：每个非主属性不能依赖于另一个非主属性（避免一对多合表造成数据冗余，不一致）。
13.2多表连接的种类 交叉连接（cross join）、内连接（inner join）、外连接（outer join）。 13.3交叉连接 数学中的组合问题。 1）假设table1表中有m条记录，table2表中有n条记录，交叉连接产生的结果集为m*n。该结果产生的结果集为笛卡尔积。 2）语法： select tabname1.colname1,tabname2.colname2 from tabname1 cross join tabname2; eg：案例 select a.real_name,a.id,s.account_id,s.unix_host,s.os_username from account a cross join service s; 13.4内连接 核心解决匹配问题，建议用on and and多条件组合，不用where。 1）语法： select tabname1.colname1,tabname2.colname2 from tabname1 join tabname2 on tabname1.colname1=tabname2.colname2 and 其他条件; 2）如果有多个条件表达式，on关键字后面跟一个，其余用and条件连接。 eg：客户huangrong在哪些unix服务器上申请了远程登录业务 select a.real_name,s.unix_host,s.os_username,s.create_date from account a join service s on a.id = s.account_id and a.real_name = 'huangrong'; 3）内连接原理一： t1和t2表作内连接，连接条件为on t1.c1=t2.c2，假设t1表作驱动表，t2表作匹配表，记录过程如下：
①从t1表中读取一条记r1，若它的列c1值为1
②根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录
开始，若查找的记录的c2列的值为1，我们就说这两条记录能够匹配上，那么t1的r1和t2中刚刚匹配的该条记录组合起来，作为结果集里的一条记录，否则检测t2表中的下一条记录。
③按照步骤2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中。 ④从t1表中读取第二条记录，依次重复步骤2和3，产生最终的结果集。 eg：列出申请了远程登录业务的客户姓名以及在unix服务器上的开通信息 select a.real_name,s.unix_host,s.os_username,s.create_date from account a join service s on a.id = s.account_id;
23
请免费分享！严禁谋利！ Oracle学习笔记
常彦博
4）内连接原理二：
t1和t2表作内连接，连接条件为on t1.c1=t2.c2，假设t1表作驱动表，t2表作匹配
表，记录的匹配有如下三种情况：
①t1表中的某条记录在t2表中找不到任何一条匹配的记录，那么t1表中的该条记
录不会出现在结果集中。
②t1表中的某条记录在t2表中只有一条匹配的记录，那么t1表中的该记录和t2表
中匹配的记录组合成新的记录出现在结果集中。
③t1表中的某条记录在t2表中有多条匹配的记录，那么t1表中的该记录会和t2表
中每一条匹配的记录组合成新的记录出现在结果集中。
? 注意事项：内连接的核心为：任何一张表里的记录一定要在另一张表中找到匹
配的结果，否则不能出现在结果集中。 5）内连接原理三： t1和t2表作内连接，连接条件为on t1.c1=t2.c2，以下两种方式都可以得到相同的结果集： ①一种t1作驱动表，t2作匹配表 ②一种t2作驱动表，t1作匹配表 ③无论那种方式，最终得到的结果集都一样，所不同的是效率。 6）内连接的结果集结构： t1.c1 t1.c2 t1.c3 ?? t2.c1 t2.c2 t2.c3 7）内连接的语句执行顺序： 先根据on和and条件对要连接的表进行过滤，将过滤后的结果集进行内连接操作（join on），再根据select语句的定义生成最终的结果集。 注意事项：内连接中使用on和where都可以。 8）from后面可跟子查询 eg1：列出客户姓名以及开通的远程登录业务的数量 方式一：先连接再统计 select a.id,max(a.real_name),count(a.id) from account a join service s on a.id = s.account_id group by a.id; 方式二：先统计再连接，效率更高。 select a.real_name,count(a.id) from account a join (select account_id count(id) cnt from service group by account_id) c on a.id = c.account_id eg2：列出客户姓名以及他的推荐人（考查了内连接、空值转换、decode） select a1.real_name recommended,decode(a2.id,a1.id,'No Recommender',a2.real_name) recommender from account a1 join account a2 on nvl(a1.recommender_id,a1.id) = a2.id; 9）自连接： ①同一张表的行（记录）之间的匹配关系可以用同一张表的列之间的条件表达式描述。
②通过给表起别名，将同一张表的列之间的关系转换成不同表的列之间的条件表达式。
eg：哪些客户是推荐人
24