前言
只是记录和整理了一些对我有用的笔记,详细得请看原著。
单表查询
查找空值
select * from emp where comm is null
NULL 不支持 加、减、乘、除、大小比较、相等比较,否则只能为空;
处理空值NVL
NVL 只能处理多个参数,如果要处理多个参数,则可以使用COALESCE
在 WHERE 子句中 引用取别名的列
select * from (select sal as 工资, comm as 提成 from emp) x where 工资 <= 1000;
限制返回的行数
rownum 是依次对数据做标识的,需要先有第一名,后面才会有第二名。
select *
from (select rownum as sn,emp.* from emp where rownum <= 2)
where sn = 2
给查询结果排序
以指定的次序返回查询结果
除了 order by 字段名 ASC 这种写法,还可以写成 order by 3 ASC 意思是按第三列排序。
用数据代替列位置只能用于order by 字句中,其他地方都不能用。
从表中随机返回n条记录
先随机排序,再取数据
select empno, ename
from (select empno,ename from emp order by dbms_random.value())
where rownum <=3
TRANSLATE
select TRANSLATE ('ab 你好 bcadefg','abcdefg','1234567' ) as new_str from dual;
结果:
12你好2314567
处理排序空值
Oracle 默认 升序时空值在后,降序时空值在前
可以使用关键字 NULLS FIRST,NULLS LAST 更改空值顺序
根据条件取不同列中的值来排序
例如:领导对工资在1000到2000元之前员工排在前面,以便优先查看。
select empno as 编码,
ename as 姓名,
sal as 工资
from emp
where dempno = 30
order by case when sal >= 1000 and sal < 2000 THEN 1 else 2 end,3;
操作多个表
IN、EXISTS和INNER JOIN
IN 写法
select empno, ename, job,sal,deptno
from emp
where (ename,job,sal) in (select ename,job,sal from emp2);
EXISTS 写法
select empno,ename,job,sal,deptno
from emp a
where exists (select NULL emp2 b where
b.ename = a.ename
and b.job =a.job
and b.sal = a.sal )
INNER JOIN 写法
select a.empno,a.ename,a.job,a.sal,a.deptno from emp a
inner join emp2 b on (b.ename = a.ename and b.job = a.job and b.sal = a.sal)
JOIN 写法中利用了 HASH JOIN(哈希连接) ,其他两种应用都是HASH JOIN SEMI (哈希半连接)。
更详细的两者的区别请看Oracle 连接和半连接,我是没看懂。。。
插入、更新与删除
阻止对某几列插入
建表的时候会遇到默认值 SYSDATE,这种列一般是为了记录数据生成的时间,不允许手动录入,那么系统控制不到位,可能会出现手动录入的情况发生这时可以通过视图来完成。
表 T4_1(c1,c2,c3,c4),c4 是SYSDATE
create or replace view v4_1 as select c1,c2,c3 from T4_1
insert into v4_1(c1,c2,c3) values ('手输c1',null,'不能改c4');
复制表的定义及数据
create table t4_2 as select * from t4_1;
用WITH CHECK OPTION 限制数据录入
当约束条件比较简单时,可以直接加在表中,如工资必须大于0
alter table emp add constraints ch_sal check(sal > 0 );
insert into
(select empno,ename,hiredate)
from emp
where hiredate <= SYSDATE WITH CHECK OPTION)
VALUES
(9999,'TEST',SYSDATE+1)
会提示错误
(select empno,ename,hiredate)
from emp
where hiredate <= SYSDATE WITH CHECK OPTION) 是个视图
多表插入语句
无条件INSERT
因为没有加条件,所以会同时向两个表插入数据,且两个表中插入的条数一样
insert all
into emp1(empno,ename) values (empno,ename)
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;
有条件INSERT ALL
insert all
when ename in ('x','t') then
into emp1(empno,ename) values (empno,ename)
when deptno in ('1','2') then
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;
有条件INSERT FIRST
当第一个表符合条件后,第二个表将不再插入对应的行
insert first
when ename in ('x','t') then
into emp1(empno,ename) values (empno,ename)
when deptno in ('1','2') then
into emp2(empno,deptno) values (empno,ename)
select empno,ename,deptno from emp;
合并记录
merge into bonuses d
using (select employee_id,salary,depart_id
from employees
where depart_id = 80
) s
on (d.employee_id = s.employee_id)
when matched then
update
set d.bonus = d.bonus + s.salary * 0.01
when not matched then
insert
(d.employee_id,d.bonus)
values
(s.employee_id,s.salary * 0.01 )
注意事项:
(1)语句是merge into bonuses ,所以在这个语句中只能更改bonuses 的数据,不能改using 后面那些表的数据。
(2)更新,插入两个操作是同时进行的,不分先后。
(3) 在MERGE INTO 语句里不能更新JOIN系列
(4) ON 后面的条件一定要放在括号里,否则会报错
on (d.employee_id = s.employee_id)
删名字重复的记录
通过rowid
delete
from dupes a
where exists (select null from dupes b where b.name = a.name
and b.rowid > a.rowid)
通过分析函数
delete
from dupes
where rowid in (select rid
from (select rowid as rid, row_number() over(partition by name order by id) as seq from dupes
whereseq >1 );
)
使用字符串
生成连续数值
select rownum as rn from emp where rownum <= 3;
select LeveL AS rn from dual CONNECT BY LEVEL <=3;
遍历字符串
select 汉字,LEVEL,substr(汉字,LEVEL,1) as 汉字拆分 from v5_2
connect by LEVEL <= length(汉字);
将字符和数字数据分离
select regexp_replace(data,'[0-9]','') dname,
regexp_replace(data,'[^0-9]','') deptno
from t5_5;
根据表中的行创建一个分割列表
select deptno,
listagg(ename,',') within group(ORDER BY ename) As total_name
from emp
GROUP BY deptno;
生成排名
row_number() over(partition by deptno order by sal desc);
rank() over(partition by deptno order by sal desc);
dense_rank() over(partition by deptno order by sal desc);
row_number() 相同的工资会生成 1,2,3
rank 相同的工资会生成同样的序号 1,1,3
dense_rank() 会生成同样的序号 1,1,2