Shulou(Shulou.com)05/31 Report--

This article mainly introduces mysql how to query the data containing the specified string, the article is very detailed, has a certain reference value, interested friends must read it!

In mysql, you can use the "SELECT" statement and the LIKE keyword to query the data containing the specified string. The LIKE keyword is mainly used to search for the specified content in the matching field. The syntax "SELECT field name FROM table name WHERE field name [NOT] LIKE 'string';".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, you can use the "SELECT" statement and the LIKE keyword to query data that contains a specified string.

The LIKE keyword is mainly used to search for the specified content in the matching field. The syntax format is as follows:

[NOT] LIKE 'string'

Where:

NOT: optional parameter that satisfies the condition when the content of the field does not match the specified string.

String: specifies the string to be used to match. "string" can be a complete string or contain wildcards.

The LIKE keyword supports percent sign "%" and underscore "_" wildcards.

Wildcards are special statements that are mainly used to obfuscate queries. When you don't know the real characters or don't bother to enter the full name, you can use wildcards instead of one or more real characters.

Examples of the use of "SELECT" statement + LIKE keyword

Query with "%" wildcard

"%" is the most commonly used wildcard character in MySQL. It can represent a string of any length, and the length of a string can be 0. For example, a% b represents a string of any length that begins with the letter an and ends with the letter b. This string can represent ab, acb, accb, accrb, and so on.

Example 1

In the tb_students_info table, look for all student names that start with the letter "T". The SQL statement and the results of the run are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE name LIKE's T% interview + | name | +-+ | Thomas | | Tom | +-+ 2 rows in set (0.12 sec)

As you can see, only the student names that begin with the letter "T" are returned in the query results.

Note: matching strings must be enclosed in single or double quotes.

NOT LIKE indicates that the condition is met when the string does not match.

Example 2

In the tb_students_info table, look for all student names that do not start with the letter "T". The SQL statement and the results of the run are as follows.

Mysql > SELECT NAME FROM tb_students_info-> WHERE NAME NOT LIKE's T% interview + | NAME | +-+ | Dany | | Green | | Henry | | Jane | | Jim | | John | | Lily | | Susan | +-+ 8 rows in set (0.00 sec)

As you can see, the name of the student who does not start with the letter "T" is returned in the query result.

Example 3

In the tb_students_info table, look for all student names that contain the letter "e", the SQL statement, and the run results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE name LIKE'% e% interview + | name | +-+ | Green | | Henry | | Jane | +-+ 3 rows in set (0.00 sec)

As you can see, all student names containing the letter "e" are returned in the query results.

Query with "_" wildcard

"_" can only represent a single character, and the length of the character cannot be 0. For example, afigb can represent strings such as acb, adb, aub, and so on.

Example 4

In the tb_students_info table, find all student names that end with the letter "y" and only precede "y" with four letters. The SQL statement and run results are as follows.

Mysql > SELECT name FROM tb_students_info-> WHERE name LIKE'_ sec + | name | +-+ | Henry | +-+ 1 row in set (0.00 sec)

LIKE is case sensitive

By default, the LIKE keyword is case-insensitive when matching characters. If you need to be case sensitive, you can add the BINARY keyword.

Example 5

In the tb_students_info table, look for all student names that begin with the letter "t". The case-sensitive and case-insensitive SQL statements and run results are as follows.

Mysql > SELECT name FROM tb_students_info WHERE name LIKE't% interview set + | name | +-+ | Thomas | | Tom | +-+ 2 rows in set (0.00 sec) mysql > SELECT name FROM tb_students_info WHERE name LIKE BINARY't% alternative empty set (0.01 sec)

As you can see from the results, when case-sensitive, records such as "Tom" and "Thomas" will not be matched.

The above is all the contents of the article "how to query data containing specified strings in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.