Comprehensive summary of mysql functions

Comprehensive summary of mysql functions

1. Commonly used string functions in MySQL

function Function
CANCAT(S1,S2,…Sn)
Concatenate S1, S2, ...Sn into a string
INSERT(str,x,y,instr)
Replace the substring of string str starting at position x and y characters long with string instr
LOWER(str)
Change all characters in string str to lowercase
UPPER(str)
Convert all characters in string str to uppercase
LEFT(str, x)
Returns the leftmost x characters of the string str
RIGHT(str,x)
Returns the rightmost x characters of the string str
LPAD(str,n,pad)
Use the string pad to pad the left side of str until the length is n characters.
RPAD(str,n,pad)
Use the string pad to pad the right side of str until the length is n characters.
LTRIM(str)
Remove spaces from the left side of the string str
RTRIM(str)
Remove the trailing spaces from the string str
REPEAT(str,x)
Returns str repeated x times
REPLACE(str,a,b)
Replace all occurrences of string a in string str with string b
STRCMP(s1,s2)
Compares strings s1 and s2
TRIM(str)
Remove spaces from the beginning and end of a string
SUBSTRING(str,x,y)
Returns a string of length y characters starting from position x in the string str

1: CANCAT(S1,S2,…Sn) function connects the passed parameters into a string.

Note: After concatenating with null, it returns NULL directly

mysql> select concat('aaa','bbb','d'),concat('dd',null);
+-------------------------+-------------------+
| concat('aaa','bbb','d') | concat('dd',null) |
+-------------------------+-------------------+
| aaabbbd | NULL |
+-------------------------+-------------------+
1 row in set (0.00 sec)

2: INSERT(str, x, y, instr) function: replaces the substring of string str starting from position x and y characters long with string instr.

Replace the 3 characters starting from the 3rd character in the string "123456" with "me"

mysql> select insert('123456',3,3,'me');
+---------------------------+
| insert('123456',3,3,'me') |
+---------------------------+
|12me6|
+---------------------------+
1 row in set (0.02 sec)

3: LOWER(str) and UPPER(str) functions: convert a string to lowercase or uppercase.

mysql> select lower("ZHANG"),upper("zhang");
+----------------+----------------+
| lower("ZHANG") | upper("zhang") |
+----------------+----------------+
| zhang | ZHANG |
+----------------+----------------+
1 row in set (0.00 sec)

4: LEFT(str,x) and RIGHT(str,x) functions: return the leftmost x characters and rightmost x characters of the string respectively. Note: If the second argument is NULL, no string will be returned.

mysql> select left('zhang',2),left('zhang',null),right('zhang',2),left('zhang',10);
+-----------------+--------------------+------------------+------------------+
| left('zhang',2) | left('zhang',null) | right('zhang',2) | left('zhang',10) |
+-----------------+--------------------+------------------+------------------+
| zh | NULL | ng | zhang |
+-----------------+--------------------+------------------+------------------+
1 row in set (0.00 sec)

5: LPAD(str,n,pad) and RPAD(str,n,pad) functions: pad the leftmost and rightmost parts of str with the string pad until the length is n characters.

mysql> select lpad('bin',8,'zhang'),rpad('zhang',8,'bin'),rpad('beijing',20,'2008');
+----------------------+-----------------------+---------------------------+
| lpad('bin',8,'zhang') | rpad('zhang',8,'bin') | rpad('beijing',20,'2008') |
+----------------------+-----------------------+---------------------------+
| zhangbin | zhangbin | beijing2008200820082 |
+----------------------+-----------------------+---------------------------+
1 row in set (0.00 sec)

6: LTRIM(str) and RTRIM(str) functions: remove spaces on the left and right sides of the string str.

mysql> select ' zhang',ltrim(' zhang'),rtrim('zhang ');
+----------+-------------------+--------------------+
| zhang | ltrim(' zhang') | rtrim('zhang ') |
+----------+-------------------+--------------------+
| zhang | zhang | zhang |
+----------+-------------------+--------------------+
1 row in set (0.00 sec)

