第04章 数据库内置函数

SELECT ABS(-4)  a FROM DUAL;
select sysdate from dual;
select abs(score) score from tbl_scoreinfo;

select dbms_random.value, 
dbms_random.value(55,100) from dual;

select substr(stuname,1,1)from tbl_studentinfo;
select concat('abc','123') as newColumn from dual;
select concat(stuno,stuname) as newColumn from tbl_studentinfo;

SELECT lpad('zhanglt',10,'z') from dual;

select rpad('zhanglt',10,'z') from dual;

select TRIM(' zhanglt ') from dual;

SELECT sysdate  FROM dual;
--在日期date上增加count个月
select add_months(sysdate,3)  hz from dual; 
--给出 Date2 - date1 的月数(可以是小数)
select  months_between(sysdate,to_date('2006-01-01','YYYY-MM-DD')) from dual;
--给出日期date 之后下一天的日期，这里的day 为星期，如MONDAY,Tuesday等。
select
 to_char(new_time(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
--取得当前的系统日期
select sysdate from dual;

--转换日期/数值格式到字符串
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(10,'$99.9') from dual;

--将字串转换为ORACLE 的日期
select to_date('2009/12/09','yyyy/mm/dd') from dual;
select to_date('2009-12-09','yyyy-mm-dd') from dual;

--将给出的字符转换为数字
SELECT TO_NUMBER ('1947') FROM DUAL

select * from tbl_studentinfo;
insert into tbl_studentinfo(stuno,stuname) values('05005','张三');
insert into tbl_studentinfo values('1004','hh',null,null,null,null,null)
--nvl（str,1） if str ==null  return 1; else return str; 
select nvl(stutel,123) from tbl_studentinfo
select nvl(stubirth,to_date('2006-01-01','YYYY-MM-DD')) from tbl_studentinfo
--NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。 
select nvl2(stutel,123,12) from tbl_studentinfo
select stusex, NVL2(stusex, 0, 1) from tbl_studentinfo;

SELECT 
    stuno,stusex,
    CASE stusex 
        WHEN '0'  THEN  '男'
        WHEN '1' THEN '女'
        ELSE '没有内容'   
    END 
from tbl_studentinfo;

SELECT 
    stuno,stusex,
    CASE stusex 
        WHEN '0'  THEN  '男'
        WHEN '1' THEN '女'
        ELSE '没有内容'   
    END 性别
from tbl_studentinfo;

select stuno,stusex,
DECODE( stusex ,
        '0'  ,  '男', 
        '1' ,  '女',  
        '没有内容'  ) "性别"
from tbl_studentinfo