oracle函数

-----------------------单行函数-----------------------
1.lower/upper/initcap函数，使用dual哑表测试
SELECT lower(‘HELLOWORLD’) FROM DUAL;–转换为小写
SELECT upper(‘helloworld’) FROM DUAL;–转成大写
SELECT initcap(‘hello world’) FROM DUAL;–首字母转成大写

2.concat函数【拼接】
SELECT CONCAT(‘hello’,‘world’) FROM DUAL;-- 正确
SELECT CONCAT(‘hello’,‘world’,‘aaa’) FROM DUAL;-- 错误
SELECT ‘hello’ || ‘world’ || ‘aaa’ FROM DUAL;-- 正确
SELECT CONCAT(‘hello’,CONCAT(‘world’, ‘aaa’)) FROM DUAL;-- 正确

3.substr函数【截取】
SELECT SUBSTR(‘helloworld’, 5, 3) FROM DUAL;
5表示从第几个开始取，从1开始，中英文无区别
3代表取几个字符

4.length/lengthb函数【编码格式为UTF-8，1英文1字节，1中文2/3个字节】
SELECT LENGTH(‘HELLO你好’) FROM DUAL;字符长度
SELECT LENGTHB(‘HELLO你好’) FROM DUAL;字节长度

5.instr/lpad/rpad函数【从做找第一次出现的位置，从1开始】
SELECT INSTR(‘HELLOWORLD’,‘W’) FROM DUAL;找不到返回0，大小写敏感
SELECT LPAD(‘hello’, 10, ‘#’) FROM DUAL;获取指定长度字符不够再左边补充#
SELECT RPAD(‘hello’, 10, ‘#’) FROM DUAL;获取指定长度字符不够再右边补充#

6.trim/replace函数
SELECT TRIM(‘o’ FROM ‘ohello’) FROM DUAL;去掉两边o
SELECT REPLACE(‘HELLO’, ‘L’, ‘p’) FROM DUAL;将L替代为p，找不到原样输出

7.round/trunc/mod函数作用于数值型
SELECT ROUND(3.1415, 3) FROM DUAL;四舍五入
SELECT TRUNC(3.1415, 3) FROM DUAL;去一法
SELECT MOD(10,3) FROM DUAL;取模

8.round/trunc函数用于日期
SELECT ROUND(SYSDATE, ‘MONTH’) FROM DUAL;
SELECT ROUND(SYSDATE, ‘YEAR’) FROM DUAL;
SELECT TRUNC(SYSDATE, ‘MONTH’) FROM DUAL;
SELECT TRUNC(SYSDATE, ‘YEAR’) FROM DUAL;

9.显示昨天，今天，明天的日期
SELECT SYSDATE-1 “昨天”,SYSDATE “今天”, SYSDATE+1 “明天” FROM DUAL;

10.显示员工入职天数，日期-日期=数值
SELECT ename “名字”,ROUND(SYSDATE-hiredate,0) “入职天数” FROM emp;

11.以年形式显示员工近似工龄，假设：一年以365天计算，一月以30天计算
select ename “姓名”,round(sysdate-hiredate,0)/365 “天数” from emp;

11.months_between函数精确计算到年底还有几个月
select months_between(‘31-12月-18’,sysdate) from dual;

12.精确计算员工工龄有多少月。
select ename “员工名字”,months_between(sysdate,hiredate) “工龄” from emp;

13.add_months函数，计算上/下个月的今天是多少号
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;
思考：三月31号上个月的今天日期是什么

14.next_day函数，从今天计算，下一个星期三是多少号
SELECT NEXT_DAY(SYSDATE, ‘星期三’) FROM DUAL;
下一个星期三是多少号;
SELECT NEXT_DAY(NEXT_DAY(SYSDATE, ‘星期三’), ‘星期三’) FROM DUAL;
下个星期三的星期日
SELECT NEXT_DAY(NEXT_DAY(SYSDATE, ‘星期三’), ‘星期日’) FROM DUAL;

15.last_day函数，本月的最后一天是多少号
SELECT LAST_DAY(SYSDATE) FROM DUAL;
本月的倒数第二天是多少号
SELECT LAST_DAY(SYSDATE)-1 FROM DUAL;
下个月的最后一天
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 1)) FROM DUAL;
上个月的最后一天
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) FROM DUAL;

--------------------类型转换函数------------------------------------------------
1.oracle中三大类型与隐式数据类型转换
（1）varchar2变长/char定长–>number，例如：‘123’->123
（2）varchar2/char–>date，例如：‘25-4月-15’->‘25-4月-15’
（3）number---->varchar2/char，例如：123->‘123’
（4）date------>varchar2/char，例如：‘25-4月-15’->‘25-4月-15’

2.oracle如何隐式转换：
1）=号二边的类型是否相同
2）如果=号二边的类型不同，尝试的去做转换
3）在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

3.查询1980年12月17日入职的员工（方式一：日期隐示式转换）
select * from emp where hiredate = ‘17-12月-80’;

4.使用to_char(日期，‘格"常量"式’)函数将日期转成字符串.
select to_char(sysdate,‘yyyy" 年 “mm” 月 “dd” 日 "day’) from dual;

