This article describes the commonly used MySQL functions. Share with you for your reference, the details are as follows:

In this article:

• Introduction to mysql functions
• Aggregate functions
  • avg
  • count
  • max
  • min
  • sum
• Functions for manipulating strings
  • Merge string function: concat(str1,str2,str3…)
  • Compare string size function: strcmp(str1,str2)
  • Get the number of bytes in a string: length(str)
  • Get the number of characters in a string: char_length(str)
  • Letter case conversion function: uppercase: upper(x),ucase(x); lowercase: lower(x),lcase(x)
  • String search functions
  • Get the substring at the specified position
  • String removal function
  • String replacement function:
• Functions for manipulating numbers
  • Absolute value function: abs(x)
  • Ceiling function: ceil(x)
  • Floor function: floor(x)
  • Modulo function: mod(x,y)
  • Random number function: rand()
  • Rounding function: round(x,y)
  • Numerical truncation function: truncate(x,y)
• Functions for processing time and date
  • Get the current date: curdate(), current_date()
  • Get the current time: curtime(), current_time()
  • Get the current date and time: now()
  • Select the month from the date: month(date), monthname(date)
  • Select the week number from the date: week(date)
  • Select the week number from the date: year(date)
  • Select the hours from the time: hour(time)
  • Select the minutes from the time: minute(time)
  • Select the day of the week from the time: weekday(date), dayname(date)

Release date: 2018-04-14

Introduction to mysql functions:

• To simplify operations, MySQL provides a large number of functions for programmers to use (for example, if you want to enter the current time, you can call the now() function)
• Functions can appear in: values() of insert statements, update statements, delete statements, query statements and their clauses.

Aggregate functions:

• Aggregation functions are used to aggregate records (for example, if you don’t want to know the exact information of each student record, but only want to know the number of student records, you can use count()).
• Aggregate functions are used to process "aggregate data" and do not require detailed record information.
• Aggregate function A function that operates on a group of rows and computes and returns a single value.

Experimental table data (the following running data is based on this table):

create table student(
name varchar(15),
gender varchar(15),
age int
);
insert into student values("lilei","male",18);
insert into student values("alex","male",17);
insert into student values("jack","male",20);
insert into student values("john","male",19);
insert into student values("nullpeople","male",null);

avg(field) function:

• Returns the average value of the data in the specified field
• avg() finds the average value of a specified field by counting the number of rows in a table and summing the data for that field.
• 
• The avg() function ignores rows with NULL column values. If a row's specified field is null, then this row is not counted.

count(field) function:

• Returns the number of rows (number of records) of data in the specified field
• The field can be "*", which represents the number of all records. When it is different from the number of fields, the number of records includes records whose fields are null, while the number of fields does not include records whose fields are null.
• 
• 

max(field) function:

• Returns the maximum value of the data in the specified field
• 
• If the data type of the specified field is a string type, first compare them as strings and then return the maximum value.
• The max() function ignores rows where the column value is null

min(field) function:

• Returns the minimum value of the data in the specified field
• 
• If the data type of the specified field is a string type, first compare them as strings and then return the minimum value.
• The min() function ignores rows where the column value is null

sum(field) function:

• Returns the sum of the data in the specified field
• 
• The sum() function ignores rows where the column value is null

Replenish:

• If the data of the field of the aggregate function is null, the records with null values ​​are ignored.
  • For example, avg: has 5 rows, but only four rows contain age data, so the calculation result only counts the four rows.
  • However, if it is not for a field, it will be calculated. For example, count(x) calculates the number of records, and the null value does not affect the result.
• There are some other standard deviation aggregation functions, which are not described here. If you want to know more, you can search on Baidu.
• Aggregate functions also have an option DISTINCT in version 5.0+, which is similar to select, that is, ignoring the same fields. [Not applicable to count(x)]
  • 

Functions for processing strings:

Merge string function: concat(str1,str2,str3…)

• Used to merge multiple strings into one string. If the passed value contains null, the final result will be null.
• If you want to separate each string in the result of merging multiple strings, you can use concat_ws(delimiter, str1, str2, str3...). If the delimiter passed in is null, the final result will be null (but if str is null at this time, it will not affect the result)

Compare string size function: strcmp(str1,str2)

• Used to compare the size of two strings. Returns 1 when the left is greater than the right, 0 when the left is equal to the right, and -1 when the left is less than the right.
• strcmp is similar to the comparison string function in programming languages ​​(based on ASCII code?), which will compare one by one from left to right until one is not equal and return the result, otherwise compare to the end.
• 

Get the number of bytes in a string: length(str)

• Used to get the byte length of a string (returns the number of bytes, so pay attention to the character set)
• 

Get the number of characters in a string: char_length(str)

• To get the length of a string
• 

Letter case conversion function: uppercase: upper(x),ucase(x); lowercase: lower(x),lcase(x)

• upper(x),ucase(x) are used to convert letters to uppercase, x can be a single letter or a string
• lower(x), lcase(x) are used to convert letters to lowercase. x can be a single letter or a string.
• For those that are already, no case conversion will be performed.

String search functions:

• find_in_set(str1,str2)
  • Returns the position of the string str1 in str2, where str2 contains several strings separated by commas (str2 can be seen as a list, with multiple strings as elements, and the search result is the index position of str1 in the list str2, starting from 1)
  • 
• field(str,str1,str2,str3…)
  • Similar to find_in_set, but str2 is changed from a list-like string to multiple strings, and the position of str in str1, str2, str3... is returned.
  • 
• locate(str1,str2):
  • Returns the position of the substring str1 in the string str2
  • 
• position(str1 IN str2)
  • Returns the position of the substring str1 in the string str2
  • 
• instr(str1,str2)
  • Returns the position of substring str2 in string str1 [note that it is reversed here]
  • 

Get the substring at the specified position:

• elt(index,str1,str2,str3…)
  • Returns the string at the specified index position
  • 
• left(str,n)
  • Intercept n characters from the left of str
  • 
• right(str,n)
  • Intercept n characters from the right of str
  • 
• substring(str,index,len)
  • Extract len ​​characters from the index position of str
  • 

String removal function:

• ltrim(str):
  • Remove spaces from the left side of the string str
  • 
• rtrim(str)
  • Remove spaces from the right side of the string str
  • 
• trim()
  • Remove spaces from both sides of the string str
  • 

String replacement function:

• insert(str1,index,len,str2)
  • Use str2 to replace len elements of str1 starting from the index position of str1
  • 
• replace(str,str1,str2)
  • Replace all substrings str1 in str with str2
  • 

Functions for manipulating numbers:

Absolute value function: abs(x)

• Returns the absolute value of x

Ceiling function: ceil(x)

• Returns the integer value of x rounded up.

Floor function: floor(x)

• Returns the floor value of x.

Modulo function: mod(x,y)

• Returns the result of x mod y

Random number function: rand()

• Returns a random number between 0 and 1
• If you want to use the same random value for a certain situation, you can use rand(x), which will return the same random result when x is the same.

Rounding function: round(x,y)

• Returns the value x with the decimal value y (rounded)
• 

Numerical truncation function: truncate(x,y)

• Returns the value x truncated to y decimal places (not rounded)
• 

Functions for processing time and date:

Get the current date: curdate(), current_date()

• The return format is:

Get the current time: curtime(), current_time()

• The return format is:

Get the current date and time: now()

• The return format is:

Select the month from the date: month(date), monthname(date)

Select the week number from the date: week(date)

• The return format is:

Select the week number from the date: year(date)

• The return format is:

Select the hours from the time: hour(time)

• The return format is:

Select the minutes from the time: minute(time)

• The return format is:

Select the day of the week from the time: weekday(date), dayname(date)

• The return format is:

Date functions are still relatively common. If you want to know more, you can refer to the official documentation:

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

For more functions, please refer to the official documentation (the following is for 5.7):

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.