ORACLE常用函数与数据处理

ORACLE常用函数与数据处理

1.oracle虚拟表与虚拟列rownum,物理存放地址rowid

oracle虚拟表dual
常见用法
可以作为数据查询的基表使用；
1.查询时间
select sysdate from dual;
2.操作数据计算
select 1+1 from dual;
3.查看序列下一个值
select sequence名称.nextval from dual;
4.函数处理验证等
select trunc(sysdate,‘yyyy’) from dual;
select trunc(1.31222,2) from dual;
5.字符串拼接
select ‘1’||‘2’ from dual;
oracle虚拟列rownum
常见用法
1.作为查询分页值依据
SELECT *
FROM (SELECT ROWNUM RN, A.*
FROM (SELECT * FROM tableName ) A
WHERE ROWNUM <= 40) B
WHERE B.RN >= 21;
注意：使用区间建议使用闭合区间，如>=,<=这样遍历值会少一个
2.随机查询表数据并返回
select * from tableName where rownum<=10;
3.做数据排名排序
row_number() over() ；
SELECT
row_number() OVER(PARTITION BY 分组依据 ORDER BY 排序规则 DESC)
FROM tableName F ;
oracle数据物理地址rowid
常见用法
1.rowid做数据处理，锁定该行记录进行修改
select p.*,rowid from tableName p ;
处理数据也可以使用select * from tableName for update;–不推荐，会锁表
注意：使用rowid修改数据后，记得要提交事务
2.使用rowid删除掉重复数据，详见另外一篇oracle处理重复数据
链接: oracle处理重复数据.

2.oracle查询相关操作

查询指定时间函数内数据信息
链接: 时间函数解析.
in和not in，exist和not exists
exists是找到exists后等价条件的数据，符合exists数据返回true，不符合返回false(not exists刚好相反)
常见的写法：
select *
from tableA p
where exists – not exists
(select 1 from tableB r where r.id=p.id);
–等价于
select *
from tableA p
where p.id in – not in
(select r.id from tableB r );
join和left join 及 and 使用
使用join则需要符合join后on条件，才可以查询到数据
select * from tableA a join tableB b on a.id=b.id;
小技巧：在使用多数据对单条记录join时，可以使用on 1=1 简化流程
如select * from tableA a join tableB b on 1=1;
使用left join以左表为主，若右表无数据则匹配空数据至左表
select * from tableA a left join tableB b on a.id=b.id;
查询出左边全部记录，右表能查到的取出，不能查到的为null
where 条件针对查询结果集进行过滤 and在on后表示关联关系需同时满足之后查询，不约束结果集，and在where后表示对查询之后结果集进行过滤
select * from tableA a join tableB b on a.id=b.id and a.name=‘暖阳湖光’；
select * from tableA a join tableB b on a.id=b.id where a.name=‘暖阳湖光’；
小技巧：注意，在使用join的on条件关联后，容易出现join左表单条数据关联到右表多条数据，有时候这样的结果并不是我们想要的，如果只是取出右表任意可关联一条可以先对右表做分组数据处理，某些时候如果只用join做关系关联查询，也可以使用exists替代-推荐使用join
如：select * from tableA a join (select b.age,max(b.name) name from tableB b group by b.age ) t
on a.age=b.age;
case when then和nvl
常见用法：
1.数据代码与编译值转换，数据库存入代码code值，需要翻译为中文时
select (case when sex=1 then ‘男’ else ‘女’ end) as ‘性别’ from tableName;
2.数据聚合需要查询出一行数据时，如表中有男女性别，一行求出各多少人
select sum(case p.sex when ‘1’ then 1 else 0 end) 小哥哥,
sum(case p.sex when ‘1’ then 0 else 1 end) 小姐姐
from tableName p;
3.对数据空值处理
select nvl(age,18) from tableName;
4.类似case when then的decode函数
select decode(sex,1,‘男’,‘女’)from tableName;
模糊查询
最简单用的多的模糊查询
select * from tableName where name like ‘%内容%’;–去掉左边或右边百分号表示单边like
分组聚合：分组的目的，去重/使用聚合函数/查询重复数据/我就是想分组,查询重复数据
分组函数意义在于根据字段分组后，满足分组条件的数据为同一组，可以使用聚合函数计算同组数据
未分组的字段不能出现在查询字段后，在于被分组的字段成为单行，未分组的字段成为多行
select name,age from tableName group by name,age;
等价于
select distinct name,age from tableName;–不推荐使用
小技巧：想要使用字段，又没有分组情况下，要么每个字段分组，要么针对需要的字段做聚合函数处理,如判断某个字段涉及的金钱和或最大最小等
如：select name,max(age) from tableName group by name;
查询重复数据
select name from table Name group by name having count(1)>=2;
多列数据合并为一列数据
select max(a.name),
LISTAGG(fessionname, ‘，’) WITHIN GROUP(order by 1) AS fessionnames
from (select p.name,
p.tradename || ‘-’ || p.professionname fessionname
from tableName p
group by p.name, p.tradename, p.professionname) a;
一些不常用但是用到过的函数记录
查询不是中文
select * from tableName p where ASCIISTR(p.name) not LIKE ‘%\%’;
查询包含换行符–从oracle到hive换行符坑死我了
select * from tablep where instr(p.name,chr(10))>0;
换行符替换掉
select replace(’’,chr(10),’’) from dual;

