(02)oracle新人笔记------函数

来源：互联网发布：好看的电脑包知乎编辑：程序博客网时间：2024/06/05 15:36

一、单行函数

大小写转换函数

//dual:虚表,不能保存任何数据,只有一个字段,一行记录,当我们不希望读取任何记录又想做计算时dual表.

SELECT LOWER('ABCDEFG') FROM dual;

SELECT UPPER('abcdefg') FROM dual;

SELECT INITCAP('zhao qian') FROM dual; //首字母大写，其他小写

SELECT empno,ename,deptno FROM emp WHERE ename=UPPER('blake');

字符处理函数

SELECT CONCAT('Zhao','Qian') FROM dual ; //字符拼接

SELECT SUBSTR('aaabbbcccddd',4,3)FROM dual; //截取

SELECT LENGTH('zhao qian')FROM dual; //求长度

SELECT REPLACE('ABCABCABC','B','F') FROM dual; //替换

SELECT INSTR('ABCDABCD','D') FROM dual; //查位置

SELECT INSTR('ABCDABCD','D',5) FROM dual;

SELECT LPAD('A',5,'C') FROM dual; //字符拼接：把C拼到A左边，拼接后一共5位

SELECT RPAD('A',5,'C') FROM dual; //字符拼接：把C拼到A右边，拼接后：ACCCC

SELECT TRIM('S' FROM 'SSMITH') FROM dual; //删除

SELECT REPLACE('AAABBBCCC','A','D') FROM dual; //替换

数值函数

SELECT ROUND(4343.1253,2) FROM dual; //四舍五入取两位小数

SELECT TRUNC(4312.1253,2) FROM dual; //不四舍五入取两位小数

SELECT MOD(1600,300) FROM dual; //取余

日期

SELECT SYSDATE FROM dual; //系统当前时间

SELECTename,(SYSDATE-hiredate)/7WEEKS FROM emp ;

3、SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) months FROM emp ORDER BY

months; //月数查询

SELECT ename,ADD_MONTHS(hiredate,3) new_date FROM emp where

hiredate>'01-1月-82';

//查询82年后入职的员工转正日期，其中入职后试用期三个月

5、SELECT NEXT_DAY('06-2月-06','星期一') next_day FROM dual;

//返回在2016年2 月6号后的下一个周一是什么日期

6、SELECT LAST_DAY('02-2月-06') last_day FROM dual; //返回该月最后一天

SELECT empno,hiredate,EXTRACT(MONTH FROM hiredate) "hire month" FROM emp;

//查所有员工入职月份

7、SELECT empno,hiredate,EXTRACT(YEAR FROM hiredate) "hire year "FROM emp;

//查所有员工入职年份

转换函数

1、 TO_CHAR

SELECT ename,TO_CHAR(hiredate,'YYYY') month_hired FROM emp;

SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hired FROM emp;

SELECT ename,TO_CHAR(hiredate,'YEAR') month_hired FROM emp;

SELECT ename,TO_CHAR(hiredate,'DD MONTH YYYY') HIREDATE FROM emp;

SELECT ename,TO_CHAR(sal,'$999,999,999') SALARY FROM emp;

YYYY:完整年份 YEAR：年份英文表示 MM：用两位数字表示月份

MONTH：月份全名 DAY：星期几 DY：用三个英文字符表示星期几

2、SELECT TO_DATE('2017-05-04 11:50:00','yyyy-MM-dd hh:mi:ss') FROM dual;

3、SELECT TO_NUMBER('000012345') FROM dual;

9:一位数字 0：显示前导0 $：显示美元符号

.：显示小数点，：显示千位符 L：显示本地货币符号

通用函数

//NVL(comm,100):comm不为空，返回comm；comm为空，返回100

SELECT ename,sal,comm,(sal*12)+NVL(comm,100) FROM emp;

//comm不为空，返回sal*12+comm；comm为空，返回sal*12+100

SELECT ename,NVL2(comm,sal*12+comm,sal*12+100) FROM emp;

2、//如果一样返回空，如果不一样返回第一个

SELECT NULLIF(100+200,200+100) FROM dual;

3、//comm为空，就变0

SELECT ename,COALESCE(comm,0) comm,deptno FROM emp;

4、CASE/DECODE函数：

SELECT ename,deptno,

(CASE deptno

WHEN 10 THEN '销售部'

WHEN 20 THEN '技术部'

WHEN 30 THEN '管理部'

ELSE '无' END) deptname

FROM emp;

SELECT ename,deptno,

DECODE (deptno,

10,'销售部',

20,'技术部',

30,'管理部',

'无' ) deptname

