oracle学习笔记_2019-07-18

–查询人员表中办事员的最大最小平均工资总和和办事员数量

select max(e.sal),min(e.sal),avg(e.sal),sum(e.sal),count(*) from emp e where e.job='CLERK'

–各个职位最高工资最低工资

select e.job, max(e.comm),min(e.comm) from emp e group by e.job

–统计各个职位的人数和职位名称最小员工编号

select e.job,count(*),min(e.empno) from emp e group by e.job

–2010前入职各个部门的人员数量

select count(*) from emp e where to_char(e.hiredate,'yyyy')<'2010' group by e.deptno

–人员表中存在重名的人员名称及人数

select e.ename,count(*) from emp0 e group by e.ename having count(*)>1

–效果与distinct相同

select e.ename from emp0 e group by e.ename 

–统计平均奖金大于2000的各个职位的最高奖金

select e.job,max(e.comm) from emp e group by e.job having avg(e.comm)>2000

–统计每个部门的最高工资根据最高工资倒序和部门编号升序排序

select e.deptno,max(nvl(e.sal,0)) from emp e group by e.deptno order by max(nvl(e.sal,0)) desc,e.deptno

–统计各个部门各个职位的最高工资

select e.deptno,e.job,max(nvl(e.sal,0)) from emp e group by e.deptno,e.job order by e.deptno

–统计各个部门各个岗位的最高工资，只显示最高工资大于1000的数据

select e.deptno,e.job,max(e.sal) from emp e group by e.deptno,e.job having max(e.sal)>1000

–相等连接、内连接、查询人员姓名及所属部门名称,内连接只返回符合条件的数据

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno

–查询人员姓名及上级领导姓名

select e.ename,m.ename from emp e,emp m where e.mgr = m.empno

–所有部门名称及上级部门名称

select d.dname,p.dname from dept d,dept p where d.upperdeptno = p.deptno

–所有人员名称及所属部门名称 上级领导姓名 所属部门名称

select e.ename as 人,d.dname as 部门,m.ename as 上级领导,p.dname as 部门
from emp e,dept d,emp m,dept p 
where e.mgr=m.empno and m.deptno=p.deptno and e.deptno=d.deptno

–左外连接，以左表为主，显示人员表的所有内容

select e.ename,nvl(d.dname,'无') 
from emp e left join dept d 
on e.deptno=d.deptno

–左外连接，显示部门表的所有内容

select d.dname,nvl(e.ename,'无')  from dept d left join emp e  on d.deptno = e.deptno

–右外连接

select e.ename,nvl(d.dname,'无') 
from emp e right join dept d 
on e.deptno=d.deptno

–显示部门表的所有内容 +看对面

select d.dname,e.ename
from emp e,dept d
where e.deptno(+)=d.deptno

–完全外连接，将两个表不满足条件的数据都查出来

select e.ename,d.dname,d.deptno
from dept d full join emp e
on d.deptno=e.deptno

–所有人员姓名、上级领导姓名、人员所在部门名称
–上级领导所在部门名称
–上上名，上上部门名

select e1.ename as 人员姓名,d1.dname as 部门名,e2.ename as 上级领导姓名，d2.dname as 上级领导所在部门名,e3.ename as 上上级领导姓名，d3.dname as 上上级领导所在部门名
from emp e1,emp e2,emp e3,dept d1,dept d2,dept d3
where e1.deptno=d1.deptno(+) and e1.mgr=e2.empno (+) and e2.deptno=d2.deptno(+) and e2.mgr=e3.empno(+) and e3.deptno=d3.deptno(+)

–查询刘鹏飞所在部门名称

select d.dname from dept d
where d.deptno = (select deptno from emp e where e.ename='刘鹏飞')

select d.dname from dept d,emp e
where d.deptno=e.deptno and e.ename='刘鹏飞'

–查询姓王的人员所在部门名称

select d.dname from dept d,emp e
where d.deptno=e.deptno and e.ename like '刘%'

select d.dname from dept d
where d.deptno in (select deptno from emp e where e.ename like '刘%')

–子查询返回多列数据

select e.ename,e.job,e.sal,e.deptno from emp e
where (deptno,job)=(select deptno,job from emp where ename ='刘鹏飞')