3.oracle数据处理

update后需要where条件限制要更新数据范围，update或delete后观察更新数据量大小是否与预想一致，在执行update前最好先执行下要删除数据的select操作，看下数据量，记得事务提交
update tableName p set p.name=‘暖阳湖光’ where name != ‘暖阳湖光’;
注意：不加where条件会更新全表，容易出现字段查询不到更新条件更新为null的情况
Merge Into 更新 按照on条件做唯一关联，on中条件必须唯一对应 更新速度快，可以做更新插入,详细用法另行百度，用的不多，都用来更新了
Merge Into tableA e
Using (Select b.name from tableB b ) m
On (m.name = e.name )
When Matched Then
Update
Set e.name= Nvl(m.name , 0)
；
使用临时表处理数据可以先备份主表，然后更新主表记录，或使用备份表做好数据后，删除主表数据重新由备份表操作。–停机时候可以这么搞
create table 临时表 as select * from tableName where 1=1;
小技巧：创建同表结构空表后面可以写where 1=2
时间快照用于数据恢复
如果不小心误操作使用delete删除了表数据，可以使用时间快照找回一段时间内的数据量，如果时间过久是不行的，联系运维人员处理。
select * from tablleName as of timestamp to_Date(‘2021-01-11 19:00:00’,‘yyyy-mm-dd hh24:mi:ss’);

4.oracle优化

谈到oracle优化就会很头大，大家都知道一些常用的，直接扣
1.表设计与字段设计，使用分区表与设计合理的表结构对数据库性能有很大的提升
2.最基本最简单的方式是减少访问数据库的次数。oracle在内部执行了许多工作，比如解析SQL语句, 估算索引的利用率, 读数据块等等，都将大量耗费oracle数据库的运行。
3.在执行SELECT子句时尽量避免使用 “”，因为oracle在解析的过程中，会将“” 依次转换成列名, 这是通过查询数据字典完成的, 这将耗费更长的时间。
4.在使用oracle时，尽量多使用COMMIT命令。 该命令可以明显释放运行资源，因此程序的性能得到明显提高
5.ORACLE不区分大小写，默认会转为大写执行，使用大写SQL语句。表名前加用户名增加查询速度
6.优化SQL语句，查看执行计划
7.>=替代>
8.注意函数使用在索引列和索引列字段类型比较要一致
小技巧：多练习，别人问你你就说不会-这句话千万别看懂