1、库表sql：
（1）、查询所有表的外键
select * from user_constraints where constraint_type='R';
2、开发常用sql：
（1）、分组连接，同mysql的GROUP_CONCAT的语法功能，语法
select t.org_number,to_char(wm_concat(it.identify_name)) from org t
left join org_a a on a.org_id = t.id and a.del_flag = '0'
left join ge_i_type it on it.id = a.identify_type and it.del_flag = '0'
where t.state = '6' group by t.org_number order by t.org_number asc;
（2）、校验是否为null
select NVL2(max(sort),max(sort)+1,1) from sms_template t ;
如果max(sort)不是null，返回max(sort)+1，如果max(sort)是null，则返回第三个参数1.
NVL(a1,a2) 语法：如果a1为null，返回a2，否则返回a1。
NVL2(a1,a2,a3) 语法：如果a1不为null，返回a2；a1为null，返回a3
（3）、instr 用法，用户模糊匹配
格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
入:
select instr('helloworld','l') from dual; --返回结果：3 默认第一次出现“l”的位置select instr('helloworld','l',2,2) from dual; --返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',3,2) from dual; --返回结果：4 也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual; --返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual; --返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual; --返回结果：4 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
select instr('helloworld','l',2,3) from dual; --返回结果：9 也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual; --返回结果：3 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置
4、case-when
CASE t.review_state
WHEN '1' THEN '初审'
WHEN '2' THEN '拒绝'
WHEN '3' THEN '初审通过'
WHEN '4' THEN '注册'
WHEN '5' THEN '锁定'
WHEN '6' THEN '正常'
WHEN '7' THEN '撤销入围'
ELSE '其他' END reviewTxt,
CASE t.is_pub
WHEN '1' THEN '已公示'
ELSE '未公示' END pubTxt,
5、字符串截取比较
SELECT SUBSTR('Hello SQL!', 1) FROM dual --截取所有字符串，返回'Hello SQL!'
SELECT SUBSTR('Hello SQL!', 2) FROM dual --从第2个字符开始，截取到末尾。返回'ello SQL!'
SELECT SUBSTR('Hello SQL!', -4) FROM dual --从倒数第4个字符开始，截取到末尾。返回'SQL!'
SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual --从第3个字符开始，截取6个字符。返回'llo SQ'
SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual --从倒数第4个字符开始，截取3个字符。返回'SQL'
6、日期转字符串比较
to_char(to_date(create_date,'yyyy-mm-dd,hh24:mi:ss'),'yyyymmdd') date1,
当前日期
to_date(sysdate,'yyyy-mm-dd,hh24:mi:ss')标准日期和星期格式输出
select to_char(t.create_time,'yyyy-mm-dd hh24:mi:ss day'),t.* from sys_user t ;
7、数字字符串转数字
select to_number('22.222') from dual8、常用表结构操作语句
（1）、建表语句
CREATE TABLE "CASE_DAILY" (
"ID" VARCHAR2(32 BYTE) NOT NULL ,
"CASE_ID" VARCHAR2(32 BYTE) NULL ,
"CASE_NO" VARCHAR2(64 BYTE) NULL ,
"CASE_DEPUTE_NO" VARCHAR2(64 BYTE) NULL ,
"COMMIT_DATE" DATE NULL ,
"ORG_NUMBER" NUMBER NULL ,
"ORG_NAME" VARCHAR2(128 BYTE) NULL ,
"DAILY_TITLE" VARCHAR2(100 BYTE) NULL ,
"DAILY_CONTENT" VARCHAR2(1000 BYTE) NULL ,
"REMARK" VARCHAR2(2000 BYTE) NULL ,
"CREATE_USER" VARCHAR2(32 BYTE) NULL ,
"CREATE_ORG" VARCHAR2(32 BYTE) NULL ,
"CREATE_TIME" DATE NULL ,
"MODIFY_USER" VARCHAR2(32 BYTE) NULL ,
"MODIFY_TIME" DATE NULL ,
"DEL_FLAG" CHAR(1 BYTE) NULL
);
COMMENT ON TABLE "CASE_DAILY" IS '案件日报表';
COMMENT ON COLUMN "CASE_DAILY"."ID" IS '主键';
COMMENT ON COLUMN "CASE_DAILY"."CASE_ID" IS '案件id';
（2）、新增字段
ALTER TABLE "CASE_DAILY"
ADD ( "AAA" VARCHAR2(100) NULL ) ;
（3）、修改字段名
ALTER TABLE "CASE_DAILY" RENAME COLUMN "AAA" TO "AAA1";
（4）、修改字段属性
ALTER TABLE "CASE_DAILY"
MODIFY ( "AAA" VARCHAR2(1001 BYTE) ) ;
（5）、修改备注
COMMENT ON COLUMN "CASE_DAILY"."AAA1" IS '11111222222';
（6）、删除属性
ALTER TABLE "CASE_DAILY" DROP("AAA1");
9、计算时间差（两个数据库日期对象直接减得到天数值，即本例的3.051689）
select to_char(extract(day from numtodsinterval(3.051689,'day'))) 天 ,
to_char(extract(hour from numtodsinterval(3.051689,'day'))) 小时,
to_char(extract(minute from numtodsinterval(3.051689,'day'))) 分钟,
to_char(round(to_number (extract(second from numtodsinterval(3.051689,'day'))))) 秒,
to_char(extract(day from numtodsinterval(3.051689,'day')))||'天'||
to_char(extract(hour from numtodsinterval(3.051689,'day')))||'小时'||
to_char(extract(minute from numtodsinterval(3.051689,'day')))||'分钟'||
to_char(round(to_number (extract(second from numtodsinterval(3.051689,'day')))))||'秒' 时间
from dual;
10、数字保留处理
select round(1/3,2) from dual; --四舍五入
select trunc(1/3,2) from dual; --直接裁断
select floor(100/3) from dual; --向下取整
select ceil(100/3) from dual; --向上取整
11、返回列的最大值，无则返回1。（可以用case when实现校验字段是否为空）
UPDATE sys_index_system
SET sys_type = (
SELECT
max(CASE when (t.sys_type is null or t.sys_type = '') then 0
ELSE t.sys_type END) + 1
FROM
sys_index_system t
WHERE
t.id != '111'
)
WHERE
id = '111';
12、decode语法
（1）、四个参数，前两个用于校验一致，第三个为一致的返回值，第四个为不一致的返回值
如：sys_type为2则返回1，不为2则返回0
SELECT DECODE(t.sys_type,'2',1,0) from sys_index_system t;
（2）、多个参数，decode(expression,value1,result1,value2,result2...)
第一个为表达式，值和返回结果的序列
如：sys_type为1返回优秀，为2返回良好，为3返回及格
SELECT DECODE(t.sys_type,'1','优秀','2','良好','3','及格') from sys_index_system t;
（3）、扩展1--decode嵌套使用
将成绩表中分数大于90分的分为优秀，80~90分为良好，70~80分为中等，60~70分为及格，60分以下为不及格。
（sign()函数的作用是，判断参数的值大于0则返回1，等于0则返回0，小于0则返回-1；
如图，当socre大于或等于90时，socre-90>=0，sign()函数返回1或0，则输出‘优秀’，
在score-90<0的情况下，再判断socre-80，socre-80>=0，sign()函数返回1或0，则输出‘良好’，
以此类推，用decode()的嵌套配合sign()函数来实现对分数的分段以及相应的输出，
最后60分以下的就default为‘不及格’就可以了，可以看到我们的Tony老师的分数就不及格了）
（4）、扩展2---使用decode函数对表做行列转换
原表结构
如图所示，有一张三个科目的成绩表，各科目名称和相应的分数呈现在行中，
现想将各科目名称转换为列名，每列的内容为对应科目的分数。 
（如图，先用decode函数判断subject为Chinese时，输出score，不为Chinese时，输出为NULL，
因为共有三个科目，所以输出其中一个科目的分数时，其他科目的分数为NULL，这里只需要
用sum()函数来实现聚合的作用，将空值去除掉就可以了） 
（sum()函数分别将name为John的每个列的值相加，如Chinese列，80+NULL+NULL=80，
13、将一个表的字段更新为另外一张表的字段
update A set A.unit_price = B.unit_price from A,B
where A.s_id = B.id ;
14、数据分区函数partition by
基础语法：over（partition by goods_id order by create_time）
-- 如果排序值相同，rank会排成两个1，ROW_NUMBER会排成1和2
select * from(SELECT ROW_NUMBER() over(partition by t.goods_id order by create_time desc) num, t.* FROM goods_apply_detail t) temp where temp.num = '1' ;
select * from(SELECT RANK() over(partition by t.goods_id order by create_time desc) num,
t.* FROM goods_apply_detail t) temp where temp.num = '1' ;
15、null属性排在最后（不加 nulls last，则null属性数据排在前面）
select * from sys_account t order by t.mobile_phone nulls last ;
115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
