MySQL学习足迹记录06--数据过滤--LIKE搭配百分号（%）和下划线（_）通配符

1.LIKE操作符

*%表示匹配任何字符出现任意次数（>=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)