目 录CONTENT

文章目录

常用Oracle语句

在水一方
2022-02-12 / 0 评论 / 0 点赞 / 957 阅读 / 2,778 字 / 正在检测是否收录...

相信开发的朋友会有这样一种感慨,sql写的好,能够大大减少java代码的编写,尤其对于强大的Oracle来说熟练掌握sql尤为重要,之前用过很多的oracle函数,由于没有总结很容易忘记

基础应用

select * from ts_person where rownum < 101(注意:rownum只能用小于等于,不能是大于等于)
但是如果想用的话也是可以的,可以用子查询:
select * from (select rownum as no,empno from ts_person) where no>3 and no<20

知识点累积:所有的组函数都是忽略空值的 实际中求和的时候可以用:sum(nvl(字段名,0))

查询表中用不包含的记录
select * from ts_person where id not like '0000%'

修改表字段的数据类型sql
alter table tl_year_task modify course_total varchar2(10)

给表添加字段的sql并设置默认值
alter table TL_YEARTASK add is_show varchar2(225) default '1'

查看当前用户
select * from user_users
select * from all_users
sysdate 用于返回当前的系统日期(select sysdate from dual)

字符串格式转换
to_char() 转换成字符类型
例子1:select to_char(create_time,'yyyy"年"mm"月"dd"日"') as create_time from tl_year_task
to_date() 转换成日期类型
to_number() 转换成数字类型

常用函数

1 nvl函数

nvl(tlsp.credit,0) as resource_credit 如果tlsp这个表的credit返回空,则默认用0取代

案例:
select s_id,nvl2(exam_score,exam_score,0) 数量 from tl_learn_sign_up 意思是
exam_score 有值的情况下,返回exam_score,如果为空的话则返回0 注意事项:返回的值的类型要和字段类型一致

2 case when then

案例1:
select person_id,case exam_score when 60 then '及格'
when 70 then '中等'
when 94 then '优秀' else '不及格' end member from tl_learn_sign_up
案例2:
select person_id,case when exam_score=60 then '及格'
when exam_score=70 then '中等' when exam_score=94 then '优秀'
else '其他' end recored(别名) from tl_learn_sign_up where
rownum<100

3 decode函数

案例:重点学习,其作用和case when then 差不多
select * from (select person_id,ts.empname,decode(exam_score,60,'及格',70,'中
等',94,'优秀','不及格') as record from tl_learn_sign_up tlsu
left join ts_person ts on tlsu.person_id=ts.id where tlsu.is_deleted='0') where
record='优秀'

4 concat函数拼接

SUBSTR(表达式,位置,长度)
concat(str1,str2) 字符串拼接 ste1和str2 用于指定被连接的字符串

5 length函数

select length('223243阿萨德撒旦') as lenght from dual

6 replace替换函数

将Scott中的c替换为Boy 
select replace('Scott','c','Boy') from dual;  //得到SBoyott

7 sys_connect_by_path递归函数

Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行
语法为:select ... sys_connect_by_path(column_name,'connect_symbol') from table
start with ... connect by ... prior

案例
select sys_connect_by_path(t.mpd_name,'-->') as namepath ,t.mpd_innercode as
codepath,t.mpd_name as selfname, t.mpd_code as selfcode, lpad('|--- ',6*(LEVEL-1)) ||
mpd_name as treenames
from lib_mainprocess_direct t
start with t.id = '100' connect by prior t.id = t.mpd_parentid

8 instr()函数

instr函数为字符查找函数,其功能是查找一个字符串在另一个字符串中首次出现的位置
格式一:instr( string1, string2 )    // instr(源字符串, 目标字符串)
格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

1 select instr('helloworld','l') from dual; --返回结果:3    默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果:4    即“lo”同时出现,第一个字母“l”出现的位置
3 select instr('helloworld','wo') from dual; --返回结果:6    即“wo”同时出现,第一个字母“w”出现的位置

注:MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果

wm_concat()

行转列,将多行值转成一列
wm_concat(列名)这个神奇的函数,他可以把列值用“,”分隔开,而且是显示成一行


lpad()函数

lpad函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解,l是left的简写,pad是填充的意思,所以lpad就是从左边填充的意思

格式:lpad( string, padded_length, [ pad_string ] )


Rank函数

和排序相关
rank() over开窗函数 

待补充.......

dense_rank函数

待补充.......

还有很多后面再慢慢补.....

0

评论区