5单行函数
<h1>单行函数知识</h1>
<h2>单行函数分类</h2>
<pre><code>/*
SQL 提供了一些常用的内置函数，当然你也可以自己定义 SQL 函数。
1、算术函数
2、字符串函数
3、日期函数
4、转换函数
5、通用函数
*/</code></pre>
<h3>网上找到的知识</h3>
<pre><code>-- 算术函数
SELECT ABS(-2) 绝对值 FROM dual ; --运行结果为 2。
SELECT MOD(101,3) 取余 FROM dual;--运行结果 2。
SELECT ROUND(37.25,1) 四舍五入小数位数 FROM dual ;--运行结果 37.3。、
-- 字符串函数
SELECT CONCAT('abc', 123) 串连起来 FROM dual ;--运行结果为 abc123。
SELECT LENGTH('你好') 字符长度 FROM dual; --运行结果应该为6，但为2，一个汉字三个长度，可能与库类型有关
SELECT CHAR_LENGTH('你好') FROM dual; --运行失败，其他库运行结果可能为2。
SELECT LOWER('ABC') 转为小写 FROM dual ;-- 运行结果为 abc。
SELECT UPPER('abc')转为大写 FROM dual ;--运行结果 ABC。
SELECT REPLACE('fabcd', 'abc', 123) 替换 FROM dual ;--运行结果为 f123d。
SELECT SUBSTRING('fabcd', 1,3) 截取 FROM dual ;--此方法不适用oracle,运行结果为 fab。
SELECT substr ('fabcd',1,3) FROM dual ;--oracle要用此方法
-- 时间函数
SELECT CURRENT_DATE() FROM dual ;--取系统时间，不适用oracle
SELECT SYSDATE FROM dual ;--这个才适用</code></pre>
<h3>李兴华oracel学习</h3>
<h4>字符串函数</h4>
<pre><code>SELECT UPPER('abc') FROM dual ;
SELECT LOWER('ABC') FROM dual ;
SELECT INITCAP('abc') FROM dual ;
SELECT INITCAP('ABC') FROM dual ;
SELECT REPLACE('abcde','e','123456') FROM dual ;
SELECT LENGTH('你好') FROM dual ;
SELECT SUBSTR('abcdefgh',2,3) FROM dual ;--不是从0开始的
SELECT ASCII('Q') FROM dual;--返回ASCII码
SELECT CHR('13') FROM dual;--返回反ASCII码
SELECT RPAD('abc',6,'def') FROM dual;--在右填充指定长度字符串，这里的长度是填充后的
SELECT LPAD('def',6,'abc') FROM dual;--在左填充指定长度字符串，这里的长度是填充后的
SELECT LTRIM(' abc') FROM dual;--去掉左边空格
SELECT RTRIM('abc ') FROM dual;--去掉右边空格
SELECT TRIM(' abc ') FROM dual;--去掉左右两边空格
SELECT INSTR('abcdefaade','de',1,2) FROM dual ;--查找一个子字符串是否在指定的位置开始找，第几次出现的位置，（源字符串，查到字符串，开始查找位置，第几次出现的位置）
--字符串函数使用，可以在select\where语句中使用
SELECT * FROM emp ;
SELECT * FROM emp WHERE ename = UPPER('smith');
SELECT ename 原始姓名,INITCAP(ename) 首字符大写姓名 FROM emp ;
SELECT ename 原始姓名,REPLACE(ename,'A','_') 替换后姓名 FROM emp ;
SELECT * FROM emp WHERE LENGTH(ename)=5 ;
SELECT * FROM emp WHERE SUBSTR(ename,1,3)='JAM'
SELECT ename,SUBSTR(ename,2) FROM emp ;
SELECT ename 原姓名,SUBSTR(ename,3) FROM emp WHERE deptno=10;
SELECT ename 原姓名,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;--包括当前位置截取
SELECT ename 原姓名,SUBSTR(ename,-3) FROM emp;--倒数第三个开始
SELECT ASCII('L') FROM dual;--返回ASCII码
SELECT CHR(76) FROM dual ;--返回返ASCII码
SELECT LPAD('MLDN',10,'*') 左加,RPAD('MLDN',10,'*') 右加,LPAD(RPAD('MLDN',10,'*'),16,'*') 两边加 FROM dual ;--左右补充东西</code></pre>
<h4>数值函数</h4>
<pre><code>SELECT ROUND(1.4455,2) FROM dual;--对小数四舍五入，保留多少位小数，第二参可以不要
SELECT TRUNC(1.925,2) FROM dual ;--对小数指定保留位数，不做四舍五入处理
SELECT MOD(10,4) FROM dual;--对两数取余，或者叫取模
--一些实例
SELECT ROUND(784.52) 不保留小数,ROUND(784.645,2) 保留两位小数,ROUND(785.52,-1) 对整数四舍五入 FROM dual ;--不保留小数：对第一位小数处理；保留两位小数：对第三位小数处理；负数：对个位数进行处理。
--算出员工平均日薪，不需要太多的小数
SELECT * FROM emp
SELECT empno 工号,ename 姓名,job 岗位,mgr 上司工号,hiredate 入职时间,deptno 部门编号,sal 月薪,comm 奖金,ROUND((sal+nvl(comm ,0))/30,2) 平均日薪 FROM emp --注意日薪的计算方法
SELECT TRUNC(789.24) 截小数,TRUNC(789.24,1)截留1位小数,TRUNC(789.24,-2) 取整 FROM dual;--注意取负是从个位天始算，相当于取整百
SELECT MOD(10,3) FROM dual;--取模，取余</code></pre>
<h4>日期函数（内容比较多）</h4>
<pre><code>SELECT SYSDATE FROM dual;--取得系统当前时间
SELECT SYSTIMESTAMP FROM dual;--这样才能精确到毫秒
SELECT nls_date_format FROM SESSION ;--这样查询表名无效
ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';--这样修改显示时间的格式
/*对时间操作有三个公式
日期-数字=日期
日期+数字=日期
日期-日期=数字
*/
SELECT SYSDATE-3 三天前的日期,SYSDATE+3 三天后的日期 FROM dual;--三天前，三天后
SELECT empno 雇员编号,ename 雇员名称, hiredate 雇用日期,TRUNC(SYSDATE-hiredate) 到今天雇用天数,TRUNC((SYSDATE-10)-hiredate) 十天前的雇用天数 FROM emp ;
SELECT SYSDATE-to_date('1994/05/13','yyyy/mm/dd') FROM dual ;--我出生了这么多天了
--系统给定的函数
SELECT ADD_months(SYSDATE,12*60) FROM dual ;--为指定日期加上指定月数
SELECT next_day(SYSDATE,'星期日') FROM dual;--下一个日期天
SELECT SYSDATE 今天日期,last_day(SYSDATE) 当前日期所在月最后一天日期 FROM dual;--当月最后一天日期
SELECT months_between(SYSDATE,to_date('1994/05/13','yyyy/mm/dd')) FROM dual;--两个时间之间间隔的月份
SELECT add_months(SYSDATE,3) 三个月后,add_months(SYSDATE,-3) 三个月前,add_months(SYSDATE,60) 六十个月后 FROM dual ;--直接添加的是月，有考虑闰年，直接加数据无法控制准确日期
SELECT empno,ename,job,mgr,hiredate,add_months(hiredate,3) from emp ;--雇用日期三个月后
SELECT trunc(to_date('2021/7/24','yyyy/mm/dd')-SYSDATE) FROM dual ;--距离下次发年终奖的日期
SELECT trunc(to_date('2021/2/11','yyyy/mm/dd')-SYSDATE) 春节倒记时 FROM dual ;--距离过年的天数
SELECT sysdate, next_day(SYSDATE,'星期六') 下一个星期六,next_day(SYSDATE,'星期日') 下个日期日 FROM dual; --只能是星期日不能是星期天
SELECT empno,ename,job,hiredate,last_day(hiredate) FROM emp WHERE last_day(hiredate)-2=hiredate; --前两天是倒数第三天
SELECT empno,ename,hiredate,TRUNC(months_between(SYSDATE,hiredate)) 月数,TRUNC((months_between(SYSDATE,hiredate))/12) FROM emp ;--两个时间间隔的月份数，月份数除12是年数
--98页，分三步，比较复杂。距今信用日期年数月数天数
SELECT empno,ename,hiredate,TRUNC(months_between(SYSDATE,hiredate)/12) 雇用年数,trunc(MOD(months_between(sysdate,hiredate),12)) 雇用月数,trunc(SYSDATE-add_months(hiredate,months_between(SYSDATE,hiredate))）雇用天数 FROM emp ;
SELECT EXTRACT(YEAR FROM DATE '2020-10-5') YEAR,EXTRACT(MONTH FROM DATE '2020-10-5') MONTH,EXTRACT(DAY FROM DATE '2020-10-5') DAY FROM dual ;--要减号隔开的字符串类型才可以
--从时间戳中取得年月日时分秒
SELECT EXTRACT (YEAR FROM SYSTIMESTAMP) YEAR,EXTRACT(MONTH FROM SYSTIMESTAMP) MONTH,EXTRACT(DAY FROM SYSTIMESTAMP) DAY,EXTRACT(HOUR FROM SYSTIMESTAMP) HOUR，EXTRACT(MINUTE FROM SYSTIMESTAMP) MINUTE,EXTRACT(SECOND FROM SYSTIMESTAMP)SECOND FROM dual ;
SELECT SYSDATE ,SYSTIMESTAMP FROM dual ;--时区是1，以下语句修改，要重启，没修改成功
ALTER DATABASE SET TIME_ZONE='+8:00';--修改时区</code></pre>
<h4>转换函数，数据类型间的转换</h4>
<pre><code>SELECT to_char(SYSDATE,'yyyy/mm/dd') FROM dual ;--date/number to char
SELECT to_date('2020/08/24','yyyy/mm/dd') FROM dual;--char to date
SELECT to_number('2020') FROM dual;--char to number
SELECT to_char(SYSTIMESTAMP,'yyyy/yyy/yy/y/year/month/mm/day/ddd/dd/d/dy/ww/w/hh') FROM dual;
SELECT to_char(SYSTIMESTAMP,'hh24/mi/ss/ssss/AM/FM') FROM dual;
SELECT SYSDATE 原始时间, to_char(SYSDATE ,'yyyy-mm-dd'),to_char(SYSDATE,'yyyyy/mm/dd hh24:mi:ss'),to_char(SYSDATE,'fmyyyy/mm/dd hh24:mi:ss') FROM dual ;
SELECT SYSDATE ,to_char(SYSDATE,'year/month/dy') FROM dual ;
--所有2月份入职的雇员
SELECT empno,ename,job,hiredate FROM emp WHERE to_char(hiredate,'mm')='02';
SELECT empno,ename,job,hiredate,to_char(hiredate,'yyyy')||'年'||to_char(hiredate,'mm')||'月'||to_char(hiredate,'dd')||'日' 雇用日期 FROM emp;
SELECT empno,ename,job,hiredate,to_char(hiredate,'year/month/dy') FROM emp;
SELECT to_number('08')+to_number('09'),to_number('08')*to_number('09') FROM dual ;
SELECT '08'+'09','08'*'09' FROM dual ;</code></pre>
<h4>通用函数</h4>
<pre><code>SELECT * FROM emp ;
SELECT empno,ename,job,sal,NVL(comm,0) comm FROM emp ;--nvl(列|默认值)，当列为null时显示默认值
SELECT empno,ename,job,sal,NVL2(comm,comm,0) FROM emp;--nvl(列,返回结果1,返回结果2)，结果1：不为空时;结果2：为空时。
SELECT NULLIF(3,2) FROM dual;--比较两值，如果相等返回null，如果不相等返回第一个值
SELECT empno,ename,job,sal,comm,DECODE(mgr,'7902','岗位是Clerk','7698','岗位是Salesman','岗位是其他的') FROM emp;
SELECT empno,ename,job,sal,comm,(sal+comm)*12 年薪 FROM emp;
SELECT empno,ename,job,sal,comm,(sal+NVL(comm,0))*12 年薪 FROM emp;
SELECT empno,ename,job,sal,comm,NVL2(comm,sal+comm,sal)*12 年薪 FROM emp;--不为null则第一个值
SELECT NULLIF(1,NULL),NULLIF(1,1),NULLIF(1,2) FROM dual;--如果相等则返回null，不等返回第一个值.第一个值不能为null
SELECT empno,ename,job,NULLIF(LENGTH(ename),LENGTH(job)) NULLIF FROM emp ;
SELECT DECODE(3,1,'指定值为1',2,'指定值为2','没有明确指定值') FROM dual;
SELECT empno,ename,job,sal,DECODE(job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁','没有明确岗位') FROM emp ;
SELECT EMPNO,
ENAME,
JOB,
SAL,
CASE JOB
WHEN 'CLERK' THEN SAL * 1.1
WHEN 'SALESMAN' THEN sal*1.2
WHEN 'manager' THEN sal*1.5
ELSE
SAL * 1.5
END 新工资
FROM EMP;
SELECT empno,ename,job,sal,comm,COALESCE(comm,100),COALESCE(comm,NULL,NULL) FROM emp ;--往后取值，直到非null，如果会是null，则只能是null了</code></pre>
<h4>取某字段最大的记录</h4>
<pre><code>select t.* from (select t.*, rank() over(order by sum desc) rk from (select to_char(hire_date,'day') day ,count(*) sum from employees group by to_char(hire_date,'day') ) t) t where rk = 1;</code></pre>