ORACLE SQL单行函数（二）【weber出品必属精品】
浏览数：23 /
时间：2015年06月12日
11.dual：虚表，任何用户都可以使用，表结构如下：
SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1)
12.dual的作用：
1. 查询数据库系统日期
2. 进行四则运算
SQL> select sysdate from dual; ---这里查询数据库系统日期 SYSDATE --------- 15-AUG-14 SQL> select 34*12 from dual; ---这里进行四则运算 34*12 ---------- 408 SQL> select ‘abc‘ from dual; ---这里进行字符串的输出 ‘AB --- abc
13.日期的格式：
默认的格式：DD-MON-RR
如何改变日期的格式：nls_date_format
SQL> alter session set nls_date_format=‘yyyy--mm--dd hh24:mi:ss‘; ---修改日期格式 Session altered. SQL> select sysdate from dual; SYSDATE --------------------- 2014--08--15 14:56:40
14.日期的计算
两个日期相减得到是天数：
SQL> select sysdate,hiredate,sysdate-hiredate from emp; SYSDATE HIREDATE SYSDATE-HIREDATE ------------------- ------------------- ---------------- 2014-08-15 09:11:48 1980-12-17 00:00:00 12294.3832 2014-08-15 09:11:48 1981-02-20 00:00:00 12229.3832 2014-08-15 09:11:48 1981-02-22 00:00:00 12227.3832 2014-08-15 09:11:48 1981-04-02 00:00:00 12188.3832 2014-08-15 09:11:48 1981-09-28 00:00:00 12009.3832 2014-08-15 09:11:48 1981-05-01 00:00:00 12159.3832 2014-08-15 09:11:48 1981-06-09 00:00:00 12120.3832 2014-08-15 09:11:48 1987-04-19 00:00:00 9980.38319 2014-08-15 09:11:48 1981-11-17 00:00:00 11959.3832 2014-08-15 09:11:48 1981-09-08 00:00:00 12029.3832 2014-08-15 09:11:48 1987-05-23 00:00:00 9946.38319 2014-08-15 09:11:48 1981-12-03 00:00:00 11943.3832 2014-08-15 09:11:48 1981-12-03 00:00:00 11943.3832 2014-08-15 09:11:48 1982-01-23 00:00:00 11892.3832
计算周数只需要除以7即可
SQL> select sysdate,hiredate,sysdate-hiredate from emp; SYSDATE HIREDATE SYSDATE-HIREDATE ------------------- ------------------- ---------------- 2014-08-15 09:11:48 1980-12-17 00:00:00 12294.3832 2014-08-15 09:11:48 1981-02-20 00:00:00 12229.3832 2014-08-15 09:11:48 1981-02-22 00:00:00 12227.3832 2014-08-15 09:11:48 1981-04-02 00:00:00 12188.3832 2014-08-15 09:11:48 1981-09-28 00:00:00 12009.3832 2014-08-15 09:11:48 1981-05-01 00:00:00 12159.3832 2014-08-15 09:11:48 1981-06-09 00:00:00 12120.3832 2014-08-15 09:11:48 1987-04-19 00:00:00 9980.38319 2014-08-15 09:11:48 1981-11-17 00:00:00 11959.3832 2014-08-15 09:11:48 1981-09-08 00:00:00 12029.3832 2014-08-15 09:11:48 1987-05-23 00:00:00 9946.38319 2014-08-15 09:11:48 1981-12-03 00:00:00 11943.3832 2014-08-15 09:11:48 1981-12-03 00:00:00 11943.3832 2014-08-15 09:11:48 1982-01-23 00:00:00 11892.3832
给日期减去1，效果作用在天上
SQL> select hiredate,hiredate-1 from emp; HIREDATE HIREDATE-1 --------------------- --------------------- 1980--12--17 00:00:00 1980--12--16 00:00:00 1981--02--20 00:00:00 1981--02--19 00:00:00 1981--02--22 00:00:00 1981--02--21 00:00:00 1981--04--02 00:00:00 1981--04--01 00:00:00 1981--09--28 00:00:00 1981--09--27 00:00:00 1981--05--01 00:00:00 1981--04--30 00:00:00
减少一小时，增加也是一样：
SQL> select sysdate,sysdate-1/(24) from dual; SYSDATE SYSDATE-1/(24) --------------------- --------------------- 2014--08--15 15:04:43 2014--08--15 14:04:43
增加一分钟：
SQL> select sysdate ,sysdate+1/(24*60) from dual; SYSDATE SYSDATE+1/(24*60) --------------------- --------------------- 2014--08--15 15:06:47 2014--08--15 15:07:47
增加一秒钟：
SQL> select sysdate,sysdate+1/(24*60*60) from dual; SYSDATE SYSDATE+1/(24*60*60 ------------------- ------------------- 2014-08-15 09:14:52 2014-08-15 09:14:53
15.日期函数
MONTHS_BETWEEN：两个日期之间的月数
SQL> select hiredate,sysdate,months_between(sysdate,hiredate) from emp; HIREDATE SYSDATE MONTHS_BETWEEN(SYSDATE,HIREDATE) ------------------- ------------------- -------------------------------- 1980-12-17 00:00:00 2014-08-15 09:18:21 403.947992 1981-02-20 00:00:00 2014-08-15 09:18:21 401.851218 1981-02-22 00:00:00 2014-08-15 09:18:21 401.786701 1981-04-02 00:00:00 2014-08-15 09:18:21 400.431863 1981-09-28 00:00:00 2014-08-15 09:18:21 394.593153 1981-05-01 00:00:00 2014-08-15 09:18:21 399.464121 1981-06-09 00:00:00 2014-08-15 09:18:21 398.206056 1987-04-19 00:00:00 2014-08-15 09:18:21 327.883476
ADD_MONTHS：为一个日期增加月份：
SQL> select sysdate,add_months(sysdate,1) from dual; SYSDATE ADD_MONTHS(SYSDATE,1) --------------------- --------------------- 2014--08--15 15:14:07 2014--09--15 15:14:07
NEXT_DAY：一个日期的下一个指定日子（例如：星期五）的日期
SQL> select sysdate ,next_day(sysdate,‘friday‘) next_day from dual; SYSDATE NEXT_DAY --------------------- --------------------- 2014--08--15 15:17:50 2014--08--22 15:17:50
LAST_DAY：某个月份的最后一天
SQL> select sysdate ,last_day(sysdate) from dual; SYSDATE LAST_DAY(SYSDATE) --------------------- --------------------- 2014--08--15 15:20:12 2014--08--31 15:20:12
对日期进行截取：
ROUND：要进行四舍五入
TRUNC：不进行四舍五入
SQL> select sysdate,round(sysdate) round,trunc(sysdate) trunc from dual; SYSDATE ROUND TRUNC --------------------- --------------------- -------------------- 2014--08--15 15:21:58 2014--08--16 00:00:00 2014--08--15 00:00:00
16. 转换函数
数据类型转换方式：
1. 隐士转换，也叫自动转换
2. 显示转换，通过转换函数进行转换
TO_CHAR函数：可以把数字和日期转换为字符
TO_CHAR(date, ‘format_model‘)：将日期转换为字符 format_model是：日期的格式
SQL> select sysdate,to_char(sysdate,‘mm-dd-yyyy hh24:mi:ss‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘MM --------------------- ------------------- ---按照日期格式将格式转换后输出 2014--08--15 15:28:27 08-15-2014 15:28:27
Format_model:
1、日期格式必须用单引号括起来，并且是大小写敏感的
2、日期格式必须是可用的
3、前面加上 fm字符以压缩掉开始和结束的空格或者前置的零
4、同日期值用逗号隔开
SQL> select sysdate,to_char(sysdate,‘YYYY‘) from dual; SYSDATE TO_C --------------------- ---- 2014--08--15 15:31:33 2014
验证：日期格式必须用单引号括起来，并且是大小写敏感的
SQL> select sysdate,to_char(sysdate,‘YEAR‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘YEAR‘) --------------------- ------------------------------------------ 2014--08--15 15:32:31 TWENTY FOURTEEN SQL> select sysdate ,to_char(sysdate,‘year‘) from dual; SYSDATE TO_CHAR(SYSDATE,‘YEAR‘) --------------------- ------------------------------------------ 2014--08--15 15:32:53 twenty fourteen
验证：简写和祥写的不同 ---日期元素区分大小写
SQL> select sysdate,to_char(sysdate,‘mm‘),to_char(sysdate,‘month‘) from dual; SYSDATE TO TO_CHAR(SYSDATE,‘MONTH‘) --------------------- -- --------------------------- 2014--08--15 15:35:53 08 august SQL> select sysdate,to_char(sysdate,‘dy‘),to_char(sysdate,‘day‘) from dual; SYSDATE TO_CHAR(S TO_CHAR(SYSDATE,‘DAY‘) --------------------- --------- --------------------------- 2014--08--15 15:36:43 fri friday SQL> select sysdate,to_char(sysdate,‘DY‘),to_char(sysdate,‘DAY‘) from dual; SYSDATE TO_CHAR(S TO_CHAR(SYSDATE,‘DAY‘) --------------------- --------- --------------------------- 2014--08--15 15:37:22 FRI FRIDAY
日期中时间部分的格式：
SQL> select sysdate,to_char(sysdate,‘yyyy/mm/dd hh24:mi:ss pm‘) time from dual; SYSDATE TIME --------------------- ---------------------- 2014--08--15 15:41:26 2014/08/15 15:41:26 pm
增加字符串需要用双引号将字符串括起来：
SQL> select sysdate,to_char(sysdate,‘day "of" month‘) string from dual; SYSDATE STRING ------------------- --------------------------------------------- 2014-08-15 09:40:38 friday of august
fm：去掉日期元素前置的零和空格
SQL> select sysdate,to_char(sysdate,‘fmyyyy-mm-dd hh24:mi:ss am‘) 去掉空格 from dual; SYSDATE 去掉空格 --------------------- ---------------------- 2014--08--15 15:50:40 2014-8-15 15:50:40 pm ---08前面的0被去掉了
在一个数字前面显示$符号 ---$不管在9999.99的前面还是后面，结果显示都是在9999.99的前面。
SQL> select ename,sal,to_char(sal,‘9999.99$‘) from emp; ENAME SAL TO_CHAR(S ------ ----- --------- SMITH 800 $800.00 ALLEN 1600 $1600.00 WARD 1250 $1250.00 JONES 2975 $2975.00 MARTIN 1250 $1250.00 BLAKE 2850 $2850.00 CLARK 2450 $2450.00 SCOTT 4000 $4000.00 KING 5000 $5000.00 TURNER 1500 $1500.00 ADAMS 1100 $1100.00 JAMES 950 $950.00 FORD 3000 $3000.00 MILLER 1300 $1300.00
如果9999.99变成了999.99，大于1000的数字则会显示乱码。
SQL> select ename,to_char(sal,‘999.99‘) from emp; ENAME TO_CHAR ---------- ------- SMITH 800.00 ALLEN ####### WARD ####### JONES ####### MARTIN ####### BLAKE ####### CLARK ####### SCOTT ####### KING #######
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
