Oracle查询与删除表的注意事项

1.删除表

drop table 表名：删除内容和定义，释放空间。

truncate table 表名：删除内容，释放空间，但不删除定义。表被清空后，表和表的索引将重新设置成初始化大小。隐式提交，不能对truncate使用rollback命令。

delete from 表名：删除内容，不删除定义，不释放空间。删除表中的数据是一行一行的删，并在事务日志中为删除的每行记录一项。

释放空间的理解：

delete删除行数据，再增加时ID不连续

truncate删除数据，再增加时ID是连续的

2.having

having子句对group by子句所确定的行组进行控制，having子句条件中只允许涉及常量，聚合函数或group by子句中的列。

3.查找、删除重复记录

1）重复记录是单个字段
 

select *
  from person
 where personId in (select personId
                      from person
                     group by personId
                    having count(personId) > 1);

2）删除重复记录。重复记录是单个字段，只保留rowid最小的记录。

delete from person
 where personId in (select personId
                      from person
                     group by personId
                    having count(personId) > 1)
   and rowid not in (select min(rowid)
                       from person
                      group by personId
                     having count(personId) > 1);

3）查找表中重复的是多个字段。

select *
  from person a
 where (a.personId, a.name) in (select personId, name
                                 from person
                                group by personId, name
                               having count(*) > 1);

4）删除重复记录。重复记录是多个字符，只保留rowid最小的记录。

delete from person a
 where (a.personId, a.name) in (select personId, name
                                 from person
                                group by personId, name
                               having count(*) > 1)
   and rowid not in (select min(rowid)
                       from person
                      group by personId, name
                     having count(*) > 1);

4.返回[n,m]条记录

1）取得某列中第N行的记录

select column_name
  from (select person.*, dense_rank() over(order by column desc) rank
          from person)
 where rank = &N;

2）返回第5~9条记录

A.minus

select *
  from person
 where …
   and rownum < 10
minus
select *
  from person
 where …
   and rownum < 5
 order by name;

B.第二种方法更快

select *
  from (select rownum r, a from person where rownum > 4 order by name)
 where r < 6;