7:REPEAT(str,x) function: returns the result of str repeated x times.

mysql> select repeat('mysql',5);
+---------------------------+
| repeat('mysql',5) |
+---------------------------+
|mysqlmysqlmysqlmysqlmysql|
+---------------------------+
1 row in set (0.00 sec)

8:REPLACE(str,a,b) function: Replace all occurrences of string a in string str with string b.

mysql> select replace('mysql','sql','ddd');
+------------------------------+
| replace('mysql','sql','ddd') |
+------------------------------+
|myddd|
+------------------------------+
1 row in set (0.00 sec)

9:STRCMP(s1,s2) function: compares the ASCII code values ​​of strings s1 and s2. Returns -1 if s1 is less than s2, 0 if s1 is equal to s2, and 1 if s1 is greater than s2.

mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b'),strcmp('a','A');
+-----------------+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') | strcmp('a','A') |
+-----------------+-----------------+-----------------+-----------------+
| -1 | 0 | 1 | 0 |
+-----------------+-----------------+-----------------+-----------------+
1 row in set (0.01 sec)

10:TRIM(str) function: removes spaces at the beginning and end of the target string.

mysql> select trim(' 111 $mysql$ ');
+-----------------------------+
| trim(' 111 $ mysql $ ') |
+-----------------------------+
| 111 $mysql$ |
+-----------------------------+
1 row in set (0.01 sec)

11:SUBSTRING(str,x,y) function: Returns a string of y characters starting from the xth position in the string str.

mysql> select substring('mysqlisdd',4,4);
+----------------------------+
| substring('mysqlisdd',4,4) |
+----------------------------+
|qlis|
+----------------------------+
1 row in set (0.00 sec)

2. Numerical functions

function
 Function
ABS(x)
Returns the absolute value of x
CEIL(x)
Returns the largest integer value greater than x
FLOOR(x)
Returns the largest integer value less than x
MOD(x,y)
Returns the modulus of x/y
RAND()
Returns a random value between 0 and 1
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.

1: ABS(x) function: returns the absolute value of x.

mysql> select abs(-0.3),abs(0.3);
+-----------+----------+
| abs(-0.3) | abs(0.3) |
+-----------+----------+
| 0.3 | 0.3 |
+-----------+----------+
1 row in set (0.36 sec)

2: CEIL(x) function, returns the largest integer greater than x

mysql> select ceil(-0.2),ceil(0.2);
+------------+-----------+
| ceil(-0.2) | ceil(0.2) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.00 sec)

3: FLOOR(x) function, returns the largest integer less than x, which is exactly the opposite of CEIL

mysql> select floor(-0.2),floor(0.2);
+-------------+------------+
| floor(-0.2) | floor(0.2) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)

4: MOD(x, y) function: returns the modulus of x/y. The result is the same as x%y. If either the modulus or the modulo is NULL, the result is NULL. As shown in the following example:

mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
| 5 | 1 | NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)

5: RAND() function, returns a random number between 0 and 1

mysql> select rand(),rand();
+-------------------+---------------------+
| rand() | rand() |
+-------------------+---------------------+
| 0.541937319135235 | 0.10546984067696132 |
+-------------------+---------------------+
1 row in set (0.00 sec)

6: ROUND(x,y) function, returns the rounded value of parameter x with y decimal places. If it is an integer, y digits of 0 will be retained; if y is not written, y is assumed to be 0, that is, x is rounded to the nearest integer.

mysql> select round(1.2,2),round(1.3),round(1,2);
+--------------+------------+------------+
| round(1.2,2) | round(1.3) | round(1,2) |
+--------------+------------+------------+
| 1.20 | 1 | 1 |
+--------------+------------+------------+
1 row in set (0.00 sec)

7: TRUNCATE(x,y) function: Returns the result of number x truncated to y decimal places. (Note that the difference between TRUNCATE and ROUND is that TRUNCATE only truncates, not rounds.)

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.31 sec)

