oracle函数概念

来源：互联网发布：毛少将知乎编辑：程序博客网时间：2024/06/09 14:50
-- dual表的存在，主要是为了能够运行函数，以满足语法需求SQL> desc dual;Name  Type        Nullable Default Comments----- ----------- -------- ------- --------DUMMY VARCHAR2(1) Y                         SQL> select * from dual;DUMMY-----X-- 没有访问任何表，导致sysdate运行失败SQL> select sysdate;select sysdateORA-00923: FROM keyword not found where expectedSQL> 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 alteredSQL> 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 insertedSQL> 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 26 rows selectedSQL> 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) violatedSQL> 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 insertedSQL> 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 2011    mike                                                                13     130.50   97.500 2000-1-1 107 rows selectedSQL> select to_char(sysdate,'yyyy/mm/ss') from dual;TO_CHAR(SYSDATE,'YYYY/MM/SS')-----------------------------2006/01/46SQL> 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-- 左侧补0SQL> select lpad('1',5,'0') from dual;LPAD('1',5,'0')---------------00001-- 右侧补0SQL> select rpad('1',5,'0') from dual;RPAD('1',5,'0')---------------10000SQL> 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')-----------------------dfrz00001SQL> select 'dfrz'||lpad('1',5,'0') empid from dual;EMPID---------dfrz00001SQL> 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 2011    mike                                                                13     130.50   97.500 2000-1-1 107 rows selected--五大聚合函数，count(),sum(),max(),min(),avg()SQL> select count(*) from tbl_student;  COUNT(*)----------         7SQL> select max(stu_height) from tbl_student;MAX(STU_HEIGHT)---------------          137.5SQL> select min(stu_height) from tbl_student;MIN(STU_HEIGHT)---------------          130.5SQL> select avg(stu_height) from tbl_student;AVG(STU_HEIGHT)---------------133.64285714285SQL> select sum(stu_height) from tbl_student;SUM(STU_HEIGHT)---------------          935.5SQL> select sum(stu_height)/avg(stu_height) from tbl_student;SUM(STU_HEIGHT)/AVG(STU_HEIGHT------------------------------                             7SQL> select sum(stu_height)/count(stu_height) from tbl_student;SUM(STU_HEIGHT)/COUNT(STU_HEIG------------------------------              133.642857142857
0 0