1.大小写转换函数
LOWER()、UPPER()、INITCAP()(单词首字母大写转换)
select empno, ename, deptno
FROM emp
WHERE ename = UPPER('allen');
2.字符操作函数
CONCAT('Good', 'String') --- GoodString
SUBSTR('String',1,3) --- Str
LENGTH('String') --- 6
INSTR('String', 'r') --- 3(查找字符位置)
LPAD(sal,10,'*') --- ******5000(左边填充)
TRIM('S' FROM 'SSMITH') --- MITH(修剪)
3.数字函数
ROUND():对指定的值进行四舍五入
TRUNC():对指定的值进行截取
MOD():返回触发计算后的余数
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)
FROM DUAL;
// 效果
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
SELECT TRUNC(45.923,2), TRUNC(45.923,0), TRUNC(45.923,-1)
FROM DUAL;
// 效果
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
SELECT ename, sal, comm, MOD(sal, comm)
FROM emp
WHERE job = 'SALESMAN';
4.日期函数
SYSDATE函数返回当前日期
SELECT ename, (SYSDATE-hiredate)/7 WEEKS
FROM emp
WHERE deptno = 10;
MONTHS_BETWEEN:两个日期之间的月数
ADD_MONTHS:为一个日期增加月份
NEXT_DAY:一个日期的下一个指定日子(例如 : 星期1 ) 的日期
LAST_DAY:某个月份的最后一天
ROUND:对日期进行四舍五入计算
TRUNC:对日期进行取整计算
5.类型转换函数
隐式
SELECT 5+'2' FROM dual;
SELECT sysdate+'2' FROM dual;
SELECT ename||2016 FROM emp;
TO_CHAR(date, 'fmt')日期转字符串
SELECT ename, TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE
FROM emp;
TO_CHAR(number, 'fmt')数字转字符串
// 9表示一个数字值
SELECT TO_CHAR(sal,'$99,999') SALARY
FROM emp
WHERE ename = 'MILLER';
TO_NUMBER 将一个字符串转化为数字
TO_DATE 将一个字符串转化为日期
6.NULL值
如果数学表达式中包含了一个空值 , 那么计算的结果为空值
在升序排序时,NULL值排在最后,在降序排序时,NULL值排在最前
NULLS FIRST :指定空值显示在查询结果的开始处
NULLS LAST :指定空值显示在查询结果的结尾处
IS NULL 操作符:检查有无空值
SELECT ename, mgr
FROM emp
WHERE mgr IS NULL;
AND操作符、OR 操作符、NOT操作符(优先级:NOT>AND>OR) ,用 圆括号 可以改变操作符的优先级次序
SELECT ename, job, sal
FROM emp
WHERE job='SALESMAN' OR job='PRESIDENT' AND sal>1500;
7.NVL 函数 、NVL2 函数 和 NULLIF 函数
NVL( expr1 , expr2 )
如果 expr1 为 null , 则返回 expr2, 否则返回 expr1
可以使用的数据类型是数字 、 日期 、 字符型,数据类型必须能够匹配
NVL(comm,0)
NVL(hiredate,'01-JAN-11')
NVL(job,'No Job Yet')
SELECT ename, sal, comm, (sal*12)+NVL(comm,0)
FROM emp;
NVL2 (expr1, expr2, expr3)
如果表达式1不为空值NULL,返回值为表达式2的值,如果表达式1为空值NULL,返回值为表达式3的值
表达式2和3是除 LONG 以外的任何数据类型,表达式1可以是任何数据类型
NULLIF (expr1, expr2)
如果两个表达式相等就返回空值 NULL,否则就返回表达式 1
8.DECODE 函数 、CASE 表达式、COALESCE 函数
DECODE 函数 、CASE 表达式:具有与 CASE 表达式或 IF-THEN-ELSE 语句相同的功效
SELECT job, sal,DECODE(job, 'ANALYST', SAL*1.1,
'CLERK', SAL*1.15,
'MANAGER', SAL*1.20, SAL) REVISED_SALARY
FROM emp;
SELECT CASE JOB WHEN 'CLERK' THEN 'CLERK'
WHEN 'SALESMAN' THEN 'SALESMAN'
ELSE 'OTHERS'
END
FROM EMP;
COALESCE( 表达式 1, 表达式 2, 表达式 3, …, 表达式 n)
如果第一个表达式不为空,则COALESCE函数返回该表达式 ;否则,它将对其余的表达式执行COALESCE运算
9.分组函数
AVG、COUNT、MAX、MIN、SUM
SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp
WHERE job LIKE 'SALES%';
// 返回查询出的总行数
SELECT COUNT(*)
FROM emp
WHERE deptno = 30;
// 返回expr值非空的行数
SELECT COUNT(comm)
FROM emp
WHERE deptno = 30;
分组函数会忽略数据行中的全部空值,不想忽略可以和 NVL 函数结合
SELECT AVG(NVL(comm,0))
FROM emp;
创建分组数据GROUP BY
GROUP BY后面的列可以不在SELECT列表中
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
SELECT deptno, job, sum(sal)
ROM emp
GROUP BY deptno, job;
用HAVING子句排除分组结果
SELECT deptno, max(sal)
FROM emp
GROUP BY deptno
HAVING max(sal)>2900;
分组函数也可以嵌套使用