SQL工作杂记

select * from m_senderhost;


select count(*) from user_objects;
select count(*) from dba_objects;


select * from abt_zt where zt_state='handling'


select * from abt_zt where zt_id>800;


select * from emp;


select   table_name,tablespace_name   from   user_all_tables   where   tablespace_name= 'USERS ';


---echo $ORACLE_SID
--查询发送数目
select abt_zt.zt_name,t.n,t.zt_id,t.ztb_name,abt_zt.zt_szcount
from (
      select count(*) n,zt_id,ztb_name 
      from abt_ztaction 
      where zt_id >=807 
      group by ztb_name,zt_id) t,abt_zt 
where t.zt_id=abt_zt.zt_id and t.ztb_name='短信发送' 
order by t.zt_id;
--语法： sum(col1) over(partition by col2 order by col3 )
--over ： 可以理解为基于…做求和操作
--partition by ：分组
--order by : 对分组内进行排序
--整个语法：基于col2进行分组，在分组情况下以col3排序，对col1进行累计操作              
select b.*,  sum(b.发送量) over (partition by b.ztb_name order by b.zt_id  ) 累计发送量
from (
      select abt_zt.zt_name,t.zt_id,t.ztb_name,abt_zt.zt_szcount,t.n 发送量
      from (
            select count(*) n,zt_id,ztb_name 
            from abt_ztaction 
            where zt_id >=807 
            group by ztb_name,zt_id) t,abt_zt 
      where t.zt_id=abt_zt.zt_id and t.ztb_name='邮件发送' 
      order by t.zt_id) b;
      
--各种情况            
      select abt_zt.zt_name,t.zt_id,t.ztb_name,abt_zt.zt_szcount,t.n 发送量
      from (
            select count(*) n,zt_id,ztb_name 
            from abt_ztaction
            where zt_id >=807 
            group by ztb_name,zt_id) t,abt_zt 
      where t.zt_id=abt_zt.zt_id and t.ztb_name not like '%短信%' 
      order by t.zt_id
     
      
--查询表中的前5行记录
select * from abt_zt where rownum<=5 order by zt_id asc;
--查询表中第6-10行数据
select * from abt_zt where zt_id not in (select zt_id from abt_zt where rownum<=5) and rownum<=5 order by zt_id asc;
--查询表中zt_szcount前5大的数据
select distinct zt_szcount from abt_zt where zt_szcount in(
       select distinct zt_szcount from (
              select distinct zt_szcount from abt_zt order by zt_szcount desc) where rownum<=5) order by zt_szcount desc;


select * from clientsource;


SELECT clientsourceid FROM Clientsource WHERE 1=1 ORDER BY clientsourceid DESC


select * from aby_yzfhy_month


select count(*) from t_sys_permission_cfg cfg,t_sys_permission_def def 
where cfg.permission_id=def.permission_id and def.function is not null and cfg.userid='0109100900111'


select notify0_.ID as ID99_, notify0_.BeginTime as BeginTime99_, notify0_.Content as Content99_, notify0_.CreateTime as CreateTime99_, notify0_.EndTime as EndTime99_, notify0_.Title as Title99_ 
from notify notify0_ where 1=1 and notify0_.EndTime>=trunc(sysdate+1) and notify0_.BeginTime<=trunc(sysdate+1)


select department0_.DeptName as col_0_0_ from department department0_ where department0_.DeptID='0008'


select companys0_.CompanyName as col_0_0_ from companys companys0_ where companys0_.CompanyID='0001' and (ifview is null)


drop table employee;


create table employee (
     id int,
     name varchar(50),
     age varchar(10)
)


insert into employee(id,name,age) values (001,'张三','12');




commit;


select * from v$instance;
--建立序列号
create sequence aby_yzfpayment_seq
increment by 1  
start with 1
nomaxvalue
nominvalue
nocache;
--建立触发器
create or  replace trigger aby_yzfpayment_tr
before insert on aby_yzfpayment
for each row
begin
select aby_yzfpayment_seq.nextval into :new.PAYMENT_ID from dual;
end;


commit;




select * from aby_yzfpayment where rownum>109500;


select * from aby_yzfpayment where SERIAL_NUMBER between 57570 and 51978;


select * from aby_yzfpayment where SERIAL_NUMBER=57570


select * from aby_yzfpayment where payment_id between 51968 and 51978;


update aby_yzfpayment set paytime=to_date('2012-07-16','yyyy.mm.dd') where paytime=to_date('2012-07-17','yyyy.mm.dd');


update aby_yzfpayment set paytime=to_date('2012-07-31','yyyy.mm.dd') where paytime is null;
commit;


select count(*) from aby_yzfpayment where paytime=to_date('2012-07-31','yyyy.mm.dd');


select count(*) from aby_yzfpayment where paytime is null;


select * from aby_yzfpayment where paytime is null;


select count(*) from ecs_ip_tables;