Functions of the front-end learning Database

xTable of Contents [1] character function [2] numeric operator [3] comparison operator [4] datetime [5] Information function [6] aggregate function [7] cryptographic function [8] Custom function before

Functions in the MySQL database can be divided into character functions, numeric operators and functions, comparison operators and functions, date-time functions, information functions, aggregate functions, cryptographic functions, and custom functions, and so on. The functions in the database are described in detail below

Character functions

CONCAT ()

CONCAT () function for character connections

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

Concat_ws ()

The Concat_ws () function uses the specified delimiter for character joins, and the first parameter of the function is the specified delimiter

FORMAT ()

The format () function is primarily used for numeric formatting and ultimately returns a character number. The first parameter is the number to be formatted, and the second argument is the number of decimal digits to keep

LOWER ()

LOWER () function converts characters to uppercase

UPPER ()

The UPPER () function converts characters to lowercase letters

Left ()

The left () function is used to get the character

Right ()

The right () function is used to get the character

LENGTH ()

The length () function is used to get string lengths

LTRIM ()

LTRIM () function to remove leading spaces

RTRIM ()

RTRIM () function to remove subsequent spaces

TRIM ()

TRIM () function to remove leading and trailing spaces

REPLACE ()

Replace () function for string substitution

SUBSTRING ()

The SUBSTRING () function is used for string interception, the first argument is the starting position of the start intercept, and the second argument is the number of characters to intercept.

[note] This is counted starting from 1, not starting from 0.

If the second argument is omitted, it is truncated to the end of the string

[NOT] Like

[NOT] Like is used for pattern matching, where% represents 0 or more characters and _ represents any 1 characters. Returns 1 for match, 0 for mismatched

numeric operators

Ceil ()

The Ceil () function is used primarily for rounding (rounding up)

Floor ()

The floor () function is mainly used for rounding (rounding down)

ROUND ()

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

Div

Div is primarily used for integer division

MOD

MoD is mainly used to take the remainder (modulo), equal to%, can be an integer or it can be a decimal

POWER ()

The power () function is primarily used for power operations

TRUNCATE ()

The TRUNCATE () function is mainly used for digital interception, and two parameters are numeric and truncated to n digits after a decimal point

Comparison operators

[NOT] Between ... And ...

[NOT] Between ... And ... means [not] within the range

[NOT] In ()

[NOT] In () means [not] within the range of values listed

is [NOT] NULL

is [not] null means [NOT] null

Date Time

Now ()

The now () function returns the current date and time

Curdate ()

The Curdate () function returns the current date

Curtime ()

The Curtime () function returns the current time

Date_add ()

Date_add () function date change, can be increased, can also be reduced

DATEDIFF ()

DATEDIFF () function Date difference value, two date difference

Date_format ()

Date_format () function for date formatting

Information functions

CONNECTION_ID ()

CONNECTION_ID () returns the connection ID (thread ID)

Datebase ()

Datebase () returns the current database name

LAST_INSERT_ID ()

LAST_INSERT_ID () returns the ID of the last inserted record, and when multiple records are written once, the ID returned by the function is the ID of the first record

USER ()

User () returns the current users

VERSION ()

Version () returns the release information

Aggregation functions

Aggregate functions can only be used for data tables and cannot be used for calculations of individual values

A test data table is created below for testing data

AVG (): Average count (): Count Max (): Max min (): Min sum (): Sum

Cryptographic functions

MD5 ()

MD5 (): Information digest algorithm, prepare for future Web pages, use MD5 () as much as possible

PASSWORD ()

PASSWORD (): Password algorithm, modify the password of the current user and other user through PASSWORD (), modify the client's own password

Custom functions

Functions can return any type of value, and they can also receive parameters of these types. There is no definite connection before the function parameter and the return value

[note] A function can have a maximum of 1024 parameters

User-defined functions (user-defined function,udf) are a way to extend MySQL, using the same functionality as built-in functions

Two prerequisites for a custom function: 1, parameters, 2, return value

Creating a Custom function

CREATE FUNCTION function_namereturns{string| integer| real| Decimal}routine_body

About the function body of a custom function

1. The function body can be constituted by a valid SQL statement;

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

3. Function body If it is a composite structure, use begin ... End statement;

4. A composite structure can contain declarations, loops, and control structures.

Creating a custom function with no parameters

CREATE FUNCTION F1 () RETURNS VARCHAR (()RETURN Date_format (now (),'%y year%m month%d day%h point:%i min:% s seconds ');

Create a custom function with parameters

CREATE FUNCTION F2 (num1 SMALLINT unsigned,num2 SMALLINT UNSIGNED) RETURNS FLOAT (2) UNSIGNED return< c2/> (num1+num2)/2;

[note] If the custom function has multiple statements conforming to the structure, the body of the function is to be included in the BEGIN ... End, at the same time, the default terminator needs to be passed through the delimiter; Modified to other symbols, such as://$$, lest the function due to the end of the statement; The number of interrupts caused

Delete a function

DROP FUNCTION [IF EXISTS] Function_name

Functions of the front-end learning Database