mysql like 否定_MySQL LIKE和NOT LIKE关键字

mysql> usetest;Databasechanged

mysql> create table student(age int(4), name varchar(20), address varchar(20));

Query OK,0 rows affected (0.22sec)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.08sec)

Records:5 Duplicates: 0 Warnings: 0mysql> select * fromstudent;+------+------+----------+

| age | name | address |

+------+------+----------+

| 12 | Jack | BeiJing |

| 19 | Mike | TianJin |

| 21 | Rose | ZhuHai |

| 23 | Ann | ShangHai |

| 27 | Alis | ShenZhen |

+------+------+----------+

5 rows in set (0.07sec)

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%';

Emptyset (0.00sec)

# like匹配以S开头的addressmysql> select * from student where address like 'S%';+------+------+----------+

| age | name | address |

+------+------+----------+

| 23 | Ann | ShangHai |

| 27 | Alis | ShenZhen |

+------+------+----------+

2 rows in set (0.00sec)

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.00sec)

mysql>