oracle的简单使用方法总结

本篇博客主要总结以下内容：

1.ORACLE 数据泵导入导出

2.SQLLDR 加载文件

3.ORACLE 常用函数/关键字

(MINUSE、UNION/UNION ALL、INTERSECT、LISTAGG、NULL值的坑、MERGE INTO、ROWNUM、ROWID、CONNECT BY START WITH)

ORACLE 常用分析函数(ROW_NUMBER、LAG/LEAD、SUM()OVER()))

之前自己对oracle的应用也只是简单的把他当成了另一种表达形式的MySQL,没有仔细研究过Oracle的特性，此处正好对oracle的相关特性做一下总结。

ORACLE数据泵导入导出

导出：

1、从cmd中以dba权限登录数据库：sqlplus system/密码@orcl as sysdba;

2、创建目录： Create directory TEST as 'd:\test';

3、授权：Grant read,write on directory TEST to yxf;

4、对应第二步所创建的目录，在数据库服务端的d盘新建文件夹test；

5、退出数据库登录：quit；

6、导出：

Ø 单个用户方案导出

Expdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=TEST dumpfile=X.dmp logfile=X.log

Ø 数据库全库导出

Expdp [用户名]/[密码]@[主机字符窜] full=y directory=TEST dumpfile=X.dmp logfile=X.log

执行完以上两步后，即可在d://test文件夹下看到导出的数据文件X.dmp，以及导出日志X.log

导入：与导出的前面5步相同

1、从cmd中以dba权限登录数据库：sqlplus system/密码@orcl as sysdba;

2、创建目录： Create directory TEST as 'd:\test';

3、授权：Grant read,write on directory TEST to yxf;

4、对应第二步所创建的目录，在数据库服务端的d盘新建文件夹test；

5、退出数据库登录：quit；

6、将待导入的数据文件放在导入数据库服务端的d盘下test文件夹中；

7、导入：

Ø 单个用户方案导入

impdp [用户名]/[密码]@[主机字符窜] schemas=[用户名] directory=TEST dumpfile=X.dmp logfile=X.log ignore=y

Ø 数据库全库导入

impdp [用户名]/[密码]@[主机字符窜] full=y directory=TEST dumpfile=X.dmp logfile=X.log ignore=y

SQLLDR加载文件

Sqlldr在哪

sqlldr加载文件主要依赖sqlldr.exe进行操作，该文件和sqlplus.exe位于同一目录中，我的文件位置为：E:\oracle\product\11.2.0\dbhome_1\BIN\文件夹下

Sqlldr是什么，能干什么

Sqlldr为SqlLoader的简写，主要是负责将指定文件中的数据按照指定的策略方式，导入到数据库中。当然，这一步骤通过使用java等【按行读取文件->根据分隔符split->实例化对象或填充Map->调用持久层框架将数据持久化到数据库中】来实现相同的功能。两者的区别在于，java更轻量级，在小数据量时更轻便灵活，且适配任意数据库。而sqlldr更重量级，配置相较于java略加繁琐，只针对Oracle数据库，但效率却更高。

Sqlldr的格式1

Sqlldr致力于导入外部文件中的数据，因此sqlldr要求需要一个后缀名为.ctl的控制文件。该文件中定义一些对文件的读取策略。.ctl文件中必须包含策略格式。

一个简单的.ctl文件如下

LOAD DATA

INFILE *

APPEND INTO TABLE DATA

FIELDS TERMINATED BY ","

(COLUMN1,COLUMN2,COLUMN3,COLUMN4)

BEGINDATA

1,1,1,1

2,2,2,2

3,3,3,3

4,4,4,4

第二行的星号*表示数据文件在该控制文件中，如果将数据单独分开作为一个文件，这里将换为路径（具体见第三点）

第三行INTO TABLE DATA中的DATA是需要导入数据的表名，最前方是数据插入的方法：

INSERT（缺省时默认取值）表在非空的时候进行数据插入的操作

