单行函数:输入几条记录就输出几条记录
多行函数(组函数):输出为一条记录
单行函数:
lower 转换成小写 select lower(ename) from emp;
upper转换成大写 select upper('zheng')from dual;
substr 取子串 select substr(dname, 1, 3) from dept;
chr ascii码à字符 select chr(65) from dual;
ascii 字符àascii码 select ascii('A') from dual; select ascii('ABC') from dual;
round 四舍五入 select round(33.6) from dual; select round(33.4646, 2)from dual;
select round(433.4646, -2) from dual;
to_char 控制数字格式 数字/日期à字符串 9---1位数字 若无不显示
0---1位数字若无显示0
L---¥
$---$
select to_char(12345.12345, '999,999,999.000000') fromdual;
select to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') fromemp;
to_date 字符串转换成日期
select to_date('1999-01-0113:34:34', 'YYYY-MM-DD HH24:MI:SS') from dual;
to_number 字符串转换成数字
select to_number('$1,250.00', '$9,999.99') from dual;
nvl 对null的处理
nvl(comm,0) 若comm为null,则返回0,若comm不为null,则返回comm的值
select ename, comm, nvl(comm,0) from emp;
多行函数:
max、min、avg、sum、count
select count(*) from emp;
select count(comm) from emp; 空值不计数
select count(distinct deptno) from emp;