（四）.函数课堂SQL

函数

函数就是一些预设了功能的代码，它们具有返回值
主要是用来简化代码的复杂性，避免多次重复编写同样功能代码

---

系统函数

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;