Summary of MySQL common functions

Preface:

The MySQL database provides a wide range of functions, such as commonly used aggregate functions, date and string processing functions, etc. These functions can be used in both SELECT statements and their conditional expressions. Functions can help users process data in tables more conveniently, making the MySQL database more powerful. This article mainly introduces the usage of several common functions.

1. Aggregate Functions

Aggregate functions are a type of function that is commonly used. They are listed below:

COUNT(col) counts the number of rows in the query result
MIN(col) Query the minimum value of the specified column
MAX(col) Query the maximum value of the specified column
SUM(col) returns the sum of the specified column
AVG(col) returns the average value of the specified column data

2. Numerical functions

Numerical functions are mainly used to process numerical data to obtain the desired results. Several commonly used ones are listed below. You can try their specific usage.

ABS(x) Returns the absolute value of x
BIN(x) returns the binary value of x
CEILING(x) Returns the smallest integer value greater than x.
EXP(x) returns the value e (the base of the natural logarithm) raised to the power of x
FLOOR(x) returns the largest integer value less than x.
GREATEST(x1,x2,...,xn) returns the largest value in a set
LEAST(x1,x2,...,xn) Returns the smallest value in a set
LN(x) returns the natural logarithm of x
LOG(x,y) Returns the logarithm of x to base y
MOD(x,y) returns the modulus (remainder) of x/y
PI() Returns the value of pi (pi)
RAND() returns a random value between 0 and 1. You can provide a parameter (seed) to make the RAND() random number generator generate a specified value.
ROUND(x,y) Returns the value of parameter x rounded to y decimal places
TRUNCATE(x,y) returns the number x truncated to y decimal places.

Some examples:

# ABS() function calculates the absolute valuemysql> SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0);
+--------+-----------+----------+--------+
| ABS(5) | ABS(-2.4) | ABS(-24) | ABS(0) |
+--------+-----------+----------+--------+
| 5 | 2.4 | 24 | 0 |
+--------+-----------+----------+--------+

# The rounding functions CEIL(x) and CEILING(x) have the same meaning and return the smallest integer value not less than xmysql> SELECT CEIL(-2.5),CEILING(2.5);
+------------+--------------+
| CEIL(-2.5) | CEILING(2.5) |
+------------+--------------+
| -2 | 3 |
+------------+--------------+

# The remainder function MOD(x,y) returns the remainder after dividing x by ymysql> SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);
+-----------+-------------+-------------+
| MOD(63,8) | MOD(120,10) | MOD(15.5,3) |
+-----------+-------------+-------------+
| 7 | 0 | 0.5 |
+-----------+-------------+-------------+

# When the RAND() function is called, it can generate a random number between 0 and 1 mysql> SELECT RAND(), RAND(), RAND();
+---------------------+--------------------+----------------------+
| RAND() | RAND() | RAND() |
+---------------------+--------------------+----------------------+
| 0.24996517063115273 | 0.9559759106077029 | 0.029984071878701515 |
+---------------------+--------------------+----------------------+

3. String functions

String functions can process string type data and are quite useful in program applications. Here are a few commonly used ones:

LENGTH(s) Calculates the length of a string and returns the byte length of the string
CONCAT(s1,s2...,sn) String concatenation function, returns the string generated by concatenating the parameters, which can be one or more
INSERT(str,x,y,instr) replaces the substring of string str starting from position x and y characters long with string instr and returns the result
LOWER(str) Converts the letters in a string to lowercase
UPPER(str) Converts the letters in a string to uppercase
LEFT(str,x) Returns the leftmost x characters in the string str
RIGHT(str,x) Returns the rightmost x characters in the string str
TRIM(str) removes spaces on both sides of a string
REPLACE string replacement function, returns the new string after replacement
SUBSTRING intercepts a string and returns a string of a specified length starting from a specified position.
REVERSE(str) Returns the result of reversing the string str

Some examples:

# The return value of the LENGTH(str) function is the byte length of the string mysql> SELECT LENGTH('name'), LENGTH('database');
+----------------+---------------------+
| LENGTH('name') | LENGTH('database') |
+----------------+---------------------+
| 4 | 9 |
+----------------+---------------------+

# CONCAT(sl, s2, ...) function returns the string generated by concatenating the parameters. If any parameter is NULL, the return value is NULL.
mysql> SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL);
+----------------------+----------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) |
+----------------------+----------------------+
| MySQL5.7 | NULL |
+----------------------+----------------------+

# INSERT(s1, x, len, s2) returns the string s1 with the substring starting at position x and replaces s2 with a string of len characters long
mysql> SELECT INSERT('Football',2,4,'Play') AS col1,
  -> INSERT('Football',-1,4,'Play') AS col2,
  -> INSERT('Football',3,20,'Play') AS col3;
+----------+----------+--------+
| col1 | col2 | col3 |
+----------+----------+--------+
| FPlayall | Football | FoPlay |
+----------+----------+--------+

