MySQL Common functions

1. Character functions

CONCAT () connection character

Mysql> Select Concat (' A ', ' B ', ' C '); +---------------------+| Concat (' A ', ' B ', ' C ') |+---------------------+

Concat_ws () connects with the specified delimiter

Mysql> Select Concat_ws ('-', ' A ', ' B ', ' C '); +----------------------------+| Concat_ws ('-', ' A ', ' B ', ' C ') |+----------------------------+| A-b-c                      |+----------------------------+

Format () number formatting

Mysql> Select Format (1234.567,2); +--------------------+| Format (1234.567,2) |+--------------------+| 1,234.57           |+--------------------+

LOWER () Letter converted to lowercase

Mysql> Select lower (' asdfgh '); +-----------------+| Lower (' asdfgh ') |+-----------------+| ASDFGH          |+-----------------+

UPPER () Letter converted to uppercase

Mysql> Select Upper (' asdfgh '); +-----------------+| Upper (' asdfgh ') |+-----------------+| ASDFGH          |+-----------------+

Left () gets the character

Mysql> Select Left (' Asdfgh ', 3); +------------------+| Left (' Asdfgh ', 3) |+------------------+| ASD              |+------------------+

Right () gets the character

+-------------------+| Right (' Asdfgh ', 3) |+-------------------+| FGH               |+-------------------+

Length () to get character lengths

Mysql> Select Length (' asdfgh '); +------------------+| Length (' asdfgh ') |+------------------+|                6 |+------------------+

LTRIM () Remove leading spaces

+------------------+| LTrim (' China  ') |+------------------+| China            |+------------------+

RTRIM () Remove the Guide space

+----------------------+| RTrim ('  China    ') |+----------------------+|   China              |+----------------------+

TRIM () remove leading and trailing spaces or specify characters

Mysql> Select Trim ('  China    '), +---------------------+| trim ('  China    ') |+---------------------+ | China               |+---------------------+
Mysql> Select trim (Leading ' a ' from ' Aaaaab '); +--------------------------------+| Trim (Leading ' a ' from ' Aaaaab ') |+--------------------------------+| b                              |+--------------------------------+
Mysql> Select trim (Trailing ' a ' from ' baaaaa '); +----------------------------------+| Trim (trailing ' a ' from ' baaaaa ') |+----------------------------------+| b                                |+----------------------------------+

Replace () character substitution

mysql> Select replace (' Aaaab ', ' A ', ' B '); +--------------------------+| Replace (' Aaaab ', ' A ', ' B ') |+--------------------------+| BBBBB                    |+--------------------------+

SUBSTRING () Intercept characters

mysql> Select substring (' China ', 3); +----------------------+| SUBSTRING (' China ', 3) |+----------------------+| Ina                  |+----------------------+

[NOT] Like () Pattern matching

2. Numerical Operations and functions

+ 、-、 *,/not much to say

Ceil () into a rounding

Mysql> Select Ceil (12345.34); +----------------+| Ceil (12345.34) |+----------------+|          12346 |+----------------+

Floor () Rounding and rounding

Mysql> Select Floor (12345.34); +-----------------+| Floor (12345.34) |+-----------------+|           12345 |+-----------------+

MOD takes remainder, equal to%

Mysql> Select 9 MoD 3;+---------+| 9 MoD 3 |+---------+|       0 |+---------+

DIV integer division, equal to/

Mysql> Select 9 div 3;+---------+| 9 Div 3 |+---------+|       3 |+---------+

Power () power to calculate

Mysql> Select power (2,3); +------------+| Power (2,3) |+------------+|          8 |+------------+

ROUND () rounding

Mysql> Select round (12345,345); +------------------+| Round (12345,345) |+------------------+|            12345 |+------------------+

TRUNCATE () Digital intercept

Mysql> Select truncate (12345.3456,-1); +-------------------------+| Truncate (12345.3456,-1) |+-------------------------+|                   12340 |+-------------------------+

3. Comparison operator functions

[NOT] Between. and.. Determine if there is an interval

[NOT]: In.. Determine whether to include in an array

is [NOT] null to determine if NULL

4. Date Time function

Now () displays the current date time

Curdate () Current date

Curtime () Current time

Date_add () Date and time change (can be added or subtracted)

Datedief () time difference between dates and times

Date_format () Date time formatting

5. Information function

CONNECTION_ID () Show Connection ID

Datebase () the currently open database

LAST_INSERT_ID () Displays the last inserted record

User () displays the current users

Version () Displays the revision number

6. Aggregation function

AVG () Average

Count () counts

Max () max value

Min () Min value

SUM () sum

7. Custom Functions

CREATE FUNCTION Fun_name

RETURNS type of return value

Routine_body

Example: CREATE FUNCTION adduser (username VARCHAR (20))

RETURNS INT UNSIGNED

BEGIN

INSERT Test (username) VALUES (username)

RETURN

LAST_INSERT_ID ()

END

Dorp Fun_name (); To delete a custom function

DELIMITER symbol, specifying ' symbol ' as a separator

MySQL Common functions