Oracle 之sql语句（一）

select * from XLSB_BMGJINFO where f_gid like '13123%' and f_xbh in ('3017','2688')
and f_xbmc= '双'

 select * from ( select * from operatelog order by operatetime desc)  where rownum<=20            调用APP端接口的日志记录

select * from t_importinfo
order by create_time desc;

select * from rl_user where unit_id ='1312620' 

select * from view_01_unit where unitname like'永安%'

授权语句==========================

grant select any table to 被授权用户
例子： grant select any table to TKYPTGL
=================================查询授权语句=============================

//授权表（在PW用户上执行）  
select 'grant select, insert, update, delete on PW.'|| table_name ||' to TKYZHFXGL;' from user_tables 

//授权视图（在PW用户上执行）
select 'grant select, insert, update, delete on PW.'|| view_name ||' to TKYZHFXGL;' from user_views 

执行后会生成对所有表和所有视图的赋权限语句，拷贝出来执行就可以了

====================还原/备份数据库================================

 导出数据库   exp hkb/hkb@boss_14 full=y file=c:\orabackup\hkbfull.dmp 
    -----zhaosq  2018/11/08    (full=y ignore=y) 可省略
exp TRACKPAD/Track$PAD@PV-X00138889:1521/ORCL file='E:\dbbackup\20181108\TRACKPAD.dmp'  full=y ignore=y

  还原数据库
    用户名/密码
imp bjtest/bjtest@47.92.114.182:1521/TEST file='E:\办公资料\导入数据\2018-12-4\bjj_tz.dmp' full=y ignore=y
============================创建同义词=========================

 CREATE SYNONYM T_GGSS_OPERATOR FOR TKYZHFXGL.T_GGSS_OPERATOR;
 drop SYNONYM T_GGSS_OPERATOR
 select * from T_GGSS_OPERATOR

=========================创建表用户

CREATE TABLESPACE bjtest
DATAFILE 
'd:/ncxdb/bjtest.dbf'  size 100M  reuse
AUTOEXTEND ON NEXT 60M MAXSIZE UNLIMITED;  

CREATE TABLESPACE shtest
DATAFILE 
'd:/ncxdb/shtest.dbf'  size 100M  reuse
AUTOEXTEND ON NEXT 60M MAXSIZE UNLIMITED; 

--REM 创建TKYJCSJGL用户
-- Create the user 
create user bjtest
  identified by "bjtest"
  default tablespace bjtest
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to bjtest;
grant exp_full_database to bjtest;
grant imp_full_database to bjtest;
grant resource to bjtest;
-- Grant/Revoke system privileges 
grant create any materialized view to bjtest;
grant create any table to bjtest;
grant delete any table to bjtest;
grant drop any materialized view to bjtest;
grant insert any table to bjtest;
grant select any table to bjtest;
grant unlimited tablespace to bjtest;
grant update any table to bjtest;

-- Create the user 
create user shtest
  identified by "shtest"
  default tablespace shtest
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to shtest;
grant exp_full_database to shtest;
grant imp_full_database to shtest;
grant resource to shtest;
-- Grant/Revoke system privileges 
grant create any materialized view to shtest;
grant create any table to shtest;
grant delete any table to shtest;
grant drop any materialized view to shtest;
grant insert any table to shtest;
grant select any table to shtest;
grant unlimited tablespace to shtest;
grant update any table to shtest;

===========================  查询上一周日期========================================
select (trunc(sysdate-7,'DD')-to_char(sysdate-7,'D')+2) , (trunc(sysdate-7,'DD')-to_char(sysdate-7,'D')+8) from dual 

=================================取整数值==================================================

select trunc(251.75),round(251.75),floor(251.000001),ceil(251.000001) from dual;

结果： 251 252  251 252

===========================删除重复数据，保留一条数据=========================================
   delete from table where xxx in (select xxx from table group by xxx having count(xxx) > 1) and rowid not in (select min(rowid) from table group by xxx having count(xxx) > 1)

====================为用户赋予管理员权限==========================
grant dba to username;
====================================树形图查询方式==================================
select * from table start with f_id='1119' connect by prior f_id=f_pid;