一、oracle中的基本函数
更正:图中为梳子(x)数字函数
5.1单行函数
(1)字符函数 接收字符的输入并返回字符或者数字。
SQL>select initcap ('hello') from dual;
-- 把括号中字符串的首字母大写
Hello
SQL> SELECT lower('ABC') from dual;
--把括号中的字符串变为小写(对数字不起作用)
abc
SQL> SELECT lower('ABC') from dual;
--把括号中的字符串变为小写(对数字不起作用)
abc
SQL> SELECT upper('abc') from dual;
--把括号中的字符串变为小写(对数字不起作用)
ABC
SQL> SELECT ltrim('lwxy','lw') from dual;
--从左开始匹配,并删除匹配成功的
xy
SQL> SELECT ltrim('lwxy','lw') from dual; -
-从右开始匹配,并删除匹配成功的
lw
SQL> SELECT instr('worldwide','d') from dual;
--输出d在字符串中所在位数
5
SQL> SELECT substr('worldwide',3,2) from dual;
--从字符串的第三个位置开始截取两个
rl
SQL> SELECT concat('Hello','World') from dual;
--字符串的串联
HelloWorld
除此之外还有
char与ASCII
select char(96) from dual; --返回序号96所对应的ascii码符 C
select ascii('A') from dual; --返回字符A所对应的ascii码 97
lpad与rpad 左填充 右填充
select rpad('abc',5,x) from dual; --将字符串用x向右填充至5位
abcxx
select lpad('abx',5,x) from dual;
xxabc
trim、ltrim和rtrim
trim
① 去除字符串前后空格
select trim(' abc ') from dual; -- abc
②去除指定的单个字符
trim(both,'1' from '123fd11') --去除字符串左右两边的1 23fd
trim(leading,'1' from '123fd11') ——去除前面 23fd11
trim(trailing,'1' from '123fd11') --去除后面 123fd
ltrim①去除给定字符串左边的空格②去除从左边算起匹配的多个字符
ltrim(' sdhic ') --结果 'sdhic '
ltrim('scdfg', 'xs'); --结果 cdfg
--从左边起第一个字符要与给定字符匹配才返回去重结果,否则返回整个字符串。
ltrim('sdgh','zx'); --结果 sdgh
rtrim ①去除给定字符串右边的空格②去除从右边算起匹配的多个字符
translate 与 replace
语法:select TRANSLATE(char, from, to) from dual;
用法:返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。
若from比to字符串长,那么在from中比to中多出的字符将会被删除。
三个参数中有一个是空,返回值也将是空值。
SQL> select translate('abcdefga','abc','wo') 返回值 from dual;
返回值
-------
wodefgw
-- 将a换成w b换成o c删除
分析:该语句要将’abcdefga’中的’abc’转换为’wo’,
由于’abc’中’a’对应’wo’中的’w’,
故将’abcdefga’中的’a’全部转换成’w’;
而’abc’中’b’对应’wo’中的’o’,
故将’abcdefga’中的’b’全部转换成’o’;
‘abc’中的’c’在’wo’中没有与之对应的字符,
故将’abcdefga’中的’c’全部删除;
简单说来,就是将from中的字符转换为to中与之位置对应的字符,
若to中找不到与之对应的字符,返回值中的该字符将会被删除。
repalce
语法:REPLACE(char, search_string,replacement_string)
用法:将char中的字符串search_string全部转换为字符串replacement_string。
SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
返回值
---------
fgsgswsgs
--找不到字符串fk
SQL>select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
返回值
-----------------------
fgeeerrrtttsweeerrrttts
匹配到sg换成eeerrrttt
分析:第一个例子中由于’fgsgswsgs’中没有与’fk’匹配的字符串,
故返回值仍然是’fgsgswsgs’;
第二个例子中将’fgsgswsgs’中的字符串’sg’全部转换为’eeerrrttt’。
总结:综上所述,replace与translate都是替代函数,
只不过replace针对的是字符串,而translate针对的是单个字符。
length 返回()中字符串长度
select length('asdhgbj') from dual;
decode(value,if1 ,then1, if2 , then2……else)
找出下表中每个产品的最大销售额。
create table sale(id number(2),
JanuaryM number(10) ,FebruaryM number(10));
insert into sale values (1,2000,3000);
insert into sale values (2,3000,3000);
insert into sale values (3,3000,1000);
select id,decode(sign(JanuaryM-FebruaryM),1,JanuaryM,
0,JanuaryM,-1,FebruaryM) from sale;
--结果 1 3000
-- 2 3000
-- 3 3000
(2)日期函数
add_months() 按月对指定日期增加相应的月份 注意months s!!!!
select add_months(sysdate,2) from dual;
-- 返回当前系统日期2个月以后的得日期 (参数2可正可负)
months_between() 求两个日期之间相差的月份值
select months_between(sysdate,to_date('20180708','yyyymmdd')) from dual;
extract() 返回当前时间的天数day 年数year 月数month
select extract(day from sysdate) from dual;
--返回当前天数 如: 13
last_day() 提取指定月份的最后一天
select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-9月 -18
next_day() 返回指定日期后的下一个星期几,星期可用1~7代表
select next_day(sysdate,2) from dual;
-- 查找下周星期二是几月几号。
trunc(字段名,精度) 可用来截取日期和数字,截断 非四舍五入
SQL> select trunc(sysdate,'dd') from dual;
TRUNC(SYSDATE,
--------------
16-9月 -18
SQL> select trunc(sysdate,'mm') from dual;
TRUNC(SYSDATE,
--------------
01-9月 -18
SQL> select trunc(sysdate,'yyyy') from dual;
TRUNC(SYSDATE,
--------------
01-1月 -18
(3)数字函数
解释 | 式子 | 结果 |
---|---|---|
abs取绝对值 | abs(-15) | 15 |
ceil向上取整 | ceil(44.11) | 45 |
floor向下取整 | floor(-44.78) | -45 |
cos返回余弦 | cos(180) | -0.5984601 |
cosh双曲余弦 | cosh(0) | 1 |
power(低,次方) | power(4,2) | 16 |
mod(n,m) | mod(10,3) | 1 |
sqrt(n)开方 | sqrt(4) | 12 |
round(n,m) | round(14.77,1) | 14.8 |
sign(n)中的n 大于0返回1 小于0返回-1 等于0返回0 | signh(100) | 1 |
(4)转换函数
to_char()
select to_char (33,'l00.00') from dual;
TO_CHAR(33,'L00.00')
--------------------------------
¥33.00
SQL> select to_char (sysdate,'yyyy"年"mm"月"dd"日" HH24:MI:SS')
from dual;
TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"HH24:MI:SS')
----------------------------------------------------
2018年09月16日 13:53:40
to_date()
select to_date('20181122','yyyymmdd') from dual;
to_number()
select to_number('1122') from dual;
( 5 )混合函数
nvl(n,m) 第一个为空返回第二个,不为空返回第一个
nvl2(n, m,v) 第一个不为空返回第二个,为空返回第三个
nullif(n,m) n与m相等返回空,不相等返回n
decode() 上述已讲
5.2分组函数
AVG MIN MAX SUM COUNT GROUP BY HAVING
5.3分析函数
(1)row_number()over() 不重复排 如成绩为100 90 80 80 70 的排序为 1 2 3 4
row_number() over (partition by col1 order by col2)
根据col1分组,分组内部按col2排序
(2)rank()over() 跳跃排序 如成绩为100 90 80 80 70 的排序为 1 2 2 4
(3)dense_rank() over() 顺序排 如成绩为100 90 80 80 70 的排序为 1 2 2
举例有如下一张表grade:
sno | score |
---|---|
1 | 90 |
2 | 80 |
3 | 80 |
4 | 70 |
用法:
select sno ,row_number() over(order by score desc) from grade;
--排序结果中不存在重复的等级 成绩相等也要排出先后 1 2 3 4
select sno ,rank() over(order by score desc) from grade;
--1 2 2 4
select sno ,dense_rank() over(order by score desc) from grade;
--1 2 2 3