sql 语句积累

这是在oracle里面试过的！

删除字段：alter table title drop COLUMN id

增加字段： alter table you add id int ;

修改字段为主键：alter table you modify id primary key;

java 取结果集的最后三项：
1、SELECT * FROM  (SELECT * FROM you ORDER BY id DESC ) WHERE ROWNUM<=3;

2、select id,name from (select id,name,rownum rn from you where rownum <= (select count(*) from you)) where rn > (select count(*) from you)-3;

oracle设置字段ID为主键：
alter table recommend drop column id;
alter table recommend add id int;
alter table recommend add constraint id primary key (ID) deferrable;

alter table recommend add id int primary key identity(1,1);
select distinct去掉重复记录
select distinct(type)  from hd_zbhuanyw
select content,username from abin group by content,username
查询昨天的记录
select * from hd_hotbuy t where trunc(createtime)=trunc(sysdate-1);
查询一月之内的记录数：
select mobile,to_char(createtime,'yyyy-mm'),count(distinct(to_char(createtime,'yyyy-mm-dd'))) aa from hd_gushi_log where to_char(createtime,'yyyy-mm')=to_char(sysdate,'yyyy-mm')  group by mobile,to_char(createtime,'yyyy-mm')
查找当月，用户的总的记录数：
select count(mobile) from local_membervisitlog where mobile='13968529558' and to_char(createtime,'yyyy:MM')=to_char(sysdate,'yyyy:MM')

//根据fid首先查到公司名称，然后再根据公司的名称来查找公司所有的职位名称
select * from local_recruitfirm where firmname like '%'||(select firmname from local_recruitfirm t  where fid=203)||'%'

select * from local_recruitfirm where firmname like '%诺基亚西门子通信%'


select '%'||(select firmname from local_recruitfirm t  where fid=203)||'%' from dual






select * from local_recruitfirm where firmname = ( select t.firmname from local_recruitfirm t  where fid=203)

select * from local_recruitfirm where firmname in ( select t.firmname from local_recruitfirm t  where fid=203)


//涉及到多个条件会用到或。OR，需要加括号()
select count(*) from (select * from local_news order by createtime desc)  where  newstitle like ? and (cateid='n1003' or cateid='n1004' or cateid='n1005' or cateid='n1010' or cateid='n1011' or cateid='n1012')

select * from (select t.*, rownum rn from (select * from local_news  where newstitle like ? and (cateid='n1003' or cateid='n1004' or cateid='n1005' or cateid='n1010' or cateid='n1011' or cateid='n1012') order by createtime desc)t where rownum<=? )  where rn>?

Oracle Group BY语句：
select FIRMNAME,FIRMINTRODUCE,FIRMADDRESS,JOBREQUIRE,FIRMCONTACT,count(FIRMPOSITION) from local_recruitfirm t where firmname in ( select t.firmname from local_recruitfirm  where fid=203) group by CREATETIME,FIRMNAME,FIRMINTRODUCE,FIRMADDRESS,JOBREQUIRE,FIRMCONTACT

sql里面的不等于：
delete from hd_pairtouch  where mobile <> '13588844873'
delete from hd_pairtouch  where mobile != '13588844873'
oracle随机取3条记录
select * from(select * from hd_leathercitylog where whether=1 order by dbms_random.value)where  rownum<=3



update hd_doublefinal set status=1 where term in(select term from (select * from hd_doublefinal where  mobile='13588844873' order by createtime desc) where rownum<=5)

查询从现在算起的上个月与下个月
这个要用到add_months()函数 参数 负数 代表 往前  正数 代表 往后。
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
--上一个月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;
--下一个月

譬如：统计每个mobile的个数，并且只显示个数大于3个的
select mobile,count(mobile) as count from abin group by mobile  having count(mobile)>1 order by count(mobile) desc

not exist和not in
select * from abin a where tid not in (select tid from abin t where t.tid=a.tid and tid=3)

select * from abin a where tid  in (select tid from abin t where t.tid=a.tid and tid=3)

select * from abin a where not exists (select tid from abin t where t.tid=a.tid and tid=3)

select * from abin a where exists (select tid from abin t where t.tid=a.tid and tid=3)


select * from(select id,content,author,count(id)c from(select a.*,b.* from blog_article a,blog_fengxiang b where a.id=b.aid and a.reward='最佳文字奖')group by id,content,author) order by c desc


java传值：
out.println(rn+"."+"<a href='http://www.baidu.com/'>"+nickname+"</a>"+":"+content+"<a href='fenxiang.jsp?id="+id+"&amp;mid="+mid+"'>"+"分享"+"</a>");

三张表联合查询
select * from
(select k.content,k.name,k.author,k.bid,k.picture,k.reward,k.c,rownum rn from (select t1.*,t2.c from(
select t.bid,t.content,t.author,t.picture,t.reward,s.name from blog_shortmessage t,blog_regist s
where t.author=s.mobile and t.reward='最佳提议奖' ) t1, (select count(aid) c,aid from blog_fengxiang  group by aid)t2
where t1.bid=t2.aid order by c desc)k
where rownum<=30) where rn>=1