03-Oracle学习_常用函数

旧城等待， 2021-11-04 15:14 292阅读 0赞

常用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