3. 单行函数
3.1 转换函数
select ascii('A'),chr(65) from dual;
select to_char(1243123),1231451 from dual;
靠左边的就是字符串，靠右边的就是数字
select to_char(123512a121) from dual; --错误的写法，没有引号表示数字，但是数字里面包含了字母，不合法的输入值
select to_number('123141211') from dual;
select to_number('12314a1211') from dual; --不合法的输入值
select to_date('20140610121212','yyyy-mm-dd hh24:mi:ss') from dual;
不合法的输入值：
select to_date('20141310121212','yyyy-mm-dd hh24:mi:ss') from dual;
select to_date(20140610121212,'yyyy-mm-dd hh24:mi:ss') from dual; --日期不加引号，数字类型
将日期转换为固定格式字符串
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd') from dual;
select to_number(to_char(sysdate,'yyyymmdd')) from dual;
3.2 字符串的运算
(1)字符串的连接
select concat('123124','asdfda') from dual; --只能传两个参数，但是||可以不断拼接
select '123124'||'asdfda' from dual;
(2)求长度
select length('adsfasgsadfasdf') from dual;
(3)截取串
select substr('asdfasdfasdfasdf',5,4) from dual;
从左向右第5位开始向后截取4个字节长度
select substr('asdfasdfasdfasdf',-5,4) from dual;
从右向左第5位开始向后截取4个字节长度
select length(substr('asdfasdfasdfasdf',-5,7)) from dual; --截取完为止
(4)求子串的位置
select instr('asdfasdfasdfasdf','asdf',3,1) from dual;
从左向右第3位开始，第1次出现‘asdf’的索引位置
select instr('asdfasdfasdfasdf','asdf',3,2) from dual;
从左向右第3位开始，第2次出现‘asdf’的索引位置
select instr('asdfasdfasdfasdf','asdf',-3,2) from dual; --9 （红色的为第一个，***开始为第二个，也就是第五个a是第二个，然后顺序再从右边第四个开始算起为1，所以也就是第九位） ；
从右向左第3位开始，第2次出现‘asdf’的索引位置
意思是说从右边的第三位开始算起，也就是从右边的第四位开始由1开始，然后第二次出现字符创的位置仍然是从左边开始的，也就是 a s d f a s d f a,但是数字就是从右边算起
9 8 7 6 5 4 3 2 1
select instr('asdfasdfasdfasdf','asdf',-3,1) from dual; --13
从右向左第3位开始，第1次出现‘asdf’的索引位置
(5)大小写的转换
select upper('asdfasgas'),lower('GHJAJDGJ') from dual;
select lower(ename),lower(job) from emp;
(6) 替换
select replace('asdfasdfasdfasdf','asdf','H') from dual;
(7) 补足字符串
select lpad('asdf',20,'*'),rpad('asdf',20,'*') from dual;
(8)去空格
select trim(' sadf ') as a,ltrim(' asdf ') as b,rtrim(' asdf ') as c from dual;
(9)去中间的空格
select replace(' asdf sdf dfasdf sdf ' , ' ' , '' ) as a from dual;
‘ 字符串 ’，分隔符 ‘ ’匹配条件 ‘’匹配结果
asdfsdfdfasdfsdf
###########################################################################################
4.数字函数
(1) 小数点后1位四舍五入
select round(2.76,1) from dual;
(2) 对整数四舍五入
select round(2.76) from dual;
(3)向上取整，ceil表示天花板
select ceil(4.3) from dual;
(4)向下取整，floor表示地板
select floor(4.8) from dual;
(5)求余数
select mod(10,3) from dual;
(6)截断
select trunc(10.454212348211,2),trunc(21154.1454211,-2) from dual;
注意：第二个参数是正数，截断小数点后面的两位
第二个参数是负数，截断小数点前面的两位
##########################################################################################
5.日期函数
(1)截断日期
select trunc(to_date('20140610','yyyy-mm-dd'),'dd'),trunc(to_date('20140610','yyyy-mm-dd'),'mm'),
trunc(to_date('20140610','yyyy-mm-dd'),'yyyy') from dual;
select trunc(to_date('20140610121212','yyyy-mm-dd hh24:mi:ss'),'hh'),
trunc(to_date('20140610121212','yyyy-mm-dd hh24:mi:ss'),'mi') from dual;
(2) 两个日期之间的天数 = 两个日期相减
select sysdate - to_date('20130610095000','yyyy-mm-dd hh24:mi:ss') from dual;
(3) 月份前后推
select add_months(sysdate,-12),add_months(sysdate,3) from dual;
第二个参数为负数，往前推
第二个参数为正数，往后推
(4) 算两个日期间隔的月数
select months_between(sysdate,to_date('20130610100000','yyyy-mm-dd hh24:mi:ss')) from dual;
例子：
计算入职时间年数的分布：
A：1年以内
B：1~3年
C：3~5年
D: 5年以上
select rs1,count(*) from
(select empno,(case when rs<=1 then 'BELOW 1'
when rs>1 and rs<3 then '1~3'
when rs>=3 and rs<5 then '3~5'
when rs>=5 then 'over 5' end) as rs1
from(
select empno,round((months_between(sysdate,hiredate)/12)) as rs
from emp
)
)group by rs1;
(5) decode函数
F=decode(p1,p2,p3,p4,p5...,pn)
含义：如果p1 = p2,F=p3
如果p1 = p4,F=p5
.....
如果都不满足，F=pn
select decode(deptno,10,'dept10',20,'dept20',30,'dept30','dept40') as x from dept;
=
select (case when deptno=10 then 'dept10'
when deptno=20 then 'dept20'
when deptno=30 then 'dept30'
else 'dept40' end
) as x
from dept;
(6)nvl函数
nvl(comm,9999) -- 如果第一个参数为null，就显示第二个参数
示例：求当第一个数为null的时候，值取9999，否则等于原值
select empno,nvl(comm,9999) as x,decode(comm,null,9999,comm) as y,(case when comm is null then 9999 else comm end) as z from emp;
###########################################################################################
6.表的连接
6.1等值连接
select b.deptno,a.ename from dept b (inner) join emp a on(a.deptno=b.deptno)
等值连接其实就是内连接
6.2左连接
select b.deptno,a.ename from dept b left (outer) join emp a on(a.deptno=b.deptno);
dept -- 主表
emp -- 副表
编写方式：主表 left join 副表，保证主表的数据不丢失
6.3右连接
select b.deptno,a.ename from emp a right join dept b on(a.deptno=b.deptno);
dept -- 主表
emp -- 副表
编写方式：副表 right join 主表，保证主表的数据不丢失
oracle语法：将左右连接统一表示
select b.deptno,a.ename from emp a, dept b where a.deptno(+)=b.deptno;
select b.deptno,a.ename from emp a, dept b where b.deptno=a.deptno(+);
(+)的对面的表就是主表
dept表是主表
emp 表是副表
注意：
A：如果副表上有过滤条件的时候，怎样满足左右连接的效果
select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno) where a.sal>1000;
为了加过滤条件，把主表的数据丢了，此时失去左连接的意义
select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno and a.sal>1000);
=
select b.deptno,a.ename from dept b,emp a where a.deptno(+)=b.deptno and a.sal(+)>1000;
B：主表上有过滤条件，不存在这个问题
数学原理：
左连接 ： A∪(A∩B)
等值连接： A∩B
6.4多表关联
select * from a,b,c,d where a.x=b.x and b.y=c.y and c.z=d.z;
特殊的连接：笛卡尔积
select * from a,b
6.5非等值连接
select e.ename,s.grade,e.sal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
salgrade：薪资等级表
6.6自连接
查询所有员工的直接上司的对应关系，表示成“员工名称 work for 上司名称”，一般用在有层级关系的表上面
select worker.ename ||' work for '||manager.ename
from emp worker,emp manager
where worker.mgr=manager.empno;
##########################################################################################
7 集合运算
7.1 求差集
A. not exists操作和not in 操作
select b.deptno,b.dname,b.loc from dept b
where not exists(select a.deptno from emp a where a.deptno=b.deptno);
=
select b.deptno,b.dname,b.loc from dept b
where b.deptno not in(select a.deptno from emp a where a.deptno=b.deptno);
B. minus操作
select b.deptno from dept b
minus
select a.deptno from emp a;
7.2 求交集
A exists操作 和 in操作
select b.deptno,b.dname,b.loc from dept b
where exists(select a.deptno from emp a where a.deptno=b.deptno);
=
select b.deptno,b.dname,b.loc from dept b
where b.deptno in(select a.deptno from emp a where a.deptno=b.deptno);
B 等值连接
select distinct b.deptno,b.dname,b.loc from emp a,dept b where a.deptno=b.deptno;
7.3 求并集
A 左右连接（其中一个集合是另外一个集合的子集）
select b.deptno,a.ename from dept b left join emp a on(a.deptno=b.deptno);
B union操作
select b.deptno from dept b --合并后去重
union
select a.deptno from emp a;
select b.deptno from dept b --合并之后不去重
union all
select a.deptno from emp a;
7.4 求全集
select * from emp;
7.5 求子集
select * from emp where ename like 'T%';
7.6 求补集
select * from emp where ename not like 'T%';
7.7 求映射
select ename,job from emp where ename like 'T%';
在操作ename集合的时候，同时查询job字段映射的值
###########################################################################################
8.分组报表
select b.deptno,b.dname,nvl(floor(avg(a.sal)),0) as x,nvl(sum(a.sal),0) as y,nvl(min(a.sal),0) as z,nvl(max(a.sal),0) as l
from emp a,dept b
where a.deptno(+)=b.deptno
group by b.deptno,b.dname
order by b.deptno,b.dname;
having 字句 -- 对聚合函数的结果进行过滤
select b.deptno,b.dname,nvl(floor(avg(a.sal)),0) as x,nvl(sum(a.sal),0) as y,nvl(min(a.sal),0) as z,nvl(max(a.sal),0) as l
from emp a,dept b
where a.deptno(+)=b.deptno
group by b.deptno,b.dname
having avg(a.sal)>2000
order by b.deptno,b.dname;
注意：having字句一定要放在order by的前面，group by的后面
##########################################################################################
9.单行子查询还有多行子查询
(1) 单行子查询：返回一行的子查询
需求：显示scott员工姓名、薪水、部门号并且与其同部门的人相同信息也显示出来
select ename,sal,deptno from emp
where deptno=(select deptno from emp where ename='SCOTT')
=
select ename,sal,deptno from emp
where deptno in (select deptno from emp where ename='SCOTT')
(select deptno from emp where ename='SCOTT')返回单行，叫做单行子查询。 =、in 叫做单行操作符
(2) 多行子查询：返回多行的子查询
需求：显示部门10所有岗位job在全公司对应相同岗位人的姓名、薪水、部门号
select ename,sal,deptno from emp
where job in (select job from emp where deptno=10)
in 在这里是作为多行操作符使用
select ename,sal,deptno from emp
where job = (select job from emp where deptno=10)
错误的写法，=是单行操作符
A 在多行子查询中使用any操作符
需求：显示全公司工资高于部门10任一员工工资的员工的姓名、薪水、部门号
select ename,sal,deptno from emp
where sal > any(select sal from emp where deptno=10)
>是单行操作符，而子查询是多行的，用any来解决语法冲突，满足'任一'这种需求
B 在多行子查询中使用all操作符
需求：显示全公司工资高于部门20所有员工工资的员工的姓名，薪水，部门号
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=20)
>是单行操作符，而子查询是多行的，用all来解决语法冲突，满足'所有'这种需求
注意：
1.子查询中不能写order by
2.多行子查询不能用单行操作符匹配
3.单行子查询可以匹配多行操作符
4.子查询必须写在括号里面
5.子查询要放在比较运算符后面
10. 嵌套子查询(多个子查询)
select ename,sal,deptno from emp
where sal > (select sal from emp where empno=7876) and job = (select job from emp where empno=7369);
