day8-oracle总结

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;