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

bitsCN.com

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)

 

 

bitsCN.com