Oracle 常用sql语句

LIMIT

查1条

select * from (select rownum r,c.* from (select * from LOGININFO order by UPDATETIME DESC) c)

where r=1;

查3条

select * from adminstrator
where rowid not in(select rowid from adminstrator where rownum<=0) and rownum<=3

select * from (select a.*,rownum as rno from a) where rno>20 and rno <30;

分页：按天统计用户注册数

SELECT T2.*
    FROM (
        SELECT T.*, ROWNUM R
            FROM (
                SELECT  COUNT(T1.CREATETIME ) NUMS ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') DATES
                    FROM
                USERACCOUNT T1
            WHERE CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') AND TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
                GROUP BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD')
                ORDER BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') )
            T
)T2  WHERE T2.R>0 AND T2.R<10

在时间字段上加分钟/小时/天

分： SELECT CREATETIME,CREATETIME+10/(24*60)  from LOGININFO WHERE "ID"=6009

小时：SELECT CREATETIME,CREATETIME+1/24  from LOGININFO WHERE "ID"=6009
天：SELECT CREATETIME,CREATETIME+1  from LOGININFO WHERE "ID"=6009

查时间字段加一定的时长与现在时间比：

select count(*) from LOGININFO where UPDATETIME+10/(24*60)>=sysdate

建序列

create sequence seq_name
increment by 1
start with 1
maxvalue 999999999;

查看已有序列

select sequence_name from ALL_SEQUENCES;

select sequence_name from USER_SEQUENCES;

select sequence_name from DBA_SEQUENCES;

删除序列

   DROP SEQUENCE seq_name；

从一个表插入另一个表数据

INSERT INTO ET_USERSPACE(USERID) SELECT USERID FROM SMM_USERINFO WHERE USERID>1460 AND USERID <10000;

把SMM_USERINFO表里有而ET_USERSPACE没有的数据插入ET_USERSPACE

INSERT INTO ET_USERSPACE(USERID) SELECT USERID FROM (SELECT USERID FROM (select distinct SMM_USERINFO.USERID ,ET_USERSPACE.USERID as aa from SMM_USERINFO left join ET_USERSPACE on SMM_USERINFO.USERID = ET_USERSPACE.USERID) a WHERE a.AA IS null) b;