oracle database: commonly used functions

Function introduction

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

  1. Single row function: When corresponding to the table record, one record returns one result. For example lower(x), convert the parameters to lowercase.
  2. Multi-line function; also known as 组函数or 聚合函数[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

Character function:

  • concat(x,y): Connection string xandy
  • instr(x,str,start,n): In xthe lookup str, you can specify from the startstart, you can specify from the nstart times
  • length(x): xThe length returned
  • lower(x): xConvert to lowercase
  • upper(x): xConvert to uppercase
  • ltrim(x,trim_str)xTruncate the trim_strstring on the left side , and the default space is truncated
  • rtrim(x,trim_str)xTruncate the trim_strcharacter string on the right side , and cut off spaces by default
  • replace(x,old,new): Find in x oldand replace withnew
  • substr(x,start,length): The returned xstring starts from the startbeginning and intercepts lengthcharacters. If there is no lengthparameter, it defaults to the end.

Mathematical function

  • abs(x): Find the absolute value of x
  • ceil(x):Rounded up
  • floor(x): Round down
  • mod(x,y): Find the remainder of y on x

Date function

  • 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 d1adding 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 dated2
  • next_day(d1,[c1]): Return the date of d1the next week, day of the week (parameter c1).

Conversion function

  • to_char(x,c): Convert date or data into data type xaccording to cthe formatchar
  • to_date(x,c): Convert the string into a date xaccording to cthe format
  • to_number(x): Convert a string xto a number

Common group functions

  • avg():average value
  • sum(): Summation
  • min(): Minimum
  • max(): Maximum
  • count():statistics
  • Note: nullDo not participate in calculations

Here a few functions, for example in order to see the effect of the child, data tables or use studenttable:

Insert picture description here

explain the following example used dualis oracleinside a virtual table, the installation oraclethere is no need to create additional, the inside piece of data.
Example 1 concat(x,y): 连接字符串x和y,实现将姓名和年龄连接起来::

select concat(name,age) as new_name from student;

From here, the alias new_name, the results shown below;

Insert picture description here

Example 2: instr(x,str,start,n):在x中查找str,可以指定从start开始,也可以指定从第n次开始. Here is a requirement to helloworldfind out 字母othat the 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:

Insert picture description here

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

Insert picture description here

second 字母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;

Only 翠花the names in this table are included . Take a look at the query results:

Insert picture description here

you can see them here. No names are displayed 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.

Example length(x) :返回x的长度3: . The id为5length of the student's name viewed here , of course we know it is 2.

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

Insert picture description here

you can see, the result is as expected.

Example ltrim(x,trim_str):把x左边截去trim_str字符串,缺省截去空格4: .

select ltrim('  abc  ') || 'abc' as new_str from dual;

Here we ' 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:

Insert picture description here

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:

Insert picture description here

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:

Insert picture description here

here first remove the space on the left, and then call the right intercept function to remove the space on the right.

Example 5: substr(x,start,length):返回x的字符串,从start处开始,截取length个字符,如果没length参数,默认到结尾。
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.

Insert picture description here

Example 6: sysdate:获取系统当前时间, 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:

Insert picture description here

Look at the second method:

select current_date from dual;


Insert picture description here

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;

Today is 2021年6月2日, let's take a look at the result:

Insert picture description here

here you can see that it is the date 加了1, which has become 6月3号.

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

Insert picture description here

Example 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 24or 12, after the time , to 24indicate 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 12point, mian error will be reported, which means minutes and ssseconds.
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:

Insert picture description here

Example 9: 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;

Here is +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:

Insert picture description here

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:

Insert picture description here

Example 10: 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 2进制,8进制etc.
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.

Insert picture description here

Suppose there are a 16进制number of 11such strings, to be converted to 10进制numeric types, how to achieve it

select to_number('11','xx') as res from dual;

This xxmeans that the original number is 16进制now converted into 10进制, and the result is as follows:

Insert picture description here

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 :

Insert picture description here

Example 12: max():最大值。
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:

Insert picture description here

other methods are similar, so I won’t show them one by one here.