MySQL LIKE和NOT LIKE关键字

mysql> use test;
Database changed
mysql> create table student(age int(4), name varchar(20), address varchar(20));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into student values(12, 'Jack', 'BeiJing'), (19, 'Mike', 'TianJin'), (21, 'Rose', 'ZhuHai'), (23, 'Ann', 'ShangHai'), (27, 'Alis', 'ShenZhen');
Query OK, 5 rows affected (0.08 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+------+----------+
| age  | name | address  |
+------+------+----------+
|   12 | Jack | BeiJing  |
|   19 | Mike | TianJin  |
|   21 | Rose | ZhuHai   |
|   23 | Ann  | ShangHai |
|   27 | Alis | ShenZhen |
+------+------+----------+
5 rows in set (0.07 sec)

mysql>

mysql> select * from student where name='Jack';
+------+------+---------+
| age  | name | address |
+------+------+---------+
|   12 | Jack | BeiJing |
+------+------+---------+
1 row in set (0.00 sec)

# like关键字是要完全匹配，与name='Jack'的作用相同

mysql> select * from student where name like 'Jack';
+------+------+---------+
| age  | name | address |
+------+------+---------+
|   12 | Jack | BeiJing |
+------+------+---------+
1 row in set (0.04 sec)

mysql>

mysql> select * from student where address='S%';
Empty set (0.00 sec)

# like匹配以S开头的address
mysql> select * from student where address like 'S%';
+------+------+----------+
| age  | name | address  |
+------+------+----------+
|   23 | Ann  | ShangHai |
|   27 | Alis | ShenZhen |
+------+------+----------+
2 rows in set (0.00 sec)

mysql>

mysql> insert into student values(55, 'Make', 'GuangZhou');
Query OK, 1 row affected (0.02 sec)

mysql> select * from student;
+------+------+-----------+
| age  | name | address   |
+------+------+-----------+
|   12 | Jack | BeiJing   |
|   19 | Mike | TianJin   |
|   21 | Rose | ZhuHai    |
|   23 | Ann  | ShangHai  |
|   27 | Alis | ShenZhen  |
|   55 | Make | GuangZhou |
+------+------+-----------+
6 rows in set (0.00 sec)

# like匹配'_'表示匹配一个字符

mysql> select * from student where name like 'M_ke';
+------+------+-----------+
| age  | name | address   |
+------+------+-----------+
|   19 | Mike | TianJin   |
|   55 | Make | GuangZhou |
+------+------+-----------+
2 rows in set (0.00 sec)

mysql>

# not like表示“不匹配”关键字

mysql> select * from student where name not like 'M_ke';
+------+------+----------+
| age  | name | address  |
+------+------+----------+
|   12 | Jack | BeiJing  |
|   21 | Rose | ZhuHai   |
|   23 | Ann  | ShangHai |
|   27 | Alis | ShenZhen |
+------+------+----------+
4 rows in set (0.00 sec)

mysql>