开发过程中常见的一些SQL整理



Oracle数据库的集合操作：
UNION：并集，所有的内容都查询，重复的显示一次；
UNION  ALL：并集，所有的内容都显示，包括重复的；
INTERSECT:交集，只显示重复的；
MINUS：差集，只显示对方没有的（跟顺序没有关系）

随机返回五条记录：
oracle数据库中随机查询数据：
 select * from (select * from tablename order by sys_guid()) where rownum < N; 
 select * from (select * from tablename order by dbms_random.value) where rownum< N; 
 select *  from (select * from table_name sample(10)   order by trunc(dbms_random.value(0, 1000)))  where rownum < N;
DB2数据库中随机查询五条数据：
select * from A order by rand() fetch first 5 rows only

处理空值排序：
select * from 表名 order by comm desc nulls last(first);

查询跳过表中的偶数行：
select 字段  from （select row_number() over (order by 字段) rn ,字段 form 表名 ）xx where  mod(rn,2)=1;

连续求和：
select 字段1，字段2 sum(字段2) over(),sum(字段2) over(order by 字段1) from  表名；
sum(字段2) over(order by 字段1) 指的是连续求和，按照字段1来排序，若有两个这样的窗口函数以后面的排序为主。

得到当前行上一行或者下一行的数据：
select 字段1，字段2，lead(ziduan2) over (order  by ziduan2) aaa,lag(ziduan2) over (order by ziduan2)bbb form 表名；
 

查询表中所有的字段列：
select * from all_tab_columns where table_name='t_cap_login_flag_info';

列出表的索引列：
select * from sys.all_ind_columns where table_name='t_cap_login_flag_info';
select * from sys.all_ind_columns where upper(table_name)='t_cap_login_flag_info';

列出表中约束：
select * from all_constraints where table_name='t_cap_login_flag_info';

在Oracle中描述数据字典视图：
select table_name ,comments from dictionary where table_name like '%TABLE%'

使用子查询创建表：
create table 表名2 as select * from 表名1；
create table 表明2 as select * from 表明1 where 字段；

级联删除：

根据条件修改某张表的交易时间为指定的交易日期：
update T_CIF_GROWTH_CAL_LIST t set t.txn_time=to_date('2017-06-07','yyyy-mm-dd') where t.biz_type='01';

AAA:ALTER  添加   修改  删除表的列 约束等表的定义：
查看列：DESC 表名；
修改表名：alter table t_book rename to bbb;
添加列：alter table 表名 add column 列名 varchar(30);

oracle添加列：不要column
alter table t_bpf_sys_unit add  HS_LPRENCD_ VARCHAR2(64) ;
oracle修改列：
alter table 表名 modify 字段 varchar2();
oracle删除列：
alter table 表名 drop column 字段；
清空表中数据：
truncate table 表名;
重命名表：
rename 表名 to 表名1;

删除列：alter table 表名 drop column 列名；
修改列名Mysql:alter table bbb change nnnnn hh int;
修改列名Oracle:alter table bbb rename column nnnnn to hh  int;

BBB:数据库截取其中某一个字符串的的下标位作为查询条件
select parm_level_bit,substr(t1.parm_level_bit,1,1),substr(t1.parm_level_bit,2,1),substr(t1.parm_level_bit,3,1) from t_bpf_param_conf t1 where parm_id = 'PAY_AA_MAX_AMT'

CCC:-上级获得下级的递归机构信息
 select * from lk_sysorg org start with org.orgcode='610610000' connect by PRIOR org.orgcode=org.suporg order by org.suporg;

DDD:-下级获得上级的递归机构信息
 select * from lk_sysorg org start with org.orgcode='610647536' connect by PRIOR org.suporg=org.orgcode order by org.suporg;

EEE:递归查询：
select UNIT_CODE_ from SYS_UNIT   start with unit_code_='27090509' connect by PRIOR unit_code_ = parent_ order by unit_code_;

FFF:采用foreach循环遍历list字段的值
<select id="liqDetailAmtClrHigSubject" parameterType="java.util.Map"
    resultMap="BaseResultMap">
        select a.ACT_BRCH_NO,a.acct_date,a.clr_bat_no,a.bank_no,a.ccy,sum( case when a.DC_FLAG='C' then -a.TXN_AMT
                                 else a.TXN_AMT end ) as "txnAmt" from t_act_liq_detail a,t_act_pub_org b
        where a.clr_bat_no = #{batchNo}
         and a.acct_date = #{preAcctDate}
         and a.ACT_BRCH_NO = b.ORG_NO 
        and b.BANK_LVL = #{bankLvl}
        and SUBSTR(a.act_no,9,6 ) in
      <foreach collection="listActNo" index="index" item="item" open="(" separator="," close=")">
         #{item}
         </foreach>
     group by a.ACT_BRCH_NO,a.acct_date,a.clr_bat_no,a.bank_no,a.ccy
 </select>

GGG:db2数据库以某个字段不等于什么作为查询条件的事例：
不等于:<>
select * from USR.T_CIF_REG_INFO t where t.USER_STAT <>'06';
 

HHH:数据库清表语句：
truncate table 表名
III:查询数据表中重复的数据：
select distinct 字段名  from   表名；
JJJ:用order  by  关键字对查询结果进行排序
select * from 表名 order by 字段 【ASC】/[DESC]
KKK:单独使用group by关键字，查询结果每组只显示一条记录。
select 字段1，字段2，字段3  from 表名 group by 字段1；
LLL：用limit限制查询结果的数量
select * from 表名 order by 字段  asc limit 3;
MMM：可以查询出来所有的空表
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
NNN:查询单个字段中值含0且字段值长度不一致时查询最大值   
SELECT MAX(CAST(t.SCENCE_ID AS SIGNED)) SCENCE_ID from T_FEE_POIFEE_SCENCE t
OOO:数据库用户解锁命令
[oracle@IBP28 ~]$ sqlplus system/fxdemon#2017
SQL> alter user usr account unlock;
PPP:日期字符串互转
-日期转字符串
select date_format(REMOV_DATE,'%Y-%m-%d') from  T_AMS_RELATE_ACCTLIST_NXY
--字符串转日期
select str_to_date('20170619','%Y-%m-%d') from  T_AMS_RELATE_ACCTLIST_NXY

QQQ:修改表所对应的表空间 
alter table fsds.arear move tablespace tbs_data_fsds

RRR: 导入导出库  
导入全库：
imp username/password@SID file=XXXX.dmp full=y
导入某个表：
imp username/password@SID file=xxx.dmp fromuser=xx touser=xx tables=(tablename)
注：fromuser：源用户。Touser：目标用户。 Tables:要导入的表名，多个可以用，号隔开。
导出全库：
exp   username / password  file=xxx.dmp
导出某个表：
exp   username / password   tables=( tablename)   file=xxx.dmp
exp cbrc/cbrc@ORCL tables=(lgt_test) file=F:\sxnx2\orcldata\lgttest.dmp
注：Tables:要导出的表名，多个可以用，号隔开。

SSS:快速把一张同结构的表的数据插入到另一张表中

insert into t_bpf_sys_unit_1018 select * from t_bpf_sys_unit;

SELECT DISTINCT DEPTNO FROM EMP;  --去重

3、NVL函数
(1)NVL(x,value)  --如果x为空，返回value，否则返回x。
   (2)NVL2(x,value1,value2) --如果x非空，返回value1，否则返回value2。

--将列字符串转换成行并用,号隔开
select listagg(dictname, ',') dictname from (select * from pa order by rank )