整理了一下以前项目中的笔记，分享给大家。
where 工资 < 1000 where sn = 2
放到最前面 select ename, sal, com from emp order by 3 nulls first sal 工资 from emp where deptno = 30 order by 3, 4 select empno 编码， ename 名称，nvl(mgr, deptno) 上级编码 from emp where empno = 7788 select deptno 编码， dname 名称 , null 上级编码 from dept where deptno = 10
当条件有or 时经常改写为union 1-select scc.* , scw.warhouse_name from scc_warehouse scc left join scc_warehouse scw on scc.warehouse_no = scw.virtual_no from l left join r on (l.v = r.v and r.status = 1) order by 1,2 select e.deptno, sum(e.sal) total_sal, sum(e.sal * eb2.rate) as total_bontus sum(case when eb.type = 1 then 0.1 when eb.type = 2 then 0.2 when eb.type = 3 then 0.3 end) as rate from emp_bonus eb group by eb.empno) eb2 on eb2.empno = e,empno 11. inner join , left join , right join , full join 12， union 与 union all 解决加 is not null select count(*) from dept where deptno not in (select emp.deptno from emp where emp.deptno is not null) from emp where hiredate <= sysdate with check option) values (9999, 'test', sysdate +1) into emp1(empno, ename , job ) values ( empno, ename, job) into emp2(empno, ename, deptno) values (empno, ename, deptno) select empno , ename, job , deptno from emp where deptno in (10,20)
when job in ('salesman', 'manager') then into emp1(empno, ename, job) values (empno, ename, job) when deptno in ('20','30') then into emp2(empno, ename, deptno ) values (empno, ename, deptno) select empno , ename, job , deptno from emp
set emp.ename = (select dept.dname from dept where dept.deptno = emp.deptno and dept.dname in ('accounting', 'research')) from dept where dept.deptno = emp.deptno and dept.dname in ('accounting', ' research')) from emp inner join dept on dept.deptno = emp.deptno(修改时，必须是主键） where dept.dname in ('accounting', 'research')) set dname = new_dname using (select dname, deptno from dept where dept.dname in ('accounting', 'research')) dept on (dept.deptno = emp.deptno) when matched then update set emp.dname = dept.dname ename as 姓名, sal as 人工成本, sum(sal) over (order by empno) as 成本累计 from emp where deptno = 30 order by ename; (case when seq = 1 then 金额 else -金额 end) as 转换后的值 from x sum(case when seq = 1 then 金额 else -金额 end) over (order by seq) as 余额 from x case when trx = 'py' then '取款' else '存款' end 存取类型, amt 金额, sum(case when trx = 'py' then -amt else amt end) over (order by id) as 余额 select sal, dense_rank() over(order by 出现次数 desc) as 次数排序 from (select sal, count(*) as 出现次数 from emp where deptno = 20 group by sal ) x ) y MAX(ename) keep(dense_rank first order by sal ) over (partition by deptno) as 工资最低的人, max(ename) keep (dense_rank last order by sal) over (partition by deptno) as 工资最高的人, ename,sal from emp where deptno = 10 order by 1,6 desc
select hiredate, to_number(to_char(hiredate, 'hh24')) 时, to_number(to_char(hiredate, 'mi')) 分, to_number(to_char(hiredate, 'ss')) 秒， to_number(to_char(hiredate, 'dd')) 日, to_number(to_char(hiredate, 'mm'))月, to_number(to_char(hiredate, 'yyyy')) 年， to_number(to_char(hiredate, 'ddd')) 年内第几天, trunc(hiredate , 'dd') 一天之始, trunc(hiredate, 'day') 周初, trunc(hiredate, 'mm') 月初， last_day(hiredate) 月末, add_months(trunc（会热达特，’mm'),1) 下月初, trunc(hiredte, 'yy') 年初， to_char(hiredate, 'day') 周几, to_char(hiredate, 'month') 月份 (floor_nbr * 100) + row_number() over(partition by floor_nbr order by rowid ) as room_nbr from hotel ) b on (a.rowid = b.rowid) when matched then from (select rownum as rn ,sal , ename from ( select sal,ename from emp where sal is not null order by sal ) x where rownum <=10) where rn >= 6
from (select rownum as rn, empno , ename, sal from (select empno, ename, sal from emp order by ename) x) y