24.Oracle查询易忘点

1.查询字符‘截取’数据substr

SELECT "age" SAGE, substr("name", 2, 2)  SNAME FROM Student//从字符下标为2开始截取，截取2个（首位下标为1，不是0）

2.查询拼接无关联字段

 1 select sum(bdnrjk) as "bdnrjk",sum(bdnrck) as "bdnrck",sum(bddljk) as "bddljk",sum(bddlck) as "bddlck",sum(wdnrjk)as "wdnrjk",sum(wdnrck) as "wdnrck",sum(wddljk)as "wddljk",sum(wddlck) as "wddlck" from ( 
 2     select count(*) as bdnrjk, 0 as bdnrck , 0 as bddljk , 0 as bddlck , 0 as wdnrjk , 0 as wdnrck , 0 as wddljk , 0 as wddlck 
 3         from   TESTTABLE where 1=1 UNION all 
 4     select 0 as bdnrjk, count(*) as bdnrck , 0 as bddljk , 0 as bddlck , 0 as wdnrjk , 0 as wdnrck , 0 as wddljk , 0 as wddlck 
 5         from   TESTTABLE where 1=1 UNION all 
 6     select 0 as bdnrjk , 0 as bdnrck , count(*) as bddljk , 0 as bddlck , 0 as wdnrjk , 0 as wdnrck , 0 as wddljk , 0 as wddlck 
 7         from   TESTTABLE where 1=1 UNION all 
 8     select 0 as bdnrjk , 0 as bdnrck , 0 as bddljk , count(*) as bddlck , 0 as wdnrjk , 0 as wdnrck , 0 as wddljk , 0 as wddlck 
 9         from   TESTTABLE where 1=1 UNION all 
10     select 0 as bdnrjk, 0 as bdnrck , 0 as bddljk , 0 as bddlck , count(*) as wdnrjk , 0 as wdnrck , 0 as wddljk , 0 as wddlck 
11         from   TESTTABLE where 1=1 UNION all 
12     select 0 as bdnrjk , 0 as bdnrck , 0 as bddljk , 0 as bddlck , 0 as wdnrjk , count(*) as wdnrck , 0 as wddljk , 0 as wddlck 
13         from   TESTTABLE where 1=1 UNION all 
14     select 0 as bdnrjk , 0 as bdnrck , 0 as bddljk , 0 as bddlck , 0 as wdnrjk , 0 as wdnrck , count(*) as wddljk , 0 as wddlck 
15         from   TESTTABLE where 1=1 UNION all 
16     select 0 as bdnrjk , 0 as bdnrck , 0 as bddljk , 0 as bddlck , 0 as wdnrjk , 0 as wdnrck , 0 as wddljk , count(*) as wddlck 
17         from   TESTTABLE where 1=1
18  ) 

3.REGEXP_LIKE (字段,'值|值|值|值')

select * from Student where REGEXP_LIKE (class,'1班|2班|3班|4班')//查询1、2、3、4、班的学生（即：class字段值为1班或2班或3班或4班的student）

 4.nvl(字段,默认值)

select name,nvl(age,99) as AGE from Teacher//查询老师的名字和年龄，如果表中年龄字段为空则查出的数据会赋予默认值'99'

 5.CASE WHEN 条件 THEN 结果 ... ELSE 结果   END

 1 --查
 2 select name
 3 CASE 
 4     when age=100 then '老师'
 5     when name='张三' then '三儿'
 6     when name='李四' then '四儿'
 7     else name
 8 end
 9 from Student
10 --删
11 DELETE Student where 
12 name=(case 
13     when age=100 then '老师'
14     when name='张三' then '三儿'
15     when name='李四' then '四儿'
16     else name
17 end)
18 
19 --改
20 update Student set 
21 name=(case 
22     when age=100 then '老师'
23     when name='张三' then '三儿'
24     when name='李四' then '四儿'
25     else name
26 end)

 6.数据依次累加over

原数据：

例子1：

SELECT "name",
"dateTime","money",
sum("money") over (ORDER BY "dateTime"),--此处是根据dateTiem排序累加money的值,相同的dateTime的money值会合并,也可以根据其它属性排序累加
sum("money") over () --此语法和sum("money")一个效果
FROM A;

效果：

例子2：

SELECT "name",
"dateTime","money",
sum("money") over (ORDER BY "name"),--此处是根据name排序累加money的值,相同的name的money值会合并,也可以根据其它属性排序累加
sum("money") over () --此语法和sum("money")一个效果
FROM A;

效果：