Mysql functions are used to implement certain functional operations and complete various specific operations; using functions can allow standard component programming, improve the reusability, sharing and portability of SQL statements, and can Reduce the workload of repeatedly writing program segments, improve program readability, improve program compilation and operation efficiency, and produce higher-quality target code.
The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.
The role of function:
Function is used to implement certain functional operations and complete various specific operations important means.
Advantages:
Allows standard component programming, improving the reusability, sharing and portability of SQL statements.
can reduce the workload of repeatedly writing program segments and improve program readability.
Improve program compilation and running efficiency, produce higher quality target code, and meet the "correctness, readability, robustness, efficiency and low storage capacity" of algorithm design “needs” basic requirements.
Able to achieve faster execution speed and reduce network traffic
Expand knowledge
MySQL database provides a wide range of functions, such as our commonly used aggregate functions, date and string processing functions, etc. These functions can be used in SELECT statements and their conditional expressions. Functions can help users process data in the table more conveniently, making the MySQL database more powerful. This article mainly introduces the usage of several common functions.
1. Aggregation function
Aggregation function is a type of function that is commonly used in daily life. Here are the following:
COUNT(col) Statistical query results Number of rows
MIN(col) Query the minimum value of the specified column
MAX(col) Query the maximum value of the specified column
SUM(col) Sum and return the specified Sum of columns
AVG(col) Find the average and return the average of the specified column data
2. Numerical function
Numerical function It mainly processes numerical data to get the results we want. Some commonly used ones are listed below. You can try them out for specific usage methods.
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 the set
LEAST(x1,x2,...,xn) Returns the smallest value in the set
LN(x) Returns the natural pair of x Number
LOG(x,y) Returns the base y logarithm of x
MOD(x,y) Returns the modulus (remainder) of x/y
PI() Returns the value of pi (pi ratio)
RAND() Returns a random value between 0 and 1. You can make the RAND() random number generator generate a specified value by providing a parameter (seed)
ROUND(x,y) Returns the rounded value of parameter x with y decimal places
TRUNCATE(x,y) Returns the result of number x truncated to y decimal places
Some examples:
# ABS()函数求绝对值 mysql> SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0); +--------+-----------+----------+--------+ | ABS(5) | ABS(-2.4) | ABS(-24) | ABS(0) | +--------+-----------+----------+--------+ | 5 | 2.4 | 24 | 0 | +--------+-----------+----------+--------+ # 取整函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值 mysql> SELECT CEIL(-2.5),CEILING(2.5); +------------+--------------+ | CEIL(-2.5) | CEILING(2.5) | +------------+--------------+ | -2 | 3 | +------------+--------------+ # 求余函数 MOD(x,y) 返回 x 被 y 除后的余数 mysql> 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 | +-----------+-------------+-------------+ # RAND() 函数被调用时，可以产生一个在 0 和 1 之间的随机数 mysql> SELECT RAND(), RAND(), RAND(); +---------------------+--------------------+----------------------+ | RAND() | RAND() | RAND() | +---------------------+--------------------+----------------------+ | 0.24996517063115273 | 0.9559759106077029 | 0.029984071878701515 | +---------------------+--------------------+----------------------+
3. String function
The string function can process string type data and is quite useful in program applications. , here are a few commonly used ones:
LENGTH(s) Calculates the string length function, returns the byte length of the string
CONCAT(s1,s2...,sn) Merge string function, the return result is the string generated by the connection parameters. The parameters can be one or more
INSERT(str,x,y,instr). The string str starts from the x-th position, y Replace the character-long substring with the string instr and return the result
LOWER(str) Convert the letters in the string to lowercase
UPPER(str) Convert the letters in the string to Upper case
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) Delete the spaces on the left and right sides of the string
REPLACE String replacement function, return the new string after replacement
SUBSTRING Intercept the string and return it starting from the specified position Replace the characters of the specified length
REVERSE(str) Returns the result of reversing the string str
Some examples:
# LENGTH(str) 函数的返回值为字符串的字节长度
mysql> SELECT LENGTH('name'),LENGTH('数据库');
+----------------+---------------------+
| LENGTH('name') | LENGTH('数据库') |
+----------------+---------------------+
| 4 | 9 |
+----------------+---------------------+
# CONCAT(sl，s2，...) 函数返回结果为连接参数产生的字符串 若有任何一个参数为 NULL，则返回值为 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) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2
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是大小写转换函数
mysql> SELECT LOWER('BLUE'),LOWER('Blue'),UPPER('green'),UPPER('Green');
+---------------+---------------+----------------+----------------+
| LOWER('BLUE') | LOWER('Blue') | UPPER('green') | UPPER('Green') |
+---------------+---------------+----------------+----------------+
| blue | blue | GREEN | GREEN |
+---------------+---------------+----------------+----------------+
# LEFT,RIGHT是截取左边或右边字符串函数
mysql> SELECT LEFT('MySQL',2),RIGHT('MySQL',3);
+-----------------+------------------+
| LEFT('MySQL',2) | RIGHT('MySQL',3) |
+-----------------+------------------+
| My | SQL |
+-----------------+------------------+
# REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1
mysql> SELECT REPLACE('aaa.mysql.com','a','w');
+----------------------------------+
| REPLACE('aaa.mysql.com','a','w') |
+----------------------------------+
| www.mysql.com |
+----------------------------------+
# 函数 SUBSTRING(s，n，len) 带有 len 参数的格式，从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 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 |
+--------+------+------+------+Recommended learning:mysql video tutorial
The above is the detailed content of What is the function of mysql function. For more information, please follow other related articles on the PHP Chinese website!