Oracle Database Usage Summary

--1, use month as conditional filter (to_char function and extract function)

SELECT * from Test_date where To_char (DQSJ, ' mm ') like '%07% ';
SELECT * from Test_date where extract (month from DQSJ) = 6;

--2, get view name

SELECT * from User_views;

--3, get the sequence you created

SELECT * from User_sequences;

--4, creating sequences

--related Information link: 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;

--Initialize Date data
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;--Note Here Select the first initial letter of capitalization

--5, get year or month by date

--related Information link:extract () function use: http://www.cnblogs.com/summary-2017/p/7363977.html

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

--Dual Table of the detailed: http://www.cnblogs.com/summary-2017/p/7737947.html,http://www.cnblogs.com/summary-2017/p/7737935.html

--The first way (use the to_char () function to format the date type as a required String type)

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 ');--Get year
SELECT DISTINCT (extract (year from DQSJ) years from Test_date Order by extract (yearly from DQSJ) desc; --Get year

---The following two SQL has an error, found in the interval value expression syntax errors, aliases generally do not set the keyword, otherwise there may be unknown errors, the reference of the small partners note yo!
SELECT DISTINCT (To_char (DQSJ, ' yyyy ')) year from Test_date Order by To_char (DQSJ, ' yyyy ');--Get year
SELECT DISTINCT (extract (month from DQSJ), month from Test_date order by extract (month from DQSJ);--Get month

  select DISTINCT (To_char (DQSJ, ' mm ')) from Test_date Order by To_char (DQSJ, ' mm ') ASC ;--Get the month 05,06,07, sometimes the month needs to show 5,6,7, you can use the Decode function below to handle the
  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) month from Test_date Order by To_char (DQSJ, ' mm ') asc;-- Get month--not to process the

--oracle 10g database, execute the following statement error "ORA-01791: not SELECTed expression":
--Original: The SELECT statement contains the DISTINCT keyword or operator, The sort field must correspond to the fields in the SELECT statement.
--The online search is interpreted as follows:
-Specify more than one column in the order by, the result is sorted first by the first column in the clause, then the second, and so on.
--column names that do not appear in Select are also available in the ORDER BY clause as long as there is a row in the table.
---but if the DISTINCT keyword appears in the SELECT clause, only the column name that appears,
--and if any operators are used in the SELECT clause, must be persisted in the ORDER BY clause in exactly the same way as the expression in the SELECT clause. Otherwise there is an error: "ORA-01791: not SELECTed expression".
--Above for reference only

--To re-process
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)) month 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;-- Get month
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) month 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;--get month

--Second way: Use the Extract function to extract the day of the month
Select Extract (year from sysdate) year from dual;
Select Extract (month from sysdate) month from dual;
Select Extract (Day from sysdate) day from dual;

--The following SQL has a problem, the initial estimate is because the alias set the Month keyword caused, know the cause of friends trouble tell a sound, learn from each other ha
SELECT DISTINCT (month from DQSJ) the month from Test_table order by extract (month from DQSJ) desc;--sorted by latest year--not good
--Using the Extract function, this way of getting the month is, ... 11,12
SELECT DISTINCT (extract month from DQSJ) months from test_table order by extract (month from DQSJ) asc;--sorted by latest year--so

--Use timestamp date type to get year
Select To_timestamp (To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd hh24:mi:ss ') from dual;--bad to make the internal buffer string too long
Select Extract (Year from To_timestamp (To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd hh24:mi:ss ') from dual;-- So 2017

SELECT to_char (sysdate, ' Hh24:mi ') from dual;--16::05 can be arbitrarily formatted, note the use of the system default virtual table dual, very useful

Use of--6, like keyword fuzzy query

--a date-related like query

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 as To_date (' 2017-10-25 ', ' yyyy-mm-dd ') Order by Dcno ASC;

--7, common date conditions comparison

--related Information link: 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 statement query yesterday's data
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 = ' phase ' and To_char (Haomrq, ' Yyyy-mm-dd ') =to_char (sysdate-1, ' yyyy-mm-dd ') Order by Dcno Asc

--8, date-related display issues

Select Ids,rcfrl,rlfrl,dc,to_char (DQSJ, ' mm/dd ') as DQSJ from test_table;--here is mainly the format of the date of the display type commonly used in the icon display 09/24
Select Ids,rcfrl,rlfrl,dc,to_char (DQSJ, ' yyyy.mm ') as DQSJ from test_table;--here is mainly the presentation type of the formatted date 2017.09

--9, substr functions are used with to_char functions, and with InStr functions

--related Information link, InStr () function: http://www.cnblogs.com/summary-2017/p/7735425.html
--< Span style= "COLOR: #ff0000" >instr () function: http://www.cnblogs.com/summary-2017/p/7735403.html
--substr () function: 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;--original string, search for string, default starting from 1 to find, first several occurrences
Select substr (' 2017-10-26 ', 7,3) as Str from the dual;--value is: 0-2 index starting from 7, indicating that the right side of the '-1 ' is 0 intercept, 3 means the length of the Intercept
Select substr (' 2017-10-26 ', 2,3) from dual;--value: 017 start with the second index, intercept three bits here the index, whether 0 and 1, is the first character to intercept
--Mixed use
Select InStr (To_char (Sysdate, ' yyyy-mm-dd '), '-', "," from dual;--the original string, search for the string, default starting from 1 to find, the first several occurrences, similar to IndexOf, Gets the position where a string appears in the original string note that the index here starts at 1, and 2 indicates that the second element is the first to find
Select substr (To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), 15,2) from dual;--2017-10-26 16:52:57 intercept from the 15th index position, spaces and: Represents an index value, and the final intercept value is: 52
Select substr (To_char (sysdate, ' Yyyy-mm-dd '), (InStr (To_char, ' sysdate '), '-', "+1"), 2) as month from YYYY-MM-DD; --2017-10-26,5+1,2 get month here is to practice the use of functions, there is a simple way to get the month

--10, the use of system time

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

Blog is to remember that they are easy to forget things, but also a summary of their work, the article can be reproduced, without copyright. Hope to do their own efforts to do better, we work together to improve!

If there is any problem, welcome to discuss together, code if there is a problem, you are welcome to the great God!

Oracle Database Usage Summary