MySQL query syntax record

Query string length in MySQL:

Length: is a calculation of the lengths of a field a Chinese character is counted three characters, a number or letter is counted as a character

The Char_length (str) return value is the length of the string str, and the length of the unit is a character. A multibyte character counts as a single word. For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.

Character_length (str) character_length () is a synonym for Char_length ().

Bit_length (str) returns 2 in length.

For example, you can find a list of users with a user name that is less than 6 characters long.

SQL code:

SELECT * from admin WHERE LENGTH (username) < 6

Char_length (str)

The return value is the length of the string str, and the length of the unit is a character. A multibyte character counts as a single word. For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.

Character_length (str)

Character_length () is a synonym for Char_length ().

Bit_length (str)

Returns the 2 feed length.

In a nutshell, there are two functions in MySQL to get the length of a string:

Length: Returns the number of bytes in the string, which is the length of the calculated field. A Chinese character is counted as three characters, a digit or letter is counted as a character

Char_length: Returns the number of characters in a string, whether kanji or digit or letter is a character

Append string when updating in MySQL:

Update table name set column name =concat (column name, ' appended string ');

Intercept string:

Function:

1. Intercept string from left

Left (str, length)

Description: Left (intercepted field, intercept length)

Example: Select Left (content,200) as abstract from my_content_t

2, from the right start to intercept the string

Right (str, length)

Description: Right (intercepted field, intercept length)

Example: Select Right (content,200) as abstract from my_content_t

3, intercept the string

SUBSTRING (str, POS)

SUBSTRING (str, pos, length)

Description: Substring (intercepted field, starting from the first few)

substring (truncated field, intercept from first, intercept length)

Example: Select substring (content,5) as abstract from my_content_t

Select substring (content,5,200) as abstract from my_content_t

(Note: If the number of digits is negative such as-5 is the number of digits from the back, to the end of the string or the length of the Intercept)

4, by keyword intercept string

Substring_index (Str,delim,count)

Description: Substring_index (truncated fields, keywords, number of keyword occurrences)

Example: Select Substring_index ("Blog.jb51.net", ". ", 2) as abstract from my_content_t

Result: blog.jb51

(Note: If the number of occurrences of the keyword is negative such as-2 is from the bottom to the end of the string)

Introduction to Functions:

SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for Len)

The format without the Len parameter returns a substring from string str, starting at position pos. Format with len parameter returns a substring of the same length as Len character from String str, starting at position pos.

Use the from format as the standard SQL syntax. It is also possible to use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string. In the following

You can use a negative value for a POS in a function of the format.

Type conversions used in query statements in MySQL

Cast function, convert function.

Usage: CAST (expr as type), convert (Expr,type), convert (expr USING transcoding_name).

SELECT CONVERT (' abc ' USING UTF8);

Converts varchar to int using cast (str as unsigned) str as varchar type of string.

For example, a commonly used percentage conversion:

Select CAST ((1/3) *100 as UNSIGNED) as percent from dual;

Result:33

MySQL type conversion function parameters: Cast (xxx as type), convert (XXX, type)

This type can be one of the following values:

binary[(N)]

char[(N)]

DATE

Datetime

DECIMAL

Signed [INTEGER]

Time

UNSIGNED [INTEGER]

Integer: Signed

unsigned integer: UNSIGNED

Binary, with the effect of BINARY prefix: BINARY

Character type, with parameters: CHAR ()

Dates: Date

Time: Times

Date-time: DATETIME

Floating point numbers: DECIMAL

BINARY Str is the abbreviated form of cast (str as BINARY):

Source: http://blog.csdn.net/wanghui19931015/article/details/54016117