oracle常用基本语句（持续更新）

-基本连接操作

sqlplus scmgt/scmgt as sysdba连接oracle数据库 //scmgt scmgt

sqlplus "scmgt/scmgt @testdb"  连接另外个数据库
SELECT NAME FROM V$database;   查看当前数据库名
select TABLE_NAME from all_tables;  查看所有表

-获取最新的10条数据

select * from (select * from CH_T_SONG_INFO order by seq desc) where rownum<=10;


-比较A表跟B表的主键有多少不同
select count(*) from (select ALBUM_ID from CH_T_ALBUM_INFO_0326 where ALBUM_ID not in ( select ALBUMID from ARTIST_ALBUM) );


-备份表
create table CH_T_MUSIC_SOURCE_INFO_0319 as select * from CH_T_MUSIC_SOURCE_INFO


-重命名表
rename CH_T_MUSIC_SOURCE_INFO to CH_T_MUSIC_SOURCE_INFO_new;


-if判断
SINGGER_ID= DECODE(SINGGER_ID,'',''||VROW.ARTISTID,SINGGER_ID||','||VROW.ARTISTID)


-日期
跟当前时间比较
to_date(t.INVALID_DATE,'yyyy-mm-dd hh24:mi:ss')<SYSDATE;
2个时间比较
select count(*) from CH_T_SONG_INFO_ACTION_201303 t where t.insert_date>to_date('2013-03-20 17:00:45','yyyy-mm-dd hh24:mi:ss');




删除重复数据保留一条
DELETE from CH_T_MICROBLOG_PRODUCT_RES
WHERE (COPYRIGHT_ID) IN (select COPYRIGHT_ID from CH_T_MICROBLOG_PRODUCT_RES  where  status=0 and inc=0 and action ='9' group by COPYRIGHT_ID having count(*)>1)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM  CH_T_MICROBLOG_PRODUCT_RES  where  status=0 and inc=0 and action ='9' group by COPYRIGHT_ID having count(*)>1);

分页语句

select a.* from (select c.*,rownum num from CH_T_PRODUCT_CRBT c where c.status<>'2' and rownum<="+endIndex+") a where num>"+startIndex

获取序列号语句

select SEQ_FTP_MUSIC_FILE_RES.nextval from dual

获得表的创建语句
set pagesize 0;
set long 90000;
set feedback off;
set echo off;
spool get_schema.sql;
select dbms_metadata.get_ddl('TABLE','CH_T_SONG_INFO') from dual;  

改变字段

alter table CH_T_SINGGER_INFO modify(SINGGER_ID VARCHAR2(1024));

删除,创建索引

DROP INDEX IDX_ALM_INFO_ALMID ;

create index IDX_ALM_INFO_ALMID on CH_T_ALBUM_INFO (
   ALBUM_ID ASC
)

创建主键

alter table CH_T_ALBUM_INFO add constraint pk_ALBUM_ID primary key (ALBUM_ID);

查询表索引

select index_name from user_indexes where table_name='CH_T_ALBUM_INFO';
select index_name from user_indexes where table_name='CH_T_ALBUM_INFO_0408';