目录
一、概念
什么是内置对象:内置对象就是系统中所自带的函数。
内置函数基本都在查询语句中
作用:更快的实现对数据需求的操作
二、内置函数以及使用
1.转换函数(to_number to_char to_date)
(1)to_number 将内容转换成数值型
select to_number('123')+123 from dual;
--伪表
select '123'+123 from dual;
select 123 || '123' from dual;
(2)to_char 将内容转换成字符串型
select to_char(123) from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
转换函数中提供的日期格式如果含有中文必须使用双引号括起来,否则会提示格式错误
格式:select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
to_date 将内容转换成日期格式 例:
select to_date('2010-1-1','yyyy-mm-dd') from dual;
三、日期函数
获取当前的系统时间:
select sysdate from dual;
示例:
12-2月 -22 03.07.52.088000 下午 +08:00
select systimestamp from dual;
add_months(date,number) 增加|减去指定的月份 ***
select add_months(sysdate,2) from dual;
select add_months(sysdate,-1) from dual;
select add_months(to_date('2010-1-1','yyyy-mm-dd'),10) from dual;
1. months_between(date,date) 求月份差 ***
select months_between(sysdate,to_date('2020-1-1','yyyy-mm-dd')) from dual;
2. extract(xx from date) 1 2 求指定日期的年份|月份|日 ***
select extract(day from sysdate) from dual;12
select extract(month from sysdate) from dual;2
select extract(year from sysdate) from dual;2022
3. last_day(date) 求当前提供的日期的这个月份的最后一天
select last_day(sysdate) from dual;
4. next_day(date,week) 求当前日期后指定的星期几的那一天。
select next_day(sysdate,'星期日') from dual;
select next_day(sysdate,'星期四') from dual;
5. round(date) 四舍五入
select round(sysdate) from dual;
select round(to_date('2022-2-12 08:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
6. trunc(date) 截断
select trunc(to_date('2022-2-12 08:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
select trunc(to_date('2022-2-12 23:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
案例:
查询emp员工信息表 求出2月份呢入职的人有那些人?
select * from emp where extract(month from hiredate) = 2;
求每个月份入职的人数?????????
select extract(month from hiredate) as 月份,count(*) as 人数 from emp
group by extract(month from hiredate)
四、字符串函数
1. initcap(varchar2) 将字符串的首字母转换成大写
select initcap('helloworld') from dual;
2. lower(varchar2) 转换小写
select lower('HELLO world') from dual;
3. upper(varchar2) 转换大写
select upper('hello world My') from dual;
4. ltrim/rtrim(varchar2,varchar2) 去除左右两端的空格
select length(' hello ') from dual;
select ltrim(' hello ') from dual;
select length(ltrim(' hello ')) from dual;
select rtrim(' hello ') from dual;
5. replace(varchar2,varchar2,varchar2) 替换函数
select replace('hello','e','abc') from dual;
6. instr(varchar2,varchar2[,number])在目标字符串中查找指定字符的下标位置
select instr('lkashdkjsaldjlksajdl','l') from dual;
从指定位置在目标字符串中查找指定字符的下标位置。
select instr('lkashdkjsaldjlksajdl','l',2) from dual;
7. substr(varchar2,number[,number]) 截取
从指定位置开始截取,就去到后面几位数。
select substr('zz is a nice man',3,6) from dual;
select substr('akshfdsahkjfsa',6) from dual;
8. concat(varchar2,varchar2) || 拼接
select 'a'||'b' from dual;
select concat('b','nb') from dual;
9. ascii(varchar2) 将一个字符转换成对应的ASCII值
a-97 A-65 0-48
select ascii('a') from dual;
10. chr(number) 将一个ascii值转换成对应的字符
select chr(97) from dual;a
11. lpad/rpad(varchar2,number,varchar2) 左右填充指定的内容
select lpad('hello',20,'a') from dual
select rpad('hello',20,'a') from dual
12. length(varchar2) 长度(字符)
select length('宋军好hello') from dual;
13. decode(自定义) java的if语句
select decode('sb','nb','优秀','sb','屌丝','xb','大逗比') from dual;
案例:
查询emp表,工资大于3000 标注大富豪
工资等于3000 标注 小富豪
工资低于3000 标注 回家种田
select ename,sal,
decode(sign(sal-3000),'1','大富豪','0','小富豪','-1','回家种田') as 备注
from emp;
五、数学函数
1.sign 判断正负数
select sign(100) from dual;1
select sign(0) from dual; 0
select sign(-100) from dual; -1
2. abs(number)绝对值
select abs(-10) from dual;
3. ceil(number)向上取整
select ceil(12.00001) from dual;
4. floor(number)向下取整
select floor(12.999999)from dual;
5. round(number,number)四舍五入
select round(12.678,2) from dual;
6. trunc(number,number)截断小数位
select trunc(12.678,2) from dual;
7. sign(number)判断正负数
select sign(10) from dual;
8. sqrt(number)开平方根
select sqrt(25) from dual;
9. mod(number,number)取余
select mod(10,3) from dual;
10. power(number,number)求幂
select power(2,3) from dual;
select power(2,10) from dual;
六、小结
常用内置函数:
1.转换函数:to_number to_date to_char
2.日期函数:sysdate,add_months,months_between,extract
last_day,next_day,round,trunc
3.字符串函数: instr,substr,replace,decode
ltrim,rtrim,lpad,rpad,lower,upper,ascii,chr,length
4.数学函数 sgin,mod
abs,ceil,floor,sqrt,power
内容(数据形式)小结:
1.转换函数 将字符串的首字母转换成大写
to_char() 数值转内容
to_number() 内容转数值
to_date() 内容转日期
2.日期函数
sysdate/systimestamp 获取系统时间
add_months(date,number) 增加月份
months_between(date,date) 月份差
extract(xx from date) 1 2 求出当前年/月/日
last_day(date) 本月的最后一天
next_day(date,week) 下一个星期几
round(date) 四舍五入
trunc(date) 截断
3.字符串函数
initcap(varchar2)
lower(varchar2) 小写
upper(varchar2) 大写
ltrim/rtrim(varchar2,varchar2) 去除左右空格
replace(varchar2,varchar2,varchar2) 替换
instr(varchar2,varchar2[,number]) 找到目标位置
substr(varchar2,number[,number]) 截取
concat(varchar2,varchar2) 拼接
ascii(varchar2) 将一个字符转换成对应的ASCII值
chr(number) 将一个数值转换成对应ASCll的字符
lpad/rpad(varchar2,number,varchar2) 左右自动填充
length(varchar2) 长度
decode(自定义) 多层判断
4.数学函数
abs(number) 绝对值
ceil(number) 向上取整
floor(number) 向下取整
round(number,number) 四舍五入
trunc(number,number) 取指定的小数位数
sign(number) 判断正数负数和0
sqrt(number) 开平方根
mod(number,number) 取余
power(number,number) 求幂
七、综合案例
--Oracle-03 内置函数
--内置函数基本都在查询语句中
#1.转换函数(to_number to_char to_date)
--(1)to_number 将内容转换成数值型
select '123'+123 from dual;
select to_number('123')+123 from dual;
select 123 || '123' from dual;
---(2)to_char 将内容转换成字符串型
select to_char(123) from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--转换函数中提供的日期格式如果含有中文必须使用双引号括起来,否则会提示格式错误
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
--to_date 将内容转换成日期格式
select to_date('2010-1-1','yyyy-mm-dd') from dual;
#2.日期函数
--获取当前的系统时间 ******************************
select sysdate from dual;
--12-2月 -22 03.07.52.088000 下午 +08:00
select systimestamp from dual;
--add_months(date,number) 增加|减去指定的月份 ***
select add_months(sysdate,2) from dual;
select add_months(sysdate,-1) from dual;
select add_months(to_date('2010-1-1','yyyy-mm-dd'),10) from dual;
-- months_between(date,date) 求月份差 ***
select months_between(sysdate,to_date('2020-1-1','yyyy-mm-dd')) from dual;
--extract(xx from date) 1 2 求指定日期的年份|月份|日 ***
select extract(day from sysdate) from dual;--12
select extract(month from sysdate) from dual;--2
select extract(year from sysdate) from dual;--2022
--last_day(date) 求当前提供的日期的这个月份的最后一天
select last_day(sysdate) from dual;
--next_day(date,week) 求当前日期后指定的星期几的那一天。
select next_day(sysdate,'星期日') from dual;
select next_day(sysdate,'星期四') from dual;
--round(date) 四舍五入
select round(sysdate) from dual;
select round(to_date('2022-2-12 08:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
--trunc(date) 截断
select trunc(to_date('2022-2-12 08:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
select trunc(to_date('2022-2-12 23:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;
--查询emp员工信息表 求出2月份呢入职的人有那些人?
select * from emp where extract(month from hiredate) = 2;
--求每个月份入职的人数?????????
select extract(month from hiredate) as 月份,count(*) as 人数 from emp
group by extract(month from hiredate)
#3.字符串函数
--initcap(varchar2) 将字符串的首字母转换成大写
select initcap('helloworld') from dual;
-- lower(varchar2) 转换小写
select lower('HELLO world') from dual;
-- upper(varchar2) 转换大写
select upper('hello world My') from dual;
--ltrim/rtrim(varchar2,varchar2) 去除左右两端的空格
select length(' hello ') from dual;
select ltrim(' hello ') from dual;
select length(ltrim(' hello ')) from dual;
select rtrim(' hello ') from dual;
-- replace(varchar2,varchar2,varchar2) 替换函数
select replace('hello','e','abc') from dual;
--instr(varchar2,varchar2[,number])在目标字符串中查找指定字符的下标位置
select instr('lkashdkjsaldjlksajdl','l') from dual;
--从指定位置在目标字符串中查找指定字符的下标位置。
select instr('lkashdkjsaldjlksajdl','l',2) from dual;
--substr(varchar2,number[,number]) 截取
--从指定位置开始截取,就去到后面几位数。
select substr('zz is a nice man',3,6) from dual;
select substr('akshfdsahkjfsa',6) from dual;
--concat(varchar2,varchar2) || 拼接
select 'a'||'b' from dual;
select concat('b','nb') from dual;
-- ascii(varchar2) 将一个字符转换成对应的ASCII值
--a-97 A-65 0-48
select ascii('a') from dual;
--chr(number) 将一个ascii值转换成对应的字符
select chr(97) from dual;--a
-- lpad/rpad(varchar2,number,varchar2) 左右填充指定的内容
select lpad('hello',20,'a') from dual
select rpad('hello',20,'a') from dual
--length(varchar2) 长度(字符)
select length('宋军好hello') from dual;
--decode(自定义) java的if语句
select decode('sb','nb','优秀','sb','屌丝','xb','大逗比') from dual;
--查询emp表,工资大于3000 标注大富豪
--工资等于3000 标注 小富豪
--工资低于3000 标注 回家种田
select ename,sal,
decode(sign(sal-3000),'1','大富豪','0','小富豪','-1','回家种田') as 备注
from emp;
#4.数学函数
--sign 判断正负数
select sign(100) from dual;--1
select sign(0) from dual;-- 0
select sign(-100) from dual;-- -1
--abs(number)绝对值
select abs(-10) from dual;
--ceil(number)向上取整
select ceil(12.00001) from dual;
--floor(number)向下取整
select floor(12.999999)from dual;
--round(number,number)四舍五入
select round(12.678,2) from dual;
--trunc(number,number)截断小数位
select trunc(12.678,2) from dual;
--sign(number)判断正负数
select sign(10) from dual;
--sqrt(number)开平方根
select sqrt(25) from dual;
--mod(number,number)取余
select mod(10,3) from dual;
--power(number,number)求幂
select power(2,3) from dual;
select power(2,10) from dual;