常用sql语句和函数

--查询表的字段数

select count(*) from user_tab_columns where table_name = '表名';

--查询数据库用户密码的profile（一般为default）:

SELECT username,PROFILE FROM dba_users;--查看defult的时间：
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

--修改defult的时间：修改完，直接生效。不需要重启

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--sql中使用递归 pid为父id

select t.*, level, connect_by_isleaf

from t_users t start with t.id='1'  connect by prior t.id=t.pid;

level:层级

connect_by_isleaf: 是否为叶子

t.id=t.pid 向下递归， t.pid=t.id向上递归

--wm_concat 拼接查询结果

(select replace(wm_concat(zonename), ',', '/') from
(select zonename, azlevel from
(select az.zonename, level azlevel from onlinedb.tb_auth_zone az
start with az.id = 15 connect by prior az.parentid = az.id)
order by azlevel desc)
) parentName

原本的查询结果是

中国

陕西

咸阳

wm_concat的结果为 中国，陕西，咸阳

replace之后为 中国/陕西/咸阳

--日期

select add_months(sysdate, -1) from dual; --月份操作 +、-
select substr('123456', 2, 3) from dual; --字符串截取 234
select trunc(sysdate, 'D')+7 from dual; --日期截取，年开始，月开始等，YYYY年， Q季度， MM月， D周，
SELECT LAST_DAY(sysdate) FROM DUAL; --当月最后一天，时分秒是日期中的时分秒

 

-- md5

1.创建方法

create or replace function md5utils(sargs in varchar2) return varchar2 is 
resv varchar2(32)
begin resv := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.MD5(input_string =》 sargs));
return lower(resv);
end;

2. 测试

select md5utils('123') from dual;

 

--条件判断 docode，case when

--decode

select decode('2', '2', 1, 0) from dual; -- 1  解释：'2'，if=='2' then 1 else 0

--case when查询同一机构的签约数和解约数:

select t.sgn_acct_issr_id, 
count(case when t.sgn_status = '01' then 1 else null end) 签约数,
count(case when t.sgn_status = '02' then 1 else null end) 解约数
from epcc_protocol_info t
group by t.sgn_acct_issr_id;

--case when 筛选查询条件，需求：（消息有三中级别：置顶，重要，普通； 有两种状态：未读，已读。现在需要置顶和未读排在最前面，重要和普通不影响排序）

select * from xxx t

order by case when t.level = 1 then 0 else 1 end asc , t.modifytime desc

 --case when查询当(trx_ctgy in ('0110','0111') and pyer_acct_tp in ('02','01') ) or (trx_ctgy in ('0121') and pyee_acct_tp in ('02','01'))不能用or的时候:

select * from epcc_accdel_000 twhere (case when t.trx_ctgy in ('0110','0111') and t.pyer_acct_tp in ('02','01') then 1
when t.trx_ctgy in ('0121') and t.pyee_acct_tp in ('02','01') then 1
else 0
end) = 1 ;

case when也有两种，

case qq when aa then aa else bb end ; --感觉这个和decode差不多

case when qq==aa then aa else bb end ;