oracle学习总结（三）

一、非关联子查询
1.什么叫子查询？
  子查询是在一条sql语句中嵌入select语句（不一定就是在select语句中)，外层的叫主查询，里层
  的就叫子查询。注：子查询结束之后不能加分号，因为整个语句才是一个整体，作为一个查询，只有等
  整个查询结束了才能加分号，表示查询结束
  
2.什么时候会用到子查询？
  一般的，子查询是放在条件表达式出现的地方（最多的是where后面），条件表达式一般格式为:
  列 运算符 值  
  以前，我们的值大都是常量或者表达式或者函数运算结果之类的，现在把值改为select查询语句
  就成了子查询了，既然子查询就是用来返回 值的，所以这个或这些返回值也可以被用来计算，或用
  函数处理！
  
3.子查询执行过程
  先执行主查询，子查询的返回结果作为主查询的条件再执行主查询,子查询只执行一次！
  若子查询的返回结果为多个值，去重后再返回给主查询,所以如果有多个空值，则只保留一个！
  例：哪些客户不是推荐人？
  select id, real_name
  from account
  where id not in
  (
   select recommender_id 
   from account
   where recommender_id is not null
  );
  
  
4.子查询的注意事项
  比较运算符的选择：
  若子查询的返回结果仅为一个值，只能用单值运算符，如“=”，“>”等；
  若子查询的返回结果为多个值，只能用多值运算符，如in等
  如果想用单值运算符，必须将多个返回值用组函数运算成单值的，再返回
  或者是在 单值运算符后面加 all(所有的)、any(任意一个)就可以处理多值了
  
6.多列子查询
  where 子句后面可以跟多列条件表达式
  例1：每台服务器上最早开通的os帐号
  分析：首先，我们找出每台服务器最早开通os帐号的时间，然后再一个一个比对，看哪些os帐号
        是在某台服务器上，再看他的开通时间是不是该服务器的最早开通时间即可
        
  select unix_host, os_username, create_date
  from service_lin
  where (unix_host,create_date) in(
    select unix_host, min(create_date)
    from service_lin
    group by unix_host
  );  
  
例2：哪些os帐号比它所在的unix服务器上开通的最早的时间要晚9天？
select unix_host, os_username, create_date
from service_lin
where (unix_host, to_char(create_date, 'yyyy-mm-dd')) in(
  select unix_host, to_char(min(create_date)+9, 'yyyy-mm-dd')
  from service_lin
  group by unix_host
);


例3：哪些os帐号的开通时长等于所在服务器上的平均开通天数
select unix_host, os_username, create_date
from service_lin
where (unix_host, round(sysdate-create_date)) in(
  select unix_host, avg(round(sysdate-create_date))
  from service_lin
  group by unix_host
);


二、关联子查询
在子查询里引用了主查询的表里的列
执行过程：
1）外部查询得到一条记录，并将其传入到内部查询
2）内部查询基于传入的值执行
3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成其处理，若符合
   条件，则将该记录放入到结果集中，否则，丢弃
4）重复执行1-3，知道把outer表中的所有记录都过一遍


例1：哪些os帐号开通的天数比它所在的服务器的平均开通天数要长？
select unix_host,os_username, create_date
from service_lin s1
where round(sysdate-create_date)> (
  select round(avg(sysdate-create_date))
  from service_lin s2
  where s1.unix_host = unix_host
);


2.exists和not exists
  exists执行过程：
  1）从outer表中传入一条记录给内部查询的表。
  2）对inner表中的记录一次扫描，若根据条件找到一条记录与之匹配，停止扫描，返回true，并
     将outer表中的该条记录放入结果集中；若扫描了所有记录都没有与符合条件的记录，则返回false
     outer表中的该条记录被过滤掉
  3）重复1和2，知道outer表中的记录全部被扫描完


例1：哪些客户是推荐人？
select id,real_name
from account_lin a1
where exists(
  select 1
  from account_lin a2
  where a1.id = a2.recommender_id
);
注：子查询中select后面可以根任何的常量或者是列名，因为我们并不关心子查询返回了什么值，而
    值关心它是否满足where后面的条件！
例2：哪些客户申请了远程登录业务？




  not exists的执行过程
 1）从outer表中传入一条记录给内部查询的表。
  2）对inner表中的记录依次扫描，若根据条件找到一条记录与之匹配，停止扫描，返回false，并
     将outer表中的该条记录过滤掉；若扫描了所有记录都没有与符合条件的记录，则返回true
     outer表中的该条记录被放入结果集中
  3）重复1和2，知道outer表中的记录全部被扫描完
之前的非关联子查询法：
select id, real_name
from account_lin 
where id in(
  select recommender_id
  from account_lin
);


3.什么情况用exists什么情况用in？
exests用循环的方式，由outer表的记录数决定循环的次数，所以外表记录少的时候才适合用exists；
in先执行子查询，子查询的返回结果去重后再返回给主查询，所以子查询的返回结果少的时候才适合用in


