Oracle 27个常用例子

整理了一下以前项目中的笔记，分享给大家。

1，在where子句中引用取别名的列

select * from (select sal 工资，comm 提成 from emp) x

where 工资 < 1000

需要嵌套一层，否则报错

2，拼接列

select ename || ' 的工作是 ' || job as msg from emp where deptno = 10;

clark 的工作是manager

3，rownum 取出第二名

select * from (select rownum as sn, emp.* from emp where rownum <= 2)

where sn = 2

4, 从表中随机返回n行记录

select empno, ename from (select empno, ename from emp order by dbms_random.value())

where rownum <= 3

5, 有空值排序

放到最前面 select ename, sal, com from emp order by 3 nulls first

放到最后面 select ename, sal, com from emp order by 3 nulls last

6, 根据条件取不同列中的值来排序

新生成一列

select empno 编码，ename 姓名 ， case when sal >=1000 and sal <2000 then 1 else 2 end 级别

sal 工资

from emp

where deptno = 30

order by 3, 4

7, union all 与空字符串

select empno 编码， ename 名称，nvl(mgr, deptno) 上级编码 from emp

where empno = 7788

union all

select deptno 编码， dname 名称 , null 上级编码 from dept

where deptno = 10

8, union 与or

当条件有or 时经常改写为union

create index idx_emp_empno on emp(empno )

create index idx_emp_ename on emp ( ename )

union 不仅两个数据集间重复的数据会被去重，单个数据集里重复的数据也会被去重

有重复数据的数据集用uinon后得到的数据与预期会不一致

改进方法加唯一标识（唯一列、主键列、rowid等）

9，left join

1-select scc.* , scw.warhouse_name from scc_warehouse scc

left join scc_warehouse scw

on scc.warehouse_no = scw.virtual_no

2--select l.str as left_str, r.str as right_str, r.status

from l

left join r on (l.v = r.v and r.status = 1)

order by 1,2

10, 建立视图

create or replace view v as select * from emp where deptno != 10 or ename = 'scott'

11, 聚集与内连接，聚集与外连接

先聚集后关联

select e.deptno,

sum(e.sal) total_sal,

sum(e.sal * eb2.rate) as total_bontus

from emp e

inner join (select eb.empno ,

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

where e.deptno = 10

group by e.deptno

先聚集再外连接

11. inner join , left join , right join , full join

inner join 交集，数据重叠部分

full join 并集，全部数据

12， union 与 union all

union 去重

13，子查询包含空值，not in （空值) 返回为空

解决加 is not null

select count(*) from dept where deptno not in (select emp.deptno from emp

where emp.deptno is not null)

14，阻止对某几列插入，可以用视图

15，with check option 限制数据录入

简单的

alert table emp add constrains ch_sal check(sal >0)

日期等复杂的

insert into (select empno , eanme, hiredate

from emp

where hiredate <= sysdate with check option)

values

(9999, 'test', sysdate +1)

16，多表插入语句

1，无条件

insert all

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)

2,有条件

insert all

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

insert frist 语句当第一个表符合条件后，第二个表将不再插入对应的行

17，只更新部门10：accounting, 20:research

update emp

set emp.ename =

(select dept.dname

from dept

where dept.deptno = emp.deptno

and dept.dname in ('accounting', 'research'))

where exists (select dept.dname

from dept

where dept.deptno = emp.deptno

and dept.dname in ('accounting', ' research'))

或者 update (select emp.dname, dept.dname as new_dname

from emp

inner join dept on dept.deptno = emp.deptno(修改时，必须是主键）

where dept.dname in ('accounting', 'research'))

set dname = new_dname

或者(效率最高） merge into emp

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

18， 成本累计和

select empno as 编号，

ename as 姓名,

sal as 人工成本,

sum(sal) over (order by empno) as 成本累计

from emp

where deptno = 30

order by ename;

19, 成本累计差

1---对数据排序

select rownum as seq, a.* from (select 编号，项目， 金额 from detail order by 编号） a

2---用case when 把收入和支出分开，支出为负数

with x as

(select rownum as seq, a.* from (select 编号，项目， 金额 from detail order by 编号） a）

-----with clause 以with 开头的查询，相当于创建一个临时表

select 编号，项目， 金额，

(case when seq = 1 then 金额 else -金额 end) as 转换后的值

from x

3----相加

with x as

(select rownum as seq, a.* from (select 编号，项目， 金额 from detail order by 编号） a）

select 编号，项目， 金额，

sum(case when seq = 1 then 金额 else -金额 end) over (order by seq) as 余额

from x

20, 余额问题

select id,

case when trx = 'py' then '取款' else '存款' end 存取类型,

amt 金额,

sum(case when trx = 'py' then -amt else amt end) over (order by id) as 余额

from v

order by id

21, 计算出现最多次数的值

select sal from (

select sal, dense_rank() over(order by 出现次数 desc) as 次数排序

from (select sal, count(*) as 出现次数

from emp

where deptno = 20

group by sal

) x

) y

where 次数排序 = 1

22，最低和最高工资

select deptno , empno ,

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

23, 常用的时间sql

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') 月份

from (select hiredate + 30/24/60/60 + 20/24/60 +5/24 as hiredate from emp where rownum <=1)

24, 重新生成房间号

merge into hotel a

using (select rowid as rid,

(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

update set a.room_nbr = b.room_nbr

26， 分页

select rn as 序号，ename as 姓名， sal as 工资

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

27, 跳过表中n 行

----求余函数 mod()

select empno , ename, sal ,mod(rn, 2) as m

from (select rownum as rn, empno , ename, sal

from (select empno, ename, sal from emp order by ename) x) y

where mod(rn,2) = 1