–查询工作岗位是办事员的工资最高的人员名称

select e.ename
  from emp e
 where e.sal = (select max(sal) from emp where job = 'CLERK')
   and e.job = 'CLERK'

–查询所有人员姓名，上级领导姓名

select e1.ename,
       nvl((select ename from emp e2 where e1.mgr = e2.empno), '无') as 上级领导
  from emp e1
  order by e1.ename

select e1.ename,e2.ename from emp e1,emp e2
where e1.mgr = e2.empno
order by e1.ename

–查询所有人员姓名，所在部门名称，所在部门上级部门名称

select e1.ename,
(select d1.dname from dept d1 where d1.deptno=e1.empno) 部门名,
(select d2.dname from dept d1,dept d2 where d1.upperdeptno=d2.deptno and e1.deptno=d1.deptno) 上级部门名
from emp e1

–在insert语句中使用子查询

--insert into emp(empno,ename,deptno,job) select e.empno,e.ename,203,e.job
--from emp e
--where e.ename='测'

–在update语句中使用子查询

--update emp e
--set e.deptno=(select a.deptno from emp a where a.empno=8888)
--where e.empno=8888;
--commit;

–常用函数
–使用ascii码返回指定字符对应的十进制数

select ascii('A') from dual

–chr与ascii相反

select chr(67) from dual

–连接字符串

select '此员工的姓名为【'||e.ename||'】'from emp e

–连接字符串

select concat('此员工的姓名为：',e.ename)from emp e

–返回第二个字符串在第一个字符串中出现的位置,
–不管找没找到，都会有结果，没有返回0

select instr('AABBCDEFGHIJKLMN','AB')from dual

–从第二个位置开始找

select instr('AABBCDEFGHIABJKLMNAB','AB',2)from dual

–从第三个位置开始找，找第二个出现的

select instr('AABBCDEFGHIABJKLMNAB','AB',3,2)from dual

—返回字符串的长度、字节长度,汉字2字节，数字1字节

select ename,length(e.ename),lengthb(e.ename) from emp e

–返回字符大小写

select lower('ABCDaaa')，upper('absdAAA') from dual

–去空格

select ltrim('   asdf    ')from dual
select rtrim('   asdf    ')from dual
select trim('    asdf    ')from dual

–去特定字符

select ltrim('mmmmmasdf','m')from dual
select rtrim('asdfnnnn','n') from dual

–从第三个位置取8个字符

select substr('asdfasdfasdf',3,9)from dual

–取员工名

select substr(e.ename,2,length(e.ename)-1)from emp e

–从倒数第5位开始，取3个字符

select substr('adsfasdfasd',-5,3)from dual

–将job字段中的CL字符改为AL

select replace(e.job,'CL','AL')
from emp e

–若员工名包含飞，改为飞飞

select replace(e.ename,'飞','飞飞')from emp e

–四舍五入

select round('3.1415926',2)from dual
select round('3.1415926',3)from dual
select round('3.1415926',0)from dual
select round('155.1415926',-2)from dual

–截取数字

select trunc('3.1415926',2)from dual
select trunc('3.1415926',0)from dual
select trunc('113.1415926',-2)from dual

–给当前日期加2个月

select add_months(sysdate,2)from dual

–给指定日期加2个月

select add_months(to_date('2019-07-18','yyyy-mm-dd'),2)from dual

–五个月之后的前天

select add_months(sysdate-2,5)from dual

–求当前月份的最后一天

select last_day(sysdate)from dual

–今年2月份的最后一天

select last_day(to_date('2019-02','yyyy-mm'))from dual

–查询明年当前月份的最后一天

select last_day(add_months(sysdate,12))from dual

–返回特定日期之后的第一个工作日所对应的日期

select next_day(sysdate,'星期一')from dual

–非常好用decode，相当于if else

select e.ename, decode(e.job,'CLERK','办事员','SALESMAN','销售人员','领导')from emp e

–王刚是位办事员

select '' || e.ename || '是位' ||
       decode(e.job, 'CLERK', '办事员', 'SALESMAN', '销售人员', '领导') || ''
  from emp e