学步园

    
--lower把大写转小写  upper 把小写转大写
select * from emp where lower(ename)='smith';

select upper('helloworld') from dual;

select lower('HELLOWORLD') from dual;

--INITCAP使串中的所有单词的首字母变为大写 
select INITCAP('sql course') from dual

--CONCAT 连接两个字符串;
select concat('Hello','World') from dual

--取子字符串,从start开始,取count个
select substr('HelloWorld',1,5)  from dual

--取子字符串,从4开始取到末尾
select substr('HelloWorld',4)  from dual

--LENGTH 返回字符串的长度;
select length('HelloWorld')  from dual

--INSTR(string,char) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置,从1开始;
select INSTR('HelloWorld','H')  from dual

--RPAD在列的右边粘贴字符 LPAD在列的左边粘贴字符
select RPAD(sal,8,'*') from emp

select lpad(sal,8,'*') from emp

--TRIM删除首尾的空字符串
select length(trim('  HelloWorld  ')) from dual

--TRIM删除首尾的H
select trim('H' from 'HelloWorldH') from dual

--TRIM删除首的H
select trim(leading 'H' from 'HelloWorldH') from dual

--TRIM删除尾的H
select trim(trailing 'H' from 'HelloWorldH') from dual

--TRIM删除首尾的H
select trim(both 'H' from 'HelloWorldH') from dual

--REPLACE('string','s1','s2')
--string 希望被替换的字符或变量 
--s1 需要被替换的字符串 s2 替换的字符串
select REPLACE('HelloWorld','ll','FF') from dual

--数值函数
select Round(45.926,2) from dual

--截断
select TRUNC(45.926,2) from dual

--取模
select  MOD(1600,300)  from dual

--日期函数
select sysdate from dual

--得到下一小时 0分0秒
select trunc(sysdate+1/24,'hh') from dual;

--得到下一天  0时0分0秒
select trunc(sysdate+1) from dual;


--得到下一月 1号0时0分0秒
select last_day(sysdate) from dual;
select trunc(last_day(sysdate)+1) from dual;

--得到下一年 1月1号0时0分0秒
select add_months(trunc(sysdate,'yyyy'),12) from dual;

--默认按照 dd进行 4舍5入. 超过中午 12点就进入下一天.
select ROUND(SYSDATE) from dual

select ROUND(SYSDATE,'mm')   from dual

select ROUND(SYSDATE,'yyyy')  from dual

--截断日期
select trunc(SYSDATE,'mm')   from dual

select trunc(SYSDATE,'yyyy')  from dual

--转换函数: to_number  to_char  to_date
select to_number('123') from dual;

select to_char(123) from dual;

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss DAY AM') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_date('2012-06-13 03:58:52','yyyy-mm-dd hh24:mi:ss') as currdate from dual;


--nvl
select ename,sal,nvl(comm,0) as comm,(sal+nvl(comm,0))*12 from emp;

--nvl2
select ename,sal,nvl2(comm,comm,0) from emp;

--case
select * from emp;

select ename,job,sal,
    case job when 'SALESMAN' THEN  sal*0.9 
            when 'MANAGER' then sal*0.85
            when 'ANALYST' then sal+100
            when 'CLERK' then sal+200
    else sal end 
    as t_sal  --别名
 from emp 
 
   select ename,job,sal,
     decode( job, 'SALESMAN',   sal*0.9, 
                'MANAGER',  sal*0.85,
                'ANALYST',  sal+100,
                'CLERK',  sal+200
           )as t_sal  --别名
     from emp