1、1Oracle 中用 Rowid 查找和删除重复记录平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 ORA-01452 ：不能创建唯一索引，发现重复记录。下面总结一下几种查找和删除重复记录的方法（以表 CZ 为例）：表 CZ 的结构如下：SQL desc czName Null? Type- C1 NUMBER(10)C10 NUMBER(5)C20 VARCHAR2(3)删除重复记录的方法原理：(1).在 Oracle 中，每一条记录都有一个 rowid，rowid 在整个数据库中是唯一的，rowid 确定了每条记录是在Oracle 中的哪一个数据文件、块、行上。(
2、2).在重复的记录中，可能所有列的内容都相同，但 rowid 不会相同，所以只要确定出重复记录中那些具有最大 rowid 的就可以了，其余全部删除。重复记录判断的标准是：C1,C10 和 C20 这三列的值都相同才算是重复记录。经查看表 CZ 总共有 16 条记录：SQLset pagesize 100SQLselect * from cz;C1 C10 C20- - -1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che2 3 che2 3 che2 3 che3 4 dff23 4 dff3 4 dff
3、4 5 err5 3 dar6 1 wee7 2 zxc20 rows selected.1.查找重复记录的几种方法：(1).SQLselect * from cz group by c1,c10,c20 having count(*) 1;C1 C10 C20- - -1 2 dsf2 3 che3 4 dff(2).SQLselect distinct * from cz;C1 C10 C20- - -1 2 dsf2 3 che3 4 dff(3).SQLselect * from cz a where rowid=(select max(rowid) from cz where c1=
4、a.c1 and c10=a.c10 and c20=a.c20);C1 C10 C20- - -1 2 dsf2 3 che3 4 dff2.删除重复记录的几种方法：(1).适用于有大量重复记录的情况(在 C1,C10 和 C20 列上建有索引的时候，用以下语句效率会很高)：SQLdelete cz where (c1,c10,c20) in (select c1,c10, c20 from cz group by c1,c10,c20 having count(*)1) and rowid not in (select min(rowid) from cz group by c1,c10,
5、c20 having count(*)1);SQLdelete cz where rowid not in (select min(rowid) from cz group by c1,c10,c20);(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：SQLdelete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);SQLdelete from cz a where a.rowiddelete
6、 from cz a where rowid create table test as select distinct * from cz; (建一个临时表 test 用来存放重复的记录）SQLtruncate table cz; (清空 cz 表的数据，但保留 cz 表的结构）SQLinsert into cz select * from test; (再将临时表 test 里的内容反插回来）(4).适用于有大量重复记录的情况(Exception into 子句法)：采用 alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些，
7、为了使用“excepeion into ”子句，必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于 win2000 系统和 UNIX 系统， Oracle 存放该文件的位置稍有不同，在 win2000 系统下，该脚本文件存放在$ORACLE_HOMEOra90rdbmsadmin 目录下；而对于 UNIX 系统，该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下体步骤如下：SQL?/rdbms/admin/utlexcpt.sqlTable created.SQLdesc exceptionsName Null? Ty
8、pe- ROW_ID ROWIDOWNER VARCHAR2(30)TABLE_NAME VARCHAR2(30)CONSTRAINT VARCHAR2(30)SQLalter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions; ERROR at line 1:ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys foundSQLcreate table dups as select * from cz where r
9、owid in (select row_id from exceptions); Table created.SQLselect * from dups; C1 C10 C20- - -1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che1 2 dsf1 2 dsf1 2 dsf1 2 dsf2 3 che2 3 che2 3 che2 3 che3 4 dff43 4 dff3 4 dff16 rows selected.SQLselect row_id from exceptions;ROW_ID-AAAHD/AAIAAAADSAAAAAAHD/AAIAAAADSAABA
10、AAHD/AAIAAAADSAACAAAHD/AAIAAAADSAAFAAAHD/AAIAAAADSAAHAAAHD/AAIAAAADSAAIAAAHD/AAIAAAADSAAGAAAHD/AAIAAAADSAADAAAHD/AAIAAAADSAAEAAAHD/AAIAAAADSAAJAAAHD/AAIAAAADSAAKAAAHD/AAIAAAADSAALAAAHD/AAIAAAADSAAMAAAHD/AAIAAAADSAANAAAHD/AAIAAAADSAAOAAAHD/AAIAAAADSAAP16 rows selected.SQLdelete from cz where rowid in
11、 ( select row_id from exceptions);16 rows deleted.SQLinsert into cz select distinct * from dups;3 rows created.SQLselect *from cz;C1 C10 C20- - -1 2 dsf2 3 che3 4 dff4 5 err5 3 dar6 1 wee7 2 zxc7 rows selected.从结果里可以看到重复记录已经删除。Oracle 查询删除表中重复记录最高效删除重复记录的方法，使用为例 rowid5DELETE FROM EMP E WHERE E.ROWID
12、(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 rowid 最小的记录delete from people where peopleId in (
13、select peopleId from people group by peopleId having count(peopleId) 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )1)注:rowid 为 oracle 自带不用该.3、查找表中多余的重复记录（多个字段）select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peop
14、leId,seq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有 rowid 最小的记录delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)5、查找表中多余的重复记录（多个字段），不包含 rowid 最
15、小的记录select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)(二)比方说 在 A 表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；Sele
16、ct Name,Count(*) From A Group By Name Having Count(*) 1如果还查性别也相同大则如下:Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) 1查询及删除重复记录的 SQL 语句1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId)
17、1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 rowid 最小的记录delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )1)注:rowid 为 oracle 自带不用该.3、查找表中多余的重复记录（多个字段）
18、select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有 rowid 最小的记录delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)and rowid not in (selec
19、t min(rowid) from vitae group by peopleId,seq having count(*)1)65、查找表中多余的重复记录（多个字段），不包含 rowid 最小的记录select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having co
20、unt(*)1)(二)比方说在 A 表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；Select Name,Count(*) From A Group By Name Having Count(*) 1如果还查性别也相同大则如下:Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) 1declare max integer,id integerdeclare cur_rows cursor local for select
21、 主字段,count(*) from 表名 group by 主字段 having count(*) 1open cur_rowsfetch cur_rows into id,maxwhile fetch_status=0beginselect max = max -1set rowcount maxdelete from 表名 where 主字段 = idfetch cur_rows into id,maxendclose cur_rowsset rowcount 0 方法二重复记录有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如 Nam
22、e 字段重复，而其他字段不一定重复或都重复可以忽略。1、对于第一种重复，比较容易解决，使用7select distinct * from tableName就可以得到无重复记录的结果集。如果该表需要删除重复的记录（重复记录保留 1 条），可以按以下方法删除select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如
23、下假设有重复的字段为 Name,Address，要求得到这两个字段唯一的结果集select identity(int,1,1) as autoID, * into #Tmp from tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2)最后一个 select 即得到了 Name，Address 不重复的结果集（但多了一个 autoID 字段，实际写时可以写在select
24、子句中省去此列）select * from tablename where id in (select id from tablenamegroup by idhaving count(id) 1)Oracle 数据库中表的四种连接方式讲解表的连接是指在一个 SQL 语句中通过表与表之间的关连,从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析 Oracle 表的四种不同连接方式:1. 相等连接通过两个表具有相同意义的列，可以建立相等连接条件。8只有连接列上在两个表中都出现且值相等的行才会出现在查询
25、结果中。例 查询员工信息以及对应的员工所在的部门信息;SELECT * FROM EMP,DEPT; SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;REM 显示工资超过 2000 的员工信息以及对应的员工的部门名称。2. 外连接对于外连接，Oracle 中可以使用“(+)”来表示，9i 可以使用 LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。除了显示匹配相等连接条件的信息之外,还显示无法匹配相等连接条件的某个表的信息。外连接采用(+)来识别。A) 左条件(+) = 右条件;代表除了显示匹配相等连接条件
26、的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。此时也称为“右外连接“.另一种表示方法是:SELECT . FROM 表 1 RIGHT OUTER JOIN 表 2 ON 连接条件B) 左条件 = 右条件(+);代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。此时也称为“左外连接“.SELECT . FROM 表 1 LEFT OUTER JOIN 表 2 ON 连接条件例 显示员工信息以及所对应的部门信息-无法显示没有部门的员工信息-无法显示没有员工的部门信息-SELECT * FROM EMP,DEPT WHERE EMP.DEPT
27、NO = DEPT.DEPTNO;-直接做相等连接:SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;9REM 显示员工信息以及所对应的部门信息,显示没有员工的部门信息-SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO; SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;REM 显示员工信息以及所对应的部门信息,显示没有部门的员工信息-SELECT * FROM EMP,DEPT WH
28、ERE EMP.DEPTNO = DEPT.DEPTNO(+); SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;3. 不等连接两个表中的相关的两列进行不等连接,比较符号一般为,.,BETWEEN ANDREM SALGRADE -DESC SALGRADE; -SELECT * FROM SALGRADE;REM 显示员工的编号,姓名,工资,以及工资所对应的级别。SELECT EMPNO,ENAME,SAL,SALGRADE.* FROM SALGRADE,EMP WHERE EMP.SAL BETWEEN
29、 LOSAL AND HISAL;REM 显示雇员的编号,姓名,工资,工资级别,所在部门的名称;SELECT EMPNO,ENAME,SAL,GRADE,DNAME FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL BETWEEN LOSAL AND HISAL;4. 自连接自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。下面介绍一下自连接的方法:将原表复制一份作为另一个表,两表做笛卡儿相等连接。例 显示雇员的编号,名称,以及该雇员的经理名称SE
30、LECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;10Oracle 导入和导出工具使用Oracle 提供两个工具 imp.exe 和 exp.exe 分别用于导入和导出数据。这两个工具位于 Oracle_home/bin 目录下。导出数据 exp1 将数据库 ATSTestDB 完全导出，用户名 system 密码 123456 导出到 c：export.dmp 中exp system/123456ATSTes
31、tDB file=c：export.dmp full=y其中 ATSTestDB 为数据库名称，system 为该数据库里的账户，123456 为其密码。2 将数据库中 system 用户与 sys 用户的所有相关资源导出（表，存储过程，方法，视图等等）exp system/123456ATSTestDB file= c：export.dmp owner=（system，sys）3 将数据库中的表 sys.table1、owbsys.table2 导出exp system/123456ATSTestDB file= c：export.dmp tables=（ sys.table1， owbsy
32、s.table2）注意，需要加上表的 schema 名称，如果没有加的话默认是导当前 connected 用户的表，当然你连接上去的账户要对相应的表有权限。4 将数据库中的表 table1 中的字段 Title 以“GangGe“打头的数据导出exp system/123456ATSTestDB file= c：export.dmp tables=（table1） query=“ where Title like GangGe%“斜杠 “ 后面跟冒号是为了转义字符冒号“ “ “用的，因为后面是一条条件查询语句。Query 参数只能指定一个，如果 Query 要为多张表，所以同样，tables
33、里面也只能有一张表，或者多张表，然后 query 里面的条件在这些表上面都可以运行。否则只好多写几条 exp 语句了。导出后，或许发现数据比较大，我们可以用一些压缩工具对数据进行二次压缩，例如用 winzip， winrar， 7zip 等第三方工具。同样，exp 支持一个参数用户直接对数据进行压缩：comPRess = y， 这个参数直接加到命令的后面即可实现导出的同时压缩数据。导入数据 imp我们知道怎么提取数据，那么还原的时候，就需要使用 imp 命令把导出的数据加载进去。1 向 ATSTestDB 里面加载 c：export.dmp 数据imp system/123456ATSTest
34、DB file=c：export.dmp好了，导数据得时候，有可能报错了。为什么？有两种主要的原因：A. 导入的对象（表，视图，方法等）原本不属于当前连接的用户的B. 导入的对象在该数据库的指定用户下已经存在C. 导入的对象的原本用户不再这个数据库里所有对象全部导入到指定的账户下：Imp system/123456ATSTestDB file=c：export.dmp fromuser=sys touser=system其中 fromuser=sys 为。dmp 文件里的对象的原先的 owner， touser=system 为作为导入的对象的新的 Owner.忽略/插入数据Imp syste
35、m/123456ATSTestDB file=c：export.dmp ignore=y其中 ignore=y 告诉 imp.exe 把数据直接插入到相应对象（并且如果导入的对象里面有其他的对象，如约束，索引等，会在数据插入后被创建）。2 加载其中的指定表 table1，table2imp system/123456ATSTestDB file=C：export.dmp tables=（table1，table2）3 忽略加载约束有时候导数据进来的时候，我们不需要把它的约束，比如一些外键约束等都导进来，可以加上参数constraints=Nimp system/123456ATSTestDB
36、file=C：export.dmp tables=（table1，table2） constraints=N4 不加载索引（比如唯一性的索引）imp system/123456ATSTestDB file=C：export.dmp tables=（table1，table2） indexs=N5 只加载结构，不加载数据如果只要表的结构等定义（约束，触发器），那么不要里面的数据，可以加上参数 rows=N11imp system/123456ATSTestDB file=C：export.dmp tables=（table1，table2） rows=N对于上述操作登陆操作的对象 system 是管理员，如果不是管理员，而是普通用户，那么这个用户必须有创建删除对象的权利，对象可能包括 表，视图，方法，存储过程等等常见的对象。为什么“可能“包括？因为这个视导入导出的时候是否涉及相关类型的对象而定。至此，以上简单的 Oracle 导入导出数据的命令掌握了吗？相信够用了。
