快捷導(dǎo)航

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

更新時(shí)間：2023年12月21日 09:38:01 作者：睿思達(dá)DBA_WGX

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

select查詢之LIKE與通配符用法

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

SQL 通配符必須與 LIKE 運(yùn)算符一起使用。可使用以下通配符：

通配符	描述
百分號(hào)（ % ）	替代一個(gè)或多個(gè)字符
下劃線（ _ ）	僅替代一個(gè)字符

一、百分號(hào)（ % ）

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

例如：

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 | 王大強(qiáng)    | 男     | 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、查詢名字的最后一個(gè)字是【強(qiáng)】的學(xué)生信息

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

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

mysql> SELECT
    -> *
    -> FROM
    -> stu
    -> WHERE
    -> phone LIKE '%5';
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| stu_id    | stu_name  | gender | birth               | height | phone       | dept_id |
+-----------+-----------+--------+---------------------+--------+-------------+---------+
| 201801203 | 王大強(qiáng)    | 男     | 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)

二、下劃線（ _ ）

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

必須和 LIKE 運(yùn)算符一起使用。

例如：

1、查詢名字中第二個(gè)字是【鵬】的學(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、查詢名字中第二個(gè)字是【鵬】并且名字一共是兩個(gè)字的學(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ù)第二個(gè)數(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)