Oracle数据库使用总结

--1、使用月份作为条件筛选(to_char函数与extract函数使用)

select * from test_date where to_char(dqsj,'mm') like '%07%';
select * from test_date where extract(month from dqsj) = 6;

 

--2、获取视图名称

select * from user_views;

 

--3、获取自己创建的序列

select * from user_sequences;

--4、创建序列

--相关资料链接：http://www.cnblogs.com/summary-2017/p/7736021.html

create sequence seq_test_date increment by 1 start with 1 maxvalue 9999 minvalue 1 nocycle;

--初始化日期数据
insert into TEST_DATE(ids,dqsj) values(seq_test_date.nextval,sysdate-seq_test_date.nextval);
Select seq_test_date.nextval from dual;
Select seq_test_date.currval from dual;--注意这里Select第一个首字母大写

 

--5、通过日期获取年或者月份

--相关资料链接：extract()函数的使用：http://www.cnblogs.com/summary-2017/p/7363977.html

--decode()函数：http://www.cnblogs.com/summary-2017/p/7272520.html

--dual表的详解：http://www.cnblogs.com/summary-2017/p/7737947.html，http://www.cnblogs.com/summary-2017/p/7737935.html

--第一种方式(使用to_char()函数,将日期类型格式化为需要的字符串类型)

　select substr(to_char(dqsj,'yyyy-MM-dd'),1,4) as year from TEST_TABLE group by substr(to_char(dqsj,'yyyy-MM-dd'),1,4) order by substr(to_char(dqsj,'yyyy-MM-dd'),1,4);

  select to_char(dqsj,'yyyy') as year from test_date group by to_char(dqsj,'yyyy') order by to_char(dqsj,'yyyy');--获取年份
  select distinct(extract(year from dqsj)) 年份 from TEST_DATE order by extract(year from dqsj) desc; --获取年份

--以下两条sql有个错误，在间隔值表达式中发现语法错误,别名一般不要设置关键字，否则可能出现未知的错误，引用的小伙伴们注意呦！
  select distinct(to_char(dqsj,'yyyy')) 年份 from TEST_DATE order by to_char(dqsj,'yyyy');--获取年份
  select distinct(extract(month from dqsj)) month from test_date order by extract(month from dqsj);--获取月份

  select distinct(to_char(dqsj,'mm')) from TEST_DATE order by to_char(dqsj,'mm') asc;--获取月 05,06,07,有时候月份需要显示5,6,7，可以使用下面的decode函数来处理
  select decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12) 月份 from TEST_DATE order by to_char(dqsj,'mm') asc;--获取月--未去重处理

--Oracle 10g数据库，执行下面语句出现错误“ORA-01791: 不是 SELECTed 表达式”：
-- 原来：SELECT语句中含有DISTINCT关键字或者有运算符时，排序用字段必须与SELECT语句中的字段相对应。
-- 网上搜到解释如下：
-- 在ORDER BY中指定多个列，结果将先按照子句中的第一列排序，然后第二个，依此类推。
-- 在SELECT中未出现的列名也可用于ORDER BY 子句中，只要TABLE中有就行。
-- 但如果SELECT子句中出现了DISTINCT关键字，则只能用出现过的列名，
-- 而且如果SELECT子句中使用了任何运算符，在ORDER BY 子句中必须保持和SELECT子句中表达式完全一致，否则出现错误：“ORA-01791: 不是 SELECTed 表达式”。
--以上仅供参考

--去重处理
 select DISTINCT(decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12)) 月份 from TEST_DATE order by decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12) asc;--获取月
 select decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12) 月份 from TEST_DATE group by decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12) order by decode(to_char(dqsj,'mm'),01,1,02,2,03,3,04,4,05,5,06,6,07,7,08,8,09,9,10,10,11,11,12) asc;--获取月

--第二种方式：使用extract函数抽取年月日
select extract(year from sysdate) year from dual;
select extract(month from sysdate) month from dual;
select extract(day from sysdate) day from dual;

--下面的这条sql存在问题，初步估计是因为别名设置了month关键字导致的，知道原因的朋友麻烦告诉一声，互相学习哈
select distinct(extract(month from dqsj)) month from TEST_TABLE order by extract(month from dqsj) desc;--按照最新年份排序--不好使
--使用extract函数方式，这种方式的得到的月份就是，1,2,3，。。。11,12
select distinct(extract(month from dqsj)) 月份 from TEST_TABLE order by extract(month from dqsj) asc;--按照最新年份排序--好使

