1.LIKE操作符
mysql> SELECT prod_name FROM products;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE 'jet%'; #检索一任何"jet"开头的数据
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '%anvil%';
#'%anvil%'表示匹配任何位置包含文本anvil的值
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE 's%e';
#找出以s开头并一e结尾的所有产品
+-----------+
| prod_name |
+-----------+
| Safe |
+-----------+
1 row in set (0.00 sec)
*下划线(_)通配符
用途与%一样,但下划线只匹配单个字符
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '_ ton anvil';
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.01 sec)
请对比%操作符的结果:
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '% ton anvil';
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
*%表示匹配任何字符出现任意次数(>=0)
为了便于观察,先把prod_name字段的所有的数据列出
eg:mysql> SELECT prod_name FROM products;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE 'jet%'; #检索一任何"jet"开头的数据
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '%anvil%';
#'%anvil%'表示匹配任何位置包含文本anvil的值
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE 's%e';
#找出以s开头并一e结尾的所有产品
+-----------+
| prod_name |
+-----------+
| Safe |
+-----------+
1 row in set (0.00 sec)
*下划线(_)通配符
用途与%一样,但下划线只匹配单个字符
eg:
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '_ ton anvil';
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.01 sec)
请对比%操作符的结果:
mysql> SELECT prod_name FROM products
-> WHERE prod_name LIKE '% ton anvil';
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
3 rows in set (0.00 sec)