# 字符串过滤

__*LIKE 操作*__

+ 字符字面值应使用单引号: 找到行星的名字、质量和直径
  + 地球
  + 除了地球
  + 以字母 **M** 开头的行星
  + 行星名称为 **6** 个字母
  + 第二个字母是 **a**, 至少 **5** 个字母的行星
  + 地球、火星和天王星

```11
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@= 'Earth'@!~;
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@!= 'Earth'@!~;
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@LIKE 'M%'@!~;
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@LIKE '______'@!~;
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@LIKE '_a___%'@!~;
SELECT name, mass, diameter FROM universe.planets WHERE name ~!@IN ('Earth', 'Mars', 'Uranus')@!~;
```
-------------------------------------------------------------
使用 LIKE 模糊匹配时,查找字符串中通配符 % 放首位会导致无法使用索引。业务语句中使用 LIKE 查找字符串不使用 % 放首位,或者使用时结合其他有效的约束条件。

```
tidb> SELECT name, mass, diameter FROM universe.planets WHERE name = 'Earth';
+-------+------+----------+
| name  | mass | diameter |
+-------+------+----------+
| Earth | 5.97 | 12756.00 |
+-------+------+----------+
1 row in set (0.00 sec)

tidb> SELECT name, mass, diameter FROM universe.planets WHERE name != 'Earth';
+---------+-------+-----------+
| name    | mass  | diameter  |
+---------+-------+-----------+
| Mercury |  0.33 |   4879.00 |
| Venus   |  4.87 |  12104.00 |
| Mars    | 0.642 |   6792.00 |
| Jupiter |  1898 | 142984.00 |
| Saturn  |   568 | 120536.00 |
| Uranus  |  86.8 |  51118.00 |
| Neptune |   102 |  49528.00 |
| Pluto   | 0.013 |   2376.00 |
+---------+-------+-----------+
8 rows in set (0.00 sec)

tidb> SELECT name, mass, diameter FROM universe.planets WHERE name LIKE 'M%';
+---------+-------+----------+
| name    | mass  | diameter |
+---------+-------+----------+
| Mercury |  0.33 |  4879.00 |
| Mars    | 0.642 |  6792.00 |
+---------+-------+----------+
2 rows in set (0.01 sec)

tidb> SELECT name, mass, diameter FROM universe.planets WHERE name LIKE '______';
+--------+------+-----------+
| name   | mass | diameter  |
+--------+------+-----------+
| Saturn |  568 | 120536.00 |
| Uranus | 86.8 |  51118.00 |
+--------+------+-----------+
2 rows in set (0.00 sec)

tidb> SELECT name, mass, diameter FROM universe.planets WHERE name LIKE '_a___%';
+--------+------+-----------+
| name   | mass | diameter  |
+--------+------+-----------+
| Earth  | 5.97 |  12756.00 |
| Saturn |  568 | 120536.00 |
+--------+------+-----------+
2 rows in set (0.00 sec)

tidb> SELECT name, mass, diameter FROM universe.planets WHERE name IN ('Earth', 'Mars', 'Uranus');
+--------+-------+----------+
| name   | mass  | diameter |
+--------+-------+----------+
| Earth  |  5.97 | 12756.00 |
| Mars   | 0.642 |  6792.00 |
| Uranus |  86.8 | 51118.00 |
+--------+-------+----------+
3 rows in set (0.00 sec)

```