5.使用to_char(日期，‘格式’)函数将日期转成字符串
select to_char(sysdate,‘yyyy-mm-dd"今天是"day hh24:mi:ss’) from dual;
或
select to_char(sysdate,‘yyyy-mm-dd"今天是"day HH12:MI:SS AM’) from dual;

6.使用to_char(数值，‘格式’)函数将数值转成字符串，显示如下格式：$1,234
select to_char(1234,’$9,999’) from dual;

7.使用to_char(数值，‘格式’)函数将数值转成字符串.L自动识别地区货币符号
select to_char(1234,‘L9,999’) from dual;

8.使用to_date(‘字符串’,‘格式’)函数，查询1980年12月17日入职的员工（方式二：日期显式转换）
select * from emp where hiredate = to_date(‘1980年12月17日’,‘yyyy"年"mm"月"dd"日"’);
或
select * from emp where hiredate = to_date(‘1980#12#17’,‘yyyy"#“mm”#"dd’);
或
select * from emp where hiredate = to_date(‘1980-12-17’,‘yyyy-mm-dd’);

9.使用to_number(‘字符串’)函数将字符串’123’转成数字123
select to_number(‘123’) from dual;

注意：
select ‘123’ + 123 from dual;246
select ‘123’ || 123 from dual;123123
---------------------------------------------通用函数------------------------------------------
通用函数：参与运算的参数可以是number，varchar2或者date类型
1.NVL(a,b)
统计员工的年收入

2.NVL2(a,b,c),如果a不为null，取b值，要么取c值

3.NULLIF(a,b),在类型一致的前提下，如果a和b相同返回null，否则返回a

4.使用SQL99标准通用语法中的case表达式，将职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400
case 字段
when 条件 then 表达式1
when 条件 then 表达式2
else 表达式n
end
案例：
SELECT ENAME “名字”,JOB “职位”,SAL “薪水”,
case job
when ‘analyst’ then sal+1000
when ‘manager’ then sal+800
else sal+400
end “涨后薪资”
FROM EMP;

5.使用oracle专用语法中的decode()函数
语法：decode(字段,条件1,表达式1,条件2,表达式2,…表达式n)
SELECT ename “姓名”,job “职位”,sal “涨前薪水”,
DECODE(job, ‘anallyst’,sal+1000,‘manager’,sal+800,sal+400)
“涨后薪资”
FROM emp;

---------------------------------------------------多行函数----------------------------------------
1.统计EMPLOYEES表中有多少人
SELECT COUNT(*) FROM emp;
注意：*适用字段较少的情况，字段多扫描时间久，开发中提倡扫描非null的字段，一般为主键

2.统计公司有多少个不重复的部门
SELECT COUNT(DISTINCT deptno) FROM emp;

3.统计有佣金的员工人数
SELECT COUNT(comm) FROM EMP;
不统计NULL值

4.员工总工资，平均工资，四舍五入，保留小数点后0位
SELECT SUM(sal) “总工资”,ROUND(AVG(sal), 0) “平均薪资” FROM emp;

5.查看员工的最高和最低工资
SELECT MAX(sal) “最高工资”,MIN(sal) “最低工资” FROM emp;

6.入职最早，入职最晚
SELECT MAX(hiredate) “最晚入职”,MIN(hiredate) “最早入职” FROM emp;

7.按部门求出该部门的平均工资，且平均工资为整数，采取截断
SELECT deptno “部门编号”,TRUNC(AVG(sal), 0) “平均薪资”
FROM emp
GROUP BY deptno;
继续：查询部门平均薪资大于2000的部门
SELECT deptno “部门编号”,TRUNC(AVG(sal), 0) “平均薪资”
FROM emp
GROUP BY deptno
HAVING TRUNC(AVG(sal), 0) > 2000;
继续：按照平均薪资降序排序
SELECT deptno “部门编号”,TRUNC(AVG(sal), 0) “平均薪资”
FROM emp
GROUP BY deptno
ORDER BY “平均薪资” DESC;

HAVING和WHERE区别
WHERE:
1.行过滤器
2.针对原始数据
3.跟在FROM后边
4.可以省略
5.先执行
HAVING:
1.组过滤器
2.针对分组后的数据
3.跟在GROUP BY后边
4.可以省略
5.后执行

8.除10号部门外，查询部门平均工资大于2000元的部门，方式一【having deptno<>10】
select deptno,avg(sal)
from emp
group by deptno
having deptno<>10;

9.除10号部门外，查询部门平均工资大于2000元的部门，方式二【where deptno<>10】
select deptno,avg(sal)
from emp
where deptno<>10
group by deptno;
提倡

10.显示部门平均工资的最大值
select max(avg(sal)) “部门平均工资的最大值”
from emp
group by deptno;
思考：显示平均工资最高的部门和该部门编号?
select max(avg(sal)) “部门平均工资的最大值”,deptno “部门编号”
from emp
group by deptno;
group by 子句的细节：
1）在select子句中出现的非多行函数的所有列，【必须】出现在group by子句中
2）在group by子句中出现的所有列，【可出现可不现】在select子句中
oracle中综合语法：
1）select子句-----必须
2）from子句-------必须，不知写什么表了，就写dual
3）where子句------可选
4）group by子句—可选
5）having子句-----可选
6）order by 子句–可选，如果出现列名，别名，表达式，字段