多行函数（或者叫聚合函数，组函数）
select count(ename) from emp; -- count：计算个数
select max(sal) from emp; -- max：计算最大值
select min(sal) from emp; -- min：计算最小值
select sum(sal) from emp; -- sum：计算和
select avg(sal) from emp; -- avg：计算平均值多行函数（组函数）不统计null值，解决方案：
对于
COUNT()，如果你想要包含NULL值，可以使用COUNT(*)，它会计算所有行，包括NULL。
- 优先选择不包含null的列进行计算
- mysql：IFNULL函数
select sum(comm) from emp; -- 2200
select count(comm) from emp; -- 4
select avg(comm) from emp; -- 550
select sum(IFNULL(comm,0)) from emp; -- 157.142857- oracle：nvl/nvl2函数
select avg(nvl(comm,0)) from emp; -- 157.142857单行函数:
① 字符函数
concat 字符串拼接
-- mysql和oracle都支持
select concat(ename,sal) from emp;
-- mysql 单引号 和双引号 都支持，oracle必须是单引号；mysql和oracle都支持伪表dual
select concat('hello','world') from dual;
select concat("hello","world") from dual;
select 'hello' || 'world' from dual; -- oracle中支持lower / upper / initcap
-- mysql不支持initcap
select 'Hello World' 原始, lower('Hello World') 小写,upper('Hello World') 大写, initcap('hello world') 首字母大写 from dual ;
substr 字符串截取
substr(str,begin):将str从begin开始截取substr(str,begin,len):将str从begin开始截取，截取的长度是len
select substr('Hello world',3) from dual ; -- llo world MySql:从0开始数
select substr('Hello world',3,3) from dual ; -- lloOracle/Sql Server :从1开始数
length / lengthb
获取字符个数、字节数
- length :求取字符个数
- lengthb:获取字节数，一个中文占2/3个字节数。
- utf-8：一个中文占3个字节数。
- gbk:一个中文占2个字节数。
-- mysql不支持lengthb
select * from nls_database_parameters ; -- 查看NLS_CHARACTERSET字段值（查看oracle字符集）
select length('Hello world') 字符数, lengthb('Hello world') 字节数 from dual ;
select length('西安') 字符数, lengthb('西安') 字节数 from dual ;instr 查找字符串
instr(a,b) :在a中，查找b的位置(从1开始数)。如果不存在，返回0 ;
select instr('hello world','ll') from dual ; -- 3
select instr('hello world','llw') from dual ; -- 0lpad左填充、rpad右填充
select lpad('hello',10,'*') 左填充, rpad('hello',10,'*') 右填充 from dual ;trim / replace 替换
-
trim():去掉左右两端空格 -
trim('X' from str)去掉str左右两段的X
select trim('X' from 'XXhello worldXXX') from dual ;select replace('Hello world','l','*') from dual ; -- 将l替换成*② 数值函数
a. round(数字,n) :对数字的第n位小数进行四舍五入。
select round(67.183,2) 一, round(67.183,1) 二, round(67.183,0) 三,round(67.183,-1) from dual ;
-- 可以是负数
b. mod()：求余
select mod(124,3) from dual ;
c. trunc（数字,n）：截取（舍尾）
-- mysql不支持trunc
select trunc(67.183,2) 一,trunc(67.183,1) 二, trunc(67.183,0) 三, trunc(67.183,-1) from dual ;③ 通用函数
nullif(a,b) :如果a=b，则返回null;否则返回a
select nullif('hello','hello') from dual ; -- null
select nullif('hello','world') from dual ; -- hellocoalesce :从左往右，寻找第一个不为null的值
select coalesce(null,null,'c') from dual ;条件判断函数
- (1) decode(字段,条件1,表达式1,条件2,表达式2,...,条件n,表达式n,其他)
select ename, sal 涨前,job,decode(job,'PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 涨后 from emp;- (2) case表达式(case...end)
搜索CASE
select ename, sal 涨前,job, case job
when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp ;简单CASE表达式（标准CASE表达式）
SELECT ename, sal,
CASE
WHEN job = 'PRESIDENT' THEN sal + 1000
WHEN job = 'MANAGER' THEN sal + 800
ELSE sal + 400
END AS '涨后'
FROM emp;④ 转换函数
- 数字/日期<-->字符串
TO_CHAR和TO_DATE用于在日期和字符串之间转换，TO_NUMBER用于将字符串转换为数字。
⑤ oracle的日期函数
- a.关键字sysdate：获取当前时间
- select sysdate from dual ;
- b.格式化时间 to_char(时间,格式)
- select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
- c.日期 可以+/-数字
- select (sysdate-1) 昨天,sysdate 今天,sysdate+1 明天 from dual ;
- d.日期只能-日期，不能+日期
- select sysdate-hiredate 工作天数 from emp ;
- e. months_between(日期1,日期2) :相差的月数。日期1- 日期2
- select months_between(sysdate,hiredate) 工作月数 from emp ;
- f: add_months(时间,月数):增加月份
- select add_months(sysdate,3) from dual ;
- g. last_day（时间） :当月的最后一天
- select last_day(sysdate) from dual ;
- i. next_day(时间,'星期N'):下一个星期N是哪一天
- select next_day(sysdate,'星期五') from dual ;
- j. 四舍五入
- select round(sysdate,'month') 月,round(sysdate,'year') from dual ;
- h.截取（舍尾）
- select trunc(sysdate,'month') 月,trunc(sysdate,'year') from dual ;
- 日期格式
- yy:两位数字的年份 2004--04
- yyyy:四位数字的年份 2004年
- mm:两位数字的月份 8月--08
- dd:两位数字的天数 30号--30
- hh24:二十四小时制8点--20
- hh12:十二小时制8点--08
- mi,ss--显示分钟\秒
- day显示星期几
- month显示几月
- year显示年
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
