常用SQL函数
单行函数: 有多少条记录就有多少个输出
多行函数: 也叫组函数, 一组记录只产生一个输出

1, 单行函数

① lower, 字符串转小写

SQL> select lower(ename) from emp;
SQL> select ename from emp
2 where ename like '_a%' or ename like '_A%';
SQL> select ename from emp
2 where lower(ename) like '_a%';

② upper

SQL> select upper('abc') from dual;

③ substr(字符串, 起始位置, 截取字符数), 从1开始

SQL> select substr('123456', 2, 3) from dual;
SUBSTR
------
234
SQL> select substr(ename, 2, 3) from emp;

④ chr(ASCII码), 转换成对应ASCII对应的字符

SQL> select chr(65) from dual;
CH
--
A
SQL> select chr(65+32) from dual;
CH
--
a

⑤ ascii(字符), 将字符转换成对应的ASCII码

SQL> select ascii('A') from dual;
ASCII('A')
----------
65

⑥ round(浮点数 [, 四舍五入到哪一位]), 默认为0

SQL> select round(1234.56789) from dual;
ROUND(1234.56789)
-----------------
1235
SQL> select round(1234.56789, 2) from dual;
ROUND(1234.56789,2)
-------------------
1234.57
SQL> select round(1234.56789, -1) from dual;
ROUND(1234.56789,-1)
--------------------
1230

⑦ A. to_char(数字, '格式') 格式化数字,

以指定格式输出字符串; L,本地货币符
SQL> select to_char(sal, '$99,999.9999') from emp order by sal;
TO_CHAR(SAL,'$99,999.9999'
--------------------------
$950.0000
$1,210.0000
$1,250.0000
SQL> select to_char(sal, 'L99,999.9999') from emp order by sal;
TO_CHAR(SAL,'L99,999.9999')
--------------------------------------------
￥950.0000
￥1,210.0000
SQL> select to_char(sal, 'L00,000.0000') from emp order by sal;
TO_CHAR(SAL,'L00,000.0000')
--------------------------------------------
￥00,950.0000
￥01,210.0000

B. to_char(日期, '格式')

SQL> select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS')
--------------------------------------
2013-08-10 08:18:31
SQL> select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2013-08-10 20:17:53

⑧ to_date(字符串, '格式'), 将字符串转换成指定格式的日期

SQL> select ename, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') from emp
2 where hiredate > to_date('1981-12-03 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH24:MI:SS
-------------------- --------------------------------------
SCOTT 1987-04-19 00:00:00

⑨ to_number(带货币符的字符串, '格式')

SQL> select sal from emp
2 where sal > to_number('$1,234.567', '$999,999.999');
SAL
----------
1250
2975

⑩ nvl(参数, 0), 如果参数为NULL, 则返回0

SQL> select ename, sal*12 + comm from emp;
ENAME SAL*12+COMM
-------------------- -----------
BLAKE
CLARK
SCOTT
KING
SQL> select ename, sal*12 + nvl(comm, 0) from emp;
ENAME SAL*12+NVL(COMM,0)
-------------------- ------------------
CLARK 29400
SCOTT 39600
KING 60000

2, 组函数

① max, min

-- 最大薪水
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
-- 最小薪水
SQL> select min(sal) from emp;
MIN(SAL)
----------
950

② avg, sum

-- 格式化平均薪水
SQL> select to_char(avg(sal), 'L999,999.999') from emp;
TO_CHAR(AVG(SAL),'L999,999.999')
--------------------------------------------
￥2,252.917
-- 四舍五入平均薪水
SQL> select round(to_char(avg(sal), '99999.999'), 2) avg_sal from emp;
AVG_SAL
----------
2252.92
-- 公司每个月总共需要支付给员工多少钱
SQL> select sum(sal) from emp;
SUM(SAL)
----------
27035

③ count

-- 总共有多少条记录
SQL> select count(*) from emp;
COUNT(*)
----------
13
-- 不是NULL 则算一个
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
-- 去重复
SQL> select count(distinct deptno) from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3

秒客网

03-Oracle学习_常用函数