本文共 5076 字，大约阅读时间需要 16 分钟。

文章目录

(1)Oracle正则匹配使用 (2)Oracle修改有数据的数据字段类型 (3)Oracle表数据回滚语句 (4)sql筛选出记录数大于2的记录 (5)oracle同义词 (6)oracle内外连接 (7)oracle去重关键字 (8)Oracle不等于符号 (9)1=1和1=0 (10)创建表空间 (11)Oracle in关键字和等于符号 (12)NVL函数 (13)union (14)exist和in (15)递归查询 (16)条件sql (17)oracle数据库还原 (18)重导dmp文件 (19)vm_concat函数 (20)replace函数 (21) instr函数

PS：本博客收录自己工作中遇到学到的一些Oracle技能，有时间就更新整理一下

(1)Oracle正则匹配使用 PS:这条SQL可以通过正则匹对查询一下，表A的字段a是否有非数字的数据，有时候数据表的一些字段是varchar类型的，如果sql里用to_number的话，就肯定会报无效数字的错误

select * from 表A where not regexp_like(字段a,'^[[:digit:]]+$');

1 (2)Oracle修改有数据的数据字段类型 有些时候，我们是要修改一些数据字段的类型的，mysql是可以支持的，不过Oracle对一些有数据的数据字段是不予许修改的，这时候，可以通过下面sql进行修改，大概思路就是创建一个临时的，负责转运数据，然后再切换

alter table 表A add new_temp varchar2(50);

update 表A set new_temp = a; commit; update 表A set a = ''; commit; alter table 表A modify a varchar2(50); update 表A set a = new_temp; commit; alter table 表A drop column new_temp; 1 2 3 4 5 6 7 8 9 (3)Oracle表数据回滚语句 【表数据恢复回N分钟之前】

alter table 表名 enable row movement; 

flashback table 表名 to timestamp systimestamp - interval 'N' minute;  1 2 3 4 (4)sql筛选出记录数大于2的记录 select id,count(*) from A a where a.test like '%' group by id having count(*)>=2 1 2 3 4 (5)oracle同义词 创建同义词： create public synonym tablename for user.tablename; synonyms 1 2 3 (6)oracle内外连接 Oracle左右连接可以使用(+)，+号在左表示右外连接，在右表示左外连接

(7)oracle去重关键字

Oracle关键字： distinct：过滤掉多余的重复记录只保留一条

(8)Oracle不等于符号

oracle不等于有下面三种表示： !=、^=、<> <>可移植性好

(9)1=1和1=0

select * from t where 1=1 1=1条件始终为true，是为了避免负责的条件查询出现错误 1=0条件始终未false，结果不会返回任何数据，所以可以用于建表，create table newtable as select * from oldtable where 1=0;

(10)创建表空间

–创建表空间 CREATE SMALLFILE TABLESPACE “WORKFLOW01” LOGGING DATAFILE ‘D:\app\Administrator\oradata\oanet\WORKFLOW01.ora’ SIZE 245M EXTENT MANAGEMENT LOCAL SEGMENT SPACE Management AUTO

(11)Oracle in关键字和等于符号

oracle in 和 =的区别 in可以有多条记录，=只有一条记录 =性能更好，因为使用=符合能更好地使用索引

(12)NVL函数

NVL函数的格式如下：NVL(expr1,expr2) 含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。SQL> select ename,NVL2(comm,-1,1) from emp;

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

格式如下： Coalesce(expr1, expr2, expr3…… exprn) 表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

(13)union

union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

(14)exist和in

select a.id from A a where a.id in(select b.id from B b)

select a.id from A a where exists(select b.id from B b where a.id = b.id)

1 2 3 (15)递归查询 start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历

https://www.cnblogs.com/benbenduo/p/4588612.html

ps:例子，递归查询一般用于递归查单位，地区信息等等什么的。start with后可以加一些开始条件，没有就写1=1或者不写也可以的。id和parent_id根据业务需要替换。

select * from 表格1 start with 1=1 connect by id = prior parent_id 

1 (16)条件sql case when then else end 可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候，可以更多的条件和自定义逻辑

select 1 from mytable;与select anycol(目的表集合中的任意一行） from mytable;与select * from mytable 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。select 1 from 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

(17)oracle数据库还原

1、将TESTDB.bmp放置在dpdump目录，如下路径： E:\app\Administrator\admin\orcl\dpdump

2、创建用户

create user testdb65 identified by “testdb65” default tablespace users temporary tablespace temp; 用户授权 grant connect,dba to testdb65;

3、创建表空间

CREATE TABLESPACE HR_DATA01 DATAFILE ‘D:\nhsj\hr_data01.dbf’ SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 【创建表空间】

4、导入 bgyhr2是来源库

impdp testdb65/testdb65@orcl schema=testdb65 dumpfile=TESTDB65.dmp

导出 scott 是要导出的库

expdp lfwtrain/a@orcl schema=当前用户 dumpfile=expdptest.dmp

(18)重导dmp文件

Oracle保存数据时一般以DMP文件的形式进行保存 本博客记录，DMP文件的导入 先打开CMD sqlplus /nolog 以sysdba连接 conn /as sysdba 这里创建表空间，因为表空间进行存储的话，可以提供系统性能 CREATE TABLESPACE DATA01 DATAFILE 'D:\tablespace\data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 创建用户 CREATE USER job IDENTIFIED BY job DEFAULT TABLESPACE DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON DATA01 授权 grant connect,dba to job; 复制dmp文件到oracle安装目录里的app\admin\orce\dpdump里，然后用以下代码导入dmp文件，要顺便导入logfile的话，加上logfile=文件名 impdp job/job@orcl schemas=job dumpfile=EXPDP160810GZ.dmp

(19)vm_concat函数

函数wm_concat(列名)，该函数可以把列值以","号分隔起来,并显示成一行 ps:这个列转行函数vm_comcat()在oracle10比较稳定，到了oracle11就会出现一些不兼容的情况，oracle12版本就没这个函数了

(20)replace函数

REPLACE(char, search_string [, replacement_string ] )

char : 等待替换的字符串

search_string : 搜索需要替换的字符串

replacement_string : 替换字符串

如果replacement_string缺省或者为null,那么所有char中出现的search_string 都将被移除

如果search_string为null,那么结果就是char

例子：过滤某个字段的换行符

select replace(a, chr(10)) from tableA

1 (21) instr函数 格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

例子：查询某个数据字段是否包含换行符

select * from tableA where instr(a,chr(10))>0

---------------------  作者：smileNicky  来源：CSDN  原文：https://blog.csdn.net/u014427391/article/details/82317376  版权声明：本文为博主原创文章，转载请附上博文链接！