oracle的简单使用方法总结

时间:2024-03-25 17:34:41

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

1.ORACLE 数据泵导入导出

2.SQLLDR 加载文件

3.ORACLE 常用函数/关键字

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

  1. 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加载文件
  1. Sqlldr在哪

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

  1. Sqlldr是什么,能干什么

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

  1. 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   代替->首先清空表中的数据,重新进行写入

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

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

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

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

 

  1. 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)

 

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

 

  1. 使用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常用函数/关键字

 

  1. MINUS(差集)

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

  1. UNION/UNION ALL

Union:去重。

Union all :不去重

  1. INTERSECT(交集)

select * from TEST_A t INTERSECT select * from TEST_B t

  1. 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'

 

  1. 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]

  1. ROWNUM
  2. 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

  1. ROWID

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

  1. 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

 

  1. NULL值

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

影响索引

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

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

使用!=, NOT IN

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

 

 

 

  • ORACLE常用分析函数

 

  1. ROW_NUMBER OVER()(同分不同排名)

oracle的简单使用方法总结

  1. 1

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      

  1. SUM():求和