This document is to record common functions
sysdate date function
sysdate: used by Oracle to obtain the date and time
select sysdate from dual; #Get current date
Specific common methods: https://i.csdn.net/#/user -center/collection-list?type=1&spm=1001.2101.3001.4506
rowid and rownum
rowid: used to locate a piece of data in the data table. It is unique and will not change
Rownum: indicates the position of a query record in the whole result set. The corresponding rownum is different for the same record with different query conditions, but the rowid will not change
The rowid is unique and will not be changed. It is used to delete the same data. Specific sql:
delete from emp where rowid not in (select min(t.rowid) from EMP t group by t.emp_no) #Query all rowid s in groups, exclude the smallest ones, and delete the remaining ones.
rownum can fetch the number of data pieces, for example:
select * from (select rownum no,t.* from emp t order by name) where no between 3 and 7 order by name desc #First, query the number of all rownum entries by sorting, and then take the data of interval 3-7 select rownum,id,name from student where rownum <3; #Find two pieces of data
substr string interception function
substr: function format
Format 1: substr (string, string, int a, int b);
String the string to be intercepted
Start position of interception, (0 and 1 are represented as the first element)
b the length of the string to be intercepted
Format 2: substr (string, int a);
String the string to be intercepted
A from the a-th element to the last element
select ename, substr(ename,1), #Intercept from the first to the last; substr(ename,-3), #Cut from the penultimate to the end; substr(ename,3,3) #Start from the third and intercept 3 bits; from emp; #The format of instr() function (commonly known as character lookup function) is often used in conjunction with instr substr(information,instr(information,'|',1,3)+1,instr(information,'|',1,4)-instr(information,'|',1,3)-1) Age
instr() character lookup function
- instr ('source string ',' target string ',' start position ',' how many occurrences') --- the nth subscript, such as dividing the contents between ｜, which is used in conjunction with substr
- instr ('source string ',' destination string ')
select instr('helloworld','l',2,3) from dual; #Return result: 9, that is, start at position 2(e) of "Hello world" and find the position of "l" for the third time select instr('helloworld','lo') from dual; #Return result: 4, that is, the position where "l" begins to appear in "lo"
decode() conditional function
- Decode (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n, default value)
IF condition=Value 1 THEN RETURN(Return value 1) ELSIF condition=Value 2 THEN RETURN(Return value 2) ...... ELSIF condition=value n THEN RETURN(Return value n) ELSE RETURN(Default value) END IF # Specific sql select id,name, decode(id,1,'first',2,'the second',3,'Third','No,') new_id from t_decode;
- Decode (field or field operation, value 1, value 2, value 3)
The result of this function is that when the value of the field or field operation is equal to the value 1, the function returns the value 2, otherwise it returns the value 3
Of course, values 1, 2 and 3 can also be expressions. This function makes some sql statements much simpler
Often used in conjunction with the sign function:
decode(sign(avg(t.salary + t.bonus)-3000),1,'3000 above',-1,'3000 following',0,'Flat') Average income level
sign() operation function
The sign() function returns 0, 1, - 1 respectively according to whether a value is 0, positive or negative
It is used for operation comparison and is often used in combination with decode.
SELECT sign(120 - 100) FROM DUAL; -- 1 SELECT sign(100 - 100) FROM DUAL; -- 0 SELECT sign(100 - 120) FROM DUAL; -- -1
to_char() character conversion function
You can convert the date format or splice characters that cannot be added in sql, such as single quotation marks: to_char(38)
select a.name,to_char(a.join,'yyyy-mm-dd') date,to_char(a.join,'yyyy"year"mm"month"dd"day"') Chinese display from emp1 a where name='Li Li'
The output style is: [the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-v1mzmqsu-163789341947) (C: \ users \ love \ appdata \ roaming \ typora \ typora user images \ image-20211126091342345. PNG)]
select to_char(sysdate,'dl') from dual; #Gets the current date and day of the week select to_char(sysdate,'yyyy') from dual; #Acquisition year #Character to date select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual #Date to character select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
To_char (data, FM999990.09) formatting function
Where 9 means that if there is a number, the number will be displayed, and there is no space
Where 0 means that if there is a number, the number will be displayed, and if there is no number, 0 will be displayed, that is, a placeholder
Where FM means that if it is a space brought by 9, it will be deleted
to_date date conversion function
- When using Oracle's to_date function for date conversion, you may intuitively use the format of "yyyy MM DD HH: mm: SS" as the format for conversion, but an error will be caused in Oracle: "ORA 01810 format code appears twice". For example, select to_date('2005-01-01 13:14:20 ',' yyyy mm DD hh24: mm: Ss') From dual; because SQL is not case sensitive, mm and mm are considered to be the same format code, so mi is used in Oracle SQL instead of minutes. select to_date('2005-01-01 13:14:20 ',' yyyy MM DD hh24: mi: Ss') from dual;
- In addition, it shall be displayed in the form of 24 hours, and HH24 shall be used
#Specific conversion to time to_date('2001/1/1 12:12:12','yyyy-mm-dd hh24:mi:ss') #Character to date select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual #Date to character select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
MONTHS_BETWEEN (date1, date2): used to calculate the number of months between date1 and date2.
- If date1 is later than date2 in the calendar, MONTHS_BETWEEN() returns a positive number.
- If date1 is earlier than date2 in the calendar, MONTHS_BETWEEN() returns a negative number.
- If date1 and date2 are the same, MONTHS_BETWEEN() returns a 0.
#Calculate the difference between two months. Normally, the big month comes first and the small month comes later select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual; #Sometimes in the same month, 0.2 is displayed without 0 in front, so you need to convert the string Select name full name,to_char(months_between(to_date(sysdate),to_date(to_char(join,'yyyy-mm-dd'),'yyyy-mm-dd') ),'fm9990.09')||'month' Number of months from emp1 where rownum <4;
[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-A9NSCcIj-1637893421951)(C:\Users \ love \ appdata \ roaming \ typora \ typora user images \ image-20211126092245069. PNG)] output style
round() and trunc() functions
- For data retrieval, if the number of digits is enough, trunc takes decimals without rounding, and round should be rounded.
- For time, round is rounded according to the value of the next digit. For example, take the month. When the subsequent days > 15 days, the number of months + 1. trunc directly takes the current month, year and day, and the subsequent values are the original values. For example, 2021 / 11 / 18, the year is 2021 / 1 / 1, and the month is 2021 / 11 / 1
#For the date value, to judge the most accurate gap, you should use round to get the next digit, which is more accurate, such as the month to the day select round(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual #2021/8/1 select trunc(to_date('2001/7/18 ','yyyy-mm-dd'),'mm') from dual #2021/7/1 #Number value select round(5555,-2) from dual #5600 select trunc(5555,-2) from dual #5500 select round(5555.555,2) from dual #5555.56 select trunc(5555.555,2) from dual #5555.55