Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to write fuzzy query syntax in mysql, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

MySQL LIKE fuzzy query

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

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

But sometimes we need to get all the records whose runoob_author field contains the character "COM", and 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% sign is not used, the LIKE clause has the same effect as the equal sign =.

Grammar

The following is the general syntax for SQL SELECT statements to read data from a data table using the LIKE clause:

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

You can specify any condition in the WHERE clause.

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 specify one or more conditions using AND or OR.

You can use the WHERE...LIKE clause in the DELETE or UPDATE command to specify the condition.

Four uses of fuzzy queries in mysql:

One.%: represents any 0 or more characters. Characters of any type and length can be matched. In some cases, if it is in Chinese, please use two semicolons (%%).

* Note: like'% Dragon%': query all records containing "dragon"

1. The queried field + like'% dragon%'

Eg:select * from user where realname like'% Dragon%'

Search out the realname field that contains the word "dragon"

two。 Queried field + like'% dragon% 'and queried field + like'% text%'

Eg: select * from user where realname like'% Dragon% 'and realname like'% Wen%'

= queried field + like'% text% 'and queried field + like'% dragon%'

Eg: select * from user where realname like'% Wen% 'and realname like'% Dragon%'

3. Find out all the records that contain both "dragon" and "text".

Realname like'% dragon% text%': query all "dragon text" records, which can be "dragon text", "dragon text" and "dragon text"

The word "dragon" must be in the front and the word "Wen" in the back.

Select * from user where realname like'% Dragon% Wen%'

Realname like'% text% Dragon%': query all "Wenlong" records, which can be "Wenlong", "Wenlong" and "Wenlong"

The word "Wen" must be in the front and the word "dragon" in the back.

Select * from user where realname like'% Wen% long%'

Two. _: represents any single character. Matches a single arbitrary character, which is often used to limit the character length statement of an expression:

1. Query the realname field with the word "Lin" in the middle (premise: first name)

Select * from user where realname like'_ Lin _'

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

We can only find out that the realname like "Yu Linwen" is three words and the middle word is "Lin".

two。 Find out the surname Lin (first name three words)

Select * from user where realname like' Lin _ _'(there are two bars here)

= select * from user where realname like'% Lin _%'(there are two horizontal bars here)

Query the realname with the surname "Lin", and the word number of realname must be 3

3. Query the last word of the name is "Lin" (three words)

Select * from user where realname like'_ _ Lin'(there are two bars here)

= select * from user where realname like'% _ Lin%'(there are two horizontal bars here)

Query the name whose last word is "Lin", and the number of words in realname must be 3.

4. Find out the name of Lin (there are only two words)

Select * from user where realname like 'Lin _'

Find out the name of Lin (the name can be two words or three words)

Select * from user where realname like'% Lin%'

5. Query the name whose last word is "Lin" (only two words)

Select * from user where realname like'_ Lin'

The last word of the query name is "Lin" (the name can be two or three words)

Select * from user where realname like'% _ Lin%'

Three. []: represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range that requires the matching object to be any one of them.

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

Find out "Zhang Jie", "Cai Jie", "Wang Jie" (not "Zhang Cai Wang Jie")

If there are a series of characters (01234, abcde, etc.) in [], they can be abbreviated as "0-4" or "Amure".

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

Four. [^]: represents a single character that is not listed in parentheses. Its value is the same as [], but it requires that the matching object be any character other than the specified character.

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

Find out that the surnames are not "Zhang", "Cai", "Lin Jie", "Zhao Jie" and so on.

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

Will rule out "Lin 1" to "Lin 4" and look for "Lin 5", "Lin 6", …...

Thank you for reading this article carefully. I hope it is helpful for everyone to share the fuzzy query grammar in mysql. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.