快捷導(dǎo)航

MySQL select查詢之LIKE與通配符用法

更新時間：2023年12月21日 09:38:01 作者：睿思達DBA_WGX

這篇文章主要介紹了MySQL select查詢之LIKE與通配符用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

select查詢之LIKE與通配符用法

在搜索數(shù)據(jù)庫中的數(shù)據(jù)時，SQL 通配符可以替代一個或多個字符。

SQL 通配符必須與 LIKE 運算符一起使用。可使用以下通配符：

通配符	描述
百分號（ % ）	替代一個或多個字符
下劃線（ _ ）	僅替代一個字符

一、百分號（ % ）

查詢時表示在該位置可以是任意個（0 - n 個）任意字符，必須和 LIKE 運算符一起使用。

例如：

1、查詢【姓王】的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> stu_name LIKE '王%';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801101 | 王占峰    | 男     | 1999-12-30 00:00:00 |  177.0 | 15937320987 | D02     |
| 201801201 | 王艷艷    | 女     | 1999-09-30 00:00:00 |  162.0 | NULL        | D03     |
| 201801203 | 王大強    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201901002 | 王宏偉    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
4 rows in set (0.01 sec)

2、查詢名字的最后一個字是【強】的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> stu_name LIKE '%強';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801102 | 劉國強    | 男     | 2000-08-14 00:00:00 |  174.0 | 15937320789 | D02     |
| 201801203 | 王大強    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
2 rows in set (0.00 sec)

3、查詢手機號最后一個數(shù)字是【5】的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> phone LIKE '%5';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801203 | 王大強    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201901002 | 王宏偉    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
2 rows in set (0.00 sec)

4、查詢名字中包含【鵬】字的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> stu_name LIKE '%鵬%';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801203 | 王鵬飛    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201901005 | 劉鵬      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
2 rows in set (0.00 sec)

二、下劃線（ _ ）

查詢時表示在該位置有且只有一個字符，字符的內(nèi)容不限。

必須和 LIKE 運算符一起使用。

例如：

1、查詢名字中第二個字是【鵬】的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> stu_name LIKE '_鵬%';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801203 | 王鵬飛    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201901005 | 劉鵬      | 男     | 2001-12-18 00:00:00 |  176.0 | NULL        | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
2 rows in set (0.00 sec)

2、查詢名字中第二個字是【鵬】并且名字一共是兩個字的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> stu_name LIKE '_鵬';
+-----------+----------+--------+---------------------+--------+-------+---------+
| stu_id    | stu_name | gender | birth               | height | phone | dept_id |
+-----------+----------+--------+---------------------+--------+-------+---------+
| 201901005 | 劉鵬     | 男     | 2001-12-18 00:00:00 |  176.0 | NULL  | D01     |
+-----------+----------+--------+---------------------+--------+-------+---------+
1 row in set (0.00 sec)

3、查詢 phone 中倒數(shù)第二個數(shù)字是【5】的學(xué)生信息

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> phone LIKE '%5_';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801103 | 鞏莉      | 女     | 2000-06-18 00:00:00 |  170.0 | 15937320456 | D02     |
| 201801203 | 王鵬飛    | 男     | 2000-10-19 00:00:00 |  174.0 | 15937320555 | D03     |
| 201901002 | 王宏偉    | 男     | 2001-02-15 00:00:00 |  180.0 | 15937320255 | D01     |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
3 rows in set (0.00 sec)