select substr('220594',0,2) from dual
倒序取前十条: select * from (select * from Table order by id desc ) where rownum<=10
decode(value, if1, then1, if2,then2, if3,then3, . . . else )
Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试,如果value的值为if1,Decode 函数的结果是then1;如果value等于if2,Decode函数结果是then2;等等。事实上,可以给出多个if/then 配对。如果value结果不等于给出的任何配对时,Decode 结果就返回else 。
需要注意的是,这里的if、then及else 都可以是函数或计算表达式。
group by 语法:
select substr(v.stattime,6,8),v.personid
from sys_edu_view v group by substr(v.stattime,6,8),v.personid
日期时间间隔操作
当前时间减去七分钟的时间:
1.select sysdate,sysdate - interval '7' minute from dual ;
当前时间减去七小时的时间:
2.select sysdate,sysdate - interval '7' hour from dual;
当前时间减去七天的时间:
3.select sysdate,sysdate - interval '7' day from dual ;
当前时间减去七个月的时间:
4.select sysdate,sysdate - interval '7' month from dual;
时间间隔乘以一个数:
5.select sysdate,sysdtate - 8*interval '2' year from dual ;
日期到字符串的操作:
select sysdate ,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual;
trunk/round 函数的使用
select trunc(sysdate,'year') from dual; --2012-1-1
select round(sysdate) from dual; --2012-3-23
创建视图:
create or replace view view_test as
select * from sys_person
连接字符串:
code1||code2||code3 --
截取字符串:
substr("ABCDEFG",0) --ABCDEFG 截取所有字符
substr("ABCDEFG",2) --CDEF
substr("ABCDEFG",0,2) -- AB
substr("ABCDEFG",0,-3) --DEF 注意参数-3,为负值时表示从尾部开始算起,字符串排列位置不变。
--两个Date类型字段:START_DATE,END_DATE,计算这两个日期的时间差(分别以天,小时,分钟,秒,毫秒):
--天:
ROUND(TO_NUMBER(END_DATE - START_DATE))
--小时:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
--分钟:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
--秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
--毫秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)
Oracle内连接、左外连接、右外连接、全外连接小总结 :
1.内连接
内连接即最常见的等值连接例:
select * from TableA ,TableB where TableA.A=TableB.A;
2.外连接
外连接分为左外连接和右外连接和全外连接。
(1).左外连接 left outer join 或者 left join
左外连接就是在等值的基础上加上主表中的未匹配数例:
select * from TableA left outer join TableB on TableA.A=TableB.A;
oracle 支持另一种写法:
select * from TableA ,TableB where TableA.A=TableB.A(+);
三个表做做外连接:
select * from TableA left outer join TableB on TableA.A=TableB.A left outer join TableC on
TableA.A=TableC.A;
Oracle 支持的另外一种写法
select * from TableA,TableB,TableC where TableA.A=TableB.A(+) and TableA.A=TableC.A(+)
(2).右外连接 right outer join 或 right join
右外连接是在等值的基础上加上被连接表的不匹配数据:
select * from TableA right outer join TableB on TableA.A=TableB.A;
Oracle支持的另一种写法 :
select * from TableA ,TableB where TableA.A(+)=TableB.A;
(3).全外连接 full outer join 或者 full join
select * from TableA full outer join TableB on TableA.A=TableB.A;
nvl()函数:
nvl(a,b) 如果a不为null 则返回a,如果a为null则返回b;
nvl2(a,b,c) ,如果a不为null 则返回b,如果a为null则返回c;
如果oracle 表中有大字段如(Long) 导出sql文件时出错,则可以导成dmp格式的文件
-------------------------------------------------------------------
1.按年份分组
select to_char(exportDate,'yyyy'),sum(amount) from table1 group by to_char(exportDate,'yyyy');
年份 数量
-----------------------------
2009 68
2010 137
2008 103
2.按月份分组
select to_char(exportDate,'yyyy-mm'),sum(amount) from table1 group by to_char(exportDate,'yyyy-mm')
order by to_char(exportDate,'yyyy-mm');
月份 数量
-----------------------------
2008-02 20
2008-03 2
2008-04 6
2008-06 75
2009-10 23
2009-11 45
2010-08 5
2010-09 44
2010-10 88
3.按季度分组
select to_char(exportDate,'yyyy-Q'),sum(amount) from table1 group by to_char(exportDate,'yyyy-Q')
order by to_char(exportDate,'yyyy-Q');
季度 数量
------------------------------
2008-1 22
2008-2 81
2009-4 68
2010-3 49
2010-4 88
4.按周分组
select to_char(exportDate,'yyyy-IW'),sum(amount) from table1 group by to_char(exportDate,'yyyy-IW')
order by to_char(exportDate,'yyyy-IW');
周 数量
------------------------------
2008-07 20
2008-11 2
2008-16 6
2008-24 75
2009-43 23
2009-46 45
2010-31 5
2010-35 44
2010-40 88
补充:
按季度分组还有个比较笨的方法(参考网络资源)
select to_char(exportDate,'yyyy'),
sum(decode(to_char(exportDate,'mm'),'01',amount,'02',amount,'03',amount,0)) as 第一季,
sum(decode(to_char(exportDate,'mm'),'04',amount,'05',amount,'06',amount,0)) as 第二季,
sum(decode(to_char(exportDate,'mm'),'07',amount,'08',amount,'09',amount,0)) as 第三季,
sum(decode(to_char(exportDate,'mm'),'10',amount,'11',amount,'12',amount,0)) as 第四季
from table1
group by to_char(exportDate,'yyyy');
年份 第一季 第二季 第三季 第四季 --------------------------------------------------
2009 0 0 0 68
2010 0 0 49 88
2008 22 81 0 0
-------------------------------------------------------------
select t.grouptitle 企业名称,t.purpose 用途,t.used 使用金额,(case t.type when '1' then '教育' when '2' then '生产' else '无' end)经费类别,t.usedperson 使用人 from sys_funds_used t where t.groupid=345 and to_char(t.dates,'yyyy')='2011'
------------------------------------------------------------------
Oracle模糊查询的实现
Oracle模糊查询应该如何实现呢?下面就教您一个实现Oracle模糊查询的方法,如果您在Oracle模糊查询方面遇到问题,不妨一看。
在Where子句中,可以对datetime、char、varchar字段类型的列用Like子句配合通配符选取那些“很像...”的数据记录,以下是可使用的通配符:
% 零或者多个字符
_ 单一任何字符(下划线)
\ 特殊字符
[] 在某一范围内的字符,如[0-9]或者[aeth]
[^] 不在某范围内的字符,如[^0-9]或者[^aeth]
其中关于条件,SQL提供了四种匹配模式:
1,%:表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。
比如 SELECT * FROM [user] WHERE u_name LIKE '%三%'
将会把u_name为“张三”,“张猫三”、“三脚猫”,“唐三藏”等等有“三”的记录全找出来。
另外,如果需要找出u_name中既有“三”又有“猫”的记录,请使用and条件
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%'
若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%'
虽然能搜索出“三脚猫”,但不能搜索出符合条件的“张猫三”。
2,_: 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句:
比如 SELECT * FROM [user] WHERE u_name LIKE '_三_'
只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的;
再比如 SELECT * FROM [user] WHERE u_name LIKE '三__';
只找出“三脚猫”这样name为三个字且第一个字是“三”的;
3,[ ]:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。
比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三'
将找出“张三”、“李三”、“王三”(而不是“张李王三”);
如 [ ] 内有一系列字符(01234、abcde之类的)则可略写为“0-4”、“a-e”
SELECT * FROM [user] WHERE u_name LIKE '老[1-9]'
将找出“老1”、“老2”、……、“老9”;
4,[^ ] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。
比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三'
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等;
SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';
将排除“老1”到“老4”,寻找“老5”、“老6”、……
5,查询内容包含通配符时
由于通配符的缘故,导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现,而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数:
function sqlencode(str) str=replace(str,"[","[[]") '此句一定要在最前 str=replace(str,"_","[_]") str=replace(str,"%","[%]") sqlencode=str end function 在查询前将待查字符串先经该函数处理即可。
-------------------------------------------------