MySQL讲义第22讲——select 查询之 LIKE 与通配符

MySQL讲义第22讲——select 查询之 LIKE 与通配符

在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。SQL 通配符必须与 LIKE 运算符一起使用。可使用以下通配符：

一、百分号（ % ）

查询时表示在该位置可以是任意个（0 - n 个）任意字符，必须和 LIKE 运算符一起使用。例如：

1、查询【姓王】的学生信息

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、查询名字的最后一个字是【强】的学生信息

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、查询手机号最后一个数字是【5】的学生信息

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、查询名字中包含【鹏】字的学生信息

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)

二、下划线（ _ ）

查询时表示在该位置有且只有一个字符，字符的内容不限。必须和 LIKE 运算符一起使用。例如：

1、查询名字中第二个字是【鹏】的学生信息

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、查询名字中第二个字是【鹏】并且名字一共是两个字的学生信息

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 中倒数第二个数字是【5】的学生信息

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)