- 浏览: 1477517 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (523)
- JAVA (334)
- J2EE (18)
- JSP (21)
- JavaScript (14)
- it life (2)
- mobile develop (4)
- UBUNTU (14)
- Algorithm (14)
- DataBase (56)
- Browser/Server (1)
- linux (6)
- fedora (2)
- CSS (1)
- AjAX (3)
- HTML5 (1)
- EJB (1)
- osworkflow (2)
- Java face (5)
- spring (4)
- ICE (1)
- webService (2)
- MongoDB (1)
- JavaThread (4)
- JavaCollections (3)
- Hibernate (2)
- JavaMail (1)
- JavaBasic (1)
- Cache (1)
- https (4)
- DOM4J (1)
- JDOM (0)
- Mybatis (1)
- JUNIT (1)
- eclipse (1)
- easyMock (2)
最新评论
-
yadongliang:
...
自己认为的一些技术点 -
yadongliang:
每次看你头像都看的荷尔蒙分泌失调
WebService的两种方式SOAP和REST比较 (转) -
yadongliang:
hollo 写道一直有这种感觉,盲目的跟风,确实有一些人为了潮 ...
WebService的两种方式SOAP和REST比较 (转) -
welss:
博主,JNative怎么调用dll中的这种方法: int ...
JNative调用DLL -
Java_Antelope:
session.setAttribute和session.getAttribute(
这是在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+"&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
删除字段: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+"&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
发表评论
-
sql 统计类语句
2014-03-17 16:59 11271、统计每月的成交量,按照逆序排序(2013年以来的每月销量, ... -
Jdbc 操作Oracle CLOB字段
2013-01-08 22:10 4446//这个适用于oracle10,11,以前oracle9操作C ... -
SQL重复记录处理(查找,过滤,删除)
2012-12-30 23:43 4794HZT表结构 ID int Title nvarchar( ... -
sql 如何过滤重复记录
2012-12-29 21:37 2410请各位大侠帮忙了 问题1:对于以下几个记录 ID 123456 ... -
Mysql 触发器和存储过程
2012-09-17 12:34 9701、首先建表: create table tababin( i ... -
Sql ACID
2012-09-12 17:57 856http://nathanchen.github.com/Re ... -
Oracle 表分区学习
2012-09-12 17:52 796http://love-flying-snow.iteye.c ... -
Oracle表分区
2012-09-12 17:19 1337废话少说,直接讲分区语法。 Oracle表分区分为四种:范围分 ... -
MYSQL中 ENUM 类型的详细解释
2012-03-12 23:30 2386ENUM 类型 ENUM 是一个字 ... -
Mysql 字段数据类型/长度及表类型详细说明
2012-03-12 23:20 14967一、MySQL 字段数据类型/长度 1、数值类型 列类型 ... -
MySQL中的datetime与timestamp比较
2012-03-04 19:30 1187相同 显示 TIMESTAMP列的显示格式与DATETI ... -
mysql 存储过程例子
2011-08-21 01:35 3960mysql 存储过程例子(当前我使用的是mysql 5.5) ... -
Oracle Cursor介绍
2011-08-21 00:54 1008一 概念 游标是SQL的一个内存工作区,由系统或用户以变量的 ... -
oracle 游标
2011-08-20 14:09 10421、隐式游标 实例1、用sql%found 相当于sql.fo ... -
关于DB2存储过程的几个小实例
2011-08-20 03:21 2892================ CREATE PROCED ... -
oracle 游标遍历%rowtype中的记录
2011-08-20 01:49 7449那么我们使用Oracle游标 游标分为:静态游标和引用游标( ... -
oracle concat
2011-08-19 23:26 1167oracle 连接字符串: 连接两个字符串 sel ... -
oracle 存储过程
2011-08-19 21:55 963oracle的for循环,commit放在不同的位置,一个是每 ... -
sql 删除重复的记录
2011-08-19 16:32 933通过建立临时表删除: SQL>create ta ... -
Oracle 经典
2011-08-17 17:05 12021.删除重复项, 只保留第一条 delete table t ...
相关推荐
有关于我sql平时积累的语句,后面会更新,最近做sql查询比较多
mysql SQL语句积累,学习mysql的朋友可以参考下。
平时积累的sql语句语法还有sql中的基本函数
sql常用语句积累 希望对大家有所帮助
5种数据库sql语句大全,绝对值得收藏。本人多年积累,拿出来与大家分享。
数据库表空间建立、查询、常用的SQL运维维护;长期的积累
自己积累的vb——sql语句,由不全的地方!!帮着指点下!!!!!我也在自学阶段。
mysql相关的sql语句,大部分场景都覆盖了,个人积累
SQL 查询语句积累
经典SQL语句总结,从业多年的积累,收录了从基础到高阶的各种语句及处理方法,从浅到深,适合初学者或从业人员,掌握此文档内容,日常SQL运用如鱼得水,得心应手。
sql常用语句集锦,多年来积累的sql语句,包括各类查询技巧等。
sqlserver 基础语句,高级语句,经典语句积累。
本人使用sql开发积累下来的语句,希望对大家有用。doc
分享给大家仅供参考,应用还得靠自己实际积累!!!
最迷你的SQL数据库工具 是你联系LINQ语句和语法的最佳选择 本人收藏了3年的资源 现放出 都是总结了很多系统 软件项目实施过程中的经验的 慢慢积累的
最近整理了SQL常用的语句大全。希望能带给初学者一些帮助 这些都是在实际的软件开发中积累的一些经验的东西
上面是对DDL语言操作的经典语句的集合。上面是对DDL语言操作的经典语句的集合。上面是对DDL语言操作的经典语句的集合。
1.当某一字段的值希望通过其它字值显示出来时(记录转换),可通过下面的语句实现:case Type when ‘1’ then ‘普通通道’ when ‘2’ then ‘高端通道’ end as Type其中“Type”是字段名,”1“,”2“是字段值 ...
这是实际开发过程中遇到的自己整理的的一些DB2常用的较难的SQL语句