三、多表查询
当结果集中的记录保存在多张表中时用多表查询
1.交叉连接（cross join）
交叉连接纵列数，就是从多个表中选取的列数之和，记录数就是两个表中根据相应的列选出的记录记录
进行组合。如a表中选出m列，b表中选出n列，a表根据m列得到的记录有t1条，b表有t2条，于是
得到的表中有m+n列，共有t1*t2条记录，这些记录叫笛卡尔积。由于交叉连接得到的表里的记录大部分都是没实际意义的，所以
一般使用where进行过滤
例1：查询哪些用户开通了os帐号，以及在哪台服务器上开通了。
select a.real_name, a.id, s.account_id, s.unix_host, s.os_username
from account_lin a cross join service_lin s
where a.id = s.account_id;


2.内链接（inner join 或简写 join）：就是为了匹配的！
on t1.colname1 = t2.colname2
其中colanme1和colname2是两张表中表示同一属性的列
放在前面的表叫驱动表，不管哪个作驱动表，对结果集都没有影响
例2：列出huangrong在哪些服务器上开通了业务
select s.id, s.unix_host
from service_lin s join account_lin a
on a.id = s.account_id
and a.real_name = 'huangrong';


注：是先通过a.real_name = 'huangrong'过滤再通过a.id = s.account_id连接的，而且两个条件可以互换


例3：哪些客户在20服务器上开通了服务？
select a.real_name
from service_lin s join account_lin a
on a.id = s.account_id
and s.unix_host = '192.168.0.20';


例4：每个客户开通的业务数？
select max(a.real_name), count(s.os_useranme)
from account_lin a join service_lin s
on a.id = s.account_id
group by a.id;


注：1.执行顺序:先过滤，然后连接，然后分组，最后select
    2.此处有个常用技巧，就是一般做完连接后就做计数，由于是根据id进行分组的，
      所以对real_name进行技术时可以使用max(real_name),因为在account表中
      一个id就对应一个real_name!
    3.内连接哪张表做驱动表，哪张表做匹配表没什么区别，只是效率上有点不同，所以
      一般趋向于用记录少的表作为驱动表。
    
2.from后面跟子查询
以上一题的解二：
select t1.real_name, t2.cnt
from account_lin t1 join(
  select account_id, count(*) cnt
  from service_lin
  group by account_id
) t2
on t1.id = t2.account_id;


一般的，先尽量从一张表中进行统计，然后再与另外一张表进行连接


1.哪些客户是推荐人？
1）用关联子查询：
select a1.id, a1.real_name
from account_lin a1 
where exists(
  select 1 
  from account_lin a2
  where a1.id = a2.recommender_id
);


2）用内连接
select distinct t1.id, t1.real_name
from account_lin t1 join account_lin t2
on t1.id = t2.recommender_id;


比较：第一种效率比较高，因为只要满足条件之后就会将主表中的记录放入结果集中
      而且不再往下比较，也因此记录不会重。
      第二种某个记录驱动表中的某条记录满足条件后仍然往下匹配，效率明显低一些
注：由于内连接中的驱动表里的记录是一条一条取出来拿去匹配的，所以
    “=“和in的效果是一样的，因为每次只是对从表中的一条记录进行匹配，
    相当于说 where real = 'huangrong'和where real_name in 'huangrong'
    是等价的，即单值运算符(=)可以用多值运算符（in）替换的
3）非关联子查询
select id, real_name
from account_lin
where id in
(
  select recommender_id
  from account_lin
);


拓展：列出所有客户的推荐人，如果客户没有推荐人，则在推荐人一栏里填上‘no recommender’
select t2.id, t2.real_name, 
       decode(t1.real_name, t2.real_name, 'no recommender',
       t1.real_name) recommender
from account_lin t1 join account_lin t2

on t1.id = nvl(t2.recommender_id, t2.id);

二、外连接

当需要驱动表中所有的记录都出现在结果集中时，就要用到外连接
from t1 left(right, all) [outer]join t2
on t1.c1 = t2.c2;
外连接步骤：
step1：从t1表中读取第一条记录r1，假设它的c1列值为1；
step2：根据该值到t2表中查找匹配的记录，即需要遍历t2表，从t2表中的第一条记录开始，若当前记录
        的c2列值为1，我们称这两条记录能够匹配上，则将t1的r1和t2的r1组合起来，作为结果集的一
        条记录，否则检测t2表中的下一条记录
step3：按照步骤2依次将t2表中所有的记录检测一遍，只要匹配就放入结果集中，若扫描完后，t1的
        的r1记录在t2表中找不到任何匹配的记录，t2表中模拟一条null记录与t1表中的r1组合起
        来，放入结果集中
step4：再依次从t1表中读取第二条记录，第三条记录...依次重复步骤2和3，产生最终的结果集。
    
