|
有时候我们需要查找带有某些匹配字母的表。在MySQL中我们可以使用LIKE或者NOT LIKE操作符进行比较。在MySQL中模式默认是不区分大小写的。
让我们进行一个查询示例，就是显示以字母M开头的学生名字。
mysql> select * from student where name like 'm%';
+--------+---------+-------+-----------------+---------+
| studid | name | marks | address | phone |
+--------+---------+-------+-----------------+---------+
| 3 | michael | 75 | edinburgh | 2598234 |
| 8 | mille | 98 | victoria street | 1236547 |
+--------+---------+-------+-----------------+---------+
2 rows in set (0.01 sec)
在以上的查询示例中，将列出在表student中所有以字母M开头的学生名字。
以下的查询示例将列出所有以字母e结尾的学生名字。
mysql> select * from student where name like '%e';
+--------+-------+-------+------------------+---------+
| studid | name | marks | address | phone |
+--------+-------+-------+------------------+---------+
| 1 | steve | 100 | 5th cross street | 2456987 |
| 5 | anne | 100 | downing street | 2634821 |
| 6 | steve | 75 | downing street | 2874698 |
| 7 | anne | 80 | edinburgh | 2569843 |
| 8 | mille | 98 | victoria street | 1236547 |
+--------+-------+-------+------------------+---------+
5 rows in set (0.00 sec)
我们也可以列出在任何地方包含任何特定字母的学生名字。以下的查询示例将列出包含"a"字母的学生名字。
mysql> select * from student where name like '%a%';
+--------+---------+-------+-----------------+---------+
| studid | name | marks | address | phone |
+--------+---------+-------+-----------------+---------+
| 2 | david | 98 | welling street | 547896 |
| 3 | michael | 75 | edinburgh | 2598234 |
| 4 | jack | 82 | victoria street | 2436821 |
| 5 | anne | 100 | downing street | 2634821 |
| 7 | anne | 80 | edinburgh | 2569843 |
+--------+---------+-------+-----------------+---------+
5 rows in set (0.00 sec)
假如我们要查找的名字包括5个字母，我们就可以使用特殊的字母"_"（下划线）。以下的查询示例将列好粗在表student中包括5个字母学生的名字。
mysql> select * from student where name like '_____';
+--------+-------+-------+------------------+---------+
| studid | name | marks | address | phone |
+--------+-------+-------+------------------+---------+
| 1 | steve | 100 | 5th cross street | 2456987 |
| 2 | david | 98 | welling street | 547896 |
| 6 | steve | 75 | downing street | 2874698 |
| 8 | mille | 98 | victoria street | 1236547 |
+--------+-------+-------+------------------+---------+
4 rows in set (0.00 sec)
| 
