文档章节

Oracle学习笔记整理手册

smileNicky
 smileNicky
发布于 2018/12/02 22:38
字数 1988
阅读 3
收藏 0

@[toc] PS:本博客收录自己工作中遇到学到的一些Oracle技能,有时间就更新整理一下

(1)Oracle正则匹配使用

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

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

(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;

(3)Oracle表数据回滚语句

【表数据恢复回N分钟之前】

alter table 表名 enable row movement; 
flashback table 表名 to timestamp systimestamp - interval 'N' minute; 

(4)sql筛选出记录数大于2的记录

select id,count(*) from A a
where a.test like '%'
group by id
having count(*)>=2

(5)oracle同义词

创建同义词:
create public synonym tablename for user.tablename;
synonyms

(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)

(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 

(16)条件sql

case when then else end 可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候,可以更多的条件和自定义逻辑 列子:

case when (select count(1) from t_unit_info 
					where para_unit_code = unitInfo.unit_code) > 0 
					then 'closed' else 'open' end as state

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

(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

(22) 查询某张表约束信息

select * from user_constraints t where t.table_name = '表名'

(23)decode函数

例子:下面的sql就可以加在where后面,sql意思就是t.project_Name为空的情况#{projectName}=#{projectName},也即1=1,就是不做任何操作;不为空的情况,t.project_Name=#{projectName}

decode(t.project_Name,null,#{projectName},t.project_Name) = #{projectName}

© 著作权归作者所有

smileNicky
粉丝 12
博文 19
码字总数 24361
作品 2
西城
程序员
私信 提问
2018年4月下载中心资源下载TOP榜

2018年4月1日至4月30日下载中心资源下载TOP榜 A、系统运维 NO.1 下载数:716 资源标题:Windows Server 2016管理员操作手册 资源地址:http://down.51cto.com/data/2445174 NO.2 下载数:486...

51CTO下载
2018/05/02
0
0
【分享】大量软件编程笔记

我们团队是在做java的,有很多教学和学习经验,整理了了一些比较适用于初学者的,高手们也可以提提意见 在开始这个计划前,我想还是想和大家分享一下我对未来Java之路的憧憬,虽然Java的母公...

5566jhms
2015/12/22
140
2
开源电子书

目录 语言无关类 操作系统 智能系统 分布式系统 编译原理 函数式概念 计算机图形学 WEB服务器 版本控制 编辑器 NoSQL PostgreSQL MySQL 管理和监控 项目相关 设计模式 Web 大数据 编程艺术 ...

zting科技
2017/12/11
0
0
编程类开放书籍荟萃(转载)

关于开源图书有人在网络上做了大量整理,本文为大家刊载《免费的编程中文书籍索引》 国外程序员在 stackoverflow 推荐的程序员必读书籍,中文版。 stackoverflow 上的程序员应该阅读的非编程...

行者PHPer
2016/10/09
168
0
Fanta/free-programming-books-zh_CN

免费的编程中文书籍索引 免费的编程中文书籍索引,欢迎投稿。 国外程序员在 stackoverflow 推荐的程序员必读书籍,中文版。 stackoverflow 上的程序员应该阅读的非编程类书籍有哪些? 中文版...

Fanta
2016/11/14
0
0

没有更多内容

加载失败,请刷新页面

加载更多

java通过ServerSocket与Socket实现通信

首先说一下ServerSocket与Socket. 1.ServerSocket ServerSocket是用来监听客户端Socket连接的类,如果没有连接会一直处于等待状态. ServetSocket有三个构造方法: (1) ServerSocket(int port);...

Blueeeeeee
今天
6
0
用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
5
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
16
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
18
0
jquery--DOM操作基础

本文转载于:专业的前端网站➭jquery--DOM操作基础 元素的访问 元素属性操作 获取:attr(name);$("#my").attr("src"); 设置:attr(name,value);$("#myImg").attr("src","images/1.jpg"); ......

前端老手
昨天
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部