MySQL Common functions

MySQL function

string-related functions 1 ASCII (String)

Returns the ASCII code for the first letter of a string:

SELECT ASCII (' A ');

SELECT ASCII (' AB ');

These two statements are output 65!

2 BIN (Long)

Returns a string in binary form of an integer

SELECT BIN (15);

Output 1111

3 CHAR (int ...)

Convert each integral type to the corresponding character

SELECT CHAR (65,66,67,68,69);

Output ABCDE

SELECT CHAR (' 65 ', ' 66 ', ' 67 ', ' 68 ', ' 69 ');

When the argument is a string, it is converted to an integer type and then converted to the corresponding character.

4 Char_length (String)

Returns the length of the string! The string length of ' China ' is 2. ' AB ' length is 2! Not in Chinese and English!

SELECT Char_length (' A in B country C ');

Output 5

5 Character_length (String)

Exactly the same as char_length ()!

6 CONCAT (String ...)

Concatenate multiple strings into a single string

SELECT CONCAT (' My ', ' SQL ');

Output MySQL

7 Concat_ws (separator, String ...)

Use the first argument string to concatenate the other argument strings.

SELECT concat_ws ('-', ' A ', ' B ', ' C ');

Output a-b-c

8 CONV (int n, int radix1, int radix2)

Converts n from radix1 into a radix2 binary.

SELECT CONV (' F ', 16, 10);

Parameter f is a numeric value, the second parameter 16 indicates that f is a 16 binary value, and the third parameter 10 shows that F is converted to 10, so the output is 15.

9 ELT (N, str1, str2, STR3 ...)

n is an integer, if n is 1, returns STR1, if n is 2, returns STR2, and so on

SELECT ELT (3, ' A ', ' B ', ' C ', ' d ', ' e ');

Output C

Export_set (int bits, string on, string off, strring separator, int length)

Bits is an integer that converts bits into binary, where 1 is replaced with on, 0 is switched with off, separator is used to connect each bits, and length is specified from left to right for several. If length is not specified, it is filled to 64 bits on the right.

SELECT Export_set (, ' A ', ' B ', '-', 4);

Output a-b-a-a, since the binary of 13 is 1011, where 1 is replaced with a, and 0 is replaced with B.

SELECT Export_set (, ' A ', ' B ', '-', 3);

Output a-b-a

SELECT Export_set (, ' A ', ' B ', '-', 10);

Output A-b-a-a-b-b-b-b-b-b, because 13 only has 4 bits, and length is 10, then will be in the right to fill 10 bits, are used 0 complement, so will be more than 6 B.

Process Control function 1 case value when V1 then EXPR1 when v2 then expr2 .... END

This case-then statement is basically the same as the switch statement in Java:

SELECT Case 3

When 1 Then ' a '

When 2 Then ' B '

When 3 Then ' C '

When 4 Then ' d '

END;

2 case if condition then expr1 when condition then expr2 ... END

This case-then statement is similar to the multi-branch If/else If in Java:

SELECT case

When 1>2 and then ' 1>2 '

When 1<2 and then ' 1<2 '

END;

3 IF (condition, expr1, EXPR1)

At the same time with If/else in Java

SELECT

IF (1 > 0, ' 1>0 ', ' 1<0 ');

4 Innull (EXPR1, EXPR2)

When EXPR1 is null, the function returns EXPR2, otherwise returns EXPR1.

SELECT

Ifnull (NULL, ' hello ');

?

SELECT

Ifnull (' Hello ', ' world ');

Common function 1 time date correlation function

• current_date (): outputs the current date;
• current_time (): Output current time;
• Current_timestamp (): Outputs the current date and time, which is the output current timestamp;
• Now (): Output The current date and time, same as Current_timestamp;

2 string-related functions

• CONCAT (' My ', ' SQL '): Output MySQL, that is, the connection string, parameters can be multiple;
• char_length (' HelloWorld in '): Output 11, returns the number of string characters;
• UCASE (' Hello '): output Hello;
• LCASE (' Hello '): Output HELLO;
• Left (' HelloWorld ', 5): Output Hello, output 5 characters on one side;
• LENGTH (' HelloWorld '): Output 13, returns the number of bytes that the string occupies. Of course, if the client's character set is GBK, then the output is 12;
• STRCMP (' Zhan ', ' Zhao '): Output-1, compares the size of two strings, if the former large returns 1, the latter large returns-1, equal returns 0;
• TRIM (' Hello '): Output Hello, remove left and right edges blank;

3 Mathematical operations related functions

• ABS ( -100): Output 100, absolute value;
• BIN (): Output 1111, which is a binary string of 15;
• FORMAT (1.23456, 3): Output 1.235, reserved 3 decimal places (rounded), if the whole number of parts greater than 3 bits will appear with commas;
• HEX (): Output F, that is, out of 16 binary representation;
• MOD (3): Output 1, that is, take the remainder operation;
• RAND (): A random decimal between the output 0~1;

?

?

MySQL Common functions