Oracle基本操作命令

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Windows\System32>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 5月 30 09:18:17 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  scott
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


==========================================================================================
*********************************   创建表   *********************************************
==========================================================================================


SQL> create table t1(column1 char(20));
create table t1(column1 char(20))
             *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用


SQL> create table t2(column2 varchar2(20));

表已创建。

SQL> create table t3(column3 nchar(20));

表已创建。

SQL> create table t4(column4 nvarchar2(20));

表已创建。

SQL> create table t5(column5 number(3,2));

表已创建。




==========================================================================================
****************************   查看当前用户所有的表   ************************************
==========================================================================================


SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$9ttTjqQZRbOwGEUsPDyjQQ==$0 TABLE
BIN$IOPvkTFmRNKHcxegLj0VcA==$0 TABLE
BIN$VG2JVrxZQCWarnB48qVJpQ==$0 TABLE
BIN$irv60+tpTqOJYgZkVoVoSA==$0 TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
STUDENT                        TABLE
STUDENTCOPY                    TABLE
STUDENTCOPY2                   TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T1_USER                        TABLE
T2                             TABLE
T3                             TABLE
T4                             TABLE
T5                             TABLE
T6                             TABLE
TEST                           TABLE
T_USER                         TABLE

已选择20行。


==========================================================================================
*********************************   删除表   *********************************************
==========================================================================================

SQL> drop table test;

表已删除。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$9ttTjqQZRbOwGEUsPDyjQQ==$0 TABLE
BIN$IOPvkTFmRNKHcxegLj0VcA==$0 TABLE
BIN$VG2JVrxZQCWarnB48qVJpQ==$0 TABLE
BIN$irv60+tpTqOJYgZkVoVoSA==$0 TABLE
BIN$y+sktAUPS+u3Ta8T+VcJPA==$0 TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
STUDENT                        TABLE
STUDENTCOPY                    TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
STUDENTCOPY2                   TABLE
T1                             TABLE
T1_USER                        TABLE
T2                             TABLE
T3                             TABLE
T4                             TABLE
T5                             TABLE
T6                             TABLE
T_USER                         TABLE

已选择20行。

SQL>


==========================================================================================
******************************   number数据类型   ****************************************
==========================================================================================

SQL> insert into t5 values(1.123);

已创建 1 行。

SQL> insert into t5 values(1.123456);

已创建 1 行。

SQL> select * from t5;

   COLUMN5
----------
      1.12
      1.12

SQL> insert into t5 values(11.123456);
insert into t5 values(11.123456)
                      *
第 1 行出现错误:
ORA-01438: 值大于为此列指定的允许精度



==========================================================================================
******************************************************************************************
==========================================================================================


SQL> create table t6(column6 number(3));

表已创建。

SQL> insert into t6 values(11.123456);

已创建 1 行。

SQL> select * from t6;

   COLUMN6
----------
        11

SQL> insert into t6 values(11.67);

已创建 1 行。

SQL> select * from t6;

   COLUMN6
----------
        11
        12




==========================================================================================
*********************************   获得系统当前时间   ***********************************
==========================================================================================


SQL> select sysdate from dual;

SYSDATE
--------------
30-5月 -15

