oracle常用的语句,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')

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';