--使用timestamp日期类型获取年
select to_timestamp(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') from dual;--不好使 内部缓冲区的字符串太长
select extract(year from to_timestamp(to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) from dual;--好使 2017

SELECT to_char(sysdate,'hh24:mi') from dual;--16::05 可以随意格式化，注意使用 系统默认的虚拟表dual,很好用的

--6、like关键字模糊查询的使用

--与日期相关的like查询

select IDS , dcno , dc , allcount , gkgl , gkgl_zb , gkdl , gkdl_zb , dkgl , dkgl_zb , dkdl , dkdl_zb , hm , hm_zb , mn , mn_zb , lzym , lzym_zb , HAOMRQ , dqsj
from stati_Rlgl_Pmcsrb_Zh where 1=1 and HAOMRQ like to_date('2017-10-25','yyyy-mm-dd') order by dcno ASC;

--7、常见的日期条件比较

--相关资料链接：http://www.cnblogs.com/summary-2017/p/7263220.html

select IDS , dcno , dc , allcount , gkgl , gkgl_zb , gkdl , gkdl_zb , dkgl , dkgl_zb , dkdl , dkdl_zb , hm , hm_zb , mn , mn_zb , lzym , lzym_zb , HAOMRQ , dqsj
from stati_Rlgl_Pmcsrb_Zh where 1=1 and '2017-10-25' = to_char(HAOMRQ,'yyyy-mm-dd') order by dcno ASC;

--oracle语句查询昨天的数据
select IDS , dcno , dc , allcount , gkgl , gkgl_zb , gkdl , gkdl_zb , dkgl , dkgl_zb , dkdl , dkdl_zb , hm , hm_zb , mn , mn_zb , lzym , lzym_zb , HAOMRQ , dqsj
from stati_Rlgl_Pmcsrb_Zh where dc = '一期' and to_char(HAOMRQ,'yyyy-MM-dd')=to_char(sysdate-1,'yyyy-MM-dd') order by dcno ASC

--8、与日期相关的展示问题

select ids,rcfrl,rlfrl,dc,to_char(dqsj,'mm/dd') as dqsj from TEST_TABLE;--这里主要是格式化日期的展示类型 常用于图标展示中 09/24
select ids,rcfrl,rlfrl,dc,to_char(dqsj,'yyyy.mm') as dqsj from TEST_TABLE;--这里主要是格式化日期的展示类型 2017.09

 

--9、substr函数与to_char函数，以及instr函数一起使用

--相关资料链接，instr()函数：http://www.cnblogs.com/summary-2017/p/7735425.html
--instr()函数：http://www.cnblogs.com/summary-2017/p/7735403.html
--substr()函数：http://www.cnblogs.com/summary-2017/p/7730739.html

select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;--2017-10-26 16:24:27
select instr('hello','o',2,1) from dual;--原字符串,搜索的字符串,默认从1开始查找,第几次出现
select substr('2017-10-26',7,3) as str from dual;-- 值为:0-2 索引从7开始，表示从'-1'右边紧挨着的地方0开始截取，3表示截取的长度
select substr('2017-10-26',2,3) from dual;--值：017 从第二个索引开始，截取三位 这里索引无论是0和1，都表示从第一个字符开始截取
--混合使用
select instr(to_char(sysdate,'yyyy-MM-dd'),'-',1,1) from dual;--原字符串,搜索的字符串,默认从1开始查找,第几次出现，类似于indexOf，获取某个字符串在原字符串中出现的位置 需要注意的是这里索引从1开始，2就表示从第二个元素开始查找
select substr(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),15,2) from dual; -- 2017-10-26 16:52:57 从第15个索引位置开始截取，空格和:分别表示一个索引值，最终截取值为：52
select substr(to_char(sysdate,'yyyy-mm-dd'),(instr(to_char(sysdate,'yyyy-MM-dd'),'-',1,1)+1),2) as month from dual;--2017-10-26，5+1,2 获得月份 这里就是为了练习函数的使用，有简单的方式获取月份

 

在一个字符串中搜索指定的字符,返回发现指定的字符的位置; 

比如有一个a字段格式如下：

aaaa-bbbb-cc

要截取最后的cc

1，首先获取最后一次出现的- 的位置

instr('aaaa-bbbb-cc','-',-1)，这样就得到了最后一个-的下标

2，从最后一次出现-的位置开始截取字符串

下标加一，然后截取

substr('aaaa-bbbb-cc',instr('aaaa-bbbb-cc','-',-1)+1)

运行例子：

select substr('aaaa-bbbb-cc',instr('aaaa-bbbb-cc','-',-1)+1) from dual;

--10、系统时间的使用

select sysdate as dqsj from dual;--2017-10-26 16:55:59
select sysdate-1 as dqsj from dual;--2017-10-25 16:55:22

--11、Oracle分页sql

　　SELECT * FROM (select A.*,ROWNUM rn from (select * from stati_Rlgl_Mzjyrb) A where ROWNUM <=${start}+20) where RN >=${start}; --动态的

SELECT IDS , DC , GYS , MK , PINZ , FAZ , YSDW , CHES , LAIMSL , QNET_AR , Q_KCAL , MT , MAD , AAR , AD , VDAF , STAD , STD , DHRQ , dqsj
FROM ( SELECT A.*, ROWNUM RN FROM
　　　　　　(select IDS , DC , GYS , MK , PINZ , FAZ , YSDW , CHES , LAIMSL , QNET_AR , Q_KCAL , MT , MAD , AAR , AD , VDAF , STAD , STD , DHRQ , dqsj
　　　　　　from stati_Rlgl_Mzjyrb order by dqsj ASC) A
　　    WHERE ROWNUM <= 12 )
WHERE RN > 0;

--12、获取前几条数据

--一般用于区间
select A.*,rownum rn from
(select
IDS ,
to_char(DQSJ,'mm/dd') as dqsj,
RCGBMDJ, --日采购标煤单价
RCGBMLJ , --日采购标煤累计金额
YDJHCGBM, --月度计划采购标煤
DC, --电厂，一期、二期、三期
BMDJPM --标煤单价排名
from STATI_RLGL_FINEREPORT_BIAOMEI
order by dqsj asc) A where rownum <= 10;

--常用写法
select A.* from
(select
IDS ,
to_char(DQSJ,'mm/dd') as dqsj,
RCGBMDJ, --日采购标煤单价
RCGBMLJ , --日采购标煤累计金额
YDJHCGBM, --月度计划采购标煤
DC, --电厂，一期、二期、三期
BMDJPM --标煤单价排名
from STATI_RLGL_FINEREPORT_BIAOMEI
where to_char(dqsj,'yyyy-MM-dd') <= '2017-10-31'
order by dqsj asc) A
where rownum <=10
order by rownum asc;

 

--13、查询语句，根据日期获取当前日前三十天数据（当前日往前一个月的数据）||获取当前月份前12个月的数据（包含本月共一年的数据）
select
IDS ,
to_char(DQSJ,'mm/dd') as dqsj,
RCGBMDJ, --日采购标煤单价
RCGBMLJ , --日采购标煤累计金额
YDJHCGBM, --月度计划采购标煤
DC, --电厂，一期、二期、三期
BMDJPM --标煤单价排名
from STATI_RLGL_FINEREPORT_BIAOMEI
-- where to_char(dqsj,'yyyy-MM-dd') between('2017-10-01') and('2017-10-31')
--where to_char(add_months(dqsj,-1),'yyyy-MM-dd') = '2017-09-21'
where to_char(dqsj,'yyyy-MM-dd') between to_char(add_months(to_date('2017-10-31','yyyy-MM-dd'),-1)+1,'yyyy-MM-dd') and '2017-10-31'
--and to_char(dqsj,'yyyy-MM') between to_char(add_months(to_date('2017-10','yyyy-MM'),-12),'yyyy-MM') and '2017-10' 返回前十二个月数据
order by dqsj asc

 

--14、dbms_random是oracle提供的一个随机函数包

参考资料：oracle时间的获取，前一天，上一个星期，上一个月

　　　　　　TRUNC函数的用法

Oracle查询前几条数据的方法

 

select dbms_random.value(0,10) from dual;
--TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期
select trunc(12.23,1) from dual;--12.20
select trunc(12.23,-1) from dual;--10
select trunc(sysdate,'yyyy') from dual;--2017-01-01 00:00:00

--sql语句查询显示出该字段减去一个月后得出的日期
select add_months(sysdate,-1) from dual;
--上个月第一天的最后时刻 -----------------------------
select to_char(add_months(sysdate,-1),'yyyy-mm-dd') from dual;

写博客是为了记住自己容易忘记的东西，另外也是对自己工作的总结，文章可以转载，无需版权。希望尽自己的努力，做到更好，大家一起努力进步！

如果有什么问题，欢迎大家一起探讨，代码如有问题，欢迎各位大神指正！