MySQL基础之 LIKE操作符
LIKE操作符
作用:用于在WHERE子句中搜索列中的指定模式。
语法:SELECT COLUMN_NAME FROM TABLE_NAME WHERE COLUMN_NAME LIKE PATTERN;
现在我们创建一个表
mysql> SELECT * FROM information; +------+----------+-----------------+-----------+ | id | name | address | city | +------+----------+-----------------+-----------+ | 1001 | xiaoming | NiuJin | England | | 1002 | xiaochen | Oxford street | New York | | 1003 | xiaoming | chaoyang street | Beijing | | 1004 | xiaohua | haha street | Zhengzhou | +------+----------+-----------------+-----------+ 4 rows in set (0.00 sec)
1、找出以“E”开头的城市的个人信息
mysql> SELECT * FROM information WHERE city LIKE 'E%'; +------+----------+---------+---------+ | id | name | address | city | +------+----------+---------+---------+ | 1001 | xiaoming | NiuJin | England | +------+----------+---------+---------+ 1 row in set (0.00 sec)
2、找出包含“ch”子串的姓名的个人信息
mysql> SELECT * FROM information WHERE name LIKE '%ch%'; +------+----------+---------------+----------+ | id | name | address | city | +------+----------+---------------+----------+ | 1002 | xiaochen | Oxford street | New York | +------+----------+---------------+----------+ 1 row in set (0.00 sec)
3、找出不包含“ch”子串的姓名的个人信息
mysql> SELECT * FROM information WHERE name NOT LIKE '%ch%'; +------+----------+-----------------+-----------+ | id | name | address | city | +------+----------+-----------------+-----------+ | 1001 | xiaoming | NiuJin | England | | 1003 | xiaoming | chaoyang street | Beijing | | 1004 | xiaohua | haha street | Zhengzhou | +------+----------+-----------------+-----------+ 3 rows in set (0.00 sec)