How to query data containing a specified string in mysql

In mysql, you can use the "SELECT" statement and the LIKE keyword to query 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 containing a specified string.

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

[NOT] LIKE  '字符串'

Among them:

• NOT: Optional parameter, the condition is met when the content in the field does not match the specified string.

• String: Specify the string to match. "String" can be a complete string, or it can contain wildcard characters.

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

Wildcard is a special statement, mainly used for fuzzy queries. Wildcards can be used to replace one or more real characters when the real characters are not known or you are too lazy to enter the full name.

Examples of using the LIKE keyword in the "SELECT" statement

with "%" wildcard query

"%" is the most commonly used wildcard character in MySQL. It can represent a string of any length. The length of the string can is 0. For example, a%b represents a string of any length starting with the letter a and ending with the letter b. This string can represent strings such as ab, acb, accb, accrb, etc.

Example 1

In the tb_students_info table, find all student names starting with the letter "T". The SQL statement and running results are as follows.

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

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

Note: The matching string must be enclosed in single quotes 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, find all student names that do not start with the letter "T". The SQL statement and running results are as follows.

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

As you can see, the query results return student names that do not start with the letter "T".

Example 3

In the tb_students_info table, find all student names containing the letter "e". The SQL statement and running results are as follows.

mysql> SELECT name FROM tb_students_info
    -> WHERE name LIKE '%e%';
+-------+
| 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 character

"_" can only represent A single character, the length of the character cannot be 0. For example, a_b can represent strings such as acb, adb, aub, etc.

Example 4

In the tb_students_info table, find all student names ending with the letter "y" and with only 4 letters in front of "y", SQL statement and The running results are as follows.

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

LIKE is case-sensitive

By default, the LIKE keyword is not case-sensitive when matching characters. If case sensitivity is required, you can add the BINARY keyword.

Example 5

In the tb_students_info table, find all student names starting with the letter "t", case-sensitive and case-insensitive SQL statements and operations The results are as follows.

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

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

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to query data containing a specified string in mysql. For more information, please follow other related articles on the PHP Chinese website!