select abs(-100) from dual;

select sign(-100) from dual;

select power(2,3) from dual;

select sysdate from dual;

select current_date from dual;

select add_months(hiredate,2),hiredate from emp;

select last_day(sysdate) from dual;

select months_between(sysdate,hiredate) from emp;

select sysdate 当时日期,

round(sysdate) 最近0点日期,

round(sysdate,'day') 最近星期日,

round(sysdate,'month') 最近月初,

round(sysdate,'q') 最近季初日期,

round(sysdate,'year') 最近年初日期 from dual;

select next_day(sysdate,'星期一') from dual;

select

extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,

extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,

extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,

extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,

extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,

extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年

from dual;

select localtimestamp from dual;

select current_date from dual;

select current_timestamp from dual;

trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)

trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)

trunc(sysdate)+(interval '1' hour), --加1小时(1/24)

trunc(sysdate)+(INTERVAL '1' DAY), --加1天(1)

trunc(sysdate)+(INTERVAL '1' MONTH), --加1月

trunc(sysdate)+(INTERVAL '1' YEAR), --加1年

trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒

trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒

trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟

trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟

from dual;

select '999'+10 from dual;

select to_char(sysdate,'YYYY-MI-SS HH24:MI:SS') from dual;

select to_char(123.456789,'9999') from dual;

select to_char(123.456789,'0000.00') from dual;

select to_char(123.456789,'$0000.00') from dual;

select to_char(123.456789,'L0000.00') from dual;

select to_char(123456789,'999,999,999,999') from dual;

select to_date('2019/10/10 10:10:10','YYYY-MM-DD HH24:MI:SS') from dual;

select to_number('123,456,789','999,999,999') from dual;

select ename,nvl(to_char(mgr),'boss') from emp where mgr is null;

select hiredate,next_day(add_months(hiredate,6),'星期五') from emp;

select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3) from emp;

select ename,

sal,

deptno,

case deptno

when 10 then

sal * 1.1

when 20 then

sal * 1.2

when 30 then

sal * 1.3

end

from emp;

create table test(

id number(10) primary key,

type number(10) ,

t_id number(10),

value varchar2(5)

);

insert into test values(100,1,1,'张三');

insert into test values(200,2,1,'男');

insert into test values(300,3,1,'50');

insert into test values(101,1,2,'刘二');

insert into test values(201,2,2,'男');

insert into test values(301,3,2,'30');

insert into test values(102,1,3,'刘三');

insert into test values(202,2,3,'女');

insert into test values(302,3,3,'10');

select * from test;

select decode(type, 1, value) 姓名,

decode(type, 2, value) 性别,

decode(type, 3, value) 年龄

from test;

select min(decode(type, 1, value)) 姓名,

min(decode(type, 2, value)) 性别,

min(decode(type, 3, value)) 年龄

from test group by t_id;

select avg(sal) from emp;

select min(sal) from emp;

select max(sal) from emp;

select count(sal) from emp;

select sum(sal) from emp;

select avg(sal) from emp group by deptno;

select avg(sal),deptno from emp where sal is not null group by deptno having avg(sal) >2000 order by avg(sal);

select count(10000) from emp;

select deptno,count(1) from emp where sal>2000 group by deptno

select deptno,max(sal) from emp group by deptno;

select deptno,min(hiredate),max(hiredate) from emp group by deptno;

select ename, deptno

from emp e

where hiredate in (select min(hiredate) from emp group by deptno)

or hiredate in (select max(hiredate) from emp group by deptno)

select * from emp

select mm2.deptno, e1.ename, e1.hiredate

from emp e1,

(select min(e.hiredate) mind, max(e.hiredate) maxd, e.deptno

from emp e

group by e.deptno) mm2

where (e1.hiredate = mm2.mind

or e1.hiredate = mm2.maxd)

and e1.deptno = mm2.deptno;