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 ;