SQL> select to_char(sysdate, 'yyyymmdd hh24:mm:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
20150530 00:05:35

SQL> select to_char(systimestamp, 'yyyymmdd hh24:mm:ss') from dual;

TO_CHAR(SYSTIMEST
-----------------
20150530 00:05:24

SQL> select to_char(systimestamp, 'yyyymmdd hh24:mm:ssxff6') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYYM
---------------------------
20150530 00:05:00.762000


//使用“hh24:mm:ss”这个写法，将会得到错误的系统时间，但是却没有报错！

==========================================================================================
******************************************************************************************
==========================================================================================



SQL> select to_char(systimestamp, 'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSTIMEST
-----------------
20150530 00:29:35

SQL> select to_char(systimestamp, 'yyyymmdd hh24:mi:ssxff6') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYYM
---------------------------
20150530 00:29:50.848000


//使用“hh24:mi:ss”这个写法，才可以得到正确的系统时间！


==========================================================================================
*****************************   按定义的格式创建表   *************************************
==========================================================================================


SQL> create table student(stnumber number(6),stname varchar2(10),stbirthday date
);

表已创建。


SQL> desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE


==========================================================================================
*********************************   添加列   *********************************************
==========================================================================================



SQL> alter table student add column telephone varchar2(11);
alter table student add column telephone varchar2(11)
                        *
第 1 行出现错误:
ORA-00904: : 标识符无效


SQL> alter table student add  telephone varchar2(11);

表已更改。

SQL> desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE
 TELEPHONE                                          VARCHAR2(11)



==========================================================================================
*********************************   修改列   *********************************************
==========================================================================================


SQL> alter table student modify telephone varchar2(20);

表已更改。

SQL> desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE
 TELEPHONE                                          VARCHAR2(20)

SQL> alter table student modify telephone varchar2(11);

表已更改。

SQL> desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE
 TELEPHONE                                          VARCHAR2(11)



==========================================================================================
*********************************   删除列   *********************************************
==========================================================================================


SQL> alter table student drop telephone;
alter table student drop telephone
                         *
第 1 行出现错误:
ORA-00905: 缺失关键字


SQL> alter table student drop column telephone;

表已更改。

SQL> desc student;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE



==========================================================================================
******************************   往表中插入数据   ****************************************
==========================================================================================


SQL> insert into student values(00123,'xiaoming','22-5月-2015');

已创建 1 行。


SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15


==========================================================================================
******************************************************************************************
==========================================================================================


SQL> select sysdate from dual;

SYSDATE
--------------
30-5月 -15


SQL> insert into student values(125,'xiaoli', to_date('20150523','yyyymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15



==========================================================================================
*********************************   复制表   *********************************************
==========================================================================================


SQL> create table studentcopy as select * from student;

表已创建。

SQL> select * from studentcopy;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15


==========================================================================================
*********************************   仅复制表的结构   *************************************
==========================================================================================


SQL> create table studentcopy2 as select * from student where 1>2;

表已创建。

SQL> select * from studentcopy2;

未选定行

SQL> desc studentcopy2;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 STNUMBER                                           NUMBER(6)
 STNAME                                             VARCHAR2(10)
 STBIRTHDAY                                         DATE


==========================================================================================
*****************************   将源表中的数据复制到复制表中   ***************************
==========================================================================================


SQL> insert into studentcopy2 select * from student;

已创建2行。

SQL> select * from studentcopy2;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15

SQL>




==========================================================================================
**************************   以上可以总结为Oracle的基本增删改查   ************************
==========================================================================================






==========================================================================================
*********************************   插入空值   *******************************************
==========================================================================================


SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15


//方式1：

SQL> insert into student values(124,null,to_date('20150530','yyyymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15


//方式2：

SQL> insert into student(stnumber,stbirthday) values(124, to_date('20150530','yy
yymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15
       124            30-5月 -15


//查看空值的记录：

SQL> select * from student where stname =null;

未选定行

SQL> select * from student where stname ='';

未选定行


//上面这两种方式都不行，唯一正确的是下面这种方式：

SQL> select * from student where stname is null;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       124            30-5月 -15
       124            30-5月 -15

SQL>


==========================================================================================
******************************   删除表中的所有记录   ************************************
==========================================================================================

//方式1：

SQL>truncate table studentcopy2;

表被截断。

//方式2：

SQL>delete from studentcopy2;

已删除0行。


//注意：两种方式的区别！

==========================================================================================
*********************************   插入源表的数据   *************************************
==========================================================================================

SQL> insert into studentcopy2 select * from student;    //也可以只选择几行数据插入；

已创建4行。

SQL> select * from studentcopy2;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15
       124            30-5月 -15

SQL>


==========================================================================================
******************************   更新和修改表的数据   ************************************
==========================================================================================


SQL> update studentcopy2 set stname='aa' where stnumber=124;

已更新2行。

SQL> select * from studentcopy2;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124 aa         30-5月 -15
       124 aa         30-5月 -15

SQL> delete studentcopy2 where stnumber=124;

已删除2行。

SQL> select * from studentcopy2;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15

SQL>



==========================================================================================
******************************   对表进行查询   ******************************************
==========================================================================================

//注意：大小写的问题：

SQL> insert into student values(126,'AA',to_date('20150530','yyyymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15
       124            30-5月 -15
       126 AA         30-5月 -15

SQL> insert into student values(127,'BB',to_date('20150530','yyyymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15
       124            30-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15

已选择6行。

===================================== count() 查询 =====================================================


SQL> select count(*) from student;

  COUNT(*)
----------
         6

SQL> select count(*) from student where stname is not null;

  COUNT(*)
----------
         4

SQL> select count(*) from student where stname='aa';

  COUNT(*)
----------
         0

SQL> select * from student where stname='aa';

未选定行

SQL> select * from student where stname='AA';

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       126 AA         30-5月 -15

SQL> select * from student where uper(stname)='aa';
select * from student where uper(stname)='aa'
                            *
第 1 行出现错误:
ORA-00904: "UPER": 标识符无效


SQL> select * from student where upper(stname)='aa';

未选定行

SQL> insert into student values(128,'aa',to_date('20150530','yyyymmdd'));

已创建 1 行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124            30-5月 -15
       124            30-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15

已选择7行。

SQL> select * from student where upper(stname)='aa';

未选定行

SQL> select * from student where upper(stname)='AA';    //upper的意思就是“变成大写的”。

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       126 AA         30-5月 -15
       128 aa         30-5月 -15

SQL>

==========================================================================================
*****************************   插入带有符号的字符串（A'B）   ****************************
==========================================================================================


SQL> update student set stname='A''B' where stnumber=124;

已更新2行。

SQL> select * from student;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15

已选择7行。

SQL>


====================================== 使用匹配符查询 =============================================

SQL> select * from student where stname like 'A%';

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       126 AA         30-5月 -15

SQL> select * from student where stname like 'A_';

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       126 AA         30-5月 -15

SQL>

===================================== length() 查询 ===================================================


SQL> select * from student where length(stname)=2;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15


====================================== order by 查询 ===================================================


SQL> select * from student order by stnumber desc;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       128 aa         30-5月 -15
       127 BB         30-5月 -15
       126 AA         30-5月 -15
       125 xiaoli     23-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       123 xiaoming   22-5月 -15

已选择7行。

SQL> select * from student order by stnumber;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       125 xiaoli     23-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15

已选择7行。

SQL> select * from student order by stnumber desc,stname;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       128 aa         30-5月 -15
       127 BB         30-5月 -15
       126 AA         30-5月 -15
       125 xiaoli     23-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       123 xiaoming   22-5月 -15

已选择7行。

SQL> select * from student order by stnumber , stname;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       125 xiaoli     23-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15

已选择7行。

SQL> select * from student order by  stname;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       126 AA         30-5月 -15
       127 BB         30-5月 -15
       128 aa         30-5月 -15
       125 xiaoli     23-5月 -15
       123 xiaoming   22-5月 -15

已选择7行。

SQL> select * from student order by  stname desc;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       128 aa         30-5月 -15
       127 BB         30-5月 -15
       126 AA         30-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15

已选择7行。

SQL> select * from student order by 1 desc;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       128 aa         30-5月 -15
       127 BB         30-5月 -15
       126 AA         30-5月 -15
       125 xiaoli     23-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15
       123 xiaoming   22-5月 -15

已选择7行。

SQL> select * from student order by 2 desc;

  STNUMBER STNAME     STBIRTHDAY
---------- ---------- --------------
       123 xiaoming   22-5月 -15
       125 xiaoli     23-5月 -15
       128 aa         30-5月 -15
       127 BB         30-5月 -15
       126 AA         30-5月 -15
       124 A'B        30-5月 -15
       124 A'B        30-5月 -15

已选择7行。

SQL>

======================================= 使用别名查询 ===================================================

SQL> select stnumber 学号, stname "姓名" from student;

      学号 姓名
---------- ----------
       123 xiaoming
       125 xiaoli
       124 A'B
       124 A'B
       126 AA
       127 BB
       128 aa

已选择7行。

SQL> select stnumber "学 号" from student;    //中间带空格的别名；

     学 号
----------
       123
       125
       124
       124
       126
       127
       128

已选择7行。

SQL>

===================================== 使用连接符：|| =================================================


SQL> select '学号是：'||stnumber||'姓名是：'||stname from student;

'学号是：'||STNUMBER||'姓名是：'||STNAME
------------------------------------------------------------------
学号是：123姓名是：xiaoming
学号是：125姓名是：xiaoli
学号是：124姓名是：A'B
学号是：124姓名是：A'B
学号是：126姓名是：AA
学号是：127姓名是：BB
学号是：128姓名是：aa

已选择7行。

SQL>



==========================================================================================
****************************   使用 group by 对表数据进行查询   **************************
==========================================================================================


SQL> create table studentscore(stnumber number, stcourse varchar2(20),stscore nu
mber);

表已创建。

SQL> insert into studentscore values(1211,'语文',90);

已创建 1 行。

SQL> insert into studentscore values(1211,'数学',96);

已创建 1 行。

SQL> insert into studentscore values(1211,'英语',92);

已创建 1 行。

SQL> insert into studentscore values(1222,'语文',88);

已创建 1 行。

SQL> insert into studentscore values(1222,'数学',85);

已创建 1 行。

SQL> insert into studentscore values(1233,'语文',86);

已创建 1 行。

SQL> select * from studentscore;

  STNUMBER STCOURSE                STSCORE
---------- -------------------- ----------
      1211 语文                         90
      1211 数学                         96
      1211 英语                         92
      1222 语文                         88
      1222 数学                         85
      1233 语文                         86

已选择6行。


====================================================================================================


SQL> select stnumber,sum(stscore)  from studentscore group by stnumber;

  STNUMBER SUM(STSCORE)
---------- ------------
      1222          173
      1233           86
      1211          278



SQL> select stcourse,avg(stscore)  from studentscore group by stcourse;

STCOURSE             AVG(STSCORE)
-------------------- ------------
数学                         90.5
语文                           88
英语                           92



SQL> select stnumber,avg(stscore)  from studentscore group by stnumber having(av
g(stscore)>90);

  STNUMBER AVG(STSCORE)
---------- ------------
      1211   92.6666667



SQL> select stnumber,avg(stscore)  from studentscore group by stnumber having(av
g(stscore)>85);

  STNUMBER AVG(STSCORE)
---------- ------------
      1222         86.5
      1233           86
      1211   92.6666667



SQL> select stnumber,avg(stscore)  from studentscore group by stnumber having(av
g(stscore) > (select avg(stscore) from studentscore) );

  STNUMBER AVG(STSCORE)
---------- ------------
      1211   92.6666667



SQL> select avg(stscore) from studentscore;

AVG(STSCORE)
------------
        89.5



SQL> select stnumber,avg(stscore)  from studentscore group by stnumber having(av
g(stscore)>85) order by stnumber desc;

  STNUMBER AVG(STSCORE)
---------- ------------
      1233           86
      1222         86.5
      1211   92.6666667



SQL> select stnumber,avg(stscore)  from studentscore group by stnumber having(av
g(stscore)>85) order by avg(stscore) desc;

  STNUMBER AVG(STSCORE)
---------- ------------
      1211   92.6666667
      1222         86.5
      1233           86

SQL>


========================================================================================================
*********************************** 单行函数 ****************************************************************
========================================================================================================



SQL> select initcap('hello') from dual;

INITC
-----
Hello

SQL> select upper('hello') from dual;

UPPER
-----
HELLO

SQL> select lower(upper('hello')) from dual;

LOWER
-----
hello

SQL> select lower('HELLO') from dual;

LOWER
-----
hello

SQL> select length('abcd') from dual;

LENGTH('ABCD')
--------------
             4

SQL> select substr('abcd',2) from dual;

SUB
---
bcd

SQL> select substr('abcd',2,1) from dual;

S
-
b

SQL> select replace('hello','h','a') from dual;

REPLA
-----
aello

SQL> select replace('hello world','l','a') from dual;

REPLACE('HE
-----------
heaao worad

SQL>


=================================================================================================

SQL> select chr(156) from dual;
SP2-0784: 返回的以 0x9C 开头的字符无效或不完整

SQL> select chr(96) from dual;

C
-
`

SQL> select ascii('hello') from dual;

ASCII('HELLO')
--------------
           104

SQL> select ascii('hello world') from dual;

ASCII('HELLOWORLD')
-------------------
                104

SQL> select ascii('he') from dual;

ASCII('HE')
-----------
        104

SQL> select ascii('h') from dual;

ASCII('H')
----------
       104

SQL> select ascii('i') from dual;

ASCII('I')
----------
       105

SQL> select ascii('z') from dual;

ASCII('Z')
----------
       122

SQL> select ascii('你') from dual;

ASCII('你')
-----------
      50403

SQL> select ascii('你好') from dual;

ASCII('你好')
-------------
        50403

SQL> select ascii('好') from dual;

ASCII('好')
-----------
      47811

SQL> select lpad('abcd','10','z') from dual;

LPAD('ABCD
----------
zzzzzzabcd

SQL> select lpad('abcd','12','s') from dual;

LPAD('ABCD',
------------
ssssssssabcd

SQL>

===============================================================================================================

SQL> select sysdate from dual;

SYSDATE
--------------
30-5月 -15


SQL> select add_months(sysdate,5) from dual;

ADD_MONTHS(SYS
--------------
30-10月-15


SQL> select months_between(sysdate, to_date('20150101','yyyymmdd')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('20150101','YYYYMMDD'))
------------------------------------------------------
                                             4.9601374


SQL> select months_between(sysdate, to_date('20150130','yyyymmdd')) from dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('20150130','YYYYMMDD'))
------------------------------------------------------
                                                     4


SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2015


SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)
-------------------------
                        5


SQL> select extract(day from sysdate) from dual;

EXTRACT(DAYFROMSYSDATE)
-----------------------
                     30

SQL>


==========================================================================================================

SQL> select to_char(33,'L99.99') from dual;

TO_CHAR(33,'L99.
----------------
         ￥33.00

SQL> select to_char(33,'$99.99') from dual;

TO_CHAR
-------
 $33.00

SQL> select to_number('123') from dual;

TO_NUMBER('123')
----------------
             123


=================================== 格式化日期 ======================================================



SQL> select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

TO_CHAR(SYSDAT
--------------
2015年05月30日

SQL> select to_char(sysdate, 'yyyy"年"mm"月"dd"日"hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY"
----------------------
2015年05月30日07:21:45

SQL> select to_char(sysdate, 'yyyy"年"mm"月"dd"日"hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YYYY"
----------------------
2015年05月30日19:21:57

SQL>


===========================================================================================================