oracle数据库（三）_进阶查询

层级关联

-- 根据员工的上级编号进行层级关联
select level,empno,ename,mgr
from emp
connect by prior empno = mgr
start with mgr is null
order by level;

进阶字符函数

translate(expr,from_string,to_string)替换知道的字符，to_string为null时返回null，没有匹配时的字符直接删除
regexp_replace(src,re,new)正则替换字符
regexp_substr(src,re,n)字符串分列，re.split()
regexp_count('asd-df-dfd-a','-')统计字符-的数量

-- 正则替换
select regexp_replace('adsfaad','[^a]','0') from dual;


-- 字符串分列
select 
      regexp_substr('192.168.0','[^.]+',1,1) a1
     ,regexp_substr('192.168.0','[^.]+',1,2) a2
     ,regexp_substr('192.168.0','[^.]+',1,3) a3
from dual;


-- 计算字符出现数量
select regexp_count('adsfaad','a') from dual;
select length(regexp_replace('adsfaad','[^a]')) from dual;
select translate('adsfaad','a'||'adsfaad','a') from dual;


-- 删除不需要字符
select replace(translate(job,'MAN','aaa'),'aaa','') from emp;
select regexp_replace(job,'[MAN]','') from emp;


-- 字符数字分离
select 
      regexp_replace('sdfsf04','[0-9]','') 字符
      ,regexp_replace('sdfsf04','[^0-9]','') 数字
from dual;

select 
      translate('sdfsf04','a0123456789','a') 字符
      ,translate('sdfsf04','0123456789'||'sdfsf04','0123456789') 字符
from dual;

界定符

-- 带单引号字符
select 'adsf''asfads' from dual;
select q'{'}' from dual;

listagg

• listagg() within group ：把一列字符组合在一起

-- 用法
select 
  e.deptno
  ,listagg(e.ename,',') within group (order by e.empno) names
from emp e
group by e.deptno;


-- 展示累计的逻辑
select 
  e.*
  ,sum(sal) over(partition by e.deptno order by hiredate)
  ,(select listagg(sal,'+') within group (order by e2.hiredate)
      from emp e2
      where e2.deptno = 30
            and e2.hiredate <= e.hiredate
    )-- e.hiredate的数据迭代进入计算，迭代出
from emp e
where e.deptno =30;

over

-- 开窗
rows 					--物理开窗
range 					--逻辑开窗
UNBOUNDED PRECEDING 	--前面所有行
CURRENT ROW 			--当前行
UNBOUNDED FOLLOWING 	--后面所有行
range between UNBOUNDED PRECEDING and CURRENT ROW-- 默认窗口

-- 显示员工信息按各部门薪资降序排列
select 
   empno
   ,ename
   ,sal
   ,dense_rank() over (partition by deptno order by sal desc) sal_rank
from emp;

• 分析函数
  ROW_NUMBER():不并排排名
  RANK():并排隔空排名
  DENSE_RANK():并排不隔空排名
  LAG(col,offset[,noffset]):当前行的前n行的值
  LEAD(col,offset[,noffset]):当前行的后n行的值

keep

-- 显示各部门最高工资的员工信息
select 
   deptno
   ,min(sal) keep (dense_rank first order by sal desc) top_sal
from
   emp
group by deptno;

level

select level from emp connect by level<=1;

select level from dual connect by level <= 4;

EXPLODE

--EXPLODE
select regexp_substr('555_666_5_89_','[a-zA-Z0-9]+',1,ROWNUM)
from dual
connect by ROWNUM <= regexp_count('555_666_5_89_','_');​

系统查询

• 当前用户的约束结构

SELECT
	user_ind_columns.index_name 索引名称,
	user_ind_columns.table_name 表名,
	user_ind_columns.column_name 字段名,
	user_indexes.uniqueness 约束 
FROM
	user_ind_columns,
	user_indexes 
WHERE
	user_ind_columns.index_name = user_indexes.index_name;

• 当前用户表名

select table_name from user_tables;

一行拆分多行显示

with t as (
    select 'X,Y,Z' as lll from  dual
    union all
    select 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' as lll from dual
)


select lll,  
       regexp_substr(lll, '[^,]+', 1, level) as value
from t  
    connect by  lll = prior lll  
        and instr(lll||',', ',', 1, level) > 0  
        and prior dbms_random.value is not null; 

其他常用函数

LEAST(expr1[,expr2]…)返回最小参数
GREATEST(expr1[,expr2]…)返回最大参数