文章目录
Oracle函数
一、简介
- 方便数据的统计
- 处理查询结果
二、数值函数
1、四舍五入
- round(n,[,m])
- 省略m : 0
- m>0:小数点后m位
- m<0:小数点前m位
SQL> select round(23.4), round(23.45,1),round(23.45,-1) from dual;
ROUND(23.4) ROUND(23.45,1) ROUND(23.45,-1)
----------- -------------- ---------------
23 23.5 20
注意:
- n表示要进行四舍五入的值
- m表示保留小数点后几位或者前几位
2、取整函数
- ceil(n):取整最大值
- floor(n):取整最小值
SQL> select ceil(23.45),floor(23.45) from dual;
CEIL(23.45) FLOOR(23.45)
----------- ------------
24 23
3、常用计算
3.1、绝对值
- abs(n):取绝对值
SQL> select abs(23.45),abs(-23),abs(0) from dual;
ABS(23.45) ABS(-23) ABS(0)
---------- ---------- ----------
23.45 23 0
3.2、取余数
- mod(m,n):取余数
- 如果m和n中有一个值为null值,则结果返回null值
SQL> select mod(5,2) from dual;
MOD(5,2)
----------
1
SQL> select mod(5,null) from dual;
MOD(5,NULL)
-----------
3.3、求次幂
- power(m,n):求次幂
- 表示返回m的n次幂
SQL> select power(2,3),power(null,2) from dual;
POWER(2,3) POWER(NULL,2)
---------- -------------
8
3.4、求平方根
- sqrt(n):求平方根
SQL> select sqrt(16) from dual;
SQRT(16)
----------
4
4、三角函数
- sin(n):正玄
- asin(n):反正玄
- cos(n):余玄
- acos(n):反余玄
- tan(n):正切
- atan(n):反正切
SQL> select sin(3.124) from dual;
SIN(3.124)
----------
.017591746
三、字符函数
1、大小写转换函数
- upper(char):小写变大写字母
- lower(char):大写变小写字母
- initcap(char):首字母大写
SQL> select upper('abde'),lower('ADe'),initcap('asd') from dual;
UPPE LOW INI
---- --- ---
ABDE ade Asd
2、获取子字符串函数
- substr(char,[m[,n]]):截取字符串
- n可以省略,当n省略时表示从m的位置截取到字符串末尾
- m为0,表示从字符串的首字母开始截取
- m为负数,表示从字符串的尾部开始截取
SQL> select substr('abcde',2,3), substr('abced',2),substr('abced',-2,1) from dual;
SUB SUBS S
--- ---- -
bcd bced e
3、获取字符串长度函数
- length(char):获取字符串长度
SQL> select length('abc ') from dual;
LENGTH('ABC')
-------------
4
4、字符串连接函数
- concat(char1,char2)
- 与 || 操作符的作用一样
SQL> select concat('ab','dc') from dual;
CONC
----
abdc
SQL> select 'ab' || 'cd' from dual;
'AB'
----
abcd
5、去除子串函数
- trim(c2 FROM c1):表示从字符串c1中去除字符串c2
- trim(c1):去除首位的空格
SQL> select trim('a' from 'abcde') from dual;
TRIM
----
bcde
- ltrim(c1[,c2]):去除第一个a,首部开始,一个参数时去除左边空格
SQL> select ltrim('ababaa','a') from dual;
LTRIM
-----
babaa
- rtrim(c1[,c2]):去除第一个a,尾部开始,一个参数时去除右边空格
SQL> select rtrim('ababaa','a') from dual;
RTRI
----
abab
6、替换函数
- replace(char,s_string[,r_string]):省略r_string用空格替换
SQL> select replace('abcde','a','A') from dual;
REPLA
-----
Abcde
SQL> select replace('abcde','a') from dual;
REPL
----
bcde
SQL> select replace('abcde','ab','A') from dual;
REPL
----
Acde
四、日期函数
1、系统时间
- sysdate
- 默认格式:DD-MON-RR
SQL> select sysdate from dual;
SYSDATE
--------------
17-9月 -22
2、日期操作
- add_months(date,i)
- 返回在指定日期上添加月份;
- i可以是任何数;
- 如果i是小数,则截取整数部分;
- 如果i是负数,则相当于为原日期减去月份。
SQL> select add_months(sysdate,3),add_months(sysdate,-3) from dual;
ADD_MONTHS(SYS ADD_MONTHS(SYS
-------------- --------------
17-12月-22 17-6月 -22
- next_day(date,char)
- 如果char的值是’星期一’,则返回date指定日期的下周一是哪天
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDA
--------------
19-9月 -22
- last_day(date)
- 返回日期所在月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-9月 -22
- months_between(date1,date2)
- 表示两个日期之间相隔的月份
SQL> select months_between('20-5月-22','10-1月-22') from dual;
MONTHS_BETWEEN('20-5月-22','10-1月-22')
---------------------------------------
4.32258065
- extract(date FROM datetime)
- 获取当前的年月份时分秒
SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
2022
SQL> select extract(month from sysdate) from dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
9
SQL> select extract(day from sysdate) from dual;
EXTRACT(DAYFROMSYSDATE)
-----------------------
17
SQL> select extract(hour from timestamp '2022-10-6 17:22:12') from dual;
EXTRACT(HOURFROMTIMESTAMP'2022-10-617:22:12')
---------------------------------------------
17
五、转换函数
1、日期转换成字符的函数
命令
- to_char(date,[,fmt[,params]]
- date:将要转换的日期
- fmt:转换的格式
- params:日期的语言
格式
- 默认格式:DD-MON-RR
- YY YYYY YEAR:年
- MM MONTH:月
- DD DAY:日
- HH24 HH12:时
- MI SS:分
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYY
--------------------
2022-09-17 17:26:15
SQL> select to_char(sysdate,'YYYY-MM-DD HH12:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2022-09-17 05:27:10
2、字符转换成日期的函数
- to_date(char,[,fmt[,params]])
- 按照系统默认格式显示日期
- params:指定日期的语言(通常是不写的)
SQL> select to_date('2022-05-20','YYYY-MM-DD') from dual;
TO_DATE('2022-
--------------
20-5月 -22
3、数字转换成字符的函数
- to_char(number [,fmt])
- 9:显示数字并忽略前面的0
- 0:显示数字,位数不足,用0补齐
- .或D:显示小数点
- ,或G:显示千位符
- $:美元符号
- S:加正负号(前后都可以)
SQL> select to_char(12346.789,'$99,999.999') from dual;
TO_CHAR(1234
------------
$12,346.789
SQL> select to_char(12346.789,'S99,999.999') from dual;
TO_CHAR(123
-----------
+12,346.789
SQL> select to_char(12346.789,'99,999.999S') from dual;
TO_CHAR(123
-----------
12,346.789+
4、字符转换成数字的函数
- to_number(char[,fmt])
- fmt是转换的格式,可以省略
SQL> select to_number('$1,000','$9999') from dual;
TO_NUMBER('$1,000','$9999')
---------------------------
1000
六、在查询中使用函数
1、在查询中使用字符函数
- 在员工信息表查询出员工的生日
# 建表
SQL> create table users_3(id varchar2(10), name varchar2(11), cardid varchar2(18), deptno varchar2(10), regdate date, age number(4));
表已创建。
# 查询字段
SQL> desc users_3;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID VARCHAR2(10)
NAME VARCHAR2(11)
CARDID VARCHAR2(18)
DEPTNO VARCHAR2(10)
REGDATE DATE
AGE NUMBER(4)
# 插入数据
SQL> insert into users_3(id,name,cardid,deptno,regdate,age) values('1','aaa','123123123','01',sysdate,20);
已创建 1 行。
# 查询表数据
SQL> select * from users_3;
ID NAME CARDID DEPTNO REGDATE AGE
---------- ----------- ------------------ ---------- -------------- ----------
1 aaa 123123123 01 19-9月 -22 20
# 查询员工生日
SQL> select substr(cardid,7,8) from users_3;
SUBSTR(CARDID,7,
----------------
123
SQL>
- 将部门号01全部替换成 ‘信息技术’
SQL> select replace(deptno,'01','信息技术') from users_3;
REPLACE(DEPTNO,'01','信息技术')
--------------------------------------------------------------------------------
信息技术
2、在查询中使用数值函数
- 取得员工入职的年份
SQL> select extract(year from regdate) from users_3;
EXTRACT(YEARFROMREGDATE)
------------------------
2022
- 将员工信息表中的年龄字段与10取余数
SQL> select mod(age,10) from users_3;
MOD(AGE,10)
-----------
0
SQL> update users_3 set age = 25;
已更新 1 行。
SQL> select mod (age,10) from users_3;
MOD(AGE,10)
-----------
5
3、在查询中使用日期函数
- 查询出9月份入职的员工信息
SQL> select extract(year from regdate) from users_3;
EXTRACT(YEARFROMREGDATE)
------------------------
2022
SQL> select * from users_3 where extract(month from regdate)=9;
ID NAME CARDID DEPTNO REGDATE AGE
---------- ----------- ------------------ ---------- -------------- ----------
1 aaa 123123123 01 19-9月 -22 25