3. Date and time functions

Common date and time functions in MySQL:

function Function
CURDATE() Returns the current date
CURTIME() Returns the current time
NOW() Returns the current date and time
UNIX_TIMESTAMP(date) Returns the UNIX timestamp of date
FROM_UNIXTIME Returns the date value as a UNIX timestamp
WEEK(date) Returns the week of the year for date
YEAR(date) Returns the year of date
HOUR(time) Returns the hour value of time
MINUTE(time) Returns the minute value of time
MONTHNAME(date) Returns the month name of date
DATE_FORMAT(date,fmt) Returns the date value formatted according to the string fmt
DATE_ADD(date,INTERVAL expr type) Returns a date or time value plus a time interval
DATEDIFF(expr,expr2) Returns the number of days between start time expr and end time expr2

1: CURDATE() function: Returns the current date, including only year, month and day

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2021-05-25 |
+------------+
1 row in set (0.00 sec)

2: CURTIME() function: returns the current time, including only hours, minutes and seconds

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:07:06 |
+-----------+
1 row in set (0.00 sec)

3: NOW() function: Returns the current date and time, including year, month, day, hour, minute and second.

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-05-25 14:07:33 |
+---------------------+
1 row in set (0.00 sec)

4: UNIX_TIMESTAMP(date) function: returns the UNIX timestamp of date date.

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|1621922906 |
+-----------------------+
1 row in set (0.30 sec)

5: FROM_UNIXTIME (unixtime) function: Returns the date value of the UNIXTIME timestamp, which is the inverse operation of UNIX_TIMESTAMP(date).

mysql> select from_unixtime(1621922906);
+---------------------------+
| from_unixtime(1621922906) |
+---------------------------+
| 2021-05-25 14:08:26 |
+---------------------------+
1 row in set (0.31 sec)

6: WEEK(DATE) and YEAR(DATE) functions: The former returns the week of the year for the given date, and the latter returns the year for the given date.

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
| 21 | 2021 |
+-------------+-------------+
1 row in set (0.00 sec)

7: HOUR(time) and MINUTE(time) functions: the former returns the hour of the given time, and the latter returns the minute of the given time.

mysql> select hour(now()),minute(now());
+-------------+---------------+
| hour(now()) | minute(now()) |
+-------------+---------------+
| 14 | 11 |
+-------------+---------------+
1 row in set (0.00 sec)

8: MONTHNAME(date) function: returns the English month name of date.

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| May |
+------------------+
1 row in set (0.30 sec)

9: DATE_FORMAT(date,fmt) function: Formats the date value according to the string fmt. This function can display the date in the specified format. The available format characters are as shown in the table:

Format Characters Format description
%S,%s Second as a two-digit number (00,01,...,59)
%i Minute as two digits (00,01,...,59)
%H Two-digit hour, 24-hour format (00,01,...,23)
%h,%I Two-digit hour, 12-hour period (01,02,...,12)
%k Hour as a number, 24-hour period (0,1,...,23)
%l Hour as a number, 12-hour period (1,2,...,12)
%T 24-hour time format (hh:mm:ss)
%r 12-hour time format (hh:mm:ssAM or hh:mm:ssPM)
%p AM or PM
%W The name of each day of the week (Sunday, Monday, ..., Saturday)
%a Abbreviation of the day of the week (Sun,Mon,...,Sat)
%d Two-digit day of the month (00,01,...,31)
%e The day of the month as a number (1, 2, ..., 31)
%D English suffixes indicate the day of the month (1st, 2nd, 3rd, ...)
%w The day of the week as a number (0=Sunday, 1=Monday, ..., 6=Saturday)
%j The day of the year as a 3-digit number (001,002,...,366)
%U Week (0,1,52), where Sunday is the first day of the week
%u Week (0,1,52), where Monday is the first day of the week
%M Month name (January, February, ..., December)
%b Abbreviated month name (January, February, ..., December)
%m Two-digit month (01,02,...,12)
%c Month as a number (1,2,...,12)
%Y 4-digit year
%y Two-digit year
%% Direct value "%"
mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| May,25th,2021 |
+-------------------------------+
1 row in set (0.00 sec)

