initcap(st) 返回st将每个单词的首字母大写，所有其他字母小写
lower(st) 返回st将每个单词的字母全部小写
upper(st) 返回st将每个单词的字母全部大写
concat(st1,st2) 返回st为st2接st1的末尾（可用操作符"||"）
lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格
rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格
ltrim(st[,set]) 返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
rtrim(st[,set]) 返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，删除search_st
substr(st,m[,n]) n=返回st串的子串，从m位置开始，取n个字符长。缺省时，一直返回到st末端
length(st) 数值，返回st中的字符数
instr(st1,st2[,m[,n]]) 数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1
例：
1.
select initcap('THOMAS'),initcap('thomas') from test;
initca initca
------ ------
Thomas Thomas
2.
select concat('abc','def') "first" from test;
first
-----
abcdef
3.
select 'abc'||' '||'def' "first" from test;
first
-----
abc def
4.
select lpad(name,10),rpad(name,5,'*') from test;
lpad(name,10) rpad(name,5,'*')
------------ ----------------
mmx mmx**
abcdef abcde
5.
去掉地址字段末端的点及单词st和rd
select rtrim(address,'. st rd') from test
6.
select name,replace(name,'a','*') from test;
name replace(name,'a','*')
---- ---------------------
great gre*t
7.
select substr('archibald bearisol',6,9) a,substr('archibald bearisol',11) b from test;
a b
------- -------
bald bear bearisol
8.
select name,instr(name,' ') a,instr(name,' ',1,2) b from test;
name a b
------- -------- ---------
li lei 3 0
l i l 2 4
全大写 UPPER('SQL Course') SQL COURSE
首字母大写 INITCAP('SQL Course') Sql Course
12.
求长度 LENGTH('String') 6
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
mod(m,n) m被n除后的余数
power(m,n) m的n次方
round(m[,n]) m四舍五入至小数点后n位的值（n缺省为0）
trunc(m[,n]) m截断n位小数位的值（n缺省为0）
数字函数举例：
ROUND -- 对数值进行四舍五入操作
TRUNC -- 截断。注意截断和舍入的位数，可以是负数。
ROUND (45.923, 2) 45.92
ROUND (45.923, 0) 46
ROUND (45.923, -1) 50
TRUNC (45.923, 2) 45.92
TRUNC (45.923) 45
TRUNC (45.923, -1) 40
MOD -- 返回两数相除的余数
MOD(1600, 300) -- 100
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
日期函数：
举例：
求两个日期间有多少个月份 MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 1.9774194
在日期上加指定的月数 ADD_MONTHS('11-JAN-94',6) '11-JUL-94'
下一个星期几是什么时候 NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95'
某月最后一天 LAST_DAY('01-SEP-95') '30-SEP-95'
把日期四舍五入到月份 ROUND('25-MAY-95','MONTH') 01-JUN-95
ROUND('25-MAY-95 ','YEAR') 01-JAN-95
把日期截断到月份 TRUNC('25-MAY-95 ','MONTH') 01-MAY-95
TRUNC('25-MAY-95 ','YEAR') 01-JAN-95
日期函数：
add_months(d,n) 日期d加n个月
last_day(d) 包含d的月份的最后一天的日期
month_between(d,e) 日期d与e之间的月份数，e先于d
new_time(d,a,b) a时区的日期和时间d在b时区的日期和时间
next_day(d,day) 比日期d晚，由day指定的周几的日期
sysdate 当前的系统日期和时间
greatest(d1,d2,...dn) 给出的日期列表中最后的日期
least(d1,k2,...dn) 给出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期
HH24:MI:SS AM 15:45:32 PM
在格式串中加入字符串
DD " of " MONTH 12 of OCTOBER
用后缀拼出整个日期
ddspth fourteenth
日期格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代码 说明 举例或可取值的范围
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD 该月某一天 1－3
DY 三个大写字母表示的周几 SUN，...SAT
DAY 完整的周几，大写英文 SUNDAY，...SATURDAY
MM 月份 1－12
MON 三个大写字母表示的月份 JAN，...DEC
MONTH 完整 JANUARY,...DECEMBER
RM 月份的罗马数字 I,...XII
YY或YYYY 两位，四位数字年
HH:MI:SS 时：分：秒
HH12或HH24 以12小时或24小时显示
MI 分
SS 秒
AM或PM 上下午指示符
SP 后缀SP要求拼写出任何数值字段
TH 后缀TH表示添加的数字是序数 4th,1st
FM 前缀对月或日或年值，禁止填充
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
例：
1.
下一个周五的日期
select next_day(sysdate,6) from test;
2.
两个月前的今天的日期
select add_months(sysdate,-2) from test;
1.查询某周的第一天
select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') last_day
from (select substr('2004-32', 1, 4) yy, to_number(substr('2004-32', 6)) ww
from dual)
select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d')-6 first_day from dual
select min(v_date) from
(select (to_date('200201','yyyymm') + rownum) v_date
from all_tables
where rownum < 370)
where to_char(v_date,'yyyy-iw') = '2002-49'
2.查询某周的最后一天
select trunc(decode(ww, 53, to_date(yy || '3112', 'yyyyddmm'), to_date(yy || '-' || to_char(ww * 7), 'yyyy-ddd')), 'd') - 6 first_day
from (select substr('2004-33', 1, 4) yy, to_number(substr('2004-33', 6)) ww
from dual)
select trunc(to_date(substr('2003-01',1,5)||to_char((to_number(substr('2003-01',6)))*7),'yyyy-ddd'),'d') last_day from dual
select max(v_date) from
(select (to_date('200408','yyyymm') + rownum) v_date
from all_tables
where rownum < 370)
where to_char(v_date,'yyyy-iw') = '2004-33'
3.查询某周的日期
select min_date, to_char(min_date,'day') day from
(select to_date(substr('2004-33',1,4)||'001'+rownum-1,'yyyyddd') min_date
from all_tables
where rownum <= decode(mod(to_number(substr('2004-33',1,4)),4),0,366,365)
union
select to_date(substr('2004-33',1,4)-1||
decode(mod(to_number(substr('2004-33',1,4))-1,4),0,359,358)+rownum,'yyyyddd') min_date
from all_tables
where rownum <= 7
union
select to_date(substr('2004-33',1,4)+1||'001'+rownum-1,'yyyyddd') min_date
from all_tables
where rownum <= 7
)
where to_char(min_date,'yyyy-iw') ='2004-33'
---------------------------------------------------------------
处理日期大全
TO_DATE格式
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1.
日期和字符转换函数用法（to_date,to_char）
2.
select to_char( to_date(222,'J'),'Jsp') from dual
显示Two Hundred Twenty-Two
3.
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4.
两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意要用TO_DATE(null)
6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not
in ( '1', '7' )
查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒).
9.
select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
10. Next_day的用法
Next_day(date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D
11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hh:mi:ss') from all_objects;
12.
获得小时数
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19
获取年月日与此类似
13.
年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
)
14.
处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
16.
找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年
17.
yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
转换函数 在转换函数中会使用格式串
TO_NUMBER 将字符串转换成数字
nvl(m,n) 如果m值为null,返回n,否则返回m
如果expr1为NULL，返回expr2
NVL2 (expr1, expr2, expr3)
如果expr1为非空，则返回expr2，如果expr1为NULL，则返回expr3
NULLIF (expr1, expr2)
如果expr1=expr2，返回NULL，否则返回expr1
COALESCE (expr1, expr2, ..., exprn)
返回表达式列表中的第一个非空表达式
条件表达式，用case表达式或decode实现条件逻辑
CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
decode函数
DECODE(col|expression, search1, result1
[, search2,result2,...,]
[, default])
格式串包含在''中, 大小写敏感, 可以是任意有效的日期
格式
格式串：
YYYY 表示完整的四位数字年
YEAR 表示英文拼写的年
MM 表示两位数字月
MONTH 表示英文全拼的年
DY 表示三位缩写的星期
DAY 表示英文全拼的星期
附：
to_char()函数的格式：
---------------------------------
符号 说明
---------------------------------
使用此函数将数字转换成数字
9 每个9代表结果中的一位数字
0 代表要显示的先导0
$ 美元符号打印在数的左边
L 任意的当地货币符号
. 打印十进制的小数点
, 打印代表千分位的逗号
---------------------------------
TO_DATE 将字符串转换成格式日期
使用格式串
TO_DATE(char [, 'fmt'])
例：
1.
select to_number('123.45')+to_number('234.56') form test;
to_number('123.45')+to_number('234.56')
----------------------------------------
358.01
2.
select to_char(987654321) from test;
to_char(987654321)
------------------
987654321
3.
select to_char(123,'$9,999,999') a,to_char(54321,'$9,999,999') b,to_char(9874321,'$9,999,999') c from test;
a b c
------- ---------- -----------
$123 $54,321 $9,874,321
4.
select to_char(1234.1234,'999,999.999') a,to_char(0.4567,'999,999.999') b,to_char(1.1,'999,999.999') from test;
a b c
--------- ---------- ------------
1,234.123 .457 1.100
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
分组函数:
avg([distinct/all] n) 列n的平均值
count([all] *) 返回查询范围内的行数包括重复值和空值
count([distinct/all] n) 非空值的行数
max([distinct/all] n) 该列或表达式的最大值
min([distinct/all] n) 该列或表达式的最小值
stdev([distinct/all] n) 该列或表达式的标准偏差，忽略空值
sum([distinct/all] n) 该列或表达式的总和
variance([distinct/all] n) 该列或表达式的方差，忽略空值
识别可用的组函数；
描述组函数的使用；
用GROUP BY 子句分组数据；
用HAVING 子句包含或排除分组的行；
要点：
1.组函数的语法和原则
SELECT[column,] group_function(column), ...
FROM table
[WHEREcondition]
[GROUP BY column]
[ORDER BY column];
使用组函数的原则
DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定；
用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE；
所有组函数忽略空值（即跳过此行，十行中有一空值则按九行计算），为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数；
当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集，为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句；
组函数可以被嵌套两层深度；
AVG([DISTINCT|ALL] expr) 求expr的平均值，忽略空值
SQL> select count(*) from uimuser.test;
COUNT(*)
----------
6
SQL> select count(id) from uimuser.test;
COUNT(ID)
----------
4
语法：
SELECT column, group_function(column)
FROM table
[WHEREcondition]
[GROUP BY group_by_expression]
[ORDER BYcolumn];
如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中，
（即此时在SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中。换句话说，当使用组函数时，select子句中所有非组函数的列
必须都出现在group by子句中）
如果你未能在 GROUP BY 子句中包含这些列，你会收到一个错误信息；
GROUP BY 列不必在SELECT 列表中；
使用 WHERE 子句，你可以在划分行成组以前过滤行；
在 GROUP BY 子句中必须包含列；
在 GROUP BY 子句中你不能用列别名；
可以对多列进行group by，即实现组中组的目的；
默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值；
原则：
用 WHERE 子句约束选择的行，用 HAVING 子句约束组；
HAVING 子句可以优先于 GROUP BY 子句，但建议先用 GROUP BY 子句，因为这样更合逻辑；
oracle的执行顺序为：
如有where子句，则先执行where子句建立候选行
行被分组
组函数被应用
having子句进一步约束符合要求的组
例：
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
override：弃绝，渺视
exclude：把...排除在外，不计
respective：各自的，各个的
2330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
