Oracle
dual表
Dual 是 Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select语句块中。dual表是一个单行单列的虚拟表
单行函数
字符函数
字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。
函数 | 说明 | 举例 | 结果 |
ascii(x) | 返回字符x对应的ascii码值 | select ascii ('A') from dual; | 65 |
chr(n) | 返回整数n对应的字符 | select chr (65) from dual; | A |
concat(x,y) | 连接字符串x,y 连接字段x,y | select concat('hell','world') from dual; 或 select concat(empno,ename) from emp; | hellworld |
initcap(str) | 返回字符串并将字符串的第一个字母变成大写 | select initcap(‘hello WORLD’) from dual; | Hello World |
instr(str1,str2[,s,n]) | 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; str1被搜索的字符串; str2希望搜索的字符串; s搜索的开始位置,默认为1; n第n次出现的位置,默认为1; | select instr('oracle trainin.g','ra',1,2) from dual; | 9 |
lenghh(str) | 返回字符串的长度 | select ename,length(ename) from emp; |
|
lower(str) | 返回字符串,并将所有字符小写 | select lower(ename) from emp; |
|
upper(str) | 返回字符串,并将所有字符大写 | select upper('AaBbCcDd') from dual; | AABBCCDD |
rpad(str1,x[,str2]) | 在列或字符的右边粘贴字符;x表示字符总长度 | select rpad('hello', 10, '*') from dual; | hello***** |
lpad(str1,x[,str2]) | 在列或字符的左边粘贴字符;x表示字符总长度 | select lpad('hello', 10, '*') from dual; | *****hello |
ltrim(str1 [,str2]) | 删除左边出现的字符串, 默认为空字符串 | select ltrim(' hello ') from dual; 或 select ltrim('hello world hello','hello') from dual; | hello
world hello |
rtrim(str1[,str2]) | 删除右边出现的字符串, 默认为空字符串 | select rtrim(' hello ') from dual;或 select rtrim('hello world hello','hello') from dual; | hello
hello world |
trim(str from string) | 去处string左右两边的str字符 | trim(0 from 0009872348900) | 98723489 |
substr(str,s,n) | 取子字符串,从s开始,取n个,N可省略,如果省略了参数n,那么返回从字符串的开始位置(s)到结尾。 | substr('130123456789',3,8) | 01234567 |
replace(str,st1,str2) | 用str2替换str中的str1 | replace('boll', 'l', 'd') | bodd |
数学函数
函数 | 说明 | 举例 | 结果 |
ceil(x) | 返回大于或等于x的最小整数 | ceil(3.1415) | 4 |
floor(x) | 返回小于或等于x的最大整数 | floor(3.987) | 3 |
round(x ,y) | 按照四舍五入精度进行舍入,y缺省为0 | round(124.16666,-2) round(124.16666,2) | 100 124.17 |
trunc(x,y) | 按照指定的精度截取x,y缺省为0 | trunc(124.16666,-2) trunc(124.16666,2) | 100 124.16 |
abs(x) | 返回x的绝对值 | abs(100) abs(-100) | 100 100 |
exp(x) | 返回e的x次方 | exp(2) | 7.38905609 |
ln(x) | 返回x的自然对数 | ln(2) ln(exp(2)) | 0.69314718 2 |
log(x,y) | 返回以x为底,y的对数 | Log(2,4) | 2 |
power(x,y) | 返回x的y次幂 | power(2,3) | 8 |
sqrt(x) | 返回x的平方根 | sqrt(4) | 2 |
mod(x,y) | 返回x除以y的余数 | mod(8,3) | 2 |
sign(x) | 返回x的符号,大于0返回1,小于0返回-1,等于0返回0 | sign(5)、sign(-5)、sign(0) | 1,-1,0 |
日期函数
sysdate
解释:获取当前系统日期使用sysdate
示例:
selectsysdate from dual;
add_months
语法:add_months(d,n)
参数:d日期类型,n增减的月份值
解释:返回在日期d基础上再加或减n个月后新的日期
示例:
select add_months(sysdate,2) from dual;
select hiredate,add_months(hiredate,2) from emp whereename='SMITH';
练习:
请查找最近半年入职的员工
select ename,hiredate from emp whereadd_months(hiredate,6)>=sysdate;
last_day
语法:last_day(d)
参数:d日期类型
解释:返回日期d所在月的最后一天;
示例:
select last_day(sysdate) from dual;
months_between
语法:months_between(d2,d1)
参数:d2日期类型,d1日期类型
解释:给出d2-d1的月份,共有多少个月;
示例:
select months_between('19-12月-1999','19-3月-1999') fromdual;
selectmonths_between(to_date('2000-05-20','yyyy-mm-dd'),to_date('2005-05-20','yyyy-mm-dd'))from dual;
next_day
语法:next_day(d,w)
参数:d日期,w星期
解释:给出日期d和星期w之后计算下一个星期的日期;
示例:
select next_day('26-6月-2017','星期五') next_dayfrom dual;
例子:
问题:查找已经入职8个月多的员工
select * fromemp where sysdate>=add_months(hiredate,8);
问题:显示满10年服务年限的员工的姓名和受雇日期。
select ename,hiredate from emp where sysdate>=add_months(hiredate,12*10);
问题:对于每个员工,显示其加入公司的天数。
select floor(sysdate-hiredate) "入职天数",ename from emp;
select trunc(sysdate-hiredate) "入职天数",enamefrom emp;
问题:找出各月倒数第3天受雇的所有员工。
selecthiredate,ename from emp where last_day(hiredate)-2=hiredate;
转换类型函数
to_char
语法:to_char(d,formate)
参数:d日期类型或者数值类型,formate字符格式
解释:将日期类型或者数值类型转换成指定字符串格式
示例:
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sal,'$9999.99') from emp;
特别说明:
日期格式:
yy:两位数字的年份 2004则为04
yyyy:四位数字的年份 2004年
mm:两位数字的月份 8月--08
dd:两位数字的天数 30号--30
hh24:二十四小时制8点--20
hh12:十二小时制8点--08
mi:显示分钟
ss:显示秒
day:显示星期几
数字格式:
9:显示数字,并忽略前面0
0:显示数字,如位数不足,则用0补齐
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前加美元符号
L:在数字前加本地货币符号
C:在数字前加国际货币符号
G:在指定位置显示组分隔符
D:在指定位置显示小数点符号(.)
说明:,逗号和.小数点可以合在一起使用,G分隔符和D小数点符可以合在一起使用,但逗号和点不能和GD综合使用,否则报错。
to_date
语法:to_date(str,formate)
参数:str字符类型,formate日期格式
解释:将字符串转换成日期
示例:
select to_date('2004-05-07 1:23:44','yy-mm-dd hh:mi:ss') fromdual;
to_number
语法:to_number(str)
参数:str字符类型
解释:将给出的字符转换为数字;
示例:select to_number('1999') year from dual;
例子:
问题:显示薪水的时候,把本地货币单位加在前面
select ename, to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'), to_char(sal,'L99999.99') from emp;
问题:显示1980年入职的所有员工
select * from emp whereto_char(hiredate, 'yyyy')=1980;
问题:显示所有12月份入职的员工
select * from emp whereto_char(hiredate, 'mm')=12;