函数
函数就是一些预设了功能的代码,它们具有返回值
主要是用来简化代码的复杂性,避免多次重复编写同样功能代码
系统函数
1.字符型
INITCAP 首字母大写
SELECT INITCAP('aaaa') FROM DUAL;
LOWER 全部小写
SELECT LOWER('AAAA') FROM DUAL;
UPPER 转换成大写
SELECT UPPER('aaaa') FROM DUAL;
TRIM 去掉左右空格
SELECT TRIM(' A AAA ') FROM DUAL;
LENGTH 返回字符串长度
SELECT LENGTH('AA AAA') FROM DUAL;
LTRIM RTRIM 左右截取
SELECT LTRIM('HELLO WORLD','HEL') FROM DUAL;
SELECT RTRIM('HELLO WORLLLLD','LD') FROM DUAL;
REPLACE 替换
SELECT REPLACE('HELLO WORLD','O','哈喽') FROM DUAL;
INSTR 查找字符第一次出现的位置
SELECT INSTR('HELLO WORLD','O') FROM DUAL;
SUBSTR 从左向右截取,第二个参数是开始截取的位置,第三个参数是截取的个数
SELECT SUBSTR('HELLO WORLD',2,6) FROM DUAL;
CONCAT 拼接字符串
SELECT CONCAT('HELLO ','WORLD!') FROM DUAL;
2.数值型
ABS 取数字绝对值
SELECT ABS(-23.5) FROM DUAL;
CEIL 向上取整
SELECT CEIL(10.00000000000000000000000001)FROM DUAL;
FLOOR 向下取整
SELECT FLOOR(10.999999999999999999999999) FROM DUAL;
POWER 求幂
SELECT POWER(8,4) FROM DUAL;
ROUND 四舍五入
SELECT ROUND(123456.123456) FROM DUAL;
SQRT 开方
SELECT SQRT(121) FROM DUAL;
3.日期
SELECT SYSDATE FROM DUAL;
MONTHS_BETWEEN 返回两个月份相差的精确的月份数
SELECT MONTHS_BETWEEN(SYSDATE,'01-5月-18') FROM DUAL;
ADD_MONTHS 返回第一个时间参数加上第二个参数数字月份的时间
SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL;
NEXT_DAY 如果第二个参数的星期几在第一个时间参数里已过,返回的则是下个星期对应的那一天
–如果没有过,则返回这个星期对应的天数
SELECT NEXT_DAY(SYSDATE,'星期五') FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期六') FROM DUAL;
LAST_DAY 返回参数时间月份的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
多行函数
–拿到多行的数据进行运算,只返回一个结果
–也叫分组函数,聚合函数
SUM 求和
SELECT SUM(SAL) FROM EMP;
AVG 求平均值
SELECT AVG(SAL) FROM EMP;
MAX,MIN 最大值与最小值
SELECT MAX(SAL),MIN(SAL) FROM EMP;
COUNT 计数 但是会忽略NULL,没有值则不会计数
SELECT COUNT(*) FROM EMP;
SELECT COUNT(COMM) FROM EMP;
注意:SUM跟AVG只能用于NUMBER类型
–MAX,MIN,COUNT能用于任何类型
SELECT SUM(SAL) 总工资,AVG(SAL) 平均工资,MAX(SAL) 最高工资,MIN(SAL) 最低工资,
COUNT(1) 总人数 FROM EMP;
SELECT MAX(ENAME) FROM EMP; --字母Z最大
SELECT MIN(ENAME) FROM EMP; --字母A最小
SELECT MAX(HIREDATE) FROM EMP; --离当前时间越近则越大
GROUP BY 分组查询
SELECT SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
注意:分组查询SELECT 后面只能放聚合函数或者分组条件
–求每个部门不同岗位的平均工资
SELECT AVG(SAL),JOB,DEPTNO FROM EMP GROUP BY JOB,DEPTNO;
–求每个部门不同岗位的平均工资大于2500
SELECT AVG(SAL),JOB,DEPTNO FROM EMP
GROUP BY JOB,DEPTNO HAVING AVG(SAL)>2500;
SELECT AVG(SAL),JOB,DEPTNO FROM EMP
GROUP BY JOB,DEPTNO HAVING AVG(SAL)>2500 ORDER BY AVG(SAL);
转换函数
TO_CHAR 转换指定格式
SELECT TO_CHAR(111.111,'9999.9999') FROM DUAL; --9,补足小数位
SELECT TO_CHAR(111.111,'0000.0000') FROM DUAL; --0,全部补足
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YY-MM-DD') FROM DUAL; --指定时间格式
TO_DATE 转换字符串为日期
SELECT '2018-10-26' FROM DUAL;
SELECT TO_DATE('2018-10-26','YY-MM-DD') FROM DUAL;
TO_NUMBER 转换字符串到数字
SELECT TO_NUMBER('1234.1234','0000.0000') FROM DUAL;
NVL 如果第一个参的值为NULL,则替换成第二个参数
SELECT EMPNO,ENAME,NVL(COMM,0) 总薪资 FROM EMP;
SELECT EMPNO,ENAME,NVL(ENAME,'小狼') 总薪资 FROM EMP;
UPDATE EMP SET ENAME=NULL WHERE EMPNO=7369;
SELECT * FROM EMP;