mysql function procedure

MySQL is an open source relational database management system that provides many functions and procedures to help us operate the database. In this article, I will introduce the commonly used functions and procedures in MySQL.

1. Function

1. String function

(1) concat() function

This function is used to combine multiple Strings are concatenated. For example, we can concatenate "Hello", "," and "world" to get the string "Hello, world".

Example:

SELECT CONCAT('Hello', ',', 'world');

Output:

Hello,world

(2) substring() function

This function is used to intercept part of the string. For example, we can intercept "world" in the string "Hello,world".

Example:

SELECT SUBSTRING('Hello,world', 7);

Output:

world

(3) replace() function

This function is used to replace one substring in a string with another substring. For example, we can replace "world" in the string "Hello,world" with "everyone".

Example:

SELECT REPLACE('Hello,world', 'world', 'everyone');

Output:

Hello,everyone

2. Number function

(1) abs() function

This function is used to return the absolute value of a number. For example, we can calculate the absolute value of -4.

Example:

SELECT ABS(-4);

Output:

4

(2) floor() function

This function is used to round down a decimal or double type value. For example, we can round 3.8 down to 3.

Example:

SELECT FLOOR(3.8);

Output:

3

(3) rand() function

This function is used to generate a random number. For example, we can generate a random number between 0 and 100.

Example:

SELECT FLOOR(RAND() * 100);

Output:

(random number)

3. Date function

(1) now() function

This function is used to return the current date and time. For example, we can get the current date and time.

Example:

SELECT NOW();

Output:

(current date and time)

(2) datediff ()Function

This function is used to calculate the number of days difference between two dates. For example, we can calculate the difference in days between two dates.

Example:

SELECT DATEDIFF('2022-01-01', '2021-01-01');

Output:

365

(3) year() function

This function is used to return the year of a date. For example, we can get the year of a date.

Example:

SELECT YEAR('2022-01-01');

Output:

2022

2. Process

A procedure is a predefined set of SQL statements that can be executed with a single call. There are the following types of procedures in MySQL:

(1) Creation process

The creation process can be used to perform complex operations, such as querying, updating and deleting data. When creating a procedure, you need to specify the procedure name and procedure body.

Example:

CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(50))
BEGIN
SELECT COUNT(*) INTO param1 FROM table1;
SELECT MAX(column1) INTO param2 FROM table1 WHERE column2 = param1;
END;

(2) Calling the procedure

When calling the procedure, you need to use the CALL statement to specify the procedure name and the data that needs to be passed parameter.

Example:

CALL procedure_name(@param1, @param2);
SELECT @param1 AS param1, @param2 AS param2;

（3）Delete process

When deleting a process, you need to use the DROP PROCEDURE statement.

Example:

DROP PROCEDURE procedure_name;

Summary

MySQL provides many functions and procedures to help us operate the database, including string functions , numeric functions and date functions, etc. Procedures can also be used to perform complex operations. Mastering these commonly used functions and procedures can allow us to operate the database more efficiently.

The above is the detailed content of mysql function procedure. For more information, please follow other related articles on the PHP Chinese website!