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;