Functions of the front-end database, functions of the database

Functions of the front-end database, functions of the database
* Directory [1] character functions [2] numerical operators [3] comparison operators [4] Date and time [5] information functions [6] Aggregate functions [7] encryption functions [8] before a UDF

Functions in mysql databases can be divided into character functions, numerical operators and functions, comparison operators and functions, date and time functions, information functions, Aggregate functions, encryption functions, and custom functions. The following describes the functions in the database.

 

Character Functions

CONCAT ()

The CONCAT () function is used for character connection.

The CONCAT () function can also be used to concatenate all records of two fields in a data table.

CONCAT_WS ()

The CONCAT_WS () function uses the specified separator to connect characters. The first parameter of this function is to specify the delimiter.

FORMAT ()

The FORMAT () function is mainly used to FORMAT numbers and return a numeric value. The first parameter is the number to be formatted, and the second parameter is the number of decimal places to be retained

LOWER ()

The LOWER () function converts characters into uppercase letters.

UPPER ()

The UPPER () function converts characters to lowercase letters.

LEFT ()

The LEFT () function is used to obtain characters on the LEFT.

RIGHT ()

The RIGHT () function is used to obtain characters on the RIGHT.

LENGTH ()

The LENGTH () function is used to obtain the string LENGTH.

LTRIM ()

The LTRIM () function is used to delete leading spaces.

RTRIM ()

The RTRIM () function is used to delete subsequent spaces.

TRIM ()

The TRIM () function is used to delete leading and trailing spaces.

REPLACE ()

The REPLACE () function is used to REPLACE strings.

SUBSTRING ()

The SUBSTRING () function is used to intercept strings. The first parameter is the starting position of the intercept, and the second parameter is the number of characters to intercept.

[Note] the count starts from 1, not from 0.

If the second parameter is omitted, It is intercepted until the end of the string.

[NOT] LIKE

[NOT] LIKE is used for pattern matching. % Represents 0 or multiple characters, and _ represents any 1 character. Returns 1 to indicate a match, and 0 to indicate a mismatch.

 

Numeric Operators

CEIL ()

The CEIL () function is mainly used to get an integer (rounded up)

 

FLOOR ()

The FLOOR () function is mainly used to get an integer (rounded down)

ROUND ()

The ROUND () function is mainly used for rounding. The two parameters are floating point numbers and reserved decimal places, respectively.

DIV

DIV is mainly used for integer division.

MOD

MOD is mainly used to obtain the remainder (Modulo), which is equivalent to %. It can be an integer or decimal.

POWER ()

The POWER () function is mainly used for POWER operations.

TRUNCATE ()

The TRUNCATE () function is mainly used to intercept numbers. The two parameters are numerical values and n digits after the decimal point.

 

Comparison Operators

[NOT] BETWEEN... AND...

[NOT] BETWEEN... AND... indicates that [NOT] is within the range.

[NOT] IN ()

[NOT] IN () indicates that [NOT] is within the range of listed values

IS [NOT] NULL

IS [NOT] NULL indicates [No] IS NULL

Date and Time

NOW ()

The NOW () function returns the current date and time.

CURDATE ()

The CURDATE () function returns the current date.

CURTIME ()

CURTIME () function returns the current time

DATE_ADD ()

DATE_ADD () function date change, which can be increased or reduced

 DATEDIFF ()

DATEDIFF () function date difference, difference between two dates

DATE_FORMAT ()

DATE_FORMAT () function for date formatting

 

Information Functions

CONNECTION_ID ()

CONNECTION_ID () returns the connection ID (thread ID)

DATEBASE ()

DATEBASE () returns the name of the current database.

LAST_INSERT_ID ()

LAST_INSERT_ID () returns the ID of the last inserted record. When multiple records are written at a time, the ID returned by the function is the ID of the first record.

USER ()

USER () returns the current USER

VERSION ()

VERSION () returns VERSION information

Aggregate functions

Aggregate functions can only be used for data tables and cannot be used for calculation of a single value.

Create a test data table to test the data.

AVG (): average COUNT (): count max (): Maximum MIN (): Minimum SUM (): SUM

Encryption Functions

MD5 ()

MD5 (): information digest algorithm, used to prepare for future Web pages. use MD5 () whenever possible ()

PASSWORD ()

PASSWORD (): PASSWORD algorithm. Use PASSWORD () to change the PASSWORD of the current user and other users, and change the PASSWORD of the client.

Custom Functions

The function can return any type of values and receive these types of parameters. Function parameters are not necessarily related to the returned values.

[Note] A function can have a maximum of 1024 parameters.

User-defined Functions (udfs) are a way to expand MySQL. They are used in the same way as built-in functions.

Two necessary conditions for a user-defined function: 1. parameters; 2. return values

Create a UDF

CREATE FUNCTION function_nameRETURNS{STRING|INTEGER|REAL|DECIMAL}routine_body

UDF body

1. The function body can be composed of legal SQL statements;

2. The function body can be a simple SELECT or INSERT statement;

3. If the function body is in a composite structure, use the BEGIN... END statement;

4. The composite structure can contain declaration, loop, and control structures.

Create a UDF without Parameters

Create function f1 () returns varchar (30) RETURN DATE_FORMAT (NOW (), '% Y % m month % d % H: % I: % s ');

Create a UDF with Parameters

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS FLOAT(10,2) UNSIGNED RETURN  (num1+num2)/2;

[Note] if a user-defined function has multiple statements that conform to the structure, the function body must be included in BEGIN... at the same time, you must use DELIMITER to change the default terminator to other symbols, such as: // $, to avoid interruption of the function due to the; sign at the END of the statement.

Delete A Function

DROP FUNCTION [IF EXISTS] function_name