注：t1必须作为驱动表，t2表必须作为匹配表
外连接的结果集 = 内连接的结果集 + t1表中匹配不上的记录和t2表中一条null记录的组合


外连接其实就是在内连接的基础上，把驱动表中在匹配表中找不到匹配的记录也留下来了，t1表
中的记录一个都不少地出现在结果集中了。
所以就有以下结论：
1.外连接的结果集中的记录数 >= 驱动表中结果集的记录数
2.内连接的结果集是外连接结果集的子集
3.当外连接驱动表中所有的记录都能在匹配表中找到匹配时，外连接和内连接的结果集是一样的。
4.一般哪个表中的所有记录要被全部保留下来哪个表就做为驱动表


例1：列出客户姓名以及他所开通的业务的信息
select a.real_name, s.id, s.unix_host, s.os_username
from account_lin a left join service_lin s
on a.id = s.account_id;


例2：列出客户姓名和他开通的业务数目
select max(a.real_name) real_name, count(os_username) cnt
from account_lin a left join service_lin s
on a.id = s.account_id
group by a.id;
注：1.此处按照a.id和s.account_id分组不一样，因为s那边可能有null值
    2.在做count统计时，必须使用匹配表中的非空列进行统计！此处count()方法里的参数不能写*或者a.id,因为当找不到匹配的时候，
      应该此条记录不能算在内，但用*或者a.id统计的话，就会算在内了；
      也不能用pause_date,因为这一列总是为空，所以总为0
   


以上采用的是先连接后分组，也可以先分组后连接
select t2.real_name, t1.cnt
from (
  select account_id, count(*) cnt
  from service_lin s
  group by account_id
) t1 right join account_lin t2
on t1.account_id = t2.id;
此处由于用t2做的驱动表，所以如果匹配不到的话结果集中t1表对应的列就为空，当然cnt列就为空了，
所以要用到nvl()函数


2.外连接的第二种应用场景：不匹配问题
例1：哪些客户不是推荐人
思路：先把匹配的和不匹配的都找出来，再把不匹配的剔除掉
select a1.real_name
from account_lin a1 left join account_lin a2
on a1.id = a2.recommender_id
where a2.id is null;




例2：哪些服务器没有开通服务？
select id 
from host_chen t1 left join(
  select distinct unix_host
  from service_lin
) t2
on t1.id = t2.unix_host
where t2.unix_host is null;


注：wehere后面跟的是匹配表中的非空列！


例3：哪些服务器上没有‘weixb’的账户？
法一：
select h.id
from(
  select unix_host 
  from service_lin 
  where os_username = 'weixb'
) t1 right join host_chen h
on h.id = t1.unix_host
where t1.unix_host is null;


法二：
select h.id
from host_chen h left join service_lin s
on h.id = s.unix_host
and s.os_username = 'weixb'
where s.unix_host is null;


3.外连接的执行顺序：
首先用on和and对要进行的连接表中的记录进行过滤，将过滤后的结果集进行外连接操作（join，on），其实就是确定驱动表和匹配表中哪些列进行匹配，再对外连接的结果集用where子句进行过滤，最后用select语句生成结果集


三、非等值连接
前面的连接都是基于两张表有共同的属性列，on后面跟的条件都是 =，这是等值连接，
如果用两张表没有共同属性的列，但两张表的列可以进行比较运算，就要用到非等值连接
例1：显示客户的年龄段
select real_name, round((sysdate-birthdate)/365) age,
       a2.name
from account_lin a1 join age_segment_cjj a2
on round((sysdate-birthdate)/365) between a2.lowage and a2.hiage;


例2：显示客户‘huangrong’的年龄段
select a1.real_name, round((sysdate-birthdate)/365) age,
       a2.name
from account_lin a1 join age_segment_cjj a2
on round((sysdate-birthdate)/365) between a2.lowage and a2.hiage
and a1.real_name = 'huangrong';


例3：显示每个年龄段的客户数(没有客户的年龄段不用显示在结果集中)
select a2.name, count(a1.real_name)
from account_lin a1 join age_segment_cjj a2
on round((sysdate-birthdate)/365) between a2.lowage and a2.hiage
group by a2.name;


例4：显示每个年龄段的客户数(没有客户的年龄段在结果集中显示客户数为0)
select a2.name, count(a1.real_name)
from account_lin a1 right join age_segment_cjj a2
on round((sysdate-birthdate)/365) between a2.lowage and a2.hiage
group by a2.name;
即：将age_segment表作为驱动表，将里面没有匹配的记录的对应列置为0即可


四、各类连接的应用场景
1.交叉连接：笛卡尔积  
2.内连接：匹配问题  --> 等值连接、非等值连接、自连接
3.外连接：不匹配问题(外连接 + where匹配表的非空列 is null)、
          驱动表中所有记录都出现在结果集中  --> 等值连接、非等值连接、自连接
4.分组(group by)：驱动表的id列
  统计(count)：匹配表的非空列
  过滤(where)：匹配表的非空列