Oracle 原理：复杂的SQL语句

1.select [列名] from [表名] where [条件] group by [列名] having [条件] order by [列名]  的执行顺序。

 在查询里面， 首先执行 from 来需要确定表，然后一条一条地遍历表数据；执行where语句把符合的表数据行筛选数来，也就得到了可以输出的数据;之后，将筛选出来的数据进行group by分组;之后用having来确定哪些数据不显示;之后，用orderby来对显示数据的数据进行排序。 

------------------------------------------------------分割线------------------------------------------------

有如下的  工资表(雇员名称,所属部门，薪资,部门组长)；

drop table salary_tbl;
create table salary_tbl(
   employer_nm varchar(20),
   department varchar(20) not null,
   salary number not null,
   leader_nm varchar(20)
);
truncate table salary_tbl;
begin
 for i in  1..100
     loop
     insert into salary_tbl values('雇佣者'||i,'部门'||Mod（i,6）,100*POWER(10000,i*0.01),'雇佣者'||Mod（i,6）); 
   end loop;
end;
/ 
commit;

 

写SQL一般先确定表和表之间的关联关系，然后列出筛选条件个数，例如,查询最高薪资的部门组长信息

①确认要查询的表为 salary_tbl   ② 筛选条件两个：要为部门组长，在组长里薪资最高

------查询薪资最高的部门组长-----
select * from salary_tbl s where
EMPLOYER_NM in (select distinct LEADER_NM from salary_tbl)  --条件1：部门组长信息确定
and salary =(select max(salary) from salary_tbl s where     --条件2: 薪资为部门组长里最高的
EMPLOYER_NM in (select distinct LEADER_NM from salary_tbl));

对于复杂的增删改语句中也是一样的思路，首先确定表和表之间的关联关系，确定要修改的字段，把筛选条件一一列举出来就可以思路清晰地实现了

2.集合运算 union(并集),minus(差集),intersect(交集)

  Where 后面的条件筛选 也可以用集合运算来实现。

---------查询薪资低于150块钱部门组长信息(两次)---------
select s.* from (select * from salary_tbl where EMPLOYER_NM=leader_nm) s  
minus
select * from salary_tbl where salary>=150       --部门组长表和薪资大于150的差集
UNION ALL                --不去重联合查询
select s3.* from 
（select s.* from (select * from salary_tbl where EMPLOYER_NM=leader_nm) s
INTERSECT
select * from salary_tbl where salary<150） s3;   --部门组长表和薪资小于150的交集

3.  rownum 和 Rowid

 rownum和rowid 在查询时都不是直接展示的。其中rownum即行标，在执行完SQL查询时后面添加上去的，所以每条数据的rownum根据不同的筛选条件是会变的，而rowId是由该数据行的物理地址信息所组成的字段。如果数据的物理位置没变，那么rowid也就不会变，当查询语句没跟order by时，默认按rowId排序。如图

当把rownum当做筛选条件时 只有 rownum < 或者 rownum <= 才能起效果，而大于,等于，不等于，between都不能有效的当做筛选条件。例如查询该表中的最后5行数据

---------------
select * from 
(select salary_tbl.* ,rownum as row_num,rowID as row_id from salary_tbl) s
where s.row_num  between (select count(1) from salary_tbl )-5 and (select count(1) from salary_tbl);
----------------
select s.*,rownum from salary_tbl s
minus
select salary_tbl.*,rownum  from salary_tbl where rownum<
(select count(1) from salary_tbl )-5;

4. case when..then....else  和decode(value,if1,then1,if2,then2,if3,then3,...,else)

 case when...then ..else  和decode 很像面向对象程序语言里的  if...elseif...elseif ..else 语句，但是decode里的 if条件只能用来判断相等，而不能直接的进行比大小。

select s.employer_nm,
       s.department,
       case when s.salary<1000 then '穷鬼'
            when salary<8000   then '正常'
            when salary <40000 then '牛逼'
            else '大佬'   
       end as 薪资水平,
       s.leader_nm from salary_tbl s
-----------
select s.employer_nm,
       s.department,
       decode(sign(salary-10000),1,'高',-1,'低','刚好一万元') as 薪资高低,
       s.leader_nm from salary_tbl s

  5. 变量,行变量，游标

例子：下面是输出    查询表中的最后5行数据

declare 
   max_num number;
   rowdata salary_tbl%rowtype;     --行类型变量
   type cur_type is ref cursor;    
   output1 cur_type ;              --游标
   v_sql varchar2(2000) :=''; 
   begin 
    select count(1) into max_num from salary_tbl;   -- 给变量赋值
    v_sql :='select s.employer_nm,s.department,s.salary,s.leader_nm from 
            (select salary_tbl.*, rownum as row_num from salary_tbl) s where 
            s.row_num  between '|| (max_num-5)||' and '||max_num;    --记得单词间的空格，不用加分号
             
    open output1 for v_sql;     
    
    LOOP
      fetch output1 into rowdata; 
      exit when output1%notfound;
      dbms_output.put_line(rowdata.employer_nm || '|@|' ||
                           rowdata.department  || '|@|' ||
                           rowdata.salary      || '|@|' ||
                           rowdata.leader_nm);
    END LOOP;            --循环输出
   end;

输出结果::