oracle的简单使用方法总结

APPEND 向表中追加数据

REPLACE 代替->首先清空表中的数据，重新进行写入

第四行定义每个字段之间的分割方式。

第五行提供了对齐的列名。

第六行是数据开始的标志。

接下来每一行都是按行待处理的数据。

Ctl的格式2（独立数据文件与控制文件）

LOAD DATA

INFILE 'C:\Users\dell\Desktop\Oracle\Project\2019.03.18\loadData.dat'

APPEND INTO TABLE DATA

FIELDS TERMINATED BY ","

(COLUMN1,COLUMN2,COLUMN3,COLUMN4)

相比与第一种，在第二行中绝对定位到数据文件的位置。其他地方和第一种相似。

使用sqlldr

格式：SQLLDR 用户名/密码 CONTROL=文件路径，

其中，这里的文件路径不用加引号。

示例：sqlldr userid=XREPORT/[email protected] control=E:\ctlcfg\CUSM.ctl

使用sqlldr导入数据时，也会产生执行日志和错误日志等，可手动定位日志路径

log=E:\ctlcfg\0191460D.i2220251215.log bad=E:\ctlcfg\0191460D.i2220251215.bad。

Log定义success日志，bad存放错误日志。

组合在一起后格式为：sqlldr userid=XREPORT/[email protected] control=E:\ctlcfg\CUSM.ctl log=E:\ctlcfg\0191460D.i2220251215.log bad=E:\ctlcfg\0191460D.i2220251215.bad。

ORACLE常用函数/关键字

MINUS（差集）

使用示例：select * from TEST_A t MINUS select * from TEST_B t

UNION/UNION ALL

Union：去重。

Union all ：不去重

INTERSECT（交集）

select * from TEST_A t INTERSECT select * from TEST_B t

LISTAGG（类比group_concat）

代码示例：

使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)

SELECT T .DEPTNO, listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names FROM

SCOTT.EMP T WHERE T .DEPTNO = '20' GROUP BY T .DEPTNO

使用 listagg() within GROUP () over 将多行记录在一行显示(没有遇到过这种使用场景)

SELECT T .DEPTNO, listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) over(PARTITION BY T .DEPTNO) FROM SCOTT.EMP T WHERE T .DEPTNO = '20'

MERGE INTO（类比insert on duplicate key）

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)

WHEN MATCHED THEN

[UPDATE sql]

WHEN NOT MATCHED THEN

[INSERT sql]

ROWNUM
ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除，接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。

更多ROWNUM总结，可参考：https://blog.csdn.net/qq_39196949/article/details/84379874

ROWID

oracle数据库的表中的每一行数据都有一个唯一的标识符，或者称为rowid，在oracle内部通常就是使用它来访问数据的。

CONNECT BY START WITH（递归查询）

select * from table [start with condition1]

connect by [prior] id=parentid

一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。

start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。

connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。

更多递归查询可参考:https://blog.csdn.net/wang_yunj/article/details/51040029

NULL值

Null值不计数count。count()，遇到null值时，这条记录不会计算在内；CONCAT(a,null)结果为null等。

影响索引

给java开发带来空指针隐患。

Null值相比not null所占空间更大。

使用！=, NOT IN

自己在对于可空字段进行判断时，要判断not null and 字段!=”” ，更繁琐。

ORACLE常用分析函数

ROW_NUMBER OVER()（同分不同排名）

oracle的简单使用方法总结

lag 和lead 可以获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值）

示例：

SQL> select * from kkk;

ID NAME

---------- --------------------

1 1name

2 2name

3 3name

4 4name

5 5name

SQL> select id,name,lag(name,1,0) over ( order by id ) from kkk;

ID NAME LAG(NAME,1,0)OVER(ORDERBYID)

---------- -------------------- ----------------------------

1 1name 0

2 2name 1name

3 3name 2name

4 4name 3name

5 5name 4name

SUM()：求和

oracle的简单使用方法总结

相关推荐