Oracle查询易忘点

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

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

2.查询拼接无关联字段

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 ( 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1 UNION all 
    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 
        from   TESTTABLE where 1=1
 ) 

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

--查
select name
CASE 
    when age=100 then '老师'
    when name='张三' then '三儿'
    when name='李四' then '四儿'
    else name
end
from Student
--删
DELETE Student where 
name=(case 
    when age=100 then '老师'
    when name='张三' then '三儿'
    when name='李四' then '四儿'
    else name
end)

--改
update Student set 
name=(case 
    when age=100 then '老师'
    when name='张三' then '三儿'
    when name='李四' then '四儿'
    else name
end)