oracle sqlProvides special functions for performing specific operations. These functions greatly enhance the function of the
SQLlanguage. The function can receive zero or more input parameters and return an output result. This kind
oracleof function provided to us is called built-in function. In addition to built-in functions, you can also customize functions.
According to the return result of the function, we divide the function into single-line function and multi-line function
- Single row function: When corresponding to the table record, one record returns one result. For example
lower(x), convert the parameters to lowercase.
- Multi-line function; also known as
聚合函数[emphasis]: This type of function can operate on multiple records at the same time and return a result [emphasis]. For example:
max(x)find the maximum value.
Commonly used one-line functions
concat(x,y): Connection string
str, you can specify from the
startstart, you can specify from the
xThe length returned
xConvert to lowercase
xConvert to uppercase
trim_strstring on the left side , and the default space is truncated
trim_strcharacter string on the right side , and cut off spaces by default
replace(x,old,new): Find in x
oldand replace with
substr(x,start,length): The returned
xstring starts from the
startbeginning and intercepts
lengthcharacters. If there is no
lengthparameter, it defaults to the end.
abs(x): Find the absolute value of x
floor(x): Round down
mod(x,y): Find the remainder of y on x
sysdate: Get the current time of the system
current_date: Return the current system date
add_months(d1,n1): Return to the new date after
n1months to the date
last_day(d1): Return the date
d1of the last day of the month in which the date is
months_between(d1,d2): Return the number of months between the date
d1and the date
next_day(d1,[c1]): Return the date of
d1the next week, day of the week (parameter
to_char(x,c): Convert date or data into data type
to_date(x,c): Convert the string into a date
to_number(x): Convert a string
xto a number
Common group functions
nullDo not participate in calculations
Here a few functions, for example in order to see the effect of the child, data tables or use
explain the following example used
oracleinside a virtual table, the installation
oraclethere is no need to create additional, the inside piece of data.
select concat(name,age) as new_name from student;
From here, the alias
new_name, the results shown below;
instr(x,str,start,n)：在x中查找str，可以指定从start开始，也可以指定从第n次开始. Here is a requirement to
dualvirtual table is used:
select instr('helloworld','o') from dual;
Here is a note,
helloworldthere are two
字母o, see if you can find them all:
as shown in the figure above, only one can be found, and the first
字母osubscript position is returned . If you want to find the second
oposition, you can add a parameter
select instr('helloworld','o',6) from dual;
In this way,
下标为6the letter of the starting position is specified , and the result of the query is as follows: The
字母oposition is successfully queried .
It can also be used to check whether the student’s name is included
‘花’, and if it does, the location will be displayed:
select instr(name,'花') from student;
翠花the names in this table are included
花. Take a look at the query results:
you can see them here. No names
0, there are flowers on the show the
花position of the word, and there is no aliases, so find out the result set field defaults to the code inside the field.
length(x) ：返回x的长度3: . The
id为5length of the student's name viewed here , of course we know it is
select name,length(name) as length from student where id = 5;
Here, for the convenience of reading, I gave the result set an alias
length, and the result is as shown in the figure below: As
you can see, the result is as expected.
select ltrim(' abc ') || 'abc' as new_str from dual;
' abc 'do an example, the
abctwo sides have two spaces followed by a mosaic
abcof characters, from the alias
new_str, look at the results:
you can see there is no space left, successfully intercepted a space.
select rtrim(' abc ') || 'abc' as new_str from dual;
Next, look at the effect of intercepting spaces on the right:
you can see that the spaces on the right are gone. So what if you want to intercept the spaces on both sides?
select rtrim(ltrim(' abc '))||'A' as new_str from dual;
Here is a splicing one on the right
字母A, because there is no space on the right that is difficult to see with the naked eye. The result is shown in the following figure:
here first remove the space on the left, and then call the right intercept function to remove the space on the right.
Here is an example of querying the surname of a student:
select name,substr(name,1,1) as substr_name from student;
The two functions in the function
参数1are: intercept from the first one, and intercept one.
current_date：返回当前系统日期. Both of these methods can obtain the system time. Here
dualis an example of a virtual table:
select sysdate from dual;
The result is shown in the figure below:
Look at the second method:
select current_date from dual;
Here is a detail, these two functions can be added and subtracted, for example
sysdate+1, here is a demonstration of the effect:
select sysdate+1 from dual;
2021年6月2日, let's take a look at the result:
here you can see that it is the date
加了1, which has become
next_day(d1,[c1])：返回日期d1在下周，星期几（参数c1）的日期7: . Here is an example of next Monday:
select next_day(sysdate,'星期一') as 下周一 from dual;
Here first get the current system time, and then get
下周一the time according to the current system time.
to_char(x,c)：将日期或数据x按照c的格式转换为char数据类型8: . This function is generally used to convert dates into characters.
The first parameter can pass in the date, and the second parameter passes in the character format to be converted. Most of the date formats queried just now are in
2021/6/7 13:46:37this format. I want to see
2021-06-07 13:46:37this format:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
There is a point to note here.
hhYou can fill in
12, after the time , to
24小时制that you can display
13a time value such as a point. If you select it
12小时制, but the current time is greater than the
mian error will be reported, which means minutes and
If you want to display
2021年6月7日 13时46分37秒such a format, how to achieve it?
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') as time_now from dual;
双引号Chinese characters are used here to indicate that this part of the content should be output as it is [important]. The effect is shown in the following figure:
to_date(x,c)：将字符串x按照c的格式转换为日期。This function is generally used to convert a string of character strings that are intercepted to represent the date into a date format.
xRepresents the string to be converted into a date, and
crepresents the format of the converted date.
select to_date('1996/12/26','yyyy/mm/dd')+1 as next_day from dual;
+1used after the date to test whether the conversion is really successful, because the date
+1represents the day after this date,
asfollowed by aliases, to see the effect:
Of course, the string representing the date should be the same as the date format to be converted. Here is a different situation to test:
select to_date('1996/12/26','yyyy-mm-dd')+5 as next_day from dual;
Let's see what the result is:
to_number(x)：将字符串x转换为数字型。This method is not used much. The special feature is that characters can be converted into digital types in a specific base, such as
If you want to convert to
10进制, you can use the method to achieve automatic conversion:
select '12'+1 from dual;
If you do not want to change the value of the numbers themselves, it can be used
+0to achieve here
+1is to see the effect, because the character is not satisfied digit addition and subtraction, so we can add instructions automatically converted opinions success.
Suppose there are a
11such strings, to be converted to
10进制numeric types, how to achieve it
select to_number('11','xx') as res from dual;
xxmeans that the original number is
16进制now converted into
10进制, and the result is as follows:
Example 11: The
avg()：平均值。group functions are all as the name suggests, for example, this is an average value:
select avg(math),avg(english) from student;
Here is to find the average score of the math and English scores in the student table :
Here to find the highest score in the mathematics score of the student table, and display all the information of this person:
select * from student where math = (select max(math) from student);
whereClauses are used here to locate the highest score:
other methods are similar, so I won’t show them one by one here.