# UPPER, LOWER are case conversion functions mysql> SELECT LOWER('BLUE'), LOWER('Blue'), UPPER('green'), UPPER('Green');
+---------------+---------------+----------------+----------------+
| LOWER('BLUE') | LOWER('Blue') | UPPER('green') | UPPER('Green') |
+---------------+---------------+----------------+----------------+
| blue | blue | GREEN | GREEN |
+---------------+---------------+----------------+----------------+

# LEFT, RIGHT is a function to intercept the left or right string mysql> SELECT LEFT('MySQL',2), RIGHT('MySQL',3);
+-----------------+------------------+
| LEFT('MySQL',2) | RIGHT('MySQL',3) |
+-----------------+------------------+
| My | SQL |
+-----------------+------------------+

# REPLACE(s, s1, s2) replaces all occurrences of string s1 in string s with string s2
mysql> SELECT REPLACE('aaa.mysql.com','a','w');
+----------------------------------+
| REPLACE('aaa.mysql.com','a','w') |
+----------------------------------+
| www.mysql.com |
+----------------------------------+

# The function SUBSTRING(s, n, len) takes the format of the len parameter and returns a substring of length len characters from the string s, starting at position n
mysql> SELECT SUBSTRING('computer',3) AS col1,
  -> SUBSTRING('computer',3,4) AS col2,
  -> SUBSTRING('computer',-3) AS col3,
  -> SUBSTRING('computer',-5,3) AS col4;
+--------+------+------+------+
| col1 | col2 | col3 | col4 |
+--------+------+------+------+
| mputer | mput | ter | put |
+--------+------+------+------+

4. Date and time functions

The CURDATE and CURRENT_DATE functions have the same function, returning the current system date value.
The CURTIME and CURRENT_TIME functions have the same function, returning the current system time value.
The NOW and SYSDATE functions have the same function, returning the current system date and time value.
UNIX_TIMESTAMP Get UNIX timestamp function, return an unsigned integer based on UNIX timestamp
FROM_UNIXTIME converts UNIX timestamp to time format, which is the inverse function of UNIX_TIMESTAMP
MONTH Gets the month in the specified date
MONTHNAME Gets the English name of the month in the specified date
DAYNAME Gets the English name of the day of the week corresponding to the specified date
DAYOFWEEK Gets the index position value of the week corresponding to the specified date
WEEK Gets the week of the year for the specified date. The return value is in the range of 0 to 52 or 1 to 53.
DAYOFYEAR Gets the day of the year for the specified date. The return value range is 1~366
DAYOFMONTH Gets the day of the month for a specified date. The return value range is 1 to 31
YEAR Gets the year, the return value range is 1970 to 2069
TIME_TO_SEC Converts a time parameter to seconds
SEC_TO_TIME converts seconds to time, which is the inverse function of TIME_TO_SEC
The DATE_ADD and ADDDATE functions have the same function, both of which add a specified time interval to a date.
The DATE_SUB and SUBDATE functions have the same function, both of which subtract a specified time interval from a date.
ADDTIME Time addition operation, add the specified time to the original time
SUBTIME time subtraction operation, subtract the specified time from the original time
DATEDIFF Gets the interval between two dates and returns the value of parameter 1 minus parameter 2.
DATE_FORMAT formats the specified date and returns the value in the specified format according to the parameter
WEEKDAY Gets the corresponding weekday index of the specified date in a week

Some examples:

# The CURDATE() and CURRENT_DATE() functions have the same function, returning the current date in "YYYY-MM-DD" or "YYYYMMDD" format. mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;
+------------+----------------+------------------+
| CURDATE() | CURRENT_DATE() | CURRENT_DATE()+0 |
+------------+----------------+------------------+
| 2019-10-22 | 2019-10-22 | 20191022 |
+------------+----------------+------------------+

# The MONTH(date) function returns the month corresponding to the specified datemysql> SELECT MONTH('2017-12-15');
+---------------------+
| MONTH('2017-12-15') |
+---------------------+
| 12 |
+---------------------+

# DATE_ADD(date,INTERVAL expr type) and ADDDATE(date,INTERVAL expr type) have the same function, both of which are used to perform date addition operations.
mysql> SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,
  -> DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS C2,
  -> ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;
+---------------------+---------------------+---------------------+
| C1 | C2 | C3 |
+---------------------+---------------------+---------------------+
| 2018-11-01 00:00:00 | 2018-11-01 00:01:00 | 2018-11-01 00:00:00 |
+---------------------+---------------------+---------------------+

# DATEDIFF(date1, date2) returns the number of days between the start time date1 and the end time date2mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
  -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| col1 | col2 |
+------+------+
| 1 | -15 |
+------+------+

# The DATE_FORMAT(date, format) function displays the date value according to the format specified by format mysql> SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,
  -> DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;
+------------------------------+----------------------------+
| col1 | col2 |
+------------------------------+----------------------------+
| Wednesday November 15th 2017 | 09:1 PM November 15th 2017 |
+------------------------------+----------------------------+

5. Process control function

Process control functions can perform conditional operations to implement SQL conditional logic, allowing developers to convert some application business logic to the database backend, as listed below:

