oracle函数概念

-- dual表的存在，主要是为了能够运行函数，以满足语法需求

SQL> desc dual;
Name  Type        Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y                         

SQL> select * from dual;

DUMMY
-----
X

-- 没有访问任何表，导致sysdate运行失败
SQL> select sysdate;

select sysdate

ORA-00923: FROM keyword not found where expected

SQL> select sysdate from dual;

SYSDATE
-----------
2006-1-17 2

-- 起别名 (alias)
SQL> select sysdate thisisnowtime from dual;

THISISNOWTIME
-------------
2006-1-17 20:

SQL> desc tbl_student;
Name       Type         Nullable Default Comments
---------- ------------ -------- ------- --------
STU_NO     CHAR(3)                                
STU_NAME   VARCHAR2(30)                           
STU_AGE    INTEGER                                
STU_HEIGHT NUMBER(5,2)  Y                         
STU_MARK   NUMBER(5,3)  Y                         

SQL> alter table tbl_student add stu_birth date;

Table altered

SQL> desc tbl_student;
Name       Type         Nullable Default Comments
---------- ------------ -------- ------- --------
STU_NO     CHAR(3)                                
STU_NAME   VARCHAR2(30)                           
STU_AGE    INTEGER                                
STU_HEIGHT NUMBER(5,2)  Y                         
STU_MARK   NUMBER(5,3)  Y                         
STU_BIRTH  DATE         Y                         

-- 插入数据，直接从sysdate获得时间
SQL> insert into tbl_student values('010','mike',13,130.5,97.5,sysdate);

1 row inserted

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001    mary                                                                14     131.50          
002    david                                                               14     132.50          
003    tom                                                                 15     137.50          
006    kent                                                                14     136.50          
009    jenny                                                               15     136.50          
010    mike                                                                13     130.50   97.500 2006-1-17 2

6 rows selected

SQL> insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00');

insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00')

ORA-01861: literal does not match format string

-- 使用to_date完成字符串到日期的转化
SQL> insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'))

ORA-00001: unique constraint (TEST1.SYS_C005269) violated

SQL> insert into tbl_student values('011','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));

1 row inserted

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001    mary                                                                14     131.50          
002    david                                                               14     132.50          
003    tom                                                                 15     137.50          
006    kent                                                                14     136.50          
009    jenny                                                               15     136.50          
010    mike                                                                13     130.50   97.500 2006-1-17 2
011    mike                                                                13     130.50   97.500 2000-1-1 10

7 rows selected

SQL> select to_char(sysdate,'yyyy/mm/ss') from dual;

TO_CHAR(SYSDATE,'YYYY/MM/SS')
-----------------------------
2006/01/46

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

TO_CHAR(SYSDATE,'YYYY/MM/DD')
-----------------------------
2006/01/17

-- 使用to_char完成日期到字符串的转化
SQL> select to_char(sysdate,'yyyy/mm/dd') nowdate from dual;

NOWDATE
----------
2006/01/17

--转小写
SQL> select lower('AbcDD') from dual;

LOWER('ABCDD')
--------------
abcdd

--转大写
SQL> select upper('AbcDD') from dual;

UPPER('ABCDD')
--------------
ABCDD

--切割掉左右空格
SQL> select trim('AbcDD ') from dual;

TRIM('ABCDD')
-------------
AbcDD

-- 左侧补0
SQL> select lpad('1',5,'0') from dual;

LPAD('1',5,'0')
---------------
00001

-- 右侧补0
SQL> select rpad('1',5,'0') from dual;

RPAD('1',5,'0')
---------------
10000

SQL> select lpad('1',5,' ') from dual;

LPAD('1',5,'')
--------------
    1

-- 胶水符 (glue),类似于concat函数
SQL> select 'dfrz'||lpad('1',5,'0') from dual;

'DFRZ'||LPAD('1',5,'0')
-----------------------
dfrz00001


SQL> select 'dfrz'||lpad('1',5,'0') empid from dual;

EMPID
---------
dfrz00001

SQL> select * from tbl_student;

STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001    mary                                                                14     131.50          
002    david                                                               14     132.50          
003    tom                                                                 15     137.50          
006    kent                                                                14     136.50          
009    jenny                                                               15     136.50          
010    mike                                                                13     130.50   97.500 2006-1-17 2
011    mike                                                                13     130.50   97.500 2000-1-1 10

7 rows selected

--五大聚合函数，count(),sum(),max(),min(),avg()
SQL> select count(*) from tbl_student;

  COUNT(*)
----------
         7

SQL> select max(stu_height) from tbl_student;

MAX(STU_HEIGHT)
---------------
          137.5

SQL> select min(stu_height) from tbl_student;

MIN(STU_HEIGHT)
---------------
          130.5

SQL> select avg(stu_height) from tbl_student;

AVG(STU_HEIGHT)
---------------
133.64285714285

SQL> select sum(stu_height) from tbl_student;

SUM(STU_HEIGHT)
---------------
          935.5

SQL> select sum(stu_height)/avg(stu_height) from tbl_student;

SUM(STU_HEIGHT)/AVG(STU_HEIGHT
------------------------------
                             7

SQL> select sum(stu_height)/count(stu_height) from tbl_student;

SUM(STU_HEIGHT)/COUNT(STU_HEIG
------------------------------
              133.642857142857