一、单行函数
大小写转换函数
//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;