Oracle常用的sql语句

这只是我本人平时常用的sql的整理，顺序也是想到哪里就写到哪里,我举的例子可能也会穿插着别的语法，关键字或者函数的应用，具体使用请各自对应实际情况修改

1.分页查询

select * from (select t.*, rownum r from ( select * from table_name where 1=1 ) t where rownum <=5) where r > 0

2.exists和not exists，这里只举一个例子

select a.* from  table_name1 a where 1=1 and a.column_name is not null and a.column_name <> '0' and not exists (select 1 from 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 group by column_name1

4.over partition by,高级去重语法

select t.* from (
select a.column_name1,b.column_name2,c.column_name3, row_number() over(partition by a.a.column_name1,b.column_name2,c.column_name3  order by c.column_name3 asc)as row_num from table_name1 a,table_name2 b,table_name3 c
 where and a.column_name1 = b.column_name2 and a.column_name1 between date '2021-08-01' and date '2021-08-31'  and c.column_name3 like '?%' 
)t  where t.row_num > 1

5.trunc函数

--trunc函数本身有两种用法，截断数字trunc(number，fmt)或者截断日期trunc(date，fmt)
--截断数字,fmt为截断到哪一位，如果为正数，表示截断到第几位小数，如果为负数，表示要截断小数点前的整数到哪一位，trunc截断不是四舍五入
select trunc(1234.56,-1) from dual
--截断日期，fmt如果是y则为当年的第一天，mm当月的第一天，dd当前年月日，d为当前周的第一天，hh当前时，mi当前分，trunc()函数没有秒的精确
select trunc(sysdate,'y') from dual 

6.to_char函数，to_char是一个强大的函数，有很多博客都有很详细的讲述它，这里我只举我用到过的几个例子

--详细的格式模板网上有
--日期转string
select to_char(sysdate,'HH12:MI:SS') from dual
--保留两位小数并自动补零
select to_char(13199.1,'fm999999.00') from dual
--number转string
select to_char(856.377,'999D99') from dual

7.replace函数，数据有空格和换行的，可以用这种方法进行处理

select replace(replace(column_name1, chr(13), ''), chr(10), '') from table_name where column_name2='???'

8.grant和revoke，数据库的授权撤权

--授予表查询的权限
grant select on tablename to '数据库的名称'
--撤掉表查询的权限
revoke select on tablename to '数据库的名称'

9.查询权限

//查看当前用户所有权限
select * from user_sys_privs;
//查看所用用户对表的权限
select * from user_tab_privs;

10.查询数据库的表名，列名

--查询表名
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 not in ('','')

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' order by t.FIRST_LOAD_TIME desc;
--开启行移动
alter table table_name enable row movement;
--数据恢复到某个操作的时间点之前
flashback table table_name to timestamp to_timestamp('20210812 20:30:55','YYYYMMDD HH24:MI:SS');

12.删除重复数据，保留主键唯一性

delete from table_name1 a where 1=1  and column1='' and column2='' and a.rowid != (
          select max(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=''
 )