FROM emp;

SELECT ename,sal,deptno,(CASE deptno

WHEN 10 THEN sal*1.1

WHEN 20 THEN sal*1.2

WHEN 30 THEN sal*1.3

END) NEWSAL

FROM emp

二、分组函数

查询流程：FROM---->WHERE---->GROUP BY---->HAVING---->SELECT---->ORDER BY

1、SELECT MAX(sal),MIN(sal),AVG(sal),SUM(sal) FROM emp;

2、 //除了COUNT(*),其他所有分组函数都会忽略列中的空值

SELECT COUNT(*) FROM emp WHERE deptno=30;

SELECT AVG(comm) FROM emp;

SELECT AVG(NVL(comm,0)) FROM emp;

3、SELECT COUNT(comm) FROM emp WHERE deptno=30;

4、SELECT COUNT(DISTINCT deptno) FROM emp; //去重复

5、SELECT deptno,AVG(sal) FROM emp GROUP BY deptno; //分组

SELECT deptno,job,SUM(sal) FROM emp GROUP BY deptno,job;

6、正确：SELECT deptno,MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>2900;

错误：SELECT deptno,MAX(sal) FROM emp where MAX(sal)>2900;

7、组函数嵌套：SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

练习：

SELECTSYSDATE FROM dual;
SELECTename,(SYSDATE-hiredate) FROM emp ;
SELECTNEXT_DAY('06-2月-06','星期一') next_day FROM dual;
SELECTLAST_DAY('02-2月-06') last_day FROM dual;
SELECTempno,hiredate,TRUNC(hiredate,'MONTH') FROM emp WHERE SUBSTR(hiredate,-2,2)='81';
SELECTempno,hiredate,EXTRACT(MONTH FROM hiredate) "hire month" FROM emp;
SELECTempno,hiredate,EXTRACT(YEAR FROM hiredate) "hire year "FROM emp;
SELECThiredate,MONTHS_BETWEEN('01-1月-00',hiredate),EXTRACT(MONTH FROM hiredate) FROM emp;
SELECTename,hiredate,ADD_MONTHS(hiredate,3),NEXT_DAY(hiredate,'星期日'),LAST_DAY(hiredate) FROM emp WHERE job!='MANAGER';
SELECTTO_DATE('2015-03-18 13:13:13 ','YYYY-MM-DD hh24:mi:ss') FROM dual;
SELECT ename,sal,comm,(sal*12)+NVL(comm,0) FROM emp;
SELECTename,

NVL(TO_CHAR(mgr),'No Manager')

FROM emp

WHEREMGR IS NULL;
SELECTSUM(sal),AVG(sal) FROM emp WHERE deptno=20;
SELECTMAX(sal),MIN(sal) FROM emp WHERE deptno=20;
SELECTCOUNT(deptno) FROM emp;
SELECT deptno,job,AVG(sal)

FROM emp

WHERE job IN('SALESMAN','MANAGER','CLERK')

GROUP BY deptno,job

HAVING AVG(sal)>1000

ORDER BY 3 DESC

SELECTdeptno,COUNT(empno),AVG(sal)

FROM EMP

GROUP BY deptno

HAVING COUNT(empno)>2 AND AVG(sal)>2000

ORDER BY COUNT(empno) ASC;

SELECT deptno,COUNT(empno)

FROM emp

GROUP BY deptno

HAVING COUNT(empno)>2;

SELECTMONTHS_BETWEEN(SYSDATE,'01-1月-00'),

MONTHS_BETWEEN(SYSDATE,'01-1月-00')*4

FROM dual;

SELECT* FROM emp WHERE SUBSTR(ename,3,1)='A';
SELECTTRIM('h' FROM 'hello') FROM dual;
SELECTTRIM('H' FROM 'Hello') FROM dual;
SELECTename,NVL2(comm,sal*12+comm,sal*12+100) FROM emp;
SELECTCONCAT(TO_CHAR(sal,'999,999.99'),' RNB') FROM emp;
SELECTTO_CHAR(hiredate,'MM/YYYY') FROM emp;
SELECTename,LPAD(sal,15,'$') salary FROM emp;
SELECT deptno,ROUND(MAX(sal),0),ROUND(MIN(sal),0)

FROM emp

GROUP BY deptno

HAVING COUNT(empno)>=2;

SELECT job,SUM(sal)

FROM emp

GROUP BY job

HAVING SUM(sal)>=2500 AND job NOT LIKE 'SALESMAN';

SELECTdeptno,MAX(sal)-MIN(sal) CHA_E

FROM emp

GROUP BY deptno;

阅读全文

0 0