IF(test,t,f) If test is true, return t; otherwise return f
IFNULL(arg1,arg2) If arg1 is not null, return arg1, otherwise return arg2
NULLIF(arg1,arg2) returns NULL if arg1=arg2; otherwise returns arg1
CASE WHEN [test1] THEN [result1]...ELSE [default] END If testN is true, then return resultN, otherwise return default
CASE [test] WHEN [val1] THEN [result]...ELSE [default]END If test and valN are equal, then resultN is returned, otherwise default is returned

Some examples:

# The IF statement allows you to execute a group of SQL statements based on a condition or value of an expression mysql> SELECT IF(1<2,1,0) c1,IF(1>5,'√','×') c2,IF(STRCMP('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3 |
+----+----+-----+
| 1 | × | yes |
+----+----+-----+

# IFNULL accepts two arguments and returns the first argument if the first is not NULL. Otherwise, the IFNULL function returns the second argumentmysql> SELECT IFNULL(5,8),IFNULL(NULL,'OK');
+-------------+-------------------+
| IFNULL(5,8) | IFNULL(NULL,'OK') |
+-------------+-------------------+
| 5 | OK |
+-------------+-------------------+

# NULLIF functionmysql> SELECT NULLIF(5,8),NULLIF(8,8);
+-------------+-------------+
| NULLIF(5,8) | NULLIF(8,8) |
+-------------+-------------+
| 5 | NULL |
+-------------+-------------+

# CASE WHEN functionmysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true |
+--------------------------------------------+
mysql> SELECT CASE 11 WHEN 1 THEN 'one'
  -> WHEN 2 THEN 'two' ELSE 'more' END;
+-------------------------------------------------------------+
| CASE 11 WHEN 1 THEN 'one'
WHEN 2 THEN 'two' ELSE 'more' END |
+-------------------------------------------------------------+
| more |
+-------------------------------------------------------------+

6. Encryption Function

The encryption function is mainly used to encrypt strings. Several commonly used ones are listed below:

ENCRYPT(str,salt) uses the UNIXcrypt() function to encrypt the string str with the keyword salt (a string that can uniquely identify the password, like a key)
ENCODE(str,key) Encrypts the string str using key as the key. The result of calling ENCODE() is a binary string, which is stored as a BLOB type.
MD5() Calculates the MD5 checksum of the string str
PASSWORD(str) returns the encrypted version of the string str. This encryption process is irreversible and uses a different algorithm than the UNIX password encryption process.
SHA() Calculates the Secure Hash Algorithm (SHA) checksum of the string str

Some examples:

# ENCRYPT functionmysql> SELECT ENCRYPT('root','salt');
+------------------------+
| ENCRYPT('root','salt') |
+------------------------+
|saFKJij3eLACw |
+------------------------+

# MD5() functionmysql> SELECT MD5('123456');
+----------------------------------+
| MD5('123456') |
+----------------------------------+
|e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+

# PASSWORD functionmysql> select PASSWORD('abcd');
+-------------------------------------------+
| PASSWORD('abcd') |
+-------------------------------------------+
| *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
+-------------------------------------------+

Summarize:

This article mainly introduces the functions of commonly used MySQL functions and briefly illustrates the usage of some functions. In actual environments, these functions may be nested and used in a much more complicated way. I hope you will pay more attention to the role of each parameter when using them.

This is the end of this article about the summary of commonly used MySQL functions. For more relevant MySQL commonly used functions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL stored procedures and common function code analysis
Summary of common MySQL function examples [aggregate functions, strings, numbers, time and date processing, etc.]
A complete list of commonly used MySQL functions (classified and summarized)
Common functions in MySQL
Summary of commonly used MySQL functions (sharing)
MySQL detailed summary of commonly used functions

<<: Detailed explanation of mixins in Vue.js

>>: Zabbix monitors Linux hosts based on snmp

Detailed explanation of Vue's self-implementation of dispatch and broadcast (dispatch and broadcast)

Summary of MySQL common functions

Detailed explanation of Vue's self-implementation of dispatch and broadcast (dispatch and broadcast)

CSS to achieve the first row and first column of the table fixed and adaptive window example code

Summary of Linux user groups and permissions

Build Tomcat9 cluster through Nginx and realize session sharing

Examples of using HTML list tags dl, ul, ol

Detailed explanation of the use of stat function and stat command in Linux

mysql 5.6.23 winx64.zip installation detailed tutorial

How MySQL uses transactions

About the layout method of content overflow in table

MySQL database transaction example tutorial

Recommend

CSS sets Overflow to hide the scroll bar while allowing scrolling

Pure client-side and pure server-side implementation solutions for HTML to PDF conversion

Introduction to the use and disabling of transparent huge pages in Linux

NestJs uses Mongoose to operate MongoDB

mysql method to recursively search for all child nodes of a menu node

Detailed explanation of MySQL's MERGE storage engine

How to implement online hot migration of KVM virtual machines (picture and text)

Use of TypeScript Generics

How to store images in MySQL

MySQL 8.0.12 installation configuration method and password change

Vue+el-table realizes merging cells

MySQL scheduled backup solution (using Linux crontab)

The process of building a Jenkins project under Linux (taking CentOS 7 as an example)

Summarize the common application problems of XHTML code

Query process and optimization method of (JOIN/ORDER BY) statement in MySQL