10: DATE_ADD (date, INTERVAL expr type) function: Returns the date that is INTERVAL time period away from the given date.

INTERVAL is the interval type keyword, expr is an expression that corresponds to the following type, and type is the interval type. MySQL provides 13 interval types.

As shown in the following table: Date interval types in MySQL:

Expression Type describe Format
HOUR Hour hh
MINUTE point mm
SECOND Second ss
YEAR Year YY
MONTH moon MM
DAY day DD
YEAR_MONTH Year and month YY-MM
DAY_HOUR Day and hour DD
DAY_MINUTE Day and minute DD hh:mm
DAY_ SECOND Days and seconds DD hh:mm:ss
HOUR_MINUTE Hours and minutes hh:mm
HOUR_SECOND Hours and seconds hh:ss
MINUTE_SECOND Minutes and seconds mm:ss

For example, the first column returns the current date and time, the second column returns the date and time 31 days from the current date, and the third column returns the date and time one year and two months from the current date.

mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2021-05-25 14:32:30 | 2021-06-25 14:32:30 | 2022-07-25 14:32:30 |
+---------------------+---------------------+------------------------+
1 row in set (0.03 sec)

You can also use negative numbers to return to a previous date and time. For example, the first column below returns the current date and time, the second column returns the date and time 31 days before the current date, and the third column returns the date and time one year and two months before the current date.

mysql> select now() current,date_add(now(),interval -31 day) before31days,date_add(now(),interval '-1_-2' year_month) before_oneyear_twomonth;
+---------------------+---------------------+----------------------+
| current | bef31days | bef_oneyear_twomonth |
+---------------------+---------------------+----------------------+
| 2021-05-25 14:34:38 ​​| 2021-04-24 14:34:38 ​​| 2020-03-25 14:34:38 ​​|
+---------------------+---------------------+----------------------+
1 row in set (0.00 sec)

11: DATEDIFF (date1, date2) function: used to calculate the difference in days between two dates

mysql> select datediff('2013-09-01',now());
+------------------------------+
| datediff('2013-09-01',now()) |
+------------------------------+
|-2823 |
+------------------------------+
1 row in set (0.30 sec)

4. Process Function

Process functions in MySQL:

function Function
IF(value,tf) If value is true, returns t; otherwise returns f
IFNULL(value1,value2) If value1 is not empty, return value1, otherwise return value2

CASE WHEN [value1]
THEN [result1]…ELSE [default] END

 If value1 is true, return result1, otherwise return default

CASE [expr] WHEN [value1]
THEN [result1]…ELSE [default] END

 If expr is equal to value1, return result1, otherwise return default

1: IF(value,tf), Example: Create and initialize an employee salary table, insert some test data, we believe that employees with a monthly salary of more than 2,000 yuan are high-paid, represented by "high", and employees with a monthly salary of less than 2,000 yuan are low-paid, represented by "low".

mysql> create table salary(userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.47 sec)

mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
6 rows in set (0.00 sec)

mysql> select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low |
| low |
| high |
| high |
| high |
| low |
+------------------------------+
6 rows in set (0.31 sec)

2: IFNULL(value1,value2) function is generally used to replace NULL values. We know that NULL values ​​cannot participate in numerical operations. The following statement replaces NULL values ​​with 0.

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
6 rows in set (0.00 sec)

3:CASE WHEN [value1] THEN [result1]…ELSE [default] END function: We can also use the case when…then function to solve the problem of high salary and low salary in the above example.

mysql> select case when salary<=2000 then 'low' else 'high' end from salary;
+---------------------------------------------------+
| case when salary<=2000 then 'low' else 'high' end |
+---------------------------------------------------+
| low |
| low |
| high |
| high |
| high |
| high |
+---------------------------------------------------+
6 rows in set (0.29 sec)

4:CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END function: Here you can also divide the employee's salary into multiple levels according to multiple situations, such as the example below, which is divided into three levels: high, medium and low.

mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
+-----------------------------------------------------------------------+
| case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+-----------------------------------------------------------------------+
| low |
| mid |
| high |
| high |
| high |
| high |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

5. Other commonly used functions

Other commonly used functions in MySQL:

function Function
DATABASE() Returns the current database name
VERSION() Returns the current database version
USER() Returns the currently logged in user name
INET_ATON(IP) Returns a numeric representation of the IP address
INET_NTOA(num) Returns the IP address represented by the number
PASSWORD(str) Returns the encrypted version of the string str
MD5() Returns the MD5 value of the string str

1: DATABASE() function: returns the current database name.

mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

2: VERSION() function: returns the current database version.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.26 |
+-----------+
1 row in set (0.00 sec)

3: USER() function: returns the currently logged-in user name.

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4: INET_ATON(IP) function: Returns the network byte order representation of the IP address.

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|3232235777 |
+--------------------------+
1 row in set (0.30 sec)

5: INET_NTOA(num) function: returns the IP address represented by network byte order.

mysql> select inet_ntoa('3232235777');
+-------------------------+
| inet_ntoa('3232235777') |
+-------------------------+
| 192.168.1.1 |
+-------------------------+
1 row in set (0.00 sec)

6: PASSWORD(str) function: Returns the encrypted version of the string str, a 41-bit string.

mysql> select password('1223456');
+-------------------------------------------+
| password('1223456') |
+-------------------------------------------+
| *3B5C2394E86BB91F1D03C5A1F2D3962BB287590B |
+-------------------------------------------+
1 row in set, 1 warning (0.33 sec)

7: MD5(str) function: returns the MD5 value of the string str, which is often used to encrypt data in applications.

mysql> select md5('123456');
+----------------------------------+
| md5('123456') |
+----------------------------------+
|e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.30 sec)

This is the end of this article on the comprehensive summary of MySQL functions. For more relevant MySQL function content, 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:
  • Thoroughly understand MySQL stored procedures and functions
  • Analysis of the difference between MySQL stored functions and stored procedures
  • Introduction to commonly used functions in MYSQL database
  • MySQL batch inserts data through function stored procedures
  • MySQL stored functions detailed introduction
  • Detailed explanation of creating stored procedures and functions in mysql
  • MySQL detailed summary of commonly used functions
  • How to use MYSQL functions

<<:  Summary of the differences between get and post requests in Vue

>>:  Detailed steps to deploy lnmp under Docker

Recommend

Fixed table width table-layout: fixed

In order to make the table fill the screen (the re...

Implementation of Mysql User Rights Management

1. Introduction to MySQL permissions There are 4 ...

MySql Sql optimization tips sharing

One day I found that the execution speed of a SQL...

How to support full Unicode in MySQL/MariaDB

Table of contents Introduction to utf8mb4 UTF8 by...

A brief discussion on the comparison of varchar, char and text in postgresql database

As shown below: name describe character varying(n...

How to package the project into docker through idea

Many friends have always wanted to know how to ru...

MySQL commonly used SQL and commands from entry to deleting database and running away

Table of contents Start and stop Database related...

The whole process of node.js using express to automatically build the project

1. Install the express library and generator Open...

Docker implements cross-host container communication based on macvlan

Find two test machines: [root@docker1 centos_zabb...

A simple method to deal with the tabBar at the bottom of WeChat applet blocking content

After configuring the tabBar in the WeChat applet...

Detailed explanation of MySQL injection without knowing the column name

Preface I feel like my mind is empty lately, as I...

When should a website place ads?

I recently discussed "advertising" with...

A record of the pitfalls of the WeChat applet component life cycle

The component lifecycle is usually where our busi...

Detailed steps to install nginx on Apple M1 chip and deploy vue project

brew install nginx Apple Mac uses brew to install...

display:grid in CSS3, an introduction to grid layout

1. Grid layout (grid): It divides the web page in...