Oracle資料庫使用總結

標籤：字元   bst   href   group by   大寫   運算式   原來   values   star   

--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 獲得月份 這裡就是為了練習函數的使用，有簡單的方式擷取月份

 

--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

 

寫部落格是為了記住自己容易忘記的東西，另外也是對自己工作的總結，文章可以轉載，無需著作權。希望盡自己的努力，做到更好，大家一起努力進步！

如果有什麼問題，歡迎大家一起探討，代碼如有問題，歡迎各位大神指正！

Oracle資料庫使用總結