1.Select query data in the table
Select * from Stu;---Query the stu table for all data, * represents all
2.dual , pseudo-table, the data to be queried does not exist in any table when used
select sysdate from dual;----Query the current time of the system
3.concat Connect two strings to a string
Select Concat (' Lone boat ', ' snow-fishing ') str from dual;---str is a field name (pickup)
equivalent operation: | | (connector)
select ' Mo, ' | | ' The young head of the white, ' | | ' Empty mournful. ' Str from dual;
4. Length Returns the lengths of the strings, in units of characters
Select Length (name) from Stu;
5.Upper(uppercase),lower(lowercase),inicap(capital letter)
Select Upper (upper), lower (lower), Inicap (INICAP) from dual;---upper/lower/inicap
6.Trim---Remove duplicate strings on both sides: only one character can be intercepted
LTrim---Remove the repeated left string: Multiple characters can be intercepted
RTrim---Remove the repeating string on the right: multiple characters can be intercepted
Select Trim (' n ' from ' Come and go with every season ') from dual;
Select LTrim (' Come and go with every season ', ' Con ') from dual;---me and go with every season
Select RTrim (' Come and go with every season ', ' Eason ') from dual;---Come and go with every
7.substrIntercept string substr (char,start,length)
Select substr (name,1,2) from Stu;
8.Lpad/rpadLeft and right complement
Select Lpad (name,12, ' * '), Rpad (name,12, ' * ') from Stu; use (*) to complement a name that is less than 12 characters long
9.InStr(Char1,char2[,n,m])---Find the position of the string char2 in Char1,
N: Starting from the first few characters of Char1, the m:char2 character appears several times, the default is 1
InStr (' InStr ', ' s ') from dual; ---3
10.round(N[,m]): The number n is rounded, M is reserved number n decimal point, and does not write default is 0
Select Round (55.555) from dual;---56
Select Round (55.555,2) from dual;---55.56
Select Round (55.555,-1) from dual;---60
11.trunc(N[,m]): Direct interception of data, N, m with round N, m
Select Trunc (55.555,2) from dual;---55.55
12.Ceil,floor: Up (down) rounding, only one parameter
Select Ceil (55.55), floor (55.55) from dual;---56,55
13.mod (n,m): Returns the remainder after n divided by M
Select mod (14,5) from dual; ---4
14.Date: Date type, 7 bytes
Timestamp: Date type, 11 bytes, can store fractional seconds
Date can be calculated, return the number of days
YY 2-digit year YYYY 4-digit year MM 2-digit month MON Jane spell month
Month full spell months DD 2-digit days DY weeks of abbreviation day weeks of the full spell
HH24 24-Hour hour HH12 12-hour Hour
MI Display sub SS display seconds AM on PM
to_date: You can convert a given string to a date type
Select To_date (' 2017-12-12 20:05:22 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;
To_char: You can convert a date to a string in a fixed format
Select To_char (sysdate, ' yyyy-mm-dd am Hh24:mi:ss ') from dual;
last_day (date): Returns the end date of the month for the given date
add_months (data,i) : Returns the date after a given date plus I months
Months_between (date1,date2) : How many months date1 the count varies between date2
extract () : Extracting time components for a given date
Select extract (months from sysdate) from dual;
Select Extract (second from systimestamp) from dual;
least/greatest Returns the minimum (large) value, which can be compared in the following types: number, date
Oracle (3) keyword descriptions commonly used in SELECT statements