【2017/4/24】oracle之sql（三）

来源：互联网发布：什么叫菜鸟网络编辑：程序博客网时间：2024/06/10 03:33

数字函数：

ROUND按指定小数位四舍五入
TRUNC按指定小数位截断
MOD两数相除，取余

4 5 6 . 7 8 9
-3 -2 -1 0 1 2 3

round(456.789,2)-->9>4-->456.79
trunc(456.789,2)-->----->456.78
round(456.789,-2)-->5>4-->500
round(456.789,-1)-->6>4-->460

SQL> select round(456.789,-1) from dual;

ROUND(456.789,-1)
-----------------
460

SQL> select round(456.789,-2) from dual;

ROUND(456.789,-2)
-----------------
500

SQL>

SQL> select mod(456,2) from dual;

MOD(456,2)
----------
0

SQL> select mod(457,2) from dual;

MOD(457,2)
----------
1

SQL>

SQL> select abs(-100) from dual; #绝对值

ABS(-100)
----------
100

日期函数

DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
---------
10-APR-17

SQL>
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

TO_CHAR(SY
----------
2017-04-10

SQL>

修改日期显示格式
SQL> select hiredate from emp where deptno=10;

HIREDATE
---------
09-JUN-81
17-NOV-81
23-JAN-82

SQL> show parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string
SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> select hiredate from emp where deptno=10;

HIREDATE
----------
1981-06-09
1981-11-17
1982-01-23

SQL>

SQL> select sysdate-hiredate,ename from emp;

SYSDATE-HIREDATE ENAME
---------------- ----------
13263.7462 SMITH
13198.7462 ALLEN
13196.7462 WARD

SQL> select sysdate-1,ename from emp;

SYSDATE-1 ENAME
---------- ----------
2017-04-09 SMITH
2017-04-09 ALLEN
2017-04-09 WARD
2017-04-09 JONES
2017-04-09 MARTIN

SQL> select ename,months_between(sysdate,hiredate) months from emp;

ENAME MONTHS
---------- ----------
SMITH 435.798359
ALLEN 433.701585

SQL> select ename,add_months(hiredate,3) from emp;

ENAME ADD_MONTHS
---------- ----------
SMITH 1981-03-17
ALLEN 1981-05-20
WARD 1981-05-22

SQL> select next_day(sysdate,'month') from dual;

NEXT_DAY(S
----------
2017-04-17

SQL> select to_char(sysdate,'day') from dual;

TO_CHAR(SYSDATE,'DAY')
------------------------------------
monday

SQL> select to_char(sysdate,'month') from dual;

TO_CHAR(SYSDATE,'MONTH')
------------------------------------
april

SQL> select to_char(sysdate,'year') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
twenty seventeen

SQL>
SQL> select last_day(sysdate) from dual;

LAST_DAY(S
----------
2017-04-30

SQL>

0 0