select*from(select t.*, rownum r from(select*from table_name where1=1) t where rownum <=5)where r >0
2.exists和not exists,这里只举一个例子
select a.*from table_name1 a where1=1and a.column_name isnotnulland a.column_name <>'0'andnotexists(select1from table_name2 b where a.column_name=b.column_name)
3.group by
select column_name1,count(column_name1) form table_name where column_name2 like'%?'from table_name groupby column_name1
4.over partition by,高级去重语法
select t.*from(select a.column_name1,b.column_name2,c.column_name3, row_number()over(partitionby a.a.column_name1,b.column_name2,c.column_name3 orderby c.column_name3 asc)as row_num from table_name1 a,table_name2 b,table_name3 c
whereand a.column_name1 = b.column_name2 and a.column_name1 betweendate'2021-08-01'anddate'2021-08-31'and c.column_name3 like'?%')t where t.row_num >1
--查询表名select table_name from user_tables where table_name like upper('l%')--查询列名select column_name from user_tab_columns where table_name =''and column_name notin('','')
11.恢复被delete或者update的数据
--查询某段时间内的操作执行记录select t.FIRST_LOAD_TIME, t.SQL_TEXT from v$sqlarea t where to_char(t.FIRST_LOAD_TIME)>'2021-08-12/20:00:00'orderby t.FIRST_LOAD_TIME desc;--开启行移动altertable table_name enablerow movement;--数据恢复到某个操作的时间点之前
flashback table table_name totimestamp to_timestamp('20210812 20:30:55','YYYYMMDD HH24:MI:SS');
12.删除重复数据,保留主键唯一性
deletefrom table_name1 a where1=1and column1=''and column2=''and a.rowid !=(selectmax(b.rowid)from table_name2 b
where a.column1= b.column1
and a.column1= b.column1
and a.column1= b.column1
and b.column2=''and column2='')