ORACLE学习笔记三

ORACLE学习笔记三

dual--虚拟表

单行函数：

单行函数一共有五类，分别是：字符函数，数字函数，日期函数，转换函数，通用函数。

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO

---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-12月-80            800                    20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30

      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10

      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20

      7839 KING       PRESIDENT            17-11月-81           5000                    10

      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30

      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20

      7900 JAMES      CLERK           7698 03-12月-81            950                    30

      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10

1.字符函数：

UPPER():转大写

LOWER()：转小写

INITCAP()：首字母大写

LENGTH()：求长度

REPLACE()：替换

SUBSTR（）：截取字符串

例如：select upper(ename) from scott.emp; 

结果：

UPPER(ENAM

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

JAMES

FORD

MILLER

例如：select replace(ename,'A','我') from emp;

结果：

REPLACE(ENAME,'A','

--------------------

SMITH

我LLEN

W我RD

JONES

M我RTIN

BL我KE

CL我RK

SCOTT

KING

TURNER

我D我MS

J我MES

FORD

MILLER

例如：select ename,substr(ename,2),substr(ename,2,3),substr(ename,-2) from emp;【注意substr(ename,-2)】的用法，-2表示为截取从后面

开始的两个字符。

结果：

ENAME      SUBSTR(ENAME,2)    SUBSTR SUBS

---------- ------------------ ------ ----

SMITH      MITH               MIT    TH

ALLEN      LLEN               LLE    EN

WARD       ARD                ARD    RD

JONES      ONES               ONE    ES

MARTIN     ARTIN              ART    IN

BLAKE      LAKE               LAK    KE

CLARK      LARK               LAR    RK

SCOTT      COTT               COT    TT

KING       ING                ING    NG

TURNER     URNER              URN    ER

ADAMS      DAMS               DAM    MS

JAMES      AMES               AME    ES

FORD       ORD                ORD    RD

MILLER     ILLER              ILL    ER

2.数字函数

ROUND():四舍五入

TRUNC():不四舍五入

MOD():取模运算

例如：select round(514.7524),round(514.7524,2),round(514.7524,-2),round(514.7524,-1) from dual;

结果：注意ROUND(514.7524,-2)和ROUND(514.7524,-1)的结果。

ROUND(514.7524) ROUND(514.7524,2) ROUND(514.7524,-2) ROUND(514.7524,-1)

--------------- ----------------- ------------------ ------------------

            515            514.75                500                510

例如：select trunc(514.7524),trunc(514.7524,2),trunc(514.7524,-2),trunc(514.7524,-1) from dual;

结果：

TRUNC(514.7524) TRUNC(514.7524,2) TRUNC(514.7524,-2) TRUNC(514.7524,-1)

--------------- ----------------- ------------------ ------------------

            514            514.75                500                510

例如：

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

 MOD(14,2)

----------

         0

例如：

SQL> select mod(14,3) from dual;

 MOD(14,3)

----------

         2

3.日期函数

a.sysdate; 当前日期的获取

例如：

SQL> select sysdate,sysdate+1,sysdate-100 from dual;

SYSDATE        SYSDATE+1      SYSDATE-100

-------------- -------------- --------------

11-10月-11     12-10月-11     03-7月 -11

b.last_day(日期) 获取日期的月份最后一天

例如：

select last_day(sysdate) from dual;

结果：

LAST_DAY(SYSDA SYSDATE

-------------- --------------

31-10月-11     11-10月-11

c.next_day(日期,星期X） 获取指定日期之后的下个星期X的日期

例如：select next_day(sysdate,'星期六') from dual;

结果：

NEXT_DAY(SYSDA

--------------

15-10月-11

d.add_months(日期，数字)获取若干月之后的日期

例如：select add_months(sysdate,3) from dual;

ADD_MONTHS(SYS

--------------

11-1月 -12

e.months_between(日期1，日期2）

例如：select months_between(sysdate,hiredate) from emp;

结果：

MONTHS_BETWEEN(SYSDATE,HIREDATE)

--------------------------------

                      369.817491

                      367.720716

                        367.6562

                      366.301362

                      360.462652

                       365.33362

                      364.075555

                      293.752975

                      358.817491

                      361.107813

                      292.623942

                      358.269104

                      358.269104

                      356.623942

4.转换函数

a.to_char(字符串|列，'格式字符串')

例如：select to_char(sysdate,'yyyy-mm-dd'), to_char(sysdate,'fmyyyy-mm-dd'), to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), to_char

(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

结果：

TO_CHAR(SY TO_CHAR(SY TO_CHAR(SYSDATE,'YY TO_CHAR(SYSDATE,'YY

---------- ---------- ------------------- -------------------

2011-05-11 2011-5-11  2011-05-11 10:19:34 2011-05-11 22:19:34

例如：select to_char(789673512312312,'999,999,999,999,999'),to_char(789673512312312,'L999,999,999,999,999') from dual;

结果：

TO_CHAR(789673512312 TO_CHAR(789673512312312,'L999,

-------------------- ------------------------------

 789,673,512,312,312          ￥789,673,512,312,312

b.to_date（字符串，格式) 字符串类型转换为日期类型

例如：select to_date('19410103','yyyy-mm-dd')   from dual;

结果：

TO_DATE('19410

--------------

03-1月 -41    

c.to_number(字符串) 字符串类型转换为number类型

例如：select to_number('1') + to_number('2') , '1'+'2' from dual;

结果：

TO_NUMBER('1')+TO_NUMBER('2')    '1'+'2'

----------------------------- ----------

                            3          3

5.通用函数

a.nvl() 处理null

例如：select  sal,comm,nvl(comm,0),(sal + nvl(comm,0))*12,nvl(comm,20)  from emp;

结果：

 SAL       COMM NVL(COMM,0) (SAL+NVL(COMM,0))*12 NVL(COMM,20)

---- ---------- ----------- -------------------- ------------

 800                      0                 9600           20

1600        300         300                22800          300

1250        500         500                21000          500

2975                      0                35700           20

1250       1400        1400                31800         1400

2850                      0                34200           20

2450                      0                29400           20

3000                      0                36000           20

5000                      0                60000           20

1500          0           0                18000            0

1100                      0                13200           20

 950                      0                11400           20

3000                      0                36000           20

1300                      0                15600           20

b.decode（列，判断值1，显示值1，判断值2，显示值2，。。。） 必须把此列中的所有情况全部写在后面，若不写则替换为空

例如：select dname,decode(dname,'ACCOUNTING','财务部','RESEARCH','研发部','SALES','销售部','OPERATIONS','管理部')as 全部替

换,decode(dname,'ACCOUNTING','财务部','SALES','销售部','OPERATIONS','管理部') as 部分替换 from dept;

结果：

DNAME          全部替 部分替

-------------- ------ ------

ACCOUNTING     财务部 财务部

RESEARCH       研发部

SALES          销售部 销售部

OPERATIONS     管理部 管理部