What is the fuzzy query syntax in mysql?

MySQL LIKE Fuzzy Query

We know that we use the SQL SELECT command in MySQL to read data, and we can use it in the SELECT statement WHERE clause to get the specified records.

You can use the equal sign = in the WHERE clause to set the conditions for obtaining data, such as "runoob_author = 'RUNOOB.COM'".

But sometimes we need to get all the records whose runoob_author field contains "COM" characters, then we need to use the SQL LIKE clause in the WHERE clause.

The percent sign % character is used in the SQL LIKE clause to represent any character, similar to the asterisk * in UNIX or regular expressions.

If the percent sign % is not used, the LIKE clause has the same effect as the equal sign =.

Syntax

The following is the general syntax of the SQL SELECT statement using the LIKE clause to read data from the data table:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• You can use the WHERE clause to specify any conditions.

• You can use the LIKE clause in the WHERE clause.

• You can use the LIKE clause instead of the equal sign =.

• LIKE is usually used with %, similar to a metacharacter search.

• You can use AND or OR to specify one or more conditions.

• You can use the WHERE...LIKE clause in a DELETE or UPDATE command to specify conditions.

Four uses of fuzzy query in mysql:

一.%: represents any 0 or more characters. Can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%) to express it.

*Note: like '%龙%': Query all records containing "dragon"

1. Query field like '%龙%'

eg:select * from user where realname like '%龙%'

Search for the realname field containing the word "龙"

2. The queried field is like '%龙%' and the queried field is like '%文%'

eg: select * from user where realname like '%龙%' and realname like '%文%'

= The queried field is like '%文%' and the queried field is like '%龙%'

#eg: select * from user where realname like '%文% ' and realname like '%龙%'

3. Query all records that contain both "龙" and "文"

realname like'%龙%文%': Query all "龙文" records, which can be "龙_文" "_龙文" "龙文_"

"Dragon" must be in front, the word "Wen" is behind

SELECT * From User Where RealName Like '%Dragon%Dragon%'

realname like'%文%龙%':

Query all "文龙" records, which can be "文_龙" "_文龙" "文龙_"

                                                                                                                                                                           The word must be in front, and the word "龙" in the back

select * from user where realname like '%文%龙%'

##二._： Represents any single character. Matches a single arbitrary character, which is often used to limit the character length of expressions:

1. Query the realname field containing the word "Lin" in the middle

(Premise: three names Words)

select * from user where realname like '_林_'

=select * from user where realname like '%_林_%'

We can only query the realname like "Yu Linwen", which has three characters and the middle character is: "林"

##2. Query the name of the surname Lin

(three characters of the name)

select * from user where realname like'Lin__'(there are two horizontal bars here)

###=select * from user where realname like '%林__%'###(There are two horizontal bars here)######

Query the realname of the surname "Lin", and the number of characters in realname must be 3

3. Query the last character of the name For the name of "Lin"(three characters of the name)

select * from user where realname like '__Lin'(there are two horizontal bars here)

= select * from user where realname like '%__林%'(There are two horizontal bars here)

Query the last name The name is "Lin", and the number of characters in realname must be 3

##4. Query the name with the surname Lin (The name has only two characters)

select * from user where realname like 'Lin_'

Query the person with the surname Lin Name(The name can be two characters or three characters)

select * from user where realname like '%林_%'

5. Query the name whose last character is "Lin" (the name has only two characters )

select * from user where realname like '_林'

Query the name whose last character is "Lin" (the name can have two characters , or it can be three characters)

select * from user where realname like '%_林%'

三.[ ]： Represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range, requiring the matched object to be any one of them.

select * from user where realname like '[Zhang Cai Wang] Jie'

Query "Zhang Jie", "Cai Jie", "Wang Jie"

(instead of "Zhang Jie" Cai Wangjie")

If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"

select * from user where realname like 'Lin [1-9]' will query "Lin 1" "Lin 2"... "Lin 9"

四.[^ ]: means not in the brackets A single character within a column. Its value is the same as [], but it requires that the matched object is any character other than the specified character.

select * from user where realname like '[^Zhang Cai Wang] Jie'

Query out "Lin Jie" whose surname is not "Zhang", "Cai", "Wang", " Zhao Jie" etc.

select * from user where realname like 'Lin[^1-4]'

will exclude "Lin 1" to "Lin 4" and look for "Lin 5", "Lin 6",...

Recommended tutorial:

mysql video tutorial

The above is the detailed content of What is the fuzzy query syntax in mysql?. For more information, please follow other related